Thursday, May 15, 2008

.Net Windows Forms Database Connectivity with Ado .Net

Ado .Net is a data access technology of .Net. Ado .Net Contains Managed Providers and DataSet. Two important Managed Providers are SqlClient and OleDb.

SqlClient namespace contains classes required for interacting with Sql Server 7 and above.

OleDb is used to connect to any RDBMS say Oracle, Access and even Sql Server. But it is not recommended using OleDb with Sql Server, since SqlClient is tailor made for Sql Server. SqlConnection class is used for establishing a live session between front end and back end. In this example we are using Integrated Security, ie. using the windows credential for connecting to Sql Server. SqlDataReader is a read-only forward only recordset. In the SqlCommand's constructor we are passing the SqlCommand. Inorder to create an SqlDataReader the ExecuteReader method of SqlCommand have to be used.

SqlDataReader Read() Method
After creating the SqlDataReader we use the Read method for iterating throug the SqlDataReader and filling the ListBox.

using System.Data.SqlClient;

using System.Data.SqlClient;

SqlConnection cnn; 
SqlCommand cmd; SqlDataReader dr; 
private void Form1_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());
    }
}




VB .Net

Imports System.Data.SqlClient Dim cnn As New SqlConnection Dim cmd As SqlCommand Dim dr As SqlDataReader Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn = New SqlConnection("integrated security=sspi;initial catalog=Northwind; data source=.\sqlExpress") cnn.Open() cmd = New SqlCommand("select* from categories", cnn) dr = cmd.ExecuteReader() While (dr.Read()) lstCategories.Items.Add(dr("categoryName")) End While End Sub

Inserting Records from Front End For inserting records use the ExecuteNonQuery() method of SqlCommand class. using System.Data.SqlClient; SqlConnection cnn; SqlCommand cmd; private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin"); cnn.Open(); } private void btnInsert_Click(object sender, EventArgs e) { cmd = new SqlCommand("insert into Categories values ('Condiments')", cnn); cmd.ExecuteNonQuery(); MessageBox.Show("Record saved"); } VB .Net

Imports System.Data.SqlClient

Dim cnn as SqlConnection Dim cmd as SqlCommand

Private Sub Form_Load(.. cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin") cnn.Open() End Sub

Private Sub btnInsert_Click(sender as object, e as EventArgs) .. cmd = new SqlCommand("insert into Categories values ('Condiments')", cnn)

cmd.ExecuteNonQuery() MessageBox.Show("Record saved") End Sub Connecting to Sql Server 2005 cnn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=sspi;Initial Catalog=Shalvin");