Introduction
Connecting to Databases on the SQL Server is an elementary task often repeated needlessly by developers. The SQLServerClient is an assembly which provides a very easy to use and organized mechanism to access databases on the SQL Server. It comes with a demo windows forms application in C# .NET which gives a sample of the working of the SQLServerClient. In the demo application the user can setup the SQL server data source and the desired database. Following which the user can run select or other queries and commands and see the results and the corresponding messages.
Understanding Connectivity To SQL Server in .NET
Defined below is a combination of selected components and code fragments which shall aid beginners in quickly understanding the basic know how of connecting to the SQL Server in the .NET framework.
SqlConnection
A SqlConnection object represents a unique session to a SQL Server data source. In the case of a client/server database system, it is equivalent to a network connection to the server. Given below is a tiny code snippet which defines how an object of the SqlConnection class has been created in the SqlServerClient
SqlDataAdapter
The SqlDataAdapter, serves as a bridge between a Dataset and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source. Given below is a tiny code snippet which defines how an object of the SqlDataAdapter class has been created in the SQLServerClient.
SqlCommand
The SqlCommand class Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. In the context of the SQLServerClient it has been used to generate SqlCommand from the string query object that has been obtained. A tiny code snippet follows:
SqlConnection m_SQLConnector = new SqlConnection();
SqlDataAdapter m_SQLAdapter;
SqlCommand insertCommand = new SqlCommand();
insertCommand.CommandText = iQuery;
Thus the conjunction of the above defined class objects is used to create an organized approach to access databases on the SQL Server.
Major Operations in SQLServerClient
This section simply defines the few basic functions & methods of connection to the SQL server
Open Connection
The code snippet shown below opens a connection to the desired SQL database. The desired SQLServer & the name of the database are specified in the SQL Conenction String.
this.m_SQLConnector = new SqlConnection("Initial Catalog=" +
this.m_DatabaseName + ";Data Source=" +
this.m_DataSource + ";Integrated Security=SSPI;");
this.m_SQLConnector.Open();
Close Connection
The code snippet shown below closes the already open connection to the desired SQL database.
//Close Connection
this.m_SQLConnector.Close();
Run a Select Query
To run a select query we need to use the SqlComand component. We firstly define a Datatable or a Dataset to hold the results of the query. Then a SqlCommand is generated by feeding in the query in string format and the already open SqlConnection. Then the SqlAdapter is used fill the given Datatable or Dataset with the results of the query.
//Create data table for holding results
DataTable returnDataObject = new DataTable();
//Compile a new SQL select command
SqlCommand selectCommand = new SqlCommand( iQuery );
//Set the existing SQL connection
selectCommand.Connection = this.m_SQLConnector;
//Initialize the SQL adapter
this.m_SQLAdapter = new SqlDataAdapter();
//Apply the compiled select command
this.m_SQLAdapter.SelectCommand = selectCommand;
//Fill the results data table
this.m_SQLAdapter.Fill(returnDataObject);
Run a Non Select Query
Non select queries include any other query such as INSERT , UPDATE , IDENTITY_INSERT. Always set the INDENTITY_INSERT as ON before trying to insert any records into a table. The code fragment shown below is self explanatory.
//Init a new SQL command
SqlCommand insertCommand = new SqlCommand();
//Feed the input query
insertCommand.CommandText = iQuery;
//Feed the existing connection
insertCommand.Connection = this.m_SQLConnector;
//Execute the non select query
insertCommand.ExecuteNonQuery();
Integrating the SQLServerClient in Your Code
As mentioned earlier this is quite an easy to use component. Defined below are a few operations that can be performed with one or two lines of code using the SQLServerClient.
Declaring an Object
/// <summary>
/// Object of the SQLServerClient
/// </summary>
SQLServerClient m_SQLClient = new SQLServerClient();
Defining Server , Database & Opening Connection
//New database name
this.m_SQLClient.DataBase = �DATABASE NAME�
//New datasoucre name
this.m_SQLClient.DataSource = �DATA SOURCE NAME�
//Reset & Reopen Connection
this.m_SQLClient.resetAndReopenConnection();
Running a Select Query
//Run the query written in the query text box
DataTable result = this.m_SQLClient.runSelectQuery(�DESIRED QUERY�);
Running a Non Select Query
//Run the query written in the query text box
DataTable result = this.m_SQLClient.runNonSelectQuery(�DESIRED QUERY�);
Conclusion
As always all code available in this article is free to use and is not copyrighted. You are free to integrate the SQLServerClient in your application and or make any changes to it. This article is not an attempt to teach the experts out there just a guide to beginner. So please RATE IT ACCORDINGLY and rate it well. On a more serious note a reiteration that the SQLServerClient is a simple & efficient mechanism to access SQL databases in your .NET Application.