Hi asrul,
I have created sample that full fill your requirement.
SQL
I have made use of the following table Customers as follows.
CREATE TABLE Customers(
CustomerId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Country VARCHAR(50) NOT NULL
)
GO
Then I have created a User Defined Table Type in SQL Server using the following query.
CREATE TYPE [dbo].[CustomerType] AS TABLE(
[Id] [int] NULL,
[Name] [varchar](100) NULL,
[Country] [varchar](50) NULL
)
GO
SQL Server 2008 came up with a nice function called MERGE, which allows to perform INSERT operation when records are not present and UPDATE when records are present in the table.
CREATE PROCEDURE [dbo].[Update_Customers]
@tblCustomers CustomerType READONLY
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO Customers c1
USING @tblCustomers c2
ON c1.CustomerId=c2.Id
WHEN MATCHED THEN
UPDATE SET c1.Name = c2.Name
,c1.Country = c2.Country
WHEN NOT MATCHED THEN
INSERT VALUES(c2.Id, c2.Name, c2.Country);
END
The below Stored Procedure can be used where in the SQL Server version 2005 where MERGE function is not supported. It first performs UPDATE using INNER JOIN and then INSERTS all records that are not present in the table.
CREATE PROCEDURE [dbo].[Update_Customers]
@tblCustomers CustomerType READONLY
AS
BEGIN
SET NOCOUNT ON;
--UPDATE EXISTING RECORDS
UPDATE Customers
SET Name = c2.Name
,Country = c2.Country
FROM Customers c1
INNER JOIN @tblCustomers c2
ON c1.CustomerId = c2.Id
--INSERT NON-EXISTING RECORDS
INSERT INTO Customers
SELECT Id, Name, Country
FROM @tblCustomers
WHERE Id NOT IN(SELECT CustomerId FROM Customers)
END
ConnectionString
<add name="constr" connectionString="Server=.;DataBase=Sample;UID=sa;PWD=xxxxx" providerName="System.Data.SqlClient"/>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
Namespaces C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
Namespaces VB
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
HTML
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Visible="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Id" ItemStyle-Width="30" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<table>
<tr>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td>
<asp:Button ID="btnUpload" Text="Upload" OnClick="Upload" runat="server" />
</td>
<td>
<asp:Button ID="btnUpdate" Text="Update" OnClick="Update" runat="server" />
</td>
<td>
<asp:Label ID="lblMessage" runat="server" ForeColor="Red" />
</td>
</tr>
</table>
</div>
Code C#
protected void Upload(object sender, EventArgs e)
{
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("CustomerId", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
GridView1.DataSource = dtExcelData;
GridView1.DataBind();
}
excel_con.Close();
lblMessage.Text = "File uploaded successfully, Now you can update the record.";
}
}
protected void Update(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("CustomerId", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
foreach (GridViewRow row in GridView1.Rows)
{
int id = int.Parse(row.Cells[0].Text);
string name = row.Cells[1].Text;
string country = row.Cells[2].Text;
dt.Rows.Add(id, name, country);
}
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Update_Customers"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblCustomers", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
Code VB
Protected Sub Upload(sender As Object, e As EventArgs)
Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)
Dim conString As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
Case ".xls"
'Excel 97-03
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
Exit Select
End Select
conString = String.Format(conString, excelPath)
Using excel_con As New OleDbConnection(conString)
excel_con.Open()
Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
Dim dtExcelData As New DataTable()
dtExcelData.Columns.AddRange(New DataColumn(2) {New DataColumn("CustomerId", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
oda.Fill(dtExcelData)
GridView1.DataSource = dtExcelData
GridView1.DataBind()
End Using
excel_con.Close()
lblMessage.Text = "File uploaded successfully, Now You can update the record."
End Using
End Sub
Protected Sub Update(sender As Object, e As EventArgs)
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("CustomerId", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
For Each row As GridViewRow In GridView1.Rows
Dim id As Integer = Integer.Parse(row.Cells(0).Text)
Dim name As String = row.Cells(1).Text
Dim country As String = row.Cells(2).Text
dt.Rows.Add(id, name, country)
Next
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("Update_Customers")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.AddWithValue("@tblCustomers", dt)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
Screenshot
Before
After
