C

1
C Programming for the Microsoft SQL Server
Database

• Dave Henson
• dhenson_at_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
• CgtCsc Simple.cs
• Produces Simple.exe in current directory
• CgtCsc /targetlibrary DBUtil.cs
• Produces DBUtil.dll in the current directory
• CgtCsc /rDBUtil.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(drFirstName)

16
foreach statement

• DirectoryInfo dir new DirectoryInfo(_at_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 server10.0.0.1UIDsaPWD
• Dim Conn As New SqlConnection(ConnString)
• In Web.Config XML file
• ltconfigurationgt
• ltappSettingsgt
• ltadd keyConnString valueserver10.0.0.1UI
  DsaPWD/gt
• lt/appSettingsgt
• lt/configurationgt

31
Connection Pooling

• Defined
• Controlling Min/Max-Example6ShowConnectionStatePoo
  lControl.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-Example7AdapterVsReaderUsin
  gTypedAccessorMethods.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