C# Programming for the Microsoft SQL Server Database

1. C# Programming for the Microsoft SQL Server Database Dave Henson dhenson@certfiednetworks.com

2. Logistics • Class Hours • Notes/Handouts • Demos • Class website

3. Recommended Reading • Beginning C# Databases, APress, ISBN 1-59059-433-9

4. Course Setup • Software required to complete course labs: • Windows xp, 2000 or 2003 • .Net Framework 1.1 • MSDE • Recommended Software: • Visual Studio 2003 • SQL Server Standard Version

5. Course Topics • Quick C# Primer • Tools • Creating/Using Class Libraries • ADO.Net • Exception handling • Stored Procedures • Avoiding SQL Injection • Com Interop • SQL Server Best Practices and Techniques

6. Chapter 1 - Tools • Visual Studio • Command Line Tools • Other Tools

7. Visual Studio • Fully integrated IDE • Intellisense • Automatic Code Generation • Drag/Drop Database and Control Interface • Database and Other Design Wizards

8. Command Line Tools • Subdirectory: C:\WINDOWS\Microsoft.NET\Framework\V1.1.4322 • Csc.exe (vbc.exe, jsc.exe) • Compiler • GACUtil.exe • Global Assembly Cache tool • Ngen.exe • Native windows exe converter • RegAsm.exe • Register a .Net assembly with COM • SN.exe • Strong Name Generator

9. Csc.exe – C# Compiler • Typical Syntax Examples: C:>Csc Simple.cs • Produces Simple.exe in current directory C:>Csc /target:library DBUtil.cs • Produces DBUtil.dll in the current directory C:>Csc /r:DBUtil.dll DBClient.cs • Products DBClient.exe which uses the dll

10. Other Tools • IDE • Codecharge studio • Automated Code Generation: • SQL Queries that dynamically c# • C# that dynamically builds c# • C# that dynamically builds SQL

11. Chapter 2 – C# Review • Program Structure • Case sensitivity • if/while/foreach • try/catch • Creating objects

12. Program Structure using System; class App{ public static void Main() { Console.WriteLine("hey"); Console.ReadLine(); } }

13. Case Sensitivity • Most .Net classes are Mixed Case • Many C# keywords are lower case • Valid Code: … String greeting = “hello”; String Greeting = “goodbye”;

14. if Statement if (loop != 20) { string errorMessage = “Oops”; }

15. while statement while(dr.Read()) { Console.WriteLine(dr[“FirstName”]); }

16. foreach statement DirectoryInfo dir = new DirectoryInfo(@“c:\\temp”); try { foreach(FileInfo f in dir.GetFiles()) { Console.WriteLine(f.Name); } } catch(Exception e) { Console.WriteLine(e.Message); } Finally { }

17. Creating Objects • An object is an instance of a class • Examples: String stuff = “hello”; MyClass class = new MyClass(1); SqlDataSet ds = CreateDataSet(query);

18. Chapter 3 - Class Libraries

19. Definitions • Class library – body of functions and properties usable by other code • Examples: • ActiveX control/COM object • .Net Assembly • Web Service • Win32 dll • Implementation: • Often in a .dll file

20. Purpose • Supply re-usable code to developer • Maintain consistency • Ease of maintenance – Separation of front end from back end • Reduce development costs

21. Methods Class biz{ //Static – member of the type static public string SayHi(){ return “hi”; } //Non static – member of the instance public string SayBye(){ string message = _message; return message; } }

22. Properties • Properties are methods! • Get • Set • “value” keyword has special meaning

23. Application Layers • Presentation • Business Logic • Data Access • Potential ease of use and potential disaster

24. Chapter 4 – ADO.Net

25. ADO.NET Components • SqlConnection • SqlDataAdapter • DataSet • DataTable • DataRow, DataColumn collections • SqlDataReader • SqlCommand

26. Required Namespaces • System.Data • System.Data.SqlClient

27. Providers • Providers Available: • SQL Server .NET Provider • OleDB .NET Provider • Example-AllRecordsBasicOleDB.aspx • ODBC .NET Provider • SQL XML .NET Provider

28. Connections • Connection Defined • Where to Store the Connection String • Connection Syntax Examples • Connection Pooling • Security • Close Your Connections! • Monitoring Connections

29. Where to Store the Connection String • Options Available: • Configuration Class • Front End App (.aspx file) • Web.Config • UDL File (OleDB Only) • Registry • Custom File • COM+ Catalog Using Connection Strings • Evaluation Terms: Security, Convenience, Performance

30. Two Connection String Syntax Examples • In the .aspx file: ConnString = “server=10.0.0.1;UID=sa;PWD=;” Dim Conn As New SqlConnection(ConnString) • In Web.Config XML file: <configuration> <appSettings> <add key=“ConnString” value=“server=10.0.0.1;UID=sa;PWD=;”/> </appSettings> </configuration>

31. Connection Pooling • Defined • Controlling Min/Max-Example6ShowConnectionStatePoolControl.aspx • Importance of “Exact String Match” • Pooling for SqlClient vs. OleDBClient • Effects of pooling on SQL security • Close Your Connections!

32. Performance Issues • Choose Providers Wisely • DataReader vs. DataAdapter • Repeater Control vs. DataGrid Control • Connection Pooling • Embedding SQL vs. Stored Procedures • Controlling The HTML • Typed Accessor Methods-Example7AdapterVsReaderUsingTypedAccessorMethods.asp

33. DataReader Vs. DataAdapter • DataReader • Forward Only • Only One Record At A Time In Memory • “Firehose” Functionality • Typed Accessor Methods Avoid Conversions • One datareader open per connection • DataAdapter • More Overhead • More Flexible

34. Repeater Control vs. DataGrid(or DataList) Control • Repeat Control Simply Repeats • Low overhead • You Have To Do Everything • You Can Do It Better Than Microsoft Did! • DataGrid • Default HTML Behaviour • Higher Overhead, Most Functionality

35. Embedding SQL vs. Stored Procedures • Stored Proc Advantages: • Procedure Cache • Separate Security Model • Potentially Less Network Traffic • Output Params, Error Code & Result Set • Can Do Anything Server Side • Abstracts the Front End from Changes – Possible Disadvantage with Xcopy Deployment

36. Controlling the Presentation • HTML-Use Stylesheets if Possible! • Make Sure The Page Fails Gracefully If Needed • With DataGrids, Use TemplateColumns

37. Final Recommendations • Use DataGrids Only When Updates Are Needed • Embed Connection In Web.Config through Config class • Only “Select” What You Need • Call StoredProcs For Ultimate Performance When “Paging”