/// <summary>
/// 返回查询Dataset
/// </summary>
/// <param name="pageParam"></param>
/// <param name="total">总行数</param>
/// <returns></returns>
/// <remarks></remarks>
public static DataSet GetListByPage <T>(this IService <T> service, PageParam pageParam, ref int total) where T : BaseEntity
{
SqlParameter[] parameters =
{
new SqlParameter("@TableName", DbType.AnsiString),
new SqlParameter("@FieldList", SqlDbType.NVarChar, 2000),
new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 100),
new SqlParameter("@Where", SqlDbType.NVarChar, 2000),
new SqlParameter("@Order", SqlDbType.VarChar, 100),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@TotalCount", SqlDbType.Int)
};
parameters[0].Value = pageParam.TableName;
parameters[1].Value = pageParam.FieldList;
parameters[2].Value = pageParam.PrimaryKey;
parameters[3].Value = pageParam.Where;
parameters[4].Value = pageParam.Order;
parameters[5].Value = pageParam.pageSize;
parameters[6].Value = pageParam.pageIndex;
parameters[7].Direction = ParameterDirection.Output;
DataSet ds = DbHelperSql.RunProcedure(DbHelperSql.DefaultQueryConn, "SP_COM_QUERY_BY_PAGE", parameters, "ds");
total = Convert.ToInt32(parameters[7].Value);
return(ds);
}
public static DataSet RunProcedure <T>(this IService <T> service,
DbOperation opt,
String storedProcName,
IDataParameter[] parameters,
int timeout = 3000) where T : BaseEntity
{
return(DbHelperSql.RunProcedure(opt == DbOperation.Read ? DbHelperSql.DefaultQueryConn : DbHelperSql.DefaultUpdateConn,
storedProcName,
parameters, "tb1", timeout));
}
/// <summary>
/// 分配权限
/// </summary>
/// <param name="privilegeID">已经存在的权限</param>
/// <param name="strSql">添加SQL:select..union select</param>
/// <returns></returns>
/// <remarks></remarks>
public ResultInfo AssignPrivilege(string privilegeID, string strSql)
{
SqlParameter[] parameters =
{
new SqlParameter("@ErrorNo", SqlDbType.Int, 4),
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 500),
new SqlParameter("@privilegeID", SqlDbType.VarChar, 4000),
new SqlParameter("@strSql", SqlDbType.NVarChar, 4000)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = privilegeID;
parameters[3].Value = strSql;
DbHelperSql.RunProcedure(DbHelperSql.DefaultUpdateConn, "SP_LOG_ASSIGN_PRIVILEGE", parameters);
return(new ResultInfo()
{
ErrorNo = (int)parameters[0].Value,
ErrorMsg = (string)parameters[1].Value
});
}
public bool BatchAdd(DataTable dt)
{
SqlParameter[] parameters =
{
new SqlParameter("@ErrorNo", SqlDbType.Int, 4),
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 500),
new SqlParameter("@tpImportAccount", dt)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
DbHelperSql.RunProcedure(DbHelperSql.DefaultUpdateConn, "sp_BatchImportAccount", parameters, "ds");
if ((int)parameters[0].Value == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 分配权限新
/// </summary>
/// <param name="privilegeList"></param>
/// <returns></returns>
public ResultInfo AssignPrivilege(List <PrivilegeAssignDto> privilegeList)
{
DataTable dt = this.ToDataTable(privilegeList);
SqlParameter[] parameters =
{
new SqlParameter("@ErrorNo", SqlDbType.Int, 4),
new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, 500),
new SqlParameter("@tpLOG_Privilege", dt)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = dt;
DbHelperSql.RunProcedure(DbHelperSql.DefaultUpdateConn, "SP_LOG_ASSIGN_PRIVILEGE", parameters);
return(new ResultInfo()
{
ErrorNo = (int)parameters[0].Value,
ErrorMsg = (string)parameters[1].Value
});
}
public PageOfDatatable GetPaged(string tableName, string whereClause, string fields, string orderBy,
int pageIndex, int pageSize, string primaryKey = "Id")
{
if (_providerConfig.DbProvider == DbProvider.MySql)
{
if (!string.IsNullOrEmpty(whereClause) && !whereClause.TrimStart().ToLower().StartsWith("where "))
{
whereClause = " where " + whereClause;
}
var totalCount = Count(tableName, whereClause);
PageOfDatatable pageOfDatatable = new PageOfDatatable()
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalItemCount = totalCount
};
if (!string.IsNullOrEmpty(orderBy))
{
orderBy = " order by " + orderBy;
}
if (string.IsNullOrEmpty(fields))
{
fields = "*";
}
var sqlClause = string.Format("SELECT {2} FROM {0} {1} {3} limit {4},{5}",
tableName, whereClause, fields, orderBy, pageOfDatatable.StartIndex, pageOfDatatable.PageSize);
using (var ds = DbHelperMySql.Query(_providerConfig.DbConnectionString, sqlClause))
{
pageOfDatatable.Data = ds.Tables[0];
}
return(pageOfDatatable);
}
else
{
var parameters = new[]
{
new SqlParameter("@TableName", SqlDbType.NVarChar, 255),
new SqlParameter("@Fields", SqlDbType.NVarChar, 255),
new SqlParameter("@WhereClause", SqlDbType.NVarChar, 2000),
new SqlParameter("@OrderBy", SqlDbType.NVarChar, 255),
new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 255),
new SqlParameter("@PageIndex", SqlDbType.Int, 4),
new SqlParameter("@PageSize", SqlDbType.Int, 4),
};
parameters[0].Value = tableName;
parameters[1].Value = string.IsNullOrEmpty(fields) ? "*" : fields;
parameters[2].Value = whereClause ?? string.Empty;
parameters[3].Value = orderBy ?? string.Empty;
parameters[4].Value = primaryKey;
parameters[5].Value = pageIndex;
parameters[6].Value = pageSize;
using (
var reader = DbHelperSql.RunProcedure(_providerConfig.DbConnectionString, "up_GetPaged", parameters)
)
{
PageOfDatatable pageOfDatatable = new PageOfDatatable()
{
PageIndex = pageIndex,
PageSize = pageSize,
};
if (reader.Tables.Count >= 2 && reader.Tables[1].Rows.Count > 0)
{
var row = reader.Tables[1].Rows[0];
if (row["TotalRowCount"] == DBNull.Value)
{
pageOfDatatable.TotalItemCount = 0;
}
else
{
pageOfDatatable.TotalItemCount = (Int32)row["TotalRowCount"];
}
}
pageOfDatatable.Data = reader.Tables[0];
return(pageOfDatatable);
}
}
}