ASP.NET six common database connection methods

1. Connect C# to Access

Program code:

using   System.Data;   

using   System.Data.OleDb;   
 ..      

  string   
strConnection="Provider=Microsoft.Jet.OleDb.4.0;";   
  strConnection+=@"Data 
  Source=C:BegASPNETNorthwind.mdb";   

  OleDbConnection   
objConnection=new   OleDbConnection(strConnection);   
  ..   

  
objConnection.Open();   
  objConnection.Close();   
  

Explanation:


Connecting to the Access database requires importing additional namespaces, so having the first two using commands is essential!


The strConnection variable holds the connection string needed to connect to the database. It specifies the data provider to be used and the data source to be used.


"Provider = Microsoft. Jet. OleDb. 4.0;" Is the index data provider, Microsoft is used here
The Jet engine, also known as the data engine in Access,asp.net connects to the Access database based on this.

"Data
Source=C:\BegASPNET\ Northwind.mdb "indicates the location of the data source. Its standard form is "Data"
Source=MyDrive:MyPath\MyFile.MDB".

PS:

1. The "@" symbol after "+=" prevents "\" from being parsed as an escape character.

2. If the database file you want to connect is in the same directory as the current file, you can also use the following method:
strConnection + = "Data
Source=";
strConnection + = MapPath (" Northwind. mdb ");

This will save you a lot of writing!
3. Note that the parameters in the connection string are separated by semicolons.


"OleDbConnection objConnection = new
OleDbConnection (strConnection);" This is the use of a defined connection string to create a link object that we will have to deal with for future operations on the database.


"objConnection. Open ();" This is used to open the connection. At this point, the connection to the Access database is complete.



2.C# connect SQL Server

Program code:

  
  using   
System.Data;   
  using   System.Data.SqlClient;   
  ..   

  
string   strConnection="user   id=sa;password=;";   

strConnection+="initial   catalog=Northwind;Server=YourSQLServer;";   

strConnection+="Connect   Timeout=30";   

  SqlConnection   
objConnection=new   SqlConnection(strConnection);   
  ..   

  
objConnection.Open();   
  objConnection.Close();   
  

Explanation:


The mechanism for connecting to the SQL Server database is not much different from the mechanism for connecting to the Access, except that it changes the Connection object and different parameters in the connection string.


First, the namespace used to connect SQL Server is not "System.Data.OleDb ", but" System.Data.SqlClient ".


The second is his connection string, we will introduce 1 by 1 parameter (note: the parameters are separated by semicolon):
"user
id=sa": the authenticated user connecting to the database is sa. He also has an alias "uid", so we could also write "uid=sa".

"password=": the authentication password for the connection to the database is empty. His alias is "pwd", so we can write "pwd=".
Notice here, your SQL
Server must have been set up to require a user name and password to log in, otherwise you cannot log in this way
Server is set to Windows to log in, so there is no need to use "user "here
To log in, use "id" and "password" instead of "Trusted_Connection=SSPI".
"initial
catalog=Northwind": the data source used is the database "Northwind". Its alias is "Database".

"Server=YourSQLServer": use a server named "YourSQLServer". His alias is "Data"
Source","Address","Addr". If the local database is used and the instance name is defined, write "Server=(local)\ instance name "; If it is a remote server, replace "(local)" with the name of the remote server or the IP address.

"Connect Timeout=30": connection timeout is 30 seconds.


Here, the constructor used to establish the connection object is :SqlConnection.

3. C Oracle # connection

Program code:

  
  using   System.Data.OracleClient;   
  using   System.Data;   
    
  // Add on the form 1 A button, call Button1 , double-click the Button1 , enter the following code    
  private   void   
Button1_Click(object   sender,   System.EventArgs   e)   
  {   
  string 
  ConnectionString="Data   Source=sky;user=system;password=manager;";// Write a connection string    
  OracleConnection   conn=new   OracleConnection(ConnectionString);// create 1 A new connection  

  try   
  {   
  conn.Open();   
  OracleCommand   
cmd=conn.CreateCommand();   

  cmd.CommandText="select   *   from   
MyTable";// Write here sql statements    
  OracleDataReader   
odr=cmd.ExecuteReader();// create 1 a OracleDateReader object    

while(odr.Read())// Read the data if odr.Read() Return to false That means we're at the end of the recordset                        

  {   

Response.Write(odr.GetOracleString(1).ToString());// The output field 1 , this number is the field index, how to use the field name is still to be studied  

  }   
  odr.Close();   
  }   
  catch(Exception   ee)   
  { 

  Response.Write(ee.Message);   // If there is an error, output an error message    
  }   
  finally   
  {   
  conn.Close();   // Close the connection    
  }   
  }   
  

4. C MySQL # connection

Program code:

  
  using   MySQLDriverCS;     

  //    Establishing a database connection    

MySQLConnection   DBConn;   
  DBConn   =   new   MySQLConnection(new   
MySQLConnectionString("localhost","mysql","root","",3306).AsString);   

DBConn.Open();     

  //    Execute query statement    
  MySQLCommand   DBComm;   
  DBComm   =   new   MySQLCommand("select   Host,User   from   
user",DBConn);     

  //    Read the data    
  MySQLDataReader   DBReader   
=   DBComm.ExecuteReaderEx();     

  //    Display the data    
  try   
  { 

  while   (DBReader.Read())   
  {   
  Console.WriteLine("Host   = 
  {0}   and   User   =   {1}",   DBReader.GetString(0),DBReader.GetString(1));   
  }   
  }   
  finally   
  {   
  DBReader.Close();   

DBConn.Close();   
  }     

  // Close the database connection    
  DBConn.Close();   

5.C# connect IBM DB2

Program code:

  
  OleDbConnection1.Open();   
  // Open database connection    

OleDbDataAdapter1.Fill(dataSet1,"Address");   
  // Fill in the data dataSet   

DataGrid1.DataBind();   
  // Data binding    
  OleDbConnection1.Close();   

// Close the connection      

  // Add database data    
   in Web   
Form Add the number of corresponding fields on TextBox , and 1 a button , to add to the button Click The response event code is as follows:    

  
this.OleDbInsertCommand1.CommandText   =   "INSERTsintosADDRESS(NAME,   

EMAIL,   AGE,   ADDRESS)   VALUES   

('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"','"+TextBox4.Text+"')"; 

  OleDbInsertCommand1.Connection.Open();   
  // Open the connection    

OleDbInsertCommand1.ExecuteNonQuery();   
  // Perform the SQL statements    

OleDbInsertCommand1.Connection.Close();   
  // Close the connection      

6. C SyBase # connection
Program code: (OleDb)

  Provider=Sybase.ASEOLEDBProvider.2;Initial   Catalog= The database name ;User   
ID= The user name ;Data   Source= The data source ;Extended   Properties="";Server   Name=ip address ;Network   
Protocol=Winsock;Server   Port   Address=5000;