ADO. NET - PowerPoint PPT Presentation

About This Presentation
Title:

ADO. NET

Description:

Although some of the concepts are similar to the classical ADO, there are some ... specifying parameter to a stored procedure. populating a Dataset. ... – PowerPoint PPT presentation

Number of Views:402
Avg rating:3.0/5.0
Slides: 26
Provided by: eunjeo
Category:
Tags: ado | net | kiwi

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com