Server-side data access

Access to | server | Data server-side data access

Server-Side Data introduction
connections, commands, and datasets
Access to sql-based data
Binding SQL data to a DataGrid
Perform a parameterized selection
Inserting data into a SQL database
Updating data in a SQL database
To delete data from an SQL database
Sort data in an SQL database
Handling Master-from relationships
Writing and using Stored procedures
Access to xml-based data
Summary of this section



Server-Side Data introduction
Data access is a central part of any real application, and ASP.net provides a rich set of controls that are well integrated with the managed data access APIs provided in the common language runtime. This section walkthroughs the same example several times, using the ASP.net DataGrid control to bind to the results of the SQL query and the XML data file. This section assumes that you are familiar with database basics and SQL query language.
Server-side data access is unique because WEB pages are essentially stateless. This presents some difficult challenges when attempting to execute a transaction, such as inserting or updating a record in a dataset retrieved from a database. As you will see in this section, the DataGrid control can help meet these challenges, allowing you to focus more on application logic, with no regard for the specifics of state management and event handling.



connections, commands, and datasets
The common language runtime provides a complete set of managed data access APIs for data-intensive application development. These APIs help abstract data and represent data in a consistent way, regardless of the actual data source (SQL Server, OLE DB, XML, and so on). The most commonly used objects are basically three types: connections, commands, and datasets.
A connection represents a physical connection to some data store, such as a SQL Server or an XML file.
command represents an instruction to retrieve (select) from the data store or manipulate (insert, UPDATE, delete) a data store.
A dataset represents the actual data used by an application. Note that datasets are always disconnected from their source connections and data models and can be modified independently. However, changes to the dataset can be easily reconciled with the starting data model.
For a more detailed walkthrough of a managed data access solution in the common language runtime, please read the Ado.net overview section of this tutorial.

Access to sql-based data
Applications typically need to perform one or more SELECT, INSERT, UPDATE, or delete queries against the SQL database. The following table shows an example of each of these queries.

Inquire
Example
Simple Choice
SELECT * from Employees WHERE FirstName = ' Bradley ';
Join selection
SELECT * from Employees E, managers M WHERE e.firstname = m.firstname;
Insert
INSERT into Employees VALUES (' 123-45-6789 ', ' Bradley ', ' Millington ', ' program Manager ');
Update
UPDATE Employees SET Title = ' Development lead ' WHERE FirstName = ' Bradley ';
Delete
DELETE from Employees WHERE productivity < 10;
In order for the page to access the classes needed to perform SQL data access, you must import the System.Data and System.Data.SqlClient namespaces into the page.

<%@ import namespace= "System.Data"%> <%@ import namespace= "System.Data.SqlClient"%>

To perform a select query on a SQL database, create a SqlConnection with the database, pass the connection string, and then construct the SqlDataAdapter object that contains the query statement. To populate a DataSet object with query results, call the Fill method for the command.

SqlConnection myconnection = new SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes "); SqlDataAdapter mycommand = new SqlDataAdapter ("select * from Authors", MyConnection); DataSet ds = new DataSet (); Mycommand.fill (ds, "Authors"); Dim MyConnection as New SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes ") Dim mycommand As New SqlDataAdapter (" select * from Authors ", MyConnection) Dim ds As New DataSet ( ) Mycommand.fill (ds, "Authors") var myconnection:sqlconnection = new SqlConnection ("server= (local) \netsdk;database= Pubs Trusted_connection=yes "); var mycommand:sqldataadapter = new SqlDataAdapter ("select * from Authors", MyConnection); var ds:dataset = new DataSet (); Mycommand.fill (ds, "Authors");
As mentioned earlier in this section, the benefit of using a dataset is that it provides you with a disconnected database view. You can manipulate the dataset in your application, and then reconcile the changes and the actual database at a later time. This is usually the best way to run a long-running application. For WEB applications, a short operation is usually performed on each request (typically just displaying data). Typically, you do not need to persist a DataSet object between a series of requests. For this type of situation, you can use SqlDataReader.
SqlDataReader provides forward-only read-only pointers to data retrieved from the SQL database. Because SqlDataReader uses tabular data stream (TDS) to read data directly from a database connection, it can perform more efficiently than a DataSet if it is available for scenarios.
To use SqlDataReader, declare SqlCommand instead of SqlDataAdapter. SqlCommand publicly returns the ExecuteReader method of SqlDataReader. Also note that when you use SqlCommand, you must explicitly turn SqlConnection on and off. After calling ExecuteReader, SqlDataReader can bind to the ASP.net server control, as will be seen in the next section.
<tab name= "C #" >
SqlConnection myconnection = new SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes "); SqlCommand mycommand = new SqlCommand ("SELECT * from Authors", MyConnection);

Myconnection.open ();


SqlDataReader dr = Mycommand.executereader ();

...

Myconnection.close ();
</Tab>

<tab name= "VB" >
Dim MyConnection As SqlConnection = New SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes ") Dim mycommand as SqlCommand = New SqlCommand (" SELECT * from Authors ", MyConnection)

Myconnection.open ()


Dim dr As SqlDataReader = Mycommand.executereader ()

...

Myconnection.close ()
</Tab>

<tab name= "JScript" >
var myconnection:sqlconnection = new SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes "); var mycommand:sqlcommand = new SqlCommand ("SELECT * from Authors", MyConnection);

Myconnection.open ();

var Dr:sqldatareader; Dr = Mycommand.executereader ();

...

Myconnection.close ();
</Tab>
SqlCommand is also used when executing commands that do not require the return of data, such as inserts, updates, and deletes. The command is emitted by calling the ExecuteNonQuery method, and this method returns the number of rows affected. Note When you use SqlCommand, you must explicitly open the connection; SqlDataAdapter automatically handles how to open the connection for you.
<tab name= "C #" > SqlConnection myconnection = new SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes "); SqlCommand mycommand = new SqlCommand ("UPDATE Authors SET phone=" (a) 555-5555 ' WHERE au_id = ' 123-45-6789 ' ", myconnecti ON);

MyCommand.Connection.Open (); Mycommand.executenonquery (); MyCommand.Connection.Close ();
</Tab>

<tab name= "VB" > Dim myconnection as New SqlConnection ("server= (local) \netsdk;database=pubs;" Trusted_connection=yes ") Dim mycommand as New SqlCommand (_" UPDATE Authors SET phone= ' (a) 555-5555 ' WHERE au_id = ' 123- 45-6789 ' ", _ MyConnection)

MyCommand.Connection.Open () Mycommand.executenonquery () MyCommand.Connection.Close ()
</Tab>

<tab name= "JScript" > var myconnection:sqlconnection = new SqlConnection ("server= (local) \netsdk;database=pubs; Trusted_connection=yes "); var mycommand:sqlcommand = new SqlCommand ("UPDATE Authors SET phone=" () 555-5555 ' WHERE au_id = ' 123-45-6789 ' ", myconn ection);

MyCommand.Connection.Open (); Mycommand.executenonquery (); MyCommand.Connection.Close ();
</Tab>

Important: Always remember to close the connection to the data model before the page finishes executing. If you do not close the connection, you may inadvertently exceed the connection limit while waiting for the page instance to be garbage collected.

Binding SQL data to a DataGrid
The following example shows a simple select query that is bound to the DataGrid control. The DataGrid renders tables that contain SQL data.