SqlBulkCopy

January 30, 2007 Leave a comment

If you are ever in a scenario where you are going to be doing tons of repetitive inserts, consider using the new SqlBulkCopy command that’s found in .NET 2.0.   It uses an insert bulk to do all of your inserts in a single round trip, and has settings which you can use to batch up or break up the inserts if needed, and a timeout setting as well.  The potential performance savings are huge, especially if you are doing things like importing data from outside vendors or processing data submissions from third parties via FTP or web services.

 

Here’s an example of how it’s used.

 

First you would create a bulk copy object given the connection string.

SqlBulkCopy oBulk = new SqlBulkCopy(yourconnectionstring);

Then you would create a datatable to import…in this case I’ll call some DB method
DataTable dtBulk = GetAllZipsOutMyDatabase();

Next set the destination table name where you want the bulk copy to go:

oBulk.DestinationTableName = “MyDestinationTable”

 

Now this next step is important, IF the columns in your datatable do not exactly match the column structure in the destination table.  If your local datatable is structured exactly like the destination table, meaning all of the columns are represented and they are the correct datatype, you can do without this step.  However, if your table has an identity column, or some columns are not represented, you MUST map your columns to the destination table.  In this example I am using the names of the local datatable columns and mapping them onto the destination table, since I named my local datatable columns the same as they are in the destination table.

oBulk.ColumnMappings.Clear();
foreach(DataColumn oCol in dtBulk.Columns)

{
         oBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(oCol.ColumnName, oCol.ColumnName));

}

Now that that’s done I can set the batch size and the timeout:

oBulk.BatchSize = 1000;
oBulk.BulkCopyTimeout = 6000;

Now I’m all set up and I can write to the server.

oBulk.WriteToServer(dtBulk);

And that’s it.  Rather than looping on a bunch of inserts, I can do all of the inserts in a single round trip.

 

Happy bulk copying,

 

Rob

Categories: SQL

SLO .NET User Group has first meeting…and gets recognized by INETA

November 15, 2006 Leave a comment

Hi everyone,

Sorry I’ve been a bit silent lately, but I went to VSLive! in Boston the last week in October and spent some time on either side of the conference visiting my family up in New Hampshire. Then I came back and it took me a bit to catch up with everything. One of the things I had to catch up on was my first User Group meeting.

Last night the first meeting was held and by all accounts is was a great success. 10 people showed up for pizza, lively discussion, and networking. I wasn’t sure what to expect, but I was very pleased with the turnout and everyone’s interaction.

I give myself a B on my presentation. I tried to pick an interesting topic, but I didn’t have a whole lot of time to prepare and so I was a bit slow during the code samples. But it was my first time through the talk so I already know what I need to do to improve on it. Plus I got some good feedback on the talk from the group.

On top of that, today INETA, the International .NET Association, marked our group as Active and now it can be found on their site. Plus we get access to speakers and I get to put the logo on the UG website. Needless to say, I’m very excited about this turn of events.

I hope to get back to my blog topics pretty soon…going to try and get a synopsis of my last talk up here pretty quickly.

Talk to you later!

Rob

Categories: .NET User Group

Custom Control Selection At Design Time

October 19, 2006 Leave a comment

So I found this going through some old code this past week. I had built a custom header to interact with a Wizard control. I don’t particularly care for the sidebar and I wanted navigation tabs floating above the Wizard. That was relatively easy to put together. It uses the ActiveStepIndex on the Wizard to determine its tab appearance, so when I dropped it into the Designer, I wanted to be able to select the Wizard1 control as the Wizard for my header to interact with. How you do that is with a TypeConverter.

A TypeConverter allows you to specify, at design time, the controls you would like your property to interact with. In this example, I have a string property on my WizardHeader control that I would like to set to “Wizard1”, the ID of the Wizard on my WebForm. Now I could type that in, but I could also type in any number of other things that would not be the type of control I wish to use and would break when I went after the ActiveStepIndex property during execution.

So I built a custom TypeConverter that would return to me the IDs for the controls on the page that were of type Wizard. Here’s how it’s done:

First, I will inherit from StringConverter, which is the base Converter for capturing properties of type string. There are a couple of overrides. The most important is GetStandardValues. This allows you to customize how you want to acquire the values that are available through your converter.

By looping through the controls that are available in the component context container, I can check for their control type, and if they match the type I am looking for (in this case System.Web.UI.WebControls.Wizard), I add those IDs to a collection. At the end I build a string list and hand back a StandardValuesCollection based on that list.


using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Collections;

namespace WebControls.Wizards
{
public class WizardListConverter : StringConverter
{
private static
TypeConverter.StandardValuesCollection myReferences;

public WizardListConverter()
{

}

public override bool
GetStandardValuesSupported(ITypeDescriptorContext context)
{
return true;
}

public override bool
GetStandardValuesExclusive(ITypeDescriptorContext context)
{
return true;
}

public override TypeConverter.StandardValuesCollection
GetStandardValues(ITypeDescriptorContext context)
{
ArrayList matchingReferences = new ArrayList();

for (int i = 0; i < context.Container.Components.Count; i++)
{
if (context.Container.Components[i]
is System.Web.UI.WebControls.Wizard)
{
System.Web.UI.Control contr =
(System.Web.UI.Control)context.Container.Components[i];

matchingReferences.Add(contr.ID);
}
}

matchingReferences.Sort(0, matchingReferences.Count, null);

if (context.Container.Components.Count > 0)
{
string sSplitReferences = "";

for (int j = 0; j < matchingReferences.Count; j++)
{
if (matchingReferences[j] + "" != "")
{
sSplitReferences += "," + matchingReferences[j];
}
}

string[] references = sSplitReferences.Split(',');

myReferences = new
TypeConverter.StandardValuesCollection(references);
}
else
{
myReferences = new
TypeConverter.StandardValuesCollection(new string[] { "" });
}


return myReferences;
}
}
}

Now that that is done, I need to go back to my WizardHeader control and add the WizardListConverter as an attribute:


using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Web.UI;

namespace WebControls.Wizards
{
public class WizardHeader : System.Web.UI.Control
{
//the id of the control I wish to interact with
private string m_sWizardControl;

[TypeConverter(typeof(WizardListConverter))]
public string WizardControl
{
get { return m_sWizardControl; }
set { m_sWizardControl = value; }
}
}
}

Now when I drop the WizardHeader onto a WebForm, I can simply select the appropriate wizard in the property list.

So now I can make it a lot easier to control the interaction between my custom controls at design time to make sure I am only interacting with the types of controls I want.

Talk to you later!

Rob

Categories: ASP.NET Code