Recently, I have a cold for a few days, and I am busy, plus the content of this chapter is more, so when I translate, the interval
Time is long.
Server-side data introduction
Data Access is the core content of the application in the real world. ASP.NET provides a rich set of controls, he is with CLR (
General Language Runtuances) The API (Application Interface) for managing data access is closely combined. This chapter is released
Several DateGrid controls that repeatedly use the ASP.NET to bind SQL query results and examples of XML data files. This chapter assumes
Learners are familiar with the database foundation and SQL query language.
Wincheer Note: Walk Through this word doesn't know how to translate it, he means in the computer
In the order design, organized discussions in a group in order to track the logic of the computer program.
Process.
Server-side data access is relatively unique, the reason is that the web page is idostatic. This leads to trying to perform transactions, such as
There are some difficult challenges when inserting or updating records. As you will see it in this chapter, DataGrid
Controls can help manage these challenges, allow you to concentrate more application logic and reduce event processing and status
Details of management.
Connections, Commands, and DataSets (Data Set)
Universal Language Runtime (CLR) provides an APIS for a set of management data access to enhance data application development
surroundings. These application interfaces are used to get and fill the data in a consistent way, regardless of the actual data source (
SQL Server, OLEDB, XML, etc.) The most commonly used three objects are Connections, Commands, and Datas
ETS.
Connection represents a physical connection to the data store, such as connecting SQL Server or XML files.
Command represents a command to obtain (Insert, Update, delete) data stored.
DataSet represents the actual data used to work. Note that DataSet is always connected to their data source connections and numbers.
According to the model, it can be modified independently. However, modifications to DataSet can be easily and the original number
According to the coordination of the model.
For more details on managing data access in general language runtime, see the ADO.NET overview.
Access SQL-based data
Applications often need to perform one or more SELECT, INSERT, UPDATE, or DELETE language for SQL databases
sentence. The following table shows some sample code that implements these functions.
Functional example
-------------------------------------------------- ----------------
Simple inquiry | SELECT * from Employees where firstname = 'bradley';
-------------------------------------------------- ----------------
Joint inquiry | SELECT * from Employees E, Managers M Where E.firstname = m.firstna
ME;
-------------------------------------------------- ----------------
Insert | INSERT INTO Employees Values ('123-45-6789', 'BRADLEY', 'MILLINGTON', 'P
Rogram Manager ');
-------------------------------------------------- ----------------
Update | Update Employees Set Title = 'Development LEAD' Where Firstname = 'Br
ADLEY ';
-------------------------------------------------- ----------------
Delete | Delete from Employees Where Productivity <10;
-------------------------------------------------- ----------------
To allow your page to access the SQL database, you must introduce System.Data and System.Data.sq in the page.
LCLIENT namespace:
In order to execute a SELECT query from the SQL database, you need to establish a SQLCON that connects to the database via the connection string.
The Nection object and then constructs a SQLDataAdapter object that contains query statements. In order to return the result of the query
Fill to the DataSet object, you need to call the Fill method for the SqlDataAdapter.
SqlConnection MyConnection = New SqlConnection ("Server = (local) / netsdk; database
= PUBS; Trusted_Connection = YES ");
Sqldataadapter mycommand = new sqldataadapter ("Select * from authors", MyConne
CTION;
DataSet DS = New Dataset ();
MyCommand.Fill (DS, "Authors");
As mentioned earlier in this chapter, the advantage of using DataSet is to provide a separate database view. You can
Operate the DataSet in the app, then your modification is consistent with the actual database. For long running applications
Program, because frequent reading data sources are avoided, it is the best way to handle. For web applications
It is often a short operation (usually just simple display data) to handle the client's request. under these circumstances
We can use SqldataReader to replace DataSet objects.
The SQLDataReader object provides a pointer that is only forward and read-only when data is obtained from the SQL database. Due to SQLD
Atareader objects use Table Fields (TDS) to read data directly from the database, so it is allowed
In the case of his performance efficiency is higher than DataSet.
When using the SqlDataReader object, you need to use SQLCommand to replace SqlDataAdapter. Sqlcomman
D Get the SqlDataReader object using the ExecuteReader method. Note that when using sqlcommand, it must
SqlConnection must be explicitly opened and closed. After calling the ExecuteReader method, the SqlDataReader object is
It can be bound to the ASP.NET server control as a data source. The next section will demonstrate this situation.
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 ();
SQLCO is also used when performing a SQL command that does not need to return data, such as INSERTS, UPDATES, and DELETES.
Mmand. This command is executed by calling the ExecuteNonQuery method, returning the number of rows that actually processed. Note using SQ
When LCOMMAND, you must explicitly open the connection; and SqlDataAdapter automatically opens.
SqlConnection MyConnection = New SqlConnection ("Server = (local) / netsdk; database
= PUBS; Trusted_Connection = YES ");
SQLCommand mycommand = new sqlcommand
"Update Authors Set Phone = '(800) 555-5555' Where au_id = '123-45-678
9'",
MyConnection);
mycommand.connection.open ();
mycommand.executenonquery ();
Mycommand.connection.close ();
Important: After the page is executed, you must remember to turn the connection to the data model to close. Otherwise, when waiting for garbage back
When you receive the function to process the page instance, you may consume the number of connections in an unwittime.
Bind SQL data to DataGrid
The following example shows the binding a simple query statement to the DataGrid control. DataGrid delivers a package
Table containing SQL data.
C # datagrid1.aspx
[Run] | [Source Code]
Similar data binds that the DROPDOWNLIST mentioned in that chapter supports IEnumerable or Icolle
The CTION type DataSource property is like DataSet. You can pass it (included in DataSet)
The DEFAULTVIEW attribute assigns the name of the table to the table to be used (DataSet), using the DataSet control,
The DEFAULTVIEW property represents the status of the current table in the DataSet, containing any changes made by the application code (for example
Drain or value changes). After setting the DataSource property, you can call DATABIND () to fill the control.
MyDataGrid.datasource = ds.tables ["authors"]. DefaultView;
MyDataGrid.databind ();
Another same syntax is to specify DataSource and DataMember. In this case, ASP.NET automatically gets you.
I took the defaultview.
MyDataGrid.dataSource = DS;
MyDataGrid.DataMember = "Authors";
MyDataGrid.databind ();
You can also bind directly to SqlDataReader. If you only display data, then SqlDataReader's "only
The front "feature is very suitable for this, and you can get higher execution performance.
C # dataGrid1.1.aspx [run] | [Source code]
Note: In this rest of this chapter, we are using DataSet's data access mode to demonstrate; in fact, this
Some examples can also be rewritten using SqlDataReader.
Perform the select command represented by parameter
You can also use the SqlDataAdapter object to perform a Select statement with parameters. The following example demonstrates how to use
The value passed by the SELECT HTMLControl control change the result of the query.
C # dataGrid2.aspx
[Run] | [Source Code]
SqlDataAdapter contains the parameters collection, you can use the variable identifier (add one "@") before you replace it.
value. You can add a new SQLParameter to specify the name, type, and size of the parameter.
Then set the value of his value attribute.
MyCommand.selectCommand.Parameters.Add (New SqlParameter ("@ State", SqldbType.nV
Archar, 2));
MyCommand.selectCommand.Parameters ["@ State"]. Value = MySelect.Value;
Important: Note that the EnableViewState property of DataGrid is the default settings of False. If you are in each page
Dump data, there is no need to let DataGrid save the status information sent by the form. Save it due to DataGrid
All status of all states, thus closing EnableViewState to increase page execution performance.
The above example static fills the data of the list box. If the value in the database has changed, this method is not
Can work very well. Since the list box also supports the IEnumerable DataSource property, you can use SEL
The ECT query is dynamically populated with the value of the list box, which ensures that the database and user interfaces are always consistent. Below
The child demonstrates this process.
C # datagrid3.aspx
[Run] | [Source Code]
Insert data in the SQL database
In order to insert a line record in the database, you can add an input form on the page and then submit in the form.
An insertion command is performed in the handle. Just like two examples above, you are using the parameter collection of command objects.
The value of the order. Before inserting the data, pay attention to check and make sure that the value obtained from the form cannot be empty, so that the number can be avoided.
According to the unexpected error of the library field constraint. In order to prevent the main index in the data table, the record is repeated, can be used
The TRY / CATCH statement block performs the insert command.
C # datagrid4.aspx
[Run] | [Source Code]
In addition to clearly checking the input data, you can also use the verification control mentioned in the previous section to check the data entry.
The following example shows you how to use this. Note that the regular expression verification control is checking the author ID, mail
The convenience of the political code and phone number and other fields.
C # dataGrid5.aspx
[Run] | [Source Code]
We will continue to introduce server-side data access in the next part. Please look forward to...
======== to be renewed ==================================================================================================================
