Using ADO.Net Controls in ASP.Net Pages
One of the strengths of ASP.Net is its ability to integrate data tables in Web applications with very little code. In this tutorial, we will see how to add a table to a form, sort it and page through the data.
- What are Active Data Objects?
- Connecting to a SQL Server Database with C#
- Consuming Data in Windows Forms C# Applications
- Using ADO.Net Controls in ASP.Net Pages
- Modifying Data and Updating Databases with C#
- Connecting to MySql Databases with C# / ASP.Net
- Reading Excel Spreadsheets with C# ADO.Net
- Creating SQL Server Stored Procedures in C#
- Calling SQL Server Stored Procedures from C#
- Importing and Exporting XML from a DataSet
Adverts Blocked Please disable AdBlocking software and allow me to set cookies so that I can continue providing free content and services.
This article relates to an old version of the .Net Framework. While the contents are still relevant, future versions of the framework may change, or improve upon, the contents of this article.
Start off with a new ASP.Net website in Visual Web Developer or Visual Studio. You will need a connection to a database, if you don't have one you can download the sample Northwind Traders database files from the Microsoft Website.
The easiest way of adding a data table to a web form is to drag the table from the Database Explorer to the form. Visual Studio will do all the hard work and will present to you the table with all the columns created and spaced out. It will show the GridView tasks where you can enable Sorting, Paging, Editing, Selecting and Deleting. You can also change the look and feel of the table by changing the Style.
Drag the Products table to the web form. After a little bit of processing, it will show a default table with the correct headings and some sample data. From the Grid View Tasks select "Edit Columns". This will allow us to remove a few columns and reorder them. In the selected fields section, remove all columns apart from ProductID, ProductName, CategoryID and UnitPrice, then click OK.
Back on the Grid View Tasks, enable paging and sorting by ticking the appropriate boxes and then select Configure Data Source. This will allow us to change the SQL query that will select the records that are shown. In this example, we are only going to show records that are in stock and not discontinued.
On the first screen of the Configure Data Source wizard click next as we have already got a connection string. In the next screen untick the boxes that correspond with the headings removed in the previous steps. You should not SELECT fields unless you need them. You should just have ProductID, ProductName, CategoryID and UnitPrice selected.
Now click on the WHERE button and another screen will appear. In the column heading select Discontinued, operator set to =, source = None and in the parameter value section type in "false". Now click on add. This will construct the SQL query to exclude products that are discontinued.
Do the same thing, this time for products with no stock. When you have added the two queries click OK, then Next. In this screen, you can preview the query to see the data returned is correct and amend as necessary, then click Finish.
The Data Source Wizard can also be used to SELECT records that match the value of a form control, session value, and cookie value, profile value or from the QueryString parameter.
When you run the page and view it you will have a table presented with products that are in stock and not discontinued.
The Manual Way
The other way of performing the same task is to hand code the connection and query details. I feel this method gives me greater control and flexibility over the first method.
The method for connecting an ASP.Net control to a database is very similar to the method for connecting to a database using Windows forms.
All you need to do is add a grid view to the aspx page:
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
In the code behind this code will connect to the database and return a dataset that we use to display in the grid view.
string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:SQL Server 2000 Sample DatabasesNORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True"; string commandString = "SELECT * FROM Products"; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString); da.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind();
This code will display all fields and all records, but like the first example, we need to limit the data shown.
A quick modification of the QueryString is all that is required to get a subset of the data.
string commandString = "SELECT ProductID, ProductName, CategoryID, UnitPrice FROM Products WHERE UnitsInStock > 0 AND Discontinued = 'false'";
To enable paging and sorting you can either set the property from with the editor as described in the first method, or you can set the properties through C# code or you can hand code the elements of the Grid View.
GridView1.AllowPaging = true; GridView1.AllowSorting = true;
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"></asp:GridView>
Both code samples will have the same result.
Last updated on: Friday 23rd June 2017
There are no comments for this post. Be the first!