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.
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.
// 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
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Language=C# Debug="True" %>
<html>
<head>
<script runat="server">
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();
}
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<body>
Records deleted!
