110,538
社区成员
发帖
与我相关
我的任务
分享
public class adb
{
//static OleDbConnection con;
public static OleDbConnection getCon()
{
return new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]));
}
public void runCmd(string cmdText, out OleDbDataReader dataReader)
{
OleDbConnection con = getCon();
con.Open();
OleDbCommand command = con.CreateCommand();
command.CommandText = cmdText;
dataReader = command.ExecuteReader();
}
}
public List<T> runCmd(string cmdText, Func<IDataRecord, T> converter)
using (var conn = Common.Extensions.ConnectDB())
{
var cmd = conn.CreateCommand();
cmd.SetParam("TM1", timeFrom);
cmd.SetParam("TM2", dayTo);
var sql = "select ";
sql += "a.*,c.Class from Flow a ";
if (staffId != null)
{
sql += $"inner join {Extensions.LB}Flow$$Target{Extensions.RB} b on a.Id=b.ParentId " +
"inner join BaselineDefination c on a.BaselineDefination=c.Id " +
$"where a.CreateTime>={Extensions.Prefix}TM1 and a.CreateTime<{Extensions.Prefix}TM2 and " +
$"b.Value={Extensions.Prefix}USRID and a.Status=0 ";
cmd.SetParam("USRID", staffId);
}
else
{
sql += "inner join BaselineDefination c on a.BaselineDefination=c.Id " +
$"where a.CreateTime>={Extensions.Prefix}TM1 and a.CreateTime<{Extensions.Prefix}TM2 and a.Status=0 ";
}
if (!string.IsNullOrEmpty(filter))
{
sql += $" and (a.Summary like '%{filter.Replace("'", "''")}%' or exists(select * from Flow pf where pf.Id=a.PrevId and pf.UpdateAssignee={Extensions.Prefix}assa))";
cmd.SetParam("assa", filter);
}
if (!string.IsNullOrEmpty(className))
{
sql += $" and c.Class={Extensions.Prefix}cls";
cmd.SetParam("cls", className);
}
if (!string.IsNullOrEmpty(baselineName))
{
sql += $" and c.Id={Extensions.Prefix}basename";
cmd.SetParam("basename", baselineName);
}
sql += " order by a.CreateTime desc";
cmd.CommandText = sql;
return cmd.Enumerate(x => Common.BLL.GetWorkNode.GetBaseData(x, fullData), skip, take)
.ToArray();
}
这里边就使用了2、3个 SQLHelper 方法。 public List<Model> runCmd(string cmdText)
{
using (var con = getCon())
{
con.Open();
OleDbCommand command = con.CreateCommand();
command.CommandText = "select a.fa,a.dt,b.number from ..........";
var dataReader = command.ExecuteReader();
return (from IDataRecord rd in reader
select Model
{
FieldA = (string)rd["fa"],
FieldB = (DateTime)rd["dt"]
FieldC = (double)rd["number"]
}).ToList();
}
}
public List<Model> runCmd(string cmdText, Func<IDataRecord, T> converter)
{
using (var con = getCon())
{
con.Open();
OleDbCommand command = con.CreateCommand();
command.CommandText = cmdText;
var dataReader = command.ExecuteReader();
return (from IDataRecord rd in reader
select converter(rd)).ToList();
}
}
[/code]
public OleDbConnection getCon()
{
return new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]));
}
public void runCmd(string cmdText, out OleDbDataReader dataReader)
{
using(OleDbConnection con = getCon()){
con.Open();
OleDbCommand command = con.CreateCommand();
command.CommandText = cmdText;
dataReader = command.ExecuteReader();
}
}