Import Excel Data Into SQL Server Using Asp Net C#

In this article, I will show you how to Import Excel Data Into SQL Server Using Asp Net C#.
Let’s take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of data broken into two columns, ID and Data. I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema. Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:


public void BulkCopy()
{
	// Connection String to Excel Workbook
	string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";

	// Create Connection to Excel Workbook
	using (OleDbConnection connection =
	new OleDbConnection(excelConnectionString))
	{
		OleDbCommand command = new OleDbCommand
		("Select ID,Data FROM [Data$]", connection);

		connection.Open();

		// Create DbDataReader to Data Worksheet
		using (DbDataReader dr = command.ExecuteReader())
		{
			// SQL Server Connection String
			string sqlConnectionString = "Data Source=.;
		Initial Catalog = Test; Integrated Security = True";
		
// Bulk Copy to SQL Server
			using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
			{
				bulkCopy.DestinationTableName = "ExcelData";
				bulkCopy.WriteToServer(dr);
			}
		}
	}
	```

Post a Comment

Please do not post any spam link in the comment box😊

Previous Post Next Post

Blog ads

CodeGuru