Connection to Sql server using C#

Namespace for SQL Connection:

using System.Data.Sqlclient;

using System.Configration;

Components of ADO.NET

IDBConnection:

SqlConnection con=new SqlConnection();

Properites

ConnectionString

Syntax:

For Windows Authentication:

con.ConnectiionString=”Server=localhost; Database=DatabaseName; IntegratedSecurity=true”;

For Sql Authentication:

con.ConnectiionString=”Server=serverName; Database=DatabaseName; uid=as; pwd=sa”;

State
It gives State of the Connection

Open();
Close();
Broken();
Unknown();

IDBCOmmand:

Action Query——>Insert, Update, Delete

Retrival Query—->Select

1. ExcuteNonQuery()

Used for excuting Inserte, Update and Delete. Result of the method is a Integer

2. ExcuteScaler()

Returns Object as a Result.(Count no.of items in a perticular Column).

3.ExcuteReader()

Excute selecte statement and returns DataReader as a result.

DataReader

It is forward Read only, values cann’t be altered and does not MetaData not serializable

Methods

Read();
Close();

Application cann’t closed without closing DataReader

Properties of IDBCommand:

Command Type

TableDirect
Text
StoredProcedure

Connection to Sql server using C#

Step 1. open SQL server and create a datebase(name=org. Table name=emp). Table contains the following columns

Id —->Int

Name —->VarChar(50)

Session —->VarChar(50)

Dob —->VarChar(50)

step 2.Open VS 2008 and create new project(Windows form) named DbTest and design the form as given below.

Step 3. From the solution Explorer add new Application configration file and enter Define ConnectionString

<?xml version=”1.0″ encoding=”utf-8″ ?>

<configuration>

<connectionStrings>

<add name=”Datacon” connectionString=”server=ASHOK\SQLEXPRESS;DataBase=org;Integrated Security=true”/>

</connectionStrings>

</configuration>

<?xml version=”1.0″ encoding=”utf-8″ ?>

<configuration>

<connectionStrings>

<add name=”Datacon” connectionString=”server=ASHOK\SQLEXPRESS;DataBase=org;Integrated Security=true”/>

</connectionStrings>

</configuration>

Step 4. Add a new class named DataControl

class DataConnection
{
public static SqlConnection Getconnection()
{
SqlConnection con = new SqlConnection();
string str = ConfigurationManager.ConnectionStrings[“Datacon”].ConnectionString;
con.ConnectionString = str;
return con;
}
}

Step 5. Add the given code to the TestConnection Btn control method

private void TestConBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using (con = DataConnection.Getconnection())
{
con.Open();
MessageBox.Show(“Connection ” + con.State);
}
}

Step 6. Add the given code to the View Btn control method

private void ViewBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd;
SqlDataReader dr;
using (con = DataConnection.Getconnection())
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = “Select Name from Employee”;
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr.GetString(0));
}
}
}

Step 7. Add the given code to the ComboBox control method

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd;
SqlDataReader dr;
string s = comboBox1.SelectedItem.ToString();
using(con = DataConnection.Getconnection())
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = “Select * from Employee where name='” + s + “‘”;
dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox1.Text = dr.GetInt32(0).ToString();
textBox2.Text = dr.GetString(1);
textBox3.Text = dr.GetString(2);
textBox4.Text = dr.GetString(3);
}
}
}

Step 8. Add the given code to the AddBtn control method

private void AddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using(con = DataConnection.Getconnection());
{
string str = “insert into Employee(Id,name,session,DOB) values(@Id,@name,@session,@DOB)”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”,textBox1.Text);
cmd.Parameters.AddWithValue(“@name”, textBox2.Text);
cmd.Parameters.AddWithValue(“@session”,textBox3.Text);
cmd.Parameters.AddWithValue(“@DOB”,textBox4.Text);
con.Open();
int i = cmd.ExecuteNonQuery();
MessageBox.Show(“New Record Added” + i.ToString());
}
}

Step 9. Add the given code to the UpdateBtn control method

private void UpdateBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using(con = DataConnection.Getconnection())
{
string str = “Update Employee SET Id=@Id,name=@name,session=@session,DOB=@DOB where id=@Id”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”, textBox1.Text);
cmd.Parameters.AddWithValue(“@name”, textBox2.Text);
cmd.Parameters.AddWithValue(“@session”, textBox3.Text);
cmd.Parameters.AddWithValue(“@DOB”, textBox4.Text);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show(“Record Updated”);
}
}

Step 10. Add the given code to the DeleteBtn control method

private void DeleteBtn_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
using (con = DataConnection.Getconnection())
{
string str = “Delete from Employee where id=@Id”;
SqlCommand cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue(“@Id”, textBox1.Text);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show(“” + textBox1.Text + “Record Deleted”);
}
}