Loading excel sheet (Workbook) data to SQL database using ASP.NET C#

This will describe about loading data from excel sheet (workbook) to SQL database from an asp.net application. It will also provide solutions to all the queries related to exporting excel to SQL, using bulk copy, loading excel data to SQL database etc.

There are many ways to load excel sheet (workbook) data to SQL database. Here I am describing about using bulk copy and data reader. Before proceeding further I would like to mention that we need to insert (define) name to rows and columns of excel sheet that contains the actual data. The first row will be treated as names of columns and rest as data.

Now loading excel sheet (workbook) data to SQL database using C#. Let we have Book1.xls excel file that has our data and we need to load this data to tbl_Emp_details table of our SQL database. Let the excel sheet has five columns i.e. name, sex, age, address and qualification. Also our SQL database has same columns along with ID column (primary key). Please note that if you are using bulk copy command then the column sequence of excel sheet and SQL data table must be same. If you are using primary key column (Identity column) then make this column the last column of the table because the bulk copy client will insert first column data of data reader into the first column of SQL data table and so on. If you have defined the first column as Identity column (primary key) then the data of first column of excel sheet will be skipped and the data of remaining columns will be inserted in the sequence of data reader columns. Before loading the excel file data do these things:

1. Select all rows and columns of excel sheet and click on ‘Insert’ menu and select ‘Names’ then ‘Define’.
2. In the popup window define (type) name of table (e.g. myExcelTable) in ‘Names in workBook’ and click ‘OK’.

We define name (Insere Name) is because the selected rows and columns will be treated as a table and we can execute select command on it. Now the defined name will be the name of the table and the first row of excel sheet will be treated as the names of columns and remaining rows as data. If you do not define name then you will get the error as “The Microsoft Jet database engine could not find the object 'myExcelTable'. Make sure the object exists and that you spell its name and the path name correctly.”

Define connection string in webconfig file for SQL database (In example it is for express database)

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=DataDirectory\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>

Now add references of Oledb and SqlClient to the code behind file.

using System.Data.OleDb;
using System.Data.SqlClient;


Now define a string variable to store connection string for Oledb connection and assign it the proper string. Define another string variable and assign sql connection string (connection string defined in webconfig file) to it. Now initialize a new Oledb connection using Oledb Connection string and open it. Define Oledb command and initialize it with select command. The table name in select command will be the name that we had defined (Inserted) by selecting rows and columns of excel sheet. Note: First row of excel sheet will be treated as the names of columns and we will select these columns only in our select command.
Now using data reader and bulk copy command we will insert data into SQL database.


//drive conniction string from webconfig file
string SQLconnStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
//define connectionstring and excel file name for Oledb database engine
string OledbConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/Book1.xls;" + "Extended Properties=Excel 8.0;";
//Oledb connection
OleDbConnection myOLEDBConn = new OleDbConnection(OledbConStr);
try
{

myOLEDBConn.Open();
//select data (rows) from excel sheet (workbook)
OleDbCommand myOLEDBCmnd = new OleDbCommand("select name, sex, age, address, qualification from myExcelTable", myOLEDBConn);

using (OleDbDataReader dbRdr = myOLEDBCmnd.ExecuteReader())
{
//save to sql database
using (SqlBulkCopy mysqlblkcpy = new SqlBulkCopy(SQLconnStr))
{
//define destination table name
mysqlblkcpy.DestinationTableName = "tbl_Emp_Details";
//define batch size in order to make operation easy
mysqlblkcpy.BatchSize = 500;
mysqlblkcpy.WriteToServer(dbRdr);
}
}


}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}



If we want more reliable method to load excel data to SQL database then we should use simple insert command instead of bulk copy command.

Note: If you receive the error: "Could not find installable ISAM." It means that your Oledb Connection string is not correct. Either you have misspell something or
Extended Properties=Excel 8.0; version is incorrect. Try the version 11.0 in place of 8.0 or correct spellings.

What to do when error like “ System.Data.SqlClient.SqlException: Failed to update database "C:\....\ASPNETDB.MDF" because the database is read-only"

What to do when error comes like “Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\....\APP_DATA\ASPNETDB.MDF" because the database is read-only”
or
“Exception Details: System.Data.SqlClient.SqlException: Failed to save to database "C:\INETPUB\WWWROOT\....\APP_DATA\ASPNETDB.MDF" because the database is read-only”

“Database is read-only” types of errors are common errors encountered when hosting the website on IIS with MS SQL Express database. These types of errors may occur when you have the NTFS file system and ASPNET user account does not have proper access permissions. If you do not provide write permissions to ASPNET user account to the hosted directory this error will occur. Simply add ASPNET user account to permission list to the directory and assign write permissions to this account. There are many ways to assign permissions and overcome this problem.

First do not host (place) your website to wwwroot directory. Either save it to the root of primary drive (C:\) or host it some where else because sometimes default access permissions do not provide ASPNET user account the write permissions. If you are hosting your website to wwwroot directory then make sure that ASPNET user account has write permission to App_Data Directory or .MDF file. You can check this by right clicking on App_Data directory and then selecting security tab. If you are able to see ASPNET user account in “Group or user names” then click on it and make sure that ‘write check box’ is checked (selected) in “permissions for ASPNET”. If it is not checked (selected) then check(select) it. If you do not find ASPNET user account into “Groups or user names” then add it and give it write permissions.

Second if you are hosting your website to any directory other than wwwroot then give ASPNET user account write permissions to website directory or App_Data directory as follow:

1. Right clicking on App_Data directory and then selecting security tab.
2. If you are able to see ASPNET user account in “Group or user names” then click on it and make sure that ‘write check box’ is checked (selected) in “permissions for ASPNET”. If it is not checked (selected) then check(select) it.
3. If you ado not find ASPNET user account into “Groups or user names” then click on ‘Add’.
4. In ‘Select Users or Groups’ windows click on ‘Advance’.
5. Now click on ‘Find Now’.
6. Here you will see the list of users. Now select ASPNET and click on ‘Ok’ to close third window and click again on ‘Ok’ to close second window (Select Users or Group).
7. Now click on ASPNET user account and check (select) write check box and click on ‘Apply’ and then ‘Ok’.

Now you have enough permission to your ASPNET user account to write or update Express Database. You can run your website now.

Third, you can place your website in shared folder but the access (write) permissions to ASPNET user account are required.

Thus you can overcome these types of errors.
If you still have problem, Please feel free to seek help.

To turn on the security tab in folder properties please follow the process:

. Click 'Tools' menu of explorer and select 'Folder Options'
. Select 'View' tab and uncheck (remove) the option 'Use simple file sharing (Recommended)' from the 'Advanced Options'. This option is the last in the list.