Working with Connection Strings

Working with Connection Strings

Working with Connection Strings A connection string contains initialization information that is passed as a parameter to a data source.The connection string is parsed immediately after being set. 1.Persist Security Info=true (or false); if setting it to true or yes will allow security-sensitive information,including the userID and password,to be obtained from the connection after the connection has been opened.If false or no  will discarded it after opened.This is very important if you are supplying an open connection to an untrusted source or persisiting connection information to disk 2.Using Windows Authentication It commonly referred to as integreted security,to connect to server databases.For SQL Server,we can use either of the two:    Integrated Security = true;    Integrated Security = SSPI; However .only the second one will work with .NET Framwork Data Provider for OleDb    Integrated Security = SSPI; For the .NET Framework Data Provider for ODBC you must use the following to specify Windows authentication.    Trusted_Connection=yes; 3.Creating Connection String Each of .NET Framework data provider has a strongly type connection string builder class that inherits from DbConnectionStringBuilder.It can help programmer to creat syntactically correct connection strings based on use's input. 4.Storing and Retrieving Connection Strings We recommend that you no embed connection string in your code.If location of the server should ever change,your application will need to be recompiled.In addition,it can be viewed using the MSIL Disassembler(ildasm.exe) (1)Storing Connection String in Cnfiguration Files We can store them in the web.config file for an ASP.NET application and in the app.config file for the windows application. example:(store in the config)                       connectionString="Persist Security Info=False;Integrated Security=SSPI;database=Northwind;server=(local);"              providerName="System.Data.SqlClient" />    (2)Retrieving Connection String from Configuration File. The [System.Configureation] namespace provides classed for working with configuration information stored in configuration files.The [ConnectionStringSettings] class has two properties ,which map to names .     [Name]     [ConnectionString] The following example retrieves the connectin string from the configuration file by passing the name of connection string to the ConfigurationMangaer,which returns a [ConnectionStringSettings] object.The [ConnectionString] property is used to display the value. example: using System; using System.Configuration; class program {    static void Main()    {      ConnectionStringSetting settings;      settings=ConfigurationManager.ConnectionStrings["DatabaseConnection"];      if(setting!=null)      {         Console.Writeline(settings.ConnectionString);      }    } } 5.SqlClient Connection Strings (1)SqlClient Connection String Syntax     "Persist Security Info=False;Integrated Security=true;Initial Catalog=AdventureWorks;Server=MSSQL1"     "Persist Security Info=False;Integrated Security=SSPI;database=AdventureWorks;server=(local)"     "Persist Security Info=False;Trusted_Connection=True;database=AdventureWorks;server=(local)" To force a protocol, add one of the following prefixes:      np:(local), tcp:(local), lpc:(local) For SQL Server Authentication, use this syntax specify a user name and password, where asterisks represent a valid user name and password.     "Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=AdventureWorks;Server=MySqlServer" (2)Connecting to Named Instances To connect to a named instance of SQL Server 2000 or later, use the server name\instance name syntax Server=MySqlServer\MSSQL1;" (3)Setting the Network Library Use this syntax to connect using an IP address, where the network library is Win32 Winsock TCP/IP and 1433 is the port being used (the default).      Network Library=dbmssocn;Data Source=000.000.000.000,1433; SQL Server allows you to use the following network libraries when establishing a connection. dbnmpntw Win32 Named Pipes dbmssocn Win32 Winsock TCP/IP dbmsspxn Win32 SPX/IPX dbmsvinn Win32 Banyan Vines dbmsrpcn Win32 Multi-Protocol (Windows RPC) 6.OleDb Connection Strings (1)Syntax You must specify a provider name for an OleDbConnection connection string. The following connection string connects to a Microsoft Access database using the Jet provider. Note that the UserID and Password keywords are optional if the database is unsecured (the default).     Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\Northwind.mdb;User ID=Admin;Password=; If the database is secured, you must provide the location of the workgroup information file.    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Northwind.mdb;Jet OLEDB:System Database=d:\NorthwindSystem.mdw;User ID=*****;Password=*****; Use the sqloledb keyword for SQL Server 6.5 or earlier    Provider=sqloledb;Data Source=MySqlServer;Initial Catalog=pubs;User Id=*****;Password=*****; 7.Odbc Connection Strings The ConnectionString property of a OdbcConnection allows you to get or set a connection string for an OLE DB data source. The following connection string uses the Microsoft Text Driver.    Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=d:\bin 8.Oracle Connection Strings The ConnectionString property of a OracleConnection allows you to get or set a connection string for an OLE DB data source.    Data Source=Oracle9i;User ID=*****;Password=*****;

转载于:https://www.cnblogs.com/wangzw/archive/2006/03/15/350617.html