ADO. NET

1
ADO. NET
2
What is ADO.Net?

• ADO.Net is a new object model for dealing with
  databases in .Net. Although some of the concepts
  are similar to the classical ADO, there are some
  new concepts such as the Dataset.

3
Introduction to ADO.NET
MS-Access MS-SQL Description
NameSpace System.Data.OleDb System.Data. SqlClient
Connection OleDbConnection SqlConnection opening a connection to the database
Command OleDbCommand SqlCommand invoking SQL commands or stored procedures
RecordSet OleDbDataReader SqlDataReader connected forward-only access to database
DataSet OleDbDataAdapter SqlDataAdapter populating a Dataset.
Stored Procedure OleDbParameter SqlParameter specifying parameter to a stored procedure
ransaction OleDbTransaction SqlTransaction programming database transactions
4
Process of using DB (1)

1. Import Namespace
2. Make Connection
3. Open Connection
4. Send Query
5. Execute Query
6. Store result
7. Populate data in UI
8. Close connection

5
Process of using DB (2)

• SqlConnection conn new SqlConnection(connstr)
• //using(SqlConnection conn new
  SqlConnection(connstr))
• conn.Open()
• SqlCommand cmd new SqlCommand(SQLstr,conn)
• SqlDataReader reader cmd.ExecuteReader()
• while (reader.Read())
• listBox1.Items.Add(reader1.ToString())
• conn.Close()

6
Connection(1)

• Connection String
• MS-SQL
• string connstr "serverSERVERNAMEuidUSERIDpwd
  PASSWORDrdatabaseDBNAME"
• string connstr "serverrainnyseauidtestuserpw
  dtestuserdatabaseTeststd"
• MS-Acess
• string connstr "ProviderProviderNameVersionDa
  ta SourceFileNameOnServer"
• string connstr "ProviderMicrosoft.Jet.OLEDB.4.0
  Data SourceD\\cs440\\Test\\data\\xyz.mdb"

7
Connection(2)

• database connections are an important resource
  and you should explicitly close the connection .
• conn SqlConnection(ConnString)
• try
• // open the connection to the database
• conn.Open()
• // do something with the database
• catch (Exception e)
• // report error
• finally
• conn.Close( )
•  

8
Execute Method on the Command
ExecuteNonQuery( ) used when no records are expected in the output e.g., insert or update sql commands.
ExecuteReader( ) returns a connected DataReader (actually DataReader interface)
ExceuteScalar( ) returns a single row, useful when verifying login for a particular user.
ExecuteXmlReader( ) returns an XML reader.
9
Command(1) ExecuteScalar()

• ExecuteScalar()
• SqlCommand cmd
• new SqlCommand(qry, this.con )
• Object obj cmd.ExecuteScalar()
• // returns one row

10
Command(2) ExecuteReader()

• ExecuteReader()
• SqlCommand cmd
• new SqlCommand(sql,conn)
• SqlDataReader reader cmd.ExecuteReader()

11
Command(3) ExecuteNonQuery()

• ExecuteNonQuery()
• SqlCommand cmd
• new SqlCommand(sql,conn)
• int cntrows cmd.ExecuteNonQuery()

12
Data Stored Object(1)
DataReader It present a forward-only stream of data to the application.
DataSet It is an in-memory representation of a collection of related data tables. It can be generated using the help of a DataAdapter or filled dynamically by the application
DataAdapter A class that is used to fill DataSet object from a data source.
13
Data Stored Object(2)
DataReader
DataSet
14
What is Data Set?

• A Dataset is a disconnected object that can
  potentially contain all or part of the database
  including tables, constraints and their
  relationships. Thus for using databases in web
  applications, Dataset can provide a much higher
  performance for mostly read-only type of data.
• The DataSet class can further use DataTable,
  DataRow, DataColumn, DataRelation and Constraint
  classes to define its offline data.

15
What is ths Data Adapter?

• DataAdapter class acts as the communication point
  between the DataSet And tha database.
• This object is created much the same way as the
  Command object.
• Fill() method fills DataSet with data obtained
  from SQL query.

16
DataSet(1)

• DataSet DS new DataSet()
• SqlDataAdapter Adapter
• new SqlDataAdapter(qry, conn)
• Adapter.Fill(DS, tblName)

17
DataGrid Control(1)

• displays data in a series of rows and columns.
• displaying either a single table or the
  hierarchical relationships between a set of
  tables.
• update the data in the bound DataSet, the
  DataGrid control reflects the changes.

18
DataGrid Control(2)

• dataGrid.DataSource
• dataset.DefaultViewManager
• dataGrid.DataSource
• dataset.Tablestablename".DefaultView

19
DataGrid Control(2)

• dataset.Relations.Add
• ("CategoryProducts",
• ds.Tables"category".Columns"catID",
• ds.Tables"product".Columns"catID")
• //(relationship name,
• Parent column name,
• Child column name)

20
Parameterized query(1)

• Construct the SqlCommand command string with
  parameters.
• Declare a SqlParameter object, assigning values
  as appropriate.
• Assign the SqlParameter object to the SqlCommand
  object's Parameters property.

21
Parameterized query(2)

• str "update tblCategory
• set catdesc _at_newname
• where catdesc _at_oldname"
• SqlCommand cmd
• new SqlCommand(str, Conn())
• cmd.Parameters.Add(new SqlParameter("_at_newname",new
  name))

22
Stored Procedure(1)

1. A pre-defined, reusable routine that is stored in
   a database.
2. Accept input parameters and return multiple
   values.
3. Reduced client/server traffic.

23
Stored Procedure(2)

• CREATE PROCEDURE
• procedure_name(_at_parameter_name
• as datatype)
  AS
• Insert into tblcategory(catdesc)
• values(_at_catDesc)
• // SQL Query
• GO

24
Stored Procedure(3)

1. create a command object identifying the stored
   procedure.
2. set the command object so it knows to execute a
   stored procedure.
3. add parameter to command, which will be passed to
   the stored procedure.

25
Stored Procedure(4)

• SqlCommand cmd
• new SqlCommand("cateIns", Conn() )
• cmd.CommandType
• CommandType.StoredProcedure
• cmd.Parameters.Add
• (new sqlParameter("_at_catDesc",
• SqlDbType.VarChar,50,"catDesc"))
• cmd.Parameters0.Value newname