When using the SqlDataSource control to select data, you can start from two attributes: 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, used to determine what information will be extracted from the SQL Server database. In SelectCommand, you can use any SQL SELECT statement that complies with the syntax, including the statements discussed in the previous chapter or appendix. Many SQL Server administrators do not allow users to access tables directly. The database administrator will create some restricted licenses on SPROC. Alternatively, you can create a table view to provide part of the table data or restrict the data that can be modified in the table. The syntax for connecting to a view is as follows:
SelectCommand = "SELECT * from MyView">
If the table, query, SPROC, or view name contains space characters, you should use square brackets to include the entire name, as shown below:
SelectCommand = "SELECT * from [My View]"
You may have noticed the Filter attribute in the GridView and want to know what is the difference between it and the WHERE clause in the SelectCommand data source. Filters are only used for certain buffering conditions. We will introduce them in Chapter 15th.
With the connection string and SelectCommand, you can create a page for using data from SQL Server.
Try it #1 -- simple example of SqlDataSource
In this exercise, the product's GridView should be displayed from the SQL version of Northwind in the grid (table) format. You can start by adding the DataSource Control and Data Binding control, so that you can create the simplest source code. A faster development technique (drag-and-drop column name) will be used ).
(1) confirm that SSE has been installed, including the sample database Northwind, as described in chapter 1st. This exercise also involves SQL Server or MSDE.
(2) Create the folder ch03 and create a file named TIO-1-SqlSimple-1.ASPX in it. Use Menu: View | Toolbox (Ctrl + Alt + X) to display the Toolbox. Note that the toolbox has a Data area that can be expanded.
(3) In the Design view, drag and drop a SqlDataSource control from the Data area of the toolbox to the page. On the convenient task panel, configure the data source as a new connection. Enter the server name (local) \ SQLExpress and use Windows NT for authentication. Select a database named Northwind and test the connection. Click OK. You will automatically return to the Data Source Configuration dialog box and click Next. In this example, you do not need to save the connection string in the configuration file. 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. In this way, the DataSource control is added.
(4) Add the GridView data binding control. On the convenient task panel, select SqlDataSource and close the convenient task panel. In this way, a data binding control is created. Save and run the page as follows:
<% @ Page Language = "VB" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.1 // EN"
Http://www.w3.org/TR/xhtmlll/DTD/xhtmlll.dtd>
<Script runat = "server">
</Script>
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> Ch03-Tio # l-SqlSimple-verl </title>
</Head>
<Body>
<H2>
Chapter 3 TIO #1 SqlSimple verl
</H2>
<Form id = "forml" runat = "server">
<Div>
<Asp: SqlDataSource ID = "sql1_cel" 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"
Performanceid = "sql1_cel"
DataKeyNames = "ProductID"
AutoGenerateColumns = "False">
<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>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
