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;
            }
        }
    }
}
Advertisements

Me, MyClass or MyBase

Me

According to MSDN the Me keyword provides a way to refer to the specific instance of a class or structure in which the code is currently executing. Me behaves like either an object variable or a structure variable referring to the current instance. Using Me is particularly useful for passing information about the currently executing instance of a class or structure to a procedure in another class, structure, or module.

Let’s say that in Visual Basic .Net when you create a form you want to explicitly create the default constructor.
You may be probably doing this to add some extra-initialization to the constructor. Another reason could be to make it obvious that a constructor exists as otherwise a default constructor is provided implicitly and transparently.

Now when you just typed:

Public Sub New()

and pressed Enter, Visual Studio will automatically insert code by adding some comments and a call to

InitializeComponent()

This method is responsible of instantiating all the controls that you placed on the form at design time and many others … so you want to have it called from any other constructors you may end up with. This method would have been called automatically by the default provided constructor.

So, to recap, the default constructor that you just typed, looks like this:

Public Sub New()
' This call is required by the designer.
 InitializeComponent()
' Add any initialization after the InitializeComponent() call.
End Sub

Let’s say we need a constructor that takes a parameter. To preserve having InitializeComponent() called but to not have to call it explicitly we call the default constructor from this new one  :

Public Sub New(param as String)
‘Calls the parameterless constructor thus InitializeComponent gets called as well
 Me.New() txtParam.Text = param
End Sub

If we need another constructor that take two parameters we do the same only this time we call the constructor that takes one parameter:

Public Sub New(param as String, param2 as String)
‘ same thing; calling the previous constructor to preserve the initialization
 Me.New(param)
 txtParam2.Text = param2
End Sub

Again, we avoided duplicating the code that calls the InitializeComponent and some other code the previous parameterized constructor was executing by simply invoking that constructor through the use of the Me keyword.

What about MyClass? Well, with this one it is a bit more complicated.

According to MSDN the MyClass keyword behaves like an object variable referring to the current instance of a class as originally implemented. MyClass is similar to Me, but all method calls on it are treated as if the method were NotOverridable.

One thing for starters is that you would use this keyword in a base class’ code not in the inheritor. Why? Because it offers you a mechanism to write methods in that base class that circumvent polymorphism and guaranties a client a certain original/base functionality even when called from a derived class instance.

Let’s say we have:

Class baseClass
Public Sub testMethod()
 MsgBox("Base class string")
 End Sub
End Class

and we decided we need new functionality for testMethod but we need to preserve the baseClass functionality. We change the testMethod in the base class to Overridable and redefine it in a derived class with Overrides (this will give you the polymorphic behaviour):

Class baseClass
 Public Overridable Sub testMethod()
  MsgBox("Base class string")
 End Sub
End Class

Class derivedClass : Inherits baseClass
 Public Overrides Sub testMethod()
  MsgBox("Derived class string")
 End Sub
End Class

Now, if we instantiate a derivedClass

Dim testObj As derivedClass = New derivedClass()
testObj.testMethod()

and we call testMethod we will se the “Derived class string” being displayed. Even if we cast or declare the testObj instance to or as a baseClass, still the overridden method will execute. Even if you define another method on the base class that calls testMethod

Class baseClass
 Public Overridable Sub testMethod()
  MsgBox("Base class string")
 End Sub
 
 Public Sub useTestMethod()
  ‘ The following call uses the calling class's version,
  ‘ even if that version is an override.
  Me.testMethod()
 End Sub
End Class

the testMethod that will execute will be the one on the derivedClass (as long as the instance is a derivedClass)

So, how can we get the original testMethod execute on a derivedClass instance. Well, we can try to re-write the base class like this:

Class baseClass
 Public Overridable Sub testMethod()
  MsgBox("Base class string")
 End Sub

 Public Sub useBaseTestMethod()
  ' The following call uses this version and not any override.
  MyClass.testMethod()
 End Sub
End Class

and have the client call useBaseTestMethod instead of testMethod. The difference is that calling testMethod on a derivedClass instance you will get derivedClass functionality but when you call useBaseTestMethod you will get the baseClass original testMethod to execute and only because of the special way of invoking it: prefixed by the MyClass keyword.

So , MyClass allows the designer of a class to say: I want this method call, when it invokes certain other methods of this class, to always invoke whatever implementation (or portion of implementation) I wrote for those in this class and never ever even think of executing the override versions.

And MyBase?

Considering the previous example if you would want to create a method in the derivedClass that performs both the functionality from the baseClass and the new one in from the derived class. If the testMethod of the derivedClass changed to:

Class derivedClass 
 Inherits baseClass
Public Overrides Sub testMethod()
 MyBase.testMethod()
 MsgBox("Derived class string")
 End Sub
End Class

The addition of the MyBase.testMethod() allows us to execute the baseClass functionality first so we get both message boxes displayed. So, if we have the possibility of calling MyBase.testMethod to obtain baseClass functionality, what good is MyClass then?

Let us not forget that what we were talking about was calling base functionality from the baseClass and you cannot use MyBase in the baseClass. More, MyClass is about enforcing and assuring the consumer that when he calls an inherited method M1 that in turn calls (using MyClass) another inherited method M2 that was in the meantime overridden, the base class code will execute always for M2. MyClass never invokes base class functionality but is merely used in the base class to set an execution path in stone avoiding the situation when polymorphism would redirect the execution to the new version of M2 thus changing M1’s expected functionality.

Cheers.

Silverlight and RIA services – overriding an attribute set in a base class.

Recently I found myself in the following situation. I had let’s say this class: DerivedClass inheriting from a BaseClass on the server side of things.

One of the properties of the BaseClass has the [Required] attribute applied.

public class BaseClass{

[Required]
public string Name { get; set; }

}

It just happen that in my DerivedClass I needed some more validation to be done so I would like to go for a [CustomValidation] attribute with its own custom validation method. More, I don’t need the constraint of the [Required] attribute anymore (if you remember the [Required] attribute, by default, implies the property should not have an empty or null value) so I want to revert its effect.

You can write the following code:

using System.ComponentModel.DataAnnotations

[MetadataType(typeof(DerivedClassMetaData)]
public class DerivedClass : BaseClass{

}

public class DerivedClassMetaData{

[Required(AllowEmptyStrings=true)]
[CustomValidation(typeof(SomeValidationClass), “ValidationMethod”)]
public string Name{ get; set; }

}

public class SomeValidationClass{

public static ValidationResult ValidationMethod(object value, ValidationContext validationContext){
}
}

The most important thing in the code above is the [MetadataType] attribute which is basically allowing me to attach meta data type to my derived class and basically readjusting some of its inherited attributes. One thing I did was to redefine the [Required] attribute to undo the effect of the similar definition on the BaseClass. Secondly I attached a custom validation to the Name property.
A very good resource on custom validation is
here.

On the client side, in the code generated the Name property looks like this:

[System.ComponentModel.DataAnnotations.CustomValidationAttibute(typeof(SomeValidationClass), @”ValidationMethod”)]
[System.ComponentModel.DataAnnotations.Required(AllowEmptyStrings=true)]
public string Name{

}

The whole SomeValidationClass code is brought over from the business side and included in the generated code so the CustomValidationAttribute can find its arguments.

Cheers.

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.

System.Data.ConstraintException: Failed to enable constraints.

(continued from the title) One or more rows contain values violating non-null, unique, or foreign-key constraints.

I was working on a small project and I used the DataSet designer to quickly obtain an adapter and a strongly typed table.
I parameterized the table adapter so I can provide a different connection string and a different table for the SELECT query.

As always when you use the designer to initially generate the adapter and data table you have to point it to a table.
The designer inspects then the table and builds all the good things: table adapter, strongly typed data table and row, and so on.
The problem is that it hardcodes the connection string and the name of the table in the SELECT command and I would really like to re-use the generated code to obtain data from other tables too. So, I parameterized the adapter to accept a different table knowing well enough that the other tables have to have identical schema/structure.
I guess otherwise, the adapter.GetData() or adapter.Fill() methods will fail, probably with the above message – the one in the title.

If you read the error message in the title it doesn’t give you the impression it would be thrown for a schema discrepancy. Oh, but it does and that’s what confuses you as you don’t suspect a schema difference to be the problem! And we are not necessarily talking of a big discrepancy like missing a field or even a field of a different type.
No, the one that I encountered was caused by a different width of the field. The autogenerated MaxLength for one of my fields (10) was smaller than the width of the same field in a second table that I tried to use the adapter on. The field in the second table was 30 characters wide. This is what caused the exception to be thrown.

So, to get into some details I will start by saying that I am trying to access dbf tables somewhere in a folder. I am using the Visual FoxPro OLEDB driver which has the ability to treat a folder as a database and the dbf files in the folder as tables of the database. The Visual FoxPro ODBC driver does the same but needs a preconfigured entry in the ODBC connections of the system whereas with the OLEDB you can have the connection string built at run-time.

Anyway with MyDataAdapter and FileDataTable classes already generated, I wrote a few lines of code below to test that the adapter and my parameterizations work well enough to fill data from more than the initial table that I used to generate the classes from.

MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt;
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE1.DBF”);
try {
// attempt to fill the DataTable
sdt = sta.GetData();
}
catch (Exception ex) {
System.Diagnostics.Debug.WriteLine(ex.Message);
throw;
}

Now, this code works well when trying to fetch the records from FILE1.DBF (the original file) but when I tried to do the same from a structurally identical (almost) FILE2.DBF an exception was thrown: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

This message is not helping at all in finding precisely what the problem is and more, there was really no constraints to be concerned with and all the columns of the FileDataTable were defined to support nulls. Anyway, in an effort to see if I can get some more information I did a bit of digging and I was led to the Datatable.GetErrors() method. I thought it is worth a try as I had nothing else to go on with anyway.

So, I made the first modification to the code above by adding a DataRow[] dr = sdt.GetErrors(); line in the catch clause. That really didn’t work, as the line was causing an exception complaining that the sdt variable is null. Pretty obvious I said: “I am trying to call GetErrors on a table that doesn’t exist as it failed to be instantiated because an exception.” So, what I had to do is to instantiate an empty DataTable manually ahead of time, and then, in the try block use adapter.Fill(dataTable) instead of the GetData() syntax. This way by the time we call Fill(dataTable), dataTable is already an instantiated object which we can call GetErrors() on and obtain our array containing the DataRows with the problem.

MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt = new Test.FileDataTable();
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE.DBF”);
try {
// attempt to fill the DataTable
sta.Fill(sdt);
}
catch (Exception ex) {

// get all the rows involved in errors
DataRow[] dr = sdt.GetErrors();
throw;

}

By expanding the first row in the now populated dr array, and looking at the RowError property, I can see:   “Column ‘prod’ exceeds the MaxLength limit.”
So this is when I checked the table and saw that the prod field was 30 chars wide, clearly more than the 10 chars MaxLength specified in the FileDataTable definition.
Apparently out of two tables which I believed identical the first one had a field narrower than the second table.

If you want to make this go away you could edit the FileDataTable’s problem field definition by modifying its MaxLength to be roomy enough.
Another way is to disable the enforcement of constraints at run-time by setting DataSet.EnforceConstraints = false, with the downside that the data in wider fields will be truncated and lost.

The last code addition is to accommodate this second approach. As the data tables don’t have their own EnforcedConstraints property we have to instantiate a DataSet.  We then add the table to it and then set the EnforceConstraints = false; This will cause the ignoring of the discrepancy between the field width in the data set definition and the width of the field that exists in the physical table. The code will not error out again but the value of the field will be truncated.

The final code could look something like this:

DataSet ds = new DataSet(“Test”)
MyTableAdapter sta = new MyTableAdapter();
Test.FileDataTable sdt = new Test.FileDataTable();
ds.Tables.Add(sdt);
ds.EnforceConstraints = true;
// setting the new connection for the adapter
sta.SetConnectionString(new OleDbConnection(“Provider=VFPOLEDB.1;Data Source=” + @
\\TESTSERVER\c$\FOLDER));
// setting the table we will use the adapter against
sta.SetTable(“FILE.DBF”);
try {
// attempt to fill the DataTable
sta.Fill(sdt);
}
catch (Exception ex) {

// get all the rows involved in errors
DataRow[] dr = sdt.GetErrors();
throw;
}

Cheers.

Talking about TFS 2010 Dashboard Permissions when running in SharePoint Server 2010 – Layers & Layers of Security

  This is a very interesting and revealing article about one of the many very misterious sides of the integration between Team Foundation Server Sharepoint. It is not exhaustive but for the amount of brand new information it contains, I surely not regret the 10 minutes that I spent reading it .

TFS 2010 Dashboard Permissions when running in SharePoint Server 2010 – Layers & Layers of Security

Good luck

Using foreach to loop through the elements of an enumeration in .Net (C#)

If you find yourself in need of looping through the elements of an enumeration
and do something for each of them then you may want to take a look at the following code:

enum Elements {
        Manganese,         Chrome,         Gold,         Argon
}
// we cannot loop through the enumeration but we can through an array of strings
// representing the name of the elements of that enumeration,
// which is what Enum.GetNames() method will gladly give us if we pass the type of the enumeration

foreach (string elementName in Enum.GetNames(typeof(Elements))) {
    // then, if we need to create an instance of the enumeration type based on the
    // current elementName we use Enum.Parse() method which takes:
    //  - the type of the enumeration and
    //  - the elementName (a string);
    //  Enum.Parse() returns <object> so we have to explicitly cast it to our enumeration type

    Elements element = (Elements)Enum.Parse(typeof(Elements), elementName);

}
so, the above foreach loops four times and in each iteration you have access to an element
of the enumeration as its native type and as a string representation – its name.
Cheers.