Asp. NET hands-on Tutorials (9)

Server-Side Data introduction

Data access is the core content of applications in the real world. ASP.net provides a rich set of controls that combine tightly with the APIs (application interfaces) provided by the CLR (the common language runtime) to manage data access. This chapter provides several examples of Dategrid controls that repeatedly use ASP.net to bind SQL query results and XML data files. This chapter assumes that the learner is familiar with the database base and SQL query language.

Wincheer Note: Pre-row (walk through) the word does not know how to translate accurately, he means in computer programming, in a group of organized discussion, in order to the logic of computer programs to track the process of checking.

Server-side data access is unique because Web pages are stateless. This results in some difficult challenges when trying to execute a transaction, such as inserting or updating a record. As you will see in this chapter, the DataGrid control can help manage these challenges, allowing you to condense more application logic and reduce the details of event handling and state management.

connections (connections), Commands (commands), and Datasets (datasets)

The universal language Runtime (CLR) provides a complete set of APIs to manage data access to enhance the data application development environment. These application interfaces obtain and populate data in a consistent manner, regardless of the actual data source (SQL Server, OLE DB, XML, etc.) the three most commonly used objects are connections, commands, and datasets.

Connection represents a physical connection to a data store, such as a connection to a SQL Server or an XML file.

command represents the commands to obtain (select) or manipulate (insert, UPDATE, delete) data store.

A dataset represents the actual data that an application uses to work. Note that datasets is always separated from their data source connection and data model and can be modified independently. However, modifying the dataset makes it easy to reconcile with the original data model.

For more details on managing data access at the common language runtime, see the Ado.net Overview.

Access to sql-based data

Applications often need to execute one or more SELECT, INSERT, UPDATE, or DELETE statements on the SQL database. The following table shows some sample code to implement these features.

The following are referenced: features    examples ------------------------------------------------------------------ Simple Query | SELECT * from Employees WHERE FirstName = ' Bradley '; ------------------------------------------------------------------ Union Query | 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; ------------------------------------------------------------------

To enable your page to access the SQL database, you must introduce the System.Data and System.Data.SqlClient namespaces in the page:

<%@ Import namespace= "System.Data"%>

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

To execute a select query from a SQL database, you need to establish a SqlConnection object connected to the database through the connection string, and then construct a SqlDataAdapter object that contains the query statement. In order to populate the DataSet object with the returned results of the query, you need to invoke the SqlDataAdapter fill method.

The following are the referenced contents: 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");

As mentioned earlier in this chapter, the advantage of using a dataset is that it provides a detached database view. You can manipulate the dataset in your application and then align your changes with the actual database. For long-running applications, the best approach is to avoid frequent reading of data sources. For Web applications, the client's request is often handled with a short operation (usually simply a simple display of data). In this case, we can use SqlDataReader instead of the DataSet object.

The SqlDataReader object provides a forward-only, read-only pointer when data is obtained from an SQL database. Because the SqlDataReader object uses the table format data stream (TDS) to read data directly from the database connection, he performs more efficiently than the dataset when allowed to use.

When using SqlDataReader objects, you need to use SqlCommand instead of SqlDataAdapter. SqlCommand uses the ExecuteReader method to get the SqlDataReader object. Note that when you use SqlCommand, you must explicitly turn SqlConnection on and off. After the ExecuteReader method is invoked, the SqlDataReader object can be bound to the ASP.net server control as a data source. This is demonstrated in the next section.

The following are the referenced contents: 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 ();

SqlCommand is also used when executing an SQL command that does not need to return data, such as inserts, updates, and deletes. This command returns the number of rows actually processed by calling the ExecuteNonQuery method. Note that when you use SqlCommand, you must explicitly open the connection, and SqlDataAdapter automatically open the connection.

The following are the referenced contents: 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 '", MyConnection); MyCommand.Connection.Open (); Mycommand.executenonquery (); MyCommand.Connection.Close ();

Important: After the page finishes, be sure to close the data model connection. Otherwise, when you wait for the garbage collection to work on the page instance, you may inadvertently consume the limit of the number of connections.

Binding SQL data to a DataGrid

The following example shows the binding of a simple query statement to a DataGrid control. The DataGrid delivers a table that contains SQL data.

Similar to the DropDownList mentioned in the chapter on data binding, the DataGrid control supports IEnumerable or ICollection types of datasource attributes, just like a dataset. You can use the dataset control by assigning the DefaultView property of the table (contained in the dataset) to the name of the table in the dataset that you want to use, and the DefaultView property represents the state of the current table in the dataset. Contains any changes to the application's code (such as row deletion or value changes). After you set the DataSource property, you can call DataBind () to populate the control.

The following are the referenced contents: Mydatagrid.datasource=ds. tables["Authors"]. DefaultView; Mydatagrid.databind ();

The other same syntax is to specify both DataSource and DataMember. In this case, the ASP. NET automatically for you to get the DefaultView.

The following are the referenced contents: Mydatagrid.datasource=ds; Mydatagrid.datamember= "Authors"; Mydatagrid.databind ();

You can also bind directly to SqlDataReader. If you only show the data, then the SqlDataReader "forward only" feature is perfect for this occasion, and you can get higher performance.

Note: In the remainder of this chapter, we are using the dataset as a data access pattern to demonstrate; in fact, these examples can also be overridden using SqlDataReader.

Execute the SELECT command represented by a parameter

You can also use the SqlDataAdapter object to execute a SELECT statement with parameters. The following example shows how to use the value passed by a select HTMLControl control to change the results of a query.

SqlDataAdapter contains a parameters collection, you can substitute a value by using a variable identifier (a "@" before the name). You can add a new SqlParameter to the set to specify the name, type, and size of the parameter, and then set the value of his Value property.

The following are the referenced contents: MYCOMMAND.SELECTCOMMAND.PARAMETERS.ADD (New SqlParameter ("@State", SqlDbType.NVarChar, 2)); mycommand.selectcommand.parameters["@State"]. Value = Myselect.value;

Important: Note that the default setting for the EnableViewState property of the DataGrid is false. If you populate the data on every page request, there is no need to have the DataGrid save the status information sent by the form. Because the DataGrid holds all the state data it contains, closing the EnableViewState can improve performance on the page.

The above example statically fills the list box's data. This method does not work well if the values in the database have changed. Because the list box also supports IEnumerable DataSource properties, you can use a select query to dynamically populate the value of a list box so that the database and user interfaces are always consistent. The following example illustrates this process.

Inserting data into a SQL database

To insert a row of records in a database, you can add an input form to the page and then execute an INSERT command in the event handle of the form submission. Like the two examples above, you are populating the command's value with a command object's parameter collection. Before inserting the data, be careful to check to make sure that the values from the form cannot be empty, so that you can avoid unexpected errors in database field constraints. To prevent the primary index in the datasheet from repeating to the record you want to insert, you can use the Try/catch statement block to execute the insert command.

In addition to the explicit checking of input data, you can use the validation controls mentioned in the previous section to check data entry. The following example shows you how to use this. Note the regular expression validation control is handy when checking fields such as author IDs, postal codes, and phone numbers.