ADO.Net, ASP.
Net and SQL Server
IT 4203 Advanced Web Development
Jack G. Zheng Fall 2010
Data Access: the Big Picture
Applications Data Logic Frameworks Windows Apps
Data access API
Data Sources
Relational Database
Standard data access API
XML Data
ASP.Net Apps
Native APIs
Directory Data
PHP Apps
Direct raw data access
Spreadsheet Data
Accessing Relational Database
Windows Applications
DBMS (SQL)
Data access APIs: Standard: OLEDB, ODBC, JDBC; Native API
Databases
Web Applications
Drivers: Native ODBC JDBC OLEDB
Databases
ASP.Net Applications
Databases
Standard Data Access APIs
ODBC: Open Database Connectivity
ODBC is an uniform interface that allows applications to access data from a variety of relational Database Management Systems (DBMS).
OLEDB: Object Linking and Embedding for Databases
OLE DB is a comprehensive set of COM interfaces for accessing a diverse range of data in a variety of data stores. Designed as a higher-level replacement for ODBC
JDBC: Java Database Connectivity
4
Data Access for .Net Applications
See Microsoft Data Development Technologies
http://msdn.microsoft.com/en-us/library/ee730343.aspx
5
ADO.Net
ADO.NET is a set of classes that expose data access services for .NET applications. ADO.NET provides consistent access to data sources such as SQL Server, Oracle, XML, and to data sources exposed through OLEDB and ODBC.
ADO.NET Architecture
ADO.Net Architecture
http://msdn.microsoft.com/en-us/library/27y4ybxw(v=VS.90).aspx
7
.Net Data Providers
A .NET Framework data provider is used for connecting to a database, executing commands, and processing results. .NET Framework data providers include:
Data Provider for SQL Server Data Provider for OLE DB Data Provider for ODBC Data Provider for Oracle EntityClient Provider
.NET Framework Data Providers (ADO.NET)
http://msdn.microsoft.com/en-us/library/a6cd7c08(v=VS.90).aspx
.NET Data Provider for SQL Server
The .NET Framework Data Provider for SQL Server (System.Data.SqlClient) uses its own protocol to communicate with SQL Server.
It is optimized to access a SQL Server directly without adding an OLE DB or ODBC layer.
SQL Server for .Net Applications
.Net Applications
ASP.Net Applications
ADO.Net Data Provider for OLEDB
ADO.Net Data Provider for SQL Server
OLEDB
Windows Applications
SQL Server DBMS
Databases
Databases
10
Using Database for .Net Apps
Steps to access a database
Base Class Description
Step
Connecting Establishes a connection to a to database Connection specific data source. Executes a command against a data source. Exposes Parameters and can Preparing execute in the scope of a commands Command Transaction from a Connection. Processing Reads a forward-only, read-only results DataReader stream of data from a data source.
11
A Quick Database Query Example
Step 1: Use SqlConnection class to establish a connection to an SQL Server database. A connection string is supplied.
SqlConnection con = new SqlConnection("Server=(local); Database=books; User ID=jack; Password=zheng");
SqlCommand cmd = new SqlCommand("select top 20 * from books"); cmd.Connection = con; con.Open(); SqlDataReader data = cmd.ExecuteReader(); while (data.Read())
{ Response.Write("<p>"+data["BookTitle"]+", $"+ data["ListPrice"]+"</p>"); }
Column name
12
Step 2: Build a new command with simple and static SQL statement. Associate the command with the connection.
Step 3: Open the connection and execute the command. Use a DataReader to process the result.
con.Close();
Connection
Connection strings specify how to connect to a SQL Server database
Server: we can use IP, domain, or (local) if SQL Server is on the same server. Add instance name if necessary; for example, use (local) \sqlexpress for SQL Server Express edition.
"Server=(local); Database=books; User ID=jack; Password=zheng";
Name of the SQL Server database
SQL Server user login id and password.
More about connection strings:
http://msdn.microsoft.com/en-us/library/ms254978(v=VS.90).aspx
The Connection String Reference
http://www.connectionstrings.com/
13
Command
Simple SQL command text
Supply SQL statements as the command text
SqlCommand cmd = new SqlCommand("select top 20 * from books"); cmd.Connection = con;
Dont forget the association.
Dynamic command text
Command text can be an expression with constant strings and variables.
int number = 10; SqlCommand cmd = new SqlCommand("select top "+number+"* from books"); cmd.Connection = con; Use variables for any part of SQL statements: fields,
where clause, comparison criteria, sorting type, etc.
14
Command Execution
Depending on the command type and return value, four methods are provided for a command object.
Methods
ExecuteReader()
Return Value
Returns a DataReader object.
ExecuteScalar()
ExecuteNonQuery()
Returns a single scalar value.
Executes a command that does not return any rows.
ExecuteXMLReader() Returns an XmlReader. Available for a SqlCommand object only.
15
Command Parameters
Using parameters is a better practice than using variables.
Parameters are restricted to type, size, and other constraints defined for the column value. Its a way to validate user input.
Use @ to define a parameter where a value is supplied.
Example
SqlCommand cmd = new SqlCommand(); cmd.CommandText="select top 10 * from books where ListPrice<@price; cmd.Connection = con; SqlParameter sqlpara = new SqlParameter("@price", price); cmd.Parameters.Add(sqlpara);
Dont forget to add the parameter to the command.
Learn more here:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
16
DataReader
Getting values of each row
Use Getter methods: GetInt32(), GetString(), etc. Use collections: reader[1], reader["BookTitle"]
Use ExecuteScalar if only
SqlDataReader data = cmd.ExecuteReader(); one value is returned. while (data.Read()) The Read() method will retrieve one row at a time. { Response.Write("<p>"+data[1]+", $"+ data["ListPrice"]+"</p>"); } Use either column index number or column con.Close(); name to get a value for the current row.
More examples
http://msdn.microsoft.com/en-us/library/haa3afyz(v=VS.90).aspx
17
Database Modification
Use ExecuteNonQuery() method for SQL UPDATE, INSERT INTO, and DELETE
FROM statements
Example
Connection is the same; command is an Update SQL.
SqlCommand cmd = new SqlCommand(); cmd.CommandText = "update books set listprice = @price where bookid = 1"; cmd.Connection = con; SqlParameter sqlpara = new SqlParameter("@price", Double.Parse(this.txtNewPrice.Text)); cmd.Parameters.Add(sqlpara); con.Open(); int result = cmd.ExecuteNonQuery(); con.Close();
ExecuteNonQuery() retuens an integer indicating number of results affected. If it is 0, very likely the execution failed.
if (result == 1 ) lblBook1.Text = "Update succeed"; else lblBook1.Text = "Update failed";
18
DataSet
The DataSet object is a disconnected, in-memory representation of a consistent relational data model.
More about DataSet, DataTable, and DataView
http://msdn.microsoft.com/en-us/library/ss7fbaez(v=VS.90).aspx
19
Populating DataSet from Database
Example: use DataAdapter to fill a DataSet
SqlConnection con = new SqlConnection("Server=(local); Database=Books; User ID=jack; Password=zheng"); SqlCommand cmd = new SqlCommand("select top 10 * from books"); cmd.Connection = con; Connection and command
remain the same.
SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds, "Books"); The fill method will automatically create
the default table structure and fill the data.
foreach (DataRow dr in ds.Tables["Books"].Rows) { Response.Write("<p>"+dr[2]+", $"+ dr["ListPrice"]+"</p>"); }
Read each row in the data table, referring values by number index or column name.
More examples about populating DataSets
http://msdn.microsoft.com/en-us/library/bh8kx08z(v=VS.90).aspx
20
Reading Data from a DataSet
Use a DataView object to further manipulating the data in a DataSet
SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); Get a default view of all data. sda.Fill(ds, "Books"); DataView dv = ds.Tables[0].DefaultView; Apply sorting and filtering constraints. The
dv.Sort = "ListPrice desc"; dv.RowFilter = "BookTitle like '%system%'";
format of these constraints resemble SQL ORDER BY and WHERE clauses.
for (int i = 0; i < dv.Count; i++ ) Response.Write("<p>" + dv[i][2] + ", $" + dv[i]["ListPrice"] + "</p>");
Use a 2-D array like format to refer to the data. The first index is the row number and the second one is the column number or name.
Count returns the number of records after filtering.
Manipulating data in a DataTable
http://msdn.microsoft.com/en-us/library/tzwewss0(v=VS.90).aspx http://msdn.microsoft.com/en-us/library/fdcwwhez(v=VS.90).aspx
21
More about DataView
DataReader or DataSet
DataReader returns data in a forward-only, read-only manner.
Fast processing, improving application performance.
Use a DataSet to do the following:
Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice. Interact with data dynamically such as binding to a web server control or combining and relating data from multiple sources. Remote data between tiers or from an XML Web service. Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.
22
Summary
Key Concepts
Database access API Standard database access APIs: OLEDB, ODBC, JDBC ADO.Net .Net data provider Connection, Command, DataReader, DataSet, DataAdapter
Key skills
Use SQL Server data provider to access SQL Server databases. Send SQL queries (SELECT, UPDATE, INSERT INTO) and process results. Manipulate DataReader and DataSet objects.
23
Key Resources and References
ADO.Net
http://msdn.microsoft.com/en-us/library/e80y5yhx(v=VS.90).aspx
Retrieving and Modifying Data in ADO.NET
http://msdn.microsoft.com/en-us/library/ms254937(v=VS.90).aspx
DataSets, DataTables, and DataViews
http://msdn.microsoft.com/en-us/library/ss7fbaez(v=VS.90).aspx
Microsoft Data Development Technologies: Past, Present, and Future
http://msdn.microsoft.com/en-us/library/ee730343.aspx
Microsoft Data Access Technologies Road Map
http://msdn.microsoft.com/en-us/library/ms810810.aspx
24
