C# Connect to a SQL server CE programmatically

Question

I'm completely new to database programming. I'm working with Visual Studio 2010 and want to create a database programmatically.

So far I've tried to connect to a SQL Server Compact Edition like this:

public class HistoryDBAccess
{
    private SqlConnection conn = new SqlConnection();

    public HistoryDBAccess()
    {
        conn.ConnectionString = @"Server=localhost;Integrated security=SSPI;database=master";
        string str = "CREATE DATABASE MyDatabase ON PRIMARY " +
            "(NAME = MyDatabase_Data, " +
            "FILENAME = 'C:\\MyDatabaseData.mdf', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = MyDatabase_Log, " +
            "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";

        SqlCommand myCommand = new SqlCommand(str, conn);
        try
        {
            conn.Open();
            myCommand.ExecuteNonQuery();
            MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        catch (System.Exception ex)
        {
            MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }

    }
}

The connection in the Ctor fails with the message that a connection could not be established.

Using the database wizard with VS2010 is fine, but how I can see the SQLCe server under windows 7?

Is it somewhere under the control panel and do I need a default password in the connection string?

Many thanks,

Juergen

check this one: http://stackoverflow.com/questions/6196274/create-sqlce-database-programatically — Davide Piras, Nov 02 '11 at 10:31
@Piras: Many thanks. This is what I was looking for, especially the SqlCeEngine object! — Juergen, Nov 02 '11 at 10:45

score 0 · Answer 1 · answered Nov 02 '11 at 10:33

0

I think you should add a connection to your database in Your project using Server Explorer(or Database Explorer) Then check your Connection string.

answered Nov 02 '11 at 10:33

ahmadali shafiee

4,350
12
56
91

score 0 · Answer 2 · answered Nov 02 '11 at 10:38

It looks like you are trying to pass the con object before opening the connection.

So Try this and let me know.

 public HistoryDBAccess()
    {
        conn.ConnectionString = @"Server=localhost;Integrated security=SSPI;database=master";
        string str = "CREATE DATABASE MyDatabase ON PRIMARY " +
            "(NAME = MyDatabase_Data, " +
            "FILENAME = 'C:\\MyDatabaseData.mdf', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = MyDatabase_Log, " +
            "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";
          conn.Open();

        SqlCommand myCommand = new SqlCommand(str, conn);
        try
        {

            myCommand.ExecuteNonQuery();
            MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        catch (System.Exception ex)
        {
            MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }

    }
}

That does not work either. I guess there is something wrong with the connection string. — Juergen, Nov 02 '11 at 10:43
@Juergen: What is the exception its being thrown? We need the error, We can it for you. I do not have SQL CE. — Hari Gillala, Nov 02 '11 at 10:48
@ErikEj, I know he is asking about SQl CE, there is no need for you to mark the answer down. I told him to try. I never said this is correct answer.. — Hari Gillala, Nov 02 '11 at 10:52

score 0 · Answer 3 · answered Nov 02 '11 at 10:44

Once you create it, you're probably going to want to interact with it. You should consider using NHibernate. It has a feature called SchemaExport that can drop and create databases as much as you like.

Here's an example using SQL Express, but it's not much different to use Compact Edition. You can get NHibernate installed in your project by opening it in Visual Studio and clicking Project > Manage NuGet Packages, then searching All Online for NHibernate.

public class NHContext
{
    protected static Configuration NHConfiguration;
    protected static ISessionFactory SessionFactory;

    public static void DropDatabase()
    {
        new SchemaExport(NHConfiguration).Drop(false, true);
    }

    public static void CreateDatabase()
    {
        new SchemaExport(NHConfiguration).Create(false, true);
    }

    protected static Configuration configureNHibernate()
    {
        var configure = new Configuration();
        configure.SessionFactoryName("BuildIt");

        configure.DataBaseIntegration(db =>
        {
            db.Dialect<MsSql2008Dialect>();
            db.Driver<Sql2008ClientDriver>();//db.Driver<SqlClientDriver>();
            db.KeywordsAutoImport = Hbm2DDLKeyWords.AutoQuote;
            db.IsolationLevel = IsolationLevel.ReadCommitted;
            db.ConnectionString = @"Data Source=.\SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=NHibernate32Test;User Instance=False";
            db.Timeout = 10;

            // For testing
            db.LogFormattedSql = true;
            db.LogSqlInConsole = true;
            db.AutoCommentSql = true;
        });

        return configure;
    }

    public static void Setup()
    {
        NHConfiguration = configureNHibernate();
        HbmMapping mapping = getMappings();
        NHConfiguration.AddDeserializedMapping(mapping, "NHibernate32Test");
        SchemaMetadataUpdater.QuoteTableAndColumns(NHConfiguration);
        SessionFactory = NHConfiguration.BuildSessionFactory();
    }
}

Yes - as I said in my answer - it's trivial to modify for SqlCE. — Chris Moschini, Nov 02 '11 at 17:03

C# Connect to a SQL server CE programmatically

3 Answers3