Asp.net| Program | data | Database ONE, INTRODUCTION
On November 7, 2005, Microsoft officially released. NET 2.0 (including asp.net 2.0), Visual Studio 2005, and SQL Server 2005. All of these components are designed to work together independently. In other words, ASP. NET version 1.x and version 2.0 can be installed on the same machine; you can have both Visual Studio.NET 2002/2003 and Visual Studio 2005, as well as SQL Server 2000 and SQL Server 2005. Also, Microsoft is publishing a Express SKU for Visual Studio 2005 and SQL Server 2005. Note that the Express Edition does not have all the features of the Professional edition.
2.0 in addition to supporting 1.x-style data access, it also includes new data source controls-which make it extremely easy to access and modify database data. When using these data source controls, you simply add the control to the ASP.net page and configure the connection string and SQL query with the properties of those controls. The data source control can then be bound to a data Web control, such as the GridView, by setting the DataSourceID property of the data Web control to the ID of the data source control. Today, it is time to write code to create a connection, create a command, specify a SQL query, retrieve a DataReader or DataSet, and bind it to a data Web control. These steps can be replaced by the use of declarative data source controls. (Of course, you can also access data programmatically by using familiar steps in your 1.x code.) )
In this article we'll look at how to connect and display data from a asp.net 2.0 database. Specifically, we will discuss how to use programmatic and declarative methods to access data, and analyze basic operations that display data through the GridView control.
Ii. manipulating Databases in Visual Studio 2005
When you install any version of Visual Studio 2005, you are always asked if you also want to install the SQL Server Express version. If you choose Yes, this will install the SQL Server Express version in your development package-Note that you are installing Visual Studio 2005! (SQL Server version of Express can be installed with other versions of SQL Server, including SQL Server 2000 and other versions of SQL Server 2005.) )
If you are using SQL Server 2000 in your current project, you can manipulate the database in the most comfortable way through Enterprise Manager. Although you can still use Enterprise Manager to manipulate SQL Server 2000, or use SQL Server 2005 's management Studio to manipulate your SQL Server 2005 database, you can also work with visual Studio 2005 of data connections to manage these databases. I mention this because there is no GUI tool such as SQL Server 2000 's Enterprise Manager or SQL Server 2005 Management Studio for SQL Server version of Express; instead, you have to go through visual Studio 2005来 creates and manages your SQL Server Express version database.
Prompt to manage SQL Server the Express version database
If you have any other SQL Server 2005 versions other than Express, you can install the client tools on your machine, including the GUI tools that manage the studio-SQL Server 2005 database. If you install this tool, you can also use it to manage the SQL Server Express version database.
To manage a database through visual Studio 2005来, select to Server Explorer, where you will find a data connection node (see the snapshot above). You can add a new database connection by right-clicking on the Data Connection node and choosing Add Connection. This will bring up a dialog box that prompts you for information such as database server, authentication information, what database to use, and so on. If you have a SQL Server Express version installed on your own machine, the database is installed by default by using an instance named SQL Express. Therefore, the service name to connect to is Yourmachinename\sql Express or. \sql Express. In addition to connecting to an existing database, you can create a new database by right-clicking on the Data Connection node and choosing to create a new SQL Server database.
Once a database is added to the Data Connection tab, you can add, delete, or modify data tables, stored procedures, views, and so on through the appropriate folders. To create a new datasheet or stored procedure, right-click the appropriate folder and select the Add New X menu option, and double-click to modify an existing datasheet, stored procedure, or view. This will call them into Visual Studio-where you can modify it as needed. You can also observe and modify the data in a single datasheet-this is done by right-clicking a data table name and choosing "Show data table data."
Third, add a database to the App_Data folder
In addition to adding an existing database through a data connection selection card, you can add a site-specific database to the App_Data folder of the site. App_Data is a new folder that remains in ASP.net 2.0-it is designed to store data-related content, including SQL database files (. mdf files), Access database files (. mdb files), XML files, and so on. From a asp.net website project, you can easily create and add a New SQL Server Express database to your project-by right-clicking the App_Data folder in Solution Explorer and selecting "Add Item". Then, from the Add New Item dialog box (shown below), select a new SQL database to add.
If you want to follow the steps in this article, create a SQL Server Express database, called Customers.mdf, under the App_Data folder. Then, add a datasheet to this database called customers-with the following field column: CustomerID (an automatically incremented primary key field), Name,city,state and ZipCode. Then, add some records to this datasheet through VS 2005.
In addition, you can add existing Access database files or even SQL Server 2000 database files. (Note: In order to add an existing SQL Server. mdf file, you will need to determine that it is the first time to detach from Enterprise Manager; For this reason, right-click the database name on Enterprise Manager, select All Tasks, and select Detach Database.) Once you have separated from the database and added it to the ASP.net 2.0 project, you can reconnect to it through Enterprise Manager. )
connect the SqlDataSource control to the database
Now that we've seen how to create and use the database through Visual Studio interfaces, let's turn our attention to accessing and displaying data from a database from a asp.net 2.0 Web page. ASP.net 2.0 includes several new data source controls-their sole purpose is to provide declarative access to data. There are five built-in data source controls-both of which can be found in the data section of the Toolbox in Visual Studio (see screenshot below).
· Sqldatasource-is used to retrieve and modify data from a relational database. "SQL" Here does not mean that this data source works only with Microsoft SQL Server, but can work with any other relational database: Sql server,access,oracle, and so on. If you are connecting to a SQL Server database, then the control will be very smart to enable the SqlClient class internally.
· Accessdatasource-is used to retrieve and modify data from an Access database file. You might want to know why this control exists if SqlDataSource can work with an Access database file. In fact, this Access data source control makes it easier for you to connect to an Access database-you simply specify the path to the. mdb file in the Access database. Note that when using SqlDataSource, you need to use a fully qualified connection string for the specified data provider.
· Objectdatasource-is used to retrieve and modify data through a business object. Ideally, your ASP.net application includes a set of classes-it forms the middle tier (rather than having the ASP.net page manipulate the database directly). If you have such a structure, you can use ObjectDataSource to query the middle tier.
· Xmldatasource-is used to retrieve data from an XML file.
· Sitemapdatasource-is used to provide read-only access to the site structure defined in the site map. This control is used when you want to display the structure of a site in a TreeView or Menu control.
In this article, we'll just discuss the SQL data source control and only analyze its basic functionality.
First, let's add a SQL data source control to your ASP.net page. From Design view, the SQL data source control contains a "sensitive tag"-it enumerates its common functions. The "Configure data source" sensitive tag link activates a wizard-it will step you through the entire configuration data source process. This wizard has three main steps (and an optional "sub step"):
1. Select your data connection-in the first step, we need to specify what database to connect to. This screen contains a drop-down list of databases in the App_Data folder and a drop-down list in the Data Connection tab. There is also a "New Connection" button-you can click on it if you want to connect to a database that is not in one of these locations. If you continue, you can select the Customers.mdf option from the Drop-down list box.
A) sub-step: If this is the first time you add a connection, you will be prompted whether you want to save the connection string to Web.config, and if so, the name of the connection string. If you go on, I choose to use the name customersconnectionstring to save the connection string to the web.config.
2. Set the SELECT statement-the next step is to specify what records you want to return, what conditional statements to apply, and the sort order in which the data is returned. To do this, you can select a datasheet or view by using a wizard and choose which column to return, or you can manually enter a SELECT statement. Either way, let the SQL data source control return all columns from the Customers datasheet-in other words, let it implement the "SELECT * from Customers".
3. Query test-You can run your select query against the database to get a preview of the returned data. Please click the "Test Query" button freely; you should see all the records from the customers datasheet.
Once you've configured SqlDataSource, take a moment to look at the source view and check the declarative markup used for the SQL data source control:
connectionstring= "<%$ connectionstrings:customersconnectionstring%>"
Selectcommand= "SELECT * from [Customers]" >
|
There are a few points to note: First, the ConnectionString attribute uses the new syntax <%$ ...%>. This syntax retrieves the customersconnectionstring value of the value of the connectionstrings section from the Web.config file. In other words, it gets the connection string from the Customers database-the string that we saved in the child step of the Wizard of the SQL data source control; Second, the SELECT query statement is specified in the control's SelectCommand property.
These data source controls themselves work only with data. They do not display data. If you access this asp.net page through your browser, you will find no output generated. To see the data returned by the SQL data source control We just created, we need to add a data Web control. For this article, let's use the GridView control-you can also find it in the data section of the Toolbox. The GridView is the "Big Brother" of the DataGrid control of ASP.net 1.x-it provides some new features. In this article, we only analyze the simple data binding to the GridView.
Now, let's drag a GridView control onto your asp.net page. From Design view, the sensitive label for the GridView includes a "Choose data source" option and has a drop-down list of all the data sources controls on the page. Setting this Drop-down list to sqldatasource1-is the ID of the SQL data source control that we just added. Once this is done, the GridView automatically has a boundfields added to it-for each column returned by the data source (the BoundField of the GridView is equivalent to the BoundColumn in the DataGrid). Setting the Choose data source Drop-down list by using the sensitive tag of the GridView sets the DataSourceID property of the GridView to the ID of the selected datasource control.
So much, no need to write a code! The following is a screenshot of the GridView when viewed through a browser.
By using the GridView and SQL data source controls, we are able to move from a blank page to a page that displays the contents of a database datasheet within 30 seconds. In fact, we can add pagination, bidirectional sort, delete, and edit support to the GridView in another 30 seconds, and now don't talk!
v. Chenglian received a database
As we saw earlier, retrieving information using a SqlDataSource is fast, but what if you want to access data programmatically? Or maybe you already have the code in place-the code will get the exact data you want and be managed as needed, and once it's done, modify the data you're about to display in a GridView. No problem, you can access data in 2.0 as in 1.x-by creating a connection to a database, creating a command, specifying a query, populating a DataReader or dataset, and then using the control's DataSource property and DataBind () method to bind the result to a data Web control.
The following code shows a Page_Load event handler for a ASP.net page that programmatically binds the contents of the Customers database data table to the GridView control Gvcustomers:
Protected Sub Page_Load (ByVal sender as Object, _
ByVal e as System.EventArgs) Handles Me.load
If not Page.IsPostBack Then
' Start by determining the connection string value
Dim connstring as String = Configurationmanager.connectionstrings (connstringname). ConnectionString
' Create a SqlConnection instance
Using MyConnection as New SqlConnection (connstring)
' Specify SQL query
Const SQL as String = "SELECT * from Customers"
' Create a SqlCommand instance
Dim mycommand as New SqlCommand (SQL, MyConnection)
' Retrieve a DataSet
Dim myDataSet as New DataSet
' Create a SqlDataAdapter query
Dim Myadapter as New SqlDataAdapter (mycommand)
Myadapter.fill (myDataSet)
' Bind the dataset to the GridView
Gvcustomers.datasource = myDataSet
Gvcustomers.databind ()
' Close the connection
Myconnection.close ()
End Using
End If
End Sub |
Here are some points to note: In order to get the connection string, we can refer to the connection string setting in Web.config-using syntax as
Configurationmanager.connectionstrings (Connstringname). ConnectionString. Where the value of the connstringname should be the value specified in the child step of the SQL Data Source Control Wizard (customersconnectionstring). Also note that Visual Basic 8 (VB version used in 2.0) now supports the Using keyword-a language feature that is only suitable for C # in the previous release.
Programmatically accessing the data and outputting the results is equivalent to using the SQL data source control.
Vi. Conclusion
In this article, we analyzed the case of using the ASP.net 2.0 database in Visual Studio 2005. We discussed how to manage the database through Visual Studio and how to add a site-specific database through the App_Data folder. Finally, we analyze how to access data using SQL data source controls and programmatically. When a data source control is used, the data is bound to a GridView control by setting the DataSourceID property of the GridView, and when the data is accessed programmatically, The data is bound to the GridView by assigning the data to the DataSource property of the GridView and the DataBind () method that invokes the GridView. (The latter approach follows the pattern used in ASP.net 1.x to bind data to a DataGrid.) Regardless of the method used to access the data, the final result, as seen in a user's browser, is the same.
