Introduction to ASP.NET 2.0 SqlDataSource Control - What has been written for us by VS.NET 2005?
(Page 4 of 4 )
The ASP.NET code that has been written for us is as follows:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName],
[Title], [HomePhone] FROM [Employees]">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="HomePhone" HeaderText="HomePhone" SortExpression="HomePhone" />
</Columns>
</asp:GridView>
This is a declaration of the two controls we have used, the SqlDataSource control and the GridView control. Before we go any further let's see what other code has been written for us in the Web.Config file.
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
In the Web.Config file, and specifically in the <connectionStrings> section of that file, we have the connection string properties that we set using the Configure Data Source dialog of the SqlDataSource control that we added to the web page. The element <add> is used to add a new connection string to that section. You may have more than one connection string in your configuration file; it depends on your application needs, because you may need to store the database that contains your security data in a database that is separate from the application's database.
You have to give the connection string a name through the name attribute and define the connection string itself using the connectionString attribute. Also you need to provide the providerName attribute which is used by the SqlDataSource control to determine what ADO.NET Data Provider to use behind the scenes to create the connection, commands, data reader and data set objects. Again, don't worry if you don't know much about those objects because you can read about them in my ADO.NET articles.
The <SqlDataSource> element defines the SqlDataSource1 control that we have dragged on the page.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title], [HomePhone] FROM [Employees]">
</asp:SqlDataSource>
As you can see, the ConnectionString and the SelectCommand attributes are used to define the connection string, used internally by the SqlDataSource's connection object, and the T-SQL SELECT statement, which is used internally by the SqlDataSource's Command object, respectively. Note the syntax of the ConnectionString attribute, which is in the form of "<%$ ConnectionStrings:the name of the specific connection string element used %>. The control will retrieve the connection string from the configuration file Web.Config of the website using this syntax. We can also write the connection string directly into the control's ConnectionString attribute as in the following declaration:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName],
[Title], [HomePhone] FROM [Employees]">
</asp:SqlDataSource>
It's always best to place your connection strings in configuration files and access them from your class, as I explain in the ADO.NET 2.0 articles. The SelectCommand attribute's value is used by the control as the T-SQL SELECT statement that will be executed on the server. The result set returned from executing the query will be stored in a Dataset by default. I'll explain more about that in the next article. To display the returned data you need to add a GridView control and set its DataSourceID to the SqlDataSource control used as we did.
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
I know, there are a lot of concepts behind those controls which will be discussed in upcoming articles, so don't worry; you will soon understand everything regarding the SqlDataSource and the GridView controls.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
