笔记44 SQL2005连接字符串

笔记44 SQL2005连接字符串

  1 --SQL2005连接字符串
  2 
  3 
  4 ----------------------------.NET Framework Data Provider for SQL Server--------------------------------------------------
  5 --Type.NET Framework Class Library
  6 --UsageSystem.Data.SqlClient.SqlConnection
  7 --ManufacturerMicrosoft
  8 
  9 
 10 Standard Security
 11 Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;
 12 Password=myPassword;
 13 
 14 Standard Security alternative syntax  --美彬使用
 15 Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;
 16 Trusted_Connection=False;
 17 
 18 
 19 --------------------------------------------------------------------------------------
 20 Trusted Connection
 21 Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
 22 Trusted Connection alternative syntax
 23 Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
 24 
 25 
 26 Connecting to an SQL Server instance
 27 Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;
 28 
 29 ----------------------------------------------------------------------------------
 30 Trusted Connection from a CE device
 31 Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
 32 User ID=myDomain\myUsername;Password=myPassword;
 33 
 34 
 35 -----------------------------------------------------------------------------------
 36 Connect via an IP address  --指定端口
 37 Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;
 38 User ID=myUsername;Password=myPassword;
 39 
 40 
 41 -------------------------------------------------------------------------------------------
 42 Enabling MARS (multiple active result sets)
 43 Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
 44 MultipleActiveResultSets=true;
 45 
 46 
 47 --------------------------------------------------------------------------------
 48 --Attach a database file on connect to a local SQL Server Express instance 
 49 --始终都要安装了SQLExpress,不然没有实例附加本地数据库文件,VS的功能,连接字符串里面有默认实例跟命名实例
 50 Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname;
 51 Trusted_Connection=Yes;
 52 
 53 
 54 
 55 
 56 Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
 57 Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
 58 Trusted_Connection=Yes;
 59 
 60 
 61 
 62 
 63 
 64 Using an User Instance on a local SQL Server Express instance
 65 Data Source=.\SQLExpress;Integrated Security=true;
 66 AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
 67 
 68 
 69 ----------------------------------------------------------------------------------------
 70 Database mirroring    --数据库镜像
 71 Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
 72 Initial Catalog=myDataBase;Integrated Security=True;
 73 
 74 
 75 
 76 
 77 
 78 
 79 -------------------------------------------------------------------------------------
 80 Asynchronous processing
 81 Server=myServerAddress;Database=myDataBase;Integrated Security=True;
 82 Asynchronous Processing=True;
 83 
 84 
 85 
 86 
 87 -------------------------SQL Native Client 9.0 OLE DB provider-------------------------------------------------------------
 88 --TypeOLE DB Provider
 89 --UsageProvider=SQLNCLI
 90 --ManufacturerMicrosoft
 91 
 92 
 93 Standard security
 94 Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
 95 Pwd=myPassword;
 96 
 97 
 98 -------------------------------------------------------------------------
 99 
100 Trusted connection
101 Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
102 
103 -----------------------------------------------------------------------------
104 Connecting to an SQL Server instance
105 Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase;
106 Trusted_Connection=yes;
107 ------------------------------------------------------------------------------
108 
109 Prompt for username and password
110 oConn.Properties("Prompt") = adPromptAlways
111 oConn.Open "Provider=SQLNCLI;
112 Server=myServerAddress;DataBase=myDataBase;
113 
114 ------------------------------------------------------------------------------------
115 Enabling MARS (multiple active result sets)
116 Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
117 MARS Connection=True;
118 
119 
120 ------------------------------------------------------------------------------------
121 Encrypt data sent over network
122 Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
123 Encrypt=yes;
124 
125 
126 -------------------------------------------------------------------------------
127 Attach a database file on connect to a local SQL Server Express instance
128 Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname;
129 Trusted_Connection=Yes;
130 
131 
132 
133 
134 --------------------------------------------------------------------------------------------
135 Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
136 Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;
137 Database=dbname;Trusted_Connection=Yes;
138 
139 
140 
141 -------------------------------------------------------------------------------------------
142 
143 Database mirroring
144 Provider=SQLNCLI;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
145 Initial Catalog=myDataBase;Integrated Security=True;
146 
147 
148 
149 
150 
151 
152 -------------------------------------.NET Framework Data Provider for OLE DB--------------------------------------------------
153 --Type.NET Framework Wrapper Class Library
154 --UsageSystem.Data.OleDb.OleDbConnection
155 --ManufacturerMicrosoft
156 
157 
158 Bridging to SQL Native Client OLE DB
159 Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
160 Pwd=myPassword;
161 
162 
163 
164 ------------------------------------SQL Server Native Client 10.0 OLE DB Provider--------------------------------------------
165 --TypeOLE DB Provider
166 --UsageProvider=SQLNCLI10
167 --ManufacturerMicrosoft
168 
169 
170 Standard security
171 Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
172 Pwd=myPassword
173 
174 
175 ----------------------------------------------------------------------------
176 Trusted connection
177 Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
178 
179 
180 
181 ------------------------------------------------------------------------------------------
182 Connecting to an SQL Server instance
183 Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase;
184 Trusted_Connection=yes;
185 
186 
187 
188 ---------------------------------------------------------------------------------------
189 Prompt for username and password
190 oConn.Properties("Prompt") = adPromptAlways
191 oConn.Open "Provider=SQLNCLI10;
192 Server=myServerAddress;DataBase=myDataBase;
193 
194 
195 ----------------------------------------------------------------------------------------------------------
196 Enabling MARS (multiple active result sets)
197 Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
198 MARS Connection=True;
199 
200 
201 
202 ----------------------------------------------------------------------------------------
203 Encrypt data sent over network
204 Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
205 Encrypt=yes;
206 
207 
208 
209 -----------------------------------------------------------------------------------------------------------------
210 Attach a database file on connect to a local SQL Server Express instance
211 Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname;
212 Trusted_Connection=Yes;
213 
214 
215 
216 ----------------------------------------------------------------------------------------------------------------
217 Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
218 Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;
219 Database=dbname;Trusted_Connection=Yes;
220 
221 
222 ----------------------------------------------------------------------------------------------------------
223 Database mirroring
224 Provider=SQLNCLI10;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
225 Initial Catalog=myDataBase;Integrated Security=True;
226 
227 
228 
229 
230 ----------------------------------SQL Native Client 9.0 ODBC Driver----------------------------------------------------------
231 --TypeODBC Driver
232 --UsageDriver={SQL Native Client}
233 --ManufacturerMicrosoft
234 
235 Standard security
236 Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
237 Pwd=myPassword;
238 
239 ------------------------------------------------------------------------------------------------------------------
240 Trusted Connection
241 Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
242 Trusted_Connection=yes;
243 
244 
245 
246 ---------------------------------------------------------------------------------------------------------------------
247 Connecting to an SQL Server instance
248 Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase;
249 Trusted_Connection=yes;
250 
251 
252 
253 
254 --------------------------------------------------------------------------------------------------------------------
255 Prompt for username and password
256 oConn.Properties("Prompt") = adPromptAlways
257 Driver={SQL Native Client};
258 Server=myServerAddress;Database=myDataBase;
259 
260 
261 -------------------------------------------------------------------------------------------------------------------------
262 Enabling MARS (multiple active result sets)
263 Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
264 Trusted_Connection=yes;MARS_Connection=yes;
265 
266 
267 
268 -----------------------------------------------------------------------------------------------------------------------------
269 Encrypt data sent over network
270 Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
271 Trusted_Connection=yes;Encrypt=yes;
272 
273 
274 
275 
276 -----------------------------------------------------------------------------------------
277 Attach a database file on connect to a local SQL Server Express instance
278 Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;
279 Database=dbname;Trusted_Connection=Yes;
280 
281 
282 
283 
284 
285 -----------------------------------------------------------------------------------------
286 Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
287 Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;
288 Database=dbname;Trusted_Connection=Yes;
289 
290 
291 
292 
293 --------------------------------------------------------------------------------------------------
294 Database mirroring
295 Driver={SQL Server Native Client 10.0};Server=myServerAddress;
296 Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;
297 
298 
299 
300 ------------------------------------SQL Server Native Client 10.0 ODBC Driver-------------------------------
301 --TypeODBC Driver
302 --UsageDriver={SQL Server Native Client 10.0}
303 --ManufacturerMicrosoft
304 
305 
306 Standard security
307 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
308 Uid=myUsername;Pwd=myPassword;
309 
310 
311 --------------------------------------------------------------------------
312 Trusted Connection
313 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
314 Trusted_Connection=yes;
315 
316 
317 --------------------------------------------------------------------------------
318 Connecting to an SQL Server instance
319 Driver={SQL Server Native Client 10.0};Server=myServerName\theInstanceName;
320 Database=myDataBase;Trusted_Connection=yes;
321 
322 
323 
324 
325 
326 ------------------------------------------------------------------------------------------
327 Prompt for username and password
328 oConn.Properties("Prompt") = adPromptAlways
329 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
330 
331 
332 
333 ---------------------------------------------------------------------------------------------
334 Enabling MARS (multiple active result sets)
335 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
336 Trusted_Connection=yes;MARS_Connection=yes;
337 
338 
339 
340 ----------------------------------------------------------------------------------
341 Encrypt data sent over network
342 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
343 Trusted_Connection=yes;Encrypt=yes;
344 
345 
346 
347 
348 ---------------------------------------------------------------------------------------------------------
349 Attach a database file on connect to a local SQL Server Express instance
350 Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;
351 AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
352 
353 
354 --------------------------------------------------------------------------------------------------------
355 Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
356 Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;
357 AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
358 
359 
360 ----------------------------------------------------------------------------------------------
361 Database mirroring
362 Driver={SQL Server Native Client 10.0};Server=myServerAddress;
363 Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;
364 
365 
366 
367 
368 ------------------------------------------.NET Framework Data Provider for ODBC----------------------------------------------
369 --Type.NET Framework Wrapper Class Library
370 --UsageSystem.Data.Odbc.OdbcConnection
371 --ManufacturerMicrosoft
372 
373 Bridging to SQL Native Client 10.0 ODBC Driver
374 Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;
375 Uid=myUsername;Pwd=myPassword;
376 
377 
378 
379 
380 
381 
382 -------------------------------------SQLXML 4.0 OLEDB Provider---------------------------------------------------
383 --TypeOLE DB Provider
384 --UsageProvider=SQLXMLOLEDB.4.0;Data Provider=providername
385 --ManufacturerMicrosoft
386 
387 Using SQL Server Native Client provider
388 Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI;Data Source=myServerAddress;
389 Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
390 
391 
392 
393 -------------------------------Context Connection--------------------------------------------------------------
394 --Type.NET Framework Class Library
395 --Usage
396 --ManufacturerMicrosoft
397 
398 Context Connection
399 C#
400   using(SqlConnection connection = new SqlConnection("context connection=true"))
401   {
402       connection.Open();
403       // Use the connection
404   }
405 
406 VB.Net
407   Using connection as new SqlConnection("context connection=true")
408       connection.Open()
409       ' Use the connection
410   End Using
411 
412 
413 
414 --日常使用到的几个连接 OLEDB  ODBC SQLCLIENT
415 SQLCLIENT:
416 Standard Security alternative syntax  --美彬使用
417 Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;
418 Trusted_Connection=False;
419 
420 
421 
422 Database mirroring    --数据库镜像  要配置了两台数据库服务器为镜像先得,
423 --只是写字符串C#报错:未将服务器 joe、数据库 pratice 配置为用于数据库映像。
424 Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
425 Initial Catalog=myDataBase;Integrated Security=True;
426 
427 
428 
429 Standard Security alternative syntax  --加密
430 --需要SSL证书
431 --C#报错:已成功与服务器建立连接,但是在登录前的握手期间发生错误。
432 --(provider: SSL 提供程序, error: 0 - 证书链是由不受信任的颁发机构颁发的。)
433 Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;
434 Trusted_Connection=False; Encrypt=yes;
435 
436 
437 
438 
439 --TypeODBC Driver  --ODBC
440 --UsageDriver={SQL Native Client}
441 Standard security
442 Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
443 Pwd=myPassword;

 

posted @ 2013-08-02 22:19 桦仔 阅读(...) 评论(...)  编辑 收藏