Asp. NET database Connection string Summary

I. Connecting OLE DB data sources using OleDbConnection objects


1. Connect to an Access database




Access: "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: "Provider=microsoft.ace.oledb.12.0;data source=access file path"


Note: Access database provides only two connection properties provider (data Provider) and data source (datasource);


The file format for access2000\2003 is ". mdb", and the file format for Access2007 is ". accdb";


The data provider version of Access is backwards-compatible, and testing under WIN7 uses the microsoft.jet.oledb.3.5 hint "the microsoft.jet.oledb.3.5 provider is not registered on the local computer." , use microsoft.jet.oledb.4.0 or microsoft.ace.oledb12.0 to have full access to the Access2000 database file. Of course, you can also try using MDAC provided by Microsoft to modify the provider version.


2. Connect to an Excel database




Excel 2003: "Provider=Microsoft.Jet.OLEDB.4.0;Data source=access file path; extended Properties=excel 8.0"


Excel: "Provider=microsoft.ace.oledb.12.0;data source=access file path; extended Properties=excel 12.0"


Note: When referencing a worksheet in code, the table name should be expressed as "[sheet name $]", and when the field is reserved for the database, the field name is added [] to differentiate, such as when a SELECT statement is defined: string connstr= "select * FROM [login$] where Username= ' abc ' and [password]= ' abc123 ' ";
If you use numbers as text type data in a datasheet, you should add a single quotation mark before the number to force the default value to the text type.


3. Connecting to a SQL Server database


Provider=sqloledb;  Data source= server name;  Initial catalog= database name;  Uid= users; pwd= password Two, use SqlConnection object to connect to SQL Server database


Declaration: The following connected properties can refer to the SQL Server database connection string parameter list for its alias, and other auxiliary properties can be set in addition to the properties that must be set. such as Connect Timeout, encrypt, etc.


To set the database file path:


1. Use absolute path: "Attachdbfilename=d:\\solution1\\web\\app_data\\data.mdf"


2. Using the server-relative path: "Attachdbfilename=" +server.mappath ("\\App_Data\\data.mdf")


3. Use the simplest relative path: "attachdbfilename=| Datadirectory|\\data.mdf "


It is recommended to use the 3rd method, "| Datadirectory| " Represents an automatically created App_Data folder in an ASP.


1. To connect SQL Server Authentication Mode


(1) Connect by database name


server= server name;  database= database name;  User id= username; password= password or (use initials and aliases)


server= server name;  Initial catalog= database name;  Uid= users; pwd= Password (2) Full path connection to database file


"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 empty.


2. Connecting to SQL Server in Windows Authentication mode


(1) Connect by database name


server= server name;  database= database name; Integrated Security=sspi (2) Full path connection to database file


"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 "Remarks: SSPI is true


Iii. connecting an ODBC data source using a OdbcConnection object


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


First of all, in the Computer Management à data source à configuration of the corresponding data source (select the database type, set the database file path and the corresponding database name)


Driver= microsoft.jet.oledb.4.0;   server=.\sqlexpress;  Database=databasename; Trusted_connection=yes. Connecting to an Oracle database using OracleConnection objects


Data source=oracle8i; Integrated security=yes. Configure the database connection in the Web. config file in the ASP. NET project and get the connection string in the program code


1. Add a connection to 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> get the connection string in the <connectionStrings> tag in the program code:


Reference namespaces:


Using system.configuration; string connstr = configurationmanager.connectionstrings["ConnectionName"]. ToString (); 2. Add a connection to the <appSettings> 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> get <appSettings> in program code The connection string in the tag:


Reference namespaces:


Using system.configuration; string connstr = configurationmanager.appsettings["ConnectionName"]. ToString ();

Asp. NET database Connection string Summary