When we insert data into database from excel file
mostly we tend to use one by one reading approach. But if records are more then
it will take more time for insertion. So, to avoid
that we can import excel data as data table into Database which consumes very
less time.
How we can achieve this?
1. Make SQL Type
•
Create an SQL Type for storing bulk data into SQL
table having columns same as column in excel.
•
Make sure data types which we are passing should be
same as SQL table data type. This is
more important think in making SQL type for bulk insertion
The Process
for the above activities as follows:
CREATE TYPE [dbo].[TYPE NAME] AS TABLE
(
[Column1]
<Data Type> NULL,
[Column2]
<Data Type> NULL,
[Column3]
<Data Type> NULL,
[Column4]
<Data Type> NULL,
[Column5]
<Data Type> NULL,
[Column6] <Data
Type> NULL,
[Column7] <Data
Type> NULL,
[Column8] <Data
Type> NULL,
[Column9] <Data
Type> NULL,
.
.
.
.
.
.
.
[Column'N']
<Data Type> NULL
)
2. Make Store Procedure for bulk
insertion
Create store procedure which actually inserts bulk
data into table in which we can pass the data table as SQL Type which we
created above and do the bulk insertion in table.
The Process for the above activities as follows:
CREATE PROCEDURE [dbo].[<Procedure Name>]
(
@DataTable
AS dbo.<Type Name> READONLY
)
AS
/* SET NOCOUNT ON */
INSERT INTO <Table Name>
(
[Column1],[Column2],[Column3],[Column4],[Column5],
................,
[Column'N']
)
SELECT
[Column1],[Column2],[Column3],[Column4],[Column5],
................,
[Column'N']
From @DataTable
3. Get data from excel and pass this data to Store procedure as data table.
•
Open the connection string for excel and read the
data from excels.
•
Pass this data as data tables to store procedure, Column
names (header) from excel are must be same as column in data Table
and SQL Table.
The Process for the above activities as follows:
string xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" +<excel file path>+
";" + "Extended
Properties=Excel 12.0;";
OleDbDataAdapter
oda;
using (OleDbConnection connection = new OleDbConnection(xConnStr))
{
connection.Open();
oda = new OleDbDataAdapter
(
"Select Column1, Column2,Column3,Column4,Column5,....,Column'N'
FROM [SheetName$] where <Condition>", connection
);
DataTable tblName = new
DataTable();
Application.DoEvents();
oda.Fill(tblName);
connection.Close();
SqlConnection
con1 = new
SqlConnection((ConfigurationManager.ConnectionStrings
["<Conn
String>"]).ToString());
con1.Open();
Application.DoEvents();
cmd1 = new SqlCommand("<SPNameForInsertion>",
con1);
cmd1.CommandType = CommandType.StoredProcedure;
SqlParameter
tvparam =
cmd1.Parameters.AddWithValue("@dt",
tblName);
tvparam.SqlDbType = SqlDbType.Structured;
cmd1.CommandTimeout = 0;
cmd1.ExecuteNonQuery();
}
About Author
Vishal Kudale works with Systems Plus and is working on Dot Net technology projects.
He can be contacted at: vishal.kudale@spluspl.com
useful steps to deal with bulk insertion from excel to sql DB.
ReplyDelete