Client For SQL Server Databases in C# .NET

• Download source files - 2 Kb
• Download demo project - 48 Kb

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. <o:p>

 <o:p>

Understanding Connectivity To SQL Server in .NET<o:p>

 <o:p>

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. <o:p>

SqlConnection<o:p>

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<o:p> 

SqlDataAdapter<o:p>

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.<o:p> 

SqlCommand<o:p>

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:

/// <summary>
/// Provides a connection to the desired SQL Database
/// </summary>
SqlConnection m_SQLConnector = new SqlConnection();

/// <summary>
/// Provides an adapter for extracting data a from SQL Database
/// </summary>
SqlDataAdapter m_SQLAdapter;   

//Init a new SQL command
SqlCommand insertCommand = new SqlCommand();
//Feed the input query
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.<o:p>

<o:p> 

Major Operations in SQLServerClient<o:p> 

This section simply defines the few basic functions & methods of connection to the SQL server<o:p>

Open Connection<o:p>

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.

//Setup Connection Object with new conenction string
this.m_SQLConnector = new SqlConnection("Initial Catalog=" +
                      this.m_DatabaseName + ";Data Source=" + 
                      this.m_DataSource + ";Integrated Security=SSPI;");
//Open Connection
this.m_SQLConnector.Open();

Close Connection<o:p>

The code snippet shown below closes the already open connection to the desired SQL database. <o:p>

//Close Connection<o:p>

this.m_SQLConnector.Close();<o:p>

Run a Select Query<o:p>

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.<o:p>

//Create data table for holding results<o:p>

DataTable returnDataObject = new DataTable();<o:p>

<o:p> 

                       <o:p>

//Compile a new SQL select command<o:p>

SqlCommand selectCommand = new SqlCommand( iQuery );<o:p>

                               <o:p>

//Set the existing SQL connection<o:p>

selectCommand.Connection = this.m_SQLConnector;<o:p>

<o:p> 

//Initialize the SQL adapter                  <o:p>

this.m_SQLAdapter = new SqlDataAdapter();                            <o:p>

//Apply the compiled select command<o:p>

this.m_SQLAdapter.SelectCommand = selectCommand;<o:p>

                       <o:p>

//Fill the results data table<o:p>

this.m_SQLAdapter.Fill(returnDataObject);<o:p> 

Run a Non Select Query<o:p>

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. <o:p>

//Init a new SQL command<o:p>

SqlCommand insertCommand = new SqlCommand();<o:p>

                               <o:p>

//Feed the input query<o:p>

insertCommand.CommandText = iQuery;<o:p>

                       <o:p>

//Feed the existing connection<o:p>

insertCommand.Connection = this.m_SQLConnector;<o:p>

                               <o:p>

//Execute the non select query<o:p>

insertCommand.ExecuteNonQuery();<o:p>

<o:p> 

Integrating the SQLServerClient in Your Code<o:p>

<o:p> 

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<o:p>

/// <summary><o:p>

/// Object of the SQLServerClient  <o:p>

/// </summary><o:p>

SQLServerClient m_SQLClient = new SQLServerClient();<o:p> 

Defining Server , Database & Opening Connection<o:p>

//New database name<o:p>

this.m_SQLClient.DataBase = “DATABASE NAME”<o:p>

<o:p> 

//New datasoucre name<o:p>

this.m_SQLClient.DataSource = “DATA SOURCE NAME”<o:p>

<o:p> 

//Reset & Reopen Connection<o:p>

this.m_SQLClient.resetAndReopenConnection();<o:p> 

Running a Select Query<o:p>

//Run the query written in the query text box<o:p>

DataTable result = this.m_SQLClient.runSelectQuery(“DESIRED QUERY”);<o:p> 

Running a Non Select Query<o:p>

//Run the query written in the query text box<o:p>

DataTable result = this.m_SQLClient.runNonSelectQuery(“DESIRED QUERY”);<o:p> 

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.