Dapper with Stored Procedure in CSharp (edit)

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