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.
Moving from a legacy system, one needs to appropriately deal with bad data in the database. For example, in order to put a primary key on a table, the column(s) in that primary key must be able to uniquely identify one record. If you need to find rows with duplicate entries before adding a primary key, you can start with this query:
group by Id
having count(*) > 1
I hate when we lose functionality in applications. I'm writing a pretty classic ASP.NET application right now. One assembly is the Data Access Layer, and I try to write enough unit tests to actually show that things stand a chance of working (both now and in the future). Also, I occasionally like to work at home, disconnected from work's servers (database and other). That means that I have MSSQL installed on my laptop, which has local copies of the databases that I would need. I also have my web.config checked into source control so others on the team can do a simple checkout and have the web site working with a simple build of the MSBuild script. Given that setup, how would one go about being a responsible unit tester? It seems that what I want is a way to set a default connection string in the main config file, and allow a user.config to (optionally) exist. If it did exist, it could override the settings that were in the main config file to point to a new database.
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.
The following code assigns DateTime.MaxValue to a parameter. When trying to run this code against the Northwind databse, it will produce the error, "An overflow occurred while converting to datetime".
SqlCeConnection conn = new SqlCeConnection(connStr);
SqlCeCommand cmd = new SqlCeCommand("select * from employees where [Hire Date] < @paramDate", conn);
SqlCeParameter paramDate = cmd.CreateParameter();
paramDate.ParameterName = "@paramDate";
paramDate.DbType = DbType.DateTime;
paramDate.Value = DateTime.MaxValue;
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable tbl = new DataTable();
dataGridView1.DataSource = tbl;
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've been working on a .NET Compact Framework (CF) 2.0 application, and one thing that this applciation needed was access to a local database on the PDA. In addition, I was hoping to leverage some of the MS Application Blocks, specifically the Data Access Application Block, to make things easier and more uniform for the developers.
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.
I have a need to allow users to build up a connection string and store it in a config file. Pretty simple need, and I would imagine rather standard. After all, I can't imagine any user I deploy to would have the same connection string I do, much less the same database. As it turns out, providing a robust piece of code to get this done will have to be all custom code. Given how common this scenario is, I'm more than a bit disappointed that MS just punted on this entire thing. Do they really
expect end users to just wander around and edit config file XML syntax by hand and get it right? Do they really think not having the ability to do a "Test Connection" is a good idea?
I know about the support article from MS, but that uses interop back to plain ADO. I really can't believe they would not provide a managed code alternative that works with ADO.NET. Furthermore, the DbConnectionStringBuilder class in ADO.NET 2.0 starts to solve the problem, but stops short, like Frank Costanza on a drive through New York City. That class gives us some capability to build up the connection string, but none to visually build it up, much less edit a connection string in-place in the config file.
After way more time searching around the Internet than I care to admit, I'd like to think that I'm missing some simple link, but it looks like MS expects every single .NET developer to implement their own Connection String Wizard from scratch. Feel free to reply if you know of any pre-built solutions.
I'm using the Enterprise Library Data Access Application Block
in order to use a variety of different databases on the backend without changing my code. One thing that recently came up was how to properly use a TIMESTAMP field in MSSQL. I don't want to use SqlDbType.Timestamp, as that refers to a specific DBMS implementation, and there is no DbType.Timestamp at the base class level for me. The following code snippet does work, though, so use this to get unstuck:
db.AddOutParameter(cmd, "@newLastChanged", DbType.Binary, 8);
Joe White took some notes on NDataStore
. I will be keeping a very, very close eye on this DB. I have used JDataStore
in past lives, and it was an extreme pleasure to use it. Steve Shaugnessy really knows how to eek performance out of a DBMS.
The first paper on BDP is up on my web site
. This paper
talks about what you need to do to get your custom BDP provider to work with the Borland DataExplorer. I plan on adding more content to this area ASAP.