1 / 37

C# Programming for the Microsoft SQL Server Database

C# Programming for the Microsoft SQL Server Database. Dave Henson dhenson@certfiednetworks.com. Logistics. Class Hours Notes/Handouts Demos Class website. Recommended Reading. Beginning C# Databases, APress, ISBN 1-59059-433-9. Course Setup. Software required to complete course labs:

zora
Download Presentation

C# Programming for the Microsoft SQL Server Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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”

More Related