• (3 Pages)
  • +
  • 1
  • 2
  • 3

SQL Basics in C#

#1 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1638
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Post icon  Posted 06 August 2007 - 03:40 PM

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

< 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

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

public static string GetConnectionString(string strConnection)
{
   //variable to hold our connection string for returning it
   string strReturn = "";
   //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

/// <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.

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.

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 Attack

This 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.

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

//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 PsychoCoder: 29 May 2009 - 10:23 PM


Is This A Good Question/Topic? 3
  • +

Replies To: SQL Basics in C#

#2 chronoTrigger  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 08-October 07

Posted 29 January 2008 - 11:35 PM

I follow your tutorial but when I got here, it gives me error.

strReturn = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString;

it says something about the ConfigurationManager doesnt exist.
Was This Post Helpful? 0
  • +
  • -

#3 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Posted 29 January 2008 - 11:53 PM

Add the reference System.Configuration to your project and add using System.Configuration; to the top of the code to use this namespace and access the ConfigurationManager.


@PsychoCoder, can please fix the tutorial to mention these two steps? Thx.
Was This Post Helpful? 1
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1638
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 30 January 2008 - 11:22 AM

Jay,

I was going to edit it as you suggested but for some reason my "Edit" button isn't there, have any idea why?
Was This Post Helpful? 0
  • +
  • -

#5 chronoTrigger  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 08-October 07

Posted 31 January 2008 - 12:24 AM

Jay and PsychoCoder, thanks for your help, but I still have some issue. I added the Reference file into my project and chose system.configuration also put using system.configuration on top of the code, but I have the next problem.

strReturn = configurationManager.ConnectionStrings(strConnection).ConnectionString;

this time it says ConnectionStrings() can not be used like a method.
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Posted 31 January 2008 - 12:50 AM

Change the parenthesis into brackets in both of the following lines. Tutorial has now been updated with these latest changes.
strReturn = configurationManager.ConnectionStrings[strConnection].ConnectionString;
strReturn = ConfigurationManager.ConnectionStrings["YourConnectionName"].ConnectionString;


Are you getting any other error messages? If so, then please copy and paste them all into a post and we will edit the tutorial to make sure it is 100% accurate. Thank you.
Was This Post Helpful? 0
  • +
  • -

#7 chronoTrigger  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 08-October 07

Posted 31 January 2008 - 01:53 AM

Thanks Jayman for the quick respond, very appreciated. Now the error is gone. I haven't gone through the tutorial yet, but if I encounter any error I will definitely post in here.

Sorry I'm completely new to SQL server and C#, so the "YourConnectionName", what does it refer to in SQL? when I log into SQL, the login screen has three fields: server type, server name, and authentication, so which one is my connectionName?
Was This Post Helpful? 0
  • +
  • -

#8 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Posted 31 January 2008 - 02:12 AM

In the first code window you will see the connectionStrings tag, where you see the line name="YourConnectionName", this is where you specify the name you wish to call the connection string for the database. You can call it anything you like, it is just a way of uniquely identifying it so that you can refer to it by name.
Was This Post Helpful? 0
  • +
  • -

#9 chronoTrigger  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 08-October 07

Posted 31 January 2008 - 11:53 AM

oh ok, I got it, thanks.
Was This Post Helpful? 0
  • +
  • -

#10 PennyBoki  Icon User is offline

  • system("revolution");
  • member icon

Reputation: 53
  • View blog
  • Posts: 2,334
  • Joined: 11-December 06

Posted 10 April 2008 - 04:30 AM

Hi Psycho, I received some errors while compiling.

First this line in the GetAllRecords method:

if (!oBindingSource == null){


I think it should be
if (!(oBindingSource == null))


I see some case sensitive errors, in the same method I see
you use the word exception and in some Exception, I think Exception should be used, instead of exception. m'kay :)

Also I see you use MsgBox, but I receive it as an error, so I use MessageBox.Show().....


And yes I can't compile these lines:
strReturn = ConfigurationManager.ConnectionStrings[strConnection].ConnectionString;
strReturn = ConfigurationManager.ConnectionStrings["YourConnectionName"].ConnectionString;


I already included using System.Configuration; but still ConfigurationManager is not recognized.

Just some little notes. If I am mistaken please let me know. I am using VS 2005.
Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1638
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 10 April 2008 - 05:07 AM

You are right about MsgBox, I'm not sure how that got past the IDE when I built it, so for that I apologize. As for the last one, that one has me stumped. I use this code constantly, in fact I'm using it in the project I'm currently working on, and have no issues. This was written in 2005 so your version is correct. So you clicked Project > Add Reference and made sure System.Configuration was added, then added using System.Configuration; to your class at the top?

In this line


strReturn = ConfigurationManager.ConnectionStrings["YourConnectionName"].ConnectionString;



"YourConnectionName" must be replaced with whatever you name your connection string in the app.config or web.config (I know you know that, I just have to make sure to cover as much as possible).


This is the code Im currently using in my web application, it's identical to what I offer in this tutorial (well the names have changed, but the code is the same)


/// <summary>
/// method to retrieve the database connection string from the web.config
/// </summary>
/// <param name="name">name of the connection string we want (this allows us to have multiple if needed)</param>
/// <returns></returns>
public static string GetConnectionString(string name)
{
    try
    {
        //variable to hold our connection string for returning it
        string connString = string.Empty;
        //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(name)) //a connection string name was provided
        {
            //get the connection string by the name provided
            connString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }
        else //no connection string name was provided
        {
            //get the default connection string
            connString = ConfigurationManager.ConnectionStrings["Qmetro"].ConnectionString;
        }
		_status = true;
        //return the connection string to the calling method
        return connString;
    }
    catch (Exception ex)
    {
        _message = ex.Message;
		_status = false;
        return string.Empty;
    }
}



So I know this works, but I cannot for the life of me figure out why this isn't working for you.
Was This Post Helpful? 0
  • +
  • -

#12 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Posted 10 April 2008 - 07:37 PM

Quote

I see some case sensitive errors,

Someone needs to start working with Option Strict On and Option Explicit On. ;)

Actually this line can simply be written as this:
if (oBindingSource != null)

Was This Post Helpful? 0
  • +
  • -

#13 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 01 October 2008 - 10:55 AM

When I try to compile, I get this error...

Quote

The type or namespace name 'BindingSource' could not be found (are you missing a using directive or an assembly reference?)


I added the reference System.configuration and added these two lines...
using System.Data.SqlClient;
using System.Configuration;

Was This Post Helpful? 0
  • +
  • -

#14 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Posted 01 October 2008 - 11:39 AM

BindingSource is a member of the System.Windows.Forms namespace.
Was This Post Helpful? 0
  • +
  • -

#15 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 02 October 2008 - 06:38 AM

Yes, thank you jayman9, that cleared up some of the problems, then there was a whole bunch more... found out that System.Data was also one that is required... Here is the complete list...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;


There's a problem in the "Try" "Catch" area, it gives an error "not all code paths return a value" From what I researched and found is that it is because, "nothing is returned if the try block throws an exception and it is caught by the catch block"

This post has been edited by webwired: 02 October 2008 - 09:59 AM

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3