CODEWITHSUNDEEP
c#sql-server-ce
Pouyan
Pouyan

Reputation: 2939

How to write DDL against SQL SE database programmatically?

I have an SDF database file. I'm looking forward to manage my database in C#. for example I want to drop a table or change a table schema.

How can I achieve this?

I insist achieving this using Entity Framework and DbContext.

Upvotes: 1

Views: 197

Answers (3)

ErikEJ
ErikEJ

Reputation: 41779

cmd is a SqlCeCommand object (or IDbCommand object)

cmd.ExecuteNonQuery("DROP TABLE myTable;");

cmd.ExecuteNonQuery("ALTER TABLE MyTable ADD ...");

With Entity Framework, you can use the above SQL statements like this:

context.Database.ExecuteSqlCommand("DROP TABLE myTable;");

Upvotes: 1

Alexander Pastukhov
Alexander Pastukhov

Reputation: 58

You can use the following:

using System.Data.SqlServerCe;
using System.IO;

...

namespace SomeNamespace
{
    public class SomeClass
    {
        public static void CreateDatabase(string connectionString)
        {
            using (var engine = new SqlCeEngine(connectionString))
            {
                if (IsDatabaseExists(connectionString))
                    File.Delete(GetPathToDatabase(connectionString));
                engine.CreateDatabase();

                using (var conn = new  SqlCeConnection(connectionString))
                {
                    conn.Open();

                    var cmd = new SqlCeCommand(@"CREATE TABLE [Users](
                                        [ID] [int] IDENTITY(1,1) NOT NULL
                                        [Username] [nvarchar](128) NOT NULL
                                        CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED ([ID]))";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"CREATE TABLE [UserAnswers](
                                        [ID] [int] IDENTITY(1,1) NOT NULL,
                                        [UserID] [int] NOT NULL,
                                        [Question] [nvarchar](256) NOT NULL,
                                        [Answer] [nvarchar](256) NOT NULL,
                                        CONSTRAINT [PK_UserAnswers] PRIMARY KEY NONCLUSTERED ([ID]))";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"ALTER TABLE [UserAnswers] ADD  CONSTRAINT [FK_UserAnswers_Users] FOREIGN KEY([UserID])
                                        REFERENCES [Users] ([ID])
                                        ON DELETE CASCADE";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

Upvotes: 1

Dannydust
Dannydust

Reputation: 773

I think you want to manage your Database from a Database Project in Visual Studio, am I right? If so, you have to add a Database Project to your solution. But I really don't know if your Version is supported.

Upvotes: 0

Related Questions