VB.Net and Sql Server Express Compact

  • Thread starter Thread starter Fred Blair
  • Start date Start date
F

Fred Blair

I know this is off subject, but I can't get any responses from
Microsoft.public.sqlserver.ce.

I have tried several times to download and install SQL Server Express
Compact, and don't get any errors when it installs.

The problem is I can't find it. Where do I look to see if it is running. I
know inside VB, that it does not list it when I make a new connection.

Thanks for any assistance.
Fred
 
Fred,

Are you writing about
SqlServer.Server Express (version?)
or
SqlServer Compact Edition (CE)

Cor
 
Fred,

Are you writing about
SqlServer.Server Express (version?)
or
SqlServer Compact Edition (CE)

Cor
 
You don't find it. You add a project reference to System.Data.SqlServerCe
and use the database objects in the namespace System.Data.SqlServerCe. Then
you set the connection string to point at the database file and you're good
to go.

An Example:

Dim sqlConn As New SqlCeConnection("Data
Source=|DataDirectory|\yourdatabase.sdf;Password='yourpassword';encrypt
database=TRUE")
'Or you can explicitly set the full path to the db file without
using |DataDirectory|
Dim sqlCommand As New SqlCeCommand("", sqlConn)
Dim sqlReader As SqlCeDataReader = Nothing

Try
If sqlConn.State <> ConnectionState.Open Then
sqlConn.Open()
End If
Catch ex As Exception
Throw New Exception("Couldn't open database connection." &
Environment.NewLine & Environment.NewLine & "The connection string is: '" &
sqlConn.ConnectionString & "'." & Environment.NewLine & Environment.NewLine
& "The error was:" & ex.Message)
End Try

sqlCommand.CommandText = "UPDATE tblFoo SET bBar=1 WHERE ... your
condition ..."
sqlCommand.ExecuteNonQuery()

sqlCommand.CommandText = "SELECT bBar FROM tblFoo WHERE ... your
condition ..."

sqlReader = sqlCommand.ExecuteReader

While sqlReader.Read
Try
bBar = sqlReader.GetBoolean(0)
Catch ex As Exception
bBar = False
End Try
End While

If Not sqlReader.IsClosed Then sqlReader.Close()
sqlReader = Nothing
sqlCommand = Nothing
If sqlConn.State = ConnectionState.Open Then sqlConn.Close()
sqlConn = Nothing


As for distributing the SQL Server CE there are two ways to go:

1) you include the complete setup package from Microsoft in your application
setup

OR

2) You simply distribute the following dlls with your app. If you have those
in a path where the app can find them (e.g. the app installation directory),
there is no need for further installation of the SQL Server CE (one of the
big advantages of CE).

sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll
System.Data.SqlServerCe.dll

This is for SQL Server CE version 3.5 - if you're using the older 3.1, of
course the dll's will be called something with 31 at the end.

Good luck (shouldn't be needed though - this is very easy when you know
how),
Johnny J.
 
You don't find it. You add a project reference to System.Data.SqlServerCe
and use the database objects in the namespace System.Data.SqlServerCe. Then
you set the connection string to point at the database file and you're good
to go.

An Example:

Dim sqlConn As New SqlCeConnection("Data
Source=|DataDirectory|\yourdatabase.sdf;Password='yourpassword';encrypt
database=TRUE")
'Or you can explicitly set the full path to the db file without
using |DataDirectory|
Dim sqlCommand As New SqlCeCommand("", sqlConn)
Dim sqlReader As SqlCeDataReader = Nothing

Try
If sqlConn.State <> ConnectionState.Open Then
sqlConn.Open()
End If
Catch ex As Exception
Throw New Exception("Couldn't open database connection." &
Environment.NewLine & Environment.NewLine & "The connection string is: '" &
sqlConn.ConnectionString & "'." & Environment.NewLine & Environment.NewLine
& "The error was:" & ex.Message)
End Try

sqlCommand.CommandText = "UPDATE tblFoo SET bBar=1 WHERE ... your
condition ..."
sqlCommand.ExecuteNonQuery()

sqlCommand.CommandText = "SELECT bBar FROM tblFoo WHERE ... your
condition ..."

sqlReader = sqlCommand.ExecuteReader

While sqlReader.Read
Try
bBar = sqlReader.GetBoolean(0)
Catch ex As Exception
bBar = False
End Try
End While

If Not sqlReader.IsClosed Then sqlReader.Close()
sqlReader = Nothing
sqlCommand = Nothing
If sqlConn.State = ConnectionState.Open Then sqlConn.Close()
sqlConn = Nothing


As for distributing the SQL Server CE there are two ways to go:

1) you include the complete setup package from Microsoft in your application
setup

OR

2) You simply distribute the following dlls with your app. If you have those
in a path where the app can find them (e.g. the app installation directory),
there is no need for further installation of the SQL Server CE (one of the
big advantages of CE).

sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll
System.Data.SqlServerCe.dll

This is for SQL Server CE version 3.5 - if you're using the older 3.1, of
course the dll's will be called something with 31 at the end.

Good luck (shouldn't be needed though - this is very easy when you know
how),
Johnny J.
 
Back
Top