c#连接数据库代码集合

2021年2月15日发(作者：哈里里)

SQL

Server

ODBC

standard

Security:

< br>

Server};Server=Aron1;Database=pubs;U id=sa;Pwd=asdasd;

trusted

connection:

Server};Server=Aron1;Database=pubs; Trusted_Connection=ye

s;

prompt

for

username

and

password:

ties(

=

adPromptAlways

< /p>

Server};Server=Aron1;DataBase=pubs;

< p>

OLE

DB,

OleDbConnection

(.NET)

standard

Security:

Source=Aron1;Initial

Catalog=pubs;User

Id=sa;Pass

word=asdasd;

trusted

Connection:

Source=Aron1;Initial

Catalog=pubs;Integrated

Secu

rity=SSPI;

(use

serverNameinstanceName

as

Data

Source

to

use

an

specifik

SQLServe

r

instance,

only

SQLServer2000)

prompt

for

username

and

password:

er

=

ties(

=

adPromptAlways

Source=Aron1;Initial

Catalog=pubs;

connect

via

an

IP

address:

< p>
Source=190.190.200.100,1433;Network

Library=DB

MSSOCN;Initial

Catalog=pubs;User

ID=sa;Password=asdasd;

(dbmssocn=tcp/ip

instead

of

Named

Pipes,

at

the

end

of

the

Data

Source

is

the

port

to

use

(1433

is

the

default))

SqlConnection

(.NET)

standard

Security:

Source=Aron1;Initial

Catalog=pubs;User

Id=sa;Password=asdasd;

-

or

-

ID=sa;Password= asdasd;Trusted_Conne

ction=False

< br>

(booth

connection

strings

produces

the

same

result)

trusted

Connection:

Source=Aron1;Initial

Catalog=pubs;Integrated

Security=SSPI;

-

or

-

(booth

connection

strings

produces

the

same

result)

(use

serverNameinstanceName

as

Data

Source

to

use

an

specifik

SQLServe

r

instance,

only

SQLServer2000)

connect

via

an

IP

address:

Source=1 90.190.200.100,1433;Network

Library=DBMSSOCN;Initial

Ca

talog=pubs;User

ID=sa;Password=asdasd;

(dbmssocn=tcp/ip

instead

of

Named

Pipes,

at

the

end

of

the

Data

Source

is

the

port

to

use

(1433

is

the

default))

declare

the

SqlConnection:

C#:

using

ent;

SqlConnection

oSQLConn

=

new

SqlConnection();

tionString=

connectionstring

();

:

Imports

ent

Dim

oSQLConn

As

SqlConnection

=

New

SqlConnection()

< br>tionString=

connectionstring

()

Data

Shape

ms

Data

Shape

Provider=SQLOLEDB;Data

Source=Aron1;Initia

l

Catalog=pubs;User

ID=sa;Password=asdasd;

want

to

learn

data

shaping?

Check

out

4GuyfFromRolla's

great

article

about

Data

Shaping

>>

Read

more

How

to

define

which

network

protocol

to

use

Example:

Source=1 90.190.200.100,1433;Network

Library=DB

MSSOCN;Initial

Catalog=pubs;User

ID=sa;Password=asdasd;

Name

Network

library

dbnmpntw

Win32

Named

Pipes

dbmssocn

Win32

Winsock

TCP/IP

dbmsspxn

Win32

SPX/IPX

dbmsvinn

Win32

Banyan

Vines

dbmsrpcn

Win32

Multi-Protocol

(Windows

RPC)

important

note!

When

connecting

through

the

SQLOLEDB

provider

use

the

syntax

Network

L

ibrary=dbmssocn

and

when

connecting

through

MSDASQL

provider

use

the

syntax

Network=d

bmssocn

All

SqlConnection

connectionstring

properties

This

table

shows

all

connectionstring

properties

for

the

SqlConnect

ion

object.

Most

of

the

properties

are

also

used

in

ADO.

All

properties

and

descriptions

is

from

msdn.

Name

Default

Description

Application

Name

The

name

of

the

application,

or

'.Net

SqlClient

Data

Pro

vider'

if

no

application

name

is

provided.

AttachDBFilename

-or-

extended

properties

-or-

Initial

File

Name

The

name

of

the

primary

file,

including

the

full

path

nam

e,

of

an

attachable

database.

The

database

name

must

be

specified

with

th

e

keyword

'database'.

Connect

Timeout

-or-

Connection

Timeout

15

The

length

of

time

(in

seconds)

to

wait

for

a

conne

ction

to

the

server

before

terminating

the

attempt

and

generating

an

error.

Connection

Lifetime

0

When

a

connection

is

returned

to

the

pool,

its

creatio

n

time

is

compared

with

the

current

time,

and

the

connection

is

destroyed

if

that

time

span

(in

seconds)

exceeds

the

value

specified

by

connection

life

time.

Useful

in

clustered

configurations

to

force

load

balancing

between

a

r

unning

server

and

a

server

just

brought

on-line.

Connection

Reset

'true'

Determines

whether

the

database

connection

is

rese

t

when

being

removed

from

the

pool.

Setting

to

'false'

avoids

making

an

a

dditional

server

round-trip

when

obtaining

a

connection,

but

the

programme

r

must

be

aware

that

the

connection

state

is

not

being

reset.

Current

Language

The

SQL

Server

Language

record

name.

Data

Source

-or-

Server

-or-

Address

-or-

Addr

-or-

Network

Address

The

name

or

network

address

of

the

instance

of

SQL

Ser

ver

to

which

to

connect.

Enlist

'true'

When

true,

the

pooler

automatically

enlists

the

connection

in

th

e

creation

thread's

current

transaction

context.

Initial

Catalog

-or-

Database

The

name

of

the

database.

Integrated

Security

-or-

Trusted_Connection

'false'

Whether

the

connection

is

to

be

a

secure

connec

tion

or

not.

Recognized

values

are

'true',

'false',

and

'sspi',

which

is

equival

ent

to

'true'.

Max

Pool

Size

100

The

maximum

number

of

connections

allowed

in

the

poo

l.

Min

Pool

Size

0

The

minimum

number

of

connections

allowed

in

the

pool.

Network

Library

-or-

Net

'dbmssocn'

The

network

library

used

to

establish

a

connection

to

an

ins

tance

of

SQL

Server.

Supported

values

include

dbnmpntw

(Named

Pipes),

d

bmsrpcn

(Multiprotocol),

dbmsadsn

(Apple

Talk),

dbmsgnet

(VIA),

dbmsipcn

(Shared

Memory)

and

dbmsspxn

(IPX/SPX),

and

dbmssocn

(TCP/IP).

The

corresponding

network

DLL

must

be

installed

on

the

system

to

which

y

ou

connect.

If

you

do

not

specify

a

network

and

you

use

a

local

server

(for

example,

or

shared

memory

is

used.

Packet

Size

8192

Size

in

bytes

of

the

network

packets

used

to

communicat

e

with

an

instance

of

SQL

Server.

Password

-or-

Pwd

The

password

for

the

SQL

Server

account

logging

on.

Persist

Security

Info

'false'

When

set

to

'false',

security-sensitive

informatio

n,

such

as

the

password,

is

not

returned

as

part

of

the

connection

if

the

co

nnection

is

open

or

has

ever

been

in

an

open

state.

Resetting

the

connecti

on

string

resets

all

connection

string

values

including

the

password.

Pooling

'true'

When

true,

the

SQLConnection

object

is

drawn

from

the

appr

opriate

pool,

or

if

necessary,

is

created

and

added

to

the

appropriate

pool.

User

ID

The

SQL

Server

login

account.

Workstation

ID

the

local

computer

name

The

name

of

the

workstation

conn

ecting

to

SQL

Server.

note

Use

;

to

separate

each

property.

If

a

name

occurs

more

than

once,

the

value

from

the

last

one

in

the

conne

ctionstring

will

be

used.

If

you

are

building

your

connectionstring

in

your

app

using

values

from

use

r

input

fields,

make

sure

the

user

can't

change

the

connectionstring

by

inse

rting

an

additional

property

with

another

value

within

the

user

value.

Access

ODBC

standard

Security:

Access

Driver

(*.mdb)};Dbq=C:;Uid=A

dmin;Pwd=;

workgroup:

Access

Driver

(*.mdb)};Dbq=C:;Syste

mDB=C:;

exclusive:

Access

Driver

(*.mdb)};Dbq=C:;Exclusi

ve=1;Uid =admin;Pwd=

OLE

DB,

OleDbConnection

(.NET)

standard

security:

Source=;User

Id=admin;Password=;

workgroup

(system

database):

Source=;Jet

O

LEDB:System

Database=;

with

password:

Source=;Jet

O

LEDB:Database

Password=MyDbPassword;

Oracle

ODBC

new

version:

ODBC

for

< br>Oracle};Server=;Uid=Userna

me;Pwd=asd asd;

old

version:

ODBC

Driver

for

Oracle };ConnectString=

ld;Uid=myUsername;Pwd=m yPassword;

OLE

DB,

OleDbConnection

(.NET)

standard

security:

Source=MyOracleDB;User

< p>
Id=UserName;Password=

asdasd;

< p>

this

one's

from

Microsoft,

the

following

are

from

Oracle

standard

Security:

Source=MyOracleDB;User

< p>
Id=Username;Pa

ssword=asdasd;

< p>

trusted

Connection:

Sourc e=MyOracleDB;OSAuthent=1;

OracleConnection

(.NET)

standard:

Source=MyOracleDB;Integrated

Security=yes;

this

one

works

only

with

Oracle

8i

release

3

or

later

specifying

username

and

password:

Source=MyOracleDB;User

Id=username;Password=passwd;Integrated

Security=no;

this

one

works

only

with

Oracle

8i

release

3

or

later

declare

the

OracleConnection:

C#:

using

Client;

OracleConnection

oOracleConn

=

new

OracleConnection();

tionString

=

connectionstring

();

:

Imports

Client

Dim

oOracleConn

As

OracleConnection

=

New

OracleConnection()

tionString

=

connectionstring

()

missing

the

Client

namespace?

Download .NET

Managed

Provider

for

Oracle

?

great

article!

of

Oracle

Data

Provider

for .NET

by

Rama

Mohan

G.

at

C#

Corner

Core

Labs

OraDirect

(.NET)

standard:

ID=scott;

Password=tiger;

Host=ora;

Pooling=true;

Min

Pool

Size=0;

Max

Pool

Size=100;

Connection

Lifetime=0

Read

more

at

Core

Lab

and

the

product

page.

Data

Shape

ms

Data

Shape:

Security

Info=False;Data

Provider=MSDAO

RA;Data

Source=orac;user

id=username;password=mypw

want

to

learn

data

shaping?

Check

out

4GuyfFromRolla's

great

article

about

Data

Shaping

>>

MySQL

ODBC

odbc

2.50

Local

database:

odbc

2.50

Remote

database:

=;Database=my- database;Uid=username;Pwd=password;

odbc

3.51

Local

database:

ODBC

3.51

Driver};SERVER=localhost;DATABASE=myDat

abase;USER=myUsername;PASSWORD=myPassword;OPTI ON=3;

odbc

3.51

Remote

database:

ODBC

3.51

Driver};SERVER=;PORT=33 0

6;DATABASE=myDatabase;USER=myUsername; PASSWORD=myPassword;OP

TION=3;

OLE

DB,

OleDbConnection

(.NET)

standard:

Source=mydb;User

Id=UserName;Password=asd

asd;

MySqlConnection

(.NET)

ider:

< br>

Source=server;Database=mydb;User

ID=username;Password=pwd;Co

mman d

Logging=false

this

one

is

used

with

eInfoDesigns

dbProvider,

an

add-on

to .NET

declare

the

MySqlConnection:

C#:

using

lient;

MySqlConnection

oMySqlConn

=

new

MySqlConnection();

tionString

=

connectionstring

();

:

Imports

lient

Dim

oMySqlConn

As

MySqlConnection

=

New

MySqlConnection()

tionString

=

connectionstring

()

SevenObjects

MySqlClient

(.NET)

standard:

UserName=myusername;

Password=mypassword;Database=m

ydb;< /p>

this

is

a

freeware

data

provider

from

SevenObjects

Core

Labs

MySQLDirect

(.NET)

standard:

ID=root;

Password=pwd;

Host=localhost;

Port=3306;

Database=test;

Direct=true;

Protocol=TCP;

Compress=false;

Pooling=true;

Min

Pool

Size=0;

Max

Pool

Size=100;

Connection

Lifetime=0

Read

more

at

Core

Lab

and

the

product

page.

Interbase

ODBC,

Easysoft

local

computer: