笔记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;
