How To Use A Database In Your Program

How To Use A Database In Your Program
Many .NET solutions are database driven and so many of us often wonder how to access the database. To help you understand the answer to this question I've provided the following as a quick example of how to retrieve data from a database.

In order to connect to a SQL Server Database using .NET you will need to import the System.Data.Sql Client package into your program.

If you are not connecting to a SQL Server Database, take a look at InsertAlias's article named How to use a database in your program Part II for information on how to connect to other databases.


Connection String
The first thing you'll need to do (after you've created the database) is connect to it. In order to do this you have to define something called a Connection String.

The Connection String is used as a means to locate the database your program will be using. It also contains the information on the type of authentication to use in order to access the database. This means that the connection string may contain a user name and password.

A Connection String is typically formatted as follows:

VB Code Example:
[code=vbnet]Dim connectionStrin g As String ="data source=<nameOfD atabaseServer>; initial catalog=<nameOf Database>;user id=<databaseUse rId>;password=< passwordForData baseUser>" [/code]




C# Code Example:

[code=cpp]String connectionStrin g = "data source=<nameOfD atabaseServer>; initial catalog=<nameOf Database>;user id=<databaseUse rId>;password=< passwordForData baseUser>"; [/code]


Since Connection Strings contain very sensitive information on how to access the database so it is crucial that you protect it (by encryption or other means). If you are implementing a web application, it is a good idea to store this information in the web.config file so that it is easily protected.

SqlConnection
Once you have created a connection string you have to create a SqlConnection object. This object uses the connection string to form a connection to the database for you.

VB Code Example:
[code=vbnet]dim dbCon As SqlConnection
dbCon = New SqlConnection(c onnectionString ) [/code]



C# Code Example:
[code=cpp]SqlConnetion dbCon = new SqlConnection(c onnectionString ); [/code]



SqlCommand
After you have created an instance of a SqlConnection object, you need to create a SqlCommand. This object is used to carry out the sql commands that will be executed by the database.

VB Code Example:
[code=vbnet]Dim sqlCom As New SqlCommand [/code]



C# Code Example:
[code=cpp]SqlCommand sqlCom = new SqlCommand(); [/code]



Once we have an instance of this object we need to set some of its properties.

First of all you have to specify what type of SqlCommand you are going to be executing. This is where you specify if you are using a store procedure or if you'll be supplying a SQL statement to be carried out. In this example we'll cover how to supply your own SQL statement.

You'll have to set the SqlCommand's "CommandTex t" property to be the SQL commands that need to be carried out by the function you're creating.

Once you've set the "CommandTex t" property, you'll have to add the values of any parameters used in the SQL command statement. You do this by setting the SqlCommand's "Parameters " property.

You also have to set the SqlCommand object's "Connection " property to the SqlConnection object you created earlier in order to connect to the database.

For example, if you would like to insert a new contact entry into a table you would set the SqlCommand's "CommandTex t" to be something like:

VB Code Example:
[code=vbnet]
sqlCom.Connecti on = dbCon
sqlcom.CommandT ype = CommandType.Tex t
sqlCom.CommandT ext = "INSERT INTO CONTACT(cID, cAddress, cPhoneNumber) " _ +
"VALUES(@cID,@c Address,@cPhone Number)"
sqlCom.Paramete rs.Add("@cID", SqlDbType.Int). Value = 1234567
sqlCom.Parmaete rs.Add("@cAddre ss", SqlDbType.VarCh ar).Value = "123 Hypoth. Street..."
sqlCom.Paramete rs.Add("@cPhone Number",SqlDbTy pe.VarChar).Val ue="1-800-999-99999" [/code]




C# Code Example:
[code=cpp]sqlCom.Connecti on = dbCon;
sqlcom.CommandT ype = CommandType.Tex t;
sqlCom.CommandT ext = "INSERT INTO CONTACT(cID, cAddress, cPhoneNumber) " +
"VALUES(@cID,@c Address,@cPhone Number)";
sqlCom.Paramete rs.Add("@cID", SqlDbType.Int). Value = 1234567;
sqlCom.Parmaete rs.Add("@cAddre ss", SqlDbType.VarCh ar).Value = "123 Hypoth. Street...";
sqlCom.Paramete rs.Add("@cPhone Number",SqlDbTy pe.VarChar).Val ue="1-800-999-99999"; [/code]



Executing Your SQL Commands
After you've set up everything you can carry out your SQL commands on the database.
To do this you have to:

• first open a connection to the the database
• and then execute the SQL command
• and finally close the connection

VB Code Example:
[code=vbnet]Try
dbCon.Open()
sqlcom.ExecuteN onQuery
dbCon.Close()
Catch ex As Exception
End Try [/code]




C# Code Example:
[code=cpp]try
{
dbCon.Open();
sqlcom.ExecuteN onQuery();
dbCon.Close();
}
catch(Exception ex)
{} [/code]


In the above code we used the ExecuteNonQuery method to execute the SQL command because a SQL command that updates a table does not return any results. The ExecuteNonQuery method returns the number of rows that were updated. You can use this to determine if any rows were updated by checking to see if sqlcom.ExecuteN onQuery > 0.

If you are executing a SQL command that returns a result, such as executing a SELECT statement you will have to use a different method. The SqlCommand's ExecuteReader method returns a SqlDataReader object that contains all of the records retrieved after executing the SQL command.

The following snippet of code shows you how to store the results into a SqlDataReader object and how to access the data.

VB Code Example:
[code=vbnet]Try
Dim dr As SqlDataReader
dbCon.Open()
dr = sqlcom.ExecuteR eader
If dr.HasRows = True Then
txt_clientID.Te xt = CType(dr("cID") ,Integer).ToStr ing()
txt_clientAddre ss.Text = CType( dr("cAddress"), String)
txt_clientPhone Number.Text = CType(dr("cPhon eNumber"),Strin g)
End If
dr.Close()
dbCon.Close()
Catch ex As Exception
End Try [/code]



C# Code Example:
[code=cpp]try
{
SqlDataReader dr;
dbCon.Open();
dr = sqlcom.ExecuteR eader();
if(dr.HasRows == True)
{
txt_clientID.Te xt = ((Integer) dr["cID"]).ToString();
txt_clientAddre ss.Text = (String) dr["cAddress"];
txt_clientPhone Number.Text = (String) dr["cPhoneNumb er"];
}
dr.Close();
dbCon.Close();
}
catch(Exception ex)
{} [/code]

Once you are finished with your SqlDataReader object be sure to close it to clean up.

If your Sql command returns more than one row, you will have to loop through the data reader to retrieve all of the information.

For example the following will add all of the "clientID's " to the TextBox txt_clientID:

VB Code Example:
[code=vbnet]While dr.Read
txt_clientID.Te xt = txt_clientID.Te xt + CType(dr("cID") ,Integer).ToStr ing()
End While [/code]




C# Code Example:
[code=cpp]while(dr.Read() )
{
txt_clientID.Te xt = txt_clientID.Te xt + ((Integer)dr["cID"]).ToString();
}[/code]


Other Notes
The objects used to connect to a database implement the iDisposable interface. Because of this it is Strongly advisable to define a Dispose() method that will properly handle the disposal of any connections. Be sure to look up how to implement this method to properly clean up your memory. It really helps to manage your resources.

Hope you find this useful!

-Frinny