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;//δ�趨����
}
