//查询列表示例
public static void getlistcondition()
{
ConditionFieldModel condition = new ConditionFieldModel();
ConditionFieldItemModel fieldmodel = new ConditionFieldItemModel()
{
FieldName = "id", ValueMin = 0, ValueMax = 2012199998
};
condition.List.Add(fieldmodel);
ConditionFieldItemModel fieldmodel2 = new ConditionFieldItemModel()
{
FieldName = "num1", ValueMin = 0, ValueMax = 35000
};
condition.List.Add(fieldmodel2);
//ConditionFieldItemModel fieldmodel3 = new ConditionFieldItemModel() { FieldName = "num2", ValueMin = 300, ValueMax = 3500 };
//condition.List.Add(fieldmodel3);
long sqlnum = 0;
DBRead serverread = new DBRead();
//var d1 = serverread.GetCount(condition, ref sqlnum);
//Console.WriteLine("返回记录总数:" + d1 + ",执行数据库次数：" + sqlnum);
DataTable ddd2 = serverread.GetList(basemodel, condition, ref sqlnum);
Console.WriteLine("返回记录数:" + ddd2.Rows.Count + ",执行数据库次数：" + sqlnum);
}
public long GetCount(SqlBaseItemXml basemodel, ConditionFieldModel condition, ref long sqlnum)
{
sqlnum = 0;
long rownum = 0;
DataTable dt = new DataTable();
DBRule serverrule = new DBRule();
List <MatchServerList> serverlist = serverrule.GetMatchServer(basemodel, condition);
if (serverlist != null && serverlist.Count > 0)
{
List <TaskDataParam> taskdataparam = new List <TaskDataParam>();
foreach (var item in serverlist)
{
var servermodel = DBConfig.GetServerItemXmlConfig(basemodel, item.ServerNumber);
if (servermodel != null)
{
TaskDataParam tempparam = new TaskDataParam();
tempparam.servername = servermodel.ServerName;
tempparam.dbtype = servermodel.DBType;
tempparam.connstr = DBProxyAction.GetConnStr(basemodel, servermodel);
tempparam.sqlstr = DBProxyAction.GetSqlCount(servermodel, item, condition);
taskdataparam.Add(tempparam);
}
}
DBTask servicetask = new DBTask();
Console.WriteLine("满足条件的数据库表:" + taskdataparam.Count);
rownum = servicetask.SyncTaskManagerSum(taskdataparam, 50, false);
sqlnum = servicetask.runnumcurrent;
}
return(rownum);
}
public List <MatchServerList> GetMatchServer(SqlBaseItemXml basemodel, ConditionFieldModel conditionfield)
{
int matchnum = 0;
List <MatchServerList> ServerList = new List <MatchServerList>();
List <SqlFieldItemXml> fieldlist = new List <SqlFieldItemXml>();
if (conditionfield != null && conditionfield.List != null && conditionfield.List.Count > 0)
{
List <SqlFieldItemXml> templist = new List <SqlFieldItemXml>();
foreach (var condition in conditionfield.List)
{
long valuemin = condition.ValueMin;
long valuemax = condition.ValueMax;
if (matchnum < 1)
{
var tempfieldlist = DBConfig.GetFieldXmlConfig(basemodel).SqlFieldList.Where(m => m.FieldName == condition.FieldName).ToList();
foreach (var item in tempfieldlist)
{
if (GetMatchValid(item, condition.ValueMin, condition.ValueMax))
{
ServerList.Add(GetMatchObject(item));
}
}
}
else
{
int conditionvalidnum = 0;
foreach (var serverlist in ServerList.ToList())
{
var tempfield = DBConfig.GetFieldXmlConfig(basemodel).SqlFieldList.Where(m => m.TableNumber == serverlist.TableNumber && m.FieldName == condition.FieldName).ToList();
if (tempfield != null)
{
foreach (var itemfield in tempfield)
{
if (GetMatchValid(itemfield, condition.ValueMin, condition.ValueMax))
{
conditionvalidnum++;
break;
}
}
}
if (conditionvalidnum < 1)
{
ServerList.Remove(serverlist);
}
}
}
matchnum++;
}
}
return(ServerList);
}
public static string GetSqlWhere(SqlServerItemXml servermodel, ConditionFieldModel conditionfield)
{
StringBuilder sqlwhere = new StringBuilder();
if (conditionfield != null)
{
int i = 0;
foreach (var item in conditionfield.List)
{
if (i++ > 0)
{
sqlwhere.Append(" and ");
}
sqlwhere.AppendFormat(" {0} between {1} and {2} ", item.FieldName, item.ValueMin, item.ValueMax);
}
}
return(sqlwhere.ToString());
}
public static string GetSqlCount(SqlServerItemXml servermodel, MatchServerList item, ConditionFieldModel conditionfield)
{
return(string.Format(@"select COUNT(0) from {0}.dbo.{1} where {2}", item.DatabaseNumber, item.TableNumber, GetSqlWhere(servermodel, conditionfield)));
}
public static string GetSqlList(SqlServerItemXml servermodel, MatchServerList item, ConditionFieldModel conditionfield, long getmaxnum = 500)
{
return(string.Format(@"select top {3} * from {0}.dbo.{1} where {2} ", item.DatabaseNumber, item.TableNumber, GetSqlWhere(servermodel, conditionfield), getmaxnum));
}
public DataTable GetList(SqlBaseItemXml basemodel, ConditionFieldModel condition, ref long sqlnum)
{
sqlnum = 0;
long getmaxnum = 500;
long getrowcount = 0;
DataTable dt = new DataTable();
DBRule serverrule = new DBRule();
List <MatchServerList> serverlist = serverrule.GetMatchServer(basemodel, condition);
if (serverlist != null && serverlist.Count > 0)
{
List <TaskDataParam> taskdataparam = new List <TaskDataParam>();
foreach (var item in serverlist)
{
var servermodel = DBConfig.GetServerItemXmlConfig(basemodel, item.ServerNumber);
if (servermodel != null)
{
TaskDataParam tempparam = new TaskDataParam();
tempparam.servername = servermodel.ServerName;
tempparam.dbtype = servermodel.DBType;
tempparam.connstr = DBProxyAction.GetConnStr(basemodel, servermodel);
tempparam.sqlstr = DBProxyAction.GetSqlList(servermodel, item, condition, getmaxnum);
taskdataparam.Add(tempparam);
}
}
Console.WriteLine("满足条件的数据库表:" + taskdataparam.Count);
foreach (var itemparam in taskdataparam)
{
Console.WriteLine("访问服务器:" + itemparam.servername);
var dttemp = DBProxy.GetDBHelper(itemparam.dbtype).GetDataDable(itemparam.connstr, itemparam.sqlstr);
sqlnum++;
if (dttemp != null && dttemp.Rows.Count > 0)
{
var dttempcount = dttemp.Rows.Count;
if (getrowcount > 0)
{
foreach (DataRow dtrow in dttemp.Rows)
{
if (getrowcount >= getmaxnum)
{
return(dt);
}
getrowcount++;
DataRow r = dt.NewRow();
r.ItemArray = dtrow.ItemArray;
dt.Rows.Add(r);
}
}
else
{
getrowcount = dttemp.Rows.Count;
dt = dttemp;
}
}
if (getrowcount >= getmaxnum)
{
return(dt);
}
}
}
return(dt);
}
