Thoughts from Dan Miser RSS 2.0
# Tuesday, 03 February 2009

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.

Tuesday, 03 February 2009 04:13:23 (GMT Standard Time, UTC+00:00)  #    Comments [0] -
ADO.NET
# Monday, 22 October 2007
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:


select Id
from MyTable
group by Id
having count(*) > 1
Monday, 22 October 2007 20:33:51 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
ADO.NET
# Tuesday, 29 May 2007
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.

Tuesday, 29 May 2007 18:54:59 (GMT Daylight Time, UTC+01:00)  #    Comments [1] -
.NET | ADO.NET
# Wednesday, 21 February 2007
A while back, I wrote about how to use the Data Access Application Block (DAAB) in the Compact Framework. While this code works just fine, it turns out that Enterprise Library 3.0 will support this natively. I really like the TableExists method that they added, too.

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

Wednesday, 21 February 2007 23:23:26 (GMT Standard Time, UTC+00:00)  #    Comments [0] -
.NET | ADO.NET | Compact Framework
# Monday, 19 February 2007
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);

conn.Open();

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;

cmd.Parameters.Add(paramDate);

 

SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);

DataTable tbl = new DataTable();

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);
Monday, 19 February 2007 15:37:16 (GMT Standard Time, UTC+00:00)  #    Comments [0] -
.NET | ADO.NET
# Wednesday, 06 December 2006
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.

Wednesday, 06 December 2006 15:50:00 (GMT Standard Time, UTC+00:00)  #    Comments [0] -
ADO.NET | Compact Framework
# Tuesday, 22 August 2006
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.

Tuesday, 22 August 2006 16:14:00 (GMT Daylight Time, UTC+01:00)  #    Comments [4] -
ADO.NET
# Monday, 31 July 2006
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);
Monday, 31 July 2006 22:37:00 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
ADO.NET
# Monday, 20 February 2006
This entry is a quick one, pointing out a couple of ADO.NET related links that I came across today.

Blog entry showing how to add Primary Keys to GetSchema in SqlClient - you could also extend this logic to add other database-specific metadata to any ADO.NET provider

Introduction to Visual Studio Data Designer Extensibility (DDEX) - this shows how you can add/customize functionality to the ServerExplorer in VS.NET.

Monday, 20 February 2006 19:16:00 (GMT Standard Time, UTC+00:00)  #    Comments [0] -
ADO.NET
# Thursday, 16 September 2004
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.
Thursday, 16 September 2004 16:30:00 (GMT Daylight Time, UTC+01:00)  #    Comments [3] -
ADO.NET
# Saturday, 06 March 2004
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.
Saturday, 06 March 2004 01:32:00 (GMT Standard Time, UTC+00:00)  #    Comments [2] -
ADO.NET
Navigation
Archive
<2017 December>
SunMonTueWedThuFriSat
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
Blogroll
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Dan Miser
Sign In
Statistics
Total Posts: 388
This Year: 0
This Month: 0
This Week: 0
Comments: 630
Themes
Pick a theme:
All Content © 2017, Dan Miser
DasBlog theme 'Business' created by Christoph De Baene (delarou)