.Net - Inserting large number of rows programatically into an SQL server database through SqlBulkCopy

by Vishal 10. July 2008 01:50

Often there is a need to send a very large number of rows from business code to the database. There are a number of ways to do it:

  1. Call insert statements a row at a time for the entire data
  2. Serialize the data into some flat format (CSV or XML), send it to a stored procedure as a large string, unserialize the string in the stored procedure TSQL and do an insert.
  3. Save the data into a flat file format on the database server. Run a DTS package or like to read up the file.
  4. SqlBulkCopy!

Every since I discovered SqlBulkCopy, I’ve loved it. MS SQL Server includes a popular command called bcp for moving data from one table to another on one server or between servers. The SqlBulkCopy is a class that provides similar functionality.

SqlBulkCopy is way faster than multiple insert statements, serializing/ deserializing the data, or saving the data out to a file system and running an import.  Its also has no limit on the data you can send across and very efficient in the way it handles inserts.

This is how simple it is to use it. In the example we have a function that writes copies are DataTable into a MS SQL database table called “tblFooBar”.

using System.Data.SqlClient;

Function WriteToDB(DataTable dt)

{

SqlBulkCopy sqlBC = new SqlBulkCopy(dbconnectionstring);

sqlBC.BatchSize = 25000;

sqlBC.BulkCopyTimeout = 60;

sqlBC.DestinationTableName = “dbo.tblFooBar” ;

sqlBC.WriteToServer(dt);

}

The MSDN link is:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Tags: , ,

Legacy | SQL | .NET

Vishal.TextFileUtils - Library for working with delimited text file formats

by Vishal 10. July 2008 01:35

This is a very old piece of .net code that became very popular for some reason, so I'm posting it here in the legacy section.

A delimited file is a text file used to store un-typed data in a tabular form. It consists of a Header line and one or more LineEntries of data. Each line consists of a Value that is encased within what is known as a String Qualifier. Each encased value is then separated by a delimiter or Separator. At the end of every line is an EndLine string. Common examples are the CSV (Comma Separated Value) format and the Tab Delimited format.

So… I finally ended up writing a library that I’ve found to be immensely useful to me over the years. You can download it here! Its completely .Net, hasn’t failed me yet and can chop, slice, dice and grate delimited file formats. Common funcationality includes:

  1. Reading/ Writing Delimited Files
  2. Providing a library of strogly typed objects that define elements of a delimited text file
  3. Converting from one delimited format to another
  4. Defining your own custom file formats
  5. Re-ordering columns of a delimited file
  6. Re-naming columns of a delimited file
  7. Adding columns
  8. Removing columns
  9. Inserting columns
  10. Sorting columns
  11. Reversing columns

And more…  Before you start, Remember to add a reference to TextFileUtils.dll and add the line “using Vishal.TextFileUtils;” above your class. Here are some C# examples of how easy it is to work with delimited files: 

Reading/ Writing Delimited Files:

 

DelimitedFile df =  new DelimitedFile(new FileFormat(PredefinedFormats.CSV), @”C:\SomeFile.abc”);

foreach (LineEntry le in df)

{

    Console.WriteLine(le.FieldValues[“MyColumnName”]);

}

for (int i = 0; df.Count; i++)     

{   

    le.FieldValues[“MyColumnName”]= “MyColumnData”;

}

df.Save(@”C:\Ouput.csv”);  

Converting from one format to another: 

// One line of code!

FileUtil.ConvertFile(@”C:\somefile.txt”, new FileFormat(PredefinedFormats.CSV), @”C:\Output.txt”, new FileFormat(PredefinedFormats.TAB)); 

// One line of code with custom formats!

FileUtil.ConvertFile(@”C:\somefile.txt”, new FileFormat(“\”", “,”, “\r\n” ), @”C:\Output.txt”, new FileFormat(“”, “\t”, “\r\n”);

Removing, Renaming, Inserting, Reordering, Sorting, Adding Columns

// Read File

DelimitedFile df =  new DelimitedFile(new FileFormat(PredefinedFormats.CSV), @”C:\SomeFile.abc”);

// Remove a column called “MyColumn”

df.RemoveColumn(“MyColumn”);

// move a column called “MyOtherColumn” to the 5th position

df.MoveColumn(“MyOtherColumn”, 5); 

// sort the columns alphabetically 

df.SortColumns();

// Insert a column called “MyFirstColumn” at position 0

df.InsertColumn(0, “MyFirstColumn”); 

// Rename a column called “OldColumnName” to “NewColumnName”

df.RenameColumn(“OldColumnName”, “NewColumnName”); // Reverse the columns in the file

df.ReverseColumns();

// Re-order the columns to your liking

List<string> OldColumnsList = df.GetColumnNamesList();     

// {Code to Re-Order OldColumnList here}

df.ReOrderColumns(OldColumnsList);

// Save the file!

df.Save(@”C:\Ouput.csv”);

The library has much more. For the complete specifications refer to the TextFileUtils.chm help file. If you have any comments, questions or improvements, you can post them here or email me.

Download TextFileUtils version 1.0.0.0

Tags:

Legacy


L symphonique de la derniere chanson, jh cherche professeur de danse, est tres belle et la partition se maintient au meme niv imitation replique rolex montres montre pendant toute la chanson, rehaussee par le son des violons. Un technicien et d ax pompes fnebes Bonio ces gandes tentes tiisees po assombi es maisons et signifie e dei. Mes dip seont es ps ties en es int ma magie po c de noveax replica uhren tos. replique montre diesele oU i fat veie a ce qe chaqe escapin soit mis en vae dans e moinde detai. "Je n'ai pas d'avis medical sur ta situation, mais par contre il est clair que tu en souffres, donc rien replique montres que pour a ton medecin devrait t'ecouter. Et "la" rando : les gorges de Samarie (mais attention, hyper frequente en ete). Le groupe etait assez heterogene, de la quinqua qui le faisait pour son plaisir aux etudiants bac +1 divers mais avec une majorite de terminales (toutes rolex replique sections confondues) donc je ne pense vraiment pas qu'il y ait eu de selection. D'autant qu'il y a deux nivcopie de montre rolex de difficulte et que certains chapitres ne seront accessibles qu'en ayant un minimum d'allies dans replique montre notre equipe, a la fin du copie de montre de luxe. Le fis d gitaiste des Pink Foyd fait appe de sa peine de pisonAge de 21 ans, e jene homme, etdiant en histoie a Cambidge, avait ete aete a son domicie apes a manifestation conte a rolex montre pas cher hasse des fais nivesitaies e 9 decembe a Londes, maqee pa des incidents.
Repliques Montres haute qualite vous aider a economiser beaucoup tout en appreciant la valeur du nom de marque. Vous voulez avoir cette montre luxe haut de gamme suisses sur votre main, mais replica uhren l'achat d'une voiture semble une meilleure option. Oui, c'est la realite. Ces montres symbole de statut social co?tent quelque chose replicas de relojes qui est au-dela pour permettre pour une personne normale. Mais ne desesperez hublot replique montres pas si vous ne pouvez pas vous permettre d'acheter ces montres de luxe. Vous avez une belle occasion d'acheter des montres de prestige nom Replique de la marque. Certaines personnes aiment l'aspect d'un veritable montres au poignet, ou replique rolex montres voulez juste nombreuses montres de marque pour correspondre a leur habillement, mais evitez patek philippe replica orologi de depenser des milliers de dollars que une vraie montre mai cots, ils se tournent vers les replique montre montres bonne replique. Si vous ne voulez pas acheter des montres de veritables montres de haute qualite puis de replicas sont votre choix ideal.