public DataSet GetDataSet(MyWebControlLib.FieldValues FVS) { if (_viewname != "") { string condition = " (1=1) "; for (int i = 0; i < FVS.Count; i++) { string values = ""; FVOperater o = FVS[i].FVOperater; switch (o) { case FVOperater.Great: values = " > '" + FVS[i].Value + "' "; break; case FVOperater.Less: values = " < '" + FVS[i].Value + "' "; break; case FVOperater.LikeB: values = " Like '%" + FVS[i].Value + "' "; break; case FVOperater.LikeBE: values = " Like '%" + FVS[i].Value + "%' "; break; case FVOperater.LikeE: values = " Like '" + FVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = " <= '" + FVS[i].Value + "' "; break; case FVOperater.NotLess: values = " >= '" + FVS[i].Value + "' "; break; case FVOperater.In: values = " in (" + FVS[i].Value + ") "; break; case FVOperater.NotIn: values = " not in (" + FVS[i].Value + ") "; break; case FVOperater.NotEqual: values = " <> '" + FVS[i].Value + "' "; break; case FVOperater.Order: break; default: values = " = '" + FVS[i].Value + "' "; break; } condition = condition + _conditionOperater + FVS[i].FieldName + values; } string order = ""; ArrayList orderlist = new ArrayList(); ArrayList ordermethod = new ArrayList(); for (int i = 0; i < FVS.Count; i++) { if ((FVS[i].Ordered == true) & (orderlist.IndexOf(FVS[i].FieldName) == -1)) orderlist.Add(FVS[i].FieldName); if (FVS[i].orderMethod == OrderMenthod.ASC) ordermethod.Add(" ASC "); else ordermethod.Add(" DESC "); } for (int i = 0; i < orderlist.Count; i++) { order = order + orderlist[i].ToString() + ordermethod[i].ToString() + ","; } if (order != "") { order = " Order By " + order; order = order.Substring(0, order.Length - 1); } SqlDataAdapter adapter = new SqlDataAdapter("select " + _columnNames + " from " + _viewname + " where " + condition + order, conn); adapter.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(_viewname); adapter.Fill(ds, _viewname); return ds; } else return null; }
public DataSet GetDataSetFromTable(MyWebControlLib.FieldValues FieldFVS, MyWebControlLib.FieldValues ConditionFVS) { if (_tablename != "") { //���ɲ�ѯ���� string condition = " (1=1) "; for (int i = 0; i < ConditionFVS.Count; i++) { string values = ""; FVOperater o = ConditionFVS[i].FVOperater; switch (o) { case FVOperater.Great: values = " > '" + ConditionFVS[i].Value + "' "; break; case FVOperater.Less: values = " < '" + ConditionFVS[i].Value + "' "; break; case FVOperater.LikeB: values = " Like '%" + ConditionFVS[i].Value + "' "; break; case FVOperater.LikeBE: values = " Like '%" + ConditionFVS[i].Value + "%' "; break; case FVOperater.LikeE: values = " Like '" + ConditionFVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = " <= '" + ConditionFVS[i].Value + "' "; break; case FVOperater.NotLess: values = " >= '" + ConditionFVS[i].Value + "' "; break; case FVOperater.In: values = " in (" + ConditionFVS[i].Value + ") "; break; case FVOperater.NotIn: values = " not in (" + ConditionFVS[i].Value + ") "; break; case FVOperater.NotEqual: values = " <> '" + ConditionFVS[i].Value + "' "; break; default: values = " = '" + ConditionFVS[i].Value + "' "; break; } condition = condition + _conditionOperater + ConditionFVS[i].FieldName + values; } //����Ŀ���� string DestinationField = ""; for (int i = 0; i < FieldFVS.Count; i++) { string fieldname = FieldFVS[i].FieldName; MyWebControlLib.StatMethod statmethod = FieldFVS[i].statMethod; string distinct = ""; if (FieldFVS[i].Distinct) distinct = " Distinct "; else distinct = ""; string method = "Sum"; switch (statmethod) { case StatMethod.Avg: method = "Avg"; break; case StatMethod.Count: method = "Count"; break; case StatMethod.Max: method = "Max"; break; case StatMethod.Min: method = "Min"; break; case StatMethod.Sum: method = "Sum"; break; default: method = ""; break; } if (method == "") DestinationField = DestinationField + method + distinct + fieldname + " as " + FieldFVS[i].FieldName + ","; else DestinationField = DestinationField + method + "(" + distinct + fieldname + ") as " + FieldFVS[i].FieldName + ","; } DestinationField = DestinationField.Substring(0, DestinationField.Length - 1); //���ɷǾۺ��ֶη������� string groupby = ""; for (int i = 0; i < FieldFVS.Count; i++) { if (FieldFVS[i].statMethod == MyWebControlLib.StatMethod.None) groupby = groupby + FieldFVS[i].FieldName + ","; } if (groupby != "") { groupby = " Group by " + groupby.Substring(0, groupby.Length - 1); } //�����ֶ� string order = ""; ArrayList orderlist = new ArrayList(); ArrayList ordermethod = new ArrayList(); for (int i = 0; i < ConditionFVS.Count; i++) { if ((ConditionFVS[i].Ordered == true) & (orderlist.IndexOf(ConditionFVS[i].FieldName) == -1)) orderlist.Add(ConditionFVS[i].FieldName); if (ConditionFVS[i].orderMethod == OrderMenthod.ASC) ordermethod.Add(" ASC "); else ordermethod.Add(" DESC "); // order = order + ConditionFVS[i].FieldName + ","; } for (int i = 0; i < FieldFVS.Count; i++) { if ((FieldFVS[i].Ordered == true) & (orderlist.IndexOf(FieldFVS[i].FieldName) == -1)) orderlist.Add(FieldFVS[i].FieldName); if (FieldFVS[i].orderMethod == OrderMenthod.ASC) ordermethod.Add(" ASC "); else ordermethod.Add(" DESC "); // order = order + ConditionFVS[i].FieldName + ","; } for (int i = 0; i < orderlist.Count; i++) { order = order + orderlist[i].ToString() + ordermethod[i].ToString() + ","; } if (order != "") { order = " Order By " + order; order = order.Substring(0, order.Length - 1); } SqlDataAdapter adapter = new SqlDataAdapter("select " + DestinationField + " from " + _tablename + " where " + condition + groupby + order, conn); adapter.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(_tablename); adapter.Fill(ds, _tablename); return ds; } else return null; }
public int GetRecordCount(MyWebControlLib.FieldValues FVS) { if (_viewname != "") { //���ɲ�ѯ���� string condition = " (1=1) "; for (int i = 0; i < FVS.Count; i++) { string values = ""; FVOperater o = FVS[i].FVOperater; switch (o) { case FVOperater.Great: values = " > '" + FVS[i].Value + "' "; break; case FVOperater.Less: values = " < '" + FVS[i].Value + "' "; break; case FVOperater.LikeB: values = " Like '%" + FVS[i].Value + "' "; break; case FVOperater.LikeBE: values = " Like '%" + FVS[i].Value + "%' "; break; case FVOperater.LikeE: values = " Like '" + FVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = " <= '" + FVS[i].Value + "' "; break; case FVOperater.NotLess: values = " >= '" + FVS[i].Value + "' "; break; case FVOperater.In: values = " in (" + FVS[i].Value + ") "; break; case FVOperater.NotIn: values = " not in (" + FVS[i].Value + ") "; break; case FVOperater.NotEqual: values = " <> '" + FVS[i].Value + "' "; break; default: values = " = '" + FVS[i].Value + "' "; break; } condition = condition + _conditionOperater + FVS[i].FieldName + values; } if (conn.State != ConnectionState.Open) conn.Open(); SqlCommand command = new SqlCommand("select count(tableID) from " + _viewname + " where " + condition, conn); command.CommandTimeout = 0; SqlDataReader reader = command.ExecuteReader(); int returnvalue = 0; if ((reader.HasRows) && (reader.Read())) returnvalue = Convert.ToInt32(reader.GetValue(0).ToString()); conn.Close(); return returnvalue; } else return 0; }
public int Update(MyWebControlLib.FieldValues FV, int tableID) { if (_tablename != "") { DBAccess.DBHelper h = new DBAccess.DBHelper(); SqlDataAdapter adapter = h.GetSqlDataAdapter(_tablename); adapter.SelectCommand.CommandText = "select * from " + _tablename + " where tableID=" + tableID.ToString(); DataSet ds = new DataSet(_tablename); adapter.Fill(ds, _tablename); if (ds.Tables[0].Rows.Count > 0) { DataRow row = ds.Tables[0].Rows[0]; for (int i = 0; i < FV.Count; i++) { if (FV[i].Value == null) { row[FV[i].FieldName] = System.DBNull.Value; } else { row[FV[i].FieldName] = FV[i].Value; } } try { adapter.Update(ds, _tablename); return 0; } catch { return -1; } } else //Ϊ�ҵ���ؼ�¼ return -2; } else return -3;//δ���ñ��� }
public int Delete(MyWebControlLib.FieldValues FVS) { if (_tablename != "") { DBAccess.DBHelper h = new DBAccess.DBHelper(); SqlDataAdapter adapter = h.GetSqlDataAdapter(_tablename); string condition = " (1=1) "; for (int i = 0; i < FVS.Count; i++) { string values = ""; FVOperater o = FVS[i].FVOperater; switch (o) { case FVOperater.Great: values = " > '" + FVS[i].Value + "' "; break; case FVOperater.Less: values = " < '" + FVS[i].Value + "' "; break; case FVOperater.LikeB: values = " Like '%" + FVS[i].Value + "' "; break; case FVOperater.LikeBE: values = " Like '%" + FVS[i].Value + "%' "; break; case FVOperater.LikeE: values = " Like '" + FVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = " <= '" + FVS[i].Value + "' "; break; case FVOperater.NotLess: values = " >= '" + FVS[i].Value + "' "; break; case FVOperater.In: values = " in (" + FVS[i].Value + ") "; break; case FVOperater.NotIn: values = " not in (" + FVS[i].Value + ") "; break; case FVOperater.NotEqual: values = " <> '" + FVS[i].Value + "' "; break; case FVOperater.Order: break; default: values = " = '" + FVS[i].Value + "' "; break; } condition = condition + _conditionOperater + FVS[i].FieldName + values; } adapter.SelectCommand.CommandText = "select * from " + _tablename + " where " + condition; DataSet ds = new DataSet(_tablename); adapter.Fill(ds, _tablename); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) ds.Tables[0].Rows[i].Delete(); //ʹ��������V2 DBAccess.DBHelper dh = new DBAccess.DBHelper(); dh.Update(ds); //��ʹ��������V1 //adapter.Update(ds,_tablename); return 0; } else return -3; }
public DataTable GetOriginTableByKeys(MyWebControlLib.FieldValues FVS) { if (_tablename != "") { string values = ""; string condition = "(1=1)"; for (int i = 0; i < FVS.Count; i++) { FVOperater o = FVS[i].FVOperater; switch (o) { case FVOperater.Great: values = _conditionOperater + FVS[i].FieldName + " > '" + FVS[i].Value + "' "; break; case FVOperater.Less: values = _conditionOperater + FVS[i].FieldName + " < '" + FVS[i].Value + "' "; break; case FVOperater.LikeB: values = _conditionOperater + FVS[i].FieldName + " Like '%" + FVS[i].Value + "' "; break; case FVOperater.LikeBE: values = _conditionOperater + FVS[i].FieldName + " Like '%" + FVS[i].Value + "%' "; break; case FVOperater.LikeE: values = _conditionOperater + FVS[i].FieldName + " Like '" + FVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = _conditionOperater + FVS[i].FieldName + " <= '" + FVS[i].Value + "' "; break; case FVOperater.NotLess: values = _conditionOperater + FVS[i].FieldName + " >= '" + FVS[i].Value + "' "; break; case FVOperater.In: values = _conditionOperater + FVS[i].FieldName + " in (" + FVS[i].Value + ") "; break; case FVOperater.NotIn: values = _conditionOperater + FVS[i].FieldName + " not in (" + FVS[i].Value + ") "; break; case FVOperater.NotEqual: values = _conditionOperater + FVS[i].FieldName + " <> '" + FVS[i].Value + "' "; break; case FVOperater.Order: values = "Order by " + FVS[i].FieldName + " " + FVS[i].orderMethod; break; default: values = _conditionOperater + FVS[i].FieldName + " = '" + FVS[i].Value + "' "; break; } condition = condition + values; } SqlDataAdapter adapter = new SqlDataAdapter("select * from " + _tablename + " where " + condition, conn); adapter.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(_tablename); adapter.Fill(ds, _tablename); return ds.Tables[0]; } else return null; }
public DataTable GetDataTable(MyWebControlLib.FieldValues FieldFVS, string condition) { if (_viewname != "") { string DestinationField = ""; for (int i = 0; i < FieldFVS.Count; i++) { string fieldname = FieldFVS[i].FieldName; MyWebControlLib.StatMethod statmethod = FieldFVS[i].statMethod; string distinct = ""; if (FieldFVS[i].Distinct) distinct = " Distinct "; else distinct = ""; string method = "Sum"; switch (statmethod) { case StatMethod.Avg: method = "Avg"; break; case StatMethod.Count: method = "Count"; break; case StatMethod.Max: method = "Max"; break; case StatMethod.Min: method = "Min"; break; case StatMethod.Sum: method = "Sum"; break; default: method = ""; break; } if (method == "") DestinationField = DestinationField + method + distinct + fieldname + " as " + FieldFVS[i].FieldName + ","; else DestinationField = DestinationField + method + "(" + distinct + fieldname + ") as " + FieldFVS[i].FieldName + ","; } DestinationField = DestinationField.Substring(0, DestinationField.Length - 1); //���ɷǾۺ��ֶη������� string groupby = ""; for (int i = 0; i < FieldFVS.Count; i++) { if (FieldFVS[i].statMethod == MyWebControlLib.StatMethod.None) groupby = groupby + FieldFVS[i].FieldName + ","; } if (groupby != "") { groupby = " Group by " + groupby.Substring(0, groupby.Length - 1); } //�����ֶ� string order = ""; ArrayList orderlist = new ArrayList(); ArrayList ordermethod = new ArrayList(); for (int i = 0; i < FieldFVS.Count; i++) { if ((FieldFVS[i].Ordered == true) & (orderlist.IndexOf(FieldFVS[i].FieldName) == -1)) orderlist.Add(FieldFVS[i].FieldName); if (FieldFVS[i].orderMethod == OrderMenthod.ASC) ordermethod.Add(" ASC "); else ordermethod.Add(" DESC "); // order = order + ConditionFVS[i].FieldName + ","; } for (int i = 0; i < orderlist.Count; i++) { order = order + orderlist[i].ToString() + ordermethod[i].ToString() + ","; } if (order != "") { order = " Order By " + order; order = order.Substring(0, order.Length - 1); } if (condition.Trim() == "") condition = " 1=1 "; SqlDataAdapter adapter = new SqlDataAdapter("select " + DestinationField + " from " + _viewname + " where " + condition + " " + groupby + order, conn); adapter.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(_viewname); adapter.Fill(ds, _viewname); return ds.Tables[0]; } else return null; }
public DataTable GetDataTable(int PageSize, int CurrentPageIndex, MyWebControlLib.FieldValues FVS, MyWebControlLib.FieldValues FVS1) { // int RecordCount = GetRecordCount(FVS); int hadReadRecordCount = (CurrentPageIndex - 1) * PageSize; if (_viewname != "") { string condition = " (1=1) "; for (int i = 0; i < FVS.Count; i++) { string values = ""; FVOperater o = FVS[i].FVOperater; switch (o) { case FVOperater.Great: values = _conditionOperater + FVS[i].FieldName + " > '" + FVS[i].Value + "' "; break; case FVOperater.Less: values = _conditionOperater + FVS[i].FieldName + " < '" + FVS[i].Value + "' "; break; case FVOperater.LikeB: values = _conditionOperater + FVS[i].FieldName + " Like '%" + FVS[i].Value + "' "; break; case FVOperater.LikeBE: values = _conditionOperater + FVS[i].FieldName + " Like '%" + FVS[i].Value + "%' "; break; case FVOperater.LikeE: values = _conditionOperater + FVS[i].FieldName + " Like '" + FVS[i].Value + "%' "; break; case FVOperater.NotGreat: values = _conditionOperater + FVS[i].FieldName + " <= '" + FVS[i].Value + "' "; break; case FVOperater.NotLess: values = _conditionOperater + FVS[i].FieldName + " >= '" + FVS[i].Value + "' "; break; case FVOperater.In: values = _conditionOperater + FVS[i].FieldName + " in (" + FVS[i].Value + ") "; break; case FVOperater.NotIn: values = _conditionOperater + FVS[i].FieldName + " not in (" + FVS[i].Value + ") "; break; case FVOperater.NotEqual: values = _conditionOperater + FVS[i].FieldName + " <> '" + FVS[i].Value + "' "; break; case FVOperater.Order: break; default: values = _conditionOperater + FVS[i].FieldName + " = '" + FVS[i].Value + "' "; break; } condition = condition + values; } string condition1 = ""; for (int k = 0; k < FVS1.Count; k++) { string values1 = ""; string conditionOR = " or "; FVOperater OP = FVS1[k].FVOperater; switch (OP) { case FVOperater.Great: values1 = conditionOR + FVS1[k].FieldName + " > '" + FVS1[k].Value + "' "; break; case FVOperater.Less: values1 = conditionOR + FVS1[k].FieldName + " < '" + FVS1[k].Value + "' "; break; case FVOperater.LikeB: values1 = conditionOR + FVS1[k].FieldName + " Like '%" + FVS1[k].Value + "' "; break; case FVOperater.LikeBE: values1 = conditionOR + FVS1[k].FieldName + " Like '%" + FVS1[k].Value + "%' "; break; case FVOperater.LikeE: values1 = conditionOR + FVS1[k].FieldName + " Like '" + FVS1[k].Value + "%' "; break; case FVOperater.NotGreat: values1 = conditionOR + FVS1[k].FieldName + " <= '" + FVS1[k].Value + "' "; break; case FVOperater.NotLess: values1 = conditionOR + FVS1[k].FieldName + " >= '" + FVS1[k].Value + "' "; break; case FVOperater.In: values1 = conditionOR + FVS1[k].FieldName + " in (" + FVS1[k].Value + ") "; break; case FVOperater.NotIn: values1 = conditionOR + FVS1[k].FieldName + " not in (" + FVS1[k].Value + ") "; break; case FVOperater.NotEqual: values1 = conditionOR + FVS1[k].FieldName + " <> '" + FVS1[k].Value + "' "; break; case FVOperater.Order: break; default: values1 = conditionOR + FVS1[k].FieldName + " = '" + FVS1[k].Value + "' "; break; } condition1 = condition1 + values1; } if (condition1.Length > 3) { condition1 = condition1.Substring(3, condition1.Length - 3); condition = condition + " and (" + condition1 + ")"; } string order = ""; ArrayList orderlist = new ArrayList(); ArrayList ordermethod = new ArrayList(); for (int i = 0; i < FVS.Count; i++) { if ((FVS[i].Ordered == true) & (orderlist.IndexOf(FVS[i].FieldName) == -1)) { orderlist.Add(FVS[i].FieldName); if (FVS[i].orderMethod == OrderMenthod.ASC) ordermethod.Add(" ASC "); else ordermethod.Add(" DESC "); } } for (int i = 0; i < orderlist.Count; i++) { order = order + orderlist[i].ToString() + ordermethod[i].ToString() + ","; } if (order != "") { order = " Order By " + order; order = order.Substring(0, order.Length - 1); } SqlDataAdapter adapter = new SqlDataAdapter("select top " + Convert.ToString(PageSize) + " " + _columnNames + " from " + _viewname + " where (tableID not in (select top " + Convert.ToString(hadReadRecordCount) + " tableID from " + _viewname + " where " + condition + order + ")) and " + condition + order, conn); adapter.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(_viewname); adapter.Fill(ds, _viewname); return ds.Tables[0]; } else return null; }
public int Add(MyWebControlLib.FieldValues FV) { if (_tablename != "") { DBAccess.DBHelper h = new DBAccess.DBHelper(); SqlDataAdapter adapter = h.GetSqlDataAdapter(_tablename); DataSet ds = new DataSet(_tablename); adapter.Fill(ds, _tablename); DataRow row = ds.Tables[0].NewRow(); for (int i = 0; i < FV.Count; i++) { row[FV[i].FieldName] = FV[i].Value; } ds.Tables[0].Rows.Add(row); try { adapter.Update(ds, _tablename); return 0; } catch { return -1; } } else return -3;//δ�趨���� }