ASP. NET2.0 database entry for SqlDataSource

When selecting data using the SqlDataSource control, you can start with two properties: ConnectionString and SelectCommand, as shown below:

< asp: SqlDataSource ID = "MySourceControlName Runat" = "server

"

ConnectionString="Server=MyServer ;

Database=Northwind"

SelectCommand=" SELECT Fieldl, [Field With Space] FROM MyTable" >

< / asp: SglDataSource >

When using 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 to determine what information will be extracted from the SQL Server database. In SelectCommand, you can use any syntactically valid SQL SELECT statement, including those discussed in the previous chapter or in the appendix A. Many SQL Server administrators do not allow users to access tables directly. The database administrator will create some restricted permissions on SPROC. Alternatively, a view of the table is created to provide a portion of the table's data or to restrict the amount of data that can be modified in the table. The syntax for connecting to a view is as follows:

SelectCommand="SELECT * from MyView" >

If there is a space character in the name of a table, query, SPROC, or view, you should include the entire name with square brackets, as shown below:

SelectCommand="SELECT * from [My View] "

You may have noticed the Filter attribute in GridView and wondered how it differs from using the WHERE clause in the data source SelectCommand. Filtering is only used for certain buffer situations, which we will cover in chapter 15.

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

try # 1 -- SqlDataSource simple example

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

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

(2) create the folder ch03 and create a file named TIO-1-SqlSimple-1.ASPX. Display the toolbox via Menu: View|Toolbox(Ctrl+Alt+X). Note that the toolkit has an Data area that can be expanded.

(3) in the Design view, drag and drop an SqlDataSource control from the Data area of the toolkit 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 end. You will automatically return to the Data Source Configuration dialog box and click Next. For this example, there is no need to save the connection string in the configuration file; Click the 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 the addition of the DataSource control.

(4) add GridView data bound controls. In the handy task panel, select SqlDataSource, and then close the handy task panel. This creates a data-bound control. Save and run the page as follows:

< % @Page Language="VB" % >

"http: / / www. w3. org/TR/xhtmlll/DTD/xhtmlll dtd" >

< script runat="server" >

< / script >

The < html xmlns = "http: / / www. w3. org / 1999 / xhtml" >

< head runat="server" >

< title > Ch03-Tio # l-SqlSimple-verl < /title >

< / head >

The < body >

The < h2 >

Chapter 3 TIO #1 SqlSimple verl

< / h2 >

< form id="forml" runat="server" >

The < div >

< asp: SqlDataSource ID = "SqlDataSourcel Runat" = "server

"

ProviderName="System.Data.SqlClient"

ConnectionString="Server=(local)\SQLExpress;

Integrated Security=True;

Database=Northwind;

Persist Security Info=True"

SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM[Products]" >

< / asp: SqlDataSource >

< asp: GridView ID = "GridViewl Runat" = "server

"

DataSourceID="SqlDataSourcel"

DataKeyNames="ProductID"

AutoGenerateColumns = "False" >

The < Columns >

< asp: BoundField ReadOnly = "True HeaderText" = "ProductID

"

InsertVisible="False" DataField="ProductID"

SortExpression=" ProductID" > < /asp: BoundField >

< asp: BoundField HeaderText = "ProductName DataField" = "ProductName

"

SortExpression="ProductName" > < /asp:BoundField >

< asp: BoundField HeaderText = "UnitPrice DataField" = "UnitPrice

"

SortExpression="UnitPrice" > < /asp:BoundField >