ado.net data access with microsoft - tu wien · the role of ado.net •data access framework –...
TRANSCRIPT
ADO.NETData Access with Microsoft .NET
Andreas Schabus
Academic Relations
Microsoft Österreich GmbH
http://blogs.msdn.com/msdnat
Session Targets
• What is ADO.NET and how does it work?
• What are the differences to traditional, connected
data access methods?
• What are DataReaders and DataSets and when to use
what?
The Role Of ADO.NET
• Data access framework
– Database independent
– All types of applications
– Successor of class ADO
• Developed in .NET
– Language independent
– Application type independent
• Database independent
Changing Requirements
Different application types
• High scale web
– Always connected (?)
– Extreme performance
• Offline Smart Client
– Definitely not connected
High scale
web
Offline
Smart Client
Connected vs. Disconnected
• Disconnected model– Save resources
– Enable offline
• Connected model– Increase performance
– Easier programming model
• Solution: compromise– As few connections as possible
– Offline object / programming model
ADO.NET ARCHITECTURE
Understanding fundamental classes
The ADO.NET Model
• Get your data– Connect and retrieve
– Static forward / read only cursor
– No locking
• Work disconnected– XML support
– Similar to record set
• Update your data– Connect and execute SQL
ADO.NET Architecture
• Managed Provider
– Access to data source
– Data source classes
• Connection
• Command, Parameter
• DataReader
• Offline classes
– DataTable
– DataSet (n DataTables)
Data
Source
Application
Windows Forms, Web Forms,
Web Services
ADO .NET
KlassenDataset
Data
Reader
Managed Provider
SQL Server OLE DB…
ADO.NET Object Model
Connection
Command
Parameter
DataReader
Transaction
DataAdapter
DataSet
DataTable
DataColumn
DataRow
Constraint
DataRelation
.NET Data Provider
Database Support
• Basically independent– System.Data
– System.Data.Common
• Data providers out-of-the-box– System.Data.SqlClient
– System.Data.OracleClient
– System.Data.Odbc
– System.Data.OleDb
• Custom providers
Connection Class
• Base interfaces / classes
– IDbConnection
• Primary tasks
– Connect to database
– Start transactions
SqlConnection cn;cn = new SqlConnection("server=localhost;database=pubs");
cn.Open()'... Some tasks...
cn.Close()
Commands And Parameters
• Command base interface
– IDbCommand
• Primary tasks
– Encapsulate SQL command
• Text or Stored Procedure
– Manage query-parameters
– Execute SQL command
string strSQL = "SELECT * FROM titles"SqlCommand cmd = new SqlCommand(strSQL, Conn)
SqlDataReader reader = cmd.ExecuteReader()
Commands And Parameters
• Parameter base interface
– IDataParameter
• Primary tasks
– Parse & verify parameter
– Convert to database type
string strSQL = "SELECT * FROM titles WHERE name LIKE @val“;SqlCommand cmd = new SqlCommand(strSQL, Conn);
cmd.Parameters.Add("@val", SqlTypes.VarChar, 20);cmd.Parameters["@val„].Value = "Programming%„;
SqlDataReader reader = cmd.ExecuteReader();
DataReader Class
• Base interface
– IDataReader
• Primary task
– Read data from result-set
– Forward-only, read-only performance
SqlDataReader rdr As SqlDataReader = cmd.ExecuteReader();
While(rdr.Read()){
Console.WriteLine(rdr["OrderID“]);
Console.WriteLine(rdr[1]);
Console.WriteLine(rdr.GetString[2]);
}
rdr.Close();
Simple data access
THE OFFLINE MODEL
DataTables and DataSets
DataTable Class
• In-memory snapshot– Single table
– Scrollable, updateable
– Data source independant
• Schema information– Columns, column-types
– Rule constraints
• User data– Snapshot of database data
– DataRow class
DataSet Class
• Data container, too
– Multiple tables
– Relations between tables
• Schema information
– List of tables
– Relations between tables
• DataRelation
• Verified offline
• Enables navigation
Function of the DataAdapter
DataSet
Connection
DataAdapter
SelectCommand
InsertCommand
UpdateCommand
DeleteCommand
• Datencontainer
• verwendet intern XML
Mapping:
DataReader DataSet
DataAdapter Class
• Base interface, class
– IDataAdapter
– DataAdapter
• Primary tasks
– Execute SQL (read & update)
– Result-set DataTable / DataSet
SqlConnection cn = new SqlConnection("server=localhost;...")SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM titles", cn)
DataSet ds = new DataSet();da.Fill(ds, "Titles")
Processing Steps (1/2)
• Define Command
– Eventually create command-object
• Create DataAdapter
– Requires command & connection
• Create & fill DataTable / DataSet
– Instantiate DataSet
– Call DataAdapter.Fill()
– Optional – DataAdapter.FillSchema()
Processing Steps (2/2)
• Change data offline– Add rows (DataTable.NewRow())
– Delete rows (DataRow.Delete())
– Change rows
Row["Fieldname"] = value
• Update in database– Call DataAdapter.Update()
– All changes in DataTable / DataSet
– Changed records, only
DataAdapter und DataSets
Read via Fill() & FillSchema()
Change existing rows
SqlConnection cn = new SqlConnection("server=localhost;...");SqlAdapter da = new SqlDataAdapter("SELECT * FROM titles", cn);
DataSet ds = new DataSet();da.Fill(ds, "Titles");
DataRow row;
ds.Tables["Titles„].Rows(0)["name„] = "Neuer Name„;row = ds.Tables["Titles„].Rows(2)row.BeginEdit()row["name„] = "Advanced ADO.NET"row["Verlag„] = "Microsoft Press"row.EndEdit() 'or row.CancelEdit()
DataAdapter und DataSets
Add new rows
Delete existing rows
DataRow newRow;newRow = ds.Tables["titles„].NewRow();
newRow["name„] = "Applied ADO.NET Programming;newRow["Verlag„] = "Microsoft Press„;
ds.Tables.Rows.Add(newRow)
string filter = "PubDate <= '1/1/1995'„;
DataRow[] delRow();delRow = ds.Tables["titles„].Select(filter);
foreach( DataRow dr in delRow){dr.Delete()
]
DataAdapter and DataSets
• Creating necessary commands
– Via Designer (empfohlen)
– Dynamically: SqlCommandBuilder
• Via Get????Command() method
• Primary key in DataTable (FillSchema() !!)
• Execute the update
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM titles", cn);
SqlCommandBuilder cmdBuilder;cmdBuilder = new SqlCommandBuilder(da);
da.Update(ds)
DataView Class
• Like database view
– But offline
– On DataTable contents
• Primary tasks
– Filtering, sorting
– Intended for binding to controls!!
'...DataSet ds = new DataSet();da.Fill(ds, "Titles");
DataView dv = new DataView(ds.Tables["Titles"]);DataView dv = ds.Tables["Titles"].DefaultView;
Now you know…
• …what ADO.NET is and it works.
• …what are the differences between connected and
disconnected data access.
• …what DataReaders and DataSets are and how the
can be used?
Bibliographie
CodePlex. (21.02.2008). Microsoft SQL Server Community & Samples. Retrieved
March 09, 2008, from http://codeplex.com/SqlServerSamples
DevASP.Bet. Retrieved March 09, 2008, from
http://www.devasp.net/net/search/display/category74707.html
Microsoft Corporation. .NET Framework Developer's Guide: ADO.NET.
Retrieved March 09, 2008, from http://msdn2.microsoft.com/en-
us/library/e80y5yhx.aspx
Microsoft Corporation. Gewusst wie: Binden eines Windows Forms-
Steuerelements an ein Factoryobjekt. Retrieved March 09, 2008, from
http://msdn2.microsoft.com/de-de/library/at23yw2h(vs.80).aspx
Mössenböck, H., Beer, W., Prähofer, H., & Wöß, A. (09.02.2007). Application
Development with C# and .NET. Retrieved March 06, 2008, from
http://dotnet.jku.at/courses/dotnet/