//查询列表示例 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); }