C # Several ways to connect to a SQL Server database--server+data source, etc.

This article is reproduced from the busy Cuckoo

How do I connect to a database using connection objects?

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

There are four types of connection: Sqlconnection,oledbconnection,odbcconnection and OracleConnection.

The objects of the SqlConnection class are connected to the SQL Server database; the objects of the OracleConnection class are connected to the Oracle database; The OleDbConnection class's Object connections support OLE DB databases, such as Access While the object of the OdbcConnection class connects to any ODBC-enabled database. All communication with the database is ultimately done through the connection object.

SqlConnection class

Connection is used for "dialog" with the database and is represented by a particular provider's class (such as SqlConnection). Although the SqlConnection class is for SQL Server, many of the properties, methods, and events of this class are similar to those of OleDbConnection and OdbcConnection.

Note: different namespaces are required to be imported using different connection objects. The OleDbConnection namespace is System.Data.OleDb. The SqlConnection namespace is System.Data.SqlClient. The OdbcConnection namespace is System.Data.Odbc. The OracleConnection namespace is System.Data.OracleClinet.

We can connect to the database in two ways, using Integrated Windows authentication and using SQL Server Authentication to log on to the database.

1. Integrated Windows Authentication Syntax example

String constr = "server=.; Database=myschool;integrated Security=sspi ";

Description: A connection string was set for the SQL Server database in the program code. Where server represents the name of the computer running SQL Server, because the program and database system are located on the same computer, so we can use. (or localhost) to replace the current computer name. Database represents the name of the databases used (MySchool). Since we want to use Integrated Windows authentication, set integrated security as SSPI.

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

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

Note: In the program code, a login with a known user name and password authentication is used for the database. Database connection strings are case-insensitive. The UID is the specified database user name, and PWD is the specified user password. For security reasons, do not include the user name and password in your code, and you can improve the security of your program by using the previous Integrated Windows authentication method or by encrypting the connection string in the Web. config file.

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 ";

Description: The data source in the program code represents the computer name of the running database, and initial catalog represents the name of the database used. The UID is the specified database user name, and PWD is the specified user password.

4. The connection string for the Access database is in the following form:

string connectionString [email protected] "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datasource\myschool.mdb" ;

Description: In program code, a database connection is implemented through an OLE DB provider specifically for an Access database. This uses the OLE DB provider as microsoft.jet.oledb.4.0, and the database is stored in the C:\DataSource directory with a database file of Myschool.mdb.

[CSharp]View Plaincopy

1.  stringConstr ="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 =NewSqlConnection (CONSTR); //con.  ConnectionString = Constr;  stringsql ="Select COUNT (*) from grade"; SqlCommand com=NewSqlCommand (Sql,con); Try{con.       Open (); MessageBox.Show ("Successfully connected Database"); intx = (int) com.       ExecuteScalar (); MessageBox.Show (string. Format ("successfully read {0}, record", x)); }   Catch(Exception) {Throw; }   finally{con.       Close (); MessageBox.Show ("successfully closed database connection","Prompt Information", Messageboxbuttons.yesnocancel); }  

5. Web. config configuration

in ASP. NET 2.0, a new declarative expression syntax that resolves to a connection string value at run time is used to refer to the database connection string by name. The connection string itself is stored under the configuration section in the Web. config file so that it is easy to maintain for all pages in the application in a single location.

[CSharp]View Plaincopy

1. <?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 read the database connection string directly from the configuration file in the following way. First we need to refer to the using System.Web.Configuration namespace, which contains the classes used to set up the ASP. String connectionString =configurationmanager.connectionstrings["MySchool"]. ConnectionString;

First you should distinguish between Windows validation and the validation of SQL itself.   
  Windows authentication is that SQL Server uses the Windows-brought authentication system, if you specify that a group of Windows within SQL Servers has access permissions, Windows users who join this group have access to the database. This validation has the disadvantage that if you are not in domain mode, you cannot join a remote computer user, so if you use the C/S method to write a program, use Windows Authentication to not allow the local computer's Windows account to access the remote database server.   
   
  SQL authentication is much simpler, which is to use SQL Server's Enterprise Manager to define its own users, specify user permissions, and so on. This account information is maintained by SQL Server itself, so the information is not lost and not reset after SQL Server changes the computer.   
   
  So if your project is in a larger network with high security requirements, you should establish a domain, use Windows authentication, And, in conjunction with your system administrator, you can configure Windows accounts to access SQL Server in detail. If you use a small network, and this network is only used for projects, there is no high security requirements, then use SQL Server Authentication, and updates, upgrades, etc. are convenient.   
   
  Windows authentication is different from the database join string validated by SQL Server.