C # several methods for connecting to the SQL Server database-Server + Data Source

 

How do I use the connection object to connect to the 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.

Sqlconnection

Connection is used to "talk" to the database and is represented by a Class (such as sqlconnection) of a specific provider. Although the sqlconnection class is for SQL Server, many attributes and methods of this class are similar to events and oledbconnection and odbcconnection.

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 odbcconnection is system. Data. ODBC. The namespace of oracleconnection is system. Data. oracleclinet.

We can use the following two methods to connect to the database,That is, you can use integrated Windows Authentication and SQL Server authentication to log on to the database.

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}, record", x);} catch (exception) {Throw;} finally {con. close (); MessageBox. show ("close database connection", "prompt 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 that is only used for projects and has no high security requirements, you can use sqlserver for verification and update and upgrade.


The connection strings for Windows Authentication and SQL Server authentication are different.