Execute sql script on clickonce application

Question

I would like to know if it is possible to include a sql script in clickonce, I need to update an application that connects locally to sql server, and within the same update it is necessary to update some stored procedures, it is possible to execute these scripts with click once, or I will have Connect to the database and execute them manually, the application clickonce is stored in a server in the cloud to be downloaded

Answer 1

Store the stored procs or whatever scripts you need, as "Embedded Resource" files in the project. Then you can just load them when the program runs and execute them against the SQL Server using standard ADO.NET commands.

Reading embedded resource text file: How To Read Embedded Resource File

You just need to retrieve your script(s) and execute using code similar to the following:

// Read stored procedure script from embedded resource or simply assign in code, for example.
string scriptText = "CREATE PROCEDURE Test AS BEGIN SELECT 'Test'; END";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = scriptText;

        cmd.ExecuteNonQuery();
    }
}

For your script, you might want to write it so it can handle situations where the stored procedure already exists (for example: if it exists, drop it first, then CREATE PROCEDURE). If you have any permissions that need to be handled (eg. GRANT), they can be added to the script as well.

Typically, you would want to separate each stored procedure into a separate embedded resource text file and just loop through all the files and execute each one using SQL code like above. You might want to put these "scripts" in a separate assembly (aka Project) so it is easy to read them using the previous link.