INT422 Internet III Web Programming on Windows

1
INT422 Internet III Web Programming on
Windows

• Commands
• Parameters
• Stored Procedures

2
Agenda

• Review what you should know about commands
• then
• More about commands
• ADO.NET parameters what, when, how
• Database Stored Procedures

3
Recap what you should know
4
You should know

• ADO.NET architecture basics
• How to hand-code a connection
• You will be tested on this
• Dont drag-and-drop data toolbox objects yet
• Command object basics hand coding
• SELECT
• Use a web forms list control as a destination
• INSERT
• UPDATE
• DELETE

5
ADO.NET architecture basics

• Object-oriented
• Connection and command objects are very important
  
• Algorithm
• Declare and configure the connection
• Declare and configure the command
• Execute the command
• Do something with the result

6
Hand-code a connection

• Algorithm
• Decide on and declare your data provider
• Configure the connection string
• Connection string minimal requirements
• Data Source
• Initial Catalog
• User ID
• Password

7
Connection example

• Heres an example, using Northwind
• //Declare a connection object
• SqlConnection con new SqlConnection()
• //Configure its important properties
• con.ConnectionString
• "Data Sourcemaia.senecac.on.ca"
• "Initial CatalogNorthwind"
• "User IDhermesID"
• "Passwordxxxxxxxx"

8
Command object details
9
Data command objects - overview

• A data command object is an instance of the
  SqlCommand class
• Properties you should know
• Connection
• CommandText
• Parameters
• Methods you should know
• ExecuteReader
• ExecuteScalar
• ExecuteNonQuery

10
Data command objects more info

• Your command can return
• A result set
• You then read the result set, and store the row
  data in a web form control (listbox, drop-down
  list, etc.)
• A single value
• Good for lookups, or for calculating a single
  value
• A value holding the count/number of affected
  records
• As a result of an INSERT, UPDATE, or DELETE
  (etc.)
• Wrap your command execution in a Try Catch code
  block

11
Command object SELECT (pg. 1)

• Heres an example, using Northwind
• //Declare a command object
• SqlCommand cmd new SqlCommand()
• //Configure its important properties
• cmd.Connection con
• cmd.CommandType CommandType.Text
• //Build the command text - using plain SQL
  example
• cmd.CommandText "SELECT ProductName FROM "
• "Products ORDER BY ProductName"

12
Command object SELECT (pg. 2)

• //Execute the command
• //The result set will be attached to a
• //newly declared SqlDataReader object
• con.Open()
• SqlDataReader dr sqlCmd.ExecuteReader()
• //Go through the result set and
• //load the web forms control (ListBox1)
• ListBox1.Items.Clear()
• while (dr.Read())
• ListBox1.Items.Add(dr"ProductName".ToString()
  )
• dr.Close()
• con.Close()

13
Command object INSERT (pg. 1)

• Heres an example Assignments Users table
• //Build the command text - using plain SQL
  example
• cmd.CommandText
• "INSERT INTO Users "
• "(user_fname, user_lname, user_pwdsalt, "
• "user_pwdhash, user_lastvisit) "
• "VALUES "
• "('" tbuser_fname.Text "', "
• "'" tbuesr_lname.Text "', "
• "'" pwdSalt "', "
• "'" pwdHash "', "
• DateTime.Now ")"

14
Command object INSERT (pg. 2)

• //Execute the command - store the result in a
  label
• con.Open()
• int rows cmd.ExecuteNonQuery()
• con.Close()
• lblResult.Text "Rows added "
  rows.ToString()

15
Command object UPDATE

• Continue with the Users table
• //Build the command text - using plain SQL
  example
• cmd.CommandText
• "UPDATE Users "
• "SET user_fname "
• "'" tbuser_fname.Text "' "
• "WHERE pk_user_id "
• "'" tbuser_login.Text "'"
• //Execute the command
• con.Open()
• cmd.ExecuteNonQuery()
• con.Close()

16
Command object DELETE

• Continue with the Users table
• //Build the command text - using plain SQL
  example
• cmd.CommandText
• "DELETE FROM Users "
• "WHERE pk_user_id "
• "'" tbuser_login.Text "'"
• //Execute the command
• con.Open()
• cmd.ExecuteNonQuery()
• con.Close()

17
Command Parameters
18
Command parameters

• Use them
• They improve the quality of your code
• They make your code more readable and
  maintainable
• They reduce errors
• They improve security
• RequirementUse command parameters from now on

19
Command parameters overview

• Parameters A collection in the command
  object instance
• Add()
• Clear()
• Item()
• The Add method and Item property also have a
  Value property
• The name of each parameter should be in the
  format _at_xxxx where the xxxx matches the name
  of the database table column

20
Command parameter object

• A parameter is an object of type SqlParameter
• See the Object Browser or MSDN Library for a
  complete treatment of this types properties
  methods
• Recommendation declare and store parameters
  using the SqlDbType
• Whats a SqlDbType?
• Its an enumeration of SQL Server data types
• They allow you to map your page variables/values
  directly into SQL Server data types (which
  reduces casting/conversion errors)

21
Command parameter syntax

• Use the Add method signature that lets you
  specify the name, SqlDbType, and length
• Optionally you can also set the Value
• Heres a create (Add) example
• SqlCommand cmd new SqlCommand()
• cmd.Parameters.Add("_at_FirstName",
  SqlDbType.VarChar, 10).Value "David
• cmd.Parameters.Add("_at_LastName",
  SqlDbType.VarChar, 20).Value "Humphrey"
• cmd.Parameters.Add("_at_Title", SqlDbType.VarChar,
  30).Value "Professor"
• cmd.Parameters.Add("_at_City", SqlDbType.VarChar,
  15).Value "Toronto"

22
Command parameters using them

• Recall slide 13? Parameters yield cleaner code
• //Build the command text - using plain SQL
  example
• cmd.CommandText
• "INSERT INTO Users "
• "(user_fname, user_lname, user_pwdsalt, "
• "user_pwdhash, user_lastvisit) "
• "VALUES "
• "(_at_user_fname, _at_user_lname, _at_user_pwdsalt, "
  
• "_at_user_pwdhash, _at_user_lastvisit)"

23
SQL ServerStored Procedures
24
Stored procedures overview

• Stored procedures are SQL language programs that
  have been tested for accuracy and compiled for
  fast execution
• Think of them as functions you can call
  remotely
• To use a stored procedure, you set the values of
  its parameters and then execute it
• A stored procedure can do anything a SELECT,
  INSERT, UPDATE, or DELETE command can do, and
  more

25
Stored procedures in INT422

• Stored procedures are stored on the database
  server
• Usually, stored procedures are associated with a
  specific database
• SQL Server itself has a number of useful system
  stored procedures to do administrative functions
  
• Your Assignment database has been configured with
  some useful stored procedures
• See the walkthrough for details
• Should you use stored procedures? Yes

26
Stored procedure example (1)

• Stored procedures always accept parameters, and
  (just like regular SQL commands) may or may not
  return a result set or value
• We may create a page that will run the SQL Server
  sp_password stored procedure, which will let us
  change our maia database server password
• sp_password reference Google search
  termtransact-sql reference sp_password
  sitemsdn.microsoft.com

27
Stored procedure example (2)

• Heres the syntax
• sp_password _at_old 'old_password' ,
  _at_new 'new_password' , _at_loginame
  'login'
• Lets create a page that will let us enter the
  old (existing) password, a new password, and then
  execute the sp_password stored procedure
• This page will be the similar in function to
  http//hermes.senecac.on.ca/space/sam.aspx

28
Stored procedure example (3)

• // Declare and configure a connection object
• // We will not need an "Initial catalog" element,
  
• // because sp_password is a system-wide stored
  procedure
• SqlConnection con new SqlConnection()
• con.ConnectionString "Data sourcemaia.senecac.o
  n.ca"
• "User IDlearnidPassword"
  tbOldPassword.Text
• // Declare and configure a command object
• SqlCommand cmd new SqlCommand()
• cmd.Connection con
• cmd.CommandType CommandType.StoredProcedure
• cmd.CommandText "sp_password"

29
Stored procedure example (4)

• // Configure the parameters
• cmd.Parameters.Add("_at_old", SqlDbType.VarChar).Valu
  e tbOldPassword.Text
• cmd.Parameters.Add("_at_new", SqlDbType.VarChar).Valu
  e tbNewPassword.Text

30
Stored procedure example (5)

• ' Run the stored procedure
• try
• con.Open()
• cmd.ExecuteNonQuery()
• // Notify the user
• lblChangeIt.Text "Password has been
  changed"
• catch(Exception ex)
• // If we are here, there was a connection
  problem,
• // or sp_password had an execution error
• lblChangeIt.Text ex.Message
• finally
• con.Close()