Thursday, May 15, 2008

.Net Windows Forms Database Connectivity

Ado .Net is the data access technology of .Net.

Inserting Records from Front End

Inserting values of TextBox

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;







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");