Dailycode.info

Short solution for short problems

Drop tables procedure for .Net and MS SQL

I wrote a simple procedure that will drop tables in a database that have a certain prefix. 

First I initiate the process layer:

MESCockpitProcessLayer.DirectDataAccess pl = new MESCockpitProcessLayer.DirectDataAccess();

Then I will fill a dataset with the following statement:

//This script will get all tables with the selected prefix and generates a drop statement. I load it in a dataset, each drop statement

//is a row

string DropTableString = "SELECT 'DROP TABLE ' + TABLE_NAME + ' ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'GP_MES%'";

DataSet ds = pl.GetDataSet(new StringBuilder(DropTableString), serverName, DBName, DBUserName, DBUserPWD);

Next I simply go over each row in the dataset table and execute the statement:

if (ds != null)

{

    foreach (DataRow table in ds.Tables[0].Rows)

    {

        //Drop the table using the generated script

        pl.ExecuteQueryDirect(new StringBuilder(table[0].ToString()), serverName, DBName, DBUserName, DBUserPWD);

    }

}


The generated statement look like this:

DROP TABLE EventLog 

The data class that gets the data and executes the SQL statements is just the very simplest using the Data.SqlClient:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace MESCockpitDataLayer.SqlClient

{

    publicclassMESDataAccess

    {

        publicstaticint ExecuteNonQuery(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteNonQuery();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticobject ExecuteScalar(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            //SqlTransaction MyTrans = null;

            try

            {

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                return executeCommand.ExecuteScalar();

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

 

        publicstaticDataSet GetDataSet(StringBuilder sb, string connectionString)

        {

            SqlConnection conn = null;

            SqlCommand executeCommand = null;

            SqlDataAdapter MyDataAdapter = null;

            DataSet returnDS = newDataSet();

            try

            {

                MyDataAdapter = newSqlDataAdapter();

               

                conn = newSqlConnection(connectionString);

                conn.Open();

 

                executeCommand = newSqlCommand(sb.ToString(), conn);

                MyDataAdapter.SelectCommand = executeCommand;

               

                MyDataAdapter.Fill(returnDS);

                return returnDS;

            }

            catch (Exception er)

            {

                //Handle exception.

                throw er;

            }

        }

    }

}