ADO.

Net Programs

Program for inserting, deleting, updating, displaying data in textboxes (single & all records).

Adding data in combobox, tree view, selecting from combobox, treeview and showing in
textboxes.

// declare the below inside class

SqlConnection con = new SqlConnection("data source=LAB-387;initial

catalog=2to3;integrated security=true");
1 | Page

SqlDataReader dr;
SqlCommand cmd = new SqlCommand();
int r,r1;
string qry,fname;

// type the below in page load event :

if (! Page.IsPostBack)
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "select * from student";
dr = cmd.ExecuteReader();
TreeNode t1;
TreeNode t2=new TreeNode(Roll No);
TreeView1.Nodes.Add(t2);
DropDownList1.Items.Add(Select any Roll No);
ListBox1.Items.Add(Select any Roll No);
string rno;
while (dr.Read())
{
rno= dr.GetValue(0).ToString();
DropDownList1.Items.Add(rno);
ListBox1.Items.Add(rno);

t1=new TreeNode();
t1.Text =rno;
TreeView1.Nodes.Add(t1);
TreeView1.Nodes[0].ChildNodes.Add(t1);
}
2 | Page

con.Close();
}

// drop down list event coding :

try
{
r = int.Parse(DropDownList1.SelectedItem.Text);
r1 = r;
qry = "select * from student where rno=" + r;
//MessageBox.Show(qry);
con.Open();
cmd.Connection = con;
cmd.CommandText = qry;
dr = cmd.ExecuteReader();
if (dr.Read())
{
textBox1.Text = dr.GetValue(0).ToString();
textBox2.Text = dr.GetValue(1).ToString();
textBox3.Text = dr.GetValue(2).ToString();
textBox4.Text = dr.GetValue(3).ToString();
textBox5.Text = dr.GetValue(4).ToString();
fname = dr.GetValue(5).ToString();
if (File.Exists(fname))
pictureBox1.Image = Image.FromFile(fname);
}
}
con.Close();

Inserting, Updating, Deleting data thru Parameter Passing

3 | Page

1)
2)
3)
4)
5)
6)
7)
8)

Create objects of SqlConnection, SqlCommand.

Create objects of SqlParameter class, specify data to its Value property.
Add the Parameters to the command object.
Specify connection object to command object Connection property.
Open the connection.
Store the sql query in command obejct CommandText property.
Execute the query.
Close the connection.

// type the code in side class

SqlConnection con = new SqlConnection("server=lab-387;integrated Security=true;initial

catalog=2to3");
SqlCommand com = new SqlCommand();

// type the code in button1 event

SqlParameter sp1 = new SqlParameter("@rno", SqlDbType.Int);

sp1.Value = int.Parse(textBox1.Text);

SqlParameter sp2 = new SqlParameter("@sname", SqlDbType.VarChar);

sp2.Value = (textBox2.Text);

SqlParameter sp3 = new SqlParameter("@m1", SqlDbType.Int);

sp3.Value = int.Parse(textBox3.Text);

SqlParameter sp4 = new SqlParameter("@m2", SqlDbType.Int);

sp4.Value = int.Parse(textBox4.Text);

SqlParameter sp5 = new SqlParameter("@m3", SqlDbType.Int);

sp5.Value = int.Parse(textBox5.Text);

SqlParameter sp6 = new SqlParameter("@photo", SqlDbType.VarChar);

4 | Page

sp6.Value = (textBox6.Text);

com.Parameters.Clear();
com.Parameters.Add(sp1);
com.Parameters.Add(sp2);
com.Parameters.Add(sp3);
com.Parameters.Add(sp4);
com.Parameters.Add(sp5);
com.Parameters.Add(sp6);

com.CommandText = "insert into student values(@rno,@sname,@m1,@m2,@m3,@photo)";

com.Connection = con;
con.Open();
com.ExecuteNonQuery();
con.Close();
Response.Write("Record is added");

//com.CommandText = "update student set sname=@sname, m1=@m1, m2=@m2,

m3=@m3, photo=@photo where rno=@rno";

//com.CommandText="delete from student where rno=@rno";

//com.CommandText="select * from student where rno=@rno";

Displaying data in GridView :

5 | Page

// type the below code inside class

SqlConnection con = new SqlConnection("data source=(local);initial catalog=2to3;integrated

security=true");
SqlDataAdapter sda;
DataSet ds = new DataSet();

// type the below code in button click event

sda = new SqlDataAdapter("select * from student", con);

sda.Fill(ds, "student1");
GridView1.DataSource = ds;
GridView1.DataMember = "student1";
GridView1.DataBind();

//sda.Fill(ds, "student");
//GridView1.DataSource = ds.Tables["student"];
6 | Page

GridView1.DataBind();

//sda.Fill(ds);
//GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

Displaying multiple tables in DataGridView :

// type the below code inside class

SqlConnection con = new SqlConnection("data source=LAB-387;initial

catalog=2to3;integrated security=true");
SqlDataAdapter sda;
DataSet ds = new DataSet();
7 | Page

// type the below code in button2 click event

GridView1.DataSource = ds.Tables["emp"];
GridView1.DataBind();

// type the below code in button1 click event

GridView1.DataSource = ds.Tables["student"];
GridView1.DataBind();

// in page load event

sda = new SqlDataAdapter("select * from student", con);

sda.Fill(ds, "student");

sda = new SqlDataAdapter("select * from emp", con);

sda.Fill(ds, "emp");

Displaying data in DropDownList / Listbox / RadioButtonList :

// type the below code inside class

SqlConnection con = new SqlConnection("data source=LAB-387;initial

catalog=2to3;integrated security=true");
SqlDataAdapter sda;
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();

// in page load event

8 | Page

if (! Page.IsPostBack)
{
sda = new SqlDataAdapter("select * from student", con);
sda.Fill(ds, "student");

DropDownList1.DataSource=ds.Tables[student];
DropDownList1.DataTextField=rno;
DropDownList1.DataValueField=rno;
DropDownList1.DataBind();

ListBox1.DataSource=ds.Tables[student];
ListBox1.DataTextField=rno;
ListBox1.DataValueField=rno;
ListBox1.DataBind();

RadioButtonList1.DataSource=ds.Tables[student];
RadioButtonList1.DataTextField=rno;
RadioButtonList1.DataValueField=rno;
RadioButtonList1.DataBind();
}

// type the below in DropDownListSelectedIndexChanged event :

try
{
int r = int.Parse(DropDownList1.SelectedItem.ToString());
string qry = "select * from student where rno=" + r;
ds1.Clear();
sda = new SqlDataAdapter(qry, con);
sda.Fill(ds1);
9 | Page

Label1.Text = ds1.Tables[0].Rows[0].ItemArray[1].ToString();
Image1.ImageUrl= ds1.Tables[0].Rows[0].ItemArray[5].ToString();
}
catch (Exception e1)
{
}

// type the below in ListBoxSelectedIndexChanged event :

try
{
int r = int.Parse(ListBox1.SelectedItem.ToString());
string qry = "select * from student where rno=" + r;
ds1.Clear();
sda = new SqlDataAdapter(qry, con);
sda.Fill(ds1);
Label1.Text = ds1.Tables[0].Rows[0].ItemArray[1].ToString();
Image1.ImageUrl= ds1.Tables[0].Rows[0].ItemArray[5].ToString();
}
catch (Exception e1)
{
}

// type the below in RadioButtonListSelectedIndexChanged event :

try
{
int r = int.Parse(RadioButtonList1.Text);
qry = "select * from student where rno=" + r;
ds1.Clear();
10 | P a g e

sda = new SqlDataAdapter(qry, con);

sda.Fill(ds1);
Label1.Text = ds1.Tables[0].Rows[0].ItemArray[1].ToString();
Image1.ImageUrl= ds1.Tables[0].Rows[0].ItemArray[5].ToString();
}
catch (Exception e1)
{
}

Displaying data in CheckboxList :

// type the below code inside class

SqlConnection con = new SqlConnection("data source=LAB-387;initial

catalog=2to3;integrated security=true");
SqlDataAdapter sda;
DataSet ds = new DataSet();

// in page load event

if (! Page.IsPostBack)
{
sda = new SqlDataAdapter("select * from student", con);
sda.Fill(ds, "student");

CheckBoxList1.DataSource=ds.Tables[student];
CheckBoxList1.DataTextField=rno;
CheckBoxList1.DataValueField=rno;
CheckBoxList1.DataBind();
}
11 | P a g e

// type in button click event

string s1=,s2=,s3;
for (int i=0;i<CheckBoxList1.Items.Count;i++)
{
if (CheckBoxList1.Items[i].Selected)
s1=s1++CheckBoxList1.Items[i].Text++,;

// if string

s1=s1+CheckBoxList1.Items[i].Text+,;

// if numeric

int l=s1.length;
s2=s1.Substring(0,l-1);
s3=select * from student where rno in (+s2+);
Response.Write(s1=+s1);
Response.Write(<br>s2=+s2);
Response.Write(<br>s3=+s3);
SqlDataAdapter sda1=new SqlDataAdapter(s3,con);
DataSet ds1=new DataSet();
sda1.fill(ds1);
GridView1.DataSource = ds1.Tables[0];
GridView1.DataBind();

Displaying Records one by one :

12 | P a g e

// type the below code in class

SqlConnection con = new SqlConnection("data source=LAB-387;initial

catalog=2to3;integrated security=true");
SqlDataAdapter sda;
DataSet ds = new DataSet();
int cr, tr;

// in page load event

if (! Page.IsPostBack)
{
Session[cr] = 0;
13 | P a g e

}
sda = new SqlDataAdapter("select * from student", con);
sda.Fill(ds);
Session[tr] = ds.Tables[0].Rows.Count - 1;

public void display()

{
cr=Convert.ToInt32(Session[cr]);
Label1.Text = ds.Tables[0].Rows[cr].ItemArray[0].ToString();
Label2.Text= ds.Tables[0].Rows[cr].ItemArray[1].ToString();
}

// first
private void button1_Click(object sender, EventArgs e)
{
Session[cr] = 0;
display();
}

// last
private void button3_Click(object sender, EventArgs e)
{
Session[cr] = Session[tr];
display();
}

// next
private void button2_Click(object sender, EventArgs e)
{
14 | P a g e

cr=Convert.ToInt32(Session[cr]);
cr = cr + 1;
Session[cr]=cr;
tr=Convert.ToInt32(Session[tr]);
if (cr > tr)
Session[cr] = 0;
display();
}

// previous
private void button4_Click(object sender, EventArgs e)
{
cr=Convert.ToInt32(Session[cr]);
cr = cr - 1;
Session[cr]=cr;
if (cr < 0)
Session[cr] = Session[tr]/0;
display();
}
}
}
Calling Stored Procedures of SQL Server from .NET

Retriving total no. of rows based on particular data :

15 | P a g e

-- stored procedure for counting total no. of records according to a given condition

create procedure sp11 @a int, @b int output

as
begin
select @b=m1+m2+m3 from student where rno=@a
end

// button click event coding :

SqlConnection con = new SqlConnection("Data Source=lab-387;integrated

Security=true;initial catalog=2to3");
SqlCommand testCMD = new SqlCommand("sp11",con);

testCMD.CommandType = CommandType.StoredProcedure;
testCMD.Parameters.Clear();
16 | P a g e

SqlParameter sp1 = testCMD.Parameters.Add("@a", SqlDbType.Int);

sp1.Direction = ParameterDirection.Input;

SqlParameter sp2 = testCMD.Parameters.Add("@b", SqlDbType.Int);

sp2.Direction = ParameterDirection.Output;

sp1.Value=Convert.ToInt32(textBox1.Text);

con.Open();
SqlDataReader myReader = testCMD.ExecuteReader();
myReader.Close();
textBox2.Text=sp2.Value.ToString();
con.Close();

Inserting data in table thru Stored Procedure

create procedure insertdata @a int, @b varchar(50), @c int, @d int, @e int, @f varchar(500)

as
begin
insert into student values (@a,@b,@c,@d,@e,@f)
end
17 | P a g e

// type the below code in button click event :

SqlConnection con = new SqlConnection("Data Source=lab-387;integrated Security=true;initial

catalog=2to3");
SqlCommand testCMD = new SqlCommand("insertdata", con);
testCMD.CommandType = CommandType.StoredProcedure;
testCMD.Parameters.Clear();
SqlParameter p1 = testCMD.Parameters.Add("@a", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
SqlParameter p2 = testCMD.Parameters.Add("@b", SqlDbType.VarChar);
p2.Direction = ParameterDirection.Input;
SqlParameter p3= testCMD.Parameters.Add("@c", SqlDbType.Char);
p3.Direction = ParameterDirection.Input;
SqlParameter p4 = testCMD.Parameters.Add("@d", SqlDbType.VarChar);
p4.Direction = ParameterDirection.Input;
SqlParameter p5= testCMD.Parameters.Add("@e", SqlDbType.Int);
p5.Direction = ParameterDirection.Input;
SqlParameter p6 = testCMD.Parameters.Add("@f", SqlDbType.Int);
p6.Direction = ParameterDirection.Input;
SqlParameter p7 = testCMD.Parameters.Add("@f", SqlDbType.Int);
P7.Direction = ParameterDirection.Input;
SqlParameter p8 = testCMD.Parameters.Add("@g", SqlDbType.VarChar);
P8.Direction = ParameterDirection.Input;
SqlParameter p9 = testCMD.Parameters.Add("@h", SqlDbType.VarChar);
p9.Direction = ParameterDirection.Input;
SqlParameter p10 = testCMD.Parameters.Add("@i", SqlDbType.VarChar);
P10.Direction = ParameterDirection.Input;
p1.Value = Convert.ToInt32 (textBox1.Text);
p2.Value = (textBox2.Text);
p3.Value = (textBox3.Text);
18 | P a g e

p4.Value = (textBox4.Text);
p5.Value = Convert.ToInt32 (textBox5.Text);
p6.Value = Convert.ToInt32 (textBox6.Text);
p7.Value = Convert.ToInt32 (textBox7.Text);
p8.Value = (textBox8.Text);
p9.Value = (textBox9.Text);
p10.Value = (textBox10.Text);
con.Open();
testCMD.ExecuteNonQuery();
Response.Write("Record added");
con.Close();

19 | P a g e

ADO.NET
It is a collection of Managed Providers that can be used for communication with Data Sources.
-> When .NET was designed for Data Source communication ADO.NET has been designed, which
is an extension to the Older ADO (Collection of Unmanaged Providers).
-> ADO.NET provides various 'types' that can be used for data source communication under the
following namespaces.
1) System. Data
2) System.Data.OleDb
3) System.Data.SqlClient
4) System.Data.OracleClient
5) System.Data.Odbc
System. Data
Types under this namespace are used for holding and managing of data on Client machines.
'Classes' under this namespace are DataSet, DataTable, DataColumn, DataRow, DataView,
DataRelation etc.
System.Data.OleDb
Types under this namespace are used for communicating with any data source like Files,
Databases, and IndexingServers etc.
System.Data.SqlClient
Types under this namespace are used only for Sql Server Database communication.
System.Data.OracleClient
Types under this namespace are used only for Oracle Database communication.
System.Data.Odbc
Types under this namespace are used for communicating with traditional Odbc drivers and they
will in turn communicate with any data source.

All the above four namespaces contains same set of Classes as following.

-Connection
-Command
-DataReader
-DataAdapter
-CommandBuilder
20 | P a g e

-Parameter
Note: - Each 'class' here is referred by prefixing with their namespace before 'class' name to
discriminate between each other as following.
-OleDbConnection

-OleDbCommand

-SqlConnection

-SqlCommand

-OracleConnection

-OracleCommand

-OdbcConnection

-OdbcCommand

Each and every operation we perform on a data source has three steps in it.
1) Establishing a Connection
2) Sending request as a Statement
3) Capturing the results given by DataSource
1) Establishing a Connection
In this process we open a channel for communication with the DataSource present on local or
remote machine to perform the operations. To open a channel for communication we use
'Connection' class.
Constructors
Connection ()
Connection (string connectionstring)
'Connectionstring' is a collection of attributes that are used for connecting with a data source those
are
1) Providers
2) DataSource
3) User Id and Password
4) Database (or) Initial catalog
5) Trusted_Connection = True
6) DSN
1) Provider
As we discussed earlier a Provider is required for communicating with Data sources, where we
need to use different Provider for different Data Source.
Oracle

Msdaora

SqlServer

SqlOleDb

MS-Access (or) MS-Excel

Microsoft.Jet.OleDb.4.0

21 | P a g e

IndexingServer

Msidxs

2) DataSource
It is the name of target machine to which we want to connect with doesnt require to be specified if
data source is on local machine only.
3) User Id & Password
As Databases are secured places for storing data, to connect with them we require a valid
Username and Password.
Oracle

Scott/tiger

SqlServer

Sa/<pwd>

4) Database (or) Initial catalog

These attributes are used while connecting with SqlServer to specify the name of Database, we
need to connect with.
5) Trusted_Connection (or) Integrated Security
These attributes are used while connecting with SqlServer to specify that we want to use 'Windows
Authentication'.
6) DSN
This attributes are used to connect with a data source using 'Odbc' drivers.
Connection String for Oracle
"Provider=Msdora; User Id=Scott; Password=tiger [; DataSource=<server>]"

Connection String for SQLServer

"Provider=SqlOleDb;
source=<server>]"

User

Id=Sa;

Password=<pwd>;

Database=<db

name>

Methods and Properties of Connection Class

1) Open () -> Opens a connection with data source
2) Close () -> Close the connection which is open
3) State -> Gets the status of Connection
4) Connectionstring -> Gets or Sets a connectionstring associated the connection object.
-> The object of class Connection can be created in any of the following ways
Connection con=new Connection ();
Con.ConnectionSting="<con str>";
* Open a new Project of type Windows and name it as "DBOperations".
22 | P a g e

[;

Data

-> Create the 'Form' as following.

using System.Data.OleDb;
Under Connect with Oracle button:
OleDbConnection
oracon
=
new
Id=Scott;Password=tiger");
oracon.Open();
MessageBox.Show(oracon.State.ToString());
oracon.Close();
MessageBox.Show(oracon.State.ToString());

OleDbConnection("Provider=Msdora;User

Under Connect with SQL button:

OleDbConnection
Password=123;

sqlcon = new OleDbConnection("Provider=SqlOleDb;

Database=mydb; Data Source=praveen");

User

Id=Sa;

sqlcon.Open();
MessageBox.Show(sqlcon.State.ToString());
sqlcon.Close();
MessageBox.Show(sqlcon.State.ToString());
Sending request as a Statement
In this process we send a request to Data source specifying the type of action we want to perform
using a Sql Statement like Insert, Update, Delete and Select. 'Command' class is used for sending
request and executing of the statements.
Command ()
Command (string sqlstmt, Connection con)
Properties of Command class
1) Connection: - Sets or gets the connection object associated with Command.
23 | P a g e

2) CommandText: - Sets or gets the statement associated with Command.

Object of class 'Command' can be created in any of these following ways.
Command cmd = new Command ();
cmd. Connection=<con>;
(or)
Command cmd=new Command ("<sql stmt>",con);
Methods of 'Command' Class
ExecuteReader ()

---> DataReader

ExecuteScalar ()

---> object

ExecutNonQuery ()

---> int

After creating the object of 'Command' class we need to call any of these three methods to execute
the statement.
-> Use 'ExecuteReader' method when we want to execute a 'Select' statement that returns data as
Rows and Columns. The method returns an object of class 'DataReader' which holds the data that is
retrieved in the form of Rows and Columns.
-> Use 'ExecuteScalar' method when we want to execute a 'Select' statement that returns a single
value result. Return type of this method is object, which gets the value in a generic type.
-> Use 'ExecutNonQuery' method when we want to ExecutNonQuery statements (DML statements
like Insert, Update and Delete). In this case we need to find out the number of Rows affected by the
statement and also the return type of the method is an 'int'.
Note: - The above process of calling an appropriate method in the appropriate case is our third step
capturing of the results.
Accessing Data from DataReader
DataReader is a class which will hold the data in the form of Rows and Columns (Table Structure).
To access data from DataReader it provides us following methods.
1) Read () -> bool
Move record 'Pointer' from the current location to next row and returns a Boolean status which tells
whether the row to which we have moved contains data in it or not. That will be 'true' if present or
'false' if not represent.
2) GetValue (int index) -> object
Used for retrieving field values from the row to which 'pointer' was pointing by specifying the
Column index position.
Note: - We can access the row pointer by pointer in the form of a single Dimensional array also
either by specifying column index position or name as following.
<DR> [index]
24 | P a g e

-> object

<DR> [columnname]

-> object

3) GetName (int index) -> string

Returns the name of column for given index position.
4) NextResult ()

-> bool

Moves the record pointer from current table to next table if a table exists and returns true or else
returns false.

using System.Data.OleDb;
Declarations:
OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader dr;
Under Form Load:
con = new OleDbConnection("Provider=Msdaora;User Id=Scott;Password=tiger");
cmd = new OleDbCommand("Select Deptno,Dname,Loc From Dept", con);
con.Open ();
dr = cmd.ExecuteReader();
label1.Text = dr.GetName (0);
label2.Text = dr.GetName (1);
label3.Text = dr.GetName (2);
ShowData ();
25 | P a g e

private void ShowData()

{
if (dr.Read())
{
textBox1.Text = dr.GetValue (0).ToString ();
textBox2.Text = dr.GetValue (1).ToString ();
textBox3.Text = dr.GetValue (2).ToString ();
}
else
MessageBox.Show ("Last Record");
}
Under Next button:
ShowData ();
Under Close button:
if (con.State != ConnectionState.Closed)
con.Close ();
this.Close ();

-> Set Deptno TextBox 'ReadOnly' property as 'true'

using System.Data.OleDb;
26 | P a g e

Declarations:
OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader dr;
string sqlstr;
Under Form Load:
con = new OleDbConnection("Provider=Msdaora;User Id=Scott;Password=tiger");
cmd = new OleDbCommand();
cmd.Connection = con;
LoadData ();
private void LoadData()
{
sqlstr = "Select Deptno,Dname,Loc From Dept Order By Deptno";
SetStmt ();
dr = cmd.ExecuteReader();
ShowData ();
}
private void SetStmt()
{
if (con.State != ConnectionState.Closed)
con.Close ();
cmd.CommandText = sqlstr;
con.Open ();
}
private void ShowData()
{
if (dr.Read())
{
textBox1.Text = dr [0].ToString ();
textBox2.Text = dr [1].ToString ();
textBox3.Text = dr [2].ToString ();
}
else
MessageBox.Show ("Last record of the Table");
}
private void ExecuteDML()
{
DialogResult d = MessageBox.Show (sqlstr + "\n\nDo you wish to execute the Query?",
"Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (d == DialogResult.Yes)
{
SetStmt ();
int Count = cmd.ExecuteNonQuery();
if (Count > 0)
MessageBox.Show ("Statement Executed Successfully");
Else
MessageBox.Show ("Statement Execution Failed");
LoadData ();
27 | P a g e

}
}
Under Next button:
ShowData ();
Under New button:
textBox1.Text = textBox2.Text = textBox3.Text = ""; sqlstr = "Select Max(Deptno) + 10 From
Dept";
SetStmt ();
textBox1.Text = cmd.ExecuteScalar ().ToString ();
textBox2.Focus ();
Under Insert button:
sqlstr=String.Format("Insert
Into
Dept
textBox1.Text, textBox2, Text, textBox3.Text);
ExecuteDML ();

(Deptno,Dname,Loc)

Values({0},'{1}','{2}')",

Under Update button:

sqlstr=String.Format ("Update Dept Set Dname='{0}', Loc='{1}' Where Deptno= {2}",
textBox2.Text, textBox3.Text, textBox1.Text);
ExecuteDML ();
Under Delete button:
sqlstr = String.Format("Delete From Dept Where Deptno= {0}", textBox1.Text);
ExecuteDML ();
Under Close button:
if (con.State != ConnectionState.Closed)
con.Close ();
this.Close ();
Working with SQL Server
Sql Server is a collection of Databases, where a database is again collection of various objects like
tables, views, procedures etc; users can be owner of 1 or more databases at a time, so while
connecting with Sql server from a .NET application within the connection string we need to specify
name of the database we want to connect either by using Database or Initial Catalog attributes.
Sql Server provides 2 different modes of Authentication for connecting with the DB server those
are
1. Windows Authentication

2. Sql Server Authentication

When a user connects through windows authentication, Sql Server validates the account name and
password using the windows principal token in the operating system; this means that the user
identity is confirmed by windows, Sql Server does not ask for the password and does not perform
the identity validation. When using Sql Server authentication, logins are created in Sql server that
is not based on Windows user accounts, both the user name and password are created by using Sql
Server and stored in Sql Server database. Users connecting with Sql Server authentication must
provide their credentials every time they connect with Database server.

28 | P a g e

Note: - If we want to connect from a .NET application using Windows authentication mode, within
the connection string in the place of User Id and Password attributes use
"Trusted_Connection=True" or "Integrated Security=SSPI" attributes.
Connection String for Sql Server Authentication:
"Provider=SqlOleDb; User
Source=<Server Name>]"

Id=SA;

Password=<pwd>;

Database=<DBName>

[;

Data

Connection String for Windows Authentication:

"Provider=SqlOleDb; Trusted_Connection=True; Database=<DBName> [; Data Source=<Server
Name>]"
"Provider=SqlOleDb; Integrated Security=SSPI; Database=<DBName> [; Data Source=<Server
Name>]"
We can connect with Sql Server either by using OleDb or SqlClient classes when using
SqlConnection or OracleConnection string doesn't require 'Provider' attribute as these classes are
designed specific for those databases.

Creating Database on Sql Server

Go to Start Menu
-> Programs
-> MS Sql Server
-> Sql Server Management Studio
Click on it to open & provide the authentication details to login. Once the studio is opened in the
LHS we find a window "Object Explorer", in that right click on the node Databases, Select "New
Database" that opens a windows asking for the name, enter name as "CSharDB", click 'OK' which
adds the database under databases node. Now expand the CSharpDB node, right click on Tables
node & select "New Table" which opens a window asking for column names & data types enter the
following.
Eno (int), Ename (Varchar), Job (Varchar), Salary (Money), Photo (Image), Status (Bit)
Select Status column and go into column properties in the bottom and set "Default value or
Binding" property as 1, which takes the default value for status column as true. Click on the save
button on top of the studio which will prompt for table name enter name as "Employee" & Click
OK which adds the table under tables node. Now right click on the table created and select "Edit"
which opens a window under it enters the data we want ignoring Photo and Status columns. Close
the Studio.

29 | P a g e

using System.Data.SqlClient;
Declarations:
sqlConnection con;
sqlCommand cmd;
sqlDataReader dr;
Under Form Load:
con=new sqlConnection("User Id=Sa;Password=<pwd>;Database=C#DB");
cmd=new sqlCommand("Select eno,ename,job,salary From employee",con);
con.Open();
dr=cmd.ExecuteReader();
Label1.Text=dr.GetName(0);
Label2.Text=dr.GetName(1);
Label3.Text=dr.GetName(2);
Label4.Text=dr.GetName(3);
ShowData();
}
private void ShowData()
{
if(dr.Read())
30 | P a g e

{
textBox1.Text=dr[0].ToString();
textBox2.Text=dr [1].ToString ();
textBox3.Text=dr [2].ToString ();
textBox4.Text=dr [3].ToString ();
}
else
MessageBox.Show ("Last record of the Table");
Under Next button:
if(con.State !=ConnectionState.Closed)
con.Close ();
this.Close ();
DataReader
It is a class that can hold you the data on client machine in the form of Rows and Columns.
Features of DataReader
1) Faster in access to data from the data source as it is Connection oriented.
2) Can hold multiple tables in it at a time. To load Multiple table with DataReader pass multiple
selection statements as args to command separated by a ';'
Eg:
Command
Teacher; on);

cmd=new

Command

(Select*From

Statements;

Select*From

DataReader dr=cmd. ExecuteReader ();

Note: - Use NextResult () method on DataReader object to navigate from current table to next
table.
Eg: - dr. NextResult ();
Drawback of DataReader
As it is Connection Oriented requires a permanent Connection with Data Source to access the data.
So performance get degreased, if there are number of clients accessing the data same time.
It gives forward only access to the data i.e., allows to go either to next record (or) table but not to
previous record (or) table.
It is a ReadOnly object which will not allow any changes to data present in it.
Dis-Connected Architecture
ADO.NET provides two different architecture for data source communication.
31 | P a g e

1) Connection Oriented Architecture

2) Dis-Connected Architecture

In the first case we require a continuous connection with data source for accessing the data in it.
Here 'DataReader' class is used for holding data on client machines, where as in the second case we
dont require a continuous connection with Data source for accessing of the data. Here 'DataSet'
class is used for holding the data on client machines.
DatSet
It is a class under 'System.Data' namespace used for holding and managing data on Client machine
apart from DataReader.
Features of DataSet
1) It is capable of holding multiple tables.
2) It is designed in Dis-Connected architecture which doesn't require any permanent connection
with source for holding of the data.
3) If provides scrollable navigation to data that allows to navigate in any direction that is top to
bottom or bottom to top.
4) It is updatable that is changes can be performed to data present in it. And also send them back to
database.
Working with DataSet
The class that is responsible for loading of data into a DataReader from DataSource is 'Command'.
In the same way 'DataAdopter' class is used for communication between data source and 'DataSet'.
DataReader <------- Command -------> DataSource
DataSet <-------> DataAdopter <------> DataSource
Constructors
DataAdopter(string stmt,Connection con)
DataAdopter(Command cmd)
Eg: - DataAdopter da=new DataAdopter("<sql stmt>",con);

Methods of Adopter
-> Fill (DataSet ds,string tname)
-> Update (DataSet ds,string tname)
32 | P a g e

'Fill' is to load Data from a DataSource into DataSet. 'Update' is to transfer data from a DataSet to
DataSource.
Data Adopter is internally collection of four commands.
1) Select Command
2) Insert Command
3) Update Command
4) Delete Command
When we call 'Fill' method on Adopter following things takes place
1) Opens a Connection with Data Source
2) Execute the Select Command under it on the Data Source and loads data from table to DataSet.
3) Closes the Connection.
As we aware DataSet is Updatable we can make changes to the Data that in loaded into it like
Adding, Modifying and Deleting.
After making the changes to data if we want to send those changes back to data source we need to
call 'Update' method on Adopter, which performs the following.
1) Re-Open the connection with Data source
2) Changes that are made in DataSet will be back to the table where in this process it will make use
of Insert, Update and Delete Commands of Adopter.
3) Closes the Connection.

Accessing Data from DataSet

33 | P a g e

'DataReader' provides pointer based access to the data. So we can get the data only in a sequential
order where as 'DataSet' provides Index based access to the data. So we can get the from any
location ReadOnly.
'DataSet' is a collection of tables where each table is represented as a class 'DataTable' and
identified by its Index position or name.
DataSet:
-Collection of Tables (DataTable) <dataset>.Tables [Index]
(Or)
-ds.Tables [0] (or) ds.Tables ["Employee"]
-> Every DataTable is again collection of Rows and Columns where each Row is represented as a
class 'DataRow' and identified by its IndexPosition. Each column is represented as a class
'DataColumn' and identified by its Index position or name.
DataTable:
-Collection of Rows (DataRow)
< datatable >.Rows[Index]
ds.Tables [0].Rows [0]

-Collection of Columns (DataColumns)

<datatable>.Column[Index] (or) Column[name]
ds.Tables[0].Column[0] (or) ds.Tables[0].Columns["Eno"]
Referring to a cell under DataTable:
<datatable>.Rows[row][col]
ds.Tables [0].Rows [0] [0]
Or
ds.Tables [0].Rows[0]["Eno"]

34 | P a g e

-> Add reference of 'Microsoft.VisualBasic' assembly from .NET tab of add reference window and
write the following code.
using System.Data.SqlClient;
using Microsoft.VisualBasic;

Declarations:
SqlConnection con;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;
int rno = 0;
\Under Form Load:
con = new SqlConnection("User Id=sa;Password=123;Database=mydb");
da = new SqlDataAdapter("Select Empno,Ename,Job,Sal from Emp", con);
ds = new DataSet();
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, "Emp");
ShowData();
private void ShowData()
35 | P a g e

{
textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();
}
Under First button:
rno = 0;
ShowData();
Under Prev button:
if (rno > 0)
{
rno -= 1;
if (ds.Tables[0].Rows[rno].RowState == DataRowState.Deleted)
{
MessageBox.Show("Deleted Row Cannot be Accessed");
return;
}
ShowData();
}
else
MessageBox.Show("First Record of the Table");
Under Next button:
if (rno < ds.Tables[0].Rows.Count - 1)
{
rno += 1;
if (ds.Tables[0].Rows[rno].RowState == DataRowState.Deleted)
{
MessageBox.Show("Deleted Row Cannot be Acceessed");
return;
}
ShowData();
}
else
MessageBox.Show("Last Record of the Table");
Under Last button:
rno = ds.Tables[0].Rows.Count - 1;
ShowData();
Under New button:
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";
int index = ds.Tables[0].Rows.Count - 1;
textBox1.Text = (Convert.ToInt32(ds.Tables[0].Rows[index][0])+1.ToString());
textBox2.Focus();
36 | P a g e

//Adding a new record under data table

// To add new records in DataTable of DataSet adopt the following process:
//1) Create an empty row by calling the method NewRow () on DataTable
//2) Assign values to the new row by treating it as a single dimensional array.
//3) Call the Add method and add the row to DataRowCollection.
Under Insert button:
DataRow dr = ds.Tables[0].NewRow();
dr[0] = textBox1.Text;
dr[1] = textBox2.Text;
dr[2] = textBox3.Text;
dr[3] = textBox4.Text;
ds.Tables[0].Rows.Add(dr);
MessageBox.Show("Record Added to Table");
rno = ds.Tables[0].Rows.Count - 1;
//Updating an existing record of DataTable: To Update an existing record of data table re-assigns
modified values back to the row under data table, so that old values get changed to new.
Under Update button:
ds.Tables[0].Rows[rno][1] = textBox2.Text;
ds.Tables[0].Rows[rno][2] = textBox3.Text;
ds.Tables[0].Rows[rno][3] = textBox4.Text;
MessageBox.Show("Record modified under data table");
//Deleting an existing record of DataTable: To delete a record under data table call delete method
on DataRowCollection pointing to the row that has to be deleted.
Under Delete button:
ds.Tables[0].Rows[rno].Delete();
MessageBox.Show("Record deleted from data table");
button1.PerformClick();
//Saving changes made on DataTable of DataSet to DataBase: If we want to save changes made on
a DataTable of DataSet to DataBase we need to call Update method on DataAdapter by passing the
DatSet which contains modified values in it. If update method of DataAdapter has to work it
should contain the 3 commands under it i.e. Insert, Update and Delete, these 3 commands have to
be written by the programmers manually or generate them with the help of CommandBuilder class.
CommandBuilder if given with DataAdapter that contains a Select Command in it will generate the
remaining 3 commands that are required.
Consturctor: CommandBuilder (DataAdapter da)
Note: - CommandBuilder can generate us Update and Delete commands for a
given select
command only if the table contains Primary Key Constraints on it. To add a Primary key constraint
on our Employee table open SqlServer Management Studio-->
37 | P a g e

--> Click on "New Query"

--> Choose our CSharpDB database and write the following statements in the query window and
execute.
ALTER TABLE EMPLOYEE ALTER COLUMN INT NOT NULL
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (ENO)
Under SaveToDatabase button:
cb = new SqlCommandBuilder(da);
da.Update(ds, "Employee");
MessageBox.Show("Data Saved to DataBase");
//Searching a record of DataTable: To search a record of datatable call Find method on
DataRowCollection that can search the data on Primary Key Column(s) of table and returns a Row.
Find (Object key) -> DataRow
Note:- If the find method has to work we need to first load the Primary Key information of table
into DatSet by setting the property values as "AddWithKey" for MissingSchemaAction of
DataAdapter.
Under Search button:
string value = Interaction.InputBox("Enter Employee No.", "Employee Search", "", 150, 150);
if (value.Trim().Length > 0)
{
int eno = int.Parse(value);
DataRow dr = ds.Tables[0].Rows.Find(eno);
if (dr != null)
{
textBox1.Text = dr[0].ToString();
textBox2.Text = dr[1].ToString();
textBox3.Text = dr[2].ToString();
textBox4.Text = dr[3].ToString();
}
else
MessageBox.Show("Invalid Employee No.");
}
Under Close Button:
this.Close();

DataGridView Control
38 | P a g e

It is a control that can display data in the form of Rows and Columns, that is table structure we can
directly bind a DataTable to the Control using Data Source property of the Control. So that all the
records of DataTable gets displayed under Gridview.
dataGridView1.DataSource=<data table>
The control has a specialty, that is changes can be made to the data that is present under the control
and these changes gets reflected directly into the DataTable to which the GridView was bound
without any coding.

Eg:-

using System.Data.SqlClient;
Declarations:
SqlConnection con;
SqlDataAdapter da;
SqlCommandBuilder cb;
39 | P a g e

DataSet ds;
Under Form Load:
con = new SqlConnection("User Id=sa;Password=123;Database=MYDB");
da = new SqlDataAdapter("Select Empno,Ename,Job,Sal From Emp", con);
ds = new DataSet();
da.Fill(ds, "Emp");
dataGridView1.DataSource = ds.Tables[0];
Under Save to DB button:
cb = new SqlCommandBuilder(da);
da.Update(ds, "Emp");
MessageBox.Show("Data Saved to DB");
Under Close Button:
this.Close();
Note: Before installing the Setup on the Client Machine make sure that the .Net Framework is
installed on it.

Compilation & Execution of C# Project

40 | P a g e

Remoting
Application Architecture's: Every application contains 3 different parts in it like:
1. UI Part

2. Logic Part(BL+DL)

3. Data Source Part

Organizations as per their requirement adopts different architecture's for execution of their
application those are:
1. 'Single Tier Architecture': In this model all the 3 parts (UI+Logic+DB) will reside on the same
machine to execute, so as the Database is also present on every machine changes made on 1
machine will not be reflected to the other.
2. '2-Tier Architecture: In this model the (UI+Logic) sits on all the client machines, moving the DB
to a centralized location, so all the clients connect with the same DB Server to store & access data.
In this model changes made on 1 machine reflects to the other.
3. '3-Tier Architecture: In this model all the 3 parts will sit on 3 different machines to execute, so
the client machine what we have is only light weight client(UI) which will connect with the logic
part residing on server machine that will in turn connect with the DB server. Maintanance of the
software becomes easier in this model there were more number of client accessing the application.

41 | P a g e

To develop a 3-Tier application in desktop model we have various distributed technologies like:
RPC (Remote Procedure Call)
CORBA (Common Object Request Broker Architecture)
RMI (Remote Method Invocation (Java))
DCom (Distributed Component Object Model)
Remoting (.NET Languages)

Remoting is a technology from Microsoft for developing distributed applications replacing

traditional DCOM available under COM. All the distributed technologies speak about the same i.e.
consuming of libraries present on remote machines. In .NET libraries were implemented as
Assemblies, where we can consume an assembly residing on local machine by adding as reference.
We can now consume Assemblies residing on remote machies also using Remoting.

Developing Remoting Application: To develop a Remoting Application first we need to understand

various things regarding the process of communication, those are:
1. Serializatione & De-Serialization:
To exchange information between both the parties they make use of a process known as
Serialization and De-serialization. As applications represent the data in High Level(Object Format)
which are not free flowable, needs to be converted into Low Level(Binary or Text) and then
transferred to the other system where on the target macine Low Level data has to converted back
into High Level.
Serialization is a process of converting high level data to low level and De-Serialization is in
opposite of serialization that converts low level data to high level.

42 | P a g e

To perform Serialization & De-Serialization remoting provides Formatter Classes, those are:
-Binary Formatters:

-TCPServerChannel

-Soap Formatters:

-HttpServerChannel

-TCPClientChannel
-HttpClientChannel

Binary Formatters are used for binary serialiaztion and De-Serialization & Soap Formatters are
used for text serialization and de-serialization.
Note: - Traditional DCOM supports only binary.

2. Marshalling and UnMarshalling:

After serializing the data which has to be sent to the target machine it packages the data into
packets this is what we call as Mrshalling, at this time it associates the IP Adress of the target
machine where the information has to be sent. UnMarshalling is in opposite to Marshalling which
opens the packets of data for de-serializing.
IP-Address: Every system in a network is identified by using a unique id known as IP Adress. It is
a 4 part numeric value where each part will be ranging between 0-255. eg: 0-255.0-255.0-255.0255
We can mask IP Adress of a system by specifying an alias name to it known as HostName. System
under a network will be configured as following.
IP Adress

HostName

192.168.26.0 (Praveen)

192.168.26.24 (Naveen)
Note: - A class which is available on the server for the consumption of clients is referred as a
'Remote Class' and this Remote Class will contain all the Methods that are necessary for the
clients.

3. Activation Models:
43 | P a g e

In execution of Remoting application clients needs object or remote class to invoke methods under
it. Activation Models decide where the remote class objects resides in execution of the application.
Remoting supports 2 different activation models.
-Server Activated Objects (SAO)

-Client Activated Objects (CAO)

In SAO model object of remote class resides on server Machine and a reference of this object is
maintained on the client machine using which clients can invoke the methods of remote class.
In CAO model object of remote class resides on client machine using which clients can invoke the
methods of remote class.

Server Activated Objects (SAO):

'SingeTon' & 'SingleCall'.

In SAO model we were again provided with 2 types like

SingleTon: In this case whenever a first request comes from a client an object of remote class gets
created and its reference is given to the client, from then every new request coming from a new
client, server provides the reference of same object which is already created, so changes made by 1
client gets reflected to the other. Used in the development of application like public chat, cricket
scores, share prices etc...

SingleCall: In this model whenever a requedt comes from a client 1 object of remote class gets
created and its reference is given to client, once the request is served immediately object gets
destroyed without allowing him to make any other requests on that object, for a new request a new
object gets created again and destroyed. Used in the developement of single request application
like "Railway PNR Status Enquiry", "ATM Machines" & "Examination Results".

44 | P a g e

Note: - This is very highly secured model used in application development.

Client Activated Objects (CAO): In this case whenever the first request from a client an object of
remote class is created and provided, using which the client can make any number of requests.
Here a separate object will be given for each client so changes made by 1 client will never reflect
to the other. Used in the development of application that requires multiple requests for a single
client like "Traditional ATM Machines", where we can perfrom multiple transactions once we
insert the card.

Note: - Traditional DCOM supports only CAO.

1. Server:
When we want to develop an application to be consumed from remote machines we require
someone to take the request from clients. To take a request from client we use server's, which
works on the principles request and response. We can install multiple server software's on a
machine, but each server should be running on a separate logical address known as port.

45 | P a g e

In process of developing remoting application it is our responsibility to develop a server that takes
requests of clients to the Remote Class, because a class is not capable of taking the request. Te
server should be running on a unique port of the OS. By default every machine has port's that are
ranging between 0-65535 in which 0-1023 were OS reserved ports, rest can be used by any
application.
After developing a Remote Server every Remote Class on the machine has to be registered under
the Server with an alias name, so that clients can send their request to required class.

5. Remote Interface:
In remoting the methods what we want to define under Remote Class, that are accessible to clients
should be first declared under an interface & then implemented in remote class which was a rule.
The same interface will also be provided to clients that act as a proxy to Remote Class on client
machine, which will be used in 2 ways.
1. Using it clients can recognize methods of remote class (works as Metadata).
2. Using it we can hold reference of remote class on client machines.

46 | P a g e

Execution of a Remoting Application:

1. Client sends a request to Server.
2. Server creates object of Remote Class.
3. Sends reference of that object to client which has to be captured under interface variable.
4. After capturing the variable gets converted into reference pointing to the object on Server.
5. Now UI can invoke methods on the reference.
6. Methods get executed on server machine as the reference points to object on server.

To develop a Remoting Application we required the following:

1. Interface (Class Library)
2. Remote Class (Class Library)
3. Remote Server (Windows Service)
4. Client (UI) (Windows or Console Application)

Developing an Interface:
Open a new project of type "Class Library" & name it as "InterfaceProject". Delete the 'Class1.cs'
file under the project and add an "Interface" to the project naming it "IRemoteApp.cs", and write
the following code:
public interface IRemoteApp
{
string Get_Ename(int eno);
47 | P a g e

decimal Get_Bal(int custid);

string SayHello();
}
Now open 'Solution Explorer' and build the project which generates an assembly
'InterfaceProject.dll'.

Developing a Remote Class:

The Remote Class needs to be inherited from the pre-defined class "MarshalByRefObject" &
implement all the methods that were declared under interface.
'MarshalByRefObject' should be the base class for objects that communicate across application
domain boundaries by exchanging messages using a proxy in applications that supports remoting.

Open a new project of type 'Class Library' and name it as "ClassProject", rename the class
'Class1.cs' as "ClsRemoteApp.cs" using 'Solution Explorer'. Add reference of 'InterfaceProject.dll'
we have created previously & write the following code.

using InterfaceProject;
using System.Data.SqlClient;
public class ClsRemoteApp:MarshalByRefObject,IRemoteApp
{
SqlConnection con;
SqlCommand cmd;
int x=0;
public ClsRemoteApp()
{
con=new SqlConnection("User Id=sa;Password=123;Database=mydb");
cmd=new SqlCommand();
cmd.Connection=con;
}
public string Get_Ename(int eno)
{
string name=null;
try
48 | P a g e

{
cmd.CommandText="Select Ename From Employee Where Eno="+eno;
con.Open();
name=cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
ex=null;
}
finally
{
con.Close();
}
retrun name;
}
public decimal Get_Bal(int custid)
{
decimal bal=0;
try
{
cmd.CommandText="Select Balacne From Customer Where Custid="+custid;
con.Open();
bal=Convert.ToDecimal(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ex=null;
}
finally
{
49 | P a g e

con.Close();
}
return bal;
}
public string SayHello()
{
x+=1;
return "Hello: "+x;
}
public string Demo()
{
return "Not accessible to Remote Clients";
}
}

Process to develop a RemoteServer:

If you want to develop a remote server that should be started along with the Operating System,
develop it as a "Windows Service". A Windows Service is an application which runs in the
background process without any knowledge to end users they will be under control of Operating
System gets started when Operating System is started & stopped when we shutdown the system.
Databases & Web Servers will be implemented as Windows Services only. Every system by default
has number of services installed on it; you can view the Services that are present on a machine
making use of Services that are present on machine making use of Services Window as following:

-> Start Menu

-> Control Panel
-> Administrativ Tools
-> Services
(or) In 'run' command type 'services.msc'.

Every Service will have 4 attributes to it, those are:

1. Display Name: It is the name for identification.
50 | P a g e

2. Description: It is brief information about the service.

3. Startup Type: It decides how a service gets started on the machie, which can be set with 3
options.
-Automatic starts the services at System login
-Manual starts a servcie as required or when called form an applicaiton.
-Disabled completely disable the service and prevent it and its dependencies from running.
4. LogOnAs (Account): Indicates the account type under wihch the service runs, can be set with 3
options like User, Local System and Network Service.

Note: - While developing an Windows Service it is our responsibility to set all above 4 attributes.

How to develop a Windows Service

If we want to develop a Windows Service we can make use of "Windows Service" Project template
under VS. To choose Windows Service project template, under new project window expand the
Language node i.e. Visual C# in the left hand side panel and select the option 'Windows' which
dispalys Wiondws Service template on right hand side panel.
Every Windows Service class is a sub class of pre-defined class 'ServiceBase' present under the
namespace 'System.ServiceProcess'. In a Windows Service code has to be written under few
overridden methods like 'OnStart', 'OnStop', 'OnPause', 'OnContinue' etc..., which were declared as
virtual under parent class 'ServiceBase'. Code under 'OnStart' executes when service is started,
'OnStop' executes before the service is getting stopped, 'OnPause' executes when the service is
paused, 'OnContinue' executes when the service is resumed.
After writing code in a service class we need to set the 4 attributes discussed previously, for this,
under the project we are given with "Add Installer" option using which we can set the attributes.
Once we build the project it will generate an 'exe' assembly that should be installed on a machine
by using "installutil" tool. Once the service is installed we can view this under "Services Window".

Developing a Remote Server

Open a new project of type windows service and name it as 'RemoteServer'. Add reference of 3
assemblies to the project.
51 | P a g e

1.System.Runtime.Remoting.dll(.net) 2.ClassProject.dll(browse)

3.InterfaceProject.dll(browse)

Write the following code in Code View:

using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;

Under OnStart Method:

TcpServerChannel chan=new TcpServerChannel(5000);
ChannelServices.RegisterChannel(chan,true);
RemotingConfiguration.RegisterWellKnownServiceType(typeof(ClassProject.ClsRemoteApp),"X
XX",WellKnownObjectMode.Singleton);

Now go to design view of the project, right click on it & select Add Installer, which adds 2
components ServiceInstaller1 & ServiceProcessInstaller1 using which you need to set the 4
attributes of service. Under ServiceInstaller1 Properties set following.

1.DisplayName:RemoteServer
2.Description:Takes request from remote clients
3.StartupType:Automatic
And under ServiceProcessInstaller1 Properties set 'Account' property as "Local System" & build
the project which creates an assembly "RemoteServer.exe"

Installing the service on your machine:

To install the service on a machine we were given with a command line utility "installutil", which
has to be used as following:
Installutil [-u] <service exe name>
Note: - To un-install an installed service use the "-u" option.
Open Visual Studio Command Prompt and go into the location where "RemoteServer.exe" is
present and write the following:
<drive>\<folder>\RemoteServer\RemoterServer\bin\debug>installutil RemoteServer.exe
Now we can our Service under Services Window, right click on it & select start which will start the
server. To check server running or not, open Visual Studio Command Prompt and use the statment
"netstat -a".
52 | P a g e

To develop Remote Server we follow the below process:

Step1: Create an object of TcpServerChannel or HttpServerChannel by passing port number as
argument to the constructor.
System.Runtime.Remoting.Channels.Tcp.TcpServerChannel(int port)
System.Runtime.Remoting.Channels.Http.HttpServerChannel(int port)
Step2: Register the channel under OS using RegisterChannel static method of ChannelService class
System.Runtime.Remoting.Channels.ChannelServices.RegisterChannel(Channel obj,bool security)
ture -secured; false unsecured
Step3: Register the remote class under remote server with an alias name using the static method
RegisterWellKnownServiceType of the class RemotingConfiguration.
System.Runtime.Remoting.RemotingConfiguration.RegisterWellKnownServiceType(Type
type,string alias,Mode mode)
Note: - Mode can be SingleTon or SingleCall

Developing the Client(UI):

Under Client application we perform the following:
Step1: Create object of appropriate client channel class which doesn't require any port number.
Step2: Register the Channel under Operating System.
Step3: Send request from client to remote server requesting for reference of Remote Class using
the static method 'GetObject' of Activator class present under System namespace.
Activator.GetObject(Type type,string url) -> Object
URL: Unifrom Resource Locator
<protocol>://<hostname>:<port>/<requestfor>
e.g: http://www.yahoo.com:80/index.html

Format:
tcp://<server name>:5000/xxx

Step4: Server takes request & provides reference of RemoteClass to client in object format as
return type of GetObject method is object.
Eg:- Object obj=Activator.GetObject(<type>,<url>)
Step5: Now client needs to convert reference of Remote class present in object format to Interface
format.
Eg:- IremoteApp ira=(IRemoteApp)obj;
53 | P a g e

Step6: Now using the reference invoke methods of Remote Class that executes on server machine
only.

Deeloping Client Application (UI):

Open a new project of type windows, name it as RemoteClient & create the form as following:

Write the following code by adding reference of following assemblies:

1. System.Runtime.Remoting.dll(.net)
2. System.Configuration.dll(.net)
3. InterfaceProject.dll(browse)
Add an "Application Configuration File" under the project using "Add New Item" windows and
wirte the following between <configuration> tags:
<appSettings><add key="URL" value="tcp://server:5000/xxx"/></appSettings>

using InterfaceProject;
using System.Configuration;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channesl.Tcp;

Declarations:
IRemoteApp ira;

54 | P a g e

Under Form Load:

string url=ConfigurationManager.AppSettings.Get("URL");
TopClientChannel chan=new TcpClientChannel();
ChannelServices.RegisterChannel(chan,true);
Object obj=Activator.GetObject(typeof(IRemoteApp),Url);
ira=(IRemoteApp)obj;

Under Call SayHello button:

button1.Text=ira.SayHello();
Under Call Get_Sname butotn:
button2.Text=ira.Get_Sname(int.Parst(textBox1.Text));
Under Call Get_Bal Button:
button3.Text=ira.Get_Bal(int.Parst(textBox2.Text)).ToString();

Execution of the Application:

As our Remote Server is a windows service it gets generated automatically whenever OS is started.
Now run the Remote Client application we have developed and test it. Once it was working
perfectly prepare a set-up for the application which includes RemoteClient.exe, ConfigFile &
InterfaceProject.dll which can be carried & installed on any system in the network. To run client
application on a machine, first open the config file, edit the "URL" and then start the application.

SingleTon vs SingleCall
Right now remote class is registered under remote server in singleton mode, in this mode whenever
the first request comes from the client an object of remote class is created and it's reference is
provided to the client, form then for any new request coming from a new client server provides
reference of same object that is already created, so all clients share same object memory because of
this changes mady by any 1 client gets reflected to others. To change mode of application from
sigleton to singlecall follow the belwo process:
1) Open Visual Studio command prompt, go into the folder where RemoteSerer.exe was present &
un-install it using 'installutil' tool
eg: installutil -u RemoteServer.exe
2) Now open RemoteServer project in VS & under it change the mode from SingleTon to
SingleCall with in the 3rd statement of OnStart method and rebuild the project.
3) Re-install RemoteServer from VS command prompt again.
55 | P a g e

eg: installutil RemoteServer.exe

4) Open the services window, start the server for first time & then run the application again to
check the difference in results.

Multi Threading
A single program performing multiple options in a symaltanious fashion is known as 'Multi
Threading'.
Traditionally we have a concept known as 'Multi Tasking' where multiple programs can execute at
a time, which is basically supported by your Operating System.
Eg: Windows, Unix, Linux etc
Where 'Dos' is a Single Tasking Operating System, which is capable of running only one program
at a given point of time.
Multi Threading is supported by languages with the help of Operating System.
A thread is a unit of execution and by default every program has a thread responsible for executing
the program that is 'Main Thread', means every program is by default 'Single Threaded' that
executes a program by performing the actions one by one.
In single threaded model because the action gets performed one after the other until the current
action is completed, we cannot go to the next action, even if we have ideal time in performing the
action also.
To Overcome the above drawback we use 'Multi Threading', where we will be using multiple
threads to perform multiple actions are calling multiple methods, that is one thread for each
method.
when a application is 'Multi Threaded' the execution of an application takes place as following..
1) Time Shared: Here the operating system allocates some time period for each thread to execute
and once the time is completed it gets automatically transfer to the other thread in execution giving
equal preference to all the threads.
2) Maximum Utilization of Resources: This comes into picture when the first principle violates,
that is if a thread could not execute for some reason in its given time period without waiting for it
to execute the control immediately gets transferred to the other thread in execution without wasting
the 'CPU' resources.
How to create a Thread
To create a thread we need to create object of the class 'Thread', where each object we create is
considered as a thread.
56 | P a g e

The class 'Thread' is present under 'System.Threading' namespace & while creating the object we
should explicitly specify the method we want to call in its constructor.
System.Threading.Thread(MethodName)
eg:

Thread t1=new Thread(Method1);

Thread t2=new Thread(Method2);

Open a new project of type 'Console' and name it as 'ThreadProject' and write the following coe in
the default class 'Program'.
using System.Threading;
class Program
{
Thread t1, t2;
public Program()
{
t1 = new Thread(Test1);
t2 = new Thread(Test2);
t1.Start(); t2.Start();
}
public void Test1()
{
for (int i = 1; i <= 100; i++)
{
Console.WriteLine("Test1:" + i);
if (i == 50)
Thread.Sleep(10000);
}
}
public void Test2()
{
for (int i = 1; i <= 100; i++)
{
Console.WriteLine("Test2:" + i);
}
}
static void Main(string[] args)
{
Program p = new Program();
Console.ReadLine();
}
}

'Start' is a Method of Thread class which starts the execution of your thread.
57 | P a g e

'Stop' is a statci method of thread class which makes the current executing thread to sleep until the
given time period is elapsed.

58 | P a g e