.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"); <!-- #InsertTextBox Inserting values of TextBox The previous example cannot be used in real time since on clicking the button the same data will be inserted to the table. In this example we are constructing an Sql statement by concatenating the contents of TexBox as shown in the highlighted code. After inserting the values we blanking the control and bringing the focus back to the control. 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) { string strSql = "insert into Categories values ('" + txtCategoryName.Text + "')"; cmd = new SqlCommand(strSql, cnn); cmd.ExecuteNonQuery(); MessageBox.Show("Record saved"); BlankControls(); } private void BlankControls() { txtCategoryName.Text = ""; txtCategoryName.Focus(); } DataSet DataGrid DataSet is an offline disconnected data store.SqlDataAdapter is an intemediary between the RDBMS and DataSet. The methods of SqlDataAdapter are Fill and Update. Fill Method is used for filling a DataSet. Update Method is used synchronizing the RDBMS with changes in DataSet. using System.Data.SqlClient; SqlConnection cnn; SqlDataAdapter da; DataSet ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin"); da = new SqlDataAdapter("select * from Categories", cnn); da.Fill(ds, "Shalvin"); dataGridView1.DataSource = ds.Tables["Shalvin"]; }

VB .Net

Imports System.Data Imports System.Data.SqlClient Public Class frmGrid Dim cnn As SqlConnection Dim ds As New DataSet Dim da As SqlDataAdapter Private Sub frmGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind") cnn.Open() da = New SqlDataAdapter("select * from Categories", cnn) da.Fill(ds, "Cat") DataGridView1.DataSource = ds.Tables("Cat") End Sub End Class

DataSet with Multiple DataTables A DataSet is a collection of DataTables along with other objects just like Database is a collection of Tables (views, stored procedures, etc.). This example requires DataGrid insted of GridView Control.

using System.Data.SqlClient; SqlConnection cnn; SqlDataAdapter da; DataSet ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated security=sspi;Initial Catalog=Northwind"); da = new SqlDataAdapter("select CategoryId, CategoryName, Description from categories", cnn); da.Fill(ds, "Cat"); da = new SqlDataAdapter("select ProductId, ProductName, UnitPrice from Products", cnn); da.Fill(ds, "Prod"); dataGrid1.DataSource = ds; }

VB .Net

Imports System.Data Imports System.Data.SqlClient Public Class frmGrid Dim cnn As SqlConnection Dim ds As New DataSet Dim da As SqlDataAdapter Private Sub frmGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind") cnn.Open() da = New SqlDataAdapter("select CategoryId, CategoryName, Description from Categories", cnn) da.Fill(ds, "Cat") da = New SqlDataAdapter("select ProductId, ProductName, UnitPrice from Products", cnn) da.Fill(ds, "Prod") DataGrid1.DataSource = ds End Sub

DataTable DataColumn and DataRow Visit http://shalvinpd.blogspot.com/2008/03/datatable-datacolumn-and-datarow-aspnet.html for Asp.net code of DataTable DataColum and DataRow. DataSet is an offline disconnected Data Store. DataSet is a collection of DataTables. A DataTable Contains DataRow and DataColumns. The following code illustrating creating DataTable, DataRow and DataColumn. This approached can be used for creating a temporary storage of structured data. DataTable dt = new DataTable("Purchase"); DataColumn dc; DataRow dr; private void Form1_Load(object sender, EventArgs e) { dc = new DataColumn("ProductName"); //Adding the column to the Columns colluction of DataTable dt.Columns.Add(dc); dc = new DataColumn("Price"); //Adding the column to the Columns colluction of DataTable dt.Columns.Add(dc); //Adding a new row to the table dr = dt.NewRow(); dr["ProductName"] = "Microsoft Mouse"; dr["Price"] = 400; dt.Rows.Add(dr); dgPurchase.DataSource = dt; } Creating AutoIncrement Colum for DatTable Just like Sql Server is having an identity column, we can have an auto increment column for out datatable. Set the AutoIncrement property of column to true you can optionally set the AutoIncrementSeed and AutoIncrementStep. DataTable dt = new DataTable("Purchase"); DataColumn dc; DataRow dr; private void Form1_Load(object sender, EventArgs e) { dc = new DataColumn(("Product ID"), System.Type.GetType("System.Int32")); 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); dr = dt.NewRow(); dr["ProductName"] = "Shalvin's C# Tutorial Video"; dr["Price"] = 4000; dt.Rows.Add(dr); dgPurchase.DataSource = dt; }

DataSet and ComboBox's DisplayMember Property

Combox's DisplayMember property is used to specify the column to display in a ComboBox.

using System.Data.SqlClient; SqlConnection cnn; SqlDataAdapter da; DataSet ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind"); da = new SqlDataAdapter("select CategoryId, CategoryName, Description from Categories", cnn); da.Fill(ds, "Cat"); cboCategories.DataSource = ds.Tables["Cat"]; cboCategories.DisplayMember = "CategoryName"; }

Login Module in Windows Forms Table and Stored Procedure create table UserTable (UserId int identity(1,1) primary key, RoleId int, UserName varchar(20), Password varchar(20)) create procedure spusertable(@pMode char(1), @pUserId int = null, @pRoleId int = null, @pUserName varchar(20) = null,@ppassword varchar(20) = null) as if @pMode = 'S' select * from Usertable else if @pMode = 'I' insert into usertable values(@pRoleid,@pUsername,@ppassword) else if @pmode='D'delete from usertable where userid=@puserid go Windows Forms SqlConnection cnn; SqlCommand cmd; SqlDataReader dr; int count; private void btnLogin_Click(object sender, EventArgs e) { cmd = new SqlCommand("select count(*) from usertable where UserName = '" + txtUserName.Text + "'and password = '" + txtPassword.Text + "'", cnn); count = Int32.Parse(cmd.ExecuteScalar().ToString()); if (count != 0) { frmMain fm = new frmMain(); fm.Show(); this.Hide(); } else MessageBox.Show("Invalid User"); } }