Upgrade from OleDb to SqlCLient

[VBOfficer]

Hi, I want to upgrade from OleDb to SqlCLient, I mean from .mdb file to .sdf file.

I have installed MS SQL Server 2008 Express Compact edition and designed my database files and created tables/fields and now gonna copy it to my application directory so my application will work with that on TARGET systems, WITHOUT having SQL Server installed on target systems...

 

Well, my old connection string as follows:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\CompanyName\ProductName\Database.mdb;Jet OLEDB:Database Password = secret"

Now, what should be my new connection string for SQL 2008?

Here I found:

http://www.connectionstrings.com/sql-server-2008#1

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

But what's server address and myUsername?

How should I specify the path to my database in my application folder?

SQL Server won't be installed on target systems, so what to do?

Please help me if you can... :(

Thanks all...

[tfowler]

Not that I've used it much, but it looks like all you need to do is specify the Data Source (path to the file) and Password (if it is password protected). You also may need to add a reference to "System.Data.SqlServerCe". You can then access your database through a SqlServerCe.SqlCeEngine object:

 

Code:

Dim connection As New SqlServerCe.SqlCeConnection("Data Source=C:\MySqlServerCompactDb.sdf;Password=password")

Dim command As New SqlServerCe.SqlCeCommand("SELECT * FROM MyTable", connection)

connection.Open()

Dim results As SqlServerCe.SqlCeDataReader

results = command.ExecuteReader(CommandBehavior.CloseConnection)

 

 

or something like that.

 

Todd

[PlausiblyDamp]

You could use a connection string similar to

Data Source=|DataDirectory|\.sdf

if you are using the compact edition then this will default to the same folder as the .exe anyway.