微软企业库（Microsoft Enterprise Library Data Access Block）

1. Dynamic-link library

    Microsoft.Practices.ObjectBuilder.dll

    Microsoft.Practices.EnterpriseLibrary.Common.dll

    Microsoft.Practices.EnterpriseLibrary.Data.dll

    使用 NuGet 安装 Microsoft.Practices.EnterpriseLibrary.Data.dll 即可。

2. 基本框架

    ASP .NET MVC + 微软企业库

3. SQL配置

<connectionStrings>
    <add name="Microsoft SQL Server" connectionString="Data Source = ***.***.***.***,3000; Initial Catalog = dbtest; Persist Security Info = True; User Id = sa; Password = ******;" providerName="System.Data.SqlClient" />
</connectionStrings>

4. CRUD方法

using Microsoft.Practices.EnterpriseLibrary.Data;
using MsEntLibDemo.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Web;

namespace MsEntLibDemo.DAL
{
    public class SystemUserDA
    {
        private static readonly Database m_db = DaUtil.DB;

        public List<SystemUser> GetSystemUserList()
        {
            List<SystemUser> users = new List<SystemUser>();
            string sql = " SELECT * FROM [DBO].[SYSTEM_USER] ORDER BY ID ";
            DbCommand cmd = m_db.GetSqlStringCommand(sql);
            DataSet ds = m_db.ExecuteDataSet(cmd);
            if (ds.Tables.Count > 0)
            {
                users = DataToList<SystemUser>.ConvertToList(ds.Tables[0], "_");
            }
            return users;
        }

        public SystemUser GetSystemUser(string id)
        {
            string sql = " SELECT * FROM [DBO].[SYSTEM_USER] WHERE ID = @ID ";
            DbCommand cmd = m_db.GetSqlStringCommand(sql);
            m_db.AddInParameter(cmd, "@ID", DbType.String, id);
            DataSet ds = m_db.ExecuteDataSet(cmd);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                SystemUser user = DataToList<SystemUser>.ConvertToList(ds.Tables[0], "_")[0];
                return user;
            }
            else
            {
                return null;
            }
        }

        public int InsertSystemUser(SystemUser user)
        {
            int result = -1;
            string sql = " INSERT INTO [DBO].[SYSTEM_USER] (CODE, PWD, NAME, CONTENT, ISEFFECTIVE, LEVEL, GROUP) VALUES (@P_CODE, @P_PWD, @P_NAME, @P_CONTENT, @P_ISEFFECTIVE, @P_LEVEL, @P_GROUP) ";
            DbCommand cmd = m_db.GetSqlStringCommand(sql);
            m_db.AddInParameter(cmd, "@P_CODE", DbType.String, user.Code);
            m_db.AddInParameter(cmd, "@P_PWD", DbType.String, user.Pwd);
            m_db.AddInParameter(cmd, "@P_NAME", DbType.String, user.Name);
            m_db.AddInParameter(cmd, "@P_CONTENT", DbType.String, user.Content);
            m_db.AddInParameter(cmd, "@P_ISEFFECTIVE", DbType.Boolean, user.IsEffective);
            m_db.AddInParameter(cmd, "@P_LEVEL", DbType.Int32, user.Level);
            m_db.AddInParameter(cmd, "@P_GROUP", DbType.Int32, user.Group);
            result = m_db.ExecuteNonQuery(cmd);
            return result;
        }

        public int UpdateSystemUser(SystemUser user)
        {
            int result = -1;
            string sql = " UPDATE [DBO].[SYSTEM_USER] SET CODE = @P_CODE, PWD = @P_PWD, NAME = @P_NAME, CONTENT = @P_CONTENT, ISEFFECTIVE = @P_ISEFFECTIVE, LEVEL = @P_LEVEL, GROUP = @P_GROUP WHERE ID = @P_ID ";
            DbCommand cmd = m_db.GetSqlStringCommand(sql);
            m_db.AddInParameter(cmd, "@P_ID", DbType.Int32, user.Id);
            m_db.AddInParameter(cmd, "@P_CODE", DbType.String, user.Code);
            m_db.AddInParameter(cmd, "@P_PWD", DbType.String, user.Pwd);
            m_db.AddInParameter(cmd, "@P_NAME", DbType.String, user.Name);
            m_db.AddInParameter(cmd, "@P_CONTENT", DbType.String, user.Content);
            m_db.AddInParameter(cmd, "@P_ISEFFECTIVE", DbType.Boolean, user.IsEffective);
            m_db.AddInParameter(cmd, "@P_LEVEL", DbType.Int32, user.Level);
            m_db.AddInParameter(cmd, "@P_GROUP", DbType.Int32, user.Group);
            result = m_db.ExecuteNonQuery(cmd);
            return result;
        }

        /// <summary>
        /// delete from table system_user
        /// </summary>
        /// <param name="ids">multiple id split by ','</param>
        /// <returns></returns>
        public int DeleteSystemUser(string ids)
        {
            int result = -1;
            string sql = string.Format(" DELETE FROM [DBO].[SYSTEM_USER] WHERE ID IN ({0}) ", ids);
            DbCommand cmd = m_db.GetSqlStringCommand(sql);
            result = m_db.ExecuteNonQuery(cmd);
            return result;
        }
    }
}