C # connect to the SQL Server database

C # connect to the SQL Server database

For different. NET data providers, ADO. NET uses different Connection objects to connect to the database. These Connection objects shield us from specific implementation details and provide a unified implementation method.

There are four Connection types: SqlConnection, OleDbConnection, OdbcConnection, and OracleConnection.

Objects in the SqlConnection class are connected to the SQL Server database; objects in the OracleConnection class are connected to the Oracle database; objects in the OleDbConnection class are connected to databases that support OLE databases, such as Access; odbcConnection objects connect to any databases that support ODBC. All communication with the database is ultimately completed through the Connection object.
(1) Use SqlConnection to connect to SQL Server

Add namespace: using System. Data. SqlClient;

Connect to the Database: string conString = "data source = IP address; Database = Database name; user id = user name; password = password ";

SqlConnection myconnection = new SqlConnection (conString );

Myconnection. open ();

(2) connect Oracle with OracleConnection

Join the namespace: using System. Data. OracleClient;

Connect to the Database: string conString = "data source = IP address; Database = Database name; user id = user name; password = password ";

OracleConnection myconnection = new OracleConnection (conString );

Myconnection. open ();

(3) Use MySqlConnection to connect to MySQL

In. there are two methods to connect to the MySQL database in. NET: MySQL Connector/ODBC and MySQL Connector/NET. ODBC Connector is an interaction platform conforming to the ODBC standard. NET to access the MySQL database.

First, you need to download the installation MySql-connector-net-5.1.5.Data.msi component. If it is installed by default, you can go to C: \ Program Files \ MySQL Connector Net 5.1.5 \ Binaries \. mySql. data. dll to copy the file to the bin directory of the project. Add reference MySql. Data. dll to the project. The implementation code is as follows:

Join the namespace: using MySql. Data. MySqlClient;

Connect to the Database: string conString = "server = IP address; Database = Database name; user id = user name; password = password ";

MySqlConnection myconnection = new MySqlConnection (conString );

Myconnection. open ();

(4) use OleDbConnection to connect to various data sources

The connection strings vary depending on different data sources.

Add namespace: using System. Data. OleDb;

Connect to SQL Server: string conString = "Provider = SQLOLEDB.1; Persist Security Info = False; user id = user name; Database = Database name; data source = COMPUTER ;";

OleDbConnection myconnection = new OleDbConnection (conString );

Myconnection. open ();

Connection Access: string conString = "Provider = Microsoft. Jet. OLEDB.4.0; data source = C: \ Database1.mdb; Persist Security Info = False ;";

OleDbConnection myconnection = new OleDbConnection (conString );

Myconnection. open ();

(You can also create a. udl file to obtain the string)

Connect to Oracle: string conString = "Provider = MSDAORA; user id = username; password = password; data source = db; Persist Security Info = False ;";

OleDbConnection myconnection = new OleDbConnection (conString );

Myconnection. open ();

(You can also connect through OracleConnection)

Note: To use different Connection objects, you must import different namespaces. The namespace of OleDbConnection is System. Data. OleDb. The namespace of SqlConnection is System. Data. SqlClient. The namespace of OracleConnection is System. Data. OracleClinet.

We can use the following two methods to connect to the database: Integrated Windows Authentication and SQL Server Authentication for database login.

1. Integrated Windows Authentication syntax example

String constr = "server =.; database = myschool; integrated security = SSPI ";

Note: A connection string for the SQL Server database is set in the program code. Server indicates the name of the computer that runs SQL Server. Because the program and database system are on the same computer, we can replace the current computer name with. (Or localhost. Database indicates the name of the database used (myschool ). Because we want to adopt the integrated Windows authentication method, set the integrated security to SSPI.

2. The Windows Authentication Mode in SQL Server 2005 is as follows:

String constr = "server =.; database = myschool; uid = sa; pwd = sa ";

Note: The program code uses known user names and passwords for database login. Database connection strings are case-insensitive. Uid is the specified database user name, And pwd is the specified user password. For the sake of security, do not include the user name and password in the code. You can use the Integrated Windows authentication method or the Web. the connection string in the Config file is encrypted to improve program security.

3. the SQL Server Authentication Mode in SQL Server 2005 is as follows:

String constr = "data source =.; initial catalog = myschool; user id = sa; pwd = sa ";

Note: In the program code, data source indicates the computer name corresponding to the running database, and initial catalog indicates the database name used. Uid is the specified database user name, And pwd is the specified user password.

4. The Access database connection string format is as follows:

String connectionString = @ "provider = Microsoft. Jet. OLEDB.4.0; data source = c: \ DataSource \ myschool. mdb ";

Note: In the program code, you can use the ole db provider for the Access database to connect to the database. The ole db Provider is Microsoft. Jet. OLEDB.4.0, and the data inventory is placed in the c: \ DataSource directory. The database file is myschool. mdb.

String constr = "server =.; database = myschool; integrated security = SSPI ";
// String constr = "server =.; database = myschool; uid = sa; pwd = sa ";
// String constr = "data source =.; initial catalog = myschool; user id = sa; pwd = sa ";
SqlConnection con = new SqlConnection (constr );
// Con. ConnectionString = constr;
String SQL = "select count (*) from grade ";
SqlCommand com = new SqlCommand (SQL, con );
Try
{
Con. Open ();
MessageBox. Show ("successfully connected to the database ");
Int x = (int) com. ExecuteScalar ();
MessageBox. Show (string. Format ("successfully read {0}, records", x ));
}
Catch (Exception)
{

Throw;
}
Finally
{
Con. Close ();
MessageBox. Show ("Database Connection closed successfully", "message", MessageBoxButtons. YesNoCancel );
}

5. Web. config Configuration

In ASP. NET 2.0, a new declarative expression syntax is used to parse a connection string value at runtime, referencing the database connection string by name. The connection string is stored under the <connectionStrings> Configuration section in the Web. config file to facilitate maintenance of all pages in the application at a single location.

<? Xml version = "1.0"?>
<Configuration>
<ConnectionStrings>
<Add name = "myschool" connectionString = "Server = localhost; Integrated Security = True; Database = myschool; Persist Security Info = True" providerName = "System. Data. SqlClient"/>
</ConnectionStrings>
<System. web>
<Pages styleSheetTheme = "Default"/>
</System. web>
</Configuration>

We can also use the following method to directly read the database connection string from the configuration file. First, we need to reference the using System. Web. Configuration namespace, which contains classes used to set ASP. NET configurations. String connectionString = ConfigurationManager. ConnectionStrings ["myschool"]. ConnectionString;

First, you should distinguish between Windows Authentication and SQL authentication.
Windows verification means that the SqlServer server uses the Windows verification system. If you specify that a Windows Group in SqlServer has access permissions, Windows users in this group have access to the database. This verification has a disadvantage, that is, if the user is not in the domain mode and cannot join the remote computer, if the program is written in C/S mode, windows Authentication fails to allow the Windows Account of the local computer to access the remote database server.

SQL verification is much simpler, that is, you can use the Enterprise Manager of sqlserver to define the Users Controlled by SQL and specify the user permissions. This account information is maintained by SqlServer. Therefore, after SqlServer is replaced with a computer, the information will not be lost and you do not need to reset it.

Therefore, if your project is used in a relatively large network and has high security requirements, you should establish a domain and use Windows verification, in addition, you need to work with the system administrator to set up a Windows account that can access SqlServer. If you use a small network and the network is only used for projects, there is no high security requirements, you can use SqlServer for verification and update and upgrade.

The connection string for Windows Authentication and SQL Server authentication is different.

This article permanently updates the link address: