1	Programming SQL Server 2000 with Visual Basic .NET 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 – last tour (www.programmingmsaccess.com/seminar2001) Recent books by the author
3	What’s the Chat About? Main topic is Programming SQL Server 2000 with Visual Basic .NET Scope of issues follows from my upcoming book First part of book dwells on topics specific to SQL Server 2000, such as data access via T-SQL, SQL Server security, web access to SQL Server databases via virtual directories Second part of book focuses on Visual Basic .NET, ADO.NET, ASP.NET, and how to use these technologies with SQL Server 2000
4	What Does a VB Developer Need to Know about SQL Server? How to create your own tables (and not just link, or open, tables created by someone else) How to write T-SQL that are smarter than SELECT * FROM tablename How to create T-SQL statements that you can re-use How to secure your solutions Understand why Microsoft has web releases
5	What Should I Know about Creating Tables? Learn the SQL Server data types to Save storage and make solutions run faster Simplify running applications in multiple countries Learn about keys and indexes to Maintain data integrity Speed performance Learn about scripting tables to Document table designs in an easy-to-read format Facilitate reproducing table designs across databases and servers
6	What Should I Know about T-SQL Data Access Syntax? Use the SELECT list argument and WHERE clause to speed the availability of a result set Learning join syntax for Merging data from two or more tables Specifying a result set that joins multiple columns in a single table (and other special merges) Learn syntax for grouping rows and aggregating column values
7	What Should I Know about Re-usable Database Objects? Views are T-SQL statements that act as virtual tables Stored procedures can return result sets and enable data manipulation tasks (inserts, updates, and deletes) plus more User-defined functions are new; they can return a scalar (single number) A table based on a single T-SQL statement A table based on multiple T-SQL statements
8	What Should I Know About Web Releases? They are fully supported extensions to SQL Server 2000 that target XML capabilities Lets users return data from SQL Server databases over the web as XML documents from an IIS virtual directory Run data access, data manipulation, and data definition tasks via URL access Templates in the IIS virtual directory
9	What’s the Most Recent Web Release? Web Release 3 shipped during February, 2002 in 2 versions Feb 9 with Web Services Toolkit; lets you expose stored procedures, user-defined functions, and templates as Web services Feb 14 as a stand-alone version; minor upgrades and bug fixes to Web Release 2 Prior Web Release Dates Web Release 1 shipped on Feb 2001 Web Release 2 shipped on Oct 2001
10	What Should I Know about SQL Server Security? How to create and remove login and user security accounts How to control login permissions by assigning them to fixed server roles How to control user permissions by assigning them to fixed database roles How to create custom roles Assign users to the roles
11	How Do I use Visual Basic .NET with SQL Server? Create solutions for the desktop over a LAN with Windows Applications Create solutions for browsers over a web with ASP.NET Use ADO.NET data providers to tap SQL Server from Visual Basic .NET Use SQLXML managed classes for optimized SQL Server functionality Use Web Services to share XML data in XML format for consumption by machines
12	What’s New About Visual Basic .NET? There’s a new Windows Form class that’s distinct from forms in previous Visual Basic versions Classes are much more important than in prior Visual Basic versions Everything – even data types – are classes Inheritance lets one class inherit the properties, methods, and events of another class Structured Exception Handling is a new way of trapping and processing run-time errors
13	What Should I Know About ADO.NET? ADO.NET is not a simple outgrowth of ADO; it is brand new! ADO.NET supports forward-only, read-only data access However, its main scalability advantages come from disconnected data access Some built-in data-binding Also, unbound forms are more relevant than ever Affects how you manage data manipulation (you must use optimistic locking)
14	Other Major ADO.NET Innovations 3 data providers for 3 types of data SQL Server .NET data provider (SQL Server) OLE DB .NET data provider (Access, Oracle) ODBC .NET data provider (other ODBC) Use SqlConnection, SqlCommand, and SqlDataReader for forward-only, read-only access Use SqlConnection, SqlCommand, SqlDataAdapter, Dataset for disconnected data access
15	What Are SQLXML Managed Classes? Ship with Web releases 2 and 3; use web release 3 if you do not have backwards compatibility issues Use SQLXML Managed classes as a replacement for SQL Server .NET data provider objects Main objects are SqlXmlCommand (interacts with SqlXmlParameter objects) SqlXmlDataAdapter No explicit SqlXmlConnection object
16	What Should I Know about ASP.NET? Requires IIS and MDAC 2.6+ on the web server, but not the clients Runs side-by-side with older ASP apps ASP.NET pages (with an .aspx) extension are compiled for faster performance ASP.NET lets you build web solutions in Visual Basic – not VBScript ASP.NET keeps VB separate from HTML
17	More About ASP.NET Design with page round-trip in mind Page_Load event and IsPostBack critical for managing actions for general page startup and first-time page load Manage session state with Session variables; specially updated for web farms ViewState variables now complement query strings and hidden fields for managing session state from client Place ADO.NET objects on web page to enable Data access Data manipulation
18	ADO.NET, VB.NET, and XML Datasets can represent data as an XML document Query data sources with VB.NET and T-SQL with FOR XML clause XPath syntax against an annotated schema Invoke ExecuteToStream method for SqlXmlCommand object to send XML document to a file Use XSLT to transform XML formatted data to HTML tables ADO.NET transparently applies DiffGrams, an XML document, to facilitate data manipulation NB: An XML document is a text file with tags; everything you know about text files still works
19	Elements of a Web Services Solution with ASP.NET Create the web service app Use the built-in testing facility to verify operation of your web service Create a client app with a proxy that points at the web service app Within the client, invoke web methods and pass arguments to the web service Process returned values as an XML fragment from the web service to the client
20	Elements of a Web Service with Web Services Toolkit Graphical capabilities for creating a web service based on a stored procedure user-defined function template in IIS virtual directory Data return as XML, but with a different format than with ASP.NET web service Create client for web service, but discover web service slightly differently than with ASP.NET
21	Summary Creating solutions for SQL Server with VB.NET works best for those with a good working knowledge of SQL Server VB.NET is great for creating Windows, ASP.NET, and Web services ADO.NET is scalable and faster than ADO because of basic design features ASP.NET is more scalable and faster than ASP because of design features