Wednesday, March 12, 2014

Update SQL Server Database table data via Excel Sheet using C# asp.net or Windows application

Hi Friends, Today I will be explain that how can we update data in SQL Server database table via Excel sheet  using C# asp.net or windows application.

First, we will be adding a new webpage test.aspx to our visual studio and put a two server control file upload and a button. We will browse an excel sheet file to this file upload and on button click event data will be update in sqlserver database table as our requirement and our data.

Step 1- In step first we will be design our database, table and store procedure.
CREATE database fileupload
CREATE TABLE [dbo].[tbl_Subject_Matser](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [subjectname] [nvarchar](50) NOT NULL,
     
 CONSTRAINT [PK_Subjectmatser] PRIMARY KEY CLUSTERED
(
      [subjectname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 And store procedure is
create PROCEDURE [dbo].[FileImport]
@Subject nvarchar(50)='',
@id int=0
as begin
update dbo.tbl_Subject_Matser set subjectname=@Subject where Id=@id

END
Step 2-this is the code for test.aspx page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div><fieldset><legend>Update Data in SqlServer via Excel Sheet</legend>
            <asp:FileUpload ID="fuCSV" runat="server"></asp:FileUpload>

            <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Import/Insert" />
            <asp:Label runat="server" ID="lblerror"></asp:Label></fieldset>
        </div>
    </form>
</body>
</html>

Step 3-These are the some namespace, that will be used-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

//This is the button click event…………..
protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (fuCSV.HasFile)
        {
            string filepath = Request.PhysicalApplicationPath;
            fuCSV.PostedFile.SaveAs(filepath + "\\File.xlsx");
            DataSet ds = new DataSet();
            string strError = string.Empty;
            ds = GetExcelDataSet(filepath + "\\File.xlsx");

            if (ds != null && ds.Tables.Count > 0)
            {
                bool result = true;
                int ColumSize = ds.Tables[0].Columns.Count;
                DateTime dtCurrent = DateTime.Now;
                for (int rowIndex = 0; rowIndex < ds.Tables[0].Rows.Count; rowIndex++)
                {
                    ArrayList arr3 = new ArrayList();
                    for (int colIndex = 0; colIndex < ColumSize; colIndex++)
                    {
                        arr3.Add(ds.Tables[0].Rows[rowIndex].ItemArray[colIndex].ToString());
                    }
                    if (GetInsert(arr3, "FileImport", ref strError))
                    {
                        lblerror.Text = "File Uploaded Successfully";
                    }
                }
            }
        }
    }

    public DataSet GetExcelDataSet(string filename)
    {
        //This is Provider for normal Excel file 2003
        //OleDbConnection OleDbcnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'");

        //This is Provider for normal Excel file 2007
        OleDbConnection OleDbcnn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'");
        try
        {
            DataSet ds = new DataSet();
            OleDbCommand oledbCmd;
            DataTable dt = new DataTable();
            ds = new DataSet();
            if (OleDbcnn.State == ConnectionState.Open)
            {
                OleDbcnn.Close();
            }
            //Open OLEDB connection
            OleDbcnn.Open();
            dt = OleDbcnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int index = 0; dt.Rows.Count > index; index++)
            {
                if (Convert.ToString(dt.Rows[index].ItemArray[2]).ToLower() == "sheet1$")
                {
                    oledbCmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcnn);
                    OleDbDataAdapter oledbDA = new OleDbDataAdapter(oledbCmd);
                    oledbDA.Fill(ds);//Fill data into dataset row by row
                    break;
                }
            }

            OleDbcnn.Close();
            return ds;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (OleDbcnn.State == ConnectionState.Open)
            {
                OleDbcnn.Close();
            }
        }
    }

    public bool GetInsert(ArrayList arr, string strProcName, ref string err)
    {
        bool result = false;
        string strcol = "";
        SqlConnection con = new SqlConnection("Data Source=SANTOSH; Initial Catalog=fileupload;User ID=”””; Password=””;");
        
        try
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();

            SqlCommand strcmmand = new SqlCommand(strProcName, con);
            strcmmand.CommandType = CommandType.StoredProcedure;

            Guid idUser = Guid.NewGuid();
            //strcmmand.Parameters.Add(new SqlParameter("@UserID", idUser));
            strcmmand.Parameters.Add(new SqlParameter("@subject", arr[1].ToString().Trim()));
            strcmmand.Parameters.Add(new SqlParameter("@id", arr[2].ToString().Trim()));

            strcmmand.ExecuteNonQuery();
            con.Close();
            result = true;
        }
        catch (Exception e)
        {
            err = err + e.Message + "<br/><br/><b>Column</b> " + strcol + "<br/>";
            result = false;

        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        return result;

    }


This is snapshot for this post

No comments:

Post a Comment