public void Add_Parameter(QueryParameter Parameter)
{
DbParameter Dbp = this.ConvertParameter(Parameter);
if (Dbp != null) { this.mCmd.Parameters.Add(Dbp); }
this.mParameters.Add(Parameter);
}
public static DataSet ExecuteQuery(string ProcedureName, QueryParameter[] ProcedureParameters)
{
return Do_Methods_Query.ExecuteQuery(ProcedureName, ProcedureParameters.ToList());
}
/// <summary>
/// Get the list of QueryParameters associated in this QueryCondition
/// </summary>
/// <returns></returns>
public List<QueryParameter> GetParameters()
{
List<QueryParameter> List_Sp = new List<QueryParameter>();
foreach (Str_QueryCondition Obj in this.mQc)
{
string Name = Obj.Name;
string FieldName = Obj.FieldName;
object Value = Obj.Value;
string DataType = Obj.DataType.ToUpper();
QueryParameter Sp = new QueryParameter();
Sp.Name = "Condition_" + Name;
if (
DataType == typeof(string).Name.ToUpper()
|| DataType == typeof(string).ToString().ToUpper()
)
{
Sp.Type = Do_Constants.eParameterType.VarChar;
Sp.Size = 8000;
}
else if (
DataType == typeof(Int16).Name.ToUpper()
|| DataType == typeof(Int16).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_Int32(Value);
Sp.Type = Do_Constants.eParameterType.Int;
}
else if (
DataType == typeof(Int32).Name.ToUpper()
|| DataType == typeof(Int32).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_Int32(Value);
Sp.Type = Do_Constants.eParameterType.Int;
}
else if (
DataType == typeof(Int64).Name.ToUpper()
|| DataType == typeof(Int64).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_Int64(Value);
Sp.Type = Do_Constants.eParameterType.Long;
}
else if (
DataType == typeof(decimal).Name.ToUpper()
|| DataType == typeof(decimal).ToString().ToUpper()
|| DataType == typeof(double).Name.ToUpper()
|| DataType == typeof(double).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_Double(Value);
Sp.Type = Do_Constants.eParameterType.Numeric;
Sp.Precision = 18;
Sp.Scale = 4;
}
else if (
DataType == typeof(bool).Name.ToUpper()
|| DataType == typeof(bool).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_Boolean(Value);
Sp.Type = Do_Constants.eParameterType.Boolean;
}
else if (
DataType == typeof(DateTime).Name.ToUpper()
|| DataType == typeof(DateTime).ToString().ToUpper()
)
{
Value = Do_Methods.Convert_DateTime(Value);
Sp.Type = Do_Constants.eParameterType.DateTime;
}
Sp.Value = Value;
List_Sp.Add(Sp);
}
return List_Sp;
}
public void Add_Parameter(String Name, Do_Constants.eParameterType Type, Object Value = null, Int32 Size = 0, Byte Scale = 0, Byte Precision = 0)
{
QueryParameter Parameter =
new QueryParameter()
{
Name = Name,
Value = Value,
Type = Type,
Size = Size,
Scale = Scale,
Precision = Precision
};
this.Add_Parameter(Parameter);
}
//public void UpdateParameterValue(Do_Constants.Str_Parameters Parameter)
//{
// if (this.mParameters.Exists(X => X.Name == Parameter.Name))
// { this.mCmd.Parameters[Parameter.Name].Value = Parameter.Value; }
//}
//public void UpdateParameterValue(List<Do_Constants.Str_Parameters> Parameters)
//{
// foreach (Do_Constants.Str_Parameters Inner_Parameter in Parameters)
// { this.UpdateParameterValue(Inner_Parameter); }
//}
protected virtual DbParameter ConvertParameter(QueryParameter Parameter)
{
return null;
}
/// <summary>
/// Executes a SQL Server stored procedure and returns the resulting data set.
/// </summary>
/// <param name="ProcedureName">
/// The stored procedure name to be executed.
/// </param>
/// <param name="ProcedureParameters">
/// An array of parameters to use with the execution.
/// </param>
/// <returns></returns>
public DataSet ExecuteQuery(string ProcedureName, QueryParameter[] ProcedureParameters)
{
bool IsConnection = false;
if (this.mConnection != null)
{
if (this.mConnection.State == ConnectionState.Open)
{ IsConnection = true; }
}
if (!IsConnection)
{ this.Connect(); }
//[-]
SqlCommand Cmd = new SqlCommand();
SqlDataAdapter Adp = new SqlDataAdapter();
DataSet Ds = new DataSet();
try
{
Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandText = ProcedureName;
Cmd.CommandTimeout = CnsQueryTimeout;
if (!Information.IsNothing(ProcedureParameters))
{
foreach (QueryParameter Inner_Obj in ProcedureParameters)
{ Cmd.Parameters.AddWithValue(Inner_Obj.Name, Inner_Obj.Value); }
}
Cmd.Transaction = this.mTransaction;
Cmd.Connection = this.mConnection;
Adp.SelectCommand = Cmd;
Adp.Fill(Ds);
}
catch (Exception Ex)
{ throw Ex; }
finally
{
Cmd.Dispose();
Cmd = null;
Adp.Dispose();
Adp = null;
if (!IsConnection)
{ this.Close(); }
}
return Ds;
}
/// <summary>
/// Executes a SQL Server stored procedure.
/// </summary>
/// <param name="ProcedureName">
/// The stored procedure name to be executed.
/// </param>
/// <param name="ProcedureParameters">
/// An array of parameters to use with the execution.
/// </param>
/// <returns></returns>
public int ExecuteNonQuery(string ProcedureName, QueryParameter[] ProcedureParameters)
{
bool IsConnection = false;
if (this.mConnection != null)
{
if (this.mConnection.State == ConnectionState.Open)
{ IsConnection = true; }
}
if (!IsConnection) { this.Connect(); }
//[-]
SqlCommand Cmd = new SqlCommand();
int ReturnValue = 0;
try
{
Cmd.CommandType = System.Data.CommandType.StoredProcedure;
Cmd.CommandText = ProcedureName;
Cmd.CommandTimeout = CnsQueryTimeout;
if (!Information.IsNothing(ProcedureParameters))
{
foreach (QueryParameter Inner_Obj in ProcedureParameters)
{ Cmd.Parameters.AddWithValue(Inner_Obj.Name, Inner_Obj.Value); }
}
Cmd.Transaction = this.mTransaction;
Cmd.Connection = this.mConnection;
ReturnValue = Cmd.ExecuteNonQuery();
}
catch { }
finally
{
Cmd.Dispose();
Cmd = null;
if (!IsConnection)
{ this.Close(); }
}
return ReturnValue;
}
