How to Create Tables in a SQL Server CE database file using C# code

Question

I am creating a SQL Server Compact Edition database using C# code in a console application. Here is my code for creating database:

string con;

string fileName = "MiniProfilerData.sdf";
string password = "arcanecode";

if (File.Exists(fileName))
{
    File.Delete(fileName);
}

con = string.Format("DataSource=\"{0}\"; Password='{1}'", fileName, password);
SqlCeEngine en = new SqlCeEngine(con);
en.CreateDatabase();

This code generates the MiniProfilerData.sdf file

Now I want to create tables in that .sdf file. I have a table creation script which will generates tables, here is the script Click Here

How can I generate tables using that TableCreationScript in my MiniProfilerData.sdf file?

Any idea?

What's the issue you are having? Any errors? Or do you just need to know how to run that SQL? — TyCobb
– TyCobb, Commented Nov 28, 2014 at 5:41
I know this. If I place this script in one file say "Script.sql". Then "How can I call/execute this sql file from my application?" — Ankita
– Ankita, Commented Nov 28, 2014 at 8:13

ErikEJ · Accepted Answer · 2014-11-28 09:08:39Z

1

You need to split the script into individual CREATE statements, the execute each (pseudo-code):

var scripts = TableCreationScript
    .Trim()
    .Replace("create", "#create")
    .Split(new []{'#'}, StringSplitOptions.RemoveEmptyEntries);

for each (var script in scripts)
{
    SqlCeCommand cmd = new SqlCeCommand(script);
    cmd.Connection = connection;
    cmd.ExecuteNonQuery();
}

edited Nov 28, 2014 at 9:08

answered Nov 28, 2014 at 8:12

ErikEJ

42.1k5 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ankita Over a year ago

Can you please tell me how to split script into individual CREATE statements?

Ankita Over a year ago

Thank you! It works for me. One more question: It gives me an error on clustered/non clustered index syntax. Can you explain me why so? Also it gives me an error: Parsing error on nvarchar(max).

ErikEJ Over a year ago

You have to remove the non clustered/clustered statements, SQL CE does not like them. and nvarchar(MAX) => ntext

Ankita Over a year ago

Yes, I have already removed it from script. I just wanted to know. Thank you!

Collectives™ on Stack Overflow

How to Create Tables in a SQL Server CE database file using C# code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related