public DataTable exeSelectPro(DataCollections _DataCol, String _Pro_Name)
{
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _Pro_Name;
SqlParameter myParam;
for (int i = 0; i < _DataCol.Count(); ++i)
{
myParam = new SqlParameter();
myParam.ParameterName = _DataCol[i].DataField;
myParam.Value = _DataCol[i].DataValue;
cmd.Parameters.Add(myParam);
}
SqlDataAdapter dA = new SqlDataAdapter(cmd);
DataSet dS = new DataSet();
dA.Fill(dS);
return(dS.Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
else
{
return(new DataTable());
}
}
public int exeInsertProWithoutID(DataCollections _DataCol, String _Pro_Name)
{
if (ConnectValid() == true)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _Pro_Name;
SqlParameter myParam;
for (int i = 0; i < _DataCol.Count(); ++i)
{
myParam = new SqlParameter();
myParam.ParameterName = _DataCol[i].DataField;
myParam.Value = _DataCol[i].DataValue;
cmd.Parameters.Add(myParam);
}
cmd.ExecuteNonQuery();
return(1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(-1);
}
}
else
{
return(-1);
}
}
public String ToSQLString(QueryTypes _QueryType, DataCollections _DataCol)
{
String sSQL = "", temp = "", temp1 = "";
String strWhere = "";
DateTime?tempDate;
if (_QueryType == QueryTypes.Insert)
{
for (int i = 0; i < _DataCol.Count(); ++i)
{
if (_DataCol[i].FieldType != FieldTypes.AutoIncrement)
{
temp += _DataCol[i].DataField;
switch (_DataCol[i].DataType)
{
case DataTypes.NVarchar:
{
temp1 += "N'" + _DataCol[i].DataValue + "'";
break;
}
case DataTypes.Number:
{
if (_DataCol[i].DataValue == null)
{
temp1 += "null";
}
else
{
temp1 += "'" + _DataCol[i].DataValue + "'";
}
break;
}
case DataTypes.DateTime:
{
if (_DataCol[i].DataValue == null || Convert.ToDateTime(_DataCol[i].DataValue) == DateTime.MinValue)
{
temp1 += "null";
}
else
{
temp1 += "'" + _DataCol[i].DataValue + "'";
}
break;
}
default:
{
temp1 += _DataCol[i].DataValue;
break;
}
}
if (i < _DataCol.Count() - 1)
{
temp += ",";
temp1 += ",";
}
}
else
{
temp += _DataCol[i].DataField;
temp1 += _DataCol.DataTable + "_SEQ.nextval";
if (i < _DataCol.Count() - 1)
{
temp += ",";
temp1 += ",";
}
}
} //end of for
sSQL = "INSERT INTO " + _DataCol.DataTable + "(" + temp + ")" + " VALUES(" + temp1 + ")";
} //end of Insert
else if (_QueryType == QueryTypes.Update)
{
for (int i = 0; i < _DataCol.Count(); ++i)
{
if (_DataCol[i].FieldType == FieldTypes.Criterion)
{
if (strWhere != "")
{
strWhere += " AND ";
}
if (strWhere == "")
{
strWhere += " WHERE ";
}
switch (_DataCol[i].DataType)
{
case DataTypes.NVarchar:
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " N'" + _DataCol[i].DataValue + "'";
break;
}
case DataTypes.DateTime:
{
if (_DataCol[i].DataValue == null || Convert.ToDateTime(_DataCol[i].DataValue) == DateTime.MinValue)
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " null";
}
else
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " '" + _DataCol[i].DataValue + "'";
}
break;
}
case DataTypes.Number:
{
if (_DataCol[i].DataValue == null)
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " null";
}
else
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " '" + _DataCol[i].DataValue + "'";
}
break;
}
default:
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + _DataCol[i].DataValue;
break;
}
}
}
else
{
if (temp1 != "")
{
temp1 += ",";
}
switch (_DataCol[i].DataType)
{
case DataTypes.NVarchar:
{
temp1 += _DataCol[i].DataField + " = N'" + _DataCol[i].DataValue + "'";
break;
}
case DataTypes.DateTime:
{
if (_DataCol[i].DataValue == null || Convert.ToDateTime(_DataCol[i].DataValue) == DateTime.MinValue)
{
temp1 += _DataCol[i].DataField + " = null";
}
else
{
temp1 += _DataCol[i].DataField + " = '" + _DataCol[i].DataValue + "'";
}
break;
}
case DataTypes.Number:
{
if (_DataCol[i].DataValue == null)
{
temp1 += _DataCol[i].DataField + " = null";
}
else
{
temp1 += _DataCol[i].DataField + " = '" + _DataCol[i].DataValue + "'";
}
break;
}
case DataTypes.Null:
{
temp1 += _DataCol[i].DataField + " = null";
break;
}
default:
{
temp1 += _DataCol[i].DataField + " = " + _DataCol[i].DataValue;
break;
}
}
}//end of if
}
sSQL = "UPDATE " + _DataCol.DataTable + " SET " + temp1 + strWhere;
}//end of update
else if (_QueryType == QueryTypes.SelectQuery)
{
for (int i = 0; i < _DataCol.Count(); ++i)
{
if (_DataCol[i].FieldType == FieldTypes.Criterion)
{
if (strWhere != "")
{
strWhere += " AND ";
}
if (strWhere == "")
{
strWhere += " WHERE ";
}
switch (_DataCol[i].DataType)
{
case DataTypes.NVarchar:
{
if (_DataCol[i].Expression.ToUpper() == "LIKE")
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + "N'%" + _DataCol[i].DataValue + "%'";
}
else if (_DataCol[i].Expression.ToUpper() == "CHARINDEX")
{
strWhere += "CHARINDEX(" + _DataCol[i].DataField + "," + _DataCol[i].DataValue + ") >=0";
}
else
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " '" + _DataCol[i].DataValue + "'";
}
break;
}
case DataTypes.DateTime:
{
tempDate = Convert.ToDateTime(_DataCol[i].DataValue);
if (tempDate.HasValue)
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " '" + _DataCol[i].DataValue + "'";
}
else
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " null";
}
break;
}
default:
{
if (_DataCol[i].Expression.ToUpper() == "LIKE" || _DataCol[i].Expression.ToUpper() == "NOT LIKE")
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + "N'%" + _DataCol[i].DataValue + "%'";
}
else
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + _DataCol[i].DataValue;
}
break;
}
}
}
else
{
if (temp1 != "")
{
temp1 += ",";
}
temp1 += _DataCol[i].DataField;
}
}//end of for
sSQL = "SELECT " + temp1 + " FROM " + _DataCol.DataTable + strWhere;
if (_DataCol.FILTER != "")
{
if (sSQL.IndexOf("WHERE") < 0)
{
sSQL += " WHERE ";
}
else
{
sSQL += " AND ";
}
sSQL += _DataCol.FILTER;
}
sSQL += " " + _DataCol.ORDERBY;
}//end of SelectQuery
else if (_QueryType == QueryTypes.Delete)
{
for (int i = 0; i < _DataCol.Count(); ++i)
{
if (_DataCol[i].FieldType == FieldTypes.Criterion)
{
if (strWhere != "")
{
strWhere += " AND ";
}
if (strWhere == "")
{
strWhere += " WHERE ";
}
switch (_DataCol[i].DataType)
{
case DataTypes.NVarchar:
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + "'" + _DataCol[i].DataValue + "'";
break;
}
default:
{
strWhere += _DataCol[i].DataField + " " + _DataCol[i].Expression + " " + _DataCol[i].DataValue;
break;
}
}
}
}//end of for
sSQL = "DELETE FROM " + _DataCol.DataTable + strWhere;
}
return(sSQL);
}//end of method