Transcript ADO. NET - University of Bridgeport

ADO. NET
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.
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
Process of using DB (1)
1.
2.
3.
4.
5.
6.
7.
8.
Import Namespace
Make Connection
Open Connection
Send Query
Execute Query
Store result
Populate data in UI
Close connection
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(reader[1].ToString());
}
conn.Close();
Connection(1)

Connection String
1.
MS-SQL
string connstr =
"server=SERVERNAME;uid=USERID;pwd=PASSWORDr;databa
se=DBNAME;";
string connstr =
"server=rainnysea;uid=testuser;pwd=testuser;database=Testst
d;";
2.
MS-Acess
 string connstr = "Provider=ProviderName&Version;Data
Source=FileNameOnServer";
 string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\cs440\\Test\\data\\xyz.mdb;";
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( );
}
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.
Command(1) –

ExecuteScalar()
ExecuteScalar()
SqlCommand cmd =
new SqlCommand(qry, this.con );
Object obj = cmd.ExecuteScalar();
// returns one row
Command(2) –

ExecuteReader()
ExecuteReader()
SqlCommand cmd =
new SqlCommand(sql,conn);
SqlDataReader reader = cmd.ExecuteReader();
Command(3) –

ExecuteNonQuery()
ExecuteNonQuery()
SqlCommand cmd =
new SqlCommand(sql,conn);
int cntrows = cmd.ExecuteNonQuery();
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.
Data Stored Object(2)
DataReader
Data Reader
Data Source
Application
DataSet
Application 1
Data
Data Source
Adapter
Data Set
Application 2
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 readonly type of data.
The DataSet class can further use DataTable,
DataRow, DataColumn, DataRelation and
Constraint classes to define its offline data.
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.
DataSet(1)
DataSet DS = new DataSet();
SqlDataAdapter Adapter =
new SqlDataAdapter(qry, conn);
Adapter.Fill(DS, tblName);
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.
DataGrid Control(2)

dataGrid.DataSource =
dataset.DefaultViewManager;

dataGrid.DataSource =
dataset.Tables[“tablename"].DefaultView ;
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)
Parameterized query(1)
1.
2.
3.
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.
Parameterized query(2)
str = "update tblCategory
set catdesc = @newname
where catdesc = @oldname";
SqlCommand cmd =
new SqlCommand(str, Conn());
cmd.Parameters.Add(new
SqlParameter("@newname",newname));
Stored Procedure(1)
1.
2.
3.
A pre-defined, reusable routine
that is stored in a database.
Accept input parameters and
return multiple values.
Reduced client/server traffic.
Stored Procedure(2)
CREATE PROCEDURE
procedure_name(@parameter_name
as datatype) AS
[ Insert into tblcategory(catdesc)
values(@catDesc)]
// SQL Query
GO
Stored Procedure(3)
1.
2.
3.
create a command object identifying
the stored procedure.
set the command object so it knows
to execute a stored procedure.
add parameter to command, which
will be passed to the stored
procedure.
Stored Procedure(4)
SqlCommand cmd =
new SqlCommand("cateIns", Conn() );
cmd.CommandType =
CommandType.StoredProcedure;
cmd.Parameters.Add
(new sqlParameter("@catDesc",
SqlDbType.VarChar,50,"catDesc"));
cmd.Parameters[0].Value = newname;