I'm using SqlBulkCopy to quickly copy an Excel spreadsheet into MSSQL. It is lightning fast, and works extremely well. One snag came up because I need to have an Identity field for my primary key on this newly copied table (the short story is NHibernate's need for a PK, and the lack of anything resembling a PK in the Excel data due to it coming from legacy data).

The problem is if you add the Identity field in the first column of the destination table, SqlBulkCopy will try to line the columns up by position and things break. You can add ColumnMappings to have SqlBulkCopy write the excel columns to the right MSSQL table column, but that can be a lot of code if you have a lot of columns.

My solution ended up being to simply add the Identity field to the end of the destination table. By doing that, the columns line up during the SqlBulkCopy and everything works just fine.

 select Id from MyTable group by Id having count(*) > 1 

There is a new attribute, configSource, that looks promising because it can be used to redirect .NET from looking at the app.config (or web.config) and instead make it look at an arbitrary generic user.config file for its content. The problem with this approach is that it requires every machine to have this user.config file in the same place. What I want is more like what the old file attribute of appSettings provided. Unfortunately, the new tools in ASP.NET 2.0 get the connection string information from the connectionStrings section, so using the appSettings approach is out-dated.

For now, I'll use configSource, but it is a pain to have to tell each memeber of the dev team that they need to create their very own user.config file and place it in a certain directory, since this usage means that you wouldn't want to check user.config into your source control system.

Take a look at J.D. Meier's paper, How To Reference Web Services and Databases During Development, for some concrete samples on how this looks.

David Hayden wrote a couple of articles about this here and here.

SqlCeConnection conn = newSqlCeConnection(connStr);

conn.Open();
SqlCeCommand cmd = newSqlCeCommand("select
* from employees where [Hire Date] < @paramDate", conn);

 
SqlCeParameter paramDate = cmd.CreateParameter();

paramDate.ParameterName = "@paramDate";

paramDate.DbType = DbType.DateTime;

paramDate.Value = DateTime.MaxValue;

cmd.Parameters.Add(paramDate);

 
SqlCeDataAdapter da = newSqlCeDataAdapter(cmd);
DataTable tbl = newDataTable();

da.Fill(tbl);

dataGridView1.DataSource = tbl;

 

conn.Close();

Note that this fails when using MSSQL Compact Edition (MSSQLCE) under a standard WinForm application and a Pocket PPC platform. The same code against a standard desktop MSSQL version will work fine.

The workaround is simple. Just get away from the MaxValue. Of course, this is a workaround, so it's not perfect, but it certainly works for my needs.

 paramDate.Value = DateTime.MaxValue.AddMilliseconds(-1); 

I came across a library that ported the .NET 1.1 version of the Data Access Application Block to CF: Data Access Application Block for .NET CF. It was pretty good, but there were several things that needed fixing for my situation, namely: removal of OpenNetCF, upgrade to .NET CF 2.0, upgrade to Windows Mobile 5.0, and upgrade of the Northwind.sdf database to the latest version provided by Microsoft.

Here is my port of the original work. I'll post updates (like upgrades to Enterprise Library for .NET Framework 2.0, January 2006) to the blog here, and my web site.