1	Developing Windows and Web Solutions with ADO.NET with SQL Server Presented by Rick Dobson, Ph.D. Author/Seminar Tour Leader
2	Your Presenter, Rick Dobson, is An author A Webmaster www.programmingmsaccess.com A seminar tour promoter – current tour (www.programmingmsaccess.com/seminar2002) Recent books by the author
3	What is ADO.NET? It’s the data component of the .NET Framework ADO.NET has Data provider elements for interacting with data Datasets for manipulating data locally and representing data as XML You can use ADO.NET in Windows applications to implement solutions with data access on corporate desktops ASP.NET applications to implement solutions with data access in browsers Other contexts (for example, mobile devices and Web services)
4	ADO.NET Platform Requirements Operating system .NET Framework MDAC Version (www.microsoft.com/data)
5	ADO.NET Platform Requirements Operating system Windows.NET Windows 2000 (Service Pack 2 recommended) Windows NT (Service Pack 6a) Windows XP Windows ME (not hosting ASP.NET or XML Web Services) Windows 98 (not hosting ASP.NET or XML Web Services) .NET Framework MDAC Version (www.microsoft.com/data)
6	ADO.NET Platform Requirements Operating system .NET Framework msdn.microsoft.com/netframework/downloads/howtoget.asp .NET runtime .NET SDK Command line processing of files with code Visual Studio .NET Visual designers for Windows, Web, and other apps Editors with statement completion and IntelliSense Graphical interface for compiling and deploying MDAC Version (www.microsoft.com/data)
7	ADO.NET Platform Requirements Operating system .NET Framework MDAC Version (www.microsoft.com/data) 2.6+ for SQL Server or OLE DB .NET Data Provider 2.7+ for ODBC .NET Data Visual Studio .NET automatically installs (2.7+)
8	ADO.NET, Databases, and XML
9	ADO.NET Data Provider Elements Data Providers “hook up” your ADO.NET application with remote data sources ADO.NET Provider classes help you do common data tasks Connect to a data source Perform data access Perform data manipulation Even perform data definition
10	Overview of ADO.NET Data Providers .NET Data Providers are optimized for different data sources SQL Server .NET Data Provider SQL Server 7 SQL Server 2000 OLE DB .NET Data Provider Earlier versions of SQL Server Access Oracle
11	Other ADO.NET Data Providers ODBC .NET Data Provider Other remote ODBC data sources download from: http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml) .NET Framework Data Provider for Oracle Optimized for Oracle databases Benchmarked at 200% faster than OLE DB data provider for Oracle Supports new Oracle 9i data types, and ref cursors (useful for running Oracle stored procedures that return result sets)
12	Overview of ADO.NET Provider Classes Each .NET data provider has its own ADO.NET provider classes, including four core ones Connection – connect to an external data source Command – work within a connection to execute commands (SELECT, INSERT, UPDATE, DELETE) DataReader – returns a forward-only, read-only stream of data from a connected data source DataAdapter -- populates a dataset and passes dataset updates to a remote data source
13	ADO.NET Database Interactions Forward-only, Read-only Uses Connection and DataReader classes Connection object is unavailable for as long as a DataReader is open Very fast Disconnected Uses Connection and DataAdapter classes as well as Datasets Permits data manipulation More scalable than forward-only, read-only interaction
14	What’s a .NET Windows Application? Designed to run locally on a user’s workstation Can take advantage of local resources, such as files and other local applications, such as Microsoft Office XP Windows applications use Windows Forms and Window Form control classes
15	Creating a Windows Application in Visual Studio .NET From Start Page, choose New Project Select Windows Application Template from the Visual Basic Projects (or Visual C# Projects) folder Enter a project name, such as SearchDatabaseWin Then, choose OK
16	Adding Controls to a Windows Form for Data Browsing Drag a text box from the Windows Form tab of the Toolbox, copy it twice, and align three text boxes one on top of the other Drag and copy labels for the text box controls; size and align labels to match text box controls Drag a button from the Toolbox, copy it three times, and align the four buttons
17	The Layout of Controls on the Form
18	Adding ADO.NET Elements to the Windows Application Open Server Explorer window from the View menu Drag the Shippers table from the Data Connection for the Northwind SQL Server database; this adds to system tray SqlConnection1 SqlDbDataAdapter1 Right-click SqlDbDataAdapter1 and choose Generate Dataset In the dialog, assign SearchDatabaseWinShippers as the new dataset name Click OK to add SearchDatabaseWinShippers1 dataset to the systems tray
19	ADO.NET Objects in Systems Tray
20	Invoke Fill Method for DataAdapter to Populate Dataset Double-click Form1’s title bar to open shell for form’s default event procedure Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load End Sub Enter following expression into the shell; Shippers is the name for the local DataTable object in the dataset SqlDataAdapter1.Fill(SearchDatabaseWinShippers1, _ "Shippers")
21	Populate controls with the Dataset
22	Process for Binding a Control to a TextBox Select TextBox1 Expand DataBindings property collection Highlight Text property Expand Shippers DataTable in SearchDatabaseWinShippers1 dataset Select ShipperID to bind TextBox1 to ShipperID (by clicking it once) Repeat with TextBox2 and TextBox3 to bind CompanyName and Phone column values to each text box in turn
23	To Navigate Rows in a Data Source Use the Position property of the BindingContext object to control the row of column values showing in a set of bound text boxes Set the Position property to Position.MinValue to move to the first row -= 1 to move to the previous row += 1 to move to the next row Position.MaxValue to move to the last row
24	Creating the Event Procedure to Move to the First Row Double-click Button1 to create an event procedure shell for the default event Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click End Sub Enter the following expression within the shell Me.BindingContext(SearchDatabaseWinShippers1, "Shippers").Position = _ Me.BindingContext(SearchDatabaseWinShippers1, _ "Shippers").Position.MinValue
25	Excerpt with Navigation Expressions
26	Try the Windows Application Start application (press F5) Show navigation Edit a record Demonstrate persistence of edit in local DataTable object Close app and restart to confirm changes do not post back to the remote data source Through 10/16/02, sign Guest Book and request sample project folders at http://www.programmingmsaccess.com/mygb.htm
27	What’s a .NET Web Application? ASP.NET project is a Web application .aspx is the file extension for a Web page, which has a Web form by default ASP.NET applications must reside on a Microsoft Internet Information Services Web server; they also have a Visual Studio folder as well However, you can browse Web pages (.aspx) from any browser
28	How Does ASP.NET Compare to ASP? ASP.NET and ASP pages can run side-by-side on the same server ASP.NET pages are compiled so they can run much faster and scale better than ASP pages ASP.NET offers code-behind-page similar to code-behind-form for Windows applications – no more interspersed HTML and script language!!!
29	What’s Special about ADO.NET with ASP.NET? Web forms are necessarily disconnected, but Windows forms are optionally disconnected Perform different task on each round-trip of a page from a browser to a Web server Initially, populate the controls from the database server Subsequent round trips, can browse disconnected data or update/refresh disconnected dataset relative to database server
30	Creating an ASP.NET Application in Visual Studio From Start Page, choose New Project Select ASP.NET Web Application from the Visual Basic Projects (or Visual C# Projects) folder Enter a folder name for the project on a Web server, such as http://localhost/SearchDatabaseWeb Next, choose OK If prompted, confirm your credentials (user name, password, and domain) for editing on Web server The default page is WebForm1.aspx
31	Adding Controls to a Web Form for Data Browsing and Editing Add a text box to the WebForm1.aspx page by copying it from the Web Forms tab on the Toolbox Add two additional text boxes by copying the original text box on the page; then, align all three Add three label controls in a similar fashion to the page Add four buttons in one column; add a fifth button to the right of the column of buttons
32	Populate the Systems Tray with ADO.NET Controls Open the Server Explorer Navigate to the pubs database (it’s one of two sample databases that ship with SQL Server) Expand the Tables folder in the pubs database and drag the authors table icon to the Web page This adds SqlConnection1 and SqlDataAdapter1 objects to the systems tray Next, drag the Dataset object from the Data tab on the Toolbox to the systems tray Select Untyped dataset Click OK This adds Dataset1 to the systems tray
33	After minor editing Webform1.aspx looks like this
34	The Code Window Behind WebForm1.aspx Right-click WebForm1.aspx in the Solution Explorer window and choose View Code This opens the code behind the Web form window (such as, WebForm1.aspx.vb) with an empty Page_Load event procedure shell There are two parts for the Page_Load event procedure Fill Dataset1 with SqlDataAdapter1 and initialize the form to show the first row when the form opens Enable modifying the authors table through the form
35	Code to Fill the Dataset and Initialize WebForm1.aspx
36	Start to enable updating phone column values with an Update statement
37	Finish enabling updating phone column values by adding parameters
38	Use unbound controls and a Session Variable to Control Dataset row to show
39	Set the Session Variable to 0 to move to the first row
40	Set the Session Variable to the row count less 1 to move to the last row
41	Add 1 to the Session Variable to move to the next row
42	Subtract 1 from the Session Variable to move to the previous row
43	Update the database by transferring a form control value to Dataset11 and invoking the Update method for SqlDataAdapter1
44	Try the Web Application Choose Build, Build solution (or Rebuild solution if you are fine-tuning a previous compiled page) Right-click WebForm1.aspx in the Solution Explorer window and choose View in Browser Demo navigation buttons Demo Modify button (close and re-open app to verify server-side update) Through 10/16/02, sign Guest Book and request sample project folders at http://www.programmingmsaccess.com/mygb.htm
45	Conclusions ADO.NET works readily with any (ODBC) database it has optimized drivers for SQL Server and Oracle (use OLE DB .NET and ODBC .NET data providers for other data sources) Visual Studio .NET offers drag-and-drop ADO.NET functionality Simple small blocks of code, such as Visual Basic .NET, can complement graphical techniques for completing solutions