I'll walk you through one way of creating a connection, returning a dataset and binding that dataset to a datagrid on a Web form. At the end, I'll give you some links for further reading. Examples are in VB.NET.
There are 3 important objects you will work with most often:
- A connection object represents a physical connection to some data store, such as SQL Server or an XML file.
- A command object represents a query or other command to retrieve or manipulate data ( an SQL statement ).
- A dataset object represents the actual data an application works with.
To give your page access to the classes you will need to perform SQL data access, you must import the System.Data and System.Data.SqlClient namespaces into your page or codebehind.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
To perform a select query to a SQL database, you create a SqlConnection to the database and pass it the connection string. Next you construct a SqlDataAdapter object that contains your query statement. To populate a DataSet object with the results from the query, you call the command's Fill method. Replace the server attribute in the following code with your database server (for a local SQL Server use (local) ) and the database with the name of your database.
Place the following in a server side script in your aspx or in the codebehind:
Sub Page_Load(Sender As Object, E As EventArgs) Dim MyDS As DataSet Dim Conn As SqlConnection Dim Cmd As SqlDataAdapter Conn = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("PubsString")) Cmd = New SqlDataAdapter("select * from Authors", MyConnection) MyDS = new DataSet() Cmd.Fill(ds, "Authors") AuthorGrid.DataSource=MyDS.Tables("Authors").DefaultView AuthorGrid.DataBind() End SubChange the connection string to match your database. Next place a Datagrid on the webform and name it "AuthorGrid". Make sure the attribute runat = server. Run the page and the datagrid will fill with data from the query.
There are other ways to do this (of course!). Here are some links for more info:
This was first published in September 2003