c# programming for the microsoft sql server database dave henson [email protected]
TRANSCRIPT
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:• Windows xp, 2000 or 2003• .Net Framework 1.1• MSDE
• Recommended Software:• Visual Studio 2003• SQL Server Standard Version
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
Chapter 1 - Tools
• Visual Studio
• Command Line Tools
• Other Tools
Visual Studio
• Fully integrated IDE
• Intellisense
• Automatic Code Generation
• Drag/Drop Database and Control Interface
• Database and Other Design Wizards
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
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
Other Tools
• IDE– Codecharge studio
• Automated Code Generation:– SQL Queries that dynamically c#– C# that dynamically builds c#– C# that dynamically builds SQL
Chapter 2 – C# Review
• Program Structure
• Case sensitivity
• if/while/foreach
• try/catch
• Creating objects
Program Structureusing System;
class App{ public static void Main() { Console.WriteLine("hey"); Console.ReadLine(); }}
Case Sensitivity
• Most .Net classes are Mixed Case
• Many C# keywords are lower case
• Valid Code:…
String greeting = “hello”;
String Greeting = “goodbye”;
if Statement
if (loop != 20)
{
string errorMessage = “Oops”;
}
while statement
while(dr.Read())
{
Console.WriteLine(dr[“FirstName”]);
}
foreach statementDirectoryInfo dir = new DirectoryInfo(@“c:\\temp”);
try { foreach(FileInfo f in dir.GetFiles()) { Console.WriteLine(f.Name); }}catch(Exception e){ Console.WriteLine(e.Message);}Finally{ }
Creating Objects
• An object is an instance of a class
• Examples:String stuff = “hello”;
MyClass class = new MyClass(1);
SqlDataSet ds = CreateDataSet(query);
Chapter 3 - Class Libraries
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
Purpose
• Supply re-usable code to developer
• Maintain consistency
• Ease of maintenance – Separation of front end from back end
• Reduce development costs
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; }}
Properties
• Properties are methods!
• Get
• Set
• “value” keyword has special meaning
Application Layers
• Presentation
• Business Logic
• Data Access
• Potential ease of use and potential disaster
Chapter 4 – ADO.Net
ADO.NET Components
• SqlConnection
• SqlDataAdapter
• DataSet
• DataTable
• DataRow, DataColumn collections
• SqlDataReader
• SqlCommand
Required Namespaces
• System.Data
• System.Data.SqlClient
Providers
• Providers Available:– SQL Server .NET Provider– OleDB .NET Provider
• Example-AllRecordsBasicOleDB.aspx
– ODBC .NET Provider– SQL XML .NET Provider
Connections
• Connection Defined
• Where to Store the Connection String
• Connection Syntax Examples
• Connection Pooling
• Security
• Close Your Connections!
• Monitoring Connections
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
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>
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!
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
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
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
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
Controlling the Presentation
• HTML-Use Stylesheets if Possible!
• Make Sure The Page Fails Gracefully If Needed
• With DataGrids, Use TemplateColumns
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”