Ado .Net

ADO.NET Tutorial with Inline SQL Examples

1. Introduction to ADO.NET

ADO.NET is a data access technology in .NET that allows communication with relational databases using managed providers such as SqlClient and OleDb.

2. SqlConnection and SqlDataReader Example (C#)

This example demonstrates reading data using inline SQL:

<script runat="server">
using System.Data.SqlClient;

SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr;

protected void Page_Load(object sender, EventArgs e)
{
    cnn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind");
    cnn.Open();
    cmd = new SqlCommand("SELECT * FROM Categories", cnn);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        lstCategories.Items.Add(dr["CategoryName"].ToString());
    }
}
</script>

VB.NET Equivalent:

<script runat="server">
Imports System.Data.SqlClient

Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    cnn = New SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind")
    cnn.Open()
    cmd = New SqlCommand("SELECT * FROM Categories", cnn)
    dr = cmd.ExecuteReader()
    While dr.Read()
        lstCategories.Items.Add(dr("CategoryName"))
    End While
End Sub
</script>

3. Inserting Records using Inline SQL

<script runat="server">
SqlCommand cmd;

protected void btnInsert_Click(object sender, EventArgs e)
{
    cmd = new SqlCommand("INSERT INTO Categories VALUES ('Condiments')", cnn);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Record saved");
}
</script>

4. Inserting TextBox Values

<script runat="server">
protected void btnInsert_Click(object sender, EventArgs e)
{
    string strSql = "INSERT INTO Categories VALUES ('" + txtCategoryName.Text + "')";
    cmd = new SqlCommand(strSql, cnn);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Record saved");
    txtCategoryName.Text = "";
    txtCategoryName.Focus();
}
</script>

5. Using DataAdapter and DataSet

<script runat="server">
using System.Data.SqlClient;

SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
    cnn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind");
    da = new SqlDataAdapter("SELECT * FROM Categories", cnn);
    da.Fill(ds, "Categories");
    dataGridView1.DataSource = ds.Tables["Categories"];
}
</script>

6. Working with Multiple DataTables

<script runat="server">
SqlDataAdapter da;
DataSet ds = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
    cnn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind");

    da = new SqlDataAdapter("SELECT CategoryId, CategoryName FROM Categories", cnn);
    da.Fill(ds, "Cat");

    da = new SqlDataAdapter("SELECT ProductId, ProductName FROM Products", cnn);
    da.Fill(ds, "Prod");

    dataGrid1.DataSource = ds;
}
</script>

7. Creating AutoIncrement Column in DataTable

<script runat="server">
DataTable dt = new DataTable("Purchase");
DataColumn dc;
DataRow dr;

protected void Page_Load(object sender, EventArgs e)
{
    dc = new DataColumn("ProductID", typeof(int));
    dc.AutoIncrement = true;
    dc.AutoIncrementSeed = 100;
    dc.AutoIncrementStep = 1;
    dt.Columns.Add(dc);

    dc = new DataColumn("ProductName");
    dt.Columns.Add(dc);

    dc = new DataColumn("Price");
    dt.Columns.Add(dc);

    dr = dt.NewRow();
    dr["ProductName"] = "Microsoft Mouse";
    dr["Price"] = 400;
    dt.Rows.Add(dr);

    dgPurchase.DataSource = dt;
}
</script>

8. ComboBox DisplayMember Binding

<script runat="server">
SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();

protected void Page_Load(object sender, EventArgs e)
{
    cnn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind");
    da = new SqlDataAdapter("SELECT CategoryId, CategoryName FROM Categories", cnn);
    da.Fill(ds, "Cat");

    cboCategories.DataSource = ds.Tables["Cat"];
    cboCategories.DisplayMember = "CategoryName";
}
</script>