Have you EVER ASKED THIS Question:
"How can I connect to A Database and Display IT IN A Web Page?"
Unfortunately, that's pretty much like asking: "What occupations are available for me in the world?" Well, it's not quite that bad, but there are a lot of different ways to do it First, let's break it down into two sections -. Connecting to and getting information from the database, and Displaying it in the web page. For each of those sections (which could easily have been broken down further), there are multiple possibilities, which this tutorial will at least partially address.
However, to make this merely a simple tutorial, instead of a book, hitting the highlights of the basic things necessary for Dispaying DataBase information on a web page, using ASP.Net. Also, the only databases covered here will be SQL Server and MS Access, The Only Language Covered Here Will Be VB.Net and We're Only Going To Address Select Queries, Leaving Out (Updates, DELETES AND INSERTS).
In Connecting to a Database, There Area SEVERAL ITEMS Which Are Necessary:
Namespaces Connection String Connection SQL Query (or Non-Query, In The Case of An Insert, Update or Delete) Opening The Connection Executing The Query Closing The Connection
Namespaces
For Both SQL Server and MS Access, Weimport The System.data Namespace. Separately, for SQL Server, Also:
<% @ Import namespace = "system.data.sqlclient"%>
For MS Access (or any OLEDB Compliant Database):
<% @ Import namespace = "system.data.oledb"%>
Connection String:
Of Course, Here We Also Have Multiple Possiblities, Depending on The Database. Here'S A Sample For SQL Server:
DIM STRCONN AS STRING = "Server = (local); UID = SA; PWD = PWD; Database = Northwind" and here "one for an access data:
DIM STRCONN AS STRING = "provider = microsoft.jet.Oledb.4.0; data source =" & server.mappath ("./ northwind.mdb") & ";"
CONNECTION:
THEN, USING THAT Connection String, WE NEED TO Define An Actual Connection Using The Connection String. First, Again, IS SQL Server:
DIM MyConn AS New SqlConnection (STRCONN)
And, Using MS Access:
DIM MyConn AS New OLEDBConnection (STRCONN)
SQL Query:
A Query Is Basically a Kind of Question, With Youngh You Address The Database To Say, "I'd LIKE (this Table)". In SQL, Which Stands for
Standard Query Language, this Translates TO:
"SELECT (List of Fields) from (TABLENAME)
Defined as:
DIM mysql as string = "SELECT (List of Fields) from (TableName)
Alternately, In SQL, You Can Narrow Down Your Search / Selection by Using A '
WHERE 'CLAUSE, ADDING A Specific Criteria with Which To Search The Database Table:
"
SELECT (TABLENAME) WHERE (Field Name) = (this filter)
Opening The Connection
Here's where it gets a little complicated for someone who's unfamilar with ASP.Net. There are two major ways to get information back from the database with ADO.Net (DataBase interaction with ASP.Net). One is with a DataReader and the other is a DataSet. The differences are boiled down, very simply, here. If you want to just get the information quickly and with as little overhead as possible and then display it, use a DataReader. If you have been in the Classic ASP world, it's just like a forward-only cursor for a recordset. Just remember, there are things you can not do with a DataSet, like Paging or Sorting with a DataGrid. Of course, this leads us to the other major way to get data from a database, the DataSet. When you use a DataSet, you query the database, and receive a result set that you can re-use as an entity in different ways. But, like was noted earlier, it uses much more overhead than a DataReader. As you can see, this is a very simplistic over-view of the datareader and Datas et and it's NOT meant to be, in any way, an in-depth technical presentation for them.DataReader: With a DataReader, there are two ways to use it for receiving data You can either read through it, line by line, manipulating. the data as it comes in, or you can merely bind it to a Server Control. One reason for using a DataReader and reading it line by line, is to query the database using a where clause, narrowing down your search, and then assigning the Field Names to Variables Here's Sample Code of How To Read The Data Line By Line, Doing Just That:
Dim Objdr As SqldataReader
DIM CMD AS New Sqlcommand (MySQL, MyConn)
Myconn.open ()
Objdr = cmd.executeReader (System.Data.commandbehavior.closeConnection)
While Objdr.Read () 'There Variables Would Natrually Need To Be Declared (DIM) Earlier.
intid = Objdr ("ID")
Strfirst = Objdr ("FNAME")
Strlast = Objdr ("Lname")
End while
The first line declares the variable for creating the DataReader. If you were using MS Access, it would be OleDbDataReader, instead of the SQL variety. Then, the second line declareas and creates a command, using the SQL query and the connection defined earlier by the code. Again, this would read OleDBCommand, instead of SQLCommand, if you were using MS Access, or any other OleDb compliant database. Next, we open the previously defined connection. The ExecuteReader line actually executes the command and puts the data into the DataReader. In the while / end while construction
<% # strlast%> />
The other way to use a DataReader is to first, open the connection (like listed above), and then bind the data to one of the server controls in ASP.Net, like a DataGrid, Repeater or DataList, in order to display it on The Page:
MyDataGrid.datasource = cmd.executeReader (System.Data.commandbehavior.CloseConnection)
MyDataGrid.Database
DataSet
Here's How To create a DataSet and Bind It to a Server Control:
DIM DS AS DATASET = New Dataset ()
DIM CMD AS New SqldataAdapter (MySQL, MyConn)
'OLEDB for the Above Line Would Naturally Be: DIM CMD AS New OLEDBDataAdapter (MySQL, MyConn)
CMD.Fill (DS, "Customers")
MyDataGrid.datasource = ds.tables ("Customers"). DefaultView
MyDataGrid.Database
Let's look at this line by line - the first line creates the new DataSet, and then we create the command with the SQL statement and the connection, but here, we assign it to a DataAdapter for use Next, we the DataAdapter with the DataSet. We created. here control, we just 'grab' the data desired from the database / table and kind of 'shove' it in a server control, in order to display it For a DataGrid, it's fairly simple, since it's so columnar, like a spreadsheet. - there are rows and columns, just like the data is stored in the database table. It can get more complex, of course, with a DataList, or Repeater. there is no way to go into all the different formatting techniques that are possible in this One Tutorial, But We Tried to Hit The 'Highlights', if You Will of Connecting to a Database, Re Trieving Information and the Displaying IT IN A Very Simple Manner.
If you have a need to display the information in a different format, that requires other code examples and tutorials. Be sure to look through all tutorials and code samples on this site to give you a better idea of how to format the data you retrieve from A Database. To See Complete Code Samples of Using Oledb and SQL Server, Along With DataReader and Dataset Presentation, Click ON this Code Sample
