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.

Are they running a local database from something like Access, SlqServer CE, or SQLite, or are they able to connect to a actual server-based database instance? I assume the former, but you might end up with different responses depending on which it is. — valverij, Jan 16 '14 at 14:33
currently I want to implement on single machine for testing. Later It wiil connect to a sql server instance. — prograshid, Jan 16 '14 at 14:40

BudBrot · Answer 1 · 2014-01-16T14:50:37.847

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.

score 0 · Answer 2 · answered Jan 16 '14 at 14:32

0

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.

answered Jan 16 '14 at 14:32

Travis

10,444
2
28
48

score 0 · Answer 3 · answered Jan 16 '14 at 14:38

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

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

3 Answers3