Archive

Archive for the ‘SQL’ Category

SQLBulkCopy

May 1, 2009 1 comment

I was looking to copy some bulk data from one of the data table in memory to my SQL DB table. I tried using DBAdapter.Update but it didn’t work as in my source datatable none of the row was in updated/new stage. Then after searching a lot, I found “SQLBulkCopy”.

The SqlBulkCopy class can be used to write bulk data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. To copy the source data to destination table in the sql server, need to set destination table name and execute “WriteToServer” method. Following code snippet

// create sqlBulkCopy object
SqlBulkCopy bulkCopy = new SqlBulkCopy(connString);

// set destination table name for sqlBulkcopy object
bulkCopy.DestinationTableName = "[Table Name]";

// write source data to destination sql table
bulkCopy.WriteToServer();

Performancewise using SQLBulkCopy is far better than using normal Insert/Update commands.

Categories: c#, SQL Tags: , ,

Reading csv file in datatable

May 1, 2009 10 comments

Recently I had to work on one problem where I had to read the csv file and filling up the data table with the csv file data. I used the OLEDBAdapter to accomplish that with just few lines of code. Following is the code :

            string query = "SELECT Symbol, [Name of Company], FROM [just file name with extension]";
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + [csv file path without file name] + ";" + "Extended Properties=’text;HDR=YES;’";

            //create dataadapter object
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connStr);

            // create table
            DataTable dtSymbolDetails = new DataTable("ScriptDetails");
            dtSymbolDetails.Columns.Add("Symbol");
            dtSymbolDetails.Columns.Add("Name of Company");

            // fill the table with data using adapter
            adapter.Fill(dtDetails);

So above code fills up the details of the csv file in the data table for further use.

Also note the text in red above. If column name contains the space then in query the column name should be put between [ ]. I learned it in bit hard way. It shows I’m so poor in my sql knowledge.

Categories: c#, SQL Tags: , ,