Accessing Relational Data Using Microsoft Visual Studio .NET

1. Accessing Relational Data Using Microsoft Visual Studio .NET

2. Overview • Overview of ADO.NET • Creating a Connection to a Database • Displaying a DataSet in a List-Bound Control

3. What is ADO.NET? 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

4. 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.Data Imports System.Data.SqlClient using System.Data; using System.Data.SqlClient;

5. The ADO.NET Object Model DataSet DataTable DataTable SqlDataAdapter OleDbDataAdapter SQL Server .NET Data Provider OLE DB .NET Data Provider OleDbConnection SqlConnection SQL Server 7.0 (and later) OLEDB sources(SQL Server 6.5)

6. What is a Dataset? DataSet DataTable DataTable DataTable SqlDataAdapter Client/Web server memory Physical storage SqlConnection OleDbDataAdapter OleDbConnection SQL Server 2000 OleDb Database

7. DataSet Accessing Data with ADO.NET Database 1 Client makes request Create the SqlConnection and SqlDataAdapter objects 2 Fill the DataSet from the DataAdapter and close the connection SqlConnection 3 Web server Return the DataSet to the Client 4 SqlDataAdapter Client manipulates the data 5 Update the DataSet 6 Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection 7 List-Bound Control Client

8. Using Server Explorer to Generate a Connection • Create a new data connection using the Data Links dialog box • Create a new data connection by dragging a Table from Server Explorer

9. The DataAdapter Object Model DataSet DataAdapter SelectCommand UpdateCommand InsertCommand DeleteCommand DataReader Command Command Command Command Connection sp_SELECT sp_UPDATE sp_INSERT sp_DELETE Database

10. Creating a DataAdapter • Store the query in a DataAdapter • The DataAdapter constructor sets the SelectCommand property • Set the InsertCommand, UpdateCommand, and DeleteCommand properties if needed Dim da As 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;

11. Demonstration: Connecting to a Database • Expand Server Explorer to a table in a SQL Server database • Drag and Drop Data Access

12. 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() DataSet ds = new DataSet(); DataAdapter1.Fill(ds) DataAdapter2.Fill(ds) DataAdapter1.Fill(ds); DataAdapter2.Fill(ds);

13. Storing Multiple Tables • Add the first table • Add the subsequent table(s) daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1) daCustomers.Fill(ds, "Customers") daOrders = New SqlDataAdapter _ ("select * from Orders", conn2) daOrders.Fill(ds, "Orders") Customers conn1 conn2 DataSet Orders

14. Demonstration: Generating a DataSet • Create a typed DataSet from a DataAdapter • Add a second DataTable from a different DataAdapter • Show the schema of DataSet

15. 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

16. Property Description DataSource • The DataSet containing the data DataMember • The DataTable in the DataSet DataTextField • The field in the DataTable that is displayed DataValueField • The field in the DataTable that becomes the value of the selected item in the list Displaying DataSet Data in List-Bound Controls • Set the properties • Fill the DataSet, then call the DataBind method DataAdapter1.Fill(ds) lstEmployees.DataBind() DataAdapter1.Fill(ds); lstEmployees.DataBind();

17. 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

18. 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:datagridid="dgrEmployees"runat="server"/> <scriptLanguage="c#"runat="server">

19. 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");

20. // 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>

21. OleDB Example <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <scriptLanguage="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"]);

22. dgNameList.DataSource=objDataView; dgNameList.DataBind(); } </script> <html> <body> <h4>Reading data from the connection <asp:labelid="data_src"runat="server"/> to the DataGrid control.</h4> <asp:datagridid="dgNameList"runat="server"/><br/> </body> </html>

23. Editing Data <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <scriptLanguage="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;";

24. 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);

25. // 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();

26. // ----------------------------------------------------------------- // 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> <tablewidth="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> <tdvalign="top"><asp:DataGridid="dgNameList1"runat="server"/></td> <tdvalign="top"><asp:DataGridid="dgNameList2"runat="server"/></td> <tdvalign="top"><asp:DataGridid="dgNameList3"runat="server"/></td> <tdvalign="top"><asp:DataGridid="dgNameList4"runat="server"/></td> </tr> </table> </body> </html>

27. Command Object <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <scriptLanguage="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;

28. // 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);

29. // 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>

30. <html> <body> <tableborder="1"> <tr> <td>Command</td> <td>CommandText</td> </tr> <tr> <td>SelectCommand</td> <td><asp:Labelid="lblSelectCommand"runat="server"/> </tr> <tr> <td>UpdateCommand</td> <td><asp:Labelid="lblUpdateCommand"runat="server"/> </tr> <tr> <td>InsertCommand </td> <td><asp:Labelid="lblInsertCommand"runat="server"/> </tr> <tr> <td>DeleteCommand</td> <td><asp:Labelid="lblDeleteCommand"runat="server"/> </tr> </table> </body> </html>