SQL database connection

1. Use oledbconnection object to connect to the ole db Data Source

1. Connect to the Access Database

Access 2000: "provider = Microsoft. Jet. oledb.3.5; Data Source = access file path"

Access 2003: "provider = Microsoft. Jet. oledb.4.0; Data Source = access file path"

Access 2007: "provider = Microsoft. Ace. oledb.12.0; Data Source = access file path"

Note: The ACCESS database only provides two connection attributes: provider and data source );

Access2000 \ 2003 is in the format of ". mdb", and access2007 is in the format of ". accdb ";

The data provider version of access is backward compatible, and Microsoft is used for testing in win7. jet. oledb.3.5 prompts "Microsoft. jet. oledb.3.5 "provider. ", Use Microsoft. Jet. oledb.4.0 or Microsoft. Ace. oledb12.0 to access the database file of Access2000. Of course, you can also try to Use MDAC provided by Microsoft to modify the provider version.

2. Connect to the Excel database

Excel 2003: "provider = Microsoft. Jet. oledb.4.0; Data Source = access file path; extended properties = Excel 8.0"

Excel 2007: "provider = Microsoft. Ace. oledb.12.0; Data Source = access file path; extended properties = Excel 12.0"

Note: When referencing a worksheet in the Code, the table name should be expressed as "[worksheet name $]". When a field is reserved for the database, add [] to the field name to indicate the difference, for example, when defining a SELECT statement: String connstr = "select * from [Login $] Where username = 'abc' and [Password] = 'abc123 '";
If numbers are used as text data in the data table, the default value should be forcibly set to text type with single quotation marks before the number.

3. Connect to the SQL Server database

 
 

  
  
Provider = sqloledb;
  
  
Data Source = server name;
  
  
Initial catalog = database name;
  
  
Uid = user;
  
  
Pwd = Password
 
 

2. Use the sqlconnection object to connect to the SQL Server database

Declaration: For the following connection attributes, refer to "SQL Server database connection string parameter list" to obtain its alias. In addition to the attributes that must be set, you can also set other auxiliary attributes. Such as connect timeout and Encrypt

To set the path of a database file:

1. Use absolute path: "attachdbfilename = D: \ solution1 \ WEB \ app_data \ data. MDF"

2. Use the server relative path: "attachdbfilename =" + server. mappath ("\ app_data \ data. MDF ")

3. Use the simplest relative path: "attachdbfilename = | datadirectory | \ data. MDF"

We recommend that you use the following 3rd Methods: "| datadirectory |" indicates the app_data folder automatically created in the ASP. NET project.

1. Connect to sqlserver in SQL Server Authentication Mode

(1) connection by database name

 
 

  
  
Server = server name;
  
  
Database = database name;
  
  
User ID = user name;
  
  
Password = Password
 
 

Or (use abbreviations and aliases)

 
 

  
  
Server = server name;
  
  
Initial catalog = database name;
  
  
Uid = user;
  
  
Pwd = Password
 
 

(2) full path connection of database files

"Serve = server name; attachdbfilename = database file path; user id = username; Password = password"

Example:

 
 

  
  
Server=.\SQLEXPRESS; Database=DatabaseName; User ID =sa; Password=abc123”  
  
  
Server=.\SQLEXPRESS; Initial Catalog =DatabaseName; Uid =sa; Pwd=abc123”  
  
  
Server=(local)\SQLEXPRESS; AttachDbFilename=D:\\Solution1\\Web\\App_Data\\data.mdf;
  
  
User ID =sa; Password=abc123” 
 
 

Note: The password can be blank.

2. Connect to SQL Server in Windows Authentication Mode

(1) connection by database name

 
 

  
  
Server = server name;
  
  
Database = database name;
  
  
Integrated Security = sspi
 
 

(2) full path connection of database files

"Serve = server name; attachdbfilename = database file path; Integrated Security = true"

Example:

 
 

  
  
Server = server name;
  
  
Database = database name;
  
  
Integrated Security = sspi
  
  
Server = (local) \ sqlexpress;
  
  
Attachdbfilename = D :\\ solution1 \ WEB \ app_data \ data. MDF;
  
  
Integrated Security = true"
 
 

Note: sspi is true.

3. Use the odbcconnection object to connect to the ODBC Data Source

"Driver = database provider name; server = server name; database = database name; trusted_connection = yes"
Example:

First, you must configure the corresponding data source in Computer Management (select the database type and set the database file path and the corresponding database name)

 
 

  
  
Driver= Microsoft.Jet.OLEDB.4.0;  
  
  
Server=.\SQLEXPRESS;   
  
  
Database=DatabaseName;  
  
  
Trusted_Connection=yes 
 
 

4. Use the oracleconnection object to connect to the Oracle database

 
 

  
  
Data Source=Oracle8i;  
  
  
Integrated Security=yes 
 
 

5. Configure the database connection in the web. config file of the ASP. Net project and obtain the connection string in the program code.

1. Add a connection in the <connectionstrings> tab.

 
 

  
  
<connectionStrings>   
  
  
<add name="ConnectionName" connectionString="Server=.\SQLEXPRESS;Database=DatabaseName;
  
  
User ID=sa;Password=abc123"   
  
  
providerName="System.Data.SqlClient" />   
  
  
</connectionStrings> 
 
 

Or

 
 

  
  
<connectionStrings>   
  
  
<add name="ConnectionName" 
  
  
connectionString="Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\data.mdf;
  
  
Integrated Security=true" 
  
  
providerName="System.Data.SqlClient" />   
  
  
</connectionStrings> 
 
 

Obtain the connection string in the <connectionstrings> tag in the program code:

Reference namespace:

 
 

  
  
Using System.Configuration ；  
  
  
string connStr = ConfigurationManager.ConnectionStrings["ConnectionName"].ToString(); 
 
 

2. Add a connection in the <deleetask> tab.

 
 

  
  
<appSettings>  
  
  
<add key="ConnectionName" 
  
  
value="Server=.\SQLEXPRESS;Database=DatabaseName;User ID=sa;Password=abc123" />  
  
  
</appSettings> 
 
 

Or

 
 

  
  
<appSettings>  
  
  
<add key="ConnectionName"   
  
  
value="Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\data.mdf;Integrated Security=True" />  
  
  
</appSettings> 
 
 

Obtain the connection string in the <etettings> tag in the program code:

Reference namespace:

 
 

  
  
Using System.Configuration ；  
  
  
string connStr = ConfigurationManager.AppSettings["ConnectionName"].ToString();  
 
 

Link: http://edu.codepub.com/2011/0516/31624.php

 

 

 

Here I want to explain this problem. There are three methods to connect to the database.

1. Server = server name; database = database name; uid = user name; Pwd = Password

2. Data Source = server name; initial catalog = database name; user id = user name; Pwd = Password

Now we will introduce the third method.

As we all know, as long as a website (dynamic website) is created, a Web will always be generated automatically. Config file, which is an XML file used to store the configuration information of Web applications, such as creating a section to store database connection strings. when the web page needs to interact with the database, you do not need to connect to the database every time. When the database is migrated to another different server or the whole website for migration, you only need to modify the web page. The database connection configuration information in the config file does not need to be modified on each page.

3. Connection steps:

(1) Open the automatically generated file web. In the config file, find the sub-configuration section <connectionstrings/> under the configuration section <configuration>, and use the name attribute and <connectionstring/> attribute to configure the database connection information, replace <connectionstring/> with the following code.

<Connectionstrings>

<Add name = "heyjudeconnectionstring (custom name, for later connection .) "Connectionstring =" Data Source = 172.16.100.1 (server address); initial catalog = heyjude (name of the database to be connected); User ID = sa; Password = 123.com/>

</Connectionstrings>

(2) connect to the database on the page to be connected

Before connection, you must use using to reference the namespace using system. Data. sqlclient;

In Visual Studio 2005, You need to reference the namespace using system. configuration;

Protected void page_load (Object sender, eventargs E)

{

String connection = configurationmanager. connectionstrings ["heyjudeconnectionstring"]. connectionstring;

Sqlconnection sqlstr = new sqlconnection (connection );

Sqlstr. open ();

Response. Write ("database connection successful! ");

Sqlstr. Close ();

}

Result:

 

4. In this way, although you still need to connect to the database on each page, when the database address changes, we only need to access the Web. Change the server address in config. If not. It is troublesome to add code in config. You can simply add a sqldatasource control on the default page, and configure it in the Web by following the steps. The corresponding code is automatically displayed in config. You only need to connect to other pages.