Introduction
This article mainly describes how to import data from an Excel sheet to the SQL Server, viewing the data from SQL Server using the Gridview control, and deleting data. In case the Excel sheet does not contain data (null values), those values are replaced by zero.........
Using the Code
Here I have created a step by step process to import data from Excel to SQL Server and replace the null values with zeros and store them in the SQL Server database.
Given below is a step-by-step process:
Step 1
Create a sample table:
create table emp
(
sno int identity,
fname nvarchar(20),
lname nvarchar(20),
mobnum nvarchar(15),
city nvarchar(20),
state nvarchar(30),
zip int
)
Step 2
Take a Web page and name it as Excel2sql.aspx (Following is the code contained in the Excel2sql.aspx page).
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel2Sql.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 id="Head1" runat="server">
<title>:: Importing Data From Excel Sheet to SQL Server ::</title>
<style type="text/css">
.style1
{
width: 50%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" class="style1">
<tr>
<td align="center">
<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">
Insert Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">
View Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">
Delete Data</asp:LinkButton>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Step 3
Here is the C# code in the Excel2sql.aspx.cs file:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
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;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void insertdata_Click(object sender, EventArgs e)
{
OleDbConnection oconn = new OleDbConnection
(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("example.xls") + ";
Extended Properties=Excel 8.0");
try
{
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string fname = "";
string lname = "";
string mobnum = "";
string city = "";
string state = "";
string zip = "";
while (odr.Read())
{
fname = valid(odr, 0);
lname = valid(odr, 1);
mobnum = valid(odr, 2);
city = valid(odr, 3);
state = valid(odr, 4);
zip = valid(odr, 5);
insertdataintosql(fname, lname, mobnum, city, state, zip);
}
oconn.Close();
}
catch (DataException ee)
{
lblmsg.Text = ee.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Inserted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
protected string valid(OleDbDataReader myreader, int stval)
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
protected void viewdata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
try
{
SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (DataException de)
{
lblmsg.Text = de.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Shown Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
public void insertdataintosql(string fname, string lname,
string mobnum, string city, string state, string zip)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip)
values(@fname,@lname,@mobnum,@city,@state,@zip)";
cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void deletedata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "delete from emp";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteScalar();
conn.Close();
}
catch (DataException de1)
{
lblmsg.Text = de1.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Deleted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}
}
Points of Interest
- Using the
OledbConnection to connect to the Excel Sheet - The Connection String used to connect to the Excel sheet
- Selecting the data from the Excel file
- Replacing the
null values with zeros from the Excel sheet and inserting into the SQL Server - Viewing and deleting the data
History
- 16th January, 2009: Initial post
