ASP.NET six common database connection methods
- 2020-05-30 19:47:20
- OfStack
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;