An introduction to the ASP.NET2.0 database SqlDataSource

When you use the SqlDataSource control to select data, you can start with two properties: ConnectionString and SelectCommand, as follows:

＜asp:SqlDataSource ID="MySourceControlName" Runat="server"
ConnectionString="Server=MyServer ;
Database=Northwind"
SelectCommand=" SELECT Fieldl, [Field With Space] FROM MyTable"＞
＜/asp:SglDataSource＞

When you use Windows authentication, you can add two authentication data.

＜asp:SqlDataSource ID="MySourceControlName" Runat="server"
ConnectionString="
Server=MyServer ;
User ID=MyID;
Password=Mypass;
Database=Northwind"
SelectCommand=" SELECT Field1, [Field With Space] FROM MyTable"＞
＜/asp:SqlDataSource＞

The first is the connection string discussed earlier, and the second is SelectCommand, which determines what information will be extracted from the SQL Server database. In SelectCommand, you can use any syntax-compliant SQL SELECT statement, including the statements discussed in the previous chapter or in Appendix A. Many SQL Server administrators do not allow users to access tables directly. The database administrator will create some restricted licenses on the sproc. Alternatively, the view that creates a table is used to provide part of the table's data or to restrict the data that can be modified in the table. The syntax for connecting to the view is as follows:

Selectcommand= "SELECT * from MyView" >

If you have a space character in the name of a table, query, sproc, or view, you should include the entire name with square brackets, as follows:

Selectcommand= "SELECT * from [my View]"

You may have noticed the Filter property in the GridView and wondered how it differs from using the WHERE clause in the data source SelectCommand. The filter is only used for certain buffering situations, which we will introduce in chapter 15th.

With the connection string and SelectCommand, you can create a page that uses data from SQL Server.

Try #1--sqldatasource A simple example

In this exercise, you should display the GridView of the product from the SQL version of Northwind in grid (table) format. You can start by adding technology to add datasource controls and data-bound controls so that you can create the simplest source code. A faster development technique will then be used (drag and drop column names).

(1) Please confirm that SSE has been installed (as described in chapter 1th), including sample database Northwind. This exercise will also involve SQL Server or MSDE.

(2) Create a folder ch03, in which a file named Tio-1-sqlsimple-1.aspx is created. Through menu:view| Toolbox (ctrl+alt+x) Displays the toolbox. Note that the Toolbox has a data area that can be expanded.

(3) In Design view, drag and drop a SqlDataSource control from the data area of the toolbox to the page. On the convenience task panel, configure the data source as a new connection. Enter the server name (local) \SQLExpress and use Windows NT authentication. Select the database named Northwind and test the connection. Click OK to finish. You will automatically return to the Data Source Configuration dialog box and click Next. For this example, you do not need to save the connection string in the configuration file, or click Next. Select Specify columns from a table and select the table name products. In the Columns list, click ID, name, and unit price. Click Next and Test Query, and then click Finish. This completes adding the datasource control.