ADO'Net and Stored Procedures

1
ADO.Net and Stored Procedures
By Radhika Malladi
2
Introduction

• ADO.Net database API, used for managed
  applications
• It consists of a set of classes
• Integrates with XML
• It provides components for creating distributed,
  data-sharing applications
• It is used to connect to data source for
  retrieving, updating and manipulating data.

3
Introduction cont.

• Data providers are used to connect to the
  database, to execute commands and retrieving
  results
• ADO.Net provides two data providers
• SQL Server .Net provider Interfaces
  MicrosoftSQLServer with Managed
  applications(System.data.SqlClient)
• OLE DB .Net provider Interfaces databases with
  UnManaged OLE DB providers(System.data.OleDb)

4
Design Goals

• ADO.Net is designed to meet the following goals
• Leverage current ADO knowledge
• Support N-tier programming model
• ("Any number of levels arranged above another,
  each serving distinct and separate tasks.)
• Done by using Data Set because Data Set works
  well with all providers
• Integrating XML

5
ADO.Net Components

• Two main components
• Data Set disconnected architecture of ADO.Net
• .Net data provider components for data
  manipulation and read-only access to data
• Connection to make connections to database
• Command access to database commands
• Data Reader provides stream of data from data
  source
• Data Adapter bridge between Data Set object and
  data source

6
Architecture
7
.Net provider Components

• Connection
• SqlConnection conn  new SqlConnection
      ("serverlocalhostdatabasea1uidsapwd")
• SqlConnection conn  new SqlConnection
      ("serverxyz\malladidatabasea1uidsapwd")
  
• SqlConnection conn  new SqlConnection
      ("serverxyz\malladidatabasea1uidsapwdm
  in pool size10max pool size50connect timeout1
  0Integrated Security falsePooling true")
• OleDbConnection conn  new OleDbConnection     
• ("providersqloledbdata sourcelocalhostOLE DB S
  ervices-2"     "initial cataloga1user idsap
  assword")

8
.Net Components cont.

• Command
• SqlCommand cmd  new SqlCommand ("select  from us
  ers", conn)
• Properties CommadTimeout , CommandText
• Methods
• ExecuteNonQuery() This returns number of rows
  affected
• Ex Insert, Delete, Update (no return values)
• ExecuteScalar() single row, single column
  values
• Ex count, min, max, sum, avg etc and to retrive
  BLOBs
• ExecuteReader()

9
.Net Components cont.

• Data Reader
• SqlDataReader reader  cmd.ExecuteReader ()
• ExecuteReader() obtains query results quickly
  for read-only.
• Ex Select
• reader.close()
• Methods
• GetName retrieve field names
• GetValue retrieve field values(returns obj)
• GetOrdinal converts field name into numeric
  index

10
.Net Components cont.

• Data Adapter
• SqlDataAdapter adapter  new SqlDataAdapter ("sele
  ct  from users",     "serverlocalhostdatabasea
  1uidsapwd")

11
Example (ExecuteReader)

• SqlConnection con new SqlConnection("Data
  Source.\\SQLEXPRESSAttachDbFilenameC\\Inetpub\
  \wwwroot\\Assign2\\App_Data\\a2.mdfIntegrated
  SecurityTrueUser InstanceTrue")
• try
• con.Open()
• SqlCommand cmd new
  SqlCommand("select role from users where username
  '" TextBox1.Text.Trim() "' and password
  '" TextBox2.Text.Trim() "'", con)
• SqlDataReader dr
  cmd.ExecuteReader()
• while (dr.Read())
• role dr.GetString(dr.GetOrdi
  nal("role"))
• break
• catch (SqlException ex)
• Response.Write(ex.Message)
• finally
• con.Close()

12
Example (ExecuteScalar)

• SqlConnection connection new
  SqlConnection("Data Source.\\SQLEXPRESSAttachDbF
  ilenamec\\inetpub\\wwwroot\\Assign2\\App_Data\\a
  2.mdfIntegrated SecurityTrueUser
  InstanceTrue")
• try
• connection.Open()
• StringBuilder builder new
  StringBuilder()
• builder.Append("select count () from
  users where username \'")
• builder.Append(username)
• builder.Append("\' and cast (rtrim
  (password) as varbinary) cast (\'")
• builder.Append(password)
• builder.Append("\' as varbinary)")
• SqlCommand command new
  SqlCommand(builder.ToString(),connection)
• int count (int)command.ExecuteScalar
  ()
• return (count gt 0)
• catch (SqlException)
• return false
• finally
• connection.Close()

13
Example (ExecuteNonQuery)

• SqlConnection con new SqlConnection("Data
  Source.\\SQLEXPRESSAttachDbFilenameC\\Inetpub\
  \wwwroot\\Assign2\\App_Data\\a2.mdfIntegrated
  SecurityTrueUser InstanceTrue")
• try
• con.Open()
• SqlCommand cmd new SqlCommand("insert into
  users (username,password,role)values('"
  TextBox6.Text.Trim() "','" TextBox7.Text.Trim(
  ) "','" DropDownList1.SelectedValue "')",
  con)
• int noofrows cmd.ExecuteNonQuery()
• if (noofrows ! 0)
• Response.Write("user created
  successfully")
• catch (SqlException ex)
• Response.Write(ex.Message)
• finally
• con.Close()

14
Data Set and Data Adapters

• Set based access captures query into memory and
  supports traversal through result set
• This has 2 classes
• Data Set in-memory database
• Data Adapter bridge between Data Set and data
  source
• Perform database queries
• create DataTables containing query results
• Capable of writing changes made to data tables
  back to database

15
Data Adapter
Two versions SqlDataAdapter and
OleDbDataAdapter Main Methods of Data Adapter
are Fill and Update Namespace
system.data.common.DbDataAdapter
16
Data Adapter cont.

• Fill
• SqlDataAdapter adapter  new SqlDataAdapter ("sel
  ect  from users",     "serverlocalhostdatabase
  a1uidsapwd") DataSet ds  new DataSet ()
• adapter.Fill (ds, users")
• Update
• adapter.Update (table)
• Builder for Insert, Update, Delete
• SqlCommandBuilder builder  new SqlCommandBuilder
   (adapter)

17
Data Set

• DataSet object supports disconnected,
  distributed data with ADO.Net
• Can be used with multiple and differing data
  sources
• It represents complete set of data like related
  tables, constraints and relationships among
  tables

18
Data Set cont.
19
Data Set cont.

• DataTable Collection contains collection of
  tables
• DataRelationCollection contains relationships of
  tables
• ExtendingProperties Property Collection where
  customized information can be placed
• Ex Date/Time when data is generated

20
Example to insert records

• SqlDataAdapter adapter  new SqlDataAdapter ("sele
  ct  from titles",     "serverlocalhostdatabase
  pubsuidsapwd")
• DataSet ds  new DataSet ()
• adapter.Fill (ds, "Titles")
• // Create a new DataRow
• DataTable table  ds.Tables"Titles"
• DataRow row  table.NewRow ()
• // Initialize the DataRow
• row"title_id"  "JP1001"
• row"title"  "Programming Microsoft .NET"
• row"price"  "59.99"
• row"ytd_sales"  "1000000"
• row"type"  "business"
• row"pubdate"  "May 2002"
• // Add the DataRow to the DataTable
• table.Rows.Add (row)

21
Examples to select records

• DataRow rows  table.Select ("title_id  'JP1001
  '")
• DataRow rows  table.Select ("price lt 10.00")
• DataRow rows  table.Select ("pubdate gt '1/1/2
  000'")
• DataRow rows  table.Select ("state in ('ca', 't
  n', 'wa')")
• DataRow rows  table.Select ("state like 'ca'")
  
• DataRow rows  table.Select ("isnull (state, 0) 
   0")
• DataRow rows  table.Select ("state  'tn' and z
  ip like '37'")

22
Example to update records

• SqlDataAdapter adapter  new SqlDataAdapter ("sele
  ct  from titles",     "serverlocalhostdatabase
  pubsuidsapwd")
• DataSet ds  new DataSet ()
• adapter.Fill (ds, "Titles")
• DataRow rows  table.Select ("ytd_sales gt 10000"
  )
• foreach (DataRow row in rows)
•     row"price"  (decimal) row"price"  10.00
  m

23
Example to delete records

• SqlDataAdapter adapter  new SqlDataAdapter ("sele
  ct  from titles",     "serverlocalhostdatabase
  pubsuidsapwd")
• DataSet ds  new DataSet ()
• adapter.Fill (ds, "Titles")
• DataRow rows      table.Select 
• ("ytd_sales lt 10000 OR isnull (ytd_sales, 0)  
  0")
• foreach (DataRow row in rows)
•     row.Delete ()

24
Example for Updating the database

• SqlDataAdapter adapter      new SqlDataAdapter ("
  select  from titles",     "serverlocalhostdatab
  asepubsuidsapwd")
• SqlCommandBuilder builder  new SqlCommandBuilder 
  (adapter)
• DataSet ds  new DataSet ()
• adapter.Fill (ds, "Titles")
• // Insert a record DataTable 
• table  ds.Tables"Titles"
• DataRow row  table.NewRow ()
• row"title_id"  "JP1001"
• row"title"  "Programming Microsoft .NET"
• row"price"  59.99m
• row"ytd_sales"  1000000
• row"type"  "business"
• row"pubdate"  new DateTime (2002, 5, 1)
• table.Rows.Add (row)
• // Update the database
• adapter.Update (table)

25
Stored Procedures

• User defined command added to a database
• Executes faster as they are already complied
• Improves performance
• ADO.Net supports stored procedures
• An Example of stored procedure is..

26

• CREATE PROCEDURE proc_TransferFunds  
•    _at_Amount money,
•     _at_From char (10),
•     _at_To char (10)
• AS    
•  BEGIN TRANSACTION  
•  UPDATE Accounts SET Balance  Balance _at_Amount  
• WHERE Account_ID  _at_From   
•   IF _at__at_ROWCOUNT  0     
• BEGIN      
•    ROLLBACK TRANSACTION    
•      RETURN  
•  END  
•   UPDATE Accounts SET Balance  Balance  _at_Amount
   
•        WHERE Account_ID  _at_To  
•    IF _at__at_ROWCOUNT  0
•     BEGIN      
•    ROLLBACK TRANSACTION    
•      RETURN   

27

• SqlConnection conn  new SqlConnection
      ("serverlocalhostdatabasemybankuidsapwd
  ")
• try      
• conn.Open ()   
•   SqlCommand cmd  new SqlCommand ("proc_Transf
  erFunds", conn)
•     cmd.CommandType  CommandType.StoredProcedure
    
•   cmd.Parameters.Add ("_at_amount", 1000)
•     cmd.Parameters.Add ("_at_from", 1111)  
•    cmd.Parameters.Add ("_at_to", 2222)
•     cmd.ExecuteNonQuery ()
• catch (SqlException ex)      // TODO Handle
   the exception
• finally      conn.Close ()

28
Transacted Commands Example

• SqlConnection conn  new SqlConnection
      ("serverlocalhostdatabasemybankuidsapwd
  ")
• try 
•     conn.Open ()   
•   // Debit 1,000 from account 1111   
•   SqlCommand cmd  new SqlCommand
   ("update accounts set balance  balance -1000
  where account_id  '1111'", conn)    
•  cmd.ExecuteNonQuery ()
•     // Credit 1,000 to account 2222  
•    cmd.CommandText  "update accounts set balance 
  "         "balance  1000 where account_id  '
  2222'"   
•   cmd.ExecuteNonQuery ()
• catch (SqlException ex)      // TODO Handle the 
  exception
• finally      conn.Close ()

29
Transacted Commands Example

• SqlTransaction trans  null SqlConnection conn  
  new SqlConnection     ("serverlocalhostdatabase
  mybankuidsapwd")
• try      conn.Open ()
•     // Start a local transaction
•     trans  conn.BeginTransaction (IsolationLevel.
  Serializable)   
•   // Create and initialize a SqlCommand object
•     SqlCommand cmd  new SqlCommand ()
•     cmd.Connection  conn     cmd.Transaction  
  trans  
•    // Debit 1,000 from account 1111  
•    cmd.CommandText  "update accounts set balance 
  balance - 1000 where account_id  '1111'"  
•    cmd.ExecuteNonQuery ()
•     // Credit 1,000 to account 2222   
•   cmd.CommandText  "update accounts set balance 
  "         "balance  1000 where account_id  '2
  222'"  
•    cmd.ExecuteNonQuery ()  
•    // Commit the transaction (commit changes)  
•    trans.Commit ()
• catch (SqlException)      // Abort the transactio
  n (roll back changes)  if (trans ! null)
  trans.Rollback ()
• finally      conn.Close ()

30
Parameterized Commands Example

• SqlConnection conn  new SqlConnection
      ("serverlocalhostdatabasemybankuidsapwd
  ")
• try  conn.Open ()  
•    // Create and initialize a SqlCommand object
    
•   SqlCommand cmd  new SqlCommand("update accounts
   set balance  balance  _at_amount "        
  "where account_id  _at_id", conn)
•     cmd.Parameters.Add ("_at_amount", SqlDbType.Mone
  y)
•     cmd.Parameters.Add ("_at_id", SqlDbType.Char)
    
•   // Debit 1,000 from account 1111    
•  cmd.Parameters"_at_amount".Value  -1000   
•   cmd.Parameters"_at_id".Value  "1111"   
•   cmd.ExecuteNonQuery ()  
•    // Credit 1,000 to account 2222
•     cmd.Parameters"_at_amount".Value  1000  
•    cmd.Parameters"_at_id".Value  "2222"
      cmd.ExecuteNonQuery ()
• catch (SqlException ex)      // TODO Handle the 
  exception
• finally      conn.Close ()

31
References

• ADO.Net by Alex Homer
• Programming Microsoft .Net by Jeff Prosise
• http//msdn2.microsoft.com/en-us/library/e80y5yhx(
  VS.71).aspx
• http//www.ondotnet.com/pub/a/dotnet/excerpt/progv
  isbasic_ch08/index.html

32

• Thank you