【模板下载】分享我所使用的数据库框架

也不能称之为框架吧，其实就是几个codesmith模板，只能用于sql server 数据库,codesmith我所使用的而是6.5版本的。

作用： 生成存储过程，Data类库中的类，Business中的Entity类 ，和操作类  （自动生成的只有创建，更新，删除，分页获取 等基本操作，高级的功能还需要手动添加）

原来也用过别的ORM,有时候觉得比较繁琐，想找个简单点的方式，这种通过codesmith模板生成 存储过程 数据层 business层的方式，优点是简单，直接操作sql,有时候感觉也很优美，

后来就一直在用。这个也不是原创的，是从国外一个开源的网站中学到的。后来扩展了一下，生成的存储过程是直接支持分页获取的的。

（首先说明一下，模板生成的表，有的地方需要手工设置一下，就是有个mid 还是pid ,不是每个表都要用到的，不用的话，删除就可以了） 有时间的话大家可以研究研究哈

模板下载地址

用图来说明一下：

我们在编写程序的时候，通常有许多类库，有的是专门用来同数据库打交道的。如下图的项目中：

data层是直接同数据库打交道的，每一个类对应一个表，拿一个来举例：

类中的方法是直接操作存储过程的，这样感觉比较灵活（存储过程也是由模板生成的，当然有些是要手写的，模板生成的是几个基本的，比如添加数据，删除数据，更新数据）

 public static class DBDep
    {
        /// <summary>
        /// Gets the connection string for read.
        /// </summary>
        /// <returns></returns>
        private static string GetReadConnectionString()
        {
            return ConfigurationManager.AppSettings["MSSQLConnectionString"];

        }

        /// <summary>
        /// Gets the connection string for write.
        /// </summary>
        /// <returns></returns>
        private static string GetWriteConnectionString()
        {
            if (ConfigurationManager.AppSettings["MSSQLWriteConnectionString"] != null)
            {
                return ConfigurationManager.AppSettings["MSSQLWriteConnectionString"];
            }

            return ConfigurationManager.AppSettings["MSSQLConnectionString"];

        }


        /// <summary>
        /// Inserts a row in the Dep table. Returns new integer id.
        /// </summary>
        /// <param name="title"> title </param>
        /// <returns>int</returns>
        public static int Create(
            string title)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "Dep_Insert", 1);
            sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, 100, ParameterDirection.Input, title);
            int newID = Convert.ToInt32(sph.ExecuteScalar());
            return newID;
        }


        /// <summary>
        /// Updates a row in the Dep table. Returns true if row updated.
        /// </summary>
        /// <param name="id"> id </param>
        /// <param name="title"> title </param>
        /// <returns>bool</returns>
        public static bool Update(
            int id,
            string title)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "Dep_Update", 2);
            sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
            sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, 100, ParameterDirection.Input, title);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }

        /// <summary>
        /// Deletes a row from the Dep table. Returns true if row deleted.
        /// </summary>
        /// <param name="id"> id </param>
        /// <returns>bool</returns>
        public static bool Delete(
            int id)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "Dep_Delete", 1);
            sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);

        }


        /// <summary>
        /// Gets an IDataReader with one row from the Dep table.
        /// </summary>
        /// <param name="id"> id </param>
        public static IDataReader GetOne(
            int id)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "Dep_SelectOne", 1);
            sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
            return sph.ExecuteReader();

        }



        /// <summary>
        /// Gets an IDataReader with some list row from the Dep table.
        /// </summary>
        /// <param name="id"> id </param>
        public static IDataReader GetTopList(
            int id)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "Dep_SelectTopList", 1);
            sph.DefineSqlParameter("@pid", SqlDbType.Int, ParameterDirection.Input, id);
            return sph.ExecuteReader();

        }



        /// <summary>
        /// Gets a count of rows in the Dep table.
        /// </summary>
        public static int GetCount()
        {

            return Convert.ToInt32(SqlHelper.ExecuteScalar(
                GetReadConnectionString(),
                CommandType.StoredProcedure,
                "Dep_GetCount",
                null));

        }



        /// <summary>
        /// Gets a Listcount of rows in the Dep table.
        /// </summary>
        public static int GetListCount(int pid)
        {
            SqlParameter theSqlParameter = new SqlParameter("@Pid", pid);
            return Convert.ToInt32(SqlHelper.ExecuteScalar(
                GetReadConnectionString(),
                CommandType.StoredProcedure,
                "Dep_GetListCount",
                theSqlParameter));

        }



        /// <summary>
        /// Gets an IDataReader with all rows in the Dep table.
        /// </summary>
        public static IDataReader GetAll()
        {

            return SqlHelper.ExecuteReader(
                GetReadConnectionString(),
                CommandType.StoredProcedure,
                "Dep_SelectAll",
                null);

        }

        /// <summary>
        /// Gets a page of data from the Dep table.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public static IDataReader GetPage(
            int pageNumber,
            int pageSize,
            out int itemCount)
        {
            itemCount = GetCount();

            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "Dep_SelectPage", 2);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber + 1);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();

        }


        public static IDataReader GetListPage(
            int pageNumber,
            int pageSize,
            int pid,
            out int itemCount)
        {
            itemCount = GetListCount(pid);

            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "Dep_SelectListPage", 3);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber + 1);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            sph.DefineSqlParameter("@pid", SqlDbType.Int, ParameterDirection.Input, pid);
            return sph.ExecuteReader();

        }

    }

Business层有想对应的类

 

我们分别来看一下相关的代码：

 

Dep类

 [ProtoContract]
    public class Dep
    {

        #region Constructors

        public Dep()
        { }


        #endregion

        #region Private Properties

        private int iD = -1;
        private string title = string.Empty;

        #endregion

        #region Public Properties

        [ProtoMember(1)]
        public int ID
        {
            get { return iD; }
            set { iD = value; }
        }
        [ProtoMember(2)]
        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        #endregion




    }

 

DoDep类

 public class DoDep
    {

        #region Private Methods

        /// <summary>
        /// Gets an instance of Dep.
        /// </summary>
        /// <param name="id"> id </param>
        private static Dep GetDep(
            int id)
        {
            using (IDataReader reader = DBDep.GetOne(
                id))
            {
                return PopulateFromReader(reader);
            }

        }


        private static Dep PopulateFromReader(IDataReader reader)
        {
            Dep dep = new Dep();
            if (reader.Read())
            {
                dep.ID = Convert.ToInt32(reader["ID"]);
                dep.Title = reader["Title"].ToString();

            }
            return dep;
        }

        /// <summary>
        /// Persists a new instance of Dep. Returns true on success.
        /// </summary>
        /// <returns></returns>
        private static bool Create(Dep dep)
        {
            int newID = 0;

            newID = DBDep.Create(
                dep.Title);

            dep.ID = newID;

            return (newID > 0);

        }


        /// <summary>
        /// Updates this instance of Dep. Returns true on success.
        /// </summary>
        /// <returns>bool</returns>
        private static bool Update(Dep dep)
        {

            return DBDep.Update(
                dep.ID,
                dep.Title);

        }





        #endregion

        #region Public Methods

        /// <summary>
        /// Saves this instance of Dep. Returns true on success.
        /// </summary>
        /// <returns>bool</returns>
        public static bool Save(Dep dep)
        {
            if (dep.ID > 0)
            {
                return Update(dep);
            }
            else
            {
                return Create(dep);
            }
        }




        #endregion

        #region Static Methods

        /// <summary>
        /// Deletes an instance of Dep. Returns true on success.
        /// </summary>
        /// <param name="id"> id </param>
        /// <returns>bool</returns>
        public static bool Delete(
            int id)
        {
            return DBDep.Delete(
                id);
        }


        /// <summary>
        /// Gets a count of Dep. 
        /// </summary>
        public static int GetCount()
        {
            return DBDep.GetCount();
        }

        private static IList<Dep> LoadListFromReader(IDataReader reader)
        {
            IList<Dep> depList = new List<Dep>();
            try
            {
                while (reader.Read())
                {
                    Dep dep = new Dep();
                    dep.ID = Convert.ToInt32(reader["ID"]);
                    dep.Title = reader["Title"].ToString();
                    depList.Add(dep);

                }
            }
            finally
            {
                reader.Close();
            }

            return depList;

        }


        /// <summary>
        /// Gets an IList with some instances of Dep.
        /// </summary>
        public static IList<Dep> GetTopList(
            int id)
        {
            IDataReader reader = DBDep.GetTopList(
                id);

            return LoadListFromReader(reader);

        }


        /// <summary>
        /// Gets an IList with all instances of Dep.
        /// </summary>
        public static IList<Dep> GetAll()
        {
            IDataReader reader = DBDep.GetAll();
            return LoadListFromReader(reader);

        }

        /// <summary>
        /// Gets an IList with page of instances of Dep.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public static IList<Dep> GetPage(int pageNumber, int pageSize, out int itemCount)
        {
            itemCount = 1;
            IDataReader reader = DBDep.GetPage(pageNumber, pageSize, out itemCount);
            return LoadListFromReader(reader);
        }


        /// <summary>
        /// Gets an IList with page of instances of Dep.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="itemCount">total items</param>
        public static IList<Dep> GetListPage(int pageNumber, int pageSize, int pid, out int itemCount)
        {
            itemCount = 1;
            IDataReader reader = DBDep.GetListPage(pageNumber, pageSize, pid, out itemCount);
            return LoadListFromReader(reader);
        }



        #endregion


      
        public static IList<Dep> GetDep()
        {
            IDataReader reader = DBDep.GetAll();
            return LoadListFromReader(reader);

        }

    }

服务器是调用DoDep类的

调用的代码举例：

（直接把从数据库获取到数据，返回给客户端)

是可以直接返回List类型的数据的,客户端收到后，再做处理

  IList<Dep> list = DoDep.GetDep();

                connection.SendObject<IList<Dep>>("ResGetDep", list);

 

[源码下载]Demo2.模拟简单登陆-效果图 基于networkcomms2.3.1

[源码下载]Demo1 客户端从服务器获取信息（基于networkcomms2.3.1)

【开源下载】基于TCP网络通信的自动升级程序c#源码

 【模板下载】分享我所使用的数据库框架