ADO.Net

1
ADO.Net

• CS795

2
What is ADO.Net?

• Database language spoken by managed applications
• ADO.net database accesses go through modules
  data providers
• SQL Server .Net provider---to interface with MS
  SQL databases without any help from unmanaged
  providers
• OLE DB .Net provider---to interface with other
  databases through unmanaged OLE DB providers
• OLE DB providers provide a uniform API over a
  variety of databases

3
System.Data.SqlClient

• using System.Data.SqlClient
• SqlConnection conn new SqlConnection
• (server(local) Initial Catalog
  databasepubs uidmukka pwd)
• Or
• (server(local) Initial Catalog
  databasepubs Trusted_ConnectionYes)
• try
• conn.Open()
• SqlCommand cmd new SqlCommand(select
  from titles, conn)
• SqlDataReader reader cmd.ExecuteReader
  ()
• while (reader.Read ())
• Console.Writeln (readertitle)
• Catch (SqlException ex) Console.WriteLine
  (ex.message)
• finally conn.Close()
• Note For OLEDB, simply replace Sql in the above
  code by OleDb

4
Connections, Commands, DataReaders

• The canonical usage pattern for executing
  database commands in ADO.Net
• Create a connection object encapsulating a
  connection string
• Open the connection by calling Open on the
  connection object
• Create a command object encapsulating both an SQL
  command and the connection that the command will
  use
• Call a method on the command object to execute
  the command
• Close the connection by calling Close on the
  connection object

5
SqlConnection Class

• SqlConnection conn SqlConnection()
• Conn.ConnectionString serverlocalhost
  databasepubs uidmukka pwd
• Or
• SqlConnection conn serverlocalhost
  databasepubs uidmukka pwd
• Other parameters for ConnectionString
• http//authors.aspalliance.com/aspxtreme/sys/data/
  sqlclient/SqlConnectionClassConnectionString.aspx
• It can also be defined on web.config
• The constructor of the SqlConnection object
  expects one parameter the connection string. The
  connection string identifies the SQL server name,
  the SQL database name, and satisfies the
  authorization requirement by providing a user
  name and a password, or by specifying
  Trusted_Connectiontrue. You can specify the
  connection string in line within the constructor
  line, or you can specify it in Web.config
• ltadd key"ConnectionString value"serverHAW2L1800
  \NetSDKTrusted_ConnectiontruedatabaseStoreDOC
  /gt

6
Open and Closing Connections

• You must first open a connection and close it at
  the end.
• SqlConnection conn new SqlConnection
• (serverlocalhost databasepubs uidmukka
  pwd)
• try
• conn.Open()
• .
• catch (SqlConnection ex) ..
• finally conn.Close()

7
Command Classes

• ExecuteReader (for read)
• ExecuteNonQuery (for updates)
• SqlConnection conn new SqlConnection
  (serverlocalhost datbasepubs uidmukka
  pwd)
• try
• conn.Open()
• SqlCommand cmd new SqlCommand ()
• cmd.CommandText delete from title where
  title_id xxxx
• cmd.Connection conn
• cmd.ExecuteNonQuery ()
• catch (SqlException ex)
• .
• finally conn.Close()

8
ExecuteNonQuery

• To execute operations where database is changed
• Example insert, update, delete, create database,
  create table, etc.
• Insert, update, delete Returns number of rows
  affected by the operation
• Returns -1 for others
• SqlCommand cmd new SqlCommand
• (insert in to titles (title_id, title, type,
  pubdate)
• values (CS150,C Programming, )
• computer science, May 2006, conn)
• cmd.ExecuteNonQuery()

9
ExecuteScalar

• Returns the 1st row of the 1st column in the
  result
• Used for commands such as count, avg, min, max,
  sum
• try
• conn.Open()
• SqlCommand cmd new SqlCommand
• (select max (advance) from title,
  conn)
• decimal amount (decimal) cmd.ExecuteScalar
  ()
• Console.WriteLine (ExecuteScalar returned
  (0c), amount)
• Catch (SqlException ex Console.Writeln
  (ex.Message)
• finally conn.Close()

10
ExecuteScalar (cont.)

• To retrieve BLOBs (Binary large objects) from
  databases
• http//builder.com.com/5100-6371-5766889.html
• http//support.microsoft.com/default.aspx?scidkb
  en-us309158
• http//www.codeproject.com/cs/database/images2db.a
  sp
• http//www.codeproject.com/useritems/Blobfield.asp
  
• FileStream stream new FileStream (Logo.jpg,
  FileMode.Open)
• byte blob new byte stream.Length
• stream.Read (blob, 0, (int) stream.Length)
• stream.Close()
• SqlConnection con new
• try conn.Open()
• SqlCommand cmd new SqlCommand
• (insert into pub_info (pub_id, logo) values
  (9937, _at_logo), conn)
• cmd.Parameters.Add (_at_logo, blob)
• cmd.ExecuteNonQuery ()
• catch
• finally

11
ExecuteScalar (Cont.)

• To validate a user name and password (page
  506-507, Jeff Prosie book)
• Try conn.Open()
• StringBuilder builder new StringBuilder ()
• builder.Append (select count () from users
  where username
• .
• int count (int) command.ExecuteScalar ()
• return (count gt 0)

12
ExecuteReader Method

• To perform database queries
• Returns a DataReader object SqlDataReader or
  OleDataReader
• try conn.Open()
• SqlCommand cmd new SqlCommand(select
  from titles, conn)
• SqlDataReader reader cmd.ExecuteReader()
• While (reader.Read())
• Console.WriteLine (readertitle)
• try conn.Open() SqlCommand cmd new
  SqlCommand(select from titles, conn)
• SqlDataReader reader cmd.ExecuteReader()
• for (int i0 I ltreader.FieldCount i)
  Console.WriteLine (reader.GetNamei))
• reader.Close()

13
Transactions

• Ex transfer funds from one account (say 1234) to
  another account (say 9876).
• SqlTransaction trans null
• SqlConnection conn new SqlConnection
  (serverlocalhost databasemybank uidmukka
  pwd)
• tryconn.Open()
• trans conn.BeginTransaction (IsolationLevel.Seri
  alizable)
• SqlCommand cmd new SqlCommand()
• cmd.Connection conn
• cmd.Transactiontrans
• cmd.CommandText update accounts set balance
  balance-1500 where account_id 1234
• cmd.ExecuteNonQuery()
• cmd.CommandText update accounts set balance
  balance1500 where account_id 9876
• cmd.ExecuteNonQuery()
• Trans.Commit()
• Alternate to Commit is Rollback.

14
Parameterized Commands

• When commands are the same but the parameters are
  different
• Try conn.Open()
• SqlCommand cmd new SqlCommand(update
  accounts set balance balance
• _at_amount where account_id _at_id,
  conn)
• cmd.Parameters.Add (_at_amount, SqlDbType.Money)
• cmd.Parameters.Add (_at_id, SqlDbType.Char)
• cmd.Parameters_at_amount.Value -1500
• cmd.Parameters_at_id.Value 1234
• cmd.ExecuteNonQuery ()
• cmd.Parameters_at_amount.Value 1500
• cmd.Parameters_at_id.Value 9867
• cmd.ExecuteNonQuery ()

15
Stored Procedures

• User defined command added to a database
• Execute faster because they are already in
  compiled form.
• 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_From
• IF _at__at_ROWCOUNT 0
• BEGIN ROLLBACK TRANSACTION RETURN END
• COMMIT TRANSACTION
• GO

16
How does an application call the stored procedure?

• SqlConnection conn new SqlConnection
  (serverlocalhost databasemybank uidmukka
  pwd)
• tryconn.Open()
• SqlCommand cmd new SqlCommand
  (proc_TransferFunds, conn)
• cmd.CommandType CommandType.StoredProcedur
  e
• cmd.Parameters.Add (_at_amount, 1500)
• cmd.Parameters.Add (_at_from, 1234)
• cmd.Parameters.Add (_at_to, 9876)
• cmd.ExecuteNonQuery ()
• Catch (SqlException ex) .
• finally conn.Close()

17
Example where it returns a value

• CREATE PROCEDURE proc_GetBalance
• _at_ID char(10), _at_Balance money OUTPUT
• AS
• SELECT _at_Balance Balance From Accounts WHERE
  Account_ID _at_ID
• IF _at__at_ROWCOUNT 1 RETURN 0
• ELSE BEGIN SET _at_Balance 0 RETURN -1 END
• GO
• SqlConnection conn new SqlConnection
  (serverlocalhost databasemybank uidmukka
  pwd)
• tryconn.Open()
• SqlCommand cmd new SqlCommand
  (proc_GetBalance, conn)
• cmd.CommandType CommandType.StoredProcedur
  e
• cmd.Parameters.Add (_at_id, 1234)
• SqlParameter bal cmd.Parameters.Add
  (_at_balance, SqlDbType.Money)
• bal.Direction ParameterDirection.Output
• SqlParameter ret cmd.Parameters.Add
  (_at_return, SqlDbType.Int)
• ret.Direction ParameterDirection.ReturnVal
  ue
• cmd.ExecuteNonQuery ()
• int retval (int) ret.value

18
DataSet Class
Dataset Database
DataTable Table
DataRow Records in a DataTable
DataColumn Fields in a DataTable
DataSet.Tables
DataTabe.Rows
DataTable.Columns
UniuqeConstraint Add a contsraint to a column
DataRelation Relationship between two tables
19

• Datasets are ideal for retrieving results from
  database queries and storing them in memory.
• In addition, this data may be modified and
  propagated back to the database.
• It can also support, random access to the data
  (unlike DataReader)
• Great for caching, especially in web applications.

20
DataSets vs. DataReaders

• If the application simply queries a database and
  reads through the records one at a time until it
  finds the record it is looking for, DataReader is
  the right tool
• If the application requires all results from a
  query, say to display in a table, and have
  ability to iterate back and forth through the
  result set, DataSet is a good alternate.

21
DataAdapter

• DataSets dont interact with databases directly
• Instead, they interact through DataAdapters
• Purpose To perform database queries and create
  DataTables containing the query results also, to
  write the modified DataTables into databases
• Fill and Update

22
DataAdapter.Fill

• SqlDataAdapter adapter new SqldataAdapter
  (select from titles,
• serverlocalhost databasepubs uidmukka
  pwd)
• DataSet ds new dataSet ()
• adapter.Fill (ds. Titles)
• What does Fill do?
• Open a connection to the pubs database using
  adapter.
• Performs a query on the pubs database using the
  query string passed to adapter.
• Creates a DataTable named Titles in ds.
• Initializes DataTable with a schema that matches
  that of the Titles table in the database.
• Retrieves records produced by the query and
  writes them to the DataTable
• Closes the connection to the database

23
DataTable

• foreach (DataTable table in ds.Tables)
  Console.WriteLine (table.TableName)
• DataTable table ds.Tables0
• foreach (DataRow row in table.Rows)
  Console.WriteLine(row0)
• DataTable table ds.Tables0
• foreach (DataRow row in table.Rows)
  Console.WriteLine(rowaccount_id)
• DataTable table ds.Tables0
• foreach (DataColumn col in table.Columns)
• Console.WriteLine(Name0, Type 1,
  col.ColumnName, col.DataType)

24
Insert a record into DataTable

• SqlDataAdapter adapter new SqldataAdapter
  (select from titles,
• serverlocalhost databasepubs uidmukka
  pwd)
• DataSet ds new dataSet ()
• adapter.Fill (ds. Titles)
• DataTable table ds.TablesTitles
• DataRow row table.NewRow ()
• rowtitle_id CS795
• rowtitle .Net Security
• rowprice70.99
• Table.Rows.Add (row)

25
Propagating Changes back to Database

• SqlDataAdapter adapter new SqldataAdapter
  (select from titles,
• serverlocalhost databasepubs uidmukka
  pwd)
• SqlCommandBuilder builder new SqlCommandBuilder
  (adapter)
• DataSet ds new dataSet ()
• adapter.Fill (ds. Titles)
• DataTable table ds.TablesTitles
• DataRow row table.NewRow ()
• rowtitle_id CS795
• rowtitle .Net Security
• rowprice70.99
• table.Rows.Add (row)
• adapter.Update (table)
• (only writes the ones that were changed)

26
Links

• The C Station ADO.NET Tutorial
• Using ADO.NET for beginners
• In Depth ASP.NET using ADO.NET

27
Links

• The C Station ADO.NET Tutorial
• Using ADO.NET for beginners
• In Depth ASP.NET using ADO.NET