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;
}
