Hi zoya,
I have created one sample that full-fill your requirement.
SQL
CREATE TABLE Customers
(
CustomerId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Country VARCHAR(50) NOT NULL
)
Form Controls
I have added a DataGridView, two Buttons one is for Select file and one is for Insert to Sql in the Windows Form. I have also added an OpenFileDialog to the Form which allow us to select the excel file.
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
Connection Strings to the Excel
Excel versions 97-2003 and Excel 2007 (and higher) use different providers I have declared two connection strings of which one uses Microsoft Jet Engine for older versions and the other one uses Microsoft Ace for newer versions.
private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
Selecting the Excel File
When the Select File button is clicked, it shows the Open File Dialog, using which we need to select the Excel file we want to import to DataGridView control.
C #
private void Select(object sender, EventArgs e)
{
ofdSelect.ShowDialog();
}
Importing the Excel File’s Sheet to DataGridView control
As soon as the Excel File is selected the following event handler is executed. Here first the path of the Excel file is determined and then its extension.
Using the extension of the Excel file, appropriate connection string variable is chosen and the connection string is built using the path of the file.
Next the name of the first sheet is determined and then the sheet data is read into a DataTable which ultimately is bound to the DataGridView control.
C #
private void ofdSelectOk(object sender, CancelEventArgs e)
{
string filePath = ofdSelect.FileName;
string extension = Path.GetExtension(filePath);
string conString = "";
string sheetName = "";
switch (extension)
{
case ".xls":
conString = string.Format(Excel03ConString, filePath, "YES");
break;
case ".xlsx":
conString = string.Format(Excel07ConString, filePath, "YES");
break;
}
using (OleDbConnection con = new OleDbConnection(conString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dt.Rows[0]["Table_Name"].ToString();
con.Close();
}
}
using (OleDbConnection con = new OleDbConnection(conString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
OleDbDataAdapter oda = new OleDbDataAdapter();
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
DataTable dt = new DataTable();
oda.Fill(dt);
con.Close();
dgvCustomer.DataSource = dt;
}
}
}
Inserting bulk data from GridView to SQL Server database table in ASP.Net
The following event handler is executed on the click of the Button. Here first a DataTable is created with column schema same as that of the destination SQL Server database table and then a loop is executed of the GridView rows.
Now a connection is established with the database and the SqlBulkCopy object is initialized and the name of the Table is specified using the DestinationTableName property.
Finally the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
C #
private void InsertRecord(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 (DataGridViewRow row in dgvCustomer.Rows)
{
int customerId = Convert.ToInt32(row.Cells[0].Value);
string name = row.Cells[1].Value.ToString();
string country = row.Cells[2].Value.ToString();
dt.Rows.Add(customerId, name, country);
}
if (dt.Rows.Count > 0)
{
string str = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(str))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Customers";
sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}
Screenshot
