Overview Of ADO.NET

ADO.NET is a bundle of classes which stands for ActiveX data object. It is used to get the data from Back end to Front end.

Step to work with Ado .NET

1. Establish the Connection
2. Pass statement from front end to back end
3. Close the Connection

Classes which work with ADO.NET

• SqlConnection
• SqlCommand
• SqlDataAdapter
• Dataset

SqlConnection

SqlConnection class used to established the connection between front end and back end.

Syntax:

SqlConnection obj=new SqlConnection(“Integrated Security=true;Initial Catalog=Table_Name;Data Source=.”);-- for Windows authentication

SqlConnection obj=new SqlConnection(“user id= sa ; Password=sa123;server=.;database=name”); --Sql server Authentication

SqlCommand

SqlCommand is used to pass the statement from front end to back end by using the following syntax.

Syntax

SqlCommand objcmd=new SqlCommand(“Query Statement”,Connection object);

SqlDataAdapter

SqlDataAdapter is a mediator between database and dataset. SqlDataAdapter do not have a feature of getting the data directly from the database. Syntax for working with SqlDataAdapter is the following:

Syntax

SqlDataAdapter objda=new SqlDataAdapter(“query statement”,Connection object);

Dataset

Dataset contains Table and relation as:


Figure: Dataset

Data bound Control

Data bound control is used for displaying more than one record at a time. Here are the types of Data bound Control:

1. Repeater
2. Datalist
3. Grid view
4. Form View
5. Details View
6. List View

Data bound control fetch the data record by record and display the data field by field.

Sample Insertion Example for working with ADO.NET

SqlConnection con = new SqlConnection(“Integrate Security = true; Initial Catalog = Employee; data source = .”);
SqlCommand cmd();
Private void Page_Load() {}
Private void btnInsert_Click()
{
    Con.open();
    String s = “Insert into Emp values(@p1, @p2, @p3)”;
    Cmd = new SqlCommand(s, con);
    Cmd.CommandType = CommandType.Text;
    Cmd.Parameters.AddWithValue(“@p1”, txtEid.Text);
    Cmd.Parameters.AddWithValue(“@p2”, txtEname.Text);

    Cmd.Parameters.AddWithValue(“@p1”, txtsalary.Text);
    Int i = cmd.ExecuteNon Query();
    Con.Close();
}
   Messagebox.Show(i + “Rows are Inserted”);
}

Sample Example code for displaying the data in GridView:

Using System.Data;
Using System.Data.SqlClient;
SqlConnection con = new Sqlconnection(“Integrate Security = true; Initial Catalog = Employee; data source = .”);
SqlDataAdapter Da();
Private void Page_Load()
{
    String s = ”select * from Emp”;
    Da = new SqlDataAdapter(s, con);
    Dataset ds = new Dataset();
    Da.fill(ds, ”Empobj”);
    Gridview = ds.tables[0];
    GridView.DataBound();
}