How To: Connect to SQL using C# and SELECT, INSERT or any other SQL STATEMENT? | Simon Deshaies

by Simon Deshaies 3/12/2008 12:10:00 PM

UPDATE: I greatly improved this connection class in this post, read: http://weblog.simondeshaies.net/post/2008/11/How-To-Connect-to-SQL-Server-with-C-and-Optimize-Reusability.aspx

I started a thread on the asp.net forums a while back wanting to share with the others a easy way to connect to SQL. Since then I perfected quite a bit the code and made it a class.cs file, that can be up and running inside a few minutes.

Connecting to SQL using the design view in visual studio is quite easy and straight forward. I found to be somewhat limited though. Having a class that you can invoke in all your application and in other classes is the works.

Here I demonstrate how to use the C# in a class file to connect to SQL, and INSERT, SELECT or UPDATE an existing SQL database. In this example I will be using the Microsoft SQL Express Server. Also the connection string in this example is for a local server with SQL security; if you need to connect to an external server or any other king of SQL server go to http://connectionstrings.com to get the replacement connection string code.

First off unlike asp.net insert you don’t use a connection string in the web.config, so no changes need to be done there.

The class itself goes like this, you can grab the sample solution and try it out.

In App_Data MySqlConnection.cs add the following code.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

/// <summary>
/// This Class is specifically design to connect with optimized pooling to MyDatabase.
///
/// In private string mySqlConnectionString MyDatabase is the database you want to connect to.
/// MyUser the sql autentication user and MyPassword the Password...
/// </summary>
 
public class MySqlConnection
{
    public SqlConnection Connection;
    public SqlDataReader DataReader;
    public SqlCommand Command;
    private string mySqlConnectionString = "server=.\\SQLExpress;database=MyDatabase;User ID=MyUser;Password=MyPassword;Trusted_Connection=True;";
    public void CloseConn()
    {
        if (Connection != null)
        {
            if (Connection.State == ConnectionState.Open)
            {
                Connection.Close();
            }
            Connection.Dispose();
        }
    }
    public SqlConnection CreateConn()
    {
        if (Connection == null) { Connection = new SqlConnection(); };
        if (Connection.ConnectionString == string.Empty || Connection.ConnectionString == null)
        {
            try
            {
                Connection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";
                Connection.Open();
            }
            catch (Exception)
            {
                if (Connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
                Connection.ConnectionString = "Pooling=false;Connect Timeout=45;" + mySqlConnectionString + ";";
                Connection.Open();
            }
            return Connection;
        }
        //SqlConnection Connection = new SqlConnection();
        if (Connection.State != ConnectionState.Open)
        {
             try
            {
                Connection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";
                Connection.Open();
            }
            catch (Exception)
            {
                if (Connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
                Connection.ConnectionString = "Pooling=false;Connect Timeout=45;" + mySqlConnectionString + ";";
                Connection.Open();
            }
        }
        return Connection;
    }
    public MySqlConnection()
    {   
 
    
}
}

Here you go, have plenty of fun and let me know what you think.

Currently rated 5.0 by 3 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: ,

ASP.net | How to:

Related posts

Comments

4/5/2008 5:29:24 PM

just great men...

shekhar kadam in

4/17/2008 6:36:21 PM

Sick!! Great Job!

Dave ca

10/20/2008 8:31:37 PM

Hello Simon Deshaies,

I have to say your code is great, I have been building a database system for quite sometime now using visual basic and am looking to update it with a more robust and powerful engine. I have seen your class implementation and I think its great. I was curious about a couple of things, I have honestly not written much code in C# for quite sometime, primarily because of the investment my company made with Visual Basic. What I am hoping you can help me with is how do I use your class instance above with the "SELECT" sql statement to assign the record values into text boxes.

I have payroll program and I wish to type in a social and view their payroll information. I would really just like to see the code to implement the SQL statement with your class to assign the record to the textbox1.text object. I have seen how to insert, and with that pretty much can already view and assume how to update the table, but I have not been able to deduce how to view the record with a text box.

This is what I saw on a previous forum post you had created.
MySqlConnection mySql = new MySqlConnection();
mySql.CreateConn();
mySql.Command = mySql.Connection.CreateCommand();
mySql.Command.CommandText = "INSERT INTO ZTABLE (NAME, NAMELAST) VALUES (@textBox1, @textBox2)";

mySql.Command.Parameters.Add("@textBox1", SqlDbType.VarChar);
mySql.Command.Parameters["@textBox1"].Value = textBox1.Text;
mySql.Command.Parameters.Add("@textBox2", SqlDbType.VarChar);
mySql.Command.Parameters["@textBox2"].Value = textBox2.Text;

mySql.Command.ExecuteNonQuery();

mySql.Command.Dispose();
mySql.Connection.Close();
mySql.CloseConn();

As I said it worked great to insert the information, but I was hoping you could help me to view current data in the table and assign it to a text box.

Max Lopez us

10/21/2008 3:52:23 AM

Hi Max,

First, thank you. I'm verry happy to be able to help fellow developers.

To be able to assign a value for a database to a text box there are many to do this. You can ether use a Out parameter or a DataReader.

I prefer the DataReader, well because I'm more use to it...

Here an example.

MySqlConnection mySql = new MySqlConnection();
mySql.CreateConn();
mySql.Command = mySql.Connection.CreateCommand();
mySql.Command.CommandText = Sring.Format("SELECT [dbo].[Users].[Name] FROM [dbo].[ZTABLE] WHERE [dbo].[Users].[UserId] = {0}", MyUserIdTextBox.Text);
mySql.DataReader = mySql.Command.ExecuteDataReader();
while(mySql.DataReader.Read())
{
MyUserNameTextBox.Text = Convert.ToString(mySql.DataReader[0]);
}
mySql.DataReader.Close();
mySql.Command.Dispose();
mySql.CloseConn();

Hope this helps. Let me know. If you need any clarifications, well, just post! ;)

Simon

Simon Deshaies ca

11/21/2008 6:03:36 PM

Bonjour Simon

I quite new to asp.net and so far I have been using the "SqlDataSource" component which I have to say is very easy and handy.

I am trying to move from that and use your method, but I am not too sure how migrate my InsertCommand from the ASPX to the code behind page since my InsertCommand is not that simple. It uses IF statement and Joints. (if a particular radiobutton is selected it should execute a particular statement if another is seleted it executes another statement) I can understand your sample file but cannot apply it to my situation. A bit of help would be much appreciated.

Here is my SqlDataSource:

<asp:SqlDataSource ID="SqlDataSourceInsert" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DBConnectionString %>"
InsertCommand="
IF @ctrl_user_role = 2 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id,@Ctrl_HF_userReview)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dep_reviewers(dpt_id,user_id)
VALUES(@ctrl_user_dpt_id,IDENT_CURRENT('tbl_user'))

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END

IF @ctrl_user_role = 1 AND @ctrl_user_review = 0 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END


IF @ctrl_user_role = 1 AND @ctrl_user_review = 1 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review, user_reviewer)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review, @ctrl_user_reviewer)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END ">


<InsertParameters>
<asp:ControlParameter Name="ctrl_user_name" ControlID="Txt_user_name"/>
<asp:ControlParameter Name="ctrl_user_surname" ControlID="Txt_user_surname"/>
<asp:ControlParameter Name="ctrl_user_email" ControlID="Txt_user_email"/>
<asp:ControlParameter Name="ctrl_user_pw" ControlID="Txt_user_pw"/>
<asp:ControlParameter Name="ctrl_user_role" ControlID="check1"/>
<asp:ControlParameter Name="ctrl_user_dpt_id" ControlID="dropDown_choose_dpt"/>
<asp:ControlParameter Name="ctrl_user_review" ControlID="RBL_user_review"/>
<asp:ControlParameter Name="ctrl_user_reviewer" ControlID="DDL_user_reviewer"/>
<asp:ControlParameter Name="ctrl_HF_userReview" ControlID="HF_userReview" />
<asp:ControlParameter Name="ctrl_sub_dpt" ControlID="CheckBoxList_sub_dpt" />


</InsertParameters>
</asp:SqlDataSource>




I have another question if I may. How would you populate dynamicaly let's say a dropdownlist if you don't use a "SqlDataSource" Would you still use <aspLaughingropDownList DataSourceID="" ? would you bind it to "mySql.DataReader" ?

Here is the SqlDataSource which populate hte DropdownList

<!--DS (SqlDS_dept) populates the drop down menu "choose department"-->

<asp:SqlDataSource ID="SqlDS_dept" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DBConnectionString %>"
SelectCommand="SELECT * FROM [tbl_department] WHERE [dpt_subsection_of] = 0 "></asp:SqlDataSource>


Thanks a lot for

Christophe















christophe Goasduff gb

11/26/2008 11:02:03 PM

Christophe,

1- To move to code behind you would do something like this:
MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();
//Your command
MySqlConnection.Command.CommandText = "INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review) VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id,@Ctrl_HF_userReview)";

//Your parameter
MySqlConnection.Command.Parameters.Add("@ctrl_user_name", SqlDbType.VarChar); //Parameter SQL Type
MySqlConnection.Command.Parameters["@ctrl_user_name"].Value = Txt_user_name.Text;

//For your condition
if (check1.Checked)
{
//your code
}

But this is not the best way to do this. I would suggest you create a stored procedure you can either create it in SQL this you make your code way more easy to maintain.

If you do not want to create a procedure in SQL Server you could do something like this:

MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();

//Set all your parameter
MySqlConnection.Command.Parameters.Add("@ctrl_user_name", SqlDbType.VarChar); //Parameter SQL Type
MySqlConnection.Command.Parameters["@ctrl_user_name"].Value = Txt_user_name.Text;

//Your command
MySqlConnection.Command.CommandText = @"
IF @ctrl_user_role = 2 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id,@Ctrl_HF_userReview)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dep_reviewers(dpt_id,user_id)
VALUES(@ctrl_user_dpt_id,IDENT_CURRENT('tbl_user'))

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END

IF @ctrl_user_role = 1 AND @ctrl_user_review = 0 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END


IF @ctrl_user_role = 1 AND @ctrl_user_review = 1 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review, user_reviewer)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review, @ctrl_user_reviewer)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END";
MySqlConnection.Command.ExecuteNonQuery();

For the DropDownList you can either set them wil the DataReader
MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();
MySqlConnection.Command.CommandType = CommandType.Text;
MySqlConnection.Command.CommandText = "SELECT something FROM [dbo].[sometable] WHERE [template] = 0 ORDER BY something";
MySqlConnection.DataReader = MySqlConnection.Command.ExecuteReader();
while (MySqlConnection.DataReader.Read())
{
raceDropDownList.Items.Add(Convert.ToString(MySqlConnection.DataReader["something"]));
}
MySqlConnection.DataReader.Close();

or a DataTable as DataSource

DataTable MyAliasDataTable = new DataTable();
SqlDataAdapter MyAliasDataAdapter = new SqlDataAdapter("SELECT [userID],[alias] FROM [dbo].[users] ORDER BY [alias]", MySqlConnection.Connection);
MyAliasDataAdapter.Fill(MyAliasDataTable);
AliasDropDownList.DataSource = MyAliasDataTable;
AliasDropDownList.DataTextField = "alias";
AliasDropDownList.DataValueField = "userID";
AliasDropDownList.DataBind();
MyAliasDataTable.Dispose();
MyAliasDataAdapter.Dispose();

Regards,

Simon

Simon Deshaies

12/1/2008 4:10:07 AM

Hello,

does any of you guys know how to retrieve data from a database (be it SQL or MS Access) and display the contents on a Microsoft Visual Studio Emulator? Please help.

Thanks,
Jessica

Jessica sg

12/1/2008 12:24:29 PM

Hi
The connection is great but I am a beginner and I dont follow all of it. Is there a simpler version? All I want is a class that I can call in my code behind to connect to my database. I dont want to have to put my connection string in my code behind everytime.
I usually do it like this :
SqlConnection myConnection = new SqlConnection("fullconnectionstring");
try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}

on every code behind where I need to connect to the database.
Thanks!

Natasja za

12/2/2008 12:41:15 AM

Jessica,
I did not have the chance to use the Device Emulator in Visual Studio, so unfortunately I can’t help you.

Natasja,
A simple class would look like this… Well it’s complicated I cannot in all good conscience give you a piece of code that I think is not wordy of a production environment.
But here’s a class that will accomplish what you want to do and its rather simple. You can from it expand and modify it to fit your needs.
using System;
using System.Data;
using System.Data.SqlClient;

public static class SimpleSqlClass
{
private static String ConnectionString = "server=SERVERNAMEORIP;database=DATABASE;User ID=LOGIN;Password=PASSWORD;Trusted_Connection=False;";
private static SqlConnection PConnection;

public static SqlConnection Open()
{
if (PConnection == null) { PConnection = new SqlConnection(); }
if (String.IsNullOrEmpty(PConnection.ConnectionString) || PConnection.State != ConnectionState.Open)
{
try
{
PConnection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + ConnectionString + ";";
PConnection.Open();
}
catch (Exception)
{
if (PConnection.State != ConnectionState.Closed)
{
PConnection.Close();
}
PConnection.ConnectionString = "Pooling=false;Connect Timeout=45;" + ConnectionString + ";";
PConnection.Open();
}
return PConnection;
}
return PConnection;
}
public static void Close()
{
if (PConnection != null)
{
if (PConnection.State == ConnectionState.Open)
{
PConnection.Close();
}
PConnection.Dispose();
}
}
}

class TestClass
{
void TestMethod()
{
try
{
SimpleSqlClass.Open();
//Your code.
}
catch (Exception ex)
{
//Your exception handling.
}
finally
{
SimpleSqlClass.Close();
}
}
}

Simon Deshaies

Add comment


 

  Country flag

[b][/b] - [i][/i] - [u][/u]- [quote][/quote]



Live preview

1/7/2009 10:47:22 AM

Powered by BlogEngine.NET 1.3.1.0
Theme by Mads Kristensen

www.aubergedeslegendes.com

About the author

Simon Deshaies

Name of authorI do web development, I focus on your business processes by dramatically increasing your visibility. I develop strong Internet and Web applications. My main objective is to support your performance by optimizing your presence on the Web and to advise you on the best web technology to fit your needs.

Linked in profile

Specialties:
.NET development, Internet and Web applications.
HTML, XHTML, XML, CSS, ASP.NET, AJAX, C#, SQL.
Microsoft Windows Server 2000 to 2008, SQL Server, MySQL, IIS, DNS.


E-mail me Send mail

Calendar

<<  January 2009  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar

Pages

    Recent comments

    Disclaimer

    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

    © Copyright 2009

    Sign in