Introduction of ADO.NET with simple example

No.of Views1736
Bookmarked0 times
Downloads 
Votes0
By  usamawahabkhan   On  16 Jun 2010 11:06:28
Tag : ADO.NET , General
ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

ADO.NET provides consistent access to data sources such as Microsoft SQL Server, as well as data sources exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data.

Image Loading

ADO.NET cleanly factors data access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet object in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers. The ADO.NET DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.

The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll.

Sample Screen 

Image Loading

Implementation

// Add Namespace of sqlClient using System.Data.SqlClient; 

private static string strConnection ="user id=sa;password=openthegate;database=northwind;server=DevServer"; 


public OrderGrid() 
{ 
Page.Init += new System.EventHandler(Page_Init); 
} 

// Connect to the Database private SqlConnection GetConnection() 
{ 
SqlConnection objConnection = new SqlConnection(strConnection); 
return objConnection; 
} 
// this method will populate the DataGrid OrdGrid private void PopulateGrid() 
{ 
// Connect to the Database SqlConnection objConnection=GetConnection(); 
objConnection.Open(); 
DataSet objDataSet = new DataSet("OD-Prod"); 
SqlDataAdapter objDataAdapter = new SqlDataAdapter(); 
// copy the table Order Details in to the DataSet string strCmd="SELECT OD.ProductID,OD.Quantity,P.ProductName FROM [Order Details] OD, "; 
strCmd+=" Products P WHERE OD.ProductID=P.ProductID AND "; 
if(OrderID.Text.Length>0) 
{ 
strCmd+="OD.OrderID = "+ OrderID.Text; 
} 
else 
{ 
objConnection.Close(); 
return; 
} 
SqlCommand selCommand = new SqlCommand(strCmd,objConnection); 
objDataAdapter.SelectCommand=selCommand ; 
objDataAdapter.Fill(objDataSet,"JoinTable"); 
OrdGrid.DataSource =objDataSet.Tables["JoinTable"]; 
OrdGrid.DataBind(); 

} 
private void InitializeComponent() 
{ 
this.OrdGrid.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.CancelGrid); 
this.OrdGrid.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.EditGrid); 
this.OrdGrid.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.UpdateGrid); 

} 

protected void SerchClicK(object sender, System.EventArgs e) 
{ 
this.PopulateGrid(); 
} 


System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
OrdGrid.EditItemIndex= e.Item.ItemIndex; 
PopulateGrid(); 
// prevent the TextBox from changeing the OrderID OrderID.Enabled=false; 

} 

// Eventhandler for the event Cancel the Edit(DataGrid) private void CancelGrid(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
OrdGrid.EditItemIndex=-1; 
PopulateGrid(); 
// able the TextBox to change the OrderID OrderID.Enabled=true; 

} 


private int DirectCommand(string strCmdText) 
{ 
SqlConnection objConnection = GetConnection(); 
SqlCommand objCommand = new SqlCommand(strCmdText,objConnection); 
objCommand.Connection.Open(); 
int nAffected = objCommand.ExecuteNonQuery(); 
objConnection.Close(); 

return nAffected; 

} 

// Eventhandler for the event Updating the DataGrid private void UpdateGrid(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
string strQuantity= ((TextBox)e.Item.Cells[3].Controls[0]).Text; 
string strProductID = e.Item.Cells[1].Text; 
string strUpdate ="UPDATE [Order Details] SET Quantity = "+strQuantity; 
strUpdate+=" WHERE OrderID = "+ OrderID.Text; 
strUpdate += " AND ProductID = "+strProductID; 
// Update the table "Order Details" int nCheck = DirectCommand(strUpdate); 

OrdGrid.EditItemIndex=-1; 
PopulateGrid(); 
// able the TextBox to change the OrderID OrderID.Enabled=true; 
} 
} 
} 

//One more option to delete the row public void Page_Load() 
{ 
OleDbConnection conStudent; 
OleDbCommand cmdAuthDel; 
string myConnString; 
string strDelete; 

myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\\inetpub\\wwwroot\\dataaccess\\Northwind.mdb;"; 
conStudent = new OleDbConnection( myConnString ); 

strDelete = "DELETE * FROM Student WHERE au_fname=’Usama’"; 
cmdAuthDel = new OleDbCommand( strDelete, conStudent ); 

conStudent.Open(); 
cmdAuthDel.ExecuteNonQuery(); 
conStudent.Close(); 
}

That's all,in this article just introduction of the ADO.NET and how we could implement the CRUD in ASP.NET.

 
Sign Up to vote for this article
 
About Author
 
usamawahabkhan
Occupation-Not Provided
Company-Not Provided
Member Type-Senior
Location-Pakistan
Joined date-06 May 2010
Home Page-Not Provided
Blog Page-Not Provided
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</