Hello again. First I want to thank all the readers of my blog, who read my post and give comments on it. In my 1st post I discussed on how to retrieve data from a SQL Server database with in particular date range and bind that data in ASP.NET web form.
In this blog I am again going to discuss with that problem but now in a different way out. In my last post I used imperative programming. Now I am going to use declarative programming. I hope you have the interest to read on imperative programming and declarative programming more.
In ASP.NET there are many server side controls. These controls are very useful to create dynamic website. When a request is sent by a web browser for the particular web page then in the server side the page is dynamically created by the ASP.NET runtime engine and sent to the client end by web server. There are two types of server side control in ASP.NET. One type is HTML server control and second type is web server control. Again please read more on this subject if you have interest. I used some web server controls in my 1st post. Label, TextBox, GridView etc. In this post I will use some more.
To connect with database we can use many object of ADO.NET like SqlConnection, SqlCommand, DataTable etc. But from .NET Framework 2.0 Microsoft give a declarative approach to make the developer’s like more easy. They give some DataSource controls to work with database. This DataSource controls are web server controls and user the same ADO.NET objects under the hood. Also they fire some event in particular situation and developers can handle the events to inject more real world logic into their application.
There are 7 DataSource controls in ASP.NET 3.5 SP1. They are SqlDataSource, AccessDataSource, ObjectDataSource, XmlDataSource, SiteMapDataSource, LinqDataSource, EntityDataSource. SqlDataSource is used to work with Sql Server, Oracle, My Sql etc. AccessDataSource is especially for Microsoft office Access. ObjectDataSource is for business logic class. XmlDataSource is for xml file. SiteMapDataSource is for Web.sitemap file. LinqDataSource is for LINQ functionality and EntityDataSource is for Entity Framework functionality. I will use SqlDataSource because I am using Sql Server database. Please read some other tutorials for more information on the other types of DataSource controls provided by ASP.NET Framework.
DataSource controls acts like a bridge between DataBind control like GridView, ListView, Repeater etc and data source. It does not create any HTML in runtime for the web browser. That means client can not see these controls in their web browser. There job is to retrieve data from data source and supply them to data bind controls. Data bind controls are responsible to show data on the web page for the clients. If clients change some data on web pages in the data bind controls then changes are persist to the data source by the help of data source controls. So it is just a communication media for data source and data bind controls. We can configure data source controls declaratively in source view of aspx page or we can set their properties in properties window or we can use the wizard provided by the data source control. In the wizard we can design our Sql query visually or if we want we can write our query also. In this wizard we can set our input parameter combination for Sql query. Then we have to set the data bind control with data source control. All data bind controls have DataSourceId property. We can set the id of the data source control with DataSourceId property of the data bind control.
Ok lets create an web site to show all these practically. I am going to retrieve order data from Order table in Northwnd database which are in a particular date range. Also I am using VWD 2008 Express Edition and Sql Server 2008 Express Edition. Please read my 1st blog for the link to install these softwares.
Create a new website in VWD and give a name of your choice. Now right click on App_Data folder and add northwind database into your website. Double click on the Default.aspx and it will open in source view. Drag and drop two textbox, a button and a GridView on the source view of Default.aspx like our 1st lesson.
Go to design view of your web form. Select the GridView in design view and you can see there is a little handle at the right side of the GridView. Click the handle to open the task menu of the GridView. Now select ‘Add New DataSource’ option from ‘Chose data source’ drop down menu of the task menu of GridView. It will open a wizard. From the wizard you can configure your data source. In the 1st step select Database option. There are many other options, but as I have said we are using Sql Server that is why we are using SqlDataSource. Click ok. Then in the next step select the connection string. I described about connection strings in my 1st post. It holds all the information to connect and work with the data source. Click next. In this step it is recommended that you checked the checkbox and it will store the connection string in your web.config file and not in your source code. It is the better option to store the connection strings in the centralize location in configuration file for security reason and management reason. Click next. Now in this step you can select your database table from the drop down list and select the columns of the table. Select the Orders table from table drop down list. It will show all the columns of Orders table. Select only the columns you want to show in your web page. Now we have to create a filter here. The filter is we want to show all the Orders which fall in the particular date range. So click on the Where button to create a filter. It will open another window. In this window select the OrderDate from column drop down list. Then select >= from the operator drop down list and then select Control from source drop down list. Then click on the control id drop down list and you can see the all the control ids of this page. Select txtStartdate text box. Now click on add button. It will create the filter for you. Then we have to add another filter here for end date. Select OrderDate from the column drop down list, select <= from the operator drop down list, select control from source drop down list and select the txtEndDate text box from controlID drop down list. Click on add button. These two filters are added with your sql query with ‘and’ operator. If you want to create more complicated query then you can write your sql query manually from this wizard. So we have successfully created a SqlDataSource control which retrieves data from database in a particular date range. For the date range it will ask to the textboxes. In other words it will take the start date from the txtStartDate textbox and end date from txtEndDate textbox control. Now go to the source view of Default.aspx. You can see the entire markup generated by VWD. Notice the GridView with all the BoundFields in Columns tag.
<asp:GridView ID="grvOrders" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" > <Columns> <asp:BoundField DataField="OrderDate" HeaderText="OrderDate" SortExpression="OrderDate" /> <asp:BoundField DataField="ShipName" HeaderText="ShipName" SortExpression="ShipName" /> <asp:BoundField DataField="ShipAddress" HeaderText="ShipAddress" SortExpression="ShipAddress" /> <asp:BoundField DataField="ShipCity" HeaderText="ShipCity" SortExpression="ShipCity" /> <asp:BoundField DataField="ShipRegion" HeaderText="ShipRegion" SortExpression="ShipRegion" /> <asp:BoundField DataField="ShipCountry" HeaderText="ShipCountry" SortExpression="ShipCountry" /> </Columns> </GridView>
Then SqlDataSource. Notice that SqlDataSource do not holding the original connection string. It is holding a reference of the connection string which is in our web configuration file. In other words it referred the connection string from the centralized location of the site which is web.config file. Also notice the sql query and the parameters inside the SqlDataSource server control.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conNorthwnd %>" SelectCommand="SELECT [OrderDate], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipCountry] FROM [Orders] WHERE (([OrderDate] >= @OrderDate) AND ([OrderDate] <= @OrderDate2))"> <SelectParameters> <asp:ControlParameter ControlID="txtStartDate" Name="OrderDate" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtEndDate" Name="OrderDate2" PropertyName="Text" Type="DateTime" /> </SelectParameters> </asp:SqlDataSource>
Run the web site by pressing ctrl + F5 and give a start date and end date and then hit the button and you can see all the data (if any) inside the date range which you provide in the text boxes in the GridView. But you can not see any HTML for SqlDataSource control. Because it is data source control which is responsible for fetching the data from the database. Data bind control like GridView is responsible for showing the data coming from the database by data source control on your web page in the browser. You can also create, update and delete data by the help of data source control.
If you want to know more about ADO.NET data source and data bind controls then please refer to http://www.msdn.com.