c# - Executing SQL script to transfer data

Question

I am working on a program in c# where I have a layout as shown in the image below:

The purpose of the program is to perform data archiving in SQL server. If I choose "Create Tables", it will generate new tables into my database ( should generate about 40 tables in order) which has similar table structure (columns,constraint,triggers,etc) as original tables in the same database as well. How this works is I'll execute the SQL scripts in c# and call them (all 40 scripts) to create tables.

Right now, I added another button "Transfer data" where it will select specfic data(based on date) in old data and transfer them into the new tables I created. I will use the query Insert Into....SELECT from to transfer data.

My question is should I create sql scripts for transferring data and execute them in c# or just put the SQL queries inside my c# code ?

If I go with SQL scripts, should I split them into 40 scripts as well or place all the queries inside 1 script? I know it will be tedious if i put everything in one script as if an error occurs, it's hard to trace the source of the problem.

Below is a sample of how the sql query looks like :

SET IDENTITY_INSERT Kiosk_Log_New ON
INSERT INTO Kiosk_Log_New(LOGID,
                          logAPPID,
                          logLOGTID,
                          logGUID,
                          logOriginator,
                          logReference,
                          logAssemblyName,
                          logFunctionName,
                          logMessage,
                          logException,
                          CreatedBy,
                          CreatedDate)
SELECT                  LOGID,
                          logAPPID,
                          logLOGTID,
                          logGUID,
                          logOriginator,
                          logReference,
                          logAssemblyName,
                          logFunctionName,
                          logMessage,
                          logException,
                          CreatedBy,
                          CreatedDate FROM Kiosk_Log
WHERE CreatedDate BETWEEN '2015-01-01' AND GETDATE()

EDIT: Since many suggested stored procedure is the best option, this would be my create tables script:

  string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        /*  open sql connection to execute SQL script: PromotionEvent_New   */
        try
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                FileInfo file = new FileInfo("C:\\Users\\88106221\\Documents\\SQL Server Management Studio\\PromotionEvent_New.sql");
                string script = file.OpenText().ReadToEnd();
                Server server = new Server(new ServerConnection(con));
                server.ConnectionContext.ExecuteNonQuery(script);
                Display("PromotionEvent_New table has been created successfully");
                con.Close();



            }

        }
        catch(Exception ex)
        {

            textBox1.AppendText(string.Format("{0}", Environment.NewLine));
            textBox1.AppendText(string.Format("{0} MainPage_Load() exception - {1}{2}", _strThisAppName, ex.Message, Environment.NewLine));
            Display(ex.Message + "PromotionEvent_New could not be created");
            textBox1.AppendText(string.Format("{0}", Environment.NewLine));
            Debug.WriteLine(string.Format("{0} MainPage_Load() exception - {1}", _strThisAppName, ex.Message));


        }

Answer 1

It's best to use a stored procedure with a transaction to execute all your INSERT queries.

It's not advisable to submit queries from your C# code as explained in last post by John Ephraim Tugado due to a number of reasons; the most important reasons being,

• easier maintenance of INSERT queries
• minimal bandwidth consumption between web server and database server

Sending long queries strings from C# code will consume more bandwidth between web server and database server and could slow the database response in a high traffic scenario.

You can execute the following T-SQL code against your database to create a stored procedure for transferring/archiving data to archived tables. This procedure makes sure that all your INSERTS are executed within a transaction, that ensures you don't end up with orphaned tables and unnecessary headaches down the road.

Stored Procedure for transferring data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lord Cookie
-- Create date: 11/01/2017
-- Description: Transfers data to existing archived tables
-- =============================================
CREATE PROCEDURE dbo.ArchiveData

AS
BEGIN

    SET NOCOUNT ON;
    BEGIN TRY
         --use transaction when inserting data else you may end up with orphaned data and hard to debug issues later on
        BEGIN TRANSACTION

        --add your INSERT queries one after the other below
        SET IDENTITY_INSERT Kiosk_Log_New ON
        INSERT INTO Kiosk_Log_New (LOGID,
        logAPPID,
        logLOGTID,
        logGUID,
        logOriginator,
        logReference,
        logAssemblyName,
        logFunctionName,
        logMessage,
        logException,
        CreatedBy,
        CreatedDate)
                SELECT
                    LOGID
                    ,logAPPID
                    ,logLOGTID
                    ,logGUID
                    ,logOriginator
                    ,logReference
                    ,logAssemblyName
                    ,logFunctionName
                    ,logMessage
                    ,logException
                    ,CreatedBy
                    ,CreatedDate
                FROM Kiosk_Log
                WHERE CreatedDate BETWEEN '2015-01-01' AND GETDATE()

        --add more of your insert queries below


        -- finally commit transaction
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @errorDetails NVARCHAR(MAX);
        set @errorDetails = 'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());

          --roll back the transaction
        IF XACT_STATE() <> 0
        BEGIN
            ROLLBACK TRANSACTION
        END

        --you can log the above error message and/or re-throw the error so your C# code will see an error
        --but do this only after rolling back
    END CATCH;
END
GO

You can then call the above stored procedure using C# as shown in sample code below.

Call above stored procedure using C#

using(SqlConnection sqlConn = new SqlConnection("Your database Connection String")) {
 using(SqlCommand cmd = new SqlCommand()) {
  cmd.CommandText = "dbo.ArchiveData";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = sqlConn;
  sqlConn.Open();
  cmd.ExecuteNonQuery();
 }
}

Answer 2

Depending on the table naming and design, I would suggest creating a script to create a stored procedure for this that would generate one for each of your tables. I'm no expert in scripting but it is the same with the script that generates an audit trail for each of your tables or at least the ones you defined in the script.

Hard-coding this inside your c# application is a big NO as there is the possibility of database changes. We would want our app to be flexible to change with the least amount of effort.

If generating the script to create a stored procedure is hard for you, I would still recommend manually creating stored procedures for this task.