Transcript C# Programming for the Microsoft SQL Server Database

C# Programming for the
Microsoft SQL Server Database
Dave Henson
[email protected]
Logistics
•
•
•
•
Class Hours
Notes/Handouts
Demos
Class website
Recommended Reading
• Beginning C# Databases, APress, ISBN 159059-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 Structure
using 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 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
{
}
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/MaxExample6ShowConnectionStatePoolControl.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 MethodsExample7AdapterVsReaderUsingTypedAccessorMethods.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”