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.

DbTest

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”);
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: