How create database with script file in runtime program ?

Question

0.00/5 (No votes)

See more:

How create database with script file in runtime program
After installation in client system , does not need to Attach database in SQL Server...
my database have :
10 table , 79 procedure and 5view ... i want create in C# (when main form run)

Posted 16-May-15 3:00am

ghasem110deh

Add a Solution

Comments

Richard MacCutchan 16-May-15 9:30am

And what is the problem? Creating a database in C# is a fairly straightforward process. And if you are using an installer then you can package a precreated database in with the application.

2 solutions

Solution 1

Connect to your SQL Server in the usual way, using a user id/password combo that has permission to create databases.
Then run your DB script as a command string, exactly as if it was a normal ExecuteNonQuery command (which it is).
You can get the script from an existing DB using SSMS, by right clicking the database name and selecting "Script Database As" and "CREATE TO" from the context menu.
Save the script as a file, and load it in your app.

Posted 16-May-15 4:08am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

virusstorm · Accepted Answer · 2015-05-16T04:19:00

This is what the code would look like to OriginalGriff's solution. I wrote this a couple months ago to do basically what you are doing. It is designed to handle scripts that have the GO statement.

C#

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlCommandExecutor
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost";

            Console.WriteLine("Loading scripts...");
            StreamReader createDatabaseScriptStreamReader = File.OpenText("CreateDatabase.sql");
            StreamReader loadDataScriptStreamReader = File.OpenText("LoadDatabase.sql");

            using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
            {
                sqlConnection.Open();

                ExecuteScript(createDatabaseScriptStreamReader, sqlConnection);
                ExecuteScript(loadDataScriptStreamReader, sqlConnection);

                sqlConnection.Close();
            }

            Console.WriteLine("Finished.");
        }

        private static void ExecuteScript(StreamReader createDatabaseScriptStreamReader, SqlConnection sqlConnection)
        {
            StringBuilder stringBuilder = new StringBuilder();

            while (!createDatabaseScriptStreamReader.EndOfStream)
            {
                string line = createDatabaseScriptStreamReader.ReadLine();

                if (line == "GO")
                {
                    try
                    {
                        string command = stringBuilder.ToString();
                        string message;

                        if (command.Length > 15)
                            message = command.Substring(0, 15);
                        else
                            message = command;

                        message = message.Trim();

                        Console.WriteLine("Executing command \"" + message + "...\"");

                        SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
                        sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine();
                        Console.BackgroundColor = ConsoleColor.Red;
                        Console.ForegroundColor = ConsoleColor.White;
                        Console.WriteLine(ex.Message);
                        Console.ResetColor();
                        Console.WriteLine();
                    }

                    stringBuilder = new StringBuilder();
                }
                else
                {
                    stringBuilder.AppendLine(line);
                }

            }
        }
    }
}