|
 Home > Article > ASP.Net :: Database Select Beginner
|
ASP.Net :: Database Select Beginner |
Abstract :
here how you can connect to database within ASP.Net Framework |
Views :
25884
Published :
Friday, February 22, 2002
By
HyperLink
|
|
When I started learning ASP.NET one of the first things I wanted to learn how to do was select records from a database. With ASP.NET it can be a little confusing to start, but once you see how it is done it becomes easy. We start with the normal ASP.NET page structure with a few extras.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.ADO" %>
<html>
<head>
</head>
<body>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
|
The <%@ Import Namespace %> statements allow us to use the database APIs in the core .NET framework.
Then the normal HTML tags.
The Sub Page_Load routine is what ASP.NET pages start out with. Each page needs one of these subroutines. This is what runs when the page loads. You can think of it kind of like the Main subroutine/function in other languages.
Next we will declare our variables, connect to the database and fill our DataSet.
Dim oConnection As ADOConnection
Dim oCommand As ADODataSetCommand
Dim oDS As New DataSet
Dim sConnString As String
Dim sSQL As String
Dim nCount As Integer
'Define connection string
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & Server.MapPath("\aspxexamples\database\test1.mdb")
'Define SQL statement
sSQL = "SELECT * FROM test1"
'Create the connection
oConnection = New ADOConnection(sConnString)
'Execute this SQL statement with this Connection String
oCommand = New ADODataSetCommand(sSQL, sConnString)
'Fill the DataSet and name the table
oCommand.FillDataSet(oDS, "TestTable")
|
First the variables are declared. Notice that in ASP.NET you now need to type your variables. This means that you need to tell the compiler what type of variable you want to work with.
Next we set the connection string and define our SQL statement. This is the same way we would do it in regular ASP.
The connection is then created by creating a new instance of the ADOConnection object and passing in our connection string.
We then create a new instance of the ADODataSetCommand object to execute the SQL command.
Now we need to fill a DataSet with the data returned from our SQL statement execution. The name that we give the table does not matter, here I have given it the name "TestTable." We will use this later to print out the data. oDS is the object that we are creating here to "house" the DataSet.
Next we will look at three different ways to get the data out of the DataSet object that we just filled.
DataGrid1.DataSource = oDS.Tables("TestTable").DefaultView
DataGrid1.DataBind()
Response.Write("<br><br><font face=Arial size=2><br><br>")
Dim tTable As DataTable
Dim rRow As DataRow
Dim cColumn As DataColumn
'This function loops through all the tables in our tables
'collection and then loops through each of those tables
'and prints out the columns.
For Each tTable in oDS.Tables
For Each rRow In tTable.Rows
For Each cColumn in tTable.Columns
Response.Write(rRow(cColumn.ColumnName) & "<br>")
Next cColumn
Next rRow
Next tTable
Response.Write("-------------------------------<br><br>")
'To get to a specific table and row we would do this
Response.Write(oDS.Tables("TestTable").Rows(0)("FirstName") & _
"<br><br>")
End Sub
</script>
<asp:DataGrid id="DataGrid1" runat="server"/>
</body>
</html>
|
This is where the fun begins. I am going to show you four ways to get the data and print it out: in a grid control, loop through the DataSet, column and row specific reference, and by looping through the rows in the specific table.
DataGrid
First we tell create the DataGrid control and tell it what data to put in it. The DataSource property is set to the default view of the table. We then bind the data to the DataGrid. This will look familiar to VB programmers. Notice further down that wherever we want that DataGrid to appear we need to put in the <asp:DataGrid> tag.
DataSet Loop
The outer For loop that you see loops first through all of the tables in our DataSet. Then for each table it finds, it loops through the records (or columns).
Column & Row Specific Reference
There are definitely times when you need to get to a particular column and row. We do this by referring explicitly to the table in the DataSet and then to the ordinal in the specific table that we want.
Loop Through Table
If we know the number of columns in our table we can loop through and print out exactly what we are looking for. This is simply a variation of the above code where we looped through the DataSet.
Complete Code
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.ADO" %>
<html>
<head>
</head>
<body>
<script language="VB" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
Dim oConnection As ADOConnection
Dim oCommand As ADODataSetCommand
Dim oDS As New DataSet
Dim sConnString As String
Dim sSQL As String
Dim nCount As Integer
'Define connection string
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & Server.MapPath("\aspxexamples\database\test1.mdb")
'Define SQL statement
sSQL = "SELECT * FROM test1"
'Create the connection
oConnection = New ADOConnection(sConnString)
'Execute this SQL statement with this Connection String
oCommand = New ADODataSetCommand(sSQL, sConnString)
'Fill the DataSet and name the table
oCommand.FillDataSet(oDS, "TestTable")
DataGrid1.DataSource = oDS.Tables("TestTable").DefaultView
DataGrid1.DataBind()
Response.Write("<font face=Arial size=2>")
Dim tTable As DataTable
Dim rRow As DataRow
Dim cColumn As DataColumn
'This function loops through all the tables in our tables
'collection and then loops through each of those tables
'and prints out the columns.
For Each tTable in oDS.Tables
For Each rRow In tTable.Rows
For Each cColumn in tTable.Columns
Response.Write(rRow(cColumn.ColumnName) & "<br>")
Next cColumn
Next rRow
Next tTable
Response.Write("-------------------------------<br><br>")
'To get to a specific table and row we would do this
Response.Write(oDS.Tables("TestTable").Rows(0)("FirstName") & _
"<br><br>")
End Sub
</script>
<asp:DataGrid id="DataGrid1" runat="server"/>
</body>
</html>
|
Code Result
john
doe
mary
jane
sam
smith
-----------------------------------------------------------------
john
-----------------------------------------------------------------
| FirstName | LastName | | john | doe | | mary | jane | | sam | smith |
|
|
|
|
|
 |
Yasir
Send Feedback
Yasir is a .NET expert, with over 5 years experience in Microsoft Technologies, 8 years overall programming experience, he is the owner, founder & primary contributor of Minwar.com, and he also works as IT Director in the hospitality industry.
|
|
|
|