PS:公司用了多个数据库,所以在DAL 里写了个父类,写方法的类都继承这个父类
父类中预先写了定义了各数据库的绑定方法,eg:public class DBUtility
{
protected static SqlConnection GetMainDb()
{
return GetSqlConnection("MainDb");
}
protected static SqlConnection GetLogDb()
{
return GetSqlConnection("LogDb");
}
protected static SqlConnection GetActivityDb()
{
return GetSqlConnection("ActivityDb");
}
}
1.绑定多个数据 (DAL返回list<>,最后返回的是一个列表,Json格式的)
活动订单
public static Tuple<int, List<P_Invite_PolicyActiveOrder>> GetActiveOrderList(int pageStart, int pageLength, string ActiveName = "", string policyNo = "",
string inviteCode = "", DateTime? beginTime = null, DateTime? endTime = null, int inviteId = 0)
{
string where = string.Empty;
if (ActiveName != "")
{
// where += " AND ActiveId IN( SELECT Id FROM PolicyActive AS pa WHERE pa.Title LIKE '%" + ActiveName + "%')";
where += " AND productName like'%" + ActiveName + "%'";
}
if (policyNo != "")
where += " AND [policyNo]=@policyNo";
if (string.IsNullOrEmpty(inviteCode) == false)
where += " AND [InviteCode]=@InviteCode";
if (beginTime.HasValue)
{
where += " AND CreateDate >= '" + beginTime.Value.ToString("yyyy-MM-dd") + "'";
}
if (endTime.HasValue)
{
where += " AND CreateDate <= '" + endTime.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
}
string sql = string.Format(@"
WITH _a AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY [Id] DESC) AS RowId FROM [P_Invite_PolicyActiveOrder] pa WHERE 1=1 {0}
)
SELECT * FROM _a WHERE RowId BETWEEN @b AND @e;
SELECT COUNT(1) FROM [P_Invite_PolicyActiveOrder] WHERE 1=1 {0}", where);
using (SqlConnection conn = GetSqlConnection())
{
using (var mulit = conn.QueryMultiple(sql, new { b = pageStart, e = pageStart + pageLength, policyNo = policyNo, InviteCode = inviteCode }))
{
List<P_Invite_PolicyActiveOrder> list = mulit.Read<P_Invite_PolicyActiveOrder>().ToList();
int records = mulit.Read<int>().FirstOrDefault();
return new Tuple<int, List<P_Invite_PolicyActiveOrder>>(records, list);
}
}
}
Tuple<int, List<P_Invite_PolicyActiveOrder>>
2.绑定单个数据()