Home  »  Articles  »  ASP.Net  »

Import Data from Excel to SQL Server using ASP.Net

 Posted By : Manoranjan SahooPosted Date : 30/05/2011 01:53:40 PM Category : ASP.Net Points : 8 (Rs 3.00)

Here I am going to show you the step by step process to import data from Excel to SQL Server. To do so follow the below steps :

Step 1 :

Create a sample table:

GO
CREATE TABLE [dbo].[Product_Master](
    [Product_Code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Product_Name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Price] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Step 2 :

Create New Web site with new webpage named as Default.aspx (Following is the code contained in the Default.aspx page).

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Import Excel Data To SQL Server</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblHeading" runat="server" Text="Select File To Upload : "></asp:Label>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <br />
        <br />
        <asp:Button ID="btnUpload" runat="server" Text="Upload"
            onclick="btnUpload_Click" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:Button ID="btnSaveToDB" runat="server" Text="Save To DB"
            onclick="btnSaveToDB_Click" />
            <br /><br />
        <asp:Label ID="lblerror" runat="server" ForeColor ="Red" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>

Step 3 :
Here is the C# code of the Default.aspx.cs file:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;

public partial class _Default : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            var uploadFile = new UploadFile(FileUpload1.FileName);
            try
            {
                FileUpload1.SaveAs(uploadFile.SavePath);
               
            }
            catch (Exception ex)
            {
                lblerror.Text = "error : "+ex.Message;
            }
         }
    }
    protected void btnSaveToDB_Click(object sender, EventArgs e)
    {
        ImportDataIntoDB idid = new ImportDataIntoDB();
        string count = idid.saveDataToDB();
        if (Convert.ToInt16(count) < 0)
        {
            lblerror.Text = "Error Occurred...";
        }
        UploadFile upFile = new UploadFile();
        upFile.DeleteFileNoException();
    }
   
}

In this i call the UploadFile class which is created in a separate class file. This class is used to store uploaded file and also used to generate OleDb connection string as per file selected. Here is the code of UploadFile Class :

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;

/// <summary>
/// Summary description for UploadFile
/// </summary>
public class UploadFile
{
    private string savePath;
    public UploadFile()
    {

    }
    public UploadFile(string originalFileName)
    {
        string tempFileName = Guid.NewGuid().ToString("N") + "_" + originalFileName;
        string saveLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileUploadLocation"]);
        savePath = Path.Combine(saveLocation, tempFileName);
        HttpContext.Current.Session["savePath"] = savePath;
    }
    /// <summary>
    /// Temp path used to save the uploaded file
    /// </summary>
    public string SavePath
    {
        get
        {
            return savePath;
        }
    }
    /// <summary>
    /// Attempt to delete temp file
    /// </summary>
    public void DeleteFileNoException()
    {
        savePath = HttpContext.Current.Session["savePath"].ToString();
        if (File.Exists(savePath))
        {
            try
            {
                File.Delete(savePath);
            }
            catch { }
        }
    }

    /// <summary>
    /// Return connection strinng based on file extension
    /// </summary>
    public string GetOleDbConnectionString()
    {
        savePath = HttpContext.Current.Session["savePath"].ToString();
        var finfo = new FileInfo(savePath);

        if (!finfo.Exists)
        {
            throw new FileNotFoundException(savePath);
        }

        var fileExtension = finfo.Extension.ToLower();
        switch (fileExtension)
        {
            case ".xls":
                return string.Format(ConfigurationManager.AppSettings["xlsOleDBConnection"], savePath);
            case ".xlsx":
                return string.Format(ConfigurationManager.AppSettings["xlsxOleDBConnection"], savePath);
            default:
                throw new NotSupportedException(String.Format("This file type {0} is not supported!", fileExtension));
        }
    }
}

Now next step is read that file and save the data into sql server.
Here is the method which reads the data from excel and save it into sql server database :

    public string saveDataToDB()
    {
        int j = 0;
        string strQuery = "SELECT ProductCode,ProductName,ItemCaptionJP,ItemTextJP,IngredientsJP,CountryOfOrigin,HowToStore FROM [Sheet1$]";
        DataTable dt = new DataTable();
        OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);
        //OleDbDataAdapter da = new OleDbDataAdapter(strQuery, oleconn);
        oleconn.Open();
        OleDbDataReader odr = ocmd.ExecuteReader();
        int i = 0;
        if (odr.HasRows)
        {
            while (odr.Read())
            {
                if (i == 0)
                    sb.Append("UPDATE Product_Master set [itemCaption_jp]=N'" + odr["ItemCaptionJP"] + "',[itemText_jp]=N'" + odr["ItemTextJP"].ToString() + "',[ingredients_jp]=N'" + odr["IngredientsJP"].ToString() + "',[countryoforigin_jp]=N'" + odr["CountryOfOrigin"].ToString() + "',[howtostore_jp]=N'" + odr["HowToStore"].ToString() + "' where [alternate_code]='" + odr["ProductCode"].ToString() + "' and Product_Name='" + odr["ProductName"].ToString() + "'");
                else
                    sb.Append(";UPDATE Product_Master set [itemCaption_jp]=N'" + odr["ItemCaptionJP"] + "',[itemText_jp]=N'" + odr["ItemTextJP"].ToString() + "',[ingredients_jp]=N'" + odr["IngredientsJP"].ToString() + "',[countryoforigin_jp]=N'" + odr["CountryOfOrigin"].ToString() + "',[howtostore_jp]=N'" + odr["HowToStore"].ToString() + "' where [alternate_code]='" + odr["ProductCode"].ToString() + "' and Product_Name='" + odr["ProductName"].ToString() + "'");
                i++;
            }
            j = UpdatedCommand(sb.ToString());
        }
        return j.ToString();
    }
    #region update,delete,insert data in Database
    public int UpdatedCommand(string strupdatequery)
    {
        int intreturn = 0;
        ClientScriptManager cs = Page.ClientScript;
        Type ty = this.GetType();
        try
        {
            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();

            }
            return intreturn;

        }
        catch (SqlException oleex)
        {
            transsql.Rollback();
            strupdatequery = oleex.Message;
            strerorrmsg1 = "Error In UpdatedCommand method " + oleex.Message.ToString();
            strerorrmsg2 = strerorrmsg1.Replace("'", "");
            string msg = "alert('" + strerorrmsg2 + "');";
            cs.RegisterStartupScript(ty, strerorrmsg1, msg, true);


            return -3;
        }
        catch (System.Exception exp)
        {
            transsql.Rollback();
            strupdatequery = exp.Message;
            strerorrmsg1 = "Error In UpdatedCommand method " + exp.Message.ToString();
            strerorrmsg2 = strerorrmsg1.Replace("'", "");
            string msg = "alert('" + strerorrmsg2 + "');";
            cs.RegisterStartupScript(ty, strerorrmsg1, msg, true);
            return -3;


        }
        finally
        {
            if (sqlconn.State != ConnectionState.Closed)
            {
                sqlconn.Close();

            }

        }
    }
    #endregion 

For complete source code download from below links :

1. http://uploading.com/files/916m9479/ReadExcelFile.zip/

2. http://www.filesonic.com/file/1605889371/ReadExcelFile.zip

3. http://www.fileserve.com/file/zZAvP9M

Thank You.

Responses
 Author : prakash pradeep Member Level : BronzeDate : 13/04/2012 08:33:53 PM Points : 2

Hi All,

If you want see like this example please visit my blog :

http://moretoc.blogspot.in/2012/03/importing-excel-data-into-sql-server.html

 



Post Reply
Login to post Response.