Common tips and tricks from a SQL Developer Support perspective

DataSet, DataTable, DataRelation, PrimaryKey template.

Sometimes I need to whip up a dataset with some tables and primary keys and relations.

It’s not hard to do, but repetitive. So, here is a template that I use. Thought I’d share it.

 

        static void Main(string[] args)

        {

            // Create list of columns, add/remove/rename as necessary

            List<DataColumn> tbOneCols = new List<DataColumn>();

            tbOneCols.Add(new DataColumn("tbOne_cOne", typeof(int)));

            tbOneCols.Add(new DataColumn("tbOne_cTwo", typeof(string)));

 

            List<DataColumn> tbTwoCols = new List<DataColumn>();

            tbTwoCols.Add(new DataColumn("tbTwo_cOne", typeof(int)));

            tbTwoCols.Add(new DataColumn("tbTwo_cTwo", typeof(int)));

            tbTwoCols.Add(new DataColumn("tbTwo_cThree", typeof(string)));

 

            // Create tables and insert the rows. 

            DataTable tblOne = new DataTable("TableOne");

            foreach (DataColumn dc in tbOneCols)

            {

                tblOne.Columns.Add(dc);

            }

 

            DataTable tblTwo = new DataTable("TableTwo");

            foreach (DataColumn dc in tbTwoCols)

            {

                tblTwo.Columns.Add(dc);

            }

 

            // Add primary keys to datatables.

            DataColumn[] tblOnePk = new DataColumn[1];

            tblOnePk[0] = tblOne.Columns[0];

            tblOne.PrimaryKey = tblOnePk;

 

            DataColumn[] tblTwoPk = new DataColumn[1];

            tblTwoPk[0] = tblTwo.Columns[0];

            tblTwo.PrimaryKey = tblTwoPk;

 

            // Create DataSet and add tables.

            DataSet ds = new DataSet("TestDataSet");

            ds.Tables.Add(tblOne);

            ds.Tables.Add(tblTwo);

 

            // Add a relation to the dataset (first column in t1 to second column in t2)

            DataRelation dsRel = new DataRelation("TblOne_to_TblTwo", tblOne.Columns[0], tblTwo.Columns[1]);

            ds.Relations.Add(dsRel);

 

            // Finally add some rows

            tblOne.Rows.Add(new object[]{1, "value one"});

            tblOne.Rows.Add(new object[]{2, "value two"});

 

            tblTwo.Rows.Add(new object[] { 1, 1, "value one" });

            tblTwo.Rows.Add(new object[] { 2, 1, "value one" });

            tblTwo.Rows.Add(new object[] { 3, 1, "value one" });

            tblTwo.Rows.Add(new object[] { 4, 2, "value one" });

        }

 

 

How to examine memory dump for SqlCommand.CommandText using WinDbg and SOS

 

Ok, so yesterday I had a post on how to generate a dump for a SqlException.

 

In that scenario we had an application that was throwing an exception like so:

 

Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 

What we would like to know now is of course what SqlCommand is not returning in time and thereby causing this exception.

There may be easier ways to figure out what the SqlCommand is, but say for example that this is a deployed application (no source) and no access to SQL Server (no SQL Profiler), then this may be helpful.

This post is not intended to go into depth on how to debug .Net/ADO.Net applications, for this I would recommend my colleagues Tess blog.

 

So, first follow the steps from last post in order to create the application and dump. Found here

 

Once this is done, you should have some .dmp files in the “C:\Dumps” directory.

Now, the object of this is to figure out what SQL was executing when the application timed out. This can be done like so:

 

Download and install the debugging tools from here:

"Debugging Tools for Windows - Overview"

http://www.microsoft.com/whdc/devtools/debugging/default.mspx

 

Start the debugger (WinDbg) and go File – Open Crash Dump, select the .dmp file you are interested in (should have ‘First Chance’ in the filename)

Then you need to load what is called the symbols, so go File -> Symbol File Path, set this to:

 

srv*C:\Symbols*http://msdl.microsoft.com/download/symbols

 

Then you need to load the SOS extension. This file is found in the .Net framework directory, depending on what type of machine (x86/x64) you are running.

 

    C:\Windows\Microsoft.NET\Framework\v2.0.50727\sos.dll

    C:\Windows\Microsoft.NET\Framework64\v2.0.50727\sos.dll

 

So, in windbg, in order to load the SOS extension, type the following and hit enter:

 

.load C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\sos.dll

 

On this is done, let us check what the CLR stack looks like at the time of the exception. Do this by running:

 

!clrstack

 

This should give an output like this:

 

0:000> !clrstack

OS Thread Id: 0x2d0 (0)

*** WARNING: Unable to verify checksum for System.Data.ni.dll

Child-SP         RetAddr          Call Site

000000000012ec90 00000642b78c50b5 System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlClient.SqlException, Boolean)

000000000012ecd0 00000642b78c13fb System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(System.Data.SqlClient.TdsParserStateObject)

000000000012ed30 00000642b789a712 System.Data.SqlClient.TdsParser.Run(System.Data.SqlClient.RunBehavior, System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.BulkCopySimpleResultSet, System.Data.SqlClient.TdsParserStateObject)

000000000012ee00 00000642b7898096 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(System.String, Boolean)

000000000012ee90 00000642b7897756 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Data.Common.DbAsyncResult, System.String, Boolean)

000000000012ef40 00000642801b0248 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

000000000012efb0 000006427f602672 TimeOutApp.Program.Main(System.String[])

 

This seems correct, there is an error on a SqlConnection, it all started with a call to a SqlCommand.ExecuteNonQuery.

So, let us have a look at the objects on the stack. Do this by running:

 

!dumpstackobjects

 

This should give an output like this:

 

0:000> !dumpstackobjects

OS Thread Id: 0x2d0 (0)

RSP/REG          Object           Name

000000000012e9b8 000000000207ab80 System.String

000000000012eab0 000000000205bdd0 System.Data.SqlClient.TdsParserStateObject

000000000012eab8 000000000205bb30 System.Data.SqlClient.TdsParser

000000000012eac0 000000000206ff70 System.Data.SqlClient.SqlCommand

000000000012eac8 000000000207aa50 System.Data.SqlClient.SqlException

000000000012ead0 000000000207aa50 System.Data.SqlClient.SqlException

000000000012eae0 000000000207aa50 System.Data.SqlClient.SqlException

000000000012eb00 000000000207aa50 System.Data.SqlClient.SqlException

000000000012ebd0 000000000207aa50 System.Data.SqlClient.SqlException

000000000012ebd8 000000000207aa50 System.Data.SqlClient.SqlException

000000000012ebf0 000000000206ff70 System.Data.SqlClient.SqlCommand

000000000012ebf8 000000000205bb30 System.Data.SqlClient.TdsParser

000000000012ec00 000000000205bdd0 System.Data.SqlClient.TdsParserStateObject

000000000012ec10 000000000205bb30 System.Data.SqlClient.TdsParser

000000000012ec70 0000000001ff5640 System.Data.SqlClient.SqlConnection

000000000012ec78 0000000001ff5640 System.Data.SqlClient.SqlConnection

000000000012eca0 000000000206ff70 System.Data.SqlClient.SqlCommand

000000000012eca8 000000000205bb30 System.Data.SqlClient.TdsParser

000000000012ecb0 000000000205bb30 System.Data.SqlClient.TdsParser

.. truncated ..

 

So this also seems promising, we have a SqlException and a SqlCommand, let’s check the SqlException first and see what the error message is.

This is done by passing the objects address to the !dumpobject method.

 

0:000> !dumpobj 000000000207aa50

Name: System.Data.SqlClient.SqlException

MethodTable: 00000642b7a2e978

EEClass: 00000642b73c5aa8

Size: 144(0x90) bytes

 (C:\WINDOWS\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll)

Fields:

              MT    Field   Offset                 Type VT     Attr            Value Name

00000642784365e8  40000b5        8        System.String  0 instance 0000000000000000 _className

0000064278434990  40000b6       10 ...ection.MethodBase  0 instance 0000000000000000 _exceptionMethod

00000642784365e8  40000b7       18        System.String  0 instance 0000000000000000 _exceptionMethodString

00000642784365e8  40000b8       20        System.String  0 instance 000000000207a950 _message

000006427842dc08  40000b9       28 ...tions.IDictionary  0 instance 000000000207aae0 _data

00000642784368b0  40000ba       30     System.Exception  0 instance 0000000000000000 _innerException

00000642784365e8  40000bb       38        System.String  0 instance 0000000000000000 _helpURL

0000064278435ed8  40000bc       40        System.Object  0 instance 0000000000000000 _stackTrace

00000642784365e8  40000bd       48        System.String  0 instance 0000000000000000 _stackTraceString

.. truncated ..

 

It makes sense that the message is in the _message variable, so let’s check the value of that, again using !dumpobject and the address.

 

0:000> !dumpobj 000000000207a950

Name: System.String

MethodTable: 00000642784365e8

EEClass: 000006427803e4f0

Size: 254(0xfe) bytes

 (C:\WINDOWS\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll)

String: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Fields:

              MT    Field   Offset                 Type VT     Attr            Value Name

000006427843d858  4000096        8         System.Int32  1 instance              115 m_arrayLength

000006427843d858  4000097        c         System.Int32  1 instance              114 m_stringLength

0000064278438030  4000098       10          System.Char  1 instance               54 m_firstChar

.. truncated ..

 

So this is clearly the same error that we got from the application, promising. Let’s then examine the SqlCommand that we got by running !dumpstackobjects earlier.

Again, this is done by passing the SqlCommand object address to the !dumpobj method.

 

0:000> !dumpobj 000000000206ff70

Name: System.Data.SqlClient.SqlCommand

MethodTable: 00000642b7a2f240

EEClass: 00000642b73c5fe8

Size: 224(0xe0) bytes

 (C:\WINDOWS\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll)

Fields:

              MT    Field   Offset                 Type VT     Attr            Value Name

0000064278435ed8  400018a        8        System.Object  0 instance 0000000000000000 __identity

0000064274b3d5b0  40008c3       10 ...ponentModel.ISite  0 instance 0000000000000000 site

0000064274b6cec8  40008c4       18 ....EventHandlerList  0 instance 0000000000000000 events

0000064278435ed8  40008c2      210        System.Object  0   static 0000000001ff9120 EventDisposed

000006427843d858  40016f2       b0         System.Int32  1 instance                1 ObjectID

00000642784365e8  40016f3       20        System.String  0 instance 0000000001ff3e90 _commandText

00000642b79ee2c0  40016f4       b4         System.Int32  1 instance                0 _commandType

000006427843d858  40016f5       b8         System.Int32  1 instance               10 _commandTimeout

00000642b79f0ab0  40016f6       bc         System.Int32  1 instance                3 _updatedRowSource

00000642784357b8  40016f7       d0       System.Boolean  1 instance                0 _designTimeInvisible

00000642b7a31e90  40016f8       28 ...ent.SqlDependency  0 instance 0000000000000000 _sqlDep

00000642784357b8  40016f9       d1       System.Boolean  1 instance                0 _inPrepare

000006427843d858  40016fa       c0         System.Int32  1 instance               -1 _prepareHandle

00000642784357b8  40016fb       d2       System.Boolean  1 instance                0 _hiddenPrepare

00000642b7a2fbe0  40016fc       30 ...rameterCollection  0 instance 0000000000000000 _parameters

.. truncated ..

 

So now naturally we’ll just dump out the value for the commandText variable:

 

0:000> !dumpobj 0000000001ff3e90

Name: System.String

MethodTable: 00000642784365e8

EEClass: 000006427803e4f0

Size: 76(0x4c) bytes

 (C:\WINDOWS\assembly\GAC_64\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll)

String: waitfor delay '00:00:20';

Fields:

              MT    Field   Offset                 Type VT     Attr            Value Name

000006427843d858  4000096        8         System.Int32  1 instance               26 m_arrayLength

000006427843d858  4000097        c         System.Int32  1 instance               25 m_stringLength

0000064278438030  4000098       10          System.Char  1 instance               77 m_firstChar

.. truncated ..

 

This gives us the command that was executing when the application was throwing the exception.

In this case we have forcefully made the command to wait for 20 seconds. In your scenario you should take what you found here and

try to execute it from outside the application, for example from SQL Server Management Studio. If it runs slowly from there, we can rule out the client side as the cause of the error.

 

But as mentioned, this was just a very short introduction on how to find out what SqlCommand.CommandText was executing when getting a timeout.

For more info on SOS, have a look here:

 

".NET Framework Tools - SOS Debugging Extension (SOS.dll)"

http://msdn.microsoft.com/en-us/library/bb190764.aspx

 

How to create memory dump for SqlException using DebugDiag

 

Sometimes we in support will ask you to create a dump for when your .Net client crash due to a SqlException.

We do this in order to be able to find out more about the state of the process at the time of the crash.

 

So today I will show you the simplest way to create that dump with DebugDiag.

 

First, let us create an application that will simply connect to a database and execute a query that will run for too long and thereby cause a time out exception.

(Normally you do not know what statement is causing the exception and this one of the reasons we ask for dumps, so that we can figure this out.)

 

So, create a new C# console application called “TimeOutApp” as follows:

 

        static void Main(string[] args)

        {

            Console.WriteLine("Hit any key to get exception.");

            Console.ReadKey();

            Console.WriteLine("Running...");

 

            string cs = @"Data Source=<your server>;Integrated security=SSPI;Initial Catalog=<your database>";

            using (SqlConnection con = new SqlConnection(cs))

            {

                con.Open();

                SqlCommand cmd = con.CreateCommand();

                cmd.CommandTimeout = 10; // Lowering from default of 30s. to execption quicker.

                cmd.CommandText = "waitfor delay '00:00:20';";

                cmd.ExecuteNonQuery();

                con.Close();

            }

        }

 

and run it. This should then produce the exception:

 

Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

 

From this we can’t figure out what SQL command was executing. Of course we could trace this on server side, but that is another story. And you may not have access to the server.

So, time for some dump taking.

 

. Go to the download site for DebugDiag.

"Debug Diagnostic Tool v1.1"

http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=28bd5941-c458-46f1-b24d-f60151d875a3&displaylang=en

. Download and install it on the machine that is running the crashing application.

. Start your application (“TimeOutApp” in this case).

. Start DebugDiag.

. You should now be shown a dialog called “Select Rule Type”, select “Crash” and “Next”.

. You should now be shown a dialog called “Select Target Type”, select “A specific process” and “Next”.

. You should now be shown a dialog called “Select Target”, this should list all running processes, so select your application (“TimeOutApp” in this case) and “Next”.

. You should now be shown a dialog called “Advanced Configuration”, click “Exceptions” in the “Advanced Settings” section.

. You should now be shown a dialog called “First Chance Exception Configuration”, click “Add Exception”.

. You should now be shown a dialog called “Configure Exception”. Set this up as follows.

Exception Code = E04334F4D (Just click it in the list to the left)

.Net Exception Type = System.Data.SqlClient.SqlException

Action Type = Full Userdump

Action Limit = 3 (this is so that we get more than one dump if needed).

  then click OK. This should now list the added rule. So click “Save & Close”

. You should now be back at the “Advanced Configuration”, click “Next”.

. You should now be shown a dialog called “Select Dump Location And Rule Name”, change “Userdump Location” to “C:\Dumps” and click “Next”.

. You should now be shown a dialog called “Rule Completed”, select “Activate the rule now” and click “Finish”.

 

That’s it.

Now reproduce your problem (just hitting any key in the “TimeOutApp” in this case).

When the application has crashed, then the “Userdump Count” should have increased from 0.

Have a look in the “C:\Dumps” directory; this should contain files with the name looking something like this:

 

TimeOutApp__PID__2068__Date__12_17_2009__Time_04_02_39PM__xxxxxx.dmp

 

Compress the whole directory and upload it to us for analysis.

Very short and simple example of using SqlMetal

A simple sample of how to use SqlMetal.

 

"Code Generation Tool (SqlMetal.exe)"

http://msdn.microsoft.com/en-us/library/bb386987.aspx

 

This tool will create mappings and code for LINQ to SQL.

So, as mentioned, simple and short. Create a database, some tables and insert some data.

 

create database VeryMetal

go

use VeryMetal

go

create table Users (UserId int primary key, FirstName nvarchar(10), LastName nvarchar(10), DepartmentId int)

go

create table Departments(DepartmentId int primary key, DepartmentName nvarchar(10))

go

 

--Insert some users and departments

insert into Users values (1, 'John', 'Johnson', 1)

insert into Users values (2, 'Paul', 'Paulson', 2)

insert into Users values (3, 'Mike', 'Mikeson', 1)

insert into Users values (4, 'Mary', 'Maryson', 2)

 

insert into Departments values (1, 'Support')

insert into Departments values (2, 'Finance')

 

Then fire up the Visual Studio Command Prompt and run the following:

 

Sqlmetal /server:<your server> /database:VeryMetal /code:C:\Temp\MetalCode.cs /map:C:\Temp\MetalMap.xml /namespace:VeryMetalEntities

 

This should now have generated the .cs and .xml files.

 

So, create new console application, add the MetalCode file to the project and enter the following code:

 

        static void Main(string[] args)

        {

            try

            {

                String cs = @"Data Source=<your server>;Initial Catalog=VeryMetal;Integrated Security=True";

                String file = @"C:\Temp\MetalMap.xml";

 

                var dc = new VeryMetal(cs, XmlMappingSource.FromUrl(file));

                var users = from u in dc.Users where u.DepartmentId == 1 select u;

               

                Console.WriteLine("All users in Support Department.");

                foreach (var user in users)

                {

                    Console.WriteLine("{0} - {1}", user.FirstName, user.LastName);

                }

 

                Console.WriteLine("\nGet single user with firstname starting with Pau ");

 

                Table<Users> ut = dc.GetTable<Users>();

                var users2 = ut.Single(x => x.FirstName.StartsWith("Pau"));

                Console.WriteLine("{0} - {1}", users2.FirstName, users2.LastName);

               

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

            }

        }

 

Output should be:

 

All users in Support Department.

John - Johnson

Mike - Mikeson

 

Get single user with firstname starting with Pau

Paul - Paulson

How to see the SQL executing when updating a DataSet via DataAdapter.Update()

A fairly common question: How can I see the SQL that will be executed when updating a DataSet using a DataAdapter?

 

The first thing most people try is to get hold of is the Insert/Update commands for the DataAdapter.

This is typically done like so (using the trusted Northwind) with CommandBuilders:

 

            using (SqlConnection con = new SqlConnection(cs))

            {

                con.Open();

                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT * FROM Shippers";

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                SqlCommandBuilder scb = new SqlCommandBuilder(da);

 

                // Display insert and update commands

                Console.WriteLine("Insert: \n{0}\n",scb.GetInsertCommand().CommandText);

                Console.WriteLine("\nUpdate: \n{0}\n", scb.GetUpdateCommand().CommandText);

 

                con.Close();

            }

 

This will give the output:

 

Insert:

INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@p1, @p2)

 

Update:

UPDATE [Shippers] SET [CompanyName] = @p1, [Phone] = @p2 WHERE (([ShipperID] = @p3)

AND ([CompanyName] = @p4) AND ((@p5 = 1 AND [Phone] IS NULL) OR ([Phone] = @p6)))

 

This will however only display what the ‘template’ for the command contains.

We all know that when you call update/insert on a DataAdapter the .Net runtime will go through all rows in the dataset/datatable and check for modified rows.

If the row is modified, then the values of the columns are used in order to fill the SQL statements.

So then, how can we see what the parameters contain?

 

One way to do this is to use the DataAdapters RowUpdatingevent. This can be done like so, I hope this is self-explanatory:

 

    class Program

    {

        static void Main(string[] args)

        {

            string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";

            using (SqlConnection con = new SqlConnection(cs))

            {

                con.Open();

                SqlCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT ShipperId, CompanyName, Phone from Shippers";

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                SqlCommandBuilder sb = new SqlCommandBuilder(da);

 

                DataSet ds = new DataSet();

                da.Fill(ds);

 

                // Update existing row

                ds.Tables[0].Rows[0]["CompanyName"] = "My Company";

                ds.Tables[0].Rows[0]["Phone"] = "(555) 123-654";

 

                //// Insert a row

                DataRow newRow = ds.Tables[0].NewRow();

                newRow["ShipperId"] = 100;

                newRow["CompanyName"] = "NewShipper";

                newRow["Phone"] = "555-123-456";

                ds.Tables[0].Rows.Add(newRow);

 

                da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);

 

                da.Update(ds);

                con.Close();

            }

        }

 

        static void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)

        {

            if (e.Command != null)

            {

                Console.WriteLine("\nRow Updating...");

                Console.WriteLine("Command type: -> {0}", e.StatementType);

                Console.WriteLine("Command text: \n{0}", e.Command.CommandText);

                Console.WriteLine("\nParameters:");

                foreach (SqlParameter p in e.Command.Parameters)

                {

                    Console.WriteLine("\t{0} - {1}", p.ParameterName, p.Value);

                }

            }

        }

    }

 

The output should most certainly be self-explanatory:

 

Row Updating...

Command type: -> Update

Command text:

UPDATE [Shippers] SET [CompanyName] = @p1, [Phone] = @p2 WHERE (([ShipperId] = @p3) AND ([CompanyName] = @p4) AND ((@p5 = 1

AND [Phone] IS NULL) OR ([Phone] = @p6)))

 

Parameters:

        @p1 - My Company

        @p2 - (555) 123-654

        @p3 - 1

        @p4 - Speedy Express

        @p5 - 0

        @p6 - (503) 555-9831

 

Row Updating...

Command type: -> Insert

Command text:

INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@p1, @p2)

 

Parameters:

        @p1 - NewShipper

        @p2 - 555-123-456

 

Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT.

 

There are a few documents out there describing how to get the newly inserted AutoNumber in an Access table.

 

See for example,

 

"Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)"

http://msdn.microsoft.com/en-us/library/bb384432.aspx

"Retrieving Identity or Autonumber Values (ADO.NET)"

http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

"HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET"

http://support.microsoft.com/default.aspx?scid=kb;en-us;815629

"Tackle Data Concurrency Exceptions Using the DataSet Object"

http://msdn.microsoft.com/en-us/magazine/cc188748.aspx

 

However, what some people seems to have missed or do not know is that this must be done on the same connection that executed the insert.

So, to see this, simply follow the steps below:

 

1. Create a new Access Database (Test.mdb) with a table in it (TestTable) that has two columns "ID" (which is AutoNumber) and "OurTxt" (which is Text).

2. Create a new console application.

   DON'T FORGET TO CHANGE "Any CPU" to x86 if running on a x64 machine. Otherwise you will get the:

   “System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.”

 

  See for example:

  "System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

  http://blogs.msdn.com/spike/archive/2009/02/27/system-invalidoperationexception-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine.aspx

 

3. Set the code to be:

 

     class Program

    {

        static string path = @"<your path>";

        static string db = @"Test.mdb";

        static void Main(string[] args)

        {

            string cs = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\{1}", path, db);

            // Using the same connection for the insert and the SELECT @@IDENTITY

            using (OleDbConnection con = new OleDbConnection(cs))

            {

                con.Open();

                OleDbCommand cmd = con.CreateCommand();

                for (int i = 0; i < 3; i++)

                {

                    cmd.CommandText = "INSERT INTO TestTable(OurTxt) VALUES ('" + i.ToString() + "')";

                    cmd.ExecuteNonQuery();

 

                    cmd.CommandText = "SELECT @@IDENTITY";

                    Console.WriteLine("AutoNumber: {0}", (int)cmd.ExecuteScalar());

                }

                con.Close();

            }

            // Using a new connection and then SELECT @@IDENTITY

            using (OleDbConnection con = new OleDbConnection(cs))

            {

                con.Open();

                OleDbCommand cmd = con.CreateCommand();

                cmd.CommandText = "SELECT @@IDENTITY";

                Console.WriteLine("\nNew connection, AutoNumber: {0}", (int)cmd.ExecuteScalar());

                con.Close();

            }

        }

    }

 

This should produce the self-explanatory output:

 

AutoNumber: 1

AutoNumber: 2

AutoNumber: 3

 

New connection, AutoNumber: 0

 

How to create a DNS less linked server in Access to SQL Server.

 

Sometimes you may want to use Access as a front end for SQL Server.

When doing this you typically have linked tables to the SQL Server and you have the .mdb on a shared drive for users to access.

 

Normally you create a DSN and use that for the linked server. All works well when you are querying the linked tables from the machine that hosts the .mdb.

However, when a user opens the .mdb from the remote share, querying the linked tables fails with:

 

  "ODBC--connection to '<The DSN name>' failed."

 

Basically this means that the client, i.e. the machine that you open the .mdb file on, not the machine that hosts the .mdb, does not have the ODBC DSN in question.

This is no problem; just create an ODBC DSN with the appropriate name that points to the server on that client machine and you should be good to go.

 

However, if you have many clients, then maintaining the OBDC DSNs could be a bit tiresome. I.e. if you change the database, then you need to update all clients DSNs.

There is one way to get around this, there may be more ways than this, but I’ve found this one to work.

 

In short, you create a DSN less ODBC connection via a macro. This way the information will be in the .mdb, and when you change the database, you update

the connection string and rerun the script. Now all clients will ‘automagically’ work.

 

Example setup:

 

1. SQL Server Machine

2. Access front end machine (ie. host the .mdb)

3. Access client machine (ie. opens the .mdb from machine 2)

 

On machine 2, create an ODBC DSN (I've used sqlsrv32.dll) called “ToNorthwind”, pointing to the Northwind database on the SQL Server on Machine 1.

On Machine 2, create two Access databases;  WithDSN.mdb and WithoutDSN.mdb

 

In WithDSN.mdb;

  File -> Get External Data -> Link Tables...

  Select "ODBC Databases", select "Machine Data Source", select "ToNorthwind" and select (for example) the Employees table.

  Double click the Employees table to verify that it works.

 

- In WithoutDSN.mdb;

 

  Tools -> Macro -> Visual Basic Editor (or simply ALT+F11). This should open the editor.

  Tools -> References.

  Uncheck any "Microsoft ActiveX Data Objects 2.x Library" unless it is "Microsoft ActiveX Data Objects 2.8 Library"

  Select "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ADO Ext. 2.8 for DDL and Security"

    *** or if on Windows 2008/Vista ***

  Select "Microsoft ActiveX Data Objects 6.0 Library" and "Microsoft ADO Ext. 6.0 for DDL and Security"

  Tools -> Macros..., enter a name for the macro, for example “DnsLessLinkTable”, and Create.

  Enter the following code:

  ---------------------------------------------------------------------------------------------------------------------------------

  Sub DnsLessLinkTable()

 

  Dim con As ADODB.Connection

  Dim cat As ADOX.Catalog

  Dim tbl As ADOX.Table

 

  Set con = New ADODB.Connection

  Set cat = New ADOX.Catalog

  Set tbl = New ADOX.Table

 

  cat.ActiveConnection = CurrentProject.Connection

 

  ' Create the new (linked) table. Give it an appropriate name,

  tbl.Name = "NoDnsEmployees"

  Set tbl.ParentCatalog = cat

 

  ' Set the properties to create the linked table, Make sure you have a working connection string

  tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver={SQL Server};Server=servername\instancename;Database=Northwind;Uid=user;Pwd=password;"

 

  ' Select table in remote database, in this example, Employees table in Northwind Database

  tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"

  tbl.Properties("Jet OLEDB:Create Link") = True

 

  ' Append the table to the Tables collection.

  cat.Tables.Append tbl

  Set cat = Nothing

 

  End Sub  

  ---------------------------------------------------------------------------------------------------------------------------------

 

And Run it (F5)

Now you should have a table in the table list called "NoDnsEmployees". You may have to refresh list to see it.

Double click the “NoDnsEmployees” table to verify that it works.

 

On Machine 3,

Open the remote database called "WithDSN.mdb". You should see the “Employees” table, double click it. This should give:

 

"ODBC--connection to 'ToNorthwind' failed."

 

Open the remote database called "WithoutDSN.mdb". You should see the “NoDnsEmployees” table, double click it.

This should give your employees rows without having a 'ToNorthwind' DSN on Machine 3.

 

If adding the 'ToNorthwind' DNS to Machine 3, both Access database linked tables work.

Getting metadata about dataset using lambda. Some examples.

 

This may not be a massive business requirement, but if you have a bunch of DataSets and you want (for some reason) extract information about them

then using Lambda and LINQ queries is one way to do it. I will let the code below speak for itself.

 

Sample code for meta data collection for DataSet:

 

    class Program

    {

        static void Main(string[] args)

        {

            // Create a demo dataset

            DataSet ourDataSet = CreateDataSet();

            // Display the DataTable info

            ShowTableCollectionInfo(ourDataSet.Tables);

        }

 

        private static void ShowTableCollectionInfo(DataTableCollection dtc)

        {

            IEnumerable<DataTable> tbls = dtc.Cast<DataTable>();

            int tblCount = dtc.Count;

 

            StringBuilder sb = new StringBuilder();

 

            // Which table(s) has the most rows?

            int maxRows = tbls.Max(x => x.Rows.Count);

            var maxRowsTableQuery = tbls.Where((x, max) => x.Rows.Count == maxRows);

 

            sb.AppendFormat("Table(s) with highest # of rows ({0}):", maxRows, tblCount);

            foreach (DataTable tbl in maxRowsTableQuery)

            {

                sb.AppendFormat("\n\t{0}", tbl.TableName);

            }

 

            // How many rows are there in total, i.e. sum of rows in all datatables?

            int totalRows = tbls.Sum(x => x.Rows.Count);

            sb.AppendFormat("\n\nTotal # of rows: {0} (in {1} tables)", totalRows, tblCount);

 

            // How many column on average.

            double columnAve = tbls.Average(x => x.Columns.Count);

            sb.AppendFormat("\n\nAverage # of cols: {0} (in {1} tables)", columnAve.ToString(), tblCount);

 

            //etc.

          

            // Show results

            Console.WriteLine(sb.ToString());

        }

 

        private static DataSet CreateDataSet()

        {

            DataTable dt_1 = new DataTable("DataTable_One");

            dt_1.Columns.Add(new DataColumn("T1_C1", typeof(int)));

            dt_1.Columns.Add(new DataColumn("T1_C2", typeof(int)));

            dt_1.Columns.Add(new DataColumn("T1_C3", typeof(int)));

 

            DataTable dt_2 = new DataTable("DataTable_Two");

            dt_2.Columns.Add(new DataColumn("T2_C1", typeof(int)));

            dt_2.Columns.Add(new DataColumn("T2_C2", typeof(int)));

 

            DataTable dt_3 = new DataTable("DataTable_Three");

            dt_3.Columns.Add(new DataColumn("T3_C1", typeof(int)));

 

            DataTable dt_4 = new DataTable("DataTable_Four");

            dt_4.Columns.Add(new DataColumn("T4_C1", typeof(int)));

            dt_4.Columns.Add(new DataColumn("T4_C2", typeof(int)));

            dt_4.Columns.Add(new DataColumn("T4_C3", typeof(int)));

 

            DataSet ds = new DataSet("OurDataSet");

            ds.Tables.Add(dt_1);

            ds.Tables.Add(dt_2);

            ds.Tables.Add(dt_3);

            ds.Tables.Add(dt_4);

 

            for (int i = 0; i < 10; i++)

            {

                dt_1.Rows.Add(new object[] { i, i + 5, i + 10 });

            }

            for (int i = 0; i < 5; i++)

            {

                dt_2.Rows.Add(new object[] { i, i * 5 });

            }

            for (int i = 0; i < 15; i++)

            {

                dt_3.Rows.Add(new object[] { i * 10 });

            }

            for (int i = 0; i < 5; i++)

            {

                dt_4.Rows.Add(new object[] { i, i + 100, i + 50 });

            }

            return ds;

        }

    }

 

Running this should output the following:

 

Table(s) with highest # of rows (15):

        DataTable_Three

 

Total # of rows: 35 (in 4 tables)

 

Average # of cols: 2,25 (in 4 tables)

How a proper physical TCP/IP connection to SQL Server works. Network Monitor

 

Not about a problem today, today will be about what a successful physical connection from client to SQL Server looks like in Network Monitor.

 

First download Network Monitor (currently version 3.3):

“Microsoft Network Monitor 3.3”

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=983b941d-06cb-4658-b7f6-3088333d062f

 

. Install it on the client machine and on the SQL Server machine.

. Start it.

. Select “New Capture”

. Make sure you select the “Capture Filter” tab and not the “Display Filter” tab. Otherwise you will capture all network traffic, not just the TCP we are interested in.

. Set the “Capture Filter” to:

 

tcp.Port == xxxxx

 

  Where you replace xxxx with the port number your SQL Server is listening on. If you do not know what port it is listening on, the easiest way is to open the SQL Server log, normally found here:

 

  C:\Program Files\Microsoft SQL Server\<your server instance>\MSSQL\Log

 

  At the start of the log, you should see something like this:

 

  2009-11-04 09:06:40.73 Server      Server is listening on [ 'any' <ipv6> 60847].

  2009-11-04 09:06:40.73 Server      Server is listening on [ 'any' <ipv4> 60847].

 

 So here we would replace the xxxx with 60847.

 

. Then when the filter is set, do not forget to click Apply! This will change <No Active Filter> to tcp.Port == 60847 using the example above.

. Then Start the capture (F5), connect to your SQL Server, execute your query, Stop (F7) the capture on both machines.

. This should produce an output like this (here I have a display filter set to !SSL):

 

               TimeOfDay      SourceIP       SourcePort     DestinationIP  DestinationPort              Description

3              14:57:51.505   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=......S., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663164647, Ack=0, Win=8192

4              14:57:51.506   xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP            TCP:Flags=...A..S., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468829443, Ack=663164648,  

5              14:57:51.509   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663164648, Ack=3468829444,  

15             14:57:51.801   xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP            TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468830489, Ack=663165781,  

19             14:57:52.503   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663165897, Ack=3468830947,  

22             14:57:59.513   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663165957, Ack=3468831154,  

24             14:58:05.287   xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP            TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831154, Ack=663166035,  

26             14:58:05.523   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166035, Ack=3468831465,  

27             14:58:12.845   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...F, SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166035, Ack=3468831465,  

28             14:58:12.845   xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP            TCP:Flags=...A...., SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831465, Ack=663166036,  

29             14:58:12.846   xxx.xx.162.174 60847 (0xEDAF) xxx.xx.162.187 51712 (0xCA00) TCP            TCP:Flags=...A...F, SrcPort=60847, DstPort=51712, PayloadLen=0, Seq=3468831465, Ack=663166036,  

30             14:58:12.846   xxx.xx.162.187 51712 (0xCA00) xxx.xx.162.174 60847 (0xEDAF) TCP            TCP:Flags=...A...., SrcPort=51712, DstPort=60847, PayloadLen=0, Seq=663166036, Ack=3468831466,  

 

So here we see following chain of events (3, 4 and 5):

.The client (xxx.xx.162.187:51712) sends a SYN (S) to the server (xxx.xx.162.174:60847).

.The server (xxx.xx.162.174:60847) sends an ACK (A) and a SYN (S) to the client (xxx.xx.162.187:51712).

.The client (xxx.xx.162.187:51712) sends an ACK (A) to the server (xxx.xx.162.174:60847).

 

This is the 3way handshake and once this is complete the physical connection between the client and the server has been setup.

Still, this does not mean that the SQL Server itself has, for example, denied a Login but it proves that the physical connection has been setup.

If there was, for example, a firewall blocking or a router between the client and server that was misrouting, we would not see the S/SA/A sequence.

 

After this handshake, we will see few packages being sent, this is the data traffic.

 

Finally we have the following events (27, 28, 29 and 30):

.The client  (xxx.xx.162.187:51712) sends an ACK (A) and a FIN (F) to the server (xxx.xx.162.174:60847). FIN tells the server that we will not send more data, ACK to let the server what connection to close.

.The server (xxx.xx.162.174:60847) sends an ACK (A) to the client (xxx.xx.162.187:51712). This acknowledges the FIN received in the previous step. It is not closing yet since the server may have more data to transfer.

.The server (xxx.xx.162.174:60847) sends an ACK (A) and a FIN (F) to the client (xxx.xx.162.187:51712). FIN tells the client that the server will not send more data.

.The client  (xxx.xx.162.187:51712) sends an ACK (A) to the server (xxx.xx.162.174:60847). ACK means that the client has acknowledged that the server will send more data.

 

This is the teardown of the physical connection.

 

So, if you have a trace then what you should look for is the S/SA/A sequence and the AF/A/AF/A sequence.

If these sequences exist, then you know that the physical network is working. But as mentioned, there could be issues after the physical connection has been setup, for example,

the server is performing slowly but responds, the login fails etc.

 

As an example, if you run a trace on the client and a trace on the SQL Server and you in the client trace can see the S being in the trace taken on the client, but you do not see

the S in the trace on the server. Then the first step would be to check the firewall so that it is not blocking the port in question. The port in question is clearly seen in the trace.

 

NOTE; I you are running with connection pooling enabled, which is default from, for example, a .Net client, the S/SA/A will only show on the first connection.

And the AF/A/AF/A sequence will only be shown when the last connection is closed. This is the nature (and purpose) of connection pooling.

Posted by maspeng | 0 Comments

Format of the initialization string does not conform to specification starting at index

Short one.

 

You are letting your user connect to your trusted SQL Server (fairly common scenario J)

They are connecting using SQL Authentication (it is recommended to use Windows Authentication however) an intermittently the application throws an exception:

 

Unhandled Exception: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 42.

   at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)

   at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)

   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)

   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)

   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)

 

A common scenario here is that the user has provided a string termination character in the username or password and that character is not correctly escaped.

 

So, to illustrate:

 

            string cs = @"Data Source=server\instance;User=username;Password='password;Initial Catalog=database";

            using (SqlConnection con = new SqlConnection(cs))

            {

                try

                {

                    con.Open();

                    Console.WriteLine("We are connected...");

                    con.Close();

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

 

Will give the exception above.

 

So either escape the ‘ correctly (Password=\'password) or do not allow the single quote.

Why is autogenerated UPDATE and DELETE statements complex?

 

Sometimes people ask why the code generated for INSERT/UPDATE/DELETE is so complex, or verbose if you like, why not just operate on the primary key?

To illustrate what we are talking about here, create a table in SQL Server and insert some rows:

 

create table Person (Id int primary key, FirstName nvarchar(50), LastName nvarchar(50), City nvarchar(50), Age int)

 

insert into Person values (1, 'John','Johnson','London', 20)

insert into Person values (2, 'Paul','Paulson','Paris', 30)

insert into Person values (3, 'Sven','Svenson','Stockholm', 40)

 

Then create a new C# console application and insert the following code:

 

    class Program

    {

        static void Main(string[] args)

        {

            string cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";

            using (SqlConnection con = new SqlConnection(cs))

            {

                try

                {

                    con.Open();

                    SqlCommand cmd = con.CreateCommand();

                    cmd.CommandText = "SELECT Id, FirstName, LastName, City, Age FROM Person";

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    SqlCommandBuilder scb = new SqlCommandBuilder(adp);

                   

                    adp.InsertCommand = scb.GetInsertCommand();

                    adp.UpdateCommand = scb.GetUpdateCommand();

                    adp.DeleteCommand = scb.GetDeleteCommand();

 

                    Console.WriteLine("\n{0}", adp.InsertCommand.CommandText);

                    Console.WriteLine("\n{0}", adp.UpdateCommand.CommandText);

                    Console.WriteLine("\n{0}", adp.DeleteCommand.CommandText);

 

                    con.Close();                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

        }

    }

 

And run it, this should give the following output:

 

INSERT INTO [Person] ([Id], [FirstName], [LastName], [City], [Age]) VALUES (@p1, @p2, @p3, @p4, @p5)

 

UPDATE [Person] SET [Id] = @p1, [FirstName] = @p2, [LastName] = @p3, [City] = @p4, [Age] = @p5 WHERE (([Id] = @p6) AND ((@p7 = 1 AND [FirstName] IS NULL)

OR ([FirstName] = @p8)) AND ((@p9 = 1 AND [LastName] IS NULL) OR ([LastName] = @p10)) AND ((@p11 = 1 AND [City] IS NULL) OR ([City] = @p12)) AND

((@p13 = 1 AND [Age] IS NULL) OR ([Age] = @p14)))

 

DELETE FROM [Person] WHERE (([Id] = @p1) AND ((@p2 = 1 AND [FirstName] IS NULL) OR ([FirstName] = @p3)) AND ((@p4 = 1 AND [LastName] IS NULL)

OR ([LastName] = @p5)) AND ((@p6 = 1 AND [City] IS NULL) OR ([City] = @p7)) AND ((@p8 = 1 AND [Age] IS NULL) OR ([Age] = @p9)))

 

So why this ‘complex’ SQL, why not only operate on the primary key? If we want to delete an entry, then “DELETE FROM Person WHERE id = @id” should do it, right?

Well, yes, that SQL would work, but ADO.Net is designed around a disconnected and optimistic concurrency approach.

 

For example, UserA retrieves the data from the Person table. This means that the code will open a connection, get the data, close the connection. Then UserA can edit, add, delete as much

as he or she likes. While UserA is doing this, the data remains unchanged on the server. When UserA is done operating on the data the connection opened, the changes are submitted (using the SQL above)

and the connection is closed. So far so good, but what if UserB also operates on the data at the same time. In other words;

 

UserA retrieves the data for Person with Id 2 to the client.

UserB retrieves the same data (Person with Id 2) to the client and edits it, for example, changes the City and Age.

UserB submits the changes back to the database from UserB client.

UserA deletes the Person with Id 2 and submits the changes back to the database.

 

If we were only operating on the primary key, it would mean that the edited Person (Id 2) would be deleted. UserB would not like this. Or at least be very surprised when the data is no longer there.

By using UPDATE and DELETE statements that operates on all columns in the table, we make sure that when UserA in the scenario above deletes a row, only the row that exactly matches the row

that UserA originally retrieved is deleted. If the exact matching fails (which it will if UserB has changed City and Age) the row is not deleted.

 

This is, for example, how LINQ operates in the background.

 

More info here:

"Optimistic Concurrency (ADO.NET)"

http://msdn.microsoft.com/en-us/library/aa0416cz.aspx

"Generating Commands with CommandBuilders (ADO.NET)"

http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

My take on @@IDENTITY vs SCOPE_IDENTITY()

 

This may be considered to be somewhat of a ‘classic’, but as mentioned, many of the entries in this blog are reminders and/or point of reference for myself.

Which I am of course happy to share. This one is about a question that comes every now and then;

 

“There is something wrong with @@IDENTITY, we have been using this for some time and it has always correctly returned the identity value of the last inserted row.

Now it returns what seems to be random numbers. Also, it seems to work sometimes and fails other times. This must be some sort of a bug”.

 

The short answer here is do not use @@IDENTITY for this purpose, use SCOPE_IDENTITY(). There is a subtle difference between these.

And the behavior described above is usually an effect of using @@IDENTITY instead of SCOPE_IDENTITY() in combination with triggers.

 

As usual, I’ll show this behavior by example since this makes it easier to understand.

 

First we need two tables (with some rows) and a stored procedure that takes care of inserting rows to one of the tables.

This procedure will return the new identity of the row being inserted. The emptype here tells us if the employee is a Manager or not.

So from SQL Server Management Studio, run the following:

 

create table Employee (id int identity, empname nvarchar(100), emptype int)

create table Manager (id int identity, empid int)

 

insert into Employee values ('John', 0)

insert into Employee values ('Paul', 0)

insert into Employee values ('Jane', 0)

insert into Employee values ('Mike', 1)

insert into Employee values ('Mary', 1)

go

 

create procedure InsertPerson(@empname nvarchar(100), @emptype int)

as begin

               insert into Employee values (@empname, @emptype)

               declare @newId int

               set @newId = @@identity

               return @newId

end

 

Check the number of rows in the Employee table (select * from Employee), this should return 5 rows, with 5 being the highest id.

Test the stored procedure:

 

declare @newId int

exec @newId = InsertPerson 'Neil', 1

select @newId

 

This should return 6, recheck the number of rows in the Employee table, this should now return 6 rows. So at this stage all is well.

Create a new C# console application that uses the stored procedure in order to insert new Employees. Code as follows:

 

        static void Main(string[] args)

        {

            string cs = @"Data Source=<your server>;Integrated Security=TRUE;Initial Catalog=<your database>";

            using (SqlConnection con = new SqlConnection(cs))

            {

                try

                {

                    con.Open();

                    SqlCommand cmd = new SqlCommand("InsertPerson", con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add("@empname", SqlDbType.NVarChar).Value = "Pete";

                    cmd.Parameters.Add("@emptype", SqlDbType.Int).Value = 1;

                    cmd.Parameters.Add("@newId", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

 

                    cmd.ExecuteNonQuery();

                    int newId = (int)cmd.Parameters["@newId"].Value;

 

                    Console.WriteLine("The returned id is {0}", newId);

                    con.Close();

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

        }

 

And run it, this should return 7.

Change the name (Pete) to something else and rerun, this should return 8.

 

So now your application works. You can successfully insert new employees and get back the identity of that new row.

Your application runs fine.

Now, later on someone decides that when a new employee is added, we should also insert a row into the Manager table if the Employee emptype is 1 (which is Manager in this example).

This is solved by using a trigger. So in SSMS add the following trigger:

 

create trigger MgrTrigger on Employee after insert

as

if (select emptype from inserted) = 1

begin

               declare @id int

               set @id = (select id from inserted)

               insert into Manager(empid) values(@id)

end

 

Try this by inserting one row into the Employee table which has emptype = 1 and one row which has emptype = 0

 

insert into Employee values ('Timm', 0)

insert into Employee values ('Anna', 1)

 

Now, select * from Employee and select * from Manager.

You should have 10 rows in the Employee table and 1 row in the Manager table, so this seems to work fine.

Go back to the C# application, change the name to something new and rerun it.

 

What do you expect the return value to be now? There are 10 rows in the Employee table, so it should be 11. Right?

Wrong, the returned value is actually 2.

So you check the number of rows in the Employee table, this returns 11 and the last name is the one you added.

 

So where does the value 2 come from?

 

Well, this is where the subtle difference between using @@IDENTITY instead of SCOPE_IDENTITY() comes into play.

If we check the documentation we can see the following:

 

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

 

What this then means is that since we have introduced a trigger that does a conditional insert (if emptype is 1) it means that @@IDENTITY in this case returns the last generated identity value

for the Manager table, not the Employee table. This also means that if you change the C# code to insert an Employee that is not a manager (emptype = 0) it will again seem to work as expected.

 

Whether to use one or the other, I’ll leave that decision to you. This post is only about explaining and exemplifying what is going on.

If you need more guidance, a simple search on SCOPE_IDENTITY and @@IDENTITY will give a lot more information.  

DataTable.GetChanges(DataRowState.Modified) returns NULL

 

The scenario; you have a DataGridView on a Windows Form, you then manually, in code, create a DataTable and set this to be the DataSource of the DataGrid.

You then edit some of the data in the grid and then you need to get hold of a subset of data containing the Modified rows:

 

DataTable modifiedTable = dt.GetChanges(DataRowState.Modified);

int modifiedRowCount = modifiedTable.Rows.Count;

 

This however throws an exception

 

System.NullReferenceException was unhandled

  Message="Object reference not set to an instance of an object.";

 

This is a bit odd; you have clearly edited (Modified) the data, so why are no rows returned from the GetChanges(DataRowState.Modified) method?

 

In short, what you most likely are experiencing is that you have not called AcceptChanges() on the DataTable before you set it to be the DataSource of the DataGrid.

This will cause all rows (including the one you have edited) to be in the Added state. This will take precedence over the Modified state.

 

As usual, it is easier to show by example. Create a new Windows Form project; add a Button, a DataGridView and a ListBox to the Form.

Then replace the generated code with the code below.

 

    public partial class Form1 : Form

    {

        DataTable dt;

 

        public Form1()

        {

            InitializeComponent();

 

            dt = CreateTable();

            dataGridView1.DataSource = dt;

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            foreach (DataRow dr in dt.Rows)

            {

                listBox1.Items.Add(String.Format("Id: {0}, RowState: {1}", dr["id"], dr.RowState));

            }

 

            DataTable modifiedTable = dt.GetChanges(DataRowState.Modified);

            // Row below will fail with the NullReferenceException.

            //int modifiedRowCount = modifiedTable.Rows.Count;

        }

 

        private DataTable CreateTable()

        {

            DataTable dt = new DataTable();

 

            dt.Columns.Add(new DataColumn("id", typeof(int)));

            dt.Columns.Add(new DataColumn("FirstName", typeof(string)));

            dt.Columns.Add(new DataColumn("LastName", typeof(string)));

 

            dt.Rows.Add(new object[] { 1, "Mike", "Mikeson" });

            dt.Rows.Add(new object[] { 2, "John", "Johnson" });

            dt.Rows.Add(new object[] { 3, "Paul", "Paulson" });

 

            return dt;

        }

    }

 

Run it, edit some values in the columns and hit the button. This should produce the following output in the Listbox:

 

Id: 1, RowState: Added

Id: 2, RowState: Added

Id: 3, RowState: Added

 

And this will of course cause the dt.GetChanges(DataRowState.Modified) call to return NULL as specified in the documentation:

 

“If no rows of the desired DataRowState are found, the method returns null”

 

So, what you need to do here is simply to call AcceptChanges(), which will set all rows RowState to Unchanged, before setting it to be the DataSource of the grid.

By doing this, the changes you make will be detected and the rows will go into the Modified state. Simply add this before the DataTable is returned.

 

dt.AcceptChanges();

return dt;

 

Rerun the application make a change, this should now produce the following, allowing the dt.GetChanges(DataRowState.Modified) to return a datatable with one row.

 

Id: 1, RowState: Unchanged

Id: 2, RowState: Unchanged

Id: 3, RowState: Modified

XQuery in SQL Server, some examples

 

I got an XQuery in SQL Server related question from a colleague the other day. I thought I had the answer, but it showed that I forgot most of it.

And of course I hadn’t written down my examples or tests from previous encounters regarding this topic.

 

So this is not intended to be an explanation of XQuery in SQL Server, just a quick reference that can be used in the future, and it will be expanded when there is a need.

 

The examples should be pretty self-explanatory.

 

declare @xml xml

set @xml =

'<RecordStore>

               <Album id="1" category="Rock">

                             <Artist>Rockers Utd.</Artist>

                             <Title>Rock Until You Drop</Title>

                             <ReleaseYear>2005</ReleaseYear>

                             <Price>10</Price>

               </Album>

               <Album id="2" category="Oldies">

                             <Artist>Oldies Inc.</Artist>

                             <Title>Rock Like There Was A Tomorrow</Title>

                             <ReleaseYear>1960</ReleaseYear>

                             <Price>5</Price>

               </Album>

               <Album id="3" category="Rock">

                             <Artist>ShockRockers</Artist>

                             <Title>ShockRock</Title>

                             <ReleaseYear>2000</ReleaseYear>

                             <Price>12</Price>

               </Album>

               <Album id="4" category="Dance">

                             <Artist>Swing Its</Artist>

                             <Title>Shake and Bake</Title>

                             <ReleaseYear>2005</ReleaseYear>

                             <Price>15</Price>

               </Album>

</RecordStore>'

 

-- Using @ signifies that we query on attribute, no @ means value of node

 

-- Get all album titles in Category = "Rock"

select @xml.query('/RecordStore/Album[@category="Rock"]/Title')

-- Get all artists that released albums in 2005

select @xml.query('/RecordStore/Album[ReleaseYear=2005]/Artist')

-- Get the price for the Album called ‘ShockRock’

select @xml.query('/RecordStore/Album[Title="ShockRock"]/Price')

-- Get all albums titles with a price higher than 10

select @xml.query('/RecordStore/Album[Price>10]/Title')

-- Get the titles for the two first albums in list

select @xml.query('/RecordStore/Album[position()<=2]/Title')

 

-- Note that the above examples returns the nodes as XML, if you wish to get

-- hold of a singular value, then use value() method instead, this returns a scalar so it needs a datatype as the second argument.

 

-- Get the price for the Album called ShockRock

select @xml.value('(/RecordStore/Album[Title="ShockRock"]/ReleaseYear)[1]', 'int')

-- Get the title for the album with id 4

select @xml.value('(/RecordStore/Album[@id="4"]/Title)[1]', 'varchar(100)')

 

Running this should produce the following results

 

RockAlbums

--------------------------------------------------------------------------

<Title>Rock Until You Drop</Title><Title>ShockRock</Title>

 

(1 row(s) affected)

 

ReleasesIn2005

--------------------------------------------------------------------------

<Artist>Rockers Utd.</Artist><Artist>Swing Its</Artist>

 

(1 row(s) affected)

 

AlbumPrice

-------------------------------------------------------------------------

<Price>12</Price>

 

(1 row(s) affected)

 

PriceAbove

------------------------------------------------------------------------

<Title>ShockRock</Title><Title>Shake and Bake</Title>

 

(1 row(s) affected)

 

FirstTwoAlbums

------------------------------------------------------------------------

<Title>Rock Until You Drop</Title><Title>Rock Like There Was A Tomorrow</Title>

 

(1 row(s) affected)

 

AlbumPrice

------------------------------------------------------------------------

2000

 

(1 row(s) affected)

 

AlbumByID

------------------------------------------------------------------------

Shake and Bake

 

(1 row(s) affected)

MemberwiseClone by example

 

I not ashamed of admitting that I do not know everything about everything J

 

For example, I didn’t know that well what the method MemberwiseClone() method does.

So, first step was to check the MSDN documentation, found here.

"Object.MemberwiseClone Method"

http://msdn.microsoft.com/en-us/library/system.object.memberwiseclone.aspx

 

This gives the following explanation;

 

The MemberwiseClone method creates a shallow copy by creating a new object, and then copying the nonstatic fields of the current object to the new object.

If a field is a value type, a bit-by-bit copy of the field is performed. If a field is a reference type, the reference is copied but the referred object is not; therefore, the original object and its clone refer to the same object.

For example, consider an object called X that references objects A and B. Object B, in turn, references object C. A shallow copy of X creates new object X2 that also references objects A and B.

In contrast, a deep copy of X creates a new object X2 that references the new objects A2 and B2, which are copies of A and B. B2, in turn, references the new object C2, which is a copy C.

Use a class that implements the ICloneable interface to perform a deep or shallow copy of an object.

 

Which to me is not is that clear and further research didn’t give any good examples, so I created my own in order to see what it does.

And without any attempt to explain what is going on, here it is. Feel free to test as you like.

Hopefully this should be self-explanatory.

 

    class Manager : Employee

    {

        static void Main(string[] args)

        {

            Manager manager1 = new Manager();

            manager1.Name = "Mary Maryson";

            manager1.Age = 45;

            manager1.Department.Name = "Accounting";

            manager1.Department.City = "Stockholm";

 

            Manager manager2 = (Manager)manager1.MemberwiseClone();

 

            Console.WriteLine("Manager 1 -> {0}", manager1);

            Console.WriteLine("Manager 2 -> {0}", manager2);

 

            // Edit the first Manager, notice that this will update the Second managers

            // Department data as well since the reference is cloned, not the object.

            // For the value types, they are not affected.

 

            manager1.Name = "Mike Mikeson";

            manager1.Age = 55;

            manager1.Department.City = "London";

 

            Console.WriteLine();

            Console.WriteLine("Manager 1 -> {0}", manager1);

            Console.WriteLine("Manager 2 -> {0}", manager2);

 

            // And of course, chaning the Department for the manager2 will change the

            // Department data for manager1 as well.

 

            manager2.Name = "Paul Paulson";

            manager2.Age = 30;

            manager2.Department.City = "Paris";

 

            Console.WriteLine();

            Console.WriteLine("Manager 1 -> {0}", manager1);

            Console.WriteLine("Manager 2 -> {0}", manager2);

        }

    }

 

    class Employee

    {

        public string Name = "<default>";

        public int Age = 0;

        public Department Department = new Department();

 

        public override string ToString()

        {

            return String.Format("{0}, age: {1}, is in {2}", Name, Age, Department);

        }

    }

 

    class Department

    {

        public string Name = "<default>";

        public string City = "<default>";

       

        public override string ToString()

        {

            return String.Format("{0} / {1}", Name, City);

        }

    }