Creating tables through a script & how to run the script.

Question

Hi Guys,

Ive got a couple of scripts that I need to run on my databse. So I have a few questions.

Im simply using Connection, CommandText,CommandType and CommandTimeout when opening a connection to the databse.

First question - Does anyone know if through this method, I can create permantent tables, not temporary tables?

Secondly - How would I run this file? Could I just set the file to be a parameter, and in the query run the parameter?

Thanks

Answer 1

In C#--

1. You can create both permanent and temporary tables this way.

2. Run the script as the CommandText of a command object.

Answer 2

You can do anything in a .NET SQL connection that you could do in a SQL script. As far as "running a file", you would need to load the file text into memory and execute the loaded text as a single command.

We do something similar in our application. Our database scripts are stored in SQL scripts. We load each file sequentially from disk into memory and execute it.

Answer 3

Example From MSDN : How to set parameters in the Query,

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{

    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}