How to Use Excel to Create SQL Query String

Have you ever faced some situations where your database table's data are get updated or changed?

You have a backup database. You know you can use a backup database table to update the previous table data. But it takes more time to write update statement for each row.

You can use excel file to do that task easily as follow.

1.0 Generate the script from backup database. For example, I need to update 'is billable' columns of administrator table.
I write this query to get back up data.

SELECT aid,aisBillable FROM Administrator

2.0 Copy and Paste those query result into new excel file.

3.0  Use below SQL update function in the first row of the excel sheet as in the below image.
SQL:
="UPDATE Administrator set aisBillable ="&B2&" WHERE aid="&A2

4.0 Drag the first row to bottom rows. Then SQL 'Update' statement is updated as in the image.

5.0 You can copy those update queries and run on the SQL server to get updated table data.

Summary
Without writing an update/delete queries for each row, you can use excel functionality to make a job easier.

Dot Net Bulk Insert

I have a project to get read CSV files and pushed those data to SQL table. CSV file contains more than 5000 records.

It is not good practice to insert record one by one SQL table due to it is a time-consuming process.

To optimize the process, Dot net has an ‘SQLBulkCopy’ feature to insert data at once to target table.

I like to share my experience how I used above feature.

1.0   I have read a CSV file and put them to object list.

2.0   For a bulk update, you need to convert object list to a data table.

Code for converting object list to the data table.

   public static DataTable ToDataTable<T>(List<T> items)
   {            DataTable dataTable = new DataTable(typeof(T).Name);
             //Get all the properties            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {                //Defining type of data column gives proper data table
                var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                //Setting column names as Property names                dataTable.Columns.Add(prop.Name, type);            }            foreach (T item in items)
            {                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {                    //inserting property values to datatable rows                    values[i] = Props[i].GetValue(item, null);
                }                dataTable.Rows.Add(values); 

 }     

     //put a breakpoint here and check datatable 

return dataTable;

 }

2.1 You can call above method as below.

  DataTable dt = new DataTable("MyTable");
  dt = BaseDao.ToDataTable<T>(items);

3.0   After you need to specify the destination table. if source table and destination table’s column fields a are not match then you need to specify mapping columns.

4.0   Completed code is below.

protected override void BulkInsertData<T>(List<T> items)
 {  string connectionString = BaseDao.ConnectionString;
             DataTable dt = new DataTable("MyTable");
            dt = BaseDao.ToDataTable<T>(items);
  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
  { 

bulkCopy.BulkCopyTimeout = 660;   

bulkCopy.DestinationTableName = "Workday_Employee";

// Set up the column mappings by name.                SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("Employee_ID", "Workday_Id");bulkCopy.ColumnMappings.Add(mapID);    

SqlBulkCopyColumnMapping mapID1 = new SqlBulkCopyColumnMapping("User_Name", "Username");bulkCopy.ColumnMappings.Add(mapID1);     

bulkCopy.WriteToServer(dt);

      bulkCopy.Close();         

}

Summary