How to use ADO. Net to connect different data sources

Use ADO. net can connect to SQL Server, access, Excel, XML and other databases. First, create a connection and then use the connection. open the connection before use, and remember to close the connection. the following sections describe:
1. Connect to SQL Server
The using system. data; and using system. Data. sqlclient are required. complete code is used from creation to use:
{
String strconnection = "Server = (local); database = yourdatabase; Integrated Security = true ;";
// If your database server uses the hybrid mode for verification, use
// String strconnection = "Server = (local); database = yourdatabase; user id = sa; Password = 123456 ;";
// String strconnection = "Server = (local); database = yourdatabase; uid = sa; Pwd = 123456 ;";
　　
Sqlconnection con = new sqlconnection (strconnection); // create a connection
　　
String strsql = "select firstname, lastname, country from employees ";
Sqlcommand cmd = new sqlcommand (strsql, con );

Con. open (); // open the database connection
Gvlist. datasource = cmd. executereader (); // set the data source of the gridview Control
Gvlist. databind (); // bind data to the gridview Control
Con. Close (); // close the connection
}
Generally, strconnection = "Server = (local); database = yourdatabase; Integrated Security = true;"; the connection string is placed on the web. config file. set in <configuration> </configuration>.
Example: <configuration>
<Deleetask>
<Add key = "strconnection" value = "Server = (local); database = yourdatabase; Integrated Security = true;"/>
</Appsettings>
<System. Web>
......
</System. Web>
</Configuration>
Or: <configuration>
<Connectionstrings>
<Add name = "BBS" connectionstring = "Data Source =. \ sqlexpress; Integrated Security = true; user instance = true; attachdbfilename = | datadirectory | BBS. MDF "providername =" system. data. sqlclient "/>
</Connectionstrings>
<System. Web>
......
</System. Web>
</Configuration>
In use, using system. configuration; namespace.
Former: String strcon = configurationsettings. deleettings ["strconnection"]. tostring ();
The latter: String strcon = configurationmanager. connectionstrings ["BBS"]. connectionstring

2. Connect to access
The using system. data; and using system. Data. oledb namespaces are required. complete code is used from creation to use:
{
String strconnection = "provider = Microsoft. Jet. oledb.4.0; Data Source = E: \ yourdatabase. mdb ;";
　　　　
Oledbconnection con = new oledbconnection (strconnection); // create a connection
　　
String strsql = "select firstname, lastname, country from employees ";
Oledbcommand cmd = new oledbcommand (strsql, con );

Con. open (); // open the database connection
Gvlist. datasource = cmd. executereader (); // set the data source of the gridview Control
Gvlist. databind (); // bind data to the gridview Control
Con. Close (); // close the connection
}

3. Connect to excel
The using system. data; and using system. Data. oledb namespaces are required. complete code is used from creation to use:
{
String strconnection = "provider = Microsoft. Jet. oledb.4.0; Data Source = E: \ yourdatabase.xls; extended properties = Excel 8.0 ;";
　　　　
Oledbconnection con = new oledbconnection (strconnection); // create a connection
　　
String strsql = "select firstname, lastname, country from employees ";
Oledbcommand cmd = new oledbcommand (strsql, con );

Con. open (); // open the database connection
Gvlist. datasource = cmd. executereader (); // set the data source of the gridview Control
Gvlist. databind (); // bind data to the gridview Control
Con. Close (); // close the connection
}

4. Connect to the XML Data Source
{
String strxmlfile = "E: \ yourxmlfile. xml ";
Dataset dataset = new dataset ();
Dataset. readxml (strxmlfile );

Dvlist. datasource = dataset. Tables [0]. defaultview;
Dvlist. databind ();
}

5. other properties of the connection object
① Connction Timeout: the waiting time for the connection to be opened. The default value is 15 seconds. This time is enough to find the server, determine the existence of the database, verify the security permissions, and establish a communication channel. if it cannot be completed within 15 seconds, it will time out. if not, you can add value.
Example: String strconnection = "Server = (local); database = northwind; integared SECURITY = true; connection timeout = 30 ;";
② Packet size: the packet size. The default value is 8192 bytes, which is sometimes greater than required. If the number of data sent by the request is small, the packet size can be reduced, which improves the performance.
For example, string strconnection = "Server = (local); database = northwind; integared SECURITY = true; packet size = 512 ;";

6. Method of connection object
① Changedatabase () allows you to switch to different databases on the same server without closing the connection.
Example: String strconnection = "Server = (local); database = northwind; integared SECURITY = true ;";
Sqlconnection con = new sqlconnection (strconnection );
String strsql = "select * from employees ";
Sqlcommand cmd = new sqlcommand (strsql, con );
Con. open ();
Con. changedatabase ("master ");
Con. Close ();
② Begintransaction (), which is the most important method to obtain a transaction processing object.
For example, the connection creation code is omitted.
Sqltransaction Tran;
Con. open ();
TRAN = con. begintransaction ();