Step by step write your own sqlhelper class library (3): connection string

1. connection string Definition
We already know that the ADO. Net Class Library provides consistent access to different external data sources. These data sources can be local data files (such as Excel, txt, access, or SQLite ), it can also be a remote database server (such as SQL Server, MySQL, DB2, and Oracle ). We can select a simple and easy-to-operate identifier to connect to different data sources. At this time, the connection string plays a role.
The connection string identifies the method used by ADO. Net to connect to the database. To connect to different databases, the format of the connection string is different. When creating a database connection, you must provide the correct connection string for smooth database access.

2. connection string syntax format
The format of the connection string is the list of key/value parameter pairs separated by semicolons:

 
Keyword1 = value; keyword2 = Value
 

Keywords are case insensitive and spaces between key/value pairs are ignored.However, values may be case sensitive based on different data sources.Any value that contains semicolons, single quotes, or double quotes must be enclosed in double quotes.

3. Examples of several typical connection strings

ReferencesKomi kiddie,WrittenGood series.

3.1SQLServer connection string

SQL Server supports two authentication modes: Windows Authentication Mode and Hybrid Authentication mode. Two authentication modes are different: For Windows authentication, the SA password is not verified. If the Windows logon password is incorrect, SQL Server cannot be accessed. For hybrid mode, you can use Windows authentication to log on, you can also log on remotely using the SA password. In both cases, the connection string is also different.

(1) connection string in Windows Authentication Mode:

Data Source = myserveraddress; initial catalog = mydatabase; Integrated Security = sspi;
 

Note:

Data Source: The server to be connected.Note that,If the Express version of SQL Server is used, \ sqlexpress must be added after the server name.For example, to connect to a local SQL Server 2008 express database server, you can write data source = (local) \ sqlexpress or. \ sqlexpress.

Initial catalog: Default database name

Integrate security: Use an existing Windows security certificate to access the database.

(2) connection string in hybrid mode

 
Data Source = myserveraddress; initial catalog = mydatabase; user id = myusername; Password = mypassword;
 

Note:

Data Source: As described above.

Initial catalog: As described above.

User ID: Database Server account.

Password: Password of the database server.

(3) generally, in the vs environment, by adding a database connection, a connection string is automatically generated and right-click the attribute to check the connection string.

 

3.2 access connection string

 
Provider=Microsoft. Jet. oledb.4.0; Data Source=C: \ mydatabase. mdb;UserID=Admin; Password=;
 

 

3.3 MySQL connection string

 
Server=Myserveraddress;Database=Mydatabase; uid=Myusername; pwd=Mypassword;
 

 

3.4 DB2 connection string

 
Server=Myaddress: myportnumber;Database=Mydatabase; uid=Myusername; pwd=Mypassword;
 

 

3.5 Oracle connection string

Data Source=Torcl;UserID=Myusername; Password=Mypassword;
 

 

4. Construct a connection string

4.1The connection string is essentially a string.

 
StringConnstr ="Data Source = myserveraddress; initial catalog = database. MDF; Integrated Security = true; user instance = true";
 

 

4.2 Use the dbconnectionstringbuilder class provided by ADO. Net to construct a connection string

Take SQL Server as an example (using system. Data. sqlclient; needs to be introduced ;),Sqlconnectionstringbuilder class inheritance andDbconnectionstringbuilder class

 
Sqlconnectionstringbuilder build =NewSqlconnectionstringbuilder (); Build. datasource=@"(Local) \ sqlexpress"; Build. initialcatalog="Database"; Build. integratedsecurity=True;
 

Benefits:When dynamic String concatenation is used to generate a connection string based on user input, a connection string injection attack may occur. If the string is not verified and malicious text or characters are not escaped, attackers may access sensitive data or other resources on the server. For example, attackers can provide semicolons and append other values to initiate attacks. In this way Replace the input value of the connection string with a valid value.

 

5. Configure the connection string

5.1 connection strings are essentially strings, so they can be written inCodeIt can be referenced directly when necessary, but this is not convenient enough, and is not conducive to maintenance, and the code needs to be re-compiled for each modification. This is of low performance and is generally not used.

5.2 store the connection string in the configuration file

(1) webform:Add a connection string under <configuration/> root node </connectionstrings> in the web. config configuration file.

 <? XML version = "1.0"  ?>  <  Configuration  >    <  System. Web  >      <  Compilation  Debug  = "False"  Targetframework  = "4.0"  />    </ System. Web  >    <  Connectionstrings  >      <  Add  Name  = "Connstring"  Connectionstring  = "Data Source = TERRYCHAN-PC \ sqlexpress; initial catalog = dbtest; Integrated Security = true"  />    </  Connectionstrings  > </  Configuration  > 

 

(2) winform: Create the app. config configuration file, which is the same as Web. config.

 <?  XML version = "1.0"  ?>  <  Configuration  >    <  Connectionstrings  >      <  Add  Name = "Connstring"  Connectionstring  = "Data Source = TERRYCHAN-PC \ sqlexpress; initial catalog = dbtest; Integrated Security = true"  />    </  Connectionstrings  >  </  Configuration  > 

 

6. sqlhelper class

In this example, webform is used to create a datebase database, configure the connection string in Web. config, and introduce the system. Configuration namespace.

 Using System;  Using  System. Collections. Generic;  Using  System. LINQ;  Using  System. Web;  Using  System. Data;  Using  System. Data. sqlclient;  Using  System. configuration;  ///   <Summary>  /// Sqlhelper create by terrychan 2012-04-17  ///   </Summary>  Public   Class  Sqlhelper {  # Region Field ///   <Summary>      ///  Connection string  ///   </Summary>       Private   Readonly  Static   String Connectionstring = configurationmanager. etettings [ "  Connstring  "  ];  # Endregion  } 

 

Author: forevernome
Source: http://www.cnblogs.com/ForEvErNoME/
You are welcome to repost or share it, but be sure to declare it Article Source. If the article is helpful to you, I hope you can Recommendation Or Follow