ASP.NET

Member 12863453 wrote:

string excelConnectionString = @"Microsoft.Jet.OLEDB.4.0;Data Source=" + path +";Extended Properties=Excel 8.0;HDR =YES;Persist Security Info=False";

Member 12863453 wrote:

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

<pre>using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;
using System.Timers;
using System.Configuration;
using System.Data.OleDb;


namespace MoveToDest
{
    public partial class Service1 : ServiceBase
    {
        long delay = 5000;
        protected string LogPath = ConfigurationManager.AppSettings["LogPath"];
        protected string MoveFilePathPath = ConfigurationManager.AppSettings["MovePath"];
        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            WriteLog("Service started");


            if (!Directory.Exists(LogPath))
            {
                Directory.CreateDirectory(LogPath);
            }

            try
            {

                delay = Int32.Parse(ConfigurationManager.AppSettings["IntervalInSeconds"]) * 1000;
            }
            catch
            {
                WriteLog("IntervalInSeconds key/value incorrect.");
            }


            if (delay < 5000)
            {
                WriteLog("Sleep time too short: Changed to default(5 secs).");
                delay = 5000;
            }

            Timer timer1 = new Timer();
            timer1.Elapsed += new ElapsedEventHandler(OnElapsedTime);
            timer1.Interval = delay;
            timer1.Enabled = true;
        }

        private void OnElapsedTime(object source, ElapsedEventArgs e)
        {
            write();
            Upload();
        }

        private void write()
        {

            string sourcepath = ConfigurationManager.AppSettings["sourcepath"];
            WriteLog("Set source path");
            string[] sourcefiles = Directory.GetFiles(sourcepath);
            WriteLog("Get soutce path file");

            foreach (string childfile in sourcefiles)
            {
                WriteLog("Start to find file from loop");
                string sourceFileName = new FileInfo(childfile).Name;
                string destinationPath = ConfigurationManager.AppSettings["destinationPath"];

                string destinationFileName = sourceFileName;
                string sourceFile = Path.Combine(sourcepath, sourceFileName);
                WriteLog("Get file from source to destination");
                string destinationFile = Path.Combine(destinationPath, destinationFileName);
                WriteLog("Ready to copy");
                File.Copy(sourceFile, destinationFile, true);
                WriteLog("File copied");
                File.Delete(sourceFile);
                WriteLog("File deleted");
            }
        }
        protected void Upload()
        {
            string excelPath = ConfigurationManager.AppSettings["ExcelPath"];
            DirectoryInfo d = new DirectoryInfo(excelPath);
            FileInfo[] Files = d.GetFiles("*.xlsx");



            string str = "";
            WriteLog("ready to enter into loop");
            foreach (FileInfo file in Files)
            {
                str = file.Name;
                string sourceFilePath = ConfigurationManager.AppSettings["SourceFilePath"];
                string SourceFileName = Path.Combine(sourceFilePath, str);

                string destinationFilePath = ConfigurationManager.AppSettings["MovePath"];

                string destinationFileName = destinationFilePath + str;




                WriteLog("get file :" + str);
                String strConnection = ConfigurationManager.AppSettings["constr"];
                WriteLog("declare database connection");
                string path = excelPath + str;

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                WriteLog("declare excel oledb connection");
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                OleDbCommand cmd = new OleDbCommand("Select [ID],[Name] from [Sheet1$]", excelConnection);

                excelConnection.Open();
                WriteLog("oledb open");
                OleDbDataReader dReader;

                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

                sqlBulk.DestinationTableName = "shrabon";
                WriteLog("ready to insert into database");
                sqlBulk.WriteToServer(dReader);
                WriteLog("data inserted");
                excelConnection.Close();
                WriteLog("Process completed");
                File.Move(SourceFileName, destinationFileName);
                WriteLog("File moved to bak folder");
                excelConnection.Close();
                cmd.Dispose();
            }
        }

        protected void WriteLog(string Msg)
        {
            FileStream fs = new FileStream(LogPath + "\\" + System.DateTime.Today.ToString("yyyyMMdd") + ".log", FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);
            sw.BaseStream.Seek(0, SeekOrigin.End);
            sw.WriteLine(System.DateTime.Now.ToString() + ": " + Msg);
            sw.Close();
            sw.Dispose();
            fs.Close();
            fs.Dispose();
        }


        protected override void OnStop()
        {
            WriteLog("service stopped");
        }
    }
}

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
  <appSettings>
    <add key="IntervalInSeconds" value="5"/>
    <add key="sourcepath" value="F:\shrabon"/>
    <add key="destinationPath" value="E:\Test"/>
    <add key="ExcelPath" value="E:\Test\"/>
    <add key="LogPath" value="E:\Floralog"/>
    <add key="MovePath" value="E:\Test\bak\"/>
    <add key="SourceFilePath" value="E:\Test"/>
    <add key="constr" value="Data Source=.;Initial Catalog=zam;User ID=eftuser;Password=eftuser321"/>
  </appSettings>
</configuration>

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

Text='<%# Eval("rdlmhorsepType", "{0}") ?? "Recoil" %>'

Text='<%# string.IsNullOrEmpty(Eval("rdlmhorsepType", "{0}")) ? "Recoil" : Eval("rdlmhorsepType", "{0}") %>'

public interface IProductRepository
{
    Product Get(int id);
    void Add(Product product);
}

public class ProductService : IProductService
{
    private IProductRepository productRepository;

    public ProductService(IProductRepository productRepository)
    {
        this.productRepository = productRepository;
    }

    public Product GetProduct(int id)
    {
        return this.productRepository.Get(id);
    }
}

[TestClass]
public class ProductServiceTests : IProductRepository
{
    private Product Product { get; set; }

    [TestMethod]
    public void WhenValidGetProductIsCalledProductIsReturned()
    {
        // arrange

        this.Product = new Product { ID = 1, Name = "Test product" };
        ProductService ps = new ProductService(this);

        // act

        Product p = ps.GetProduct(1);

        // assert

        Assert.AreSame(this.Product, p);
    }

    [TestMethod]
    public void WhenInvalidGetProductIsCalledProductIsReturned()
    {
        // arrange

        this.Product = new Product { ID = 1, Name = "Test product" };
        ProductService ps = new ProductService(this);

        // act

        Product p = ps.GetProduct(2);

        // assert

        Assert.IsNull(p);
    }

    public void Add(Product product)
    {
        throw new NotImplementedException();
    }

    public Product Get(int id)
    {
        if (this.Product.ID == id)
        {
            return this.Product;
        }
        else
        {
            return null;
        }
    }
}

      dr = dt.NewRow();
        dr["RowNumber"] = 1;
        dr["horsepType"] = string.Empty;
        dr["rblIssues"] = string.Empty;
        dr["vesseltypeUse"] = string.Empty;
        dt.Rows.Add(dr);
    }
    else
    {
        dt = (DataTable)ViewState["CurrentTable"];
    }
    //Store the DataTable in ViewState for future reference
    ViewState["CurrentTable"] = dt;
    if (dt.Rows.Count > 0)
    {
        //Bind the Repeater with the DataTable
        Repeater2.DataSource = dt;
        Repeater2.DataBind();
    }
}

Text='<%# string.IsNullOrEmpty(Eval("horsepType", "{0}")) ? null : Eval("horsepType") %>'