Click here to Skip to main content
12,697,525 members (27,334 online)
Click here to Skip to main content
Add your own
alternative version

Stats

55.2K views
2.6K downloads
18 bookmarked
Posted

Client For SQL Server Databases in C# .NET

, 10 Dec 2005
Rate this:
Please Sign up or sign in to vote.
SQLServerClient is a simple assembly which provides a very organized way to connect to & edit SQL Tables. It also contains a demo Form showing its usage

Sample Image - SQLServerClient.jpg

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. <?xml:namespace prefix = o /><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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Siddhartha Batra
Web Developer
India India
Siddharth Batra is a first year graduate student, pursuing his Master's of Science in Computer Science at Stanford University. His research interests are in the domains of computer vision, digital image processing, visualizations and applied computer graphics.

He also posseses a keen interest in the .NET Framework and the C# language

You can contact Siddharth Batra at

http://www.siddharthbatra.info/

You may also be interested in...

Comments and Discussions

 
GeneralSimple but helpful demo app Pin
thorstd25-Mar-07 7:58
memberthorstd25-Mar-07 7:58 
QuestiondataSet and dataBase Pin
cosmindp6-Jul-06 11:14
membercosmindp6-Jul-06 11:14 
GeneralRated Pin
ByteGhost11-Dec-05 19:21
memberByteGhost11-Dec-05 19:21 
GeneralRe: Rated Pin
code_taker11-Dec-05 21:04
membercode_taker11-Dec-05 21:04 
GeneralRe: Rated Pin
Mike Melnikov11-Dec-05 21:35
memberMike Melnikov11-Dec-05 21:35 
GeneralThanx Pin
Siddhartha Batra11-Dec-05 23:43
memberSiddhartha Batra11-Dec-05 23:43 
GeneralRe: Rated Pin
ByteGhost12-Dec-05 13:51
memberByteGhost12-Dec-05 13:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 10 Dec 2005
Article Copyright 2005 by Siddhartha Batra
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid