Displaying DataBase Information
来源:互联网 发布:淘宝卖家流量包 编辑:程序博客网 时间:2024/05/19 21:18
"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 are 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
For both SQL Server and MS Access, we import 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's one for an Access Database:
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 which you address the database to say, "I'd like (this list of fields) from (This table)". In SQL, which stands for Standard Query Language, this translates to:
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 (list of fields) from (tablename)"Defined as:Dim MySQL as string = "Select (list of fields) from (tablename)"
"Select (list of fields) from (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't 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 DataSet 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:
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 construct, we merely assign the retrieved fields to the variables. Then, we can use the variables anywhere between the Body Tags, to display the information like this:Dim objDR as SQLDataReaderDim Cmd as New SQLCommand(MySQL, Myconn)Myconn.Open()objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)While objDR.Read()'These variables would natrually need to be declared (Dim) earlier.intID=objDR("ID")strFirst=objDR("Fname")strLast=objDR("Lname")End While
<%# 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:
DataSetMyDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)MyDataGrid.DataBind()
Here's how to create a Dataset and bind it to a server control:
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, we use 'Customers' as the name of the DataSet. Of course, to end up, in this case, we bind the DataGrid with the information we retrieved from the database.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").DefaultViewMyDataGrid.DataBind()
In short - in binding data to a server 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, retrieving information and then 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
- Displaying DataBase Information
- Oracle Database Information and Performance
- Database transaction information not available
- Database Components Information Table - DBA_REGISTRY
- Displaying image from database... (copy from ASP.NET Forums)
- about database design of classified information
- Getting Information about PeopleSoft Components from Database
- Displays information relevant to flashback database
- Retrieving CD Information from a remote Freedb database
- Query By Humming -- Musical Information Retrieval in an Audio Database
- How to Export and Import Oracle Database Statistics Information
- Information On Installed Database Components and Schemas [ID 472937.1]
- JZ0SJ: Metadata accessor information was not found on this database.
- How to Encrypt Credit Card Information in an SQL Database
- mongodb error creating initial database config information 问题处理
- Contemporary Issues in Database Design and Information Systems Development (Advances in Database Res
- 1716438 - Error :" Failed to load database information Details: The Database connector crdb_bwquery.
- information
- Designing and Implementing a Search Engine in .NET
- 从硬盘启动Linux(RHEL3)
- Informix7.3(Win2K server)数据库服务器无法启动
- 在Redhat Linux9上安装DB2
- 今天雅典奥运会开幕了 我也有了网上的第一家
- Displaying DataBase Information
- 图书馆网站完成!和 通过网设考试!
- java UIManager 中的各种属性
- java UIManager 中的各种属性
- 在Linux下安装和使用MySQL
- Build Your Own RAC Cluster on Linux and FireWire
- Sending Multiple Emails At Once
- 我的邮箱
- 为Zend Studio3.5添加PHP5 DOM代码的自动完成功能