A. Connect to an Access database
1. Connect using a connection string that already has DSN (ODBC)//Import namespaces
Using System.Data.Odbc;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "dsn=sample";
Instantiating a Connection object
OdbcConnection myconnection = new OdbcConnection (CONNSTR); To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OdbcCommand mycommand = New OdbcCommand ("SELECT * from Sampletable", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
2. Connect using a DSN-free connection string (ODBC)
Import namespaces
Using System.Data.Odbc;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "Driver=microsoft Access Driver (*.mdb);D bq=c:\sample.mdb;";
Instantiating a Connection object
OdbcConnection myconnection = new OdbcConnection (CONNSTR);
To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OdbcCommand mycommand = New OdbcCommand ("SELECT * from Sampletable", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
3. Connecting using a connection string (OLE DB)
Oledb.net Data Provider support for OLE DB Provider:
SQLOLEDB: Used to access the SQL Server database
MSDAORA: Used to access the Oracle database
Microsoft.Jet.OLEDB.4.0: Use to access the Access database.
Import namespaces
Using System.Data.OleDb;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\sample.mdb;";
Instantiating a OleDbConnection object
OleDbConnection myconnection = new OleDbConnection (CONNSTR);
To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OleDbCommand mycommand = new OleDbCommand ("SELECT * from Sampletable", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
4. Connect using a UDL file
To connect to a data source using a UDL file, follow these steps:
(1) Create a new Notepad with the extension. udl.
(2) Double-click the UDL file to bring up the Data Connection Properties dialog box.
(3) The first page of the dialog box displays the Providers tab and selects the OLE DB provider to use.
(4) Click Next, display the L Connection tab, set the correct parameters, and click Test Connection.
Using Connection Strings
Import namespaces
Using System.Data.OleDb;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "FILE name=c:\oledb.udl";
Instantiating a OleDbConnection object
OleDbConnection myconnection = new OleDbConnection (CONNSTR);
To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OleDbCommand mycommand = new OleDbCommand ("SELECT * from Sampletable", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
second, connect MySQL database
1. Connect using a connection string that already has DSN
Import namespaces
Using System.Data.Odbc;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "dsn=mysql";
Instantiating a Connection object
OdbcConnection myconnection = new OdbcConnection (CONNSTR);
To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OdbcCommand mycommand = New OdbcCommand ("SELECT * from Names", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
2. Connect using a DSN-free connection string
Import namespaces
Using System.Data.Odbc;
protected void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String [email protected] "Driver=mysql ODBC 3.51 Driver; Server=localhost;database=test; Uid=root; pwd=yourpassword;option=3; port=3306 ";
Instantiating a Connection object
OdbcConnection myconnection = new OdbcConnection (CONNSTR);
To open a connection by executing the Open method
Myconnection.open ();
Execute SQL statement
OdbcCommand mycommand = New OdbcCommand ("SELECT * from Names", MyConnection);
Assign the result of a query to the data source of the GridView
Gv. DataSource = Mycommand.executereader ();
Bind GridView
Gv. DataBind ();
Close connection
Myconnection.close ();
}
Third, connect Oracle database
1. Using oracle.net Data Provider (requires Oracle client installation)
//Import namespaces
Using System.Data.OracleClient;
public void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String connstring = @ "Data source=oraclesample; User Id=oracleid; Password=oraclepwd; ";
Instantiating a OracleConnection object
OracleConnection conn = new OracleConnection (connstring);
Open connection
connn. Open ();
}
2. Using Odbc.net Data Provider
Import namespaces
Using System.Data.Odbc;
public void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String connstring = @ "Driver=microsoft ODBC for Oracle; Server=oraclesample; Persisit Security Info=false; Trusted_connection=yes; ";
Instantiating a OracleConnection object
OdbcConnection conn = new OdbcConnection (connstring);
Open connection
connn. Open ();
}
3. Using OLE db.net Data Provider
Import namespaces
Using System.Data.Oledb;
public void Page_Load (Object Sender,eventargs E)
{
Setting the connection string
String connstring = @ "Provider=msdaora;data source=oraclesample; Persisit Security info=false;integrated security=yes; ";
Instantiating a OracleConnection object
OleDbConnection conn = new OleDbConnection (connstring);
Open connection
connn. Open ();
}
Iv. Accessing Excel
1. Accessing Excel using Odbc.net Data Provider
using system.data.odbc;
Protected void page_load (object sender,eventargs e)
{
//Setting connection string
string connstr = @" Driver=microsoft excel driver (*.xls);D bq=c:\excelsample.xls; ";
//Instantiating OdbcConnection objects
odbcconnection myconnection = new odbcconnection ( CONNSTR);
//Execute open method opens connection
Myconnection.open ();
//Execute SQL statement
Odbccommand mycommand = new odbccommand ("Select * from [sheet1$] ", myconnection);
//Use GridView to display data
GV. Datasource = mycommand.executereader ();
GV. DataBind ();
//Call the Close method to close the connection
Myconnection.close ();
} Note: The ConnectionString property is driver (drive name), DBQ (the SQL statement used when accessing Excel is the same as the statement used when accessing the database, except that the table name after from is different, such as "select * from [sheet1$] , which means that the Shee table is accessed, and to access Sheet2,sheet3, replace the sheetl in the SQL statement.
2. Accessing Excel using OLE Db.net Data provider
using system.data.oledb;
Protected void page_load (object sender,eventargs e)
{
//Setting connection string
string connstr = @" provider= microsoft.jet.oledb.4.0;data source=c:\excelsample.xls; extened properties=excel 8.0; ";
//Instantiating OdbcConnection objects
oledbconnection myconnection = new OleDbConnection (CONNSTR);
//Execute open method opens connection
Myconnection.open ();
//Execute SQL statement
Oledbcommand mycommand = new oledbcommand ("select * from [items$] ", myconnection);
//Use GridView to display data
GV. Datasource = mycommand.executereader ();
GV. DataBind ();
//Call the Close method to close the connection
Myconnection.close ();
} Note: The Conn}ctionstring property is provider (provider name), Data Source (the real path name of the Excel home daughter), Extended properties (attached property). Where Extended properties develops some additional attributes, such as the version of Excel (in this case Excel 8.0) and the HDR value. Hdr=yes represents the first behavior title of the table, and the application uses the SQL statement to query without selecting the contents of the first row; Hdr=no indicates that the application will query all the selected contents of the table (including the first row).
v. Access TXT file
1. Using Odbc.net Data Provider
string connstr = @ "Driver=microsoft Text Driver (*.txt;*.csv);D bq=c:\samplepath\; Extensions=asc,csv,tab,txt; ";
OdbcConnection myconnection = new OdbcConnection (CONNSTR);
OdbcCommand mycommand = New OdbcCommand ("SELECT * from Txtsample.txt", myconnection);
2. Using OLE db.net Data Provider
string connstr = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\samplepath\; Extended Properties=**text; Hdr=yes; Fmt=delimited "" ";
OleDbConnection myconnection = new OleDbConnection (CONNSTR);
OleDbCommand mycommand = new OleDbCommand ("SELECT * from Txtsample.txt", myconnection);
3. Using the System.IO Namespace
The System.IO namespace contains the main classes:
File: Provides a static method for creating, copying, deleting, moving, and opening files (that is, you do not need to create an instance of the class to invoke the method of the class directly).
FileInfo: Provides instance methods for creating, copying, deleting, moving, and opening files (that is, you need to create an instance of the class to invoke the methods of the Class).
StreamReader: Reads characters from the data stream.
StreamWriter: Writes characters from the data stream.
The main methods that the file class contains
OpenText: Open the existing TXT file for reading.
Exists: Determine if the established file exists.
CreateText: Creates or opens a file for writing.
AppendText: Appends txt text to the existing file.
<% @Import namespace= "System.IO"%>
<script language= "C #" runat= "Server" >
protected void Page_Load (Object sender, EventArgs e)
{
Response.Write ("To create an object of the StreamReader class
StreamReader objStreamReader;
String Filecont;
Open an existing TXT file and assign it to the StreamReader object
objStreamReader =file.opentext (@ "C:\txtsample.txt");
Loop calls the ReadLine method to read the TXT text until it finishes reading and displays the results in the form
while (objStreamReader. Peek ()!=-1)
{
Filecont = objStreamReader. ReadLine ();
Response.Write (filecont+ "<br>");
}
Read complete, close the object of the StreamReader class
objStreamReader. Close ();
}
</script>
Note: The StreamReader peek method is capable of returning the next character in the StreamReader object stream, but does not delete the character from the stream, or 1 if the stream is no longer readable in text characters.
<% @Import namespace= "System.IO"%>
<script language= "C #" runat= "Server" >
protected void Page_Load (Object sender, EventArgs e)
{
Response.Write ("Define the path to new txt text
string file_name = @ "C:\sample.txt";
If the TXT file already exists, an error is made; otherwise, a write operation is performed
if (! File.exists (file_name))
{
Create a Sreamwriter object
StreamWriter objStreamWriter;
Create a TXT file and assign it to the StreamWriter object
objStreamWriter = File.createtext (file_name);
Call the ReadLine method to write a line of characters to the TXT text
objStreamWriter. WriteLine ("Writing text successfully!");
Write complete, close the object of the StreamWriter class
objStreamWriter. Close ();
}
Else
{
Response.Write ("This file already exists!");
}
}
</script>
Several common methods of database connection
