mardi 18 juin 2019

How to execute a stored procedure from a C# console app

I want to execute this stored procedure from a C# console application.

The following is the stored procedure I wrote and am trying to execute.

   GO
   /****** Object:  StoredProcedure [dbo].[OrderHeaders_Delete]    Script 
   Date: 6/18/2019 1:44:23 PM ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE PROCEDURE [dbo].[OrderHeaders_Delete]
       @OlderThanDays int
   AS
   BEGIN
       DELETE FROM dbo.OrderHeader 
       WHERE LastUpdate < DATEADD(d, @OlderThanDays * -1, GETDATE());
   END

I've already tried it using the following code but I am looking for something more dynamic with parameters and using Factory Method Design Pattern, where I can add on similar stored procedures, run it as a job and have it go through the SP.

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

namespace Data_Prune
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection mySqlConnection =
                new SqlConnection("server=HPDWTVSERVE;database=HPDeParts;uid=RMPsa;pwd=rmp9040"
                );

            SqlCommand mySqlCmd = mySqlConnection.CreateCommand();

            mySqlCmd.CommandText = "EXECUTE OrderHeaders_Delete @OlderThanDays";

            mySqlCmd.Parameters.Add("@OlderThanDays", SqlDbType.Int).Value = "100";

            mySqlConnection.Open();
            mySqlCmd.ExecuteNonQuery();
            mySqlConnection.Close(); 



        }
    }
}

Aucun commentaire:

Enregistrer un commentaire