SQL Programming using .NET

SQL Programming using .NETSubmitted ByUser LevelDate of SubmissionTimothy VanoverIntermediate02/01/2001Download Project: StoredProcs.zip   192 KB
The StoredProc.exe program can be used to show several different data objects, some draw backs of each and some interesting things, which are not currently working in the beta 1 release quite the way that may be expected. This document will give a high level overview of the objects used.  
To run the program you must have access to a SQL Server. I am using SQL Server 2000, but feel that this can be used just as easily on 6.5 or 7.0.  
In the Framework all data access components are derived from the System.Data object. These include System.Data.ADO and System.Data.SQL. There are several similarities in these as well as differences that need explained.  
System.Data.ADO can be used to connect and manipulate data from multiple data sources, including Access, Oracle, SQL Sever, etc. This library has a connection object that can establish connections to a data source through an OLE DB provider. A connection string might appear as follows:
揚rovider=sqloledb;Initial Catalog=pubs;Data Source=Dev;Uid=sa;Pwd=pass"
As you can see the Provider is the OLE DB provider for connecting to a SQL Sever. The database selected is pubs. The SQL Sever name in this instance is Developer. The Uid is the Login name, and the Pwd is the password for the login.  
This allows generic loosely coupled objects to be reused in making connections and manipulating data to multiple diverse data sources.
System.Data.SQL can only be used to connect and manipulate SQL Server databases. The Provider in the connection string must be omitted as this is encapsulated inside the SQLConnection object. A connection string using this object would appear as follows:  揑nitial Catalog=pubs;Data Source=Dev;Uid=sa;Pwd=pass?nbsp;
The SQL library has been optimized for connections with SQL Server to help with scalability. This is a key issue in many enterprise developments where connections to a database server are an expensive resource. For example to maintain an n-tier projects connections we would not want thousands of connections to the SQL Server all at once. This would take resources and eventually crash the server. The ideal situation is to have a pool of connections that can be reused by multiple clients to connect, make data manipulation, and then exit the connection. At the very least we want to maintain statelessness to our client application so that the resources are not drained. This would also mean that we would not want opened and orphaned connections existing on the server.  
The Profiler is a tool that is installed with SQL Server and allows a wide variety of performance conditions to be examined so that issues can be resolved that may occur.
Information is gathered with a trace set up to monitor the connections to the server created in the Profiler. Below is an example of a stateless connection where data is being retrieved from the database using the System.Data.SQL library. This call comes from the List button on the StoredProcs.exe program.

As you can see by the first and third rows there is a Login, Execution, and a Logout with the trace. Also the duration column shows a value of 130. In the actual program we have made an adhoc query to select the user stored procedures metadata contained in the pubs database sysobjects system table. The actual method call is as follows:
public SQLDataReader SelectProcs(ref SQLConnection cnn)
{
       SQLDataReader dr = null;
       string query = "SELECT Name FROM sysobjects WHERE xtype = 'P' And Category = 0";
       SQLCommand cmd = new SQLCommand(query,cnn);
  
       try
       {
              cmd.CommandType = CommandType.Text;
  
              cmd.Execute(out dr);
       }
       catch(Exception e)
       {
              ErrorLog errLog = new ErrorLog();
                           
              errLog.LogError(e.Message, "Method: SelectProcs");     
       }
                     
       return(dr);
}
The actual connection object is being maintained by the calling method and the data is being returned to the caller. This data is then added to the list box by walking through and reading the data into the list box. An alternative to this could also be binding the results of this query to the list box. The actual screen will now display the stored procedures defined as a type of user as follows.
  
To view the parameters of one of the stored procedures select one from the list box and click the View button. For this example I抣l use the usp_AuthorCol stored procedure. You will notice that the top data grid is now populated with metadata about the stored procedure.

The index column is the ADOCommand.Parameters index value for the parameter listed. The name is the actual name of the parameter. The Type is the actual data type mapped to the Framework data types. The length is a length of the data type. If this had been a string, or for example in SQL a varchar or char, this value would determine the length of the parameter value. The value column is the actual value.
You can select to execute the stored procedure and the bottom grid will be populated with a dataset of the return value. If you have selected an insert, update, or delete stored procedure and have entered a value for the where clause then you can enter a value in the value field and the execute button for executing the stored procedure. You can select the option button to indicate a return value from a select statement or other, which is for insert, update, or delete statements.  

The actual code is simplistic in that it does a parameters-refresh to get the parameters of the stored procedure, and loads them into a data grid so that you can edit the values to send for testing a stored procedure.
Several other points should be made.
1.      The SQLCommand object has a Parameters object, which will not do a Parameters ResetParameters, even though the method is there. I assume that this may be fixed in a future version, as this is a popular method for development and the System.Data.SQL object is tuned for SQL Server.
2.      Connections need to be closed with something like the following.  
if(cnn.State == DBObjectState.Open)
{  
            cnn.Close();
            cnn.Dispose();
   }
3.      Run the Authors.sql sql script to install the usp_Author queries in the pubs database and step through the code. It would also be wise to learn to use the profiler to monitor the connections, and other items that will affect both your queries and your application.
4.      From the testing I have seen there are several items that should be improved when Beta2 is released in the ADOConenction as well. This object or the sqloledb provider has a tendency to keep the connection open until the program is closed even with the close code above.  
Another tool at your disposal, if you are running NT, or Windows 2000 is the event log. This can help you debug your application and find areas where a problem has occurred during the program operation. On the catch methods there is a class, which I have used to encapsulate the writing of data to the event log. You can actually create your own logs for an application or use the one that is present. The EventLog object should be played with as we are now given immense power easily, which required some API calls to do some of what is now as easy as the following code. There are some good examples in the HowTo of the SDK for doing much more than I needed for this project.
internal class ErrorLog
{
/// <summary>
///           This method writes to the application log
/// </summary>
/// <param name="errMessage"> </param>
/// <param name="errSource"> </param>
public void LogError(string errMessage, string errSource)
       {
          EventLog errLog = new System.Diagnostics.EventLog("Application",".",errSource);
  
          errLog.WriteEntry(errMessage, EventLogEntryType.Error);
       }
              
}  
Hope this helps you get some understanding on SQL with .Net. Until the next project is ready,
Timothy A. Vanover MCP, MCT, MCSD, MCDBA