ADO.NET Connections, Commands, Parameters

I wanted to write this because I’ve seen some code written in the last few years and while not all bad, some even working as intended but lacking a certain clean look and occasionally a bit inefficient.

As an example to help me explain it I wrote a little console application, you can see the code at the end of this post.

The example is using a localdb version of the MS-SQL Server where a Test database was created.
Table1 is a table on Test with two fields: Id – an autoincremented primary key and Color – an nchar(10) field.

The example inserts two records in the table, one by calling Method1() and the other one by calling Method2().

Method1() is the method that contains the code that I mentioned above is not so clean and so on.
Method2() is the cleaner alternative which I hope you’ll adopt from now on.

The comments in the code should be sufficient to explain why the Method2() code can be written that way while keeping the functionality of the code identical with the one in Method1() and looking much cleaner.

I will however summarize the improvements the new code brings:

  • Instances of classes like SqlConnection and SqlCommand are disposable (implement IDisposable) so we can use the using block/scope which upon the exit of the block automatically calls Dispose() on the variables defined at the using declaration line. Of course what the implementation of the Dispose() method is doing in a particular class is different from one to another. That is where the MSDN page for the respective class can shed some light and if that’s not enough than you will have to use a tool like Reflector or JustDecompile (from Telerik) to take a peek at how Dispose is implemented for a particular class. For SqlConnection the Dispose() method will close the connection among other things so you don’t have to bother anymore with that if you tied your SqlConnection instance in a using block.
    • Above I said “the variables defined at the using declaration line” and it is not a mistake. The using syntax allows more than one variable tied to the using statement (separated by commas) as long as they are of the same type. So basically you can declare and instantiate 2 different connections with one using. However, when declaring multiple variables we have to specify the SqlConnection type explicitly as implicit-typed variables have to be declared individually (e.g.: var m =1, n=2 is not allowed)
  • There are a few cases when you can avoid having to declare SqlParameter variables just because you need to reference them later. The Add() and AddWithValue() methods return the SqlParameter they are creating so at least one more property can be set. Often, that’s enough to set the Value when Add() was used or set the SqlDbType when AddWithValue() was used.
  • It is a good idea to always specify the SqlDbType for a parameter because if there’s a type mismatch with the value set for parameter it may be caught as an InvalidCastException before the SQL statement is executed.
  • The same goes for the size of the parameter which can be specified as the third argument when calling the Add() method. It is important because if we would use Method1() to insert a string longer than 10 characters in the [Color] field SQL will raise a truncation error that comes back to our application as a SqlException. However, if we use Method2 which specifies the size of the field (10), the .Net code will make sure the value of the parameter is truncated before being sent to the SQL Server thus ensuring the success of the execution of that SQL command.
  • You can always use the name of a parameter as an index value to reference the corresponding SqlParameter instance in the SqlCommand.Parameters collection; it may be a good way of avoiding to have to declare a new variable just to store a reference that will be reused once or twice.
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            Method1();
            Method2();
        }
        private static void Method1()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\ProjectsV12;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;");
            SqlCommand comm = new SqlCommand(@"INSERT INTO Table1 (color) VALUES (@color)", conn);
            try
            {
                SqlParameter pColor = comm.Parameters.Add("@color", SqlDbType.NChar);
                pColor.Value = "Red";

                // Sometimes we see AddWithValue being used but without specifying
                // the SqlDbType so they can avoid having to declare a new SqlParameter 
                // comm.Parameters.AddWithValue("@color", "Red");
                
                // other times we created a SqlParameter variable just because needed to be referenced later
                Console.WriteLine("Param: {0} = {1}", pColor.ParameterName, pColor.Value);

                conn.Open();

                if (conn.State == ConnectionState.Open)
                {
                    comm.ExecuteNonQuery();
                }
                // Don't need to check if connection is open or any other state, 
                // Close() can be called many times, no exception will be generated
                conn.Close(); 
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                comm.Dispose();
                //conn.Dispose(); Close and Dispose on a connection are functionally equivalent
            }
        }
        private static void Method2()
        {
            try
            {
                using (var conn = new SqlConnection(@"Data Source=(localdb)\ProjectsV12;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30"))
                {
                    conn.Open();
                    if (conn.State != ConnectionState.Open) return;

                    using (var comm = new SqlCommand(@"INSERT INTO Table1 (color) VALUES (@color)", conn))
                    {
                        // both Add() or AddWithValue return the SqlParameter so we can set at least one other property in the same line
                        comm.Parameters.Add("@color", SqlDbType.NChar, 10).Value = "Blue";
                            
                        // we don't have to create a variable to reference a Parameter, we can do it by its index
                        Console.WriteLine("Param: {0} = {1}", comm.Parameters["@color"].ParameterName, comm.Parameters["@color"].Value);

                        comm.ExecuteNonQuery();
                    } // here the SqlCommand instance will be disposed automatically
                }// here the connection will be Disposed/Closed automatically
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
    }
}

Creating a linked server dynamically and executing a query against it.

Sometimes from a SQL script running on server A we need to execute a query against a database on a different SQL server B or against any other kind of database. If this other database can normally be linked to your main SQL server A than you can definitelly do that at design time and solve your problem. The question is can you do it at run time?

I mean what if you do something in a SQL script on your main server A and you have to reach out and fetch some information from another database on a server whose names are passed as parameters or read from a config file.

The script below shows how to link a Sql database on another server dynamically at runtime and then issue a query against a table from that database.

DECLARE @v_ServerName varchar(100)
DECLARE @qry varchar(500), @v_DataBaseName varchar(50)

IF NOT EXISTS (SELECT 1 Where Exists (Select [SERVER_ID] From sys.servers WHERE [Name]=@v_ServerName))
EXEC sp_addlinkedserver @v_ServerName, N'Any', N'SQLNCLI', @v_ServerName;
SET @qry = 'SELECT * FROM [' + @v_ServerName + '].[' + @v_DataBaseName + '].[dbo].[TableName] WHERE

EXECUTE (@qry);

Try it and let me know it worked for you.

How to become a sysadmin on a SQLEXPRESS 2008 installation when you are not the original installer, SQL authentication is not enabled but you are a Windows administrator.

I have gotten a new job and I am in that phase where you start slowly to setup your machine, the environment etc.

One of the things I had to do is to set up the database for the project my team works on. The database has to be configured locally on a 2008R2 SQLEXPRESS instance (installed already by an system administrator and obviously under a different Windows account).

Long story short when I attempted to create a database I got access denied. Any other attempt to gain administrative rights over the database server failed. Did I mentioned the account I was logged on is part of the local Administrators? Yes, that too and even though I launched the management studio in admin mode as well, it did not help. It seemed as the database server did not really care that I am a mighty administrator on my machine Open-mouthed smile.

Reading some articles out there I learnt that the MS-SQL 2008 version does not include the Windows administrators in the by now very select and limited group of sysadmins. It seems to be true at least in my case. This means that if the person that installs the database server does not make you specifically a sysadmin you will not be automatically a full privileged user over the database server only because you are a Windows admin.

There is a way you can make yourself a sysadmin in a situation like mine where somebody else installed the server, left you out and you do not have access to the original installer.

You have to launch the database server in “single-user” mode which is when your Windows administrator account can act as a sysadmin and you can add it as a login to the database and make it part of the sysadmins role.

  • Start by stopping the SQL server and close the Management Studio
  • Launch a command prompt as an Administrator.
  • Then launch the SQL Server Configuration Manager; select SQL Server services; right click on the SQL Server (SQLEXPRESS) service and click Properties.
  • Select the Service tab and double click on Binary Path; you should get a drop down containing the command that launches that specific SQL instance:
  • image
  • Copy it and paste it in the previously opened command prompt window; add an extra –m to the parameters: “”c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe” –m –sSQLEXPRESS”;
  • Press Enter and it will look like Linux is about to start. When it stops and one of the last lines logged to the window is saying “SQL Server is now ready for client connection” it means you succeeded and your “single-user” instance is running.
  • Launch the SQL Server Management Studio as an Administrator and do whatever you want because you are now a sysadmin; basically you would like to add your Windows account as a SQL login; add the sysadmin server role to this login;
  • When you are done with it return to the command prompt window and press Ctrl+C and respond Y to shutdown the SQL service.
  • Go back to the Management Studio and start your instance the usual way and test. You should be able to do what you please.

Hope this helps.

Cheers.