Transcript Visual Studio .NET And SQL Server 2000

ADO .NET And
Microsoft SQL Server
2000 www.infosupport.com
Agenda


ADO .NET
XML Support




Microsoft SQL Server 2000
Microsoft .NET Framework
Demo
Questions
ADO .NET




ADO .NET Overview
Making a Connection
Reading Data
Updating Data
ADO .NET Overview
DataSet
DataRow
Data Provider
DataTable
DataAdapter
DataReader
Command
Connection
General

The namespace System.Data:


The namespace System.Data.SqlClient:



Common objects e.g. DataSet
Classes for MS SQL Server 7 (and up)
SqlConnection, SqlAdapter, ...
The namespace System.Data.OleDb


Classes for OLEDB providers
OleDbConnection, OleDbAdapter, …
General

Do not use OleDb classes to connect to
a ODBC data source


Use ODBCConnection.
See:
http://msdn.microsoft.com/downloads/default.asp
?URL=/downloads/sample.asp?url=/MSDNFILES/027/001/668/msdncompositedoc.xml

Use IDbConnection and IDataReader
interfaces to create generic code for
different types of data sources
Code Example
try
{
Type t =Type.GetType( "System.Data.SqlClient.SqlConnection,System.Data,version
=1.0.3300.0,PublicKeyToken=b77a5c561934e089,Culture=neutral",true);
IDbConnection conn = (IDbConnection)Activator.CreateInstance(t);
conn.ConnectionString = " … ";
IDataReader reader=null;
conn.Open();
IDbCommand comm = conn.CreateCommand();
comm.CommandText = "select * from authors";
reader = comm.ExecuteReader();
while (reader.Read())
Console.WriteLine(reader.GetString(0));
}
catch(SystemException e)
{
Console.WriteLine(e.Message);
}
reader.Close();
conn.Close();
Making a Connection
DataSet
DataRow
Data Provider
DataTable
DataAdapter
DataReader
Command
Transaction
Connection
The Connection Object


Use to Connect to a data source
Can start a transaction
Properties


ConnectionString
State


Open, Closed, Broken, ...
Database

Currently opened database
Methods



Open()
Close()
BeginTransaction()

Returns a Transaction object
The Transaction Object

Use to commit or rollback a transaction
or to place a savepoint
Properties

Connection


The Connection object that started the
transaction
IsolationLevel

ReadCommited, Serializable, ...
Methods

Commit()


Rollback()


Commit the transaction in the data source
Rollback the transaction in the data source
Save()

Set a savepoint during a transaction
Code Example
// Create and open an SqlConnection
SqlConnection cnn = new SqlConnection(“Server=localhost;
Integrated Security=true");
cnn.Open();
// Optionally Start a Transaction
SqlTransaction txn = cnn.BeginTransaction();
// Do work…
if(TransferFunds(fromAct, toAct, Amount) == true) //
succeeded
txn.Commit();
else
txn.Rollback();
cnn.Close();
Reading Data
DataSet
DataRow
Data Provider
DataTable
DataAdapter
DataReader
Command
Connection
The DataReader Object

Use the DataReader to read DataRows

Forward only, Firehose cursor
Properties

Item

Collection of fields
Methods

Read()


Reads the next DataRow, returns false
when no rows are left
Get...




GetString()
GetDecimal()
GetBoolean()
Etc.
The Command Object

Use the Command Object to execute
SQL batches or Stored Procedures
Properties

CommandType


CommandText


Text, StoredProcedure and TableDirect
query, stored procedure name or table
Parameters

Collection of Parameters
Methods

ExecuteReader()


ExecuteScalar()


Returns a DataReader
Returns a single scalar
ExecuteNonQuery()


Returns the number of rows affected
Use for update and delete
Code Example
// Create a Parameterized SqlCommand
SqlCommand cmd = new SqlCommand(
"Select Desc, Amt from Activity where AccountID =
@A_ID", cnn);
cmd.Parameters.Add("@A_ID",accountID);
// Get back a DataReader
SqlDataReader results = cmd.ExecuteReader();
// Print out results
while(results.Read()) {
Console.Write("Description: " + results.GetString(0));
Console.WriteLine("Amount: " + results.GetDecimal(1));
}
Updating Data
DataSet
DataRow
Data Provider
DataTable
DataRow
DataAdapter
DataReader
Command
UpdateCommand
InsertCommand
DeleteCommand
SelectCommand
Connection
The DataAdapter Object

Holds Command objects to Select,
Update, Delete and Insert data

Update, Delete and Insert Command
objects can be generated by the
CommandBuilder
Properties




SelectCommand
UpdateCommand
DeleteCommand
UpdateCommand
Methods

Fill()


Executes the SelectCommand and stores
the data in a DataTable
Update()

Executes the appropriate commands to
update the datastore
The DataSet Object



Use as a disconnected recordset
Can contain multiple DataTables
Can store DataRelations


Used to relate DataTables
Holds schema information

Schema can be acquired design-time
(typed DataSet) or run-time
Properties

Tables


Relations


Collection of DataTables
Collection of Relations
EnforceConstraints
Methods

GetChanges


Merge


Merge two DataSets
GetXml


Get a DataSet with just the changed rows
Get the data from the DataSet in XML
format
GetXmlSchema

Get the XML schema from the DataSet
The DataTable Object


The DataTable contains the DataRows
It can enforce constraints

Unique, ForeignKey
Properties

Columns


Collection of DataColumns
Rows

Collection of DataRows
Methods

NewRow()


Rows.Add()


Creates a row
Adds a DataRow to the Rows collection
Select()

Creates a subset of the Rows collection
The DataRow Object


The DataRow contains the data
Keeps a status of the row


Deleted, modified, inserted and detached
Stores the original value and the new
value
Properties

RowState


Added, Modified, Detached, Deleted and
UnChanged
Item (indexer)

Collection of the data
Methods

AcceptChanges()


Changes the RowState to Unchanged and
overwrites the original values with the
current values
RejectChanges()

Changes the RowState to Unchanged and
overwrites the current values with the
original values
Code Example
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand delete = new SqlCommand("DeleteOrder",cnn);
delete.CommandType=CommandType.StoredProcedure;
delete.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
adapter.DeleteCommand = delete;
SqlCommand insert = new SqlCommand("AddOrder",cnn);
insert.CommandType=CommandType.StoredProcedure;
insert.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
insert.Parameters.Add("@CustD",typeof(Int32)).SourceColumn="CustomerID";
insert.Parameters.Add("@Date",typeof(DateTime)).Value = DateTime.Now;
adapter.InsertCommand = insert;
SqlCommand update = new SqlCommand("UpdateOrder",cnn);
update.CommandType=CommandType.StoredProcedure;
update.Parameters.Add("@OrderID",typeof(Int32)).SourceColumn="OrderID";
update.Parameters.Add("@CustD",typeof(Int32)).SourceColumn="CustomerID";
adapter.UpdateCommand = update;
adapter.Update(ordersTable);
Overview
DataSet
DataRow
Data Provider
DataTable
DataAdapter
DataReader
Command
Connection
XML Support


Microsoft SQL Server 2000
Microsoft .NET Framework
Microsoft SQL Server 2000

Querying data as XML

Directly retrieve data as XML


Load XML results into the dataset


Single trip for hierarchical data
Use XmlReadMode.Fragement
Diffgrams for sending back changes

DataSet Diffgrams supported by SQL
Server 2000 update as updategram
format
Code Example
// Create and open a SqlConnection
SqlConnection cnn = new SqlConnection(“…");
cnn.Open();
// Create and execute SqlCommand
SqlCommand cmd =
new SqlCommand("Select * from authors FOR XML AUTO", cnn);
XmlReader xr = cmd.ExecuteXmlReader();
// Read directly
// while(xr.Read()) {
//
…
// }
// Or load into DataSet
DataSet ds = new DataSet();
ds.ReadXml(xr,XmlReadMode.Fragment);
Microsoft .NET Framework

DataSet can read/write XML for its data and/or
schema



XML-related DataSet methods for reading:





ReadXml: Reads an XML schema and data into the DataSet
ReadXmlSchema: Reads an XML schema into the DataSet
And for writing:


You can create or modify data in a DataSet using XML
You can create or modify the DataSets schema using XML
WriteXml, WriteXmlSchema
GetXml, GetXmlSchema
Namespace property: sets the namespace for
serialization
Full support for SQL Server-style DiffGrams
Code Example
String oFile = “myXmlOutput.xsd”;
String iFile = “myXmlInput.xsd”;
// Write the DataSet’s XMLSchema to an XML Document
ds.WriteXmlSchema( oFile );
// Read/Upload XML Data into the DataSet
ds.ReadXml( iFile);
// modify the data
// ...
// Write the existing Data to an XML Document
ds.WriteXml( "myXmlData.txt",
XmlWriteMode.DiffGram);
Demo
Questions
?