ASP. NET SqlDataSource Control

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.

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

Try it #1 -- SQL datasource fear simple example

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 the SqlDataSource control 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="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" 〉  
  
  
〈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 〉  
  
  
〈/Columns 〉  
  
  
〈/asp:GridView 〉  
  
  
〈/div 〉  
  
  
〈/form 〉  
  
  
〈/body 〉  
  
  
〈/html 〉 
 
 

(5) Close the browser and check the page in the Source view. Note that there are two controls in the center of the page <form>. SqlDataSource has ConnectionString and SelectCommand. The GridView has columns bound to fields of the SqlDataSource control.

(6) Now you can use Quick technology to add the second table. Save the page as a TIO-1-SqlSim-ple-2.ASPX. Switch to the Design view. In the menu, choose View> Database Explorer. Right-click Data Connections and choose Add Connection. As in the previous step, you will face the Connection Properties dialog box. Enter the server name (local) \ SQLExpress, use Windows NT Security, and select the database named Northwind. Test the connection and click OK to close the dialog box. Note the new items in the database browsing window.

(7) Expand the new (local) \ SqlExpress. Northwind. dbo connection and expand its table. Expand the Categories table. Use Ctrl + Click to select the CategoryID, CategoryName, and Description fields and drag them to the bottom of the page. Although the convenient task panel will open a new GridView for you, you can close it. Enter F5 to run the page. Scroll down to see the page that you just created by dragging and dropping column names from the Category table. The following are other source code of version 2. Note that when you drag and drop a column name, you will get a large set of code, including a series of parameters in the SqlDataSource control.

 
 

  
  
...  
  
  
 
  
  
〈asp:SqlDataSource ID="SqlDataSourcel"   
  
  
Runat="server" 
  
  
〈asp:GridView ID="GridViewl"   
  
  
Runat="server" 
  
  
...  
  
  
 
  
  
〈asp:GridView ID="GridView2" Runat="server" 
  
  
DataSourceID="SqlDataSource2"   
  
  
DataKeyNames="CategoryID" 
  
  
AutoGenerateColumns="False" 
  
  
EmptyDataText="There are no data   
  
  
records to display." 〉  
  
  
〈Columns 〉  
  
  
〈asp:BoundField ReadOnly="True"   
  
  
HeaderText="CategoryID" 
  
  
DataField="CategoryID" 
  
  
SortExpression="Category ID" 〉  
  
  
〈/asp:BoundField 〉  
  
  
〈asp:BoundField HeaderText="  
  
  
CategoryName"DataField="CategoryName"  
  
  
SortExpression="CategoryMame" 〉  
  
  
〈/asp:BoundField 〉  
  
  
〈asp:BoundField  
  
  
HeaderText="Description" DataField="Description" 
  
  
SortExpression="Description" 〉〈/asp:BoundField 〉  
  
  
〈/Columns 〉  
  
  
〈/asp:GridView 〉  
  
  
〈asp:SqlDataSource ID="SqlDataSource2" Runat="server" 
  
  
ProviderName="〈%$ ConnectionStrings:  
  
  
AppConnectionString2 . ProviderName % 〉"  
  
  
ConnectionString="〈%$ ConnectionStrings:  
  
  
AppConnectionString2 % 〉"  
  
  
SelectCommand="SELECT[CategoryID],  
  
  
[CategoryName] ,[Desc- ription] FROM 
  
  
[Categories]"  
  
  
UpdateCommand="UPDATE[Categories]SET 
  
  
[CategoryName]=@CategoryName,  
  
  
[Description] = @Description WHERE[CategoryID]=  
  
  
@original_CategoryID"  
  
  
InsertCommand="INSERT INTO[Categories]  
  
  
([CategoryName],[Description]  
  
  
VALUES (@CategoryName, @Description)"  
  
  
DeleteCommand="DELETE FROM [Categories]   
  
  
WHERE [CategoryID] =  
  
  
@original_CategoryID" 〉  
  
  
〈DeleteParameters 〉  
  
  
〈asp:Parameter Type="Int32"   
  
  
Name="CategoryID" 〉〈/asp:Parameter 〉  
  
  
〈/DeleteParameters 〉  
  
  
〈InsertParameters 〉  
  
  
〈asp:Parameter Type="String" 
  
  
Name="CategoryName" 〉〈/asp:Parameter 〉  
  
  
〈asp: Parameter Type="String"   
  
  
Name="Description" 〉〈/asp: Parameter 〉  
  
  
〈/InsertParameters 〉  
  
  
〈UpdateParameters 〉  
  
  
〈asp:Parameter Type="String"   
  
  
Name="CategoryName" 〉〈/asp:Parameter 〉  
  
  
〈asp:ParameterType="String"   
  
  
Name="Description" 〉〈/asp:Parameter 〉  
  
  
〈asp:Parameter Type="Int32" 
  
  
Name="CategoryID" 〉〈/asp:Parameter 〉  
  
  
〈/UpdateParameters 〉  
  
  
〈/asp:SqlDataSource 〉 
 
 

Example #1 -- SqlDataSource simple example

For syntax, SqlDataSource is similar to the AccessDataSource we use in Chapter 2nd. The biggest difference is the use of connection strings (rather than specifying data files ). Similar to the two, the knowledge of a control can help you understand other data source controls.

The connection string passes three parameters to SqlDataSource:

● SQL Server Name

● Name of the database or view to be used

● Indicates continued use of Windows Authentication during subsequent SSE Logon

For a table or view, if the name contains spaces, the name must be included in square brackets. The second parameter provides a selection command that complies with the standard SQL syntax.

When we move to version 2, we use the full functionality of VWD. By adding a connection to Database Explorer, we can let VWD understand the Database mode. VWD displays the table and column name. When you drag and drop a column name to the page, VWD creates a GridView containing the supported DataSource control.

1. Batch insert data to databases in ASP. NET
2. ASP. NET sends data to webpages in Post Mode
3. WEB Application Deployment in ASP. NET 2.0
4. HttpWorkerRequest object in ASP. NET
5. Introduction to ASP. net mvc Framework