ASP.NET contains some data access tools. Through these tools, you can better design our website, allowing your users to interact with the database more easily when accessing the site.
In Data Access (Data Access), the most important thing is the first thing to do is the connection to the application and data source. Below we discuss some of the methods of data access.
Use ODBC.NET
ODBC (Open Database Connectivity) is a widely accepted database access API (Application Programming Interface, application interface). It is based on X / Open and ISO / IEC call-level interface (CLI, Call-Level Interface) specification from the database API domain, and uses SQL (Structured Query Language) as its database access language.
ODBC is designed to have the greatest interoperability, that is, the same code can be used, and different database operating systems (DBMS, Database Management Systems) can be accessed by an application. The database application calls the function in the ODBC interface, and these functions are called "Drivers" in the database-specific module. Since the driver is loaded at runtime, if the user needs to access the new DBMS, you need to add a new drive, but you don't need to recompile and connect the application after loading.
The ODBC frame consists of four parts:
● Application (APPLICATION). Perform processing, call the ODBC function to submit the SQL statement and get the result.
● Drive Manager. Load and unload the drive for your application. Process the ODBC function call or pass them to the drive.
● Drive (Driver). Process the ODBC function call, submit the SQL request to the specified data source, and return the result to the application. If necessary, the drive modifies the application's request so that the request can be identified by the corresponding DBMS.
● Data Source. Including the user wants to access and its corresponding operating system, DBMS, is used to access the DBMS network platform (if any).
The figure below shows the relationship between the above four parts.
Figure 4.1 Relationship between ODBC components
Once the installation is successful, you can use a new namespace - System.Data.odbc. It is added to the Global Assembly Cache as a local part of the .NET Framework (.NET Framework).
System.Data.odbc namespace includes: OdbcConnection, Odbcdataadapter, OdbcCommand, OdbcdataReader, Odbcparameter, ODBCTRANSACTION, ODBCCMMAVAILDER, etc.
Below we start describing how to use ODBC in data access.
Using ODBC DSN (Data Source Name, data source name) needs to be as follows:
● Create a DSN.
Creating DSNs requires the "ODBC Data Source Administrator). You can find it in "Control Panel" or "Administrative Tools). The interface is shown below. Figure 4.2 ODBC Data Source Setting Interface
When using ASP.NET, make sure that you create "System DSN" (System DSN) instead of "user DSN".
In the above interface, you can add (
),delete(
) And modifications (
DSN. Click the Add button to select a suitable Drive (Driver) in the interface below.
Figure 4.3 Selecting the appropriate drive from the list of drivers
After selecting the drive, click "Finish" (
Buttons, start setting the specific properties of the "Data Source". The interface is shown in Figure 4.4.
Figure 4.4 Setting data source properties
After entering DSN (DATA SOLRE NAME) and "Description) in the figure, select (
),create(
),repair(
) Or compression (
The selected database.
After setting it, click "OK".
) Complete the DSN settings. The result is shown in Figure 4.5.
Figure 4.5 DSN settings
Note: Due to the interface in Figure 4.3, we chose the drive for the Access database, so you can only configure the Access database in the interface of Figure 4.4. If the reader wants to use another database, you can select the corresponding drive in the interface of Figure 4.3.
The DSN is set, and the data source can be accessed next.
● Connect using the connection statement.
Oconn.open "DSN = goodday;" & _
"UID =;" & _
PWD =;
Use .NET JET Driver
We usually say Jet Driver, also called desktop ODBC database drivers for odbc. In the Jet Database Engine (Jet Database Engine) version 3.5, the Internet synchronization is introduced to meet the needs of data on the Internet (Internet) or Internal Internet (intranet). With the release of Jet Database Engine 4.0, many new features are introduced into Internet synchronization, mainly:
● Support HTTP1.1 protocol, which removes the dependence of FTP for synchronization.
● Reduce transmission time.
● New, named "Anonymous" replica type.
● Additional, multiple registration codes for controlling synchronization times (Registry Keys).
Internet Synchronization can also be used for local area networks (LAN, Local Area Network, sometimes called intranet), and standard indirect synchronization (Indirect Synchronization). Internet synchronization is different from indirect synchronization, and does not need to be equipped with synchronization device on the client to implement synchronization operations of the database. To implement synchronization on the Internet, the client computer must establish an HTTP connection on the Internet or intranet server. Establishing a connection to the server based on the configuration between the client computer and the server, the server will display a login dialog on the client. At this time, only when the client computer enters the correct username and password, synchronization will only start. Once the connection is successful, the client computer creates a message file, which records the changes that occur in the database "replica" (Replica) after the last synchronization, and upload the message file to "shuttle" on the Internet or Intranet server. DROP BOX.
Internet synchronization applies these changes to basic "Base Replica) unless you use the Visual Basic® application code to specify a different" replica "that is different from the basic" replica ".
These changes are applied to the basic "Base Replica), the Internet synchronization creates a message file that contains the database from the basic" replica "(or" replica ") specified in the program code). Change and upload the message file to the "DROP BOX" on the Internet or intranet server. The Internet server returns the name and location of the message file to the client computer, and the client computer applies the contents of the message file to the client database "replica".
As mentioned in 4.2, ODBC is an API using SQL (Structure Query Language) as a database access language. You can access a lot of DBMS (Database Management System) through a session of the application. Using the Microsoft ODBC Desktop Database DRIVER, you can use ODBC's applications, users can do open, query, and modify the desktop database through the ODBC interface.
"Microsoft ODBC Desktop Database" is a set of Microsoft Jet ODBC drives (see 4.2) For knowledge about ODBC drives. It should be noted that in the "Microsoft ODBC Desktop Database" version 2.0 includes 16-bit and 32-bit drives, only included in Windows 95 and later, Windows NT Workstation or Server Version 4.0, Windows 2000 Professional, Windows 2000 Server and other operating systems 32-bit drives. These drives provide access to the following data sources:
● Microsoft Access
● DBASE
● Microsoft Excel
● Paradox
● Text (Text)
Access to other data sources, such as Lotus 1-2-3, Microsoft Exchange, and HTML, you need to install the ISAM (IISAM) drive. With Microsoft Jet's OLE Database provider (OLE DB Provider for Microsoft Jet), ADO can access Microsoft Jet Database. ● Connection String Parameters To connect to the corresponding provider (provider), you need to set the Connectionstring's Provider parameter value: Microsoft.jet.Oledb.4.0 ● Typical connection string for the OLE database provider Typical connection strings are as follows: "provider = microsoft.jet.Oledb.4.0; data source = database; user ID = username; password = userpassword;" This string includes the following keywords:
Table 4.1 OLE database provider typical connection string keyword description
In addition to using the OLE database provider to connect to the database, you can also use the ODBC drive to access the database. This is mainly directed to the connection to the database. (Once connected to the data source, the rest of the work - such as data from the data source can see the content in Chapter 3, ASP.NET Database Access-Add-ADO.NET ".) ● Using ODBC Drive establishment with AS / 400 data source: Oconn.open "Driver = {Client Access ODBC Driver (32-bit)};" & _ "system = myas400;" & _ "uid = myusername;" & _ "PWD = mypassword; "● Establish connection with Access data sources using the ODBC drive: Oconn.open" driver = {Microsoft Access Driver (* .mdb)}; "& _" DBQ = C: /SOMEPATH/Mydb.mdb; "& _ "Uid = admin;" & _ "pwd =;" If it is a system database, then you can do this: Oconn.open "driver = {Microsoft Access Driver (* .mdb)};" & _ "DBQ = C: / SomePath / mydb.mdb; "& _" systemdb = c: /somepath/mydb.mdw; ", _" admin "," If the data source is the network database: Oconn.open "driver = {Microsoft Access Driver (*. MDB)}; "& _" DBQ = // myServer / myshare / mypath / mydb.mdb; "● Connection to DBASE data sources using ODBC drive: Oconn.open" driver = {Microsoft DBase Driver (* .dbf) }; "& _" Driverid = 277; "& _" DBQ = C: / SomePath; "Note: Specify the data table name to be accessed in the SQL statement.
For example (using VB statements): Ors.open "Select * from user.dbf", Oconn,, AdcmdText Note: MDAC (Microsoft Data Access Components 2.1 (or Updated Version) Requires BDE (Borland Database Engine, Borland database engine to upgrade the DBASE DBF file. ● Use the ODBC drive to establish a connection with Excel data sources: Oconn.open "driver = {Microsoft Excel Driver (* .xls)};" & _ "DriverId = 790;" & _ "DBQ = C: /SOMEPath/Myspreadsheet.xls;" & _ "defaultdir = c: / somepath;" ● Establish connection with MySQL (via myodbc) data source using ODBC drive: Connect local database Oconn.open "Driver = {mysql}; "& _" server = myservername; "& _" option = 16834; "& _" data = mydb; "Connect remote database Oconn.open" driver = {mysql}; "& _" server = db1 .DATABASE.COM; "& _" port = 3306; "& _" option = 131072; "& _" stmt =; "& _" database = mydb; "& _" uid = myusername; "& _" PWD = Mypassword; "● Use the ODBC drive to establish a connection with Oracle data sources: Use the current version of Microsoft ODBC Drive Oconn.open" Driver = {Microsoft Odbc for Oracle}; "& _" Server = ORACLERVER.WORLD; _ "Uid = myusername;" & _ "PWD = mypassword;" uses the old version of the Oracle ODBC drive from Microsoft Oracle ODBC Drive Oconn.open "Driver = {Microsoft Odbc Driver for Oracle};" & _ "ConnectString = OracleServer.World;" & _ "UID = myusername;" & _ _ "Pwd = mypassword;"
● Connection to the Paradox data source using the ODBC drive: Oconn.open "Driver = {Microsoft Paradox Driver = {Microsoft Paradox Driver (* .db)};" & _ "driverid = 538;" & _ "Fil = Paradox 5.x;" & " _ "Defaultdir = c: / dbpath /;" & _ "DBQ = C: / dbpath /;" & _ "collatingsequence = ASCII;" Note: MDAC (Microsoft Data Access Components, Microsoft Data Access Components) 2.1 (or Update) Version) Requires BDE (BORLAND DATABASE Engine, Borland database engine) to upgrade Paradox Isam FDBF files. ● Establish connection with SQL Server data sources using ODBC drive: Oconn.open "Driver = {SQL Server} & _ "& _" Database = MyDatabaseName; "& _" UID = myusername; "& _" PWD = mypassword; "Trusted connection security): Oconn.open" Driver: Oconn.open "Driver = {SQL Server}; "& _" Server = myservername; "& _" Database = MyDatabaseName; "& _" UID =; "& _" PWD =; "& _" DRIVER = {SQL Server}; " _ "Server = myservername;" & _ "Database = MyDatabaseName;" & _ "Trusted_Connection = YES;" Tips Enter the username and password: Oconn.properties ("prompt") = adpromptalwaysocon N.Open "Driver = {SQL Server};" & _ "Server = myServerName;" & _ "Database = myDatabaseName;" ● Establish connection with Sybase data sources using the ODBC drive: Use Sybase System 11 ODBC drive: Oconn.open "Driver = {Sybase System 11};" & _ "SRVR = MyServerName;" &
_ "UID = myusername;" & _ "PWD = mypassword;" use INTERSOLV 3.10 Sybase ODBC drive: Oconn.open "driver = {INTERSOLV 3.10 32-bit Sybase};" & _ "SRVR = myservername;" & _ "UID = myusername; "& _" PWD = mypassword; "● Connection to Sybase SQL Anywhere Data Source using ODBC drive: Oconn.open" ODBC; driver = Sybase SQL Anywhere 5.0; "& _" defaultdir = C: / dbpath / ; "& _" Dbf = c: /sqlany50/mydb.db; "& _" uid = myusername; "& _" pwd = mypassword; "DSN =" ""; "Note: String in the connection string To keep a DSN tag, even an empty string (as shown in the code). Because it is necessary! If it is not set, it will cause the -7778 error. ● Use the ODBC drive to establish a connection with the teraData data source: Oconn.open "provider = teraData;" & _ "DBCNAME = mydbcname;" & _ "Database = MyDatabaseName;" & _ "uid = myusername;" & _ "PWD = Mypassword; "● Use the ODBC drive to establish a connection with the teraData data source: Oconn.open" Driver = {Microsoft Text Driver (* .txt; * .csv)}; "& _" DBQ = C: / SomePath /; "& _ "Extensions = ASC, CSV, Tab, TXT;" & _ "PERSIST Security Info = FALSE" Note: Specify the data table name to be accessed in the SQL statement.
For example (using VB statements): Ors.open "Select * from customer.csv", _ Oconn, AdopenStatic, AdlockReadOnly, AdcmdText ● Connection to Teradata Data Source using ODBC drive: Using Database container: Oconn.open Driver = { Microsoft Visual FoxPro Driver}; "& _" SourceType = DBC; "& _" SOURCEDB = C: /SOMEPATH/MysourceDb.dbc; "& _" Exclusive = NO; "Does Not use Database container (using free table): Oconn. Open "driver = {Microsoft Visual FoxPro Driver};" & _ "SourceType = DBF;" & _ "SOURCEDB = C: / SomePath / mysourceDbfolder;" & _ "EXCLUSIVE = NO;" Access MySQL To access the mysql database, need to follow Components: ● Mysql-3.23.32-win (this is the latest version, used to create a mysql database) ● Myodbc-2.50.36-dll (this is the most important, MySQL ODBC driver, you can go to www.mysql. COM Download) Access to the MySQL database requires the following steps: ● Install the mysql odbd driver to copy the downloaded myodbd-2.50.46-dll file to the Windows / System directory (Windows2000 is Winnt / System32) and then create a new file The extension is REG (registry file), copy the following to the file. Regedit4 [HKEY_LOCAL_MACHINE / SOFTWARE / ODBC / ODBCINST.INI / myodbc driver] "usageCount" = dword: 00000002 "driver" = "c: //winnt//system/Myodbc.dll" "setup" = "C: // Winnt // system // myodbc.dll "" SQLLEVEL "=" 1 "" Fileusage "=" 02.50 "" "" "" "" "" "" "" "" "" "" "=" = "1" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" 120 "[HKEY_LOCAL_MACHINS / SOFTWARE / ODBC / ODBCINST.INI / ODBC DRIVERS]" Myodbc Driver "=" installed "After saving, the above code is registered in the Windows Registry. Note: The above is suitable for use in a Windows2000 family of systems.
If installed in Windows 98, the value of Driver and SETUP primary key should change accordingly, as follows: "driver" = "c: //windows//system//myodbc.dll" "" setup "=" c: // windows / /System/Myodbc.dll "If successful, you will see this" of myodbc driver in the ODBC data source! ● When establishing a connection to connect the MySQL database, you can use the ODBC driver in the ODBC DSN or 4.3 in the 4.3 (see above). Access Oracle By using Oracle's Microsoft® ODBC drive (see 4.2 About ODBC Drive), you can connect the application to the Oracle database. Oracle's ODBC drive supports access to the PL / SQL package, XA / DTC integration. Note: For the concept of PL / SQL packages and XA / DTC integration, please refer to the relevant Oracle reference book. Oracle RDBMS is a multi-user relational database management system that can run on a variety of workstations and microcomputer operating systems. The IBM compatible machine using the Microsoft Windows Series operating system can interact with the Oracle database server via the network. Networks supporting this interaction include: Microsoft LAN Manager, NetWare, Vines, Decnet, and other networks that support TCP / IP protocols. Oracle's ODBC drive allows applications to access data access to Oracle databases through the ODBC interface. The drive can access the local Oracle database or use SQL * NET to interact with the remote database over the network. The following image (Figure 4.6) shows the structure of the drive: Figure 4.6 ODBC Drive Application Structure of Oracle To access Oracle data, you need to install the following components: ● Oracle's ODBC drive ● Oracle RDBMS Database ● Oracle client software for remote connection Need: ● A network, with a computer running an ODBC drive, and a computer running an Oracle database. ● This network must support SQL * NET connections.
For the Oracle database, you can use the following ways: ● Use ODBC DSN. Detailed configurations are described in 4.2. It should be noted that the "Microsoft Odbc for Oracle" drive is selected as shown in the figure below (Figure 4.7). Set DSN to "Goodday". Figure 4.7 Accessing the Oracle Database via an ODBC Data Source then establishing a connection using the following statement. Oconn.open "DSN = goodday;" & _ "uid =;" & _ "PWD =;
● Use the ODBC drive to connect to the Oracle database specific connection string as follows: Oconn.open "driver = {microsoft odbc for oracle};" & _ "server = oracleserver.World;" & _ "uid = myusername;" & _ "PWD = mypassword; "● Connect OLE DB Provider (Ole DB Provider) Connect Oracle Database from Microsoft OLE Database Provider Oconn.open" Provider = MSDara; "& _" Data Source = Myoracledb; "& _" User ID = myusername; "& _" password = mypassword; "OLE D database provider from Oracle OLE database provider = oraoledb.oracle;" & _ "data source = myoracledb;" & _ "user id = myusername;" & _ "Password = mypassword;" ● Connect Oracle Database Oconn.open "Trusted Connection", "& _" Data Source = MyoracleDb; "& _" User ID = /; "& _" Password =; "or Oconn.open" provider = oraoledb.racle; "& _" data source = myoracledb; "& _" osauthent = 1; "Note:" Data Source "must be set up in use The NET8 name remains. For example, for local naming, it should be an alias in the tnsnames.ora file; for Oracle naming, it should be the name of the NET8 service. Access Excelexcel is a software in Microsoft's Office suite, which is mainly used to process spreadsheets. Excel is welcomed by the majority of office staff with the advantages of friendly interface, and the rapid processing of data. So many documents are saved in Excel. For program designers, in programming, we tend to access the Excel file to get the data. But because the Excel file is not a standard database, it is more difficult to use the program language to access it. It's hard to get it, because we have asp.net, it is not difficult! Here's how to do it. To access Excel, you need the following components: ● .NET Framework SDK ● Microsoft Access Data Component 2.6 (MADC2.6) Of course, the operating system is best for Windows 2000 series or updated versions.
After configuring the environment, use the following code, we can use ODBC to establish connections with the Excel file and get data. (Procedure Using VB Language) DIM MyoledbConnection As OledbConnection = New OLEDBCONNECTION ("provider = microsoft.jet.Oledb.4.0;" & _ "data source = c: /test.xls;" & _ "extended Properties =" "Excel 8.0; "" ") DIM myoledbcommand as oledbcommand = new oledbcommand (" SELECT * from "", if you want to read the contents of Sheet2, change Sheet1 $ to Sheet2 $ DIM MyData as OledbDataAdapter = NEW OLEDBDATAADAPTER (MyoledbCommand) Description: ● Suppose we have to access the file path to: c: /test.xls. If you want to access the Excel file in other locations, you only need to do corresponding changes. ● We need to introduce the system.data.oledb namespace to complete the above operations. ● In the System.Data.OleDB namespace, the OLEDBConnection object is mainly to provide a connection method. OLEDBCommand objects provide specific operation methods for the target. The OLEDBDataAdapter object is the return data set after different operations of the object. The access to the Excel file is a comparison with the access to the Excel file. Here we specifically describe the connection to the Excel file. You can achieve connection with the Excel file by following the following. ● Use ODBC drive Oconn.open "Driver = {Microsoft Excel Driver (* .xls)}};" & _ "driverid = 790;" & _ "DBQ = C: /SOMEPATH/Myspreadsheet.xls;" & _ "Defaultdir = c: / somepath;" ● Using OLE DB Provider for Microsoft Jetoconn.Open "provider = microsoft.jet.Oledb.4.0;" & _ "data source = c: /somepath/myexcelspreadsheet.xls;" & _ " Extended Properties = "" Excel 8.0; HDR = YES; ";" Description: Where Data Source Specifies the full path to the Excel file, Extended Properties specifies some additional properties, such as Excel versions, and HDR values. Among them, HDR = yes indicates that there is a title line (usually the first line) in the area of the table, so the OLE DB Provider will not select the first line of content. If HDR = NO, OLE DB Provider will pop up all selected content into the recordset (RECORDSET).
● Use the ODBC data source to first create an ODBC DSN (see 4.2 of the specific steps and methods). The data source driver selects "Microsoft Excel Driver" (shown in Figure 4.8). Set DSN to "Goodday". Figure 4.8 Creating an ODBC data source for an Excel file and then uses the following statement to establish a connection. Oconn.open "DSN = goodday;" & _ "uid =;" & _ "PWD =;
As a supplementary content, the following sections implemented with VB.NET can export data in the DataSet to the Excel file. Imports SystemImports System.TextNameSpace TOEXCEL 'Function: Export the dataGrid in the ASP.NET to the Excel file. 'Mountains improvements: 1, 2 support Chinese, hidden columns are not displayed Public Class DataGridToCSVPublic Function GenerateFile (ByRef Page As System.Web.UI.Page, ByVal MyDataGrid As System.Web.UI.WebControls.DataGrid, ByVal FileName As String) As StringDim resp As HttpResponseDim colCount As Integer = MyDataGrid.Columns.Count - 1resp = Page.Responseresp.ContentEncoding = System.Text.Encoding.GetEncoding ( "GB2312") 'Chinese distortion of the address key' resp.Charset = "utf-8" 'resp.AddFileDependency (FileName)' resp.ContentType = "text / HTML" 'resp.AppendHeader ( "Content-Type", "text / html; charset = gb2312") resp.AppendHeader ( "Content-Disposition", "attachment ; filename = " fileName) 'this line of code can be made into a file download file Dim colHeaders As String =" "Dim strItems As StringBuilder = New StringBuilder () Dim myCol As DataGridColumnDim i As IntegerFor i = 0 to colCountmyCol = MyDataGrid.Columns (i) If myCol.Visible = True ThencolHeaders = colHeaders & myCol.HeaderText.ToString & "," End IfNextIf colHeaders.Length> 0 ThencolHeaders = colHeaders.Substring (0, colHeaders.LastIndexOf ( ",")) End IfcolHeader s = colHeaders & Chr (13) & Chr (10) resp.Write (colHeaders) Dim colRow As StringDim item As DataGridItemFor Each item In MyDataGrid.Itemsresp.Write (FormatExportRow (colCount, item, MyDataGrid)) Next itemresp.End () END FUNCTION
Private Function FormatExportRow (ByVal colCount As Integer, ByVal Item DataGridItem As, ByVal MyDataGrid As System.Web.UI.WebControls.DataGrid) As StringDim strItem As StringDim i As IntegerFor i = 0 To colCountIf MyDataGrid.Columns (i) .Visible = True Thenif Item.cells (i) .text is system.dbnull.value kil titym.cells (i) .text = "" END IFIF I = Colcount Tenstritem = item.cells (i) .text.toTString & Chr (13) & CHR (10) Elsestritem = item.cells (i) .Text.toString & "" End IFEND IFNEXTSTRITEM = Replace (stritem, "," ") Return Stritement Functionend Classend Namespace
Accessing TXT files Since the text file (.txt) is a relatively basic file type, the access method of such files is more than a relatively commonly used method. ● Use an ODBC data source. First create an ODBC DSN (see 4.2 of the specific steps and methods). The data source driver selects "Microsoft Text Driver" (shown in Figure 4.9). Set DSN to "Goodday". Figure 4.9 Creating an ODBC data source for a text file and uses the following statement to establish a connection. Oconn.open "DSN = goodday;" & _ "uid =;" & _ "PWD =; ● Using ODBC Drive Oconn.open" Driver = {Microsoft Text Driver (* .txt; * .csv)} for text files. ; "& _" DBQ = C: / SomePath /; "& _" Extensions = ASC, CSV, Tab, TXT; & _ "PERSIST Security Info = FALSE" Note: Specify the file name in the SQL statement. ● Using OLE DB Provider for Microsoft Jetoconn.open "provider = microsoft.jet.Oledb.4.0;" & _ "data source = c: / somepath /;" & _ "extended proties =" "" " ";" '' And then query data from actual files Ors.open "Select * from mytextfile.txt", Oconn, AdopenStatic, AdlockReadonly, AdpMdText ● Create a text file using System.io Namespace (C: /GoodDay.txt And written into a piece of text. "Declaring stream writing object DIM STRWRITEROBJ AS STREAMWRITER Creates text files, assigns textfile object strwriterobj = file.createtext (" c: /goodday.txt ")" Write content strwriterobj.writeline ("Welcome to Wonderful WORLD OF ASP.NET Programming ")" completes the operation, close the flow object strwriterobj.close reads text from the file you just created.
