从SQL文件构建数据库

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.

这是针对数据库课程中的一个项目,所以我试图避免像EF Code First这样的东西让我假装数据库实际上并不存在,数据只是神奇地存在。

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.

基本上我希望能够从SQL脚本中拆除并重新构建本地SQL Server CE数据库,作为项目中的构建步骤。我正在使用VS2012 express,这是一个WPF项目。理想情况下,IDE中的项目中会显示一个SQL文件夹,其中包含按顺序运行的脚本。

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

我能找到的所有工具都可以帮助您避免编写SQL。有关我如何做到这一点的任何建议?

3 个解决方案

#1

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.

你可以写一些C#代码读取目标文件夹的内容,并执行针对数据库(使用纯净,简单SqlCommands),然后把该代码放到一个MSBuild任务,并把它添加到您的csproj的AfterBuild目标。

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

编辑:更好,使用预先存在的MSBuild包(如http://www.natthompson.com/?p=183这一个)为您执行sql!

#2

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.

或者,您可以将SQL文件的内容(一次一个)读入字符串,并使用简单的ADO.Net调用以编程方式对数据库执行它们。

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.

只需传递字符串sql中的SQL脚本文件的内容,为参数列表传递null,并将连接字符串传递给dbConnectionString中的数据库。

#3

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.)

sqlcmd.exe(命令行实用程序)不适用于Sql Server CE。 (如果您使用的是完整版的Sql Server(或Sql Server Express),我将从sqlcmd.exe开始。)

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

所以这是一个想法(也许)让你开始使用CE。请参阅下面的URL。

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

#1