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