CODEWITHSUNDEEP
c#sqlsql-server-cebuild-process
Carson Myers
Carson Myers

Reputation: 38564

Building a database from SQL files

This is for a project in a databases course, so I'm trying to avoid things like EF Code First that let me pretend that databases don't actually exist and data just magically persists.

Basically I want to be able to tear-down and re-build a local SQL Server CE database from SQL scripts as a build step in the project. I'm using VS2012 express and it's a WPF project. Ideally there would be a single SQL folder visible in the project from the IDE, that contains scripts which are run in order.

All I've been able to find are tools that help you avoid writing SQL. Any suggestions for how I might do this?

Upvotes: 2

Views: 187

Answers (3)

granadaCoder
granadaCoder

Reputation: 27904

sqlcmd.exe (a command line utility) does not work with Sql Server CE.
(if you are using the full edition Sql Server (or Sql Server Express), I would start with sqlcmd.exe.)

So here is an idea to (maybe) get you started with CE. See the URL below.

http://blogs.msdn.com/b/stevelasker/archive/2007/03/31/creating-your-sql-server-compact-edition-database-and-schema-in-code.aspx

Upvotes: 0

Kevin
Kevin

Reputation: 704

Alternatively, you can read the contents of your SQL files (one at a time) into a string and programmatically execute them against the DB using simple ADO.Net calls.

You can use something like this:

    /// <summary>
    /// This method internally uses the ExecuteNonQuery method of the SqlCommand object
    /// to run a query that returns no values (updates/inserts/deletes).
    /// </summary>
    /// <param name="sql">The SQL to execute</param>
    /// <param name="parameters">The SqlParameters to use (pass null if there are none)</param>
    /// <remarks>This method is thread safe.</remarks>
    public static void runNonQuery(string sql, IList<SqlParameter> parameters, string dbConnectionString)
    {
        // Old-school, using simple ADO.Net to run a non-query (updates/inserts/deletes) - KDR
        try
        {
            Monitor.Enter(_lock);
            using (SqlConnection conn = new SqlConnection(dbConnectionString))
            {
                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;

                    if (parameters != null && parameters.Count() != 0)
                    {
                        foreach (SqlParameter param in parameters)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
            }
        }
        finally
        {
            Monitor.Exit(_lock);
        }
    }

Just pass the contents of a SQL script file in the string sql, pass null for the list of parameters, and pass the connection string to your database in dbConnectionString.

Upvotes: 1

TDaver
TDaver

Reputation: 7264

You could write some C# code that reads the contents of a target folder and executes it against the database (using pure, uncomplicated SqlCommands), then put this code into a MSBuild Task, and add it to the AfterBuild target of your csproj.

EDIT: even better, use a pre-existing MSBuild pack (like http://www.natthompson.com/?p=183 this one) to execute the sql for you!

Upvotes: 2

Related Questions