Connection to IBM Db2

clutteredreverandΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 10 μήνες)

119 εμφανίσεις

Connection to IBM Db2



Imports

System

Imports

System.Data

Imports

IBM.Data.DB2


public

class

MainClass


Shared

Sub

Main()


Dim

connString

As

String

=

"database

=

databaseName;"



Dim

conn

As

New

DB2Connection(connString)



Try



conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Console.WriteLine(
"Connection

Properties"
)


Console.WriteLine(
"
-

ConnectionString

:

{0}"
,conn.ConnectionString)


Console.WriteLine(
"
-

Database

:

{0}"
,

conn.Database)



Console.WriteLine(
"
-

ServerVersion

:

{0}"
,conn.ServerVersion)


Console.WriteLine(
"
-

State

:

{0}"
,

conn.State)



Catch

ex

As

DB2Exception


Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


conn.Close()


C
onsole.WriteLine(
"Connection

Closed"
)



End

Try


End

Sub

End

Class







MySQL


MySQL Connection String



Imports

System

Imports

System.Data

Imports

MySql.Data.MySqlClient



Module

MySqlConnect



Sub

Main()


Dim

connString

As

String

=

"Database=Test;Data

Source=localhost;
User

Id=root;Password=mypassword"



Dim

conn

As

New

MySqlConnection(connString)



Try


conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Console.WriteLine(
"Connection

P
roperties"
)


Console.WriteLine(
"
-

ConnectionString

:

{0}"
,

conn.ConnectionString)


Console.WriteLine(
"
-

Database

:

{0}"
,

conn.Database)


Console.WriteLine(
"
-

DataSource

:

{0}"
,

conn.DataSource)


Console.WriteLine(
"
-

ServerVe
rsion

:

{0}"
,

conn.ServerVersion)


Console.WriteLine(
"
-

State

:

{0}"
,

conn.State)



Catch

ex

As

MySqlException


'

D
isplay

error


Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


'

C
lose

Connection


co
nn.Close()


Console.WriteLine(
"Connection

Closed"
)



End

Try



End

Sub


End

Module






MySql.Data.MySqlClient Demo



Imports

System

Imports

System.Data

Imports

MySql.Data.MySqlClient



Module

MySqlConnect



Sub

Main()


Dim

connString

As

String

=

"Database=Test;Data

Source=localhost;
User

Id=root;Password=mypassword"



Dim

conn

As

New

MySqlConnection(connString)



Try


conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Console.Wri
teLine(
"Connection

Properties"
)


Console.WriteLine(
"
-

ConnectionString

:

{0}"
,

conn.ConnectionString)


Console.WriteLine(
"
-

Database

:

{0}"
,

conn.Database)


Console.WriteLine(
"
-

DataSource

:

{0}"
,

conn.DataSource)


Console.W
riteLine(
"
-

ServerVersion

:

{0}"
,

conn.ServerVersion)


Console.WriteLine(
"
-

State

:

{0}"
,

conn.State)



Catch

ex

As

MySqlException


'

D
isplay

error


Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


'

C
lose

Co
nnection


conn.Close()


Console.WriteLine(
"Connection

Closed"
)



End

Try



End

Sub


End

Module




OBDC


Use command parameter for Odbc
connection



Imports

System

Imports

System.Data

Imports

System.Data.Odbc


Module

Com
mandOdbcExample



Sub

Main()


Dim

thisConnection

As

New

OdbcConnection

_


(
"dsn=MyOdbc"
)



Dim

nonqueryCommand

As

OdbcCommand

=

thisConnection.CreateComman
d()



Try


thisConnection.Open()


Console.WriteLine(
"Connect
ion

Opened"
)



nonqueryCommand.CommandText

=

"CREATE

TABLE

MyTable

"

&

_


"(MyName

VARCHAR

(30),

MyNumber

integer)"


Console.WriteLine(
"Executing

{0}"
,

_


nonqueryCommand.CommandText)


Console.WriteLine(
"Number

of

rows

affected

:

{0}"
,

_


nonqueryCommand.ExecuteNonQuery())



nonqueryCommand.CommandText

=

"INSERT

INTO

MyTable

VALUES

(?,

?)"



nonqueryCommand.Parameters.Add(
"@MyName"
,

OdbcType.VarChar,

3
0
)


nonqueryCommand.Parame
ters.Add(
"@MyNumber"
,

OdbcType.Int)



'

P
repare

command

not

supported

in

ODBC


'

n
onqueryCommand.Prepare()



'

D
ata

to

be

inserted


Dim

names()

As

String

=

{
"Z"
,

"S"
,

"J"
,

"D"
}


For

i

As

Integer

=

1

To

4


nonqueryCommand.Parameters(
"@MyName"
).Value

=

names(i

-

1
)


nonqueryCommand.Parameters(
"@MyNumber"
).Value

=

i


Console.WriteLine(
"Executing

{0}"
,

_


nonqueryCommand.CommandText)


Console.WriteLine(
"Number

of

r
ows

affected

:

{0}"
,

_


nonqueryCommand.ExecuteNonQuery())


Next

i



nonqueryCommand.CommandText

=

_


"SELECT

MyName,

MyNumber

FROM

MyTable"


Dim

thisReader

As

OdbcDataReader

=

nonqueryCommand.ExecuteRea
der(
)



While

(thisReader.Read())


Console.WriteLine(
"Name

and

Number:

{0}

{1}"
,

_


thisReader.GetValue(
0
),

thisReader.GetValue(
1
))


End

While



thisReader.Close()



nonqueryCommand.CommandText

=

"DROP

T
ABLE

MyTable"


nonqueryCommand.ExecuteNonQuery()



Catch

ex

As

OdbcException


'

D
isplay

error


Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


'

C
lose

Connection


thisConnection.Close()


Cons
ole.WriteLine(
"Connection

Closed"
)



End

Try


End

Sub

End

Module






Imports

System

Imports

System.Data

Imports

System.Data.Odbc


Module

OdbcProvider


Sub

Main()


'

S
et

up

connection

string


Dim

ConnString

As

String

=

"dsn=NorthwindO
dbc"



'

S
et

up

query

string


Dim

CmdString

As

String

=

"SELECT

*

FROM

employees"



'De
clare

Connection

and

DataReader

variables


Dim

Conn

As

OdbcConnection


Dim

Reader

As

OdbcDataReader



Try


'Op
en

Connection



Conn

=

New

OdbcConnection(ConnString)


Conn.Open()



'Ex
ecute

Query


Dim

Cmd

As

New

OdbcCommand(CmdString,

Conn)


Reader

=

Cmd.ExecuteReader()



'Di
splay

output

header


Console.WriteLine(
"Querying

database

{0}

with

query

{1}"

&

_


ControlChars.NewLine,

Conn.Database,

Cmd.CommandText)


Console.WriteLine(
"FirstName"

&

ControlChars.Tab

&

"LastName"
)



'Pr
ocess

The

Result

Set


While

(Reader.Read())


Console.WriteLine
(Reader(
"FirstName"
).PadLeft(
9
)

&

_


ControlChars.Tab

&

Reader(
1
))


End

While



Catch

ex

As

Exception


Console.WriteLine(
"Error:

{0}"
,

ex)



Finally


'Cl
ose

Connection


Reader.Close()


Conn.Cl
ose()



End

Try



End

Sub

End

Module


OleDb SQL server Express Connection
string



Imports

System

Imports

System.Collections

Imports

System.Data

Imports

System.IO

Imports

System.Xml.Serialization

Imports

System.Xml

Imports

System.Windows.Forms

Im
ports

System.Data.SqlClient

Imports

System.Data.OleDb


Public

Class

MainClass



Shared

Sub

Main()


Dim

form1

As

Form

=

New

Form1


Application.Run(form1)


End

Sub

End

Class







Public

Class

Form1


Inherits

System.Windows.Forms.Form


#Region

"

Windows

Form

Designer

generated

code

"



Public

Sub

New()


MyBase.New()



'Th
is

call

is

required

by

the

Windows

Form

Designer.


InitializeComponent()



'Ad
d

any

initialization

after

the

InitializeComponent()

call



End

Sub



'Fo
rm

overrides

dispose

to

clean

up

the

component

list.


Protected

Overloads

Overrides

Sub

Dispose(ByVal

disposing

As

Boole
an)


If

disposing

Then


If

Not

(components

Is

Nothing)

Then


components.Dispose(
)


End

If


End

If


MyBase.Dispose(disposing)


End

Sub



'Re
quired

by

the

Windows

Form

Designer


Private

components

As

System.ComponentModel.IContainer



'NO
TE:

The

following

procedure

is

required

by

the

Windows

Form

Des
igner


'It

can

be

modified

using

the

Windows

Form

Designer.



'Do

not

modify

it

using

the

code

editor.


Friend

WithEvents

Label2

As

System.Windows.Forms.Label


Friend

WithEvents

OrderDetailsList

As

System.Windows.Forms.ListVie
w


Friend

With
Events

Label1

As

System.Windows.Forms.Label


Friend

WithEvents

Button1

As

System.Windows.Forms.Button


Friend

WithEvents

OrderIDsList

As

System.Windows.Forms.ListBox


<System.Diagnostics.DebuggerStepThrough()>

Private

Sub

InitializeC
omponent()



Me.Label2

=

New

System.Windows.Forms.Label()


Me.OrderDetailsList

=

New

System.Windows.Forms.ListView()


Me.Label1

=

New

System.Windows.Forms.Label()


Me.Button1

=

New

System.Windows.Forms.Button()


Me.OrderIDsList

=

New

Sy
stem.Windows.Forms.ListBox()


Me.SuspendLayout()


'


'La
bel2


'


Me.Label2.Location

=

New

System.Drawing.Point(
232
,

8
)


Me.Label2.Name

=

"Label2"


Me.Label2.Size

=

New

System.Drawing.Size(
216
,

23
)


Me
.Label2.TabIndex

=

9


Me.Label2.Text

=

"Order

Details"


'


'Or
derDetailsList


'


Me.OrderDetailsList.Location

=

New

System.Drawing.Point(
232
,

3
2
)


Me.OrderDetailsList.Name

=

"OrderDetailsList"


Me.OrderDetai
lsList.Size

=

New

System.Drawing.Size(
352
,

360
)


Me.OrderDetailsList.TabIndex

=

8


'


'La
bel1


'


Me.Label1.Location

=

New

System.Drawing.Point(
8
,

8
)


Me.Label1.Name

=

"Label1"


Me.Label1.Size

=

New

System.D
rawing.Size(
216
,

23
)


Me.Label1.TabIndex

=

7


Me.Label1.Text

=

"Order

IDs"


'


'Bu
tton1


'


Me.Button1.Location

=

New

System.Drawing.Point(
504
,

400
)


Me.Button1.Name

=

"Button1"


Me.Button1.TabIndex

=

6


Me.Button1.Text

=

"Start"


'


'Or
derIDsList


'


Me.OrderIDsList.ItemHeight

=

16


Me.OrderIDsList.Location

=

New

System.Drawing.Point(
8
,

32
)


Me.OrderIDsList.Name

=

"OrderIDsList"


Me.OrderIDsList.
Size

=

New

System.Drawing.Size(
216
,

356
)


Me.OrderIDsList.TabIndex

=

5


'


'Fo
rm1


'


Me.AutoScaleBaseSize

=

New

System.Drawing.Size(
6
,

15
)


Me.ClientSize

=

New

System.Drawing.Size(
592
,

432
)


Me.Controls.Add
Range(New

System.Windows.Forms.Control()

{Me.La
bel2,

Me.OrderDetailsList,

Me.Label1,

Me.Button1,

Me.OrderIDsList})


Me.Name

=

"Form1"


Me.Text

=

"Form1"


Me.ResumeLayout(False)



End

Sub


#
End

Region



Private

Const

ConnString

As

String

=

_


"Provider=SQLOLEDB;Data

Source=(local)
\
SQLEXPRESS;Initial

Cata
log=Northwind;Integrated

Security=SSPI;"


Private

Connection

As

OleDbConnection

=

New

OleDbConnection(ConnSt
ring)



Private

ColumnsSet

As

Boolean

=

False



Private

S
ub

Button1_Click(ByVal

sender

As

System.Object,

ByVal

e

A
s

System.EventArgs)

Handles

Button1.Click


Dim

command

As

OleDbCommand

=

_


New

OleDbCommand(
"SELECT

OrderID

FROM

Orders"
,

Connection)



Connection.Open()


Dim

reader

As

OleDbDataReader

=

command.ExecuteReader()


Do

While

(reader.Read())


OrderIDsList.Items.Add(reader.GetInt32(
0
))


Loop


reader.Close()


Connection.Close()


End

Sub



Private

Sub

OrderIDsList_SelectedIndexChang
ed(ByVal

sender

As

Obje
ct,

ByVal

e

As

System.EventArgs)

Handles

OrderIDsList.SelectedIndexCha
nged


Dim

OrderID

As

Integer

=

Convert.ToInt32(OrderIDsList.Selected
Item)


Dim

StoredProcCommand

As

OleDbCommand

=

New

OleDbCommand(
"Cust
OrdersDetail
"
,

Connection)


With

StoredProcCommand


.CommandType

=

CommandType.StoredProcedure


.Parameters.Add(
"@OrderID"
,

OrderID)


End

With



Connection.Open()


Dim

RowList

As

ArrayList

=

New

ArrayList()


Dim

reader

As

OleDbDataReader

=

StoredProcCommand.ExecuteReade
r()


Do

While

reader.Read()


Dim

Values(reader.FieldCount)

As

Object


reader.GetValues(Values)


RowList.Add(Values)


Loop



If

Not

ColumnsSet

T
hen


Dim

schema

As

DataTable

=

reader.GetSchemaTable()


SetColumnHeaders(schema)


End

If


reader.Close()


Connection.Close()



PopulateOrderDetails(RowList)


End

Sub



Private

Sub

SetColumnHeaders(ByV
al

schema

As

DataTable)


Dim

row

As

DataRow



OrderDetailsList.View

=

View.Details


For

Each

row

In

schema.Rows


OrderDetailsList.Columns.Add(row(
"ColumnName"
),

100
,

Horiz
ontalAlignment.Left)


Next


ColumnsSet

=

True


End

Sub



Private

Sub

PopulateOrderDetails(ByVal

RowList

As

ArrayList)


OrderDetailsList.Items.Clear()



Dim

row

As

Object()


For

Each

row

In

RowList


Dim

OrderDetails(row.Length)

As

String


Dim

col

As

Object


Dim

ColIdx

As

Integer



For

ColIdx

=

0

To

row.Length

-

1


OrderDetails(ColIdx)

=

Convert.ToString(row(ColIdx))


Next



Dim

NewItem

As

ListViewItem

=

New

ListViewItem(OrderDetail
s)



OrderDetailsList.Items.Add(NewItem)


Next


End

Sub

End

Class







Connect to a Access mdb file directly
using OleDbConnection




Imports

System

Imports

System.Xml

Imports

System.Xml.Schema

Imports

System.IO

Imports

System.Data.OleDb

Imports

System.Collections

Imports

System.Data



Public

Class

MainClass




Shared

Sub

Main()


Try


'

D
efine

a

connection

object


Dim

dbConn

As

New

OleDbConnection(
"Provider=Microsoft.Jet.
OLEDB.4.0;Password=;User

ID=Admin;Data

Source=Employee.mdb"
)



'

C
reate

a

data

adapter

to

retrieve

records

from

db


Dim

st
rSELECT

As

String

=

"SELECT

ID

AS

EmployeeID,

FirstN
ame

AS

FirstName,

LastName

AS

LastName

FROM

Employee"


Dim

daUsers

As

New

OleDbDataAdapter(strSELECT,

dbConn)


Dim

dsUsers

As

New

DataSet(
"Employee"
)



'

F
ill

the

dataset


daUsers.Fill(dsUsers)



'

G
o

through

the

records

and

print

them

using

the

mapped

n
ames


Dim

r

As

DataRow


For

Each

r

In

dsUsers.Tables(
0
).Rows


Console.WriteLine(
"ID:

{0},

FirstName:

{1},

LastName
:

{2}"
,

r(
"EmployeeID"
),

r(
"FirstName"
),

r(
"LastName"
))


Next


Catch

ex

As

Exception



'

A
n

error

occurred.

Show

the

error

message


Console.WriteLine(ex.Message)


End

Try


End

Sub

End

Class



ORACLE


Oracl
e SqlConnect



Imports

System

Imports

System.Data

Imports

System.Data.OracleClient


Module

OracleConnect



Sub

Main()


Dim

connString

As

String

=

"server

=

o92;

uid

=

java2s;password

=

password;"



'Cr
eate

connection


Dim

conn

As

New

Or
acleConnection(connString)



Try


conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Console.WriteLine(
"Connection

Properties"
)


Console.WriteLine(
"
-

ConnectionString

:

{0}"
,

conn.ConnectionString)


Console.W
riteLine(
"
-

ServerVersion

:

{0}"
,

conn.ServerVersion)


Console.WriteLine(
"
-

State

:

{0}"
,

conn.State)



Catch

ex

As

OracleException


Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


'

C
lose

Connection


conn.C
lose()


Console.WriteLine(
"Connection

Closed"
)



End

Try



End

Sub


End

Module


Oracle Connection String



Imports

System

Imports

System.Data

Imports

System.Data.OracleClient


Module

OracleConnect



Sub

Main()


Dim

connString

As

S
tring

=

"server

=

o92;

uid

=

java2s;password

=

password;"



'Cr
eate

connection


Dim

conn

As

New

OracleConnection(connString)



Try


conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Console.WriteLine(
"Connection

P
roperties"
)


Console.WriteLine(
"
-

ConnectionString

:

{0}"
,

conn.ConnectionString)


Console.WriteLine(
"
-

ServerVersion

:

{0}"
,

conn.ServerVersion)


Console.WriteLine(
"
-

State

:

{0}"
,

conn.State)



Catch

ex

As

OracleException



Console.WriteLine(
"Error:

"

&

ex.ToString())


Finally


'

C
lose

Connection


conn.Close()


Console.WriteLine(
"Connection

Closed"
)



End

Try



End

Sub


End

Module





SQL


Connection without User Name and
Password




Imports

System

Imports

System.Data

Imports

System.Data.SqlClient


Module

Connection_Sql



Sub

Main()


Dim

connString

As

String

=

"server=(local)
\
SQLEXPRESS;

"

&

_


"integrated

security=true;"



Dim

conn

As

New

SqlConnection(connString)



Try


conn.Open()


Console.WriteLine(
"Connection

Opened"
)



Catch

ex

As

SqlException


Con
sole.WriteLine(
"Error:

"

&

ex.ToString())


Finally


conn.Close()


Console.WriteLine(
"Connection

Closed"
)


End

Try


End

Sub

End

Module


SqlConnection Connection String for
SQL server Express




Imports

System

Imports

System.Collections

Imports

System.Data

Imports

System.IO

Imports

System.Xml.Serialization

Imports

System.Windows.Forms

Imports

System.D
ata.SqlClient



Public

Class

MainClass


Shared

Dim

WithEvents

con

As

SqlConnection



Shared

Sub

Main()


con

=

New

SqlConnection(
"Server=(local)
\
SQLEXPRESS;Initial

Cat
alog=master;Integrated

Security=SSPI"
)




Dim

cmd

As

New

SqlCommand()


cmd.CommandText

=

"PRINT('This

raises

an

InfoMessage

event')"


cmd.Connection

=

con



Try


con.Open()


cmd.ExecuteNonQuery()


Finally


con.Close()


End

Try


End

Sub



Shared

Sub

con
_InfoMessage(ByVal

Sender

As

Object,

_


ByVal

E

As

SqlInfoMessageEventArgs)

Handles

con.InfoM
essage


Console.WriteLine(

_


"{0}

-

{1}"
,

"InfoMessage"
,

E.Errors.Item(
0
).ToString())


End

Sub

End

Class


SqlConnecti
on Connection String for
SQL server Express




Imports

System

Imports

System.Collections

Imports

System.Data

Imports

System.
IO

Imports

System.Xml.Serialization

Imports

System.Windows.Forms

Imports

System.Data.SqlClient



Public

Class

MainClass


Shared

Dim

WithEvents

con

As

SqlConnection



Shared

Sub

Main()


con

=

New

SqlConnection(
"Server=(local)
\
SQLEXPRESS;Initial

Cat
alog=master;Integrated

Security=SSPI"
)




Dim

cmd

As

New

SqlCommand()


cmd.CommandText

=

"PRINT('This

raises

an

InfoMessage

event')"


cmd.Connection

=

con



Try


con.Open()


cmd.ExecuteNonQuery()



Finally


con.Close()


End

Try


End

Sub



Shared

Sub

con_InfoMessage(ByVal

Sender

As

Object,

_


ByVal

E

As

SqlInfoMessageEventArgs)

Handles

con.InfoM
essage


Console.WriteLine(

_


"{0}

-

{1}"
,

"InfoMessage"
,

E.Errors.Item(
0
).ToString())


End

Sub

End

Class




Info Message Event




Imports

System

Imports

System.Collections

Imports

System.Data

Imports

System.IO

Imports

System.Xml.Serialization

Imports

System.Windows.Forms

Imports

System.Data.SqlClient



Public

Class

MainClass


Shared

Dim

WithEvents

con

As

SqlConnection



Shared

Sub

Main()


con

=

New

SqlConnection(
"Server=(local)
\
SQL
EXPRESS;Initial

Cat
alog=master;Integrated

Security=SSPI"
)




Dim

cmd

As

New

SqlCommand()


cmd.CommandText

=

"PRINT('This

raises

an

InfoMessage

event')"


cmd.Connection

=

con



Try


con.Open()


cmd.ExecuteNo
nQuery()


Finally


con.Close()


End

Try


End

Sub



Shared

Sub

con_InfoMessage(ByVal

Sender

As

Object,

_


ByVal

E

As

SqlInfoMessageEventArgs)

Handles

con.InfoM
essage


Console.WriteLine(

_



"{0}

-

{1}"
,

"InfoMessage"
,

E.Errors.Item(
0
).ToString())


End

Sub

End

Class


Connection state change event




Imports

System

Imports

Syste
m.Collections

Imports

System.Data

Imports

System.IO

Imports

System.Xml.Serialization

Imports

System.Xml

Imports

System.Windows.Forms

Imports

System.Data.SqlClient



Public

Class

MainClass


Shared

Dim

WithEvents

con

As

SqlConnection



Shared

Sub

Main(
)


con

=

New

SqlConnection(
"Server=(local)
\
SQLEXPRESS;Initial

Catalog
=MyDatabase;Integrated

Security=SSPI"
)




Dim

cmd

As

New

SqlCommand()


cmd.CommandText

=

"SELECT

TOP

5

FirstName,

LastName

FROM

Employee"


cmd.Connection

=

con



Try


con.Open()



Dim

reader

As

SqlDataReader

=

cmd.ExecuteReader()


While

reader.Read()


Console.WriteLine(

_


"{0}

-

{1}"
,

reader.GetString(
0
),

reader.GetString(
1
))



End

While


reader.Close()


Finally


con.Close()


End

Try


End

Sub



Shared

Sub

con_StateChange(ByVal

Sender

As

Object,

_


ByVal

E

As

StateChangeEventArgs)

Handles

con.StateChange


Console.
WriteLine(

_


"{0}

-

{1}"
,

"ConnectionState"
,

E.CurrentState.ToString())


End

Sub

End

Class