SQL programming using. NET
submitted by
User Level
Date of submissionTimothy vanoverintermediate02/01/2001
Download Project:Storedprocs.zip MB KB
The StoredProc.exe program can is used to show several different data objects, some draw backs of each and some interestin G things, which are not currently working in the Beta 1 release quite the way. This document would give a high level overview of the objects used.
To run the program for you must have access to a SQL Server. I am using SQL Server but feel this can is used just as easily on 6.5 or 7.0.
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 as, as, differences, need.
System.Data.ADO can is used to connect and manipulate data from multiple data sources, including Access, Oracle, SQL Sever , etc. This library has a connection object, 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 the Provider are 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 are the password for the login.
This allows generic loosely coupled objects to is reused in making connections and manipulating data to multiple diverse D ATA sources.
System.Data.SQL can is used to connect and manipulate SQL Server databases. The Provider in the connection string must are omitted as this is encapsulated inside the SqlConnection object. A connection string using this object would appear as follows: 揑 nitial; 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 a expensive. For example to maintain a n-tier projects connections we would not want thousands of connections to the SQL Server all Once. This would take and eventually crash the server. The ideal situation is to have a pool of connections so can be reused from multiple to connect, make data clients tion, and then exit the connection. At the very least we want to maintain statelessness to our client, application so, not are. This would also mean, we would not want opened and orphaned connections on the server.
The
Profileris a tool it installed with SQL Server and allows a wide variety of performance conditions to being examined so UEs can be resolved this 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 are being retrieved from the database using the System.Data.SQL li Brary. This is call comes from the "List button on the" StoredProcs.exe program.
As you can third rows there are 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 a adhoc query to select the user stored procedures metadata contained in the pubs data Base 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 are being to the returned. This are then added to the "list box by walking through and reading the" Data into the list box. An alternative to this could also is binding the results of this query to the list box. The actual screen would 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'll notice that's the top data grid are 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 to 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 is populated with a dataset of the ' return value. If you are have selected an inserts, update, or delete stored procedure and have entered a value for the WHERE clause then 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, which are for INSERT, UPDATE, or DELETE statements.
The actual code is simplistic in which it does a parameters-refresh to get the parameters of the stored, and load s them into a data grid so this can edit the values to send for testing a stored procedure.
Several other points should is made.
1. The SQLCommand object has a Parameters object, which won't do a Parameters resetparameters, even though the Met Hod is there. I assume that's May to be fixed in a future version, as it is a popular method for development and the System.Data.SQL O Bject is tuned for SQL Server.
2. Connections need to being 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 into the pubs database and step through the code. It would also is wise to learn to the connections of the profiler to monitor, and other items that would affect both Queries and your application.
4. From the testing I have seen there are several items that should are improved when Beta2 are released in the Adocone Nction as. This object or the SQLOLEDB provider has a tendency to keep the connection open until the ' program ' closed even with the Close code above.
Another tool at your disposal, if you are running NT, or Windows am the event log. This can help you with debug your application and find areas where a problem has occurred during the program operation. On the catch methods there was a class, which I have used to encapsulate the writing of the "to" event log. You can actually create your own logs for a application or use the one this is present. The EventLog object should is played with as "We are now given immense power easily and which required some API calls to doing so Me of what is now as easy as the following code. There are some good examples in the HowTo to the SDK for doing I than for this project.
Internal class ErrorLog
{
<summary>
This is 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 for you to some understanding on SQL with. Net. Until the next project is ready,
Timothy A. Vanover MCP, MCT, MCSD, MCDBA
