Database connectivity refers to the ability of an application to connect to database system in order to store, retrieve, update or delete data.
ADO.Net is a data access technology of .Net. ADO.Net contains Managed Providers and DataSet.
Managed providers provide libraries to connect to various databases.
Two important Managed Providers are SqlClient and OleDb.
SqlClient namespace contains classes required for interacting with SQL Server.
OleDb is used to connect to any RDBMS say Oracle, Access and even SQL Server.
But it is recommended using SqlClient 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 the connection string we specify the connection options like Database instance, security mode, etc.
Connection string is passed as a constructor to the SqlConnection.
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.SqlClientDim 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");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"); } }
