Create database, tables and stored procedure using existing script from txt file using C#

Question

I have developed a winform application using C# and SQL Server 2008. Now my job is to implement it on client's machine. I am looking for the best way to create the database tables and stored procedure on client machine to run this application. I have generated the script of all my database objects. And now i want to create all database objects on client's machine with one click C# code that read each table or stored procedure script file (i.e. .sql or .txt) and create them.

Answer 1

You need to use SMO to complete this task. The normal ADO.NET stuff will complain about multi-statement execution and the like. It's really pretty easy once you integrated with SMO and have the scripts.

Answer 2

No need for smo, but a bit ugly

 SqlCommand getDataPath = new SqlCommand("select physical_name from sys.database_files;", baseConnection); // get default path where the sqlserver saves files
            string temp = getDataPath.ExecuteScalar().ToString();
            temp = temp.Replace(temp.Split('\\').Last(), string.Empty);
            StringBuilder sqlScript = new StringBuilder(Scripts.CreateDatabase); //CreateDatabase could be in ressources
            ///The @@@@ are used to replace the hardcorededpath in your script
            sqlScript.Replace("@@@@MAINDATAFILENAME@@@@", string.Concat(temp, "test.mdf"));
            sqlScript.Replace("@@@@LOGDATAFILENAME@@@@", string.Concat(temp, "test_log.ldf"));
            string[] splittedScript = new string[] { "\r\nGO\r\n" }; //remove GO
            string[] commands = sqlScript.ToString().Split(splittedScript,
              StringSplitOptions.RemoveEmptyEntries);

Then run every command in commands(SqlCommand cmd = new SqlCommand(command[x], baseConnection);)

Note: For some reasons this needs adminrights, so create a manifestfile.

Answer 3

Visual Studio supports database projects, which generates deployment scripts for you. It also allows for deployments from scratch or to upgrade existing databases. The deployments can be automated as part a build within visual studio or the build server. If you're using TFS you can also source-control your database.

Basically no more messing about with scripts!

http://msdn.microsoft.com/en-us/library/ff678491(v=vs.100).aspx

http://msdn.microsoft.com/en-us/library/xee70aty.aspx