Wednesday, 12 March 2014

Import data from Excel To Datatble and Data table to SQL using asp.net

 public DataSet ExcelRead(string ExcelFilePath, string sheetName)
    {
        DataSet dataSet = new DataSet();
        try
        {
            string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""", ExcelFilePath);
            string query = String.Format("SELECT * FROM [{0}$]", sheetName);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
         
            dataAdapter.AcceptChangesDuringFill = true;
            dataAdapter.Fill(dataSet);
         
        }
        catch(Exception ex)
        {
            ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), "Message", "alert('please Enter Sheet Name Properly..')", true);
         
        }
        return dataSet;
    }
protected void UploadButton_Click(object sender, EventArgs e)
    {
DataSet ds5 = ExcelRead(fileName, txtSheetName.Text);
  DataTable dtExcel = ds5.Tables[0];

for (int i = 0; i < dtExcel.Rows.Count; i++)
                    {
                        try
                        {
                            count = count + 1;
                            if (BankName == "VIJAYA BANK")
                            {
                                val = P.ExcuteQuery("insert into Vijaya_Bank values('" + dtExcel.Rows[i][0] + "','" + dtExcel.Rows[i][1] + "','" + dtExcel.Rows[i][2] + "','" + dtExcel.Rows[i][3] + "','" + dtExcel.Rows[i][4] + "','" + dtExcel.Rows[i][5] + "','" + ddlBankName.SelectedItem + "','" + ddlAccountNumber.SelectedItem + "')");

                            }
}

No comments:

Post a Comment

SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

CREATE PROCEDURE [CSMSDVLP].[DiskSpaceMonitor] @mailProfile nvarchar(500), @mailto nvarchar(4000), @threshold INT, @logfile nvarchar(40...