After developing an web application when you ask the operator to enter all existing data into the input interface then most of the cases the operator told you i have an Excel sheet please upload those data into the database which will reduce my time as well as human errors. This is the most common scenario. So you have to develop an interface where user can select an excel sheet to import into the SQL SERVER database.
Fortunately ADO.NET 2.0 provide us a new feature named SqlBulkCopy which gives you DTS like speed to transfer Excel sheet data into the SQL SERVER table. Here i will show you by an example how you can easily do this. At first open the Excel Sheet & determine which columns you want to transfer then if you have already a table then you need to map columns between Excel & SQL SERVER. Other create a table in your database as like as Excel Sheet. The following image shows you data structure & sample data of both SQL SERVER and Excel Sheet:
Now add an aspx page into your project. Add a button to run the import command. Let the button name is cmdImport. Now under button click event write the following code:
Don't forget to import the following two namespaces:
using System.Data.SqlClient;
using System.Data.OleDb;
Fortunately ADO.NET 2.0 provide us a new feature named SqlBulkCopy which gives you DTS like speed to transfer Excel sheet data into the SQL SERVER table. Here i will show you by an example how you can easily do this. At first open the Excel Sheet & determine which columns you want to transfer then if you have already a table then you need to map columns between Excel & SQL SERVER. Other create a table in your database as like as Excel Sheet. The following image shows you data structure & sample data of both SQL SERVER and Excel Sheet:
Now add an aspx page into your project. Add a button to run the import command. Let the button name is cmdImport. Now under button click event write the following code:
Don't forget to import the following two namespaces:
using System.Data.SqlClient;
using System.Data.OleDb;
protected void cmdImport_Click(object sender, EventArgs e)
{string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);using (sSourceConnection)
{string sql = string.Format("Select [MSISDN],[Name],[Company],[Status] FROM [{0}]", "Sheet1$");OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();using (OleDbDataReader dr = command.ExecuteReader())
{using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "tblAgent";//You can mannualy set the column mapping by the following way.//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");bulkCopy.WriteToServer(dr);
}
}
}
}
{string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);using (sSourceConnection)
{string sql = string.Format("Select [MSISDN],[Name],[Company],[Status] FROM [{0}]", "Sheet1$");OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();using (OleDbDataReader dr = command.ExecuteReader())
{using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "tblAgent";//You can mannualy set the column mapping by the following way.//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");bulkCopy.WriteToServer(dr);
}
}
}
}
Now run the application & see the performence that how fast your application import all data into the databse. One another thing if you want to try another sheet to import then run the above code by modifying the sheet name from the sql.
Sample Output:
Posts
1 comments:
Brother you are copying my full content... Why brother?
Post a Comment