This post is about the process used by Asp.Net MVC developers India to import excel data into SQL table in mvc application. In this article, the professionals have used MVC 4 app and SQL 12 data base table. You can follow the instruction shared by them and perform the same process at your place.
Today, I will explain how to import Excel data into SQL table in MVC application. To complete this document, I will use MVC 4 application and SQL-12 data base table.
First, create a table in SQL-2012 with the column name same as excel header. You do not have to worry about data type of column, you can set all to varchar date type because column data in excel are in string format, Or you can set column data type as data in excel e.g.: if you have column in excel with digits only, so you can set int data type for that column in DB table. Like Salary. Bit data type for Yes/No value in excel.
I created a table “TestAccountResult” with following structure.
Second, Create a simple MVC application and set one button on page, it will use for Import event.
Now, I want to use J-Query to call controller method. So, I created a java-script function. J-Query Ajax will call controller method which executes our Excel to SQL data import functionality.Call this java-script function from button or anchor link as below.
<ahref="Javascript:void(0);"id="btnImport"class="link"style="float: right; margin-right: 15px;"onclick="importAccounts()">Import Account</a>
Following is the Java-script Function.
<script>
functionimportAccounts() {
$.ajax({
url: '@Url.Action("ImportAccountsFromExcel")',
type: 'POST',
data: '',
contentType: 'application/json; charset=utf-8',
success: function (data) {
$('.divContent').html(data);
},
error: function (xmlHttpRequest, errorText, thrownError) {
alert(xmlHttpRequest + "|" + errorText + "|" + thrownError);
}
});
}
</script>
Following is the controller method which executes our Excel to SQL import task.
[HttpPost]
publicstringImportAccountsFromExcel()
{
stringstr = "";
try
{
List<string> fields = newList<string>();
DataTable _tb = newDataTable();
stringstrfile = "Your excel file path with file name where it locate on your machine";
stringstrProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
stringconnString = string.Format(strProvider, strfile);
varmyConnection = newOleDbConnection(connString);
varmyCommand = newOleDbDataAdapter("SELECT * FROM [AccountView$]", myConnection);
myCommand.Fill(_tb);
if (_tb.Rows.Count> 0)
{
stringconsString = ConfigurationManager.ConnectionStrings["SQLConnection"].ToString();
using (SqlConnection con = newSqlConnection(consString))
{
con.Open();
SqlCommandcmd = newSqlCommand("Truncate Table TestAccountResult", con);
cmd.ExecuteNonQuery();
con.Close();
using (SqlBulkCopysqlBulkCopy = newSqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.TestAccountResult";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
sqlBulkCopy.ColumnMappings.Add("Company", "Company");
sqlBulkCopy.ColumnMappings.Add("Address", "Address");
sqlBulkCopy.ColumnMappings.Add("City", "City");
sqlBulkCopy.ColumnMappings.Add("StateCode", "StateCode");
sqlBulkCopy.ColumnMappings.Add("ZipCode", "ZipCode");
sqlBulkCopy.ColumnMappings.Add("Phone1", "Phone1");
sqlBulkCopy.ColumnMappings.Add("Phone2", "Phone2");
sqlBulkCopy.ColumnMappings.Add("Email", "Email");
sqlBulkCopy.ColumnMappings.Add("WebSite", "WebSite");
con.Open();
sqlBulkCopy.WriteToServer(_tb);
con.Close();
}
}
}
str = "Data Import completed successfully.";
}
catch (Exception ex)
{
str = ex.Message;
}
returnstr;
}
You can see in above code the myCommand code line, which contains select statement. Here you have to provide the WorkSheet name of Excel file. In my case it is “AccountView”.
Also you can see the mapping of excel file and SQL data table in following way.
sqlBulkCopy.ColumnMappings.Add("WebSite", "WebSite");
Here Add method has two parameters. First is Source column name means here you have to give a column name same as excel sheet. And second is the Destination Column name means here you have to give a column name same as SQL db table column.
Finally, run the application and click on Import Account link. It will import data from Excel to SQL database table. See the following snap shots of excel and DB Table.
Asp.Net MVC developers India have intended this post to explain how they import excel into SQL in MVC application. They have also shared reference code and instances in the post. If you have not understood any point or any code, ask freely.
