Before the tutorial, I was told to post this here as there is no C# or .Net Tutorial section so no flamage please
In todays tutorial we will be discussing the basics of interacting with SQL Server in C#. These code ideas will be from a Windows Application, but with some (if any) modifications they can be used in a C# Web Application. There are also in .Net 2.0.
Add the following reference to your project in order to use the ConfigurationManager,
System.Configuration.
If your project don't already have one add an app.config file, in it you will be storing your connection string information for later retrieval. Your connection should look like this
CODE
< connectionStrings >
<add name="YourConnectionName"
connectionString="Persist Security Info=False;
Data Source=YourDatabase;
Initial Catalog=YourTable;
Integrated Security=SSPI;
Trusted_Connection=TRUE;
Application Name=SampleCSharpApplication"
providerName="System.Data.SqlClient" / >
< /connectionStrings>
We will be referencing this connectionString in our next code snippet. Add new Class and name it DataAccess, then at the top of you class add these using statements
CODE
using System.Data.SqlClient;
using System.Configuration;
This is needed for working with SQL Objects in the .Net Framework. The first code you will add to this class is a method to retrieve the connectionString from the app.config file. The method is this
CODE
public static string GetConnectionString(string strConnection)
{
//variable to hold our connection string for returning it
string strReturn = new string("");
//check to see if the user provided a connection string name
//this is for if your application has more than one connection string
if (!string.IsNullOrEmpty(strConnection)) //a connection string name was provided
{
//get the connection string by the name provided
strReturn = ConfigurationManager.ConnectionStrings[strConnection].ConnectionString;
}
else //no connection string name was provided
{
//get the default connection string
strReturn = ConfigurationManager.ConnectionStrings["YourConnectionName"].ConnectionString;
}
//return the connection string to the calling method
return strReturn;
}
Now, this method does several things:
- Checks to see if a connection name was provided
- If one was provided get the connection information for that name
- If one wasn't provided it returns the default connection information (as set by you the developer)
- Return the connection information to the calling method
Most people are retrieving data for populating a control, whether it be a DataGrid, A DataRepeater, etc. The easiest way I've found to populate these controls is with a BindingSource. According to the MSDN, this is the definition of a Binding Source:
QUOTE
The BindingSource component serves many purposes. First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources. This is accomplished by attaching the BindingSource component to your data source using the DataSource property.
More:
BindingSource So to retrieve a BindingSource for a Data control I use this method
CODE
/// <summary>
/// Returns a BindingSource, which is used with, for example, a DataGridView control
/// </summary>
/// <param name="cmdSql">"pre-Loaded" command, ready to be executed</param>
/// <returns>BindingSource</returns>
/// <remarks>Use this function to ease populating controls that use a BindingSource</remarks>
public static BindingSource GetBindingSource(SqlCommand cmdSql)
{
//declare our binding source
BindingSource oBindingSource = new BindingSource();
// Create a new data adapter based on the specified query.
SqlDataAdapter daGet = new SqlDataAdapter(cmdSql);
// Populate a new data table and bind it to the BindingSource.
DataTable dtGet = new DataTable();
//set the timeout of the SqlCommandObject
cmdSql.CommandTimeout = 240;
dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture;
try
{
//fill the DataTable with the SqlDataAdapter
daGet.Fill(dtGet);
}
//check for errors
catch (Exception ex)
{
MsgBox(ex.Message,"Error in GetBindingSource");
return null;
}
//set the DataSource for the BindingSource to the DataTable
oBindingSource.DataSource = dtGet;
//return the BindingSource to the calling method or control
return oBindingSource;
}
Heres how the GetBindingSource method works:
- Pass the method your SqlCommand Object from your method (this is already created and "pre-loaded" when you pass it)
- Create a SqlDataAdapter Object based on this SqlCommand Object
- Create and fill a DataTable Object with the SqlDataAdapter (this executes your SqlCommand Object)
- Set the DataSource property of your BindingSource Object to you now filled DataTable Object
- Return the BindingSource Object to the calling method for binding
The last thing to add to your DataAccess class is a way to open and close the SqlConnection based on its current state. I created a method called HandleConnection, and you pass a SqlConnection, the check its state and act accordingly.
CODE
public static void HandleConnection(SqlConnection oCn)
{
//do a switch on the state of the connection
switch (oCn.State) {
case ConnectionState.Open: //the connection is open
//close then re-open
oCn.Close();
oCn.Open();
break;
case ConnectionState.Closed: //connection is open
//open the connection
oCn.Open();
break;
default:
oCn.Close();
oCn.Open();
break;
}
}
This covers 3 important methods, and separates your data work from your form, also saves a lot of coding down the road. For the next part you can either create a new Class (my choice) or put it right in your form .cs file. First I will show you how to return all the rows in a table, this is used for displaying all the data of course and is the easiest thing for a new programmer to do.
CODE
public static BindingSource GetAllRecords()
{
//create a SqlConnection Object, use the GetConnectionString from the DataAccess class to retrieve the connection string
System.Data.SqlClient.SqlConnection cnGetRecords = new SqlConnection(DataAccess.GetConnectionString("YourConnectionName"));
//create a SqlCommand Object for executing our SQL Stored procedure
System.Data.SqlClient.SqlCommand cmdGetRecords = new SqlCommand();
//String variable that will hold the name of your Stored Procedure
string sSQL = "GetAllRecords"; //This is the name of my stored procedure
//SqlDataAdapter that will be used to fill your DataTable
System.Data.SqlClient.SqlDataAdapter daGetRecords;
//DataTable that will be bound to your DataGrid
System.Data.DataTable dtGetRecords;
//Set your SqlCommand Object Properties
cmdGetRecords.CommandText=sSQL; //This tells it what SQL to execute
cmdGetRecords.CommandType = CommandType.StoredProcedure; //this tells your SqlCommand Object that its executing a Stored Procedure
try{
//set the state of the SqlConnection Object
DataAccess.HandleConnection(cnGetRecords);
//create BindingSource to return for our DataGrid Control
System.Windows.Forms.BindingSource oBindingSource = DataAccess.GetBindingSource(cmdGetRecords);
//now check to make sure a BindingSource was returned
if (!oBindingSource == null){
//return the binding source to the calling method
return oBindingSource;
}
else { //no binding source was returned
//let the user know the error
throw new exception("There was no BindingSource returned");
}
}
//check for any errors
catch (exception ex){
MsgBox(ex.Message, "Error Retrieving Data");
}
//now we close the connection
cnGetRecords.Close;
}
Lets dive into this method. First, like the GetBindingSource it returns a BindingSource Object, this can be bound to a DataGrid, a DataRepeater or most other data controls. First thing you do is create your connection using the GetConnectionString method in the DataAccess class. You then create a couple more objects:
- A SqlCommand Object -> This will perform your query execution
- A SqlDataAdapter -> This will be used to fill your dataset
- A DataTable -> Once filled this will be bound to your BindingSource for binding to your control
- A String (sSQL) -> This will hold the name of your stored procedure
NOTE: Always use stored procedure to prevent the risk of a SQL Injection AttackThis method will return a populated BindingSource so you can bind it to your data control and display your data. A SQL return with no parameters is the simplest database query, but lets say you want to filter your data by only returning certain data that falls into a certain category, you would then need parameters in your stored procedure. Lets take a look at a method that does this.
CODE
public static BindingSource GetRecordsByYear(int year)
{
string sSQL = "GetRecordsByYear";
//Stored procedure to execute
SqlConnection cnGetRecords = new SqlConnection(amaDBHelper.GetConnectionString("YourConnectionName"));
//SqlConnection Object to use
SqlCommand cmdGetRecords = new SqlCommand();
//SqlCommand Object to use
SqlDataAdapter daAgents = new SqlDataAdapter();
DataSet dsGetRecords = new DataSet();
//Clear any parameters
cmdGetRecords.Parameters.Clear();
try
{
//set the SqlCommand Object Parameters
cmdGetRecords.CommandText = sSQL; //tell it what to execute
cmdGetRecords.CommandType = CommandType.StoredProcedure; //tell it its executing a Stored Procedure
//heres the difference from the last method
//here we are adding a parameter to send to our stored procedure
//you use the AddWithValue, then the name of the parameter in your stored procedure
//then the variable that holds that value
cmdGetRecords.Parameters.AddWithValue("@year", year);
//set the state of the SqlConnection Object
DataAccess.HandleConnection(cnGetRecords);
//create BindingSource to return for our DataGrid Control
System.Windows.Forms.BindingSource oBindingSource = DataAccess.GetBindingSource(cmdGetRecords);
//now check to make sure a BindingSource was returned
if (!oBindingSource == null)
{
//return the binding source to the calling method
return oBindingSource;
}
else //no binding source was returned
{
//let the user know the error
throw new exception("There was no BindingSource returned");
}
}
catch (Exception ex)
{
MsgBox(ex.Message, "Error Retrieving Data");
}
//now we close the connection
DataAccess.HandleConnection(cnGetRecords);
}
There is essentially one difference in this method and the previous one, its the line
cmdGetRecords.Parameters.AddWithValue("@year", year);. The AddWithValue accepts 2 parameters:
- The parameter name in your stored procedure (for this example the name is @year)
- The variable that is holding that value (in this case we pass it with the signature of the method -> year)
Now to use these methods in your form for binding a DataGridView to the data use this
CODE
//for binding to returning all the records
DataGridView1.DataSource = (BindingSource)GetAllRecords();
//for binding to to returning certain records
//for binding to returning all the records
DataGridView1.DataSource = (BindingSource)GetRecordsByYear((int)TextBox1.Text);
That is the end of the basics of working with SQL Server in C#. This should at least give you the basic tools for interacting with a SQL database in your C# application. One thing to remember, if you work with Access some things have to change:
- SqlCommand -> OleDbCommand
- SqlConnection -> OleDbConnection
- SqlDataAdapter -> OleDbDataAdapter
- using System.Data.SqlClient -> using System.Data.OleDb
If you have any questions please post them here so I can asnwer the question for you and anyone else who might have the same questions. Thanks for reading
Happy Coding!
This post has been edited by jayman9: 30 Jan, 2008 - 11:52 PM
SQL Basics in C#
6 Aug, 2007 - 02:40 PM
| 