How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

How To: Connect to SQL using C# and SELECT, INSERT or UPDATE ? 

We all know how to insert a SQL query using ASP.NET the inconvenient in this is
that you let the end user see some of your private code and information on your
application architecture.

Here I demonstrate how to use the C# in a code behind page to 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 integrated security; if you need to connect to an external Server or
a MySQL Server go to connectionstring.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.

To get the sample project follow this link SimonDeshaies.com/Sample/SQLconn.

 ASPX

Create an ASPX page with code behind.

In that page you will add 2 text boxes with a button and double click the button
to create the event in the code behind then add a label so we can keep track of
the status. Just to make it real easy here’s the ASPX code.   
<formid="form1"runat="server">    <div>   
<center>       
<asp:TextBoxID="TextBox1"runat="server"></asp:TextB
ox><br/>       
<asp:TextBoxID="TextBox2"runat="server"></asp:TextB
ox><br/>       
<asp:ButtonID="Button1"runat="server"Text="Button&qu
ot;onclick="Button1_Click"/><br/><br/>       
<asp:LabelID="Label1"runat="server"Text="Page
Loaded"></asp:Label><br/>    </center>    </div>
   </form>  C# Code Behind

You may insert this hilly commented code in the C# but understanding it first is
the best way to go about it.//Add the folowing namespaceusing System.Data;using
System.Data.Sql;using System.Data.SqlClient; publicpartialclass_Default :
System.Web.UI.Page{    //Here i like to use a region to warp the SQL Connection.
   #region SQLConnection     //Here i declare a couple of items i'm gona use and
asign the database name.    publicSqlConnection mySqlConnection;    publicString
currentDatabase = "myDatabase";    publicSqlCommand mySqlCommand;   
publicSqlDataReader mySqlDataReader;     //This is how i'm gona be closing my
connection.    privatevoid closeConn()    {        if (mySqlConnection != null) 
      {            if (mySqlConnection.State == ConnectionState.Open)           
{                mySqlConnection.Close();            }           
mySqlConnection.Dispose();        }    }     //Here is where I create my
connection.    privateSqlConnection createConn(string database)    {        //
Here you define your server. Values can not be NULL         //Database Server
Name.        string myDSN = "SQLEXPRESS";         //Local Server Name.
       string myLSN = "Valkyrie";         //Define the type of
security, 'TRUE' or 'FALASE'.        string mySecType = "TRUE";       
 //Here you have your connection string you can edit it here.        string
mySqlConnectionString = ("server=" + myLSN + "\\" + myDSN +
";database=" + database + ";Integrated Security=" +
mySecType);         //If you wish to use SQL security, well just make your own
connection string...          // I make sure I have declare what mySqlConnection
stand for.        if (mySqlConnection == null) { mySqlConnection =
newSqlConnection(); };         // Since i will be reusing the connection I will
try this it the connection dose not exist.        if
(mySqlConnection.ConnectionString == string.Empty ||
mySqlConnection.ConnectionString == null)        {            // I use a try
catch stament cuz I use 2 set of arguments to connect to the database           
try            {                //First I try with a pool of 5-40 and a
connection time out of 4 seconds. then I open the connection.               
mySqlConnection.ConnectionString = "Min Pool Size=5;Max Pool
Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";       
        mySqlConnection.Open();            }            catch (Exception)       
    {                //If it did not work i try not using the pool and I give it
a 45 seconds timeout.                if (mySqlConnection.State !=
ConnectionState.Closed)                {                   
mySqlConnection.Close();                }               
mySqlConnection.ConnectionString = "Pooling=false;Connect Timeout=45;"
+ mySqlConnectionString + ";";                mySqlConnection.Open(); 
          }            return mySqlConnection;        }        //Here if the
connection exsist and is open i try this.        if (mySqlConnection.State !=
ConnectionState.Open)        {             try            {               
mySqlConnection.ConnectionString = "Min Pool Size=5;Max Pool
Size=40;Connect Timeout=4;" + mySqlConnectionString + ";";       
        mySqlConnection.Open();            }            catch (Exception)       
    {                if (mySqlConnection.State != ConnectionState.Closed)       
        {                    mySqlConnection.Close();                }          
     mySqlConnection.ConnectionString = "Pooling=false;Connect
Timeout=45;" + mySqlConnectionString + ";";               
mySqlConnection.Open();            }        }        return mySqlConnection;   
}    #endregion    // And there you go you can now connect to the SQL Server    
protectedvoid Button1_Click(object sender, EventArgs e)    {        // So all we
done erlier is used here In the event we create the connection.       
createConn("myDatabase");        //We create the command to read the
database then we will INSERT or UPDATE.        mySqlCommand =
mySqlConnection.CreateCommand();        //Here in the command text you put your
sql select statment        mySqlCommand.CommandText = "SELECT textBox1 =
@textBox1 FROM dbo.table1 WHERE textBox2 = @textBox2";         //I like to
set the type of data I will be inserting here. Then I set where will the data
come from and repeat for each data string.       
mySqlCommand.Parameters.Add("@textBox1", SqlDbType.VarChar);       
mySqlCommand.Parameters["@textBox1"].Value = TextBox1.Text;        
mySqlCommand.Parameters.Add("@textBox2", SqlDbType.VarChar);       
mySqlCommand.Parameters["@textBox2"].Value = TextBox2.Text;        
//I execute the reader.        mySqlDataReader = mySqlCommand.ExecuteReader();  
      // If the database dose not have row i will insert the data otherwise I   
    // will just close the connection so I make sure i dont override any thing. 
      if (!mySqlDataReader.HasRows)        {            mySqlDataReader.Close();
           //If you had to do an UPDATE here insted of an INSERT statment you
would use a UPDATE statement.            mySqlCommand.CommandText = "INSERT
INTO dbo.table1 (textBox1, textBox2) VALUES (@textBox1, @textBox2)";       
    mySqlCommand.ExecuteNonQuery();          }        //Here you close the
Reader and then dispose of the command.        mySqlDataReader.Close();       
mySqlCommand.Dispose();            //Normaly i would close the connection here
but since i will be makeing            //a SELECT right away to save the
connection i will not close it just yet.             //You acualy want to close
the connection after every group of transaction            //(don't waite for
the user to do something to close the connection,             //cuz if he just
deside to close the browser the connection will stay open. for a long time...)  
         //with the database, you don't want to leave it open othewise end users
           //will be put on holt untill the connection expires.     
//closeConn();          //Now that the data was saved in the database we will do
a select        //from the database and set the text value of the label1 to
texBox2.          // Here we Create the connection to the Table.       
createConn("myDatabase");        mySqlCommand =
mySqlConnection.CreateCommand();        mySqlCommand.CommandText = "SELECT
* FROM dbo.table1 WHERE textBox1 = @textBox1";              
mySqlCommand.Parameters.Add("@textBox1", SqlDbType.VarChar);       
mySqlCommand.Parameters["@textBox1"].Value = TextBox1.Text;        
mySqlDataReader = mySqlCommand.ExecuteReader();         //Here we use a
"while" to set the value to the label        while
(mySqlDataReader.Read())        {            Label1.Text =
Convert.ToString(mySqlDataReader["textBox2"]);        }        //Here
you close the Reader and then dispose of the command adn the connection cuz we
are done.        mySqlDataReader.Close();        mySqlCommand.Dispose();       
closeConn();    }}

HI Friends.

IF You want to intrect any database in .NEt application . my best approach is
ADO.NET . u wrote very un-professional code. Dont need worry about more
Connection object .

rajeev.net@hotmail.com:

HI Friends.

IF You want to intrect any database in .NEt application . my best approach is
ADO.NET . u wrote very un-professional code. Dont need worry about more
Connection object .

I guess that what you meant is that all my line breaks were missing? The forum
played the trick on me when I clicked "Post" since this forum is
moderated I could not edit until this morning.

Hello,

i dont know whether the above code is un-professional or not,since i m new to
asp.net.

anyway how would the code look like using ADO.net? as u mentioned

it that it is more simple and professional.

can u demonstrate with simple example along with code (C#).

thanks.

jack.

Using ado.net would look like this. 


This code sample was taken in the MSDN library at
http://msdn2.microsoft.com/en-us/library/ms254507.aspx.
 


C#


// Assumes connectionString is a valid connection string.using (SqlConnection
connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here.
}

 



VB




' Assumes connectionString is a valid connection string.
Using connection AsNew SqlConnection(connectionString)
    connection.Open()
    ' Do work here.End Using


In this code sample you are missing allot of pieces per example you are not
closing the connection.
It's not reusing your existing connection ether. The code I posted uses ado.net
and manages a lot of
all you do not want to think about, take your time and study it.