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. 基本框架
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;
}
}
}