Network Protocols for SQL Server

Shared Memory

Connects to SQL Server instances running on the same computer as the client. Shared memory protocol is used primarily for troubleshooting. This protocol cannot be used on clients that use MDAC 2.8 or earlier—these clients are automatically switched to the named pipes protocol.

TCP/IP

Uses the TCP/IP protocol for communication.

Named Pipes

Interprocess communication (IPC) mechanism provided by SQL Server for communication between clients and servers.

VIA

Virtual Interface Adapter (VIA) protocol is used with VIA hardware.

As of SQL Server 2005, the following network protocols are no longer supported:

AppleTalk ADSP

Allows Apple Macintosh to communicate with SQL Server using native Apple-Talk protocol.

Banyan VINES

Supports Banyan VINES Sequenced Packet Protocol (SPP) across Banyan VINES IP network protocol.

Multiprotocol

Automatically chooses the first available network protocol to establish a connection generally with performance comparable to using a native network library. TCP/IP Sockets, NWLink IPX/SPX, and Named Pipes are supported.

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