Quick Solution of Type Conversion Excel To sqlBulkUpload

Introduction

This tip will show how we can use Excel VBA feature in C# for datatype conversion.

Background

Most of the time, while importing data from Excel To SQL Server datatype conversion arise. Generally, we convert those datatypes in C#. But VBA Type Conversion Functions can be applied in the query itself. Here are some of the functions.

CBool(expression)
CByte(expression)
CChar(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CObj(expression)
CSByte(expression)
CShort(expression)
CSng(expression)
CStr(expression)
CUInt(expression)
CULng(expression)
CUShort(expression)

And in some cases, we need to add extra columns that are not present in Excel and they are in the SQL table. In that case, the extra column can be added with null value in the query itself.

IIf(IsNull(Null),Null,Null) as EditorStatus //this will add extra col in search result as EditorStatus

Using the Code

string conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

                    System.Data.OleDb.OleDbConnection mCon=new System.Data.OleDb.OleDbConnection();

                conString = string.Format(conString, excelPath);
                mCon = new System.Data.OleDb.OleDbConnection();
                mCon.ConnectionString = conString;
               
                DataTable DTable = new DataTable();
                string strSelectQuery, mstrDBTable;
                System.Data.OleDb.OleDbDataAdapter DataAdapter = new System.Data.OleDb.OleDbDataAdapter();

strSelectQuery = "SELECT CLng(StyleId),Status,CDate(Date),IIf(IsNull(Null),Null,Null) FROM [Sheet1$]";
                // YourSheetName is the sheet in xls from where you want to load data e.g Sheet1$
                if (mCon.State == ConnectionState.Closed)
                {
                    mCon.Open();
                }
                DataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSelectQuery, mCon);
                DataAdapter.Fill(DTable);
                mCon.Close();
                using (SqlConnection con = new SqlConnection
                (ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.tblCD6";
                        con.Open();
                        sqlBulkCopy.WriteToServer(DTable);
                        con.Close();
                        
                    }
                }