Home  »  Code Snippets  »  ASP.Net  »

Upload Excel data and import into Sql Server Database using asp.net

 Posted By : Manoranjan SahooPosted Date : 14/05/2011 11:37:20 AM Category : ASP.Net Points : 5 (Rs 1.00)

First attach following namespaces into your page.

ex:

using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;


Then create the below method to get Excel data.


public DataTable GetDataFromExcel()
{
    DataTable dt = new DataTable();
    try
    {
        OleDbConnection oledbconn=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/ExcelFile.xls").ToString() + ";Extended Properties=Excel 8.0;");
        string SheetName="sheet1";//here enter sheet name
        oledbconn.Open();
        OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + SheetName + "$]", oledbconn);
        OleDbDataAdapter oledbda = new OleDbDataAdapter();
        oledbda.SelectCommand = cmdSelect;
        oledbda.Fill(dt);
        oledbconn.Close();
        oledbda = null;
       
    }
    catch (Exception ex)
    {
       Response.Write(ex.Message);
    }
    return dt;
}

Now in pageload or button click event type below code :

DataTable dt = GetDataFromExcel();
StringBuilder SqlQuery =new StringBuilder();
SqlQuery.Append("");
foreach (DataRow dr in dt.Rows)
{
    SqlQuery.Append("INSERT INTO TableName VALUES(" + dr["ID"].ToString() + ",'" + dr["Name"].ToString() + "')");
}
//Write code to insert the data into the database using the above query. if you have very large amount of data in excel file then you can also insert row one by one with in loop. otherwise you can insert all data in one execution.


Now use below method to update into sql server data base

public int UpdatedCommand(string strupdatequery)
{
    int intreturn = 0;
    try
    {
        SqlConnection sqlconn=new SqlConnection("Connection String for Sql Server");
        SqlTransaction transsql;
        SqlCommand cmdupdatecommand = new  SqlCommand();
        transsql = null;
        if (sqlconn.State != ConnectionState.Closed)
        {
            sqlconn.Close();
        }
        sqlconn.Open();
        transsql =sqlconn.BeginTransaction();
        cmdupdatecommand.CommandTimeout = 0;
        cmdupdatecommand.CommandText = strupdatequery;
        cmdupdatecommand.Connection =sqlconn;
        cmdupdatecommand.Transaction = transsql;
        intreturn = cmdupdatecommand.ExecuteNonQuery();
        transsql.Commit();
        if (sqlconn.State != ConnectionState.Closed)
        {
            sqlconn.Close();
        }
       
    }
    catch (SqlException oleex)
    {
        transsql.Rollback();
        Response.Write(ex.Message);
        intreturn= -3;
    }
    catch (System.Exception exp)
    {
        transsql.Rollback();
        Response.Write(ex.Message);
        intreturn= -3;
    }
    finally
    {
        if (sqlconn.State != ConnectionState.Closed)
        {
            sqlconn.Close();
        }
    }
    return intreturn;
}

Thank You.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.