SqlIndex - Lesson in "Connected" ADO.NET

• Download the SqlIndexer Project - 5.56 Kb

Table of Contents

• Introduction
• Disclaimer
• The Details
  • Libraries Used
  • Private Members
  • Main Method
  • Start Method
  • Index Method
• Other Applications Of
• Conclusion

Introduction

The other day I needed to add a tremendous amount of indexes spanning over 50 tables to a SQL Server database. All I had was a list of column names that needed to be indexed in any table that the column appears in. This is an unusual situation for me to be in. I felt overwhelmed when thinking of manually opening each table to see if it has the columns in question then adding an index to each one. This would have taken me several hours.

Then I remembered I have an algorithm I use to replicate and/or move data between two or more databases. I figured I could apply this algorithm to my problem. It took me 15 minutes to apply and test it. I felt this was a good use of my time since it saved me hours of manual work. After I was done, I thought this could be a simple example of how to use the algorithm, and to demonstrate a method for using ADO.NET in a connected way.

The only assumption made is that you are knowledgeable in C# programming and are familiar with the .NET Framework. The article does not assume that you know ADO.NET. The areas that can be skipped by those familiar with ADO.NET are noted. However, even though I try to explain the ADO.NET portions, I do so only to the extent used by this application. This is not an ADO.NET tutorial, there are plenty of good ones on this site, and this article does not attempt to be one.

Disclaimer

This example application is just that, it is an example of one way to do something. It is not meant as the only way or even the best way. I am sure there are other ways to solve this problem, all of which could be better than the way shown in this article. The article is meant to be used as a tool to teach someone how-to do something, and is not meant to be an end-all solution. If you have a better, more efficient way to solve this problem, please share it with the world by writing and posting an article on Code Project. Then, please create a new thread in this article to point us to your newly posted article here on Code Project.

Code Project is a great place to share ideas on multiple ways to implement something or to solve a problem. However, I do not welcome threads in my articles that only serve to flame someone, start an argument, or boast their holier-than-thou ideas.

The Details

First, let's start with a very simple break-down of the algorithm:

• Loop through the column names
• Build a query dynamically to get all tables that contain the current column
• Execute the query to get the tables
• Loop through the tables
• Build a non-query dynamically to create an index for the current column in the current table
• Execute the non-query to create the index
• Return to the top of the main loop if there are any more column names to process

You will see the different parts as we progress through the code, specifically the Index() method. Now, let's do a simple break-down of the class and what it does.

All the code is contained in a single class called SqlIndexer in the namespace SqlIndexer and it is well documented. This class contains a Main method which instantiates itself and calls the private Start() method. The Start method handles the opening and closing of connections, starting a transaction, deciding if the transaction should be committed or rolled back, and primary exception handling. After it has opened the connections and begins a transaction, it calls the private Index() method. The Index() method is where the above described algorithm is implemented. It (hopefully) exits gracefully with all the new indexes created.

Libraries Used

This library is needed because the application uses StringBuilder objects to build SQL commands.

using System.Text;

These libraries are the two ADO.NET libraries.

using System.Data;
using System.Data.SqlClient;

Private Members

This section is documented and is self-explanatory. All the members are private and only used by this class.

// Connection used to OUTPUT from SQL Server
private SqlConnection   oSqlConnOUT     = null;

// Connection used to INPUT to SQL Server
private SqlConnection   oSqlConnIN      = null;
private SqlTransaction  oSqlTransaction = null;

// SQL Server Connection Details
private String sSqlServer    = "localhost";
private String sSqlDatabase  = "DataBaseName";
private String sSqlUser      = "sa";
private String sSqlPassword  = "password";

private String[] sColumnsToIndex  = new String[]
                 {
                     "userid", "date", "split", "logid", "groupid",
                     "anid", "time", "jacksquatid", "ohyeah", "type",
                     "keyno", "item", "itemid", "value", "whatid"
                 };

// Set the Prefix for the Index
// If done manually SQL Server gives a default of IX_
// Therefore I do this as well, change to match your naming conventions.
private readonly String sIndexPrefix = "IX_";

Main Method

static void Main(string[] args)
{
  new SqlIndexer().Start();

  // Used when running inside of VS comment out if run from command line
  Console.WriteLine("\n\n...Press [ENTER] To Close Window...");
  Console.ReadLine();
}

This is very simple, it instantiates the SqlIndexer and calls its Start() method. When all is done, there are a few lines I like to use when running from Visual Studio to keep the console window open. They are not needed, especially if you are running from the command prompt. Feel free to comment these two lines out, they do not affect the operation of the application.

Constructor

This section is documented and should be self-explanatory if you have experience with ADO.NET, if so, feel free to skip the next paragraph.

/// <summary>
/// When SqlIndexer is created it creates the needed connections.
/// </summary>
private SqlIndexer()
{
  oSqlConnOUT = new SqlConnection("Database=" + sSqlDatabase +
                   ";Server=" + sSqlServer +
                   ";User ID="  + sSqlUser +
                   ";Password=" + sSqlPassword + ";");

  oSqlConnIN  = new SqlConnection("Database=" + sSqlDatabase +
                   ";Server=" + sSqlServer +
                   ";User ID="  + sSqlUser +
                   ";Password=" + sSqlPassword + ";");
}

Here is where the class instantiates the SqlConnection classes used to connect to a SQL Server database. There are several options available for use in the connection string, and all can be found in the MSDN documentation under SqlConnection.ConnectionString Property. Here, I provide the database I want to connect to, and a database server where it is located which can be a resolvable name or an IP address, a SQL Server user name, and the password for this user. Two connections are created, one is used with a SqlReader to get information out of the database. The other is used to execute Non-query SqlCommands.

Start Method

This section is documented and should be self-explanatory if you have experience with ADO.NET. If so, feel free to skip the remaining paragraphs in this section, jumping right to the Index Method section.

/// <summary>
/// Starts the whole process of opening connections to the database,
/// and beginning a transaction. Also handles if the transaction is
/// committed or rolled back.  Then finally it makes sure that all
/// the open connections get closed.
///
/// It hands off the SQL communication details to the Index method.
/// </summary>
private void Start()
{
  try
  {
    // Lets get those connections open before we use them
    oSqlConnIN.Open();
    oSqlConnOUT.Open();

    // Start a transaction
    oSqlTransaction = oSqlConnIN.BeginTransaction();

    Index();

    // If all is well then commit
    oSqlTransaction.Commit();
  }
  catch (Exception e)
  {
    // Show the message and the stack trace
    Console.WriteLine(e.Message + "\n");
    Console.WriteLine(e.StackTrace);

    // Rollback if there are any problems
    if (oSqlTransaction != null)
      oSqlTransaction.Rollback();
  }
  finally
  {
    // Always make sure all connections get closed!
    if (oSqlConnIN != null)
      oSqlConnIN.Close();

    if (oSqlConnOUT != null)
      oSqlConnOUT.Close();
  }
}

Here is where the class actually opens the two connections to the database. After that, it begins a transaction. A Transaction is a process that is used by a database to maintain its integrity. To do this, all SQL operations are batched into a transaction, and then you can decide to execute all of them completely or none of them at all.

With ADO.NET, you call BeginTransaction on your connection object to get a transaction. You then assign this transaction object to all the commands you execute on this connection. Once you begin a transaction, all commands after this on that connection have to be part of the transaction until you decide to Commit() or Rollback() the transaction. Basically, if you want to use the same connection for non-transaction commands, you have to do them before your begin it, or after you completed it. This is why I use two connections to the same database. I need to be able to read from the database at the same time I am executing transaction based commands.

After we have begun a transaction, we call the Index() method. We get into this in the next section. If all went well and there where no exceptions, we commit the transaction. If there where any exceptions, it outputs the exception information to the console and then rolls back the transaction. This will prevent any of the commit commands to be executed. The reason I am using transactions here is that this needs to be done in an "all or nothing" fashion. If it gets halfway through and then gets an error, I would not be able to run it again with out removing the new indexes. I know I can modify the code to ignore and keep going if the index already exists. The reason why I don't is since only a generic SQL Server message is provided, I would have to parse the SqlMessage to find what kind of error occurred. That is not a good way to handle the problem. The message is not guaranteed to be the same in all versions of SQL Server, and future implementations of .NET. Then if you want to modify this to run on another DBMS, it may not work.

Next, a finally clause is used to guarantee that the connections will be closed when they are no longer needed.

Index Method

This section is documented and should be self-explanatory if you have experience with ADO.NET. If so, feel free to skip the remaining paragraphs in this section, jumping right to the Conclusion section.

/// <summary>
/// Loops through each column name in the string array sColumnsToIndex.
/// Then finds all of the tables with that contain
/// a column the current column name.
/// Then loops through all the tables creating an index for current column.
/// </summary>
private void Index()
{
  for (int i = 0; i < sColumnsToIndex.Length; i++)
  {
    // Keep the user informed of what is going on
    Console.WriteLine("\n\nCurrent Column: " + sColumnsToIndex[i]);

    StringBuilder oSelectTableNamesQuery = new StringBuilder();

    // The select statement to find all tables that contain a column with the
    // current column name. Need to NOLOCK the sysobjects table since the
    // SqlReader will be reading this table while the create index process is
    // going this is one of the tables it updates. Since we are not getting a
    // column that is being updated it "should" be safe.
    //
    // WARNING: This will only work on SQL Server 7.0 or higher
    //          These system tables exist in SQL Server DBMS
    //          Each DBMS has its own way of handling this information
    oSelectTableNamesQuery.Append("SELECT DISTINCT so.name 'table'");
    oSelectTableNamesQuery.Append("FROM sysobjects so (NOLOCK)");
    oSelectTableNamesQuery.Append("INNER JOIN syscolumns sc on so.id = sc.id ");
    oSelectTableNamesQuery.Append("WHERE so.xtype ='u' ");
    oSelectTableNamesQuery.Append("AND sc.name like '");
    oSelectTableNamesQuery.Append(sColumnsToIndex[i]);
    oSelectTableNamesQuery.Append("'");

A StringBuilder is used to build the query that will select all the tables in the database that contain the current column name.

SqlCommand oGetTablesCommand =
      new SqlCommand(oSelectTableNamesQuery.ToString(), oSqlConnOUT);

SqlDataReader oSqlReader = null;

try
{
  oSqlReader = oGetTablesCommand.ExecuteReader();
}
catch (SqlException e)
{
  // Display the query then re-throw the exception.
  Console.WriteLine("\n\n" + oSelectTableNamesQuery.ToString() + "\n\n");

  throw e;
}

Here, it has created a SqlCommand using the previously built query. Then, it obtains a SqlReader by calling the ExecuteReader() method on the SqlCommand object. It does this in a try-catch block so that it can display the query to the console for examination, then it re-throws the exception so that it can be caught by the catch in the Start() method. This try-catch block is not really needed because of the main catch-all in the Start() method. However, I want to see the query in case the exception is related to an error with it.

object oCurrentTable = null;

// Keep the user informed of what is going on
Console.Write("Tables:");

while (oSqlReader.Read())
{
  StringBuilder oCreateIndexNonQuery = new StringBuilder();

  // Get the next table name from the reader
  oCurrentTable = oSqlReader.GetValue(0);

  // Creating the CREATE INDEX query for each
  // current column in the current table.
  oCreateIndexNonQuery.Append("CREATE INDEX ");
  oCreateIndexNonQuery.Append(sIndexPrefix);
  oCreateIndexNonQuery.Append(sColumnsToIndex[i]);
  oCreateIndexNonQuery.Append(" ON ");
  oCreateIndexNonQuery.Append(oCurrentTable);
  oCreateIndexNonQuery.Append(" (");
  oCreateIndexNonQuery.Append(sColumnsToIndex[i]);
  oCreateIndexNonQuery.Append(")");

Another StringBuilder is used to build the non-query that will create an index for the current column and the current table.

SqlCommand oCreateIndexCommand =
     new SqlCommand(oCreateIndexNonQuery.ToString(), oSqlConnIN);

// Link this command to the transaction.
oCreateIndexCommand.Transaction = oSqlTransaction;

try
{
  oCreateIndexCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
  // Display the query then re-throw the exception.
  Console.WriteLine("\n\n" + oCreateIndexNonQuery.ToString() + "\n\n");

  throw e;
}

Here, it has created another SqlCommand using the previously built non-query. It then assigns the transaction object to the command. Then it calls ExecuteNonQuery() method on the SqlCommand object. Again, it does this in a try-catch block so that it can display the query to the console for examination, then it re-throws the exception so that it can be caught by the catch in the Start() method. This try-catch block is not really needed because of the main catch-all in the Start() method. However, I want to see the query in case the exception is related to an error with it.

      // Keep the user informed of what is going on
      Console.Write(" " + oCurrentTable);
    }

    // Need to close Reader before next loop
    oSqlReader.Close();
  }

  // Make sure we clean up after the last Write method.
  Console.WriteLine("");
}

Finally, it closes the SqlReader and exits the method. There are Console.Write() calls. I am only using this to provide feedback for the user as to the progress of the application. Again, this is not needed for the application to function. I just like to show progress to let the user know that something is going on. You can remove these lines if you do not need this functionality.

Other Applications Of

The algorithm implemented in the Index() method can be applied to Data Replication, Custom Movement of Data, and much more. I would also recommend following the basic methodology used in the Start() method.

You would still use two connections, just that they would be to different databases, maybe even different servers, or even mixing ODBC with SQL, or OLEDB. To make the algorithm more reusable, you would want to pass in arguments into the method that you would need. Like a collection of tables to replicate, and any data transformation information you would need. Then you would need to adjust the commands you build to be more or less dynamic based on the information you pass in. Regardless, the basic principles would still remain the same.

Conclusion

I hope you find this article useful and maybe even learned something. Feel free to start a thread if you have questions about something in this article.

I apologize in advance for any typos in this article.