Sample: TestRepositories - etmendz/Mendz.Data.SqlServer GitHub Wiki

  1. In Visual Studio, create a .Net Core class library project called TestRepositories.
  2. Add a reference to Mendz.Data.
  3. Add a reference to Mendz.Data.SqlServer.
  4. Add a reference to TestModels.
  5. Add a class file named TestRepository.cs, and enter the following:

Using Dapper and stored procedures

Must add a reference to Dapper.
Must create the TestDB Test stored procedures.

using Dapper;
using Mendz.Data;
using Mendz.Data.Common;
using Mendz.Data.Repository;
using Mendz.Data.SqlServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TestModels;

namespace TestRepositories
{
    public class TestRepository : DbRepositoryBase<SqlServerDbDataContext>, 
        IDbDataCreatable<Test>, IDbDataReadable<Test>, IDbDataUpdatable<Test>, IDbDataDeletable<Test>,
        IDbDataSearchable<Test>
    {
        public TestRepository()
            : base()
        {
        }

        internal TestRepository(TestContext dbDataContext)
            : base(dbDataContext)
        {
            DbDataContext = dbDataContext;
        }

        public Test Create(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "TestCreate"
            string createdBy = "User";
            DateTime createdDate = DateTime.Now;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@code", model.Code, DbType.String, ParameterDirection.Input);
            parameters.Add("@name", model.Name, DbType.String, ParameterDirection.Input);
            parameters.Add("@description", model.Description, DbType.String, ParameterDirection.Input);
            parameters.Add("@createdBy", createdBy, DbType.String, ParameterDirection.Input);
            parameters.Add("@createdDate", createdDate, DbType.DateTime, ParameterDirection.Input);
            model.TestID = DbDataContext.Context.Execute<int>(query, parameters);
            model.CreatedBy = createdBy;
            model.CreatedDate = createdDate;
            return model;
        }

        public Test Read(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "TestRead";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            return DbDataContext.Context.QuerySingleOrDefault<Test>(query, parameters);
        }

        public Test Update(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "TestUpdate";
            string modifiedBy = "User";
            DateTime modifiedDate = DateTime.Now;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@code", model.Code, DbType.String, ParameterDirection.Input);
            parameters.Add("@name", model.Name, DbType.String, ParameterDirection.Input);
            parameters.Add("@description", model.Description, DbType.String, ParameterDirection.Input);
            parameters.Add("@modifiedBy", modifiedBy, DbType.String, ParameterDirection.Input);
            parameters.Add("@modifiedDate", modifiedBy, DbType.DateTime, ParameterDirection.Input);
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            DbDataContext.Context.Execute(query, parameters);
            model.ModifiedBy = modifiedBy;
            model.ModifiedDate = modifiedDate;
            return model;
        }

        public Test Delete(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "TestDelete";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            DbDataContext.Context.Execute(query, parameters);
            return model;
        }

        public IEnumerable<Test> Search<F, S>(F filter, S sort, 
            dynamic expansion = null, PagingInfo paging = null, List<ResultInfo> result = null)
        {
            return DbDataContext.Context.Query<Test>("TestSearch");
        }
    }
}

Using Dapper and in-line SQL

Must add a reference to Dapper.

using Dapper;
using Mendz.Data;
using Mendz.Data.Common;
using Mendz.Data.Repository;
using Mendz.Data.SqlServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TestModels;

namespace TestRepositories
{
    public class TestRepository : DbRepositoryBase<SqlServerDbDataContext>, 
        IDbDataCreatable<Test>, IDbDataReadable<Test>, IDbDataUpdatable<Test>, IDbDataDeletable<Test>,
        IDbDataSearchable<Test>
    {
        public TestRepository()
            : base()
        {
        }

        internal TestRepository(TestContext dbDataContext)
            : base(dbDataContext)
        {
            DbDataContext = dbDataContext;
        }

        public Test Create(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "INSERT INTO Test (
                        Code, 
                        Name, 
                        Description, 
                        CreatedBy, 
                        CreatedDate
                    ) VALUES (
                        @code, 
                        @name, 
                        @description, 
                        @createdBy, 
                        @createdDate
                    );
                    SELECT CAST(SCOPE_IDENTITY() AS INT);"
            string createdBy = "User";
            DateTime createdDate = DateTime.Now;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@code", model.Code, DbType.String, ParameterDirection.Input);
            parameters.Add("@name", model.Name, DbType.String, ParameterDirection.Input);
            parameters.Add("@description", model.Description, DbType.String, ParameterDirection.Input);
            parameters.Add("@createdBy", createdBy, DbType.String, ParameterDirection.Input);
            parameters.Add("@createdDate", createdDate, DbType.DateTime, ParameterDirection.Input);
            model.TestID = DbDataContext.Context.Execute<int>(query, parameters);
            model.CreatedBy = createdBy;
            model.CreatedDate = createdDate;
            return model;
        }

        public Test Read(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "SELECT * FROM Test WHERE TestID = @testID";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            return DbDataContext.Context.QuerySingleOrDefault<Test>(query, parameters);
        }

        public Test Update(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "UPDATE Test 
                    SET Code = @code, 
                        Name = @name, 
                        Description = @description,
                        ModifiedBy = @modifiedBy,
                        ModifiedDate = @modifiedDate
                    WHERE TestID = @testID";
            string modifiedBy = "User";
            DateTime modifiedDate = DateTime.Now;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@code", model.Code, DbType.String, ParameterDirection.Input);
            parameters.Add("@name", model.Name, DbType.String, ParameterDirection.Input);
            parameters.Add("@description", model.Description, DbType.String, ParameterDirection.Input);
            parameters.Add("@modifiedBy", modifiedBy, DbType.String, ParameterDirection.Input);
            parameters.Add("@modifiedDate", modifiedBy, DbType.DateTime, ParameterDirection.Input);
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            DbDataContext.Context.Execute(query, parameters);
            model.ModifiedBy = modifiedBy;
            model.ModifiedDate = modifiedDate;
            return model;
        }

        public Test Delete(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string query = "DELETE FROM Test WHERE TestID = @testID";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@testID", model.TestID, DbType.Int32, ParameterDirection.Input);
            DbDataContext.Context.Execute(query, parameters);
            return model;
        }

        public IEnumerable<Test> Search<F, S>(F filter, S sort, 
            dynamic expansion = null, PagingInfo paging = null, List<ResultInfo> result = null)
        {
            return DbDataContext.Context.Query<Test>("SELECT * FROM Test");
        }
    }
}

Using ADO.Net and stored procedures

Must create the TestDB Test stored procedures.

using Mendz.Data;
using Mendz.Data.Common;
using Mendz.Data.Repository;
using Mendz.Data.SqlServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using TestModels;

namespace TestRepositories
{
    public class TestRepository : DbRepositoryBase<SqlServerDbDataContext>, 
        IDbDataCreatable<Test>, IDbDataReadable<Test>, IDbDataUpdatable<Test>, IDbDataDeletable<Test>,
        IDbDataSearchable<Test>
    {
        public TestRepository()
            : base()
        {
        }

        internal TestRepository(TestContext dbDataContext)
            : base(dbDataContext)
        {
            DbDataContext = dbDataContext;
        }

        public Test Create(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string createdBy = "User";
            DateTime createdDate = DateTime.Now;
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "TestCreate"
                command.CommandType = CommandType.Text;
                IDbDataParameter parameter;
                parameter = command.CreateParameter();
                parameter.ParameterName = "@code";
                parameter.DbType = DbType.String;
                parameter.Value = model.Code;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@name";
                parameter.DbType = DbType.String;
                parameter.Value = model.Name;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@description";
                parameter.DbType = DbType.String;
                parameter.Value = model.Description;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@createdBy";
                parameter.DbType = DbType.String;
                parameter.Value = createdBy;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@createdDate";
                parameter.DbType = DbType.DateTime;
                parameter.Value = createdDate;
                model.TestID = (int)command.ExecuteScalar();
                model.CreatedBy = createdBy;
                model.CreatedDate = createdDate;
            }
            return model;
        }

        public Test Read(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "TestRead";
                command.CommandType = CommandType.StoredProcedure;
                IDbDataParameter parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Test()
                        {
                            TestId = (int)reader["TestID"],
                            Code = (string)reader["Code"],
                            Name = (string)reader["Name"],
                            Description = (string)reader["Description"],
                            CreatedBy = (string)reader["CreatedBy"],
                            CreatedDate = (DateTime)reader["CreatedDate"],
                            ModifiedBy = (string)reader["ModifiedBy"],
                            ModifiedDate = (DateTime?)reader["ModifiedDate"]
                        };
                    }
                }
            }
        }

        public Test Update(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string modifiedBy = "User";
            DateTime modifiedDate = DateTime.Now;
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "TestUpdate";
                command.CommandType = CommandType.StoredProcedure;
                IDbDataParameter parameter;
                parameter = command.CreateParameter();
                parameter.ParameterName = "@code";
                parameter.DbType = DbType.String;
                parameter.Value = model.Code;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@name";
                parameter.DbType = DbType.String;
                parameter.Value = model.Name;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@description";
                parameter.DbType = DbType.String;
                parameter.Value = model.Description;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@modifiedBy";
                parameter.DbType = DbType.String;
                parameter.Value = modifiedBy;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@modifiedDate";
                parameter.DbType = DbType.DateTime;
                parameter.Value = modifiedDate;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
                model.ModifiedBy = modifiedBy;
                model.ModifiedDate = modifiedDate;
            }
            return model;
        }

        public Test Delete(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "TestDelete";
                command.CommandType = CommandType.StoredProcedure;
                IDbDataParameter parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
            return model;
        }

        public IEnumerable<Test> Search<F, S>(F filter, S sort, 
            dynamic expansion = null, PagingInfo paging = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "TestSearch";
                command.CommandType = CommandType.StoredProcedure;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Test()
                        {
                            TestId = (int)reader["TestID"],
                            Code = (string)reader["Code"],
                            Name = (string)reader["Name"],
                            Description = (string)reader["Description"],
                            CreatedBy = (string)reader["CreatedBy"],
                            CreatedDate = (DateTime)reader["CreatedDate"],
                            ModifiedBy = (string)reader["ModifiedBy"],
                            ModifiedDate = (DateTime?)reader["ModifiedDate"]
                        };
                    }
                }
            }
        }
    }
}

Using ADO.Net and in-line SQL

using Mendz.Data;
using Mendz.Data.Common;
using Mendz.Data.Repository;
using Mendz.Data.SqlServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using TestModels;

namespace TestRepositories
{
    public class TestRepository : DbRepositoryBase<SqlServerDbDataContext>, 
        IDbDataCreatable<Test>, IDbDataReadable<Test>, IDbDataUpdatable<Test>, IDbDataDeletable<Test>,
        IDbDataSearchable<Test>
    {
        public TestRepository()
            : base()
        {
        }

        internal TestRepository(TestContext dbDataContext)
            : base(dbDataContext)
        {
            DbDataContext = dbDataContext;
        }

        public Test Create(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string createdBy = "User";
            DateTime createdDate = DateTime.Now;
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "INSERT INTO Test (
                        Code, 
                        Name, 
                        Description, 
                        CreatedBy, 
                        CreatedDate
                    ) VALUES (
                        @code, 
                        @name, 
                        @description, 
                        @createdBy, 
                        @createdDate
                    );
                    SELECT CAST(SCOPE_IDENTITY() AS INT);"
                command.CommandType = CommandType.Text;
                IDbDataParameter parameter;
                parameter = command.CreateParameter();
                parameter.ParameterName = "@code";
                parameter.DbType = DbType.String;
                parameter.Value = model.Code;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@name";
                parameter.DbType = DbType.String;
                parameter.Value = model.Name;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@description";
                parameter.DbType = DbType.String;
                parameter.Value = model.Description;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@createdBy";
                parameter.DbType = DbType.String;
                parameter.Value = createdBy;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@createdDate";
                parameter.DbType = DbType.DateTime;
                parameter.Value = createdDate;
                model.TestID = (int)command.ExecuteScalar();
                model.CreatedBy = createdBy;
                model.CreatedDate = createdDate;
            }
            return model;
        }

        public Test Read(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "SELECT * FROM Test WHERE TestID = @testID";
                command.CommandType = CommandType.Text;
                IDbDataParameter parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Test()
                        {
                            TestId = (int)reader["TestID"],
                            Code = (string)reader["Code"],
                            Name = (string)reader["Name"],
                            Description = (string)reader["Description"],
                            CreatedBy = (string)reader["CreatedBy"],
                            CreatedDate = (DateTime)reader["CreatedDate"],
                            ModifiedBy = (string)reader["ModifiedBy"],
                            ModifiedDate = (DateTime?)reader["ModifiedDate"]
                        };
                    }
                }
            }
        }

        public Test Update(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            string modifiedBy = "User";
            DateTime modifiedDate = DateTime.Now;
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "UPDATE Test 
                    SET Code = @code, 
                        Name = @name, 
                        Description = @description,
                        ModifiedBy = @modifiedBy,
                        ModifiedDate = @modifiedDate
                    WHERE TestID = @testID";
                command.CommandType = CommandType.Text;
                IDbDataParameter parameter;
                parameter = command.CreateParameter();
                parameter.ParameterName = "@code";
                parameter.DbType = DbType.String;
                parameter.Value = model.Code;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@name";
                parameter.DbType = DbType.String;
                parameter.Value = model.Name;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@description";
                parameter.DbType = DbType.String;
                parameter.Value = model.Description;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@modifiedBy";
                parameter.DbType = DbType.String;
                parameter.Value = modifiedBy;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@modifiedDate";
                parameter.DbType = DbType.DateTime;
                parameter.Value = modifiedDate;
                command.Parameters.Add(parameter);
                parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
                model.ModifiedBy = modifiedBy;
                model.ModifiedDate = modifiedDate;
            }
            return model;
        }

        public Test Delete(Test model, dynamic expansion = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "DELETE FROM Test WHERE TestID = @testID";
                command.CommandType = CommandType.Text;
                IDbDataParameter parameter = command.CreateParameter();
                parameter.ParameterName = "@testID";
                parameter.DbType = DbType.Int32;
                parameter.Value = model.TestID;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
            return model;
        }

        public IEnumerable<Test> Search<F, S>(F filter, S sort, 
            dynamic expansion = null, PagingInfo paging = null, List<ResultInfo> result = null)
        {
            using (IDbCommand command = DbDataContext.Context.CreateCommand())
            {
                command.CommandText = "SELECT * FROM Test";
                command.CommandType = CommandType.Text;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Test()
                        {
                            TestId = (int)reader["TestID"],
                            Code = (string)reader["Code"],
                            Name = (string)reader["Name"],
                            Description = (string)reader["Description"],
                            CreatedBy = (string)reader["CreatedBy"],
                            CreatedDate = (DateTime)reader["CreatedDate"],
                            ModifiedBy = (string)reader["ModifiedBy"],
                            ModifiedDate = (DateTime?)reader["ModifiedDate"]
                        };
                    }
                }
            }
        }
    }
}
⚠️ **GitHub.com Fallback** ⚠️