Contents tagged with ADO.NET

  • Creating Collections of Entity Objects using Reflection

    In my last blog posts I have been showing you how to create collection of entity objects using code that is custom for each table and object you create. Well, if you use a little reflection code you can shrink this code quite a bit. Yes, we all know that reflection is slow and probably should be avoided in most cases. What I have found out is that loading over 6200 product records into an entity collection still takes less than a second when using Reflection. So, I will leave it up to you to decide which way you wish to go.

    Read more...

  • Creating Collections of Entity Objects using LINQ

    As discussed in my last two blog posts you have a variety of ways to create collections of Entity classes. Using a DataSet or DataTable is a little slower than using a DataReader, but in most cases the difference is in milliseconds so in a real world app this difference would not be a killer. For instance, in my sample data I was loading 6,261 records from the Product table discussed in the last blog post and it took 45 milliseconds on average to load those records into an entity collection using a DataTable. It took only 30 milliseconds on average to load the same entity collection using a DataReader. The rendering of that data would probably take longer than that, so you can choose which one you wish to use.

    Read more...

  • Using The ConnectionStringBuilder class

    Building a connection string from scratch can sometimes be a little daunting when you do not know the exact syntax. Of course, you can always visit www.connectionstrings.com and find some great help there. In lieu of this you can also use the ConnectionStringBuilder class. Each of the ADO.NET providers supplies a version of this class that will build a connection string for you. Below is an example of how to use this class.

    VB.NET
    Imports System.Data.SqlClient

    Private Sub CreateConnectionString()
      Dim builder As New SqlConnectionStringBuilder

      builder.DataSource = "(local)"
      builder.InitialCatalog = "Northwind"
      builder.UserID = "user1"
      builder.Password = "P@ssw0rd"

      MessageBox.Show(builder.ConnectionString)
    End Sub

    C#
    using System.Data.SqlClient;

    private void CreateConnectionString()
    {
      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

      builder.DataSource = "(local)";
      builder.InitialCatalog = "Northwind";
      builder.UserID = "user1";
      builder.Password = "P@ssw0rd";

      MessageBox.Show(builder.ConnectionString);
    }

    This is a pretty easy class to use. You can just fill in the basic information such as the DataSource, InitialCatalog, the UserId and Password and it will create a connection string for you. The output from the above code will be: "Data Source=(local);Initial Catalog=Northwind;User ID=user1;Password=p@ssword".

    To add on additional keywords for your connection string you may use the Add method. This method takes the keyword and the value and will add them in the appropriate format to your connection string.

    As mentioned, each ADO.NET data provider supplies one of these classes. For example, if you are using Oracle, you would use the System.Data.OracleClient namespace, then use the OracleConnectionStringBuilder.

    The ConnectionStringBuilder class allows you to parse the individual elements of a connection string and put them into the corresponding properties in the ConnectionStringBuilder. In the following example you take an existing connection string like the one shown in the code below and place it into the ConnectionString property of the ConnectionStringBuilder. The ConnectionStringBuilder will then break it into the appropriate properties.

    VB.NET
    Private Sub ParseConnectionString()
      Dim cnn As String
      Dim builder As New SqlConnectionStringBuilder

      cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd"

      builder.ConnectionString = cnn

      MessageBox.Show(builder.DataSource)
      MessageBox.Show(builder.InitialCatalog)
      MessageBox.Show(builder.UserID)
    End Sub

    C#
    private void ParseConnectionString()
    {
      string cnn;
      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

      cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd";

      builder.ConnectionString = cnn;

      Debug.WriteLine(builder.DataSource);
      Debug.WriteLine(builder.InitialCatalog);
      Debug.WriteLine(builder.UserID);
    }

    So the next time you have a connection string that you wish to extract the elements from, or you need to build a connection string, consider using the ConnectionStringBuilder class. I would not recommend using this in a production application as I can't imagine that the performance would be that great, but for figuring out a connection string, this works great.

    Good Luck With Your Coding,
    Paul Sheriff

    ** SPECIAL OFFER FOR MY BLOG READERS **
    Visit http://www.pdsa.com/Event/Blog for a free eBook on "Fundamentals of N-Tier".

    Read more...

  • Cloning a DataRow

    I can't even tell you how many times over the last few years I have had to clone a row from one DataTable to another DataTable. To make this easier, I created a method that I can call at anytime to create this new DataRow and return a new DataTable back to me. I have another overload of this method that I can also pass in the new DataTable. In ADO.NET there is no easy way to take a single row from an existing DataTable and copy it to another DataTable. The major reason why it is not so easy is you can not add a DataRow that exists in one DataTable to another DataTable. As a result you must create a new DataRow object and copy all of the values from the original DataRow into this new one. You can then create a new DataTable (or use one with the same structure), and add that DataRow to that new DataTable. Below is a method that you can call to accomplish the copying of a single row from one DataTable to a new DataTable.

    C# Code

    private DataTable CloneDataRow(DataTable dtOld, int rowNumber)
    {
      DataRow dr;
      DataTable dtNew;

      dtNew = dtOld.Clone();

      dr = dtNew.NewRow();

      dr.ItemArray = dtOld.Rows[rowNumber].ItemArray;

      dtNew.Rows.Add(dr);

      return dtNew;
    }

    VB.NET Code

    Private Function CloneDataRow(ByVal dtOld As DataTable, ByVal rowNumber As Integer) As DataTable
      Dim dr As DataRow
      Dim dtNew As DataTable

      dtNew = dtOld.Clone()

      dr = dtNew.NewRow()

      dr.ItemArray = dtOld.Rows(rowNumber).ItemArray

      dtNew.Rows.Add(dr)

      Return dtNew
    End Function

    I hope you find this method as helpful as I have found it over the years.

    Good Luck With Your Coding,
    Paul Sheriff

    ** SPECIAL OFFER FOR MY BLOG READERS **
    Visit http://www.pdsa.com/Event/Blog for a free eBook on "Fundamentals of N-Tier".

    Read more...