accessing relational data in adonet
DESCRIPTION
ADO.NET provides a set of classes, interfaces, structures, and enumerations for working with data that manage data access from within the .NET Framework.TRANSCRIPT
Accessing Relational Data Using Microsoft Visual Studio .NET
Overview
Overview of ADO.NET
Creating a Connection to a Database
Displaying a DataSet in a List-Bound Control
ADO.NET provides a set of classes for working with data. ADO.NET provides:
An evolutionary, more flexible successor to ADO
A system designed for disconnected environments
A programming model with advanced XML support
A set of classes, interfaces, structures, and enumerations that manage data access from within the .NET Framework
What is ADO.NET?
Using Namespaces
Use the Imports or using statement to import namespaces
Namespaces used with ADO.NET include:
System.Data
System.Data.SqlClient
System.Data.OleDb
Imports System.DataImports System.Data.SqlClientImports System.DataImports System.Data.SqlClient
using System.Data;using System.Data.SqlClient;using System.Data;using System.Data.SqlClient;
DataSet
SQL Server .NET Data Provider
OLE DB .NET Data Provider
SQL Server 7.0(and later)
OLEDB sources(SQL Server 6.5)
OleDbConnectionOleDbConnection
OleDbDataAdapterOleDbDataAdapterSqlDataAdapterSqlDataAdapter
SqlConnectionSqlConnection
DataTable
DataTable
The ADO.NET Object Model
SQL Server 2000
DataSet
DataTable
DataTable
Physical storage
OleDb Database
SqlDataAdapterSqlDataAdapter
SqlConnectionSqlConnection
DataTable
Client/Web server memory
OleDbDataAdapterOleDbDataAdapter
OleDbConnection
OleDbConnection
What is a Dataset?
Accessing Data with ADO.NET
DatabaseDatabase
4. Return the DataSet to the Client
5. Client manipulates the data
2. Create the SqlConnection and SqlDataAdapter objects
3. Fill the DataSet from the DataAdapter and close the connection
SqlDataAdapter
SqlConnection
List-Bound
Control
List-Bound
Control
1. Client makes request1111
2222
3333
4444
5555
6. Update the DataSet
7. Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection
6666
7777
ClientClient
Web serverWeb server
DataSet
Using Server Explorer to Generate a Connection
Create a new data connection by dragging a Table from Server Explorer
Create a new data connection using the Data Links dialog box
The DataAdapter Object Model
sp_SELECT
CommandCommand
SelectCommand UpdateCommand InsertCommand DeleteCommand
DataAdapter
CommandCommand CommandCommand CommandCommand
ConnectionConnection
sp_UPDATE sp_INSERT sp_DELETE
Database
DataSetDataSet
DataReaderDataReader
Store the query in a DataAdapter
The DataAdapter constructor sets the SelectCommand property
Set the InsertCommand, UpdateCommand, and DeleteCommand properties if needed
Creating a DataAdapter
Dim da As New SqlDataAdapter _("select * from Authors", conn)
Dim da As New SqlDataAdapter _("select * from Authors", conn)
da.SelectCommand.CommandText da.SelectCommand.Connection
da.SelectCommand.CommandText da.SelectCommand.Connection
SqlDataAdapter da = new SqlDataAdapter("select * from Authors",conn);
SqlDataAdapter da = new SqlDataAdapter("select * from Authors",conn);
da.SelectCommand.CommandText;da.SelectCommand.Connection;
da.SelectCommand.CommandText;da.SelectCommand.Connection;
Demonstration: Connecting to a Database
Expand Server Explorer to a table in a SQL Server database
Drag and Drop Data Access
Generating a DataSet
You can generate a DataSet…
…through the UI…Creates a DataSet that allows you to access data as an object
…or through code…
and then fill…
Dim ds As New DataSet()Dim ds As New DataSet()
DataAdapter1.Fill(ds)DataAdapter2.Fill(ds)DataAdapter1.Fill(ds)DataAdapter2.Fill(ds)
DataSet ds = new DataSet();DataSet ds = new DataSet();
DataAdapter1.Fill(ds);DataAdapter2.Fill(ds);DataAdapter1.Fill(ds);DataAdapter2.Fill(ds);
Storing Multiple Tables
Add the first table
Add the subsequent table(s)
daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1)daCustomers.Fill(ds, "Customers")
daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1)daCustomers.Fill(ds, "Customers")
Orders
Customers
daOrders = New SqlDataAdapter _ ("select * from Orders", conn2)daOrders.Fill(ds, "Orders")
daOrders = New SqlDataAdapter _ ("select * from Orders", conn2)daOrders.Fill(ds, "Orders")
conn2conn1
DataSet
Demonstration: Generating a DataSet
Create a typed DataSet from a DataAdapter
Add a second DataTable from a different DataAdapter
Show the schema of DataSet
What are List-Bound Controls?
Controls that connect to a data source and display the data
List-bound controls include the following:
DropDownList
ListBox
CheckBoxList
RadioButtonList
DataGrid
DataList
Repeater
Displaying DataSet Data in List-Bound Controls
Set the properties
Fill the DataSet, then call the DataBind methodDataAdapter1.Fill(ds)lstEmployees.DataBind()DataAdapter1.Fill(ds)lstEmployees.DataBind()
PropertyPropertyPropertyProperty DescriptionDescriptionDescriptionDescription
DataSourceDataSource The DataSet containing the data The DataSet containing the data
DataMemberDataMember The DataTable in the DataSet The DataTable in the DataSet
DataTextFieldDataTextField The field in the DataTable that is displayed The field in the DataTable that is displayed
DataValueFieldDataValueField The field in the DataTable that becomes the value of the selected item in the list
The field in the DataTable that becomes the value of the selected item in the list
DataAdapter1.Fill(ds);lstEmployees.DataBind();DataAdapter1.Fill(ds);lstEmployees.DataBind();
Demonstration: Binding List-Bound Controls to a Database
Add a DataGrid to a Windows Form
Set the DataSource and DataMember properties
Fill the DataSet
Web Forms DataGrid
An SQL example
<%@Page Language="c#"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title> ASP.NET - Data - Data Table <br/>
Using Microsoft SQL Objects</title>
</head>
<body>
<h2>Display of Data in a Table (Grid) Using SQL Objects</h2>
Northwind Employees:<hr/>
<asp:datagrid id="dgrEmployees" runat="server" />
<script Language="c#" runat="server">
void Page_Load()
{
// First we will set up variables to hold two strings
string strSQL = "SELECT FirstName,LastName FROM Employees;";
string strConnection = "server=EWANB;";
strConnection += "database=Northwind;uid=sa;password=;";
DataSet objDataSet = new DataSet();
SqlConnection objConnection = new SqlConnection(strConnection);
// Create a new DataAdapter using the connection object and select statement
SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSQL, objConnection);
// Fill the dataset with data from the DataAdapter object
objDataAdapter.Fill(objDataSet, "Employees");
// Create a DataView object for the Employees table in the DataSet
DataView objDataView = new DataView(objDataSet.Tables["Employees"]);
// Assign the DataView object to the DataGrid control
dgrEmployees.DataSource = objDataView;
dgrEmployees.DataBind(); // and bind [display] the data;
}
</script>
</body>
</html>
OleDB Example
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
void Page_Load()
{
string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\BegASPNET\ch12\Northwind.mdb";
data_src.Text = strConnection;
string strSQL = "SELECT FirstName, LastName FROM Employees";
DataSet objDataSet = new DataSet();
OleDbConnection objConnection = new OleDbConnection(strConnection);
OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, objConnection);
objAdapter.Fill(objDataSet, "Employees");
DataView objDataView = new DataView(objDataSet.Tables["Employees"]);
dgNameList.DataSource=objDataView;
dgNameList.DataBind();
}
</script>
<html>
<body>
<h4>Reading data from the connection
<asp:label id="data_src" runat="server"/> to the DataGrid control.</h4>
<asp:datagrid id="dgNameList" runat="server" /><br />
</body>
</html>
Editing Data
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
void Page_Load(object sender, EventArgs e)
{
string strConnection, strSQL;
DataSet objDataSet = new DataSet();
OleDbConnection objConnection = null;
OleDbDataAdapter objAdapter = null;
OleDbCommandBuilder objBuilder = null;
// Set the connection and query details
strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\BegASPNET\ch13\Northwind.mdb";
strSQL = "SELECT FirstName, LastName FROM Employees;";
objConnection = new OleDbConnection(strConnection);
objAdapter = new OleDbDataAdapter(strSQL, objConnection);
objAdapter.Fill(objDataSet, "Employees");
dgNameList1.DataSource = objDataSet.Tables["Employees"].DefaultView;
dgNameList1.DataBind();
// -----------------------------------------------------------------
// Marker 1
DataTable objTable = objDataSet.Tables["Employees"];
DataRow objNewRow = objTable.NewRow();
objNewRow["FirstName"] = "Norman";
objNewRow["LastName"] = "Blake";
objTable.Rows.Add(objNewRow);
// Bind the data grid to the new data
dgNameList2.DataSource = objTable.DefaultView;
dgNameList2.DataBind();
// -----------------------------------------------------------------
// Marker 2
// Find the row to change
DataRow[] objRows = objTable.Select("FirstName='Margaret' AND LastName='Peacock'");
objRows[0]["FirstName"] = "John";
objRows[0]["LastName"] = "Hartford";
// Bind the data grid to the new data
dgNameList3.DataSource = objTable.DefaultView;
dgNameList3.DataBind();
// -----------------------------------------------------------------
// Marker 3
// The Rows collection is 0 indexed, so this removes the sixth row
objTable.Rows[5].Delete();
// Bind the data grid to the new data
dgNameList4.DataSource = objTable.DefaultView;
dgNameList4.DataBind();
}
</script>
<html>
<body>
<table width="100%">
<tr>
<td>Original Data</td>
<td>Data with new Row</td>
<td>Data with edited Row</td>
<td>Data with deleted Row</td>
</tr>
<tr>
<td valign="top"><asp:DataGrid id="dgNameList1" runat="server" /></td>
<td valign="top"><asp:DataGrid id="dgNameList2" runat="server" /></td>
<td valign="top"><asp:DataGrid id="dgNameList3" runat="server" /></td>
<td valign="top"><asp:DataGrid id="dgNameList4" runat="server" /></td>
</tr>
</table>
</body>
</html>
Command Object
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
void Page_Load(object sender, EventArgs e)
{
string strConnection, strSQL;
DataSet objDataSet = new DataSet();
OleDbConnection objConnection = null;
OleDbDataAdapter objAdapter = null;
OleDbCommand objCommand = null;
OleDbCommandBuilder objBuilder = null;
// Set the connection and query details
strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\BegASPNET\ch13\Northwind.mdb";
strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees";
// Open the connection and set the command
objConnection = new OleDbConnection(strConnection);
objAdapter = new OleDbDataAdapter(strSQL, objConnection);
// Create the other commands
objBuilder = new OleDbCommandBuilder(objAdapter);
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand();
objAdapter.InsertCommand = objBuilder.GetInsertCommand();
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand();
// Now display the CommandText property from each command
lblSelectCommand.Text = objAdapter.SelectCommand.CommandText;
lblUpdateCommand.Text = objAdapter.UpdateCommand.CommandText;
lblInsertCommand.Text = objAdapter.InsertCommand.CommandText;
lblDeleteCommand.Text = objAdapter.DeleteCommand.CommandText;
}
</script>
<html>
<body>
<table border="1">
<tr>
<td>Command</td>
<td>CommandText</td>
</tr>
<tr>
<td>SelectCommand</td>
<td><asp:Label id="lblSelectCommand" runat="server" />
</tr>
<tr>
<td>UpdateCommand</td>
<td><asp:Label id="lblUpdateCommand" runat="server" />
</tr>
<tr>
<td>InsertCommand </td>
<td><asp:Label id="lblInsertCommand" runat="server" />
</tr>
<tr>
<td>DeleteCommand</td>
<td><asp:Label id="lblDeleteCommand" runat="server" />
</tr>
</table>
</body>
</html>