ADO.NET Framework - C Sharp Programming - Lecture Slides, Slides of C Sharp Programming

Download ADO.NET Framework - C Sharp Programming - Lecture Slides and more Slides C Sharp Programming in PDF only on Docsity!

ADO.NET Framework

Lecture Overview

 Creating connections to providing  Executing SQL commands using providers that  Return nothing  Return one value  Read data one record at a time  Use DataAdapters to create in-memory datasets and datatables

Connections (Introduction)

 The OleDbConnection and SQLConnection classes give us the means to make a database connection  Commands are sent over this connection  The ConnectionString property contains the string that the systems uses to make the database connection  It differs from provider to provider

Connections (Using)

 Call the Open and Close methods to explicitly open and close the connections  Use for OleDbCommands  Some objects call these methods for you

Using a Connection

(Introduction)

 Once we have created a connection, we send commands over that connection  Most commonly using the OleDbCommand or SqlCommand objects  There are properties to configure the command and connection  There are methods to execute the command using the connection

OleDbCommand (Properties)

 CommandText property stores SQL statement that will be executed against a provider  Connection property stores a reference to existing OleDbConnection object

 Parameters property stores a reference to a Parameters collection (more later)

Using the OleDbCommand

Object ( ExecuteScalar )

 The ExecuteScalar method returns a single value (first column / first row)  Will not fail if you return multiple rows  Use to get a max ID value

ExecuteScalar (Example)

The DataReader

(Introduction)

 It’s a forward-only reader (like a stream)  You read one record at a time  Each time a record is read, the reader contains a reference to an array of fields

Using the OleDbDataReader

 Place an SQL SELECT statement in the OleDbcommand  Call ExecuteReader to create the forward- only reader  Call Read to read each record  Then call the various Get methods to get the value of each field

Returning a DataSet

 This gets a bit more complicated  We need another object called a DataAdapter  Microsoft calls it a bridge between a DataSet and a DataSource  Again, there are OleDb and Sql Server versions of these

 I’ll go through the OleDbDataAdapter and DataSet , in turn

OleDbDataAdapter Class

(Introduction)

 OleDbDataAdapter works in conjunction with the DataSet class to read and write data  Commands are sent over a connection  Data is retrieved into a DataSet  Commands to insert, update, or delete data return nothing

OleDbDataAdapter

(Methods)

 Fill method uses an OleDbConnection to retrieve database records  Records are loaded into a DataSet  Update method examines the DataSet for any added, changed, or deleted records  Changes are recorded to DataSet  Call AcceptChanges method on DataSet to synchronize DataSet and database

OleDbDataAdapter

(Events)

 FillError event fires if an error occurs while populating the DataSet  RowUpdating event fires just before row is updated  RowUpdated event fires just after a row in the data source is updated