Dapper with Stored Procedure in CSharp (edit)

Dapper with Output Parameter & Return Parameter

https://stackoverflow.com/questions/22353881/passing-output-parameters-to-stored-procedure-using-dapper-in-c-sharp-code

Dapper with Oracle

https://medium.com/@CodeCoo/call-oracle-store-procedure-with-dapper-c-b4176f636e11

1) CSharp

Connection Factory class

    public class ConnectionFactory : IDisposable
    {
        public static string ConnectionString { get; } = ConfigurationManager.ConnectionStrings["AuthConnection"].ConnectionString;

        public static IDbConnection GetInstance()
        {
            IDbConnection connection = new SqlConnection(ConnectionString);
            return connection;
        }

        public void Dispose()
        {
            this.Dispose();
        }
    }

Stored Procedure Factory class

    public class StoredProcedureFactory
    {
        public string Execute(Dictionary<string, object> parameters, string spname)
        {
            var error = "";
            using (var con = ConnectionFactory.GetInstance())
            {
                con.Open();
                var param = new DynamicParameters();
                foreach (var key in parameters.Keys)
                {
                    param.Add(key, parameters[key]);
                }
                param.Add("Error", dbType: DbType.String, size: 4000, direction: ParameterDirection.Output);
                con.Execute(spname, param, commandType: CommandType.StoredProcedure);
                error = param.Get("Error");
            }
            return error;
        }

        public IEnumerable GetList(string spname)
        {
            IEnumerable data;
            using (var con = ConnectionFactory.GetInstance())
            {
                data = con.Query(spname, commandType: CommandType.StoredProcedure);
            }
            return data;
        }

        public T GetOneBy(Dictionary<string, object> parameters, string spname)
        {
            T entity;
            using (var con = ConnectionFactory.GetInstance())
            {
                var param = new DynamicParameters();
                foreach (var key in parameters.Keys)
                {
                    param.Add(key, parameters[key]);
                }
                entity = con.Query(spname, param, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
            return entity;
        }

        public IEnumerable GetListBy(Dictionary<string, object> parameters, string spname)
        {
            IEnumerable data;
            using (var con = ConnectionFactory.GetInstance())
            {
                var param = new DynamicParameters();
                foreach (var key in parameters.Keys)
                {
                    param.Add(key, parameters[key]);
                }
                data = con.Query(spname, param, commandType: CommandType.StoredProcedure);
            }
            return data;
        }
    }

Select code

private UserInfo Check_USER_INFO_FB_By_TokenForBusiness(string token_for_business)
{
 //SELECT
 var dictParams = new Dictionary<string, object>
 {
 { "TokenForBusiness", token_for_business }
 };
 return new StoredProcedureFactory().GetOneBy<UserInfo>(dictParams, "sp_USER_INFO_FB_SELECT");
}

Insert code

private string Create_USER_INFO_FB_Sync(string userid, string token_for_business, string facebook_id, string app_id)
{
 //INSERT
 var dictParams = new Dictionary<string, object>
 {
 { "USERID", userid },
 { "FACEBOOKID", facebook_id },
 { "APPID", app_id },
 { "TokenForBusiness", token_for_business }
 };
 string error = new StoredProcedureFactory().Execute(dictParams, "sp_USER_INFO_FB_INSERT");
 return error;
}

2) SQL Server Stored Procedure

Stored Procedure for Insert

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_USER_INFO_FB_INSERT](
 @USERID nvarchar(20),
 @FACEBOOKID nvarchar(255) = NULL,
 @APPID nvarchar(255) = NULL,
 @TokenForBusiness nvarchar(255) = NULL,
 @Error NVARCHAR(4000) output
)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE 
 @ErrorNumber INT,
 @ErrorSeverity INT,
 @ErrorState INT,
 @ErrorLine INT,
 @ErrorProcedure NVARCHAR(200);

 BEGIN TRY
 
 DECLARE @isRecordExisting int;
 
 SELECT @isRecordExisting = COUNT(*)
 FROM USER_INFO_FB WHERE USERID = @USERID;

 IF @isRecordExisting = 0
 BEGIN
 INSERT INTO [dbo].[USER_INFO_FB]
 ([USERID]
 ,[FACEBOOKID]
 ,[APPID]
 ,[TokenForBusiness])
 VALUES
 (@USERID -- nvarchar(20),
 ,@FACEBOOKID -- nvarchar(255),
 ,@APPID -- nvarchar(255),
 ,@TokenForBusiness -- nvarchar(255)
 )
 
 SET @Error = '';
 RETURN 0; -- Success
 END
 ELSE
 BEGIN
 SET @Error = 'Record already exists';
 RETURN 2; -- Record already exists
 END
 END TRY
 BEGIN CATCH
 
 SELECT 
 @ErrorNumber = ERROR_NUMBER(),
 @ErrorSeverity = ERROR_SEVERITY(),
 @ErrorState = ERROR_STATE(),
 @ErrorLine = ERROR_LINE(),
 @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

 SET @Error = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
 'Message: '+ ERROR_MESSAGE();

 RETURN 1; -- DB Error
 END CATCH
END
GO

Stored Procedure for Select

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_USER_INFO_FB_SELECT](
 @USERID nvarchar(20) = NULL,
 @TokenForBusiness nvarchar(255) = NULL
)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT u.*, ufb.[TokenForBusiness]
 FROM [dbo].[USER_INFO] u
 LEFT JOIN [dbo].[USER_INFO_FB] ufb ON ufb.USERID=u.USERID
 WHERE ufb.TokenForBusiness = @TokenForBusiness
END
GO