public override string ToParametrizedString(SqlParameterCollection list)
{
if (Value is Array)
return ToString();
var parameter = list.AddOrGet(Value);
return parameter.ToString();
}
public SqlQuery()
{
Tables = new List<SqlTable>();
ColumnsOutput = new List<SqlColumnOutput>();
ColumnsOutput.Add(new SqlColumnOutput("*"));
Joins = new List<SqlJoin>();
OrderBy = new List<SqlOrderByColumn>();
LockColumnOutput = false;
Parameters = new SqlParameterCollection();
}
public override string ToParametrizedString(SqlParameterCollection list)
{
var builder = new StringBuilder();
if (Type == SqlLikeType.StartsWith || Type == SqlLikeType.Contains) {
builder.Append("%");
}
builder.Append(Value);
if (Type == SqlLikeType.EndsWith || Type == SqlLikeType.Contains) {
builder.Append("%");
}
var parameter = list.AddOrGet(builder.ToString());
return parameter.ToString();
//var parameter = list.AddOrGet(Value);
//var builder = new StringBuilder();
//if (Type == SqlLikeType.StartsWith || Type == SqlLikeType.Contains) {
// builder.Append("'%' + ");
//}
//builder.Append(parameter);
//if (Type == SqlLikeType.EndsWith || Type == SqlLikeType.Contains) {
// builder.Append(" + '%'");
//}
//return builder.ToString();
}
public override string GetJoinSQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
return(Data.DataObjectCommunity.GetJoinSQL(qParas, parameters));
}
public ParameterDialog(SqlParameterCollection collection)
{
InitializeComponent();
this.collection = collection;
this.dataGrid1.DataSource = this.collection;
}
/// <summary>
/// Executes the stored procedure with the given name, passes the provided parameters and returns a SqlDataReader
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="outputParameters">The output parameters.</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string storedProcedureName, IEnumerable <SqlParameter> parameters, out SqlParameterCollection outputParameters)
{
return(ExecuteReader(storedProcedureName, null, parameters, out outputParameters));
}
public static SqlParameterCollection AddCacheItemId(this SqlParameterCollection parameters, string value)
{
return(parameters.AddWithValue(Columns.Names.CacheItemId, SqlDbType.NVarChar, CacheItemIdColumnWidth, value));
}
protected void btnSave_Click(object sender, System.EventArgs e)
{
string tranName = "UpdateSubCategoryStats";
DBase.BeginTransaction(tranName);
//1. Save/update Subcategory details
SqlCommand oCmd = DBase.GetCommand(Config.DbSaveSubCategoryDetails);
SqlParameterCollection oParams = oCmd.Parameters;
oParams.Add("@ID", nSubCategoryID);
oParams.Add("@Name", txtName.Text);
oParams.Add("@StatusID", Core.GetSelectedValueInt(comboStatus));
oParams.Add("@CategoryID", Core.GetSelectedValueInt(comboCategory));
int nRes = DBase.ExecuteReturnInt(oCmd);
if (nRes <= 0)
{
lblInfo.ForeColor = Color.Red;
switch (nRes)
{
case -1:
lblInfo.Text = "Typed category name already exists";
break;
default:
lblInfo.Text = "Database error occured";
break;
}
DBase.RollbackTransaction(tranName);
return;
}
//2. update SubCategoryStats table
DataTable oDT = DBase.GetDataTableBatch(string.Format(Config.DbUpdateSubCategoryStats, nRes));
DataColumn[] PrimaryKeyArray = new DataColumn[2];
PrimaryKeyArray[0] = oDT.Columns["SubCategoryID"];
PrimaryKeyArray[1] = oDT.Columns["StatsTypeID"];
oDT.PrimaryKey = PrimaryKeyArray;
try
{
//a. Delete rows that are not any more in the list
string[] statsList = hdnStatsList.Value.Split(',');
int statsLen = statsList.Length;
if (hdnStatsList.Value == "")
{
statsLen = 0;
}
int k = 0;
while (k < oDT.Rows.Count)
{
DataRow oRow = oDT.Rows[k];
bool bFind = false;
for (int i = 0; i < statsLen; i++)
{
if (oRow["StatsTypeID"].ToString() == statsList[i])
{
bFind = true;
}
}
if (!bFind)
{
oRow.Delete();
}
k++;
}
//b. Add/update order for the list
for (int i = 0; i < statsLen; i++)
{
DataRow oRow = oDT.Rows.Find(new object[] { nRes, statsList[i] });
if (oRow == null)
{
oDT.Rows.Add(new object[] { nSubCategoryID, statsList[i], i + 1 });
}
else
{
if (Convert.ToInt32(oRow["Order"]) != i + 1)
{
oRow["Order"] = i + 1;
}
}
}
}
catch (Exception oEx)
{
Log.Write(this, "DataTable operations Error: " + oEx.Message);
DBase.RollbackTransaction(tranName);
lblInfo.Text = "DataBase error occured during updating table";
lblInfo.ForeColor = Color.Red;
return;
}
bool bRes = DBase.Update(oDT);
if (bRes)
{
DBase.CommitTransaction(tranName);
hdnSubCategoryID.Value = nRes.ToString();
StoreBackID(nRes);
lblInfo.Text = "Category details have been saved";
lblInfo.ForeColor = Color.Green;
Response.Redirect(GetGoBackUrl());
nSubCategoryID = nRes;
BindStatsList();
rowRelatedProcesses.Visible = true;
BindRelatedProcessList();
}
else
{
DBase.RollbackTransaction(tranName);
lblInfo.Text = "DataBase error occured during updating table";
lblInfo.ForeColor = Color.Red;
}
}
/// <summary>
/// Executes the stored procedure with the given name, passes the provided parameters and returns the result as a DataTable
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="sqlParameters">The sql parameters.</param>
/// <param name="outputParameters">The output parameters.</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string storedProcedureName, Dictionary <string, object> parameters, IEnumerable <SqlParameter> sqlParameters, out SqlParameterCollection outputParameters)
{
SqlCommand command = GenerateCommand(storedProcedureName, parameters, sqlParameters);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Table");
outputParameters = command.Parameters;
DoPostCommandProcessing();
return(dataSet.Tables["Table"]);
}
/// <summary>
/// Ejecuta Stored Procedure pasando el Nombre y los parametros con sus valores
/// </summary>
/// <param name="_nombreSp">Nombre del Stored Procedure de la Base de Datos</param>
/// <param name="parametros">Array de los parametros y valores siguiendo el patron "@parametro=valor=System.Tipo"</param>
/// <returns>Valor del retorno como String</returns>
/// <remarks></remarks>
public string ejecutarSP(string _nombreSp, params object[] parametros)
{
clsFunciones clsFunc = new clsFunciones();
SqlConnection conSQL = new SqlConnection(mdPrincipal.Cadena_Conexion_SQL);
SqlCommand cmdSQL = new SqlCommand(_nombreSp, conSQL);
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandTimeout = 600;
SqlParameterCollection paramColl = cmdSQL.Parameters;
try
{
foreach (object parametro in parametros)
{
//**** Creamos la matriz cogiendo el primer igual y el último
//**** Por si meten algún signo igual en el parámetro a insertar
int posprimerIgual = parametro.ToString().IndexOf("=");
string primerParametro = parametro.ToString().Substring(0, posprimerIgual);
int posultimoIgual = parametro.ToString().LastIndexOf("=");
string tercerParametro = parametro.ToString().Substring(posultimoIgual + 1, parametro.ToString().Length - (posultimoIgual + 1));
string segundoParametro = parametro.ToString().Substring(posprimerIgual + 1, posultimoIgual - (posprimerIgual + 1));
string[] matriz =
{
primerParametro,
segundoParametro,
tercerParametro
};
string parametro_sp = matriz[0];
SqlParameter valorEntrada = new SqlParameter();
valorEntrada.ParameterName = parametro_sp;
valorEntrada.Direction = ParameterDirection.Input;
valorEntrada.Value = matriz[1];
valorEntrada.SqlDbType = verSqlType(matriz[2]);
if (matriz[2].ToString().ToUpper() == "SYSTEM.DECIMAL")
{
valorEntrada.Value = clsFunc.ponerDecimales(matriz[1]);
if ((string)valorEntrada.Value == string.Empty)
{
valorEntrada.Value = 0;
}
}
if (matriz[2].ToString().ToUpper() == "SYSTEM.IMAGE")
{
if (valorEntrada.Value.ToString().ToUpper() == "NULL")
{
valorEntrada.Value = DBNull.Value;
}
if (valorEntrada.Value.ToString().ToUpper() == string.Empty)
{
valorEntrada.Value = DBNull.Value;
}
}
paramColl.Add(valorEntrada);
}
SqlParameter valorRetorno = new SqlParameter("@msgerror", SqlDbType.VarChar, 200);
valorRetorno.Direction = ParameterDirection.Output;
paramColl.Add(valorRetorno);
conSQL.Open();
cmdSQL.ExecuteNonQuery();
return(valorRetorno.Value.ToString());
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
conSQL.Close();
}
}
public override string GetInsertSQL(SqlParameterCollection parameters)
{
return(Data.DataObjectEvent.GetInsertSQL(this, parameters));
}
public static string GetInsertSQL(Business.DataObjectPollAnswer item, SqlParameterCollection parameters)
{
SetParameters(item, parameters);
return("INSERT INTO hiobj_PollAnswer ([OBJ_ID],[Position],[Answer],[Comment],[IP]) VALUES (@OBJ_ID,@Position,@Answer,@AnswerComment,@AnswerIP)");
}
public static string GetUpdateSQL(Business.DataObjectPollAnswer item, SqlParameterCollection parameters)
{
// SetParameters(item, parameters);
// return "UPDATE hiobj_PollAnswer SET [Answer] = @Answer, [Comment] = @AnswerComment";
return(string.Empty);
}
public static string GetSelectSQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
return(", hiobj_PollAnswer.*");
}
/// <summary>
///Use to a set of output parameters
/// </summary>
/// <param name="spName">The name of the stored procedure to execute.</param>
/// <param name="sqlParams">Optional parameters for the stored procedure.</param>
/// <returns>A SqlParameterCollection.</returns>
public static SqlParameterCollection ExecuteCommand(string spName, params SqlParameter[] sqlParams)
{
SqlParameterCollection ds = ExecuteCommand(Config.ConnectionString, spName, sqlParams);
return(ds);
}
public override string GetOrderBySQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
return(Data.DataObjectEvent.GetOrderBySQL(qParas, parameters));
}
public override string ToParametrizedString(SqlParameterCollection list)
{
return string.Format("({0} {1} {2})", Left.ToParametrizedString(list), Operator, Right.ToParametrizedString(list));
}
public static DataTable RetriveDownload(string[] key)
{
DataTable table = new DataTable();
string cstr = "Data Source=" + Var.SERVERIP + ";User ID=sa;Password="******";Initial Catalog=" + Var.DATABASE + ";Connection Timeout=1800;";
using (SqlConnection conn = new SqlConnection(cstr))
{
conn.Open();
string sqlStr = "rpt_TransDownload";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 1800;
SqlParameterCollection spc = cmd.Parameters;
if (key[0] != null)
{
spc.AddWithValue("@TType", key[0]);
}
if (key[1] != null)
{
spc.AddWithValue("@BCType", key[1]);
}
if (key[2] != null)
{
spc.AddWithValue("@BCNo", key[2]);
}
if (key[3] != null)
{
spc.AddWithValue("@No", key[3]);
}
if (key[4] != null)
{
spc.AddWithValue("@ProductId", key[4]);
}
if (key[2] != null)
{
spc.AddWithValue("@SupplierId", key[5]);
}
if (key[3] != null)
{
spc.AddWithValue("@CustomerId", key[6]);
}
if (key[4] != null)
{
spc.AddWithValue("@UserId", key[7]);
}
try { if (key[8] != null)
{
spc.AddWithValue("@Date", Convert.ToDateTime(key[8]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
try { if (key[9] != null)
{
spc.AddWithValue("@BCDate", Convert.ToDateTime(key[9]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
try { if (key[10] != null)
{
spc.AddWithValue("@DateFrom", Convert.ToDateTime(key[10]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
try { if (key[11] != null)
{
spc.AddWithValue("@DateTo", Convert.ToDateTime(key[11]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
cmd.Dispose();
}
conn.Close();
conn.Dispose();
}
return(table);
}
public static string GetJoinSQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
return("INNER JOIN hiobj_PollAnswer ON hiobj_PollAnswer.OBJ_ID = hitbl_DataObject_OBJ.OBJ_ID");
}
/// <summary>
/// Ejecuta Stored Procedure pasando el Nombre y los parametros con sus valores
/// </summary>
/// <param name="_nombreSp">Nombre del Stored Procedure de la Base de Datos</param>
/// <param name="parametros">Array de los parametros y valores siguiendo el patron "@parametro=valor=System.Tipo"</param>
/// <returns>Valor del retorno como un SqlCommand</returns>
/// <remarks></remarks>
public SqlCommand ejecutarSPSelect(string _nombreSp, params object[] parametros)
{
clsFunciones clsFunc = new clsFunciones();
SqlConnection conSQL = new SqlConnection(mdPrincipal.Cadena_Conexion_SQL);
SqlCommand cmdSQL = new SqlCommand(_nombreSp, conSQL);
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandTimeout = 600;
SqlParameterCollection paramColl = cmdSQL.Parameters;
try
{
foreach (object parametro in parametros)
{
object[] matriz = parametro.ToString().Split('=');
string parametro_sp = matriz[0].ToString();
SqlParameter valorEntrada = new SqlParameter();
valorEntrada.ParameterName = parametro_sp;
valorEntrada.Direction = ParameterDirection.Input;
valorEntrada.Value = matriz[1];
valorEntrada.SqlDbType = verSqlType(matriz[2].ToString());
if (matriz[2].ToString().ToUpper() == "SYSTEM.DECIMAL")
{
valorEntrada.Value = clsFunc.ponerDecimales(matriz[1].ToString());
if ((string)valorEntrada.Value == string.Empty)
{
valorEntrada.Value = 0;
}
}
if (matriz[2].ToString().ToUpper() == "SYSTEM.IMAGE")
{
if (valorEntrada.Value.ToString().ToUpper() == "NULL")
{
valorEntrada.Value = null;
}
}
paramColl.Add(valorEntrada);
}
SqlParameter valorRetorno = new SqlParameter("@msgerror", SqlDbType.VarChar, 200);
valorRetorno.Direction = ParameterDirection.Output;
paramColl.Add(valorRetorno);
conSQL.Open();
return(cmdSQL);
}
catch (SqlException ex)
{
return(new System.Data.SqlClient.SqlCommand(ex.Message));
}
finally
{
conSQL.Close();
}
}
public static string RetriveInfo(string[] user, string[] tabel, string[] key)
{
string r = "";
DataTable table = new DataTable();
string cstr = "Data Source=" + Var.SERVERIP + ";User ID=sa;Password="******";Initial Catalog=" + Var.DATABASE + ";Connection Timeout=1800;";
using (SqlConnection conn = new SqlConnection(cstr))
{
conn.Open();
string sqlStr = "Transaction_Info";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 1800;
SqlParameterCollection spc = cmd.Parameters;
spc.AddWithValue("@UserID", user[0]);
spc.AddWithValue("@DepartmentID", user[1]);
spc.AddWithValue("@Factory", user[2]);
spc.AddWithValue("@Table", tabel[1]);
spc.AddWithValue("@Type", tabel[2]);
spc.AddWithValue("@Field", tabel[3]);
if (key[0] != null)
{
spc.AddWithValue("@Key1", key[0]);
}
if (key[1] != null)
{
spc.AddWithValue("@Key2", key[1]);
}
if (key[2] != null)
{
spc.AddWithValue("@Key3", key[2]);
}
if (key[3] != null)
{
spc.AddWithValue("@Key4", key[3]);
}
if (key[4] != null)
{
spc.AddWithValue("@Key5", key[4]);
}
try { if (key[5] != null)
{
spc.AddWithValue("@Date1", Convert.ToDateTime(key[5]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
try { if (key[6] != null)
{
spc.AddWithValue("@Date2", Convert.ToDateTime(key[6]).ToString("yyyy/MM/dd"));
}
}
catch (Exception) { };
if (key[7] != null)
{
spc.AddWithValue("@Int1", Tools.StringDecimalIDToUS(key[7]));
}
if (key[8] != null)
{
spc.AddWithValue("@Int2", Tools.StringDecimalIDToUS(key[8]));
}
if (key[9] != null)
{
spc.AddWithValue("@Int3", Tools.StringDecimalIDToUS(key[9]));
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);
cmd.Dispose();
}
conn.Close();
conn.Dispose();
}
r = table.DefaultView[0][0].ToString();
return(r);
}
public static string GetFullTextWhereSQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
string retString = string.Empty;
return(retString);
}
public static string CheckData(string[] user, string[] type, string[] mkey, string[] dkey, string[] field, string[] data)
{
string r = "";
string cstr = "Data Source=" + Var.SERVERIP + ";User ID=sa;Password="******";Initial Catalog=" + Var.DATABASE + ";Connection Timeout=1800;";
using (SqlConnection conn = new SqlConnection(cstr))
{
conn.Open();
string sqlStr = "Transaction_Check";
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 1800;
SqlParameterCollection spc = cmd.Parameters;
spc.AddWithValue("@UserId", user[0]);
spc.AddWithValue("@DepartmentId", user[1]);
spc.AddWithValue("@Factory", user[2]);
spc.AddWithValue("@Table", type[1]);
spc.AddWithValue("@TableType", type[2]);
spc.AddWithValue("@ProcessType", type[3]);
spc.AddWithValue("@MasterKey", mkey[0]);
spc.AddWithValue("@MasterKey2", mkey[1]);
spc.AddWithValue("@DetailKey", dkey[0]);
spc.AddWithValue("@DetailKey2", dkey[1]);
spc.AddWithValue("@DetailKey3", dkey[2]);
int s = 0, e = 0;
e = field.Length;
while (e > s)
{
try
{
if (data[s] == null)
{
if (field[s].Substring(0, 2) == "N_")
{
data[s] = "0";
}
else
{
data[s] = null;
}
}
else
{
if (!(data[s].ToString() == " ") && (data[s].ToString().Trim() == "" || data[s].Trim() == "__/__/____")) // set null for empty spaces
{
data[s] = null;
}
else if (field[s] != null && field[s].Substring(0, 2) == "N_") // Numeric
{
spc.AddWithValue(field[s].Substring(2, field[s].Length - 2), Tools.StringDecimalIDToUS(data[s]));
}
else if (field[s] != null && field[s].Substring(0, 2) == "D_") // DateTime
{
spc.AddWithValue(field[s].Substring(2, field[s].Length - 2), Convert.ToDateTime(data[s]).ToString("yyyy/MM/dd"));
}
else if (field[s] != null)
{
string str;
str = data[s].Replace("'", "''");
str = Regex.Replace(str, " +( |$)", "$1");
spc.AddWithValue(field[s], str);
}
}
}
catch (Exception ex)
{
r = ex.Message.ToString();
if (r.IndexOf('\r') > 0)
{
r = r.Substring(0, r.IndexOf('\r'));
}
return(r);
}
s += 1;
}
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException sqle)
{
r = sqle.Message.ToString();
if (r.IndexOf('\r') > 0)
{
r = r.Substring(0, r.IndexOf('\r'));
}
}
}
conn.Close();
conn.Dispose();
}
return(r.Replace("'", ""));
}
public static string GetOrderBySQL(Business.QuickParameters qParas, SqlParameterCollection parameters)
{
return(string.Empty);
}
/// <summary>
/// Executes the stored procedure with the given name, passes the provided parameters and returns the result as a DataTable
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="outputParameters">The output parameters.</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string storedProcedureName, Dictionary <string, object> parameters, out SqlParameterCollection outputParameters)
{
return(ExecuteDataTable(storedProcedureName, parameters, null, out outputParameters));
}
public static SqlParameter FindByName(this SqlParameterCollection collection, string name)
{
return(collection.Cast <SqlParameter>().Single(p => p.ParameterName == name));
}
/// <summary>
/// Executes the stored procedure with the given name, passes the provided parameters and returns a SqlDataReader
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="outputParameters">The output parameters.</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string storedProcedureName, Dictionary <string, object> parameters, out SqlParameterCollection outputParameters)
{
return(ExecuteReader(storedProcedureName, parameters, null, out outputParameters));
}
public void ClearNotificationItem_Called_ObjectDisposed()
{
// Arrange
var spListItem = CreateShimSpListItem(_webAppGuid1);
SqlConnection createdConnection = null;
ShimSqlConnection.ConstructorString = (connection, _) =>
{
createdConnection = connection;
};
ShimSPSecurity.RunWithElevatedPrivilegesSPSecurityCodeToRunElevated = action =>
{
action();
};
SqlConnection openedConnection = null;
ShimSqlConnection.AllInstances.Open = connection =>
{
openedConnection = connection;
};
SqlCommand executedCommand = null;
string executedSpName = null;
CommandType? executedCommandType = null;
SqlConnection executedCommandConnection = null;
SqlParameterCollection executedParams = null;
ShimSqlCommand.AllInstances.ExecuteNonQuery = command =>
{
executedCommandConnection = command.Connection;
executedSpName = command.CommandText;
executedCommandType = command.CommandType;
executedParams = command.Parameters;
executedCommand = command;
return(0);
};
SqlConnection disposedConnection = null;
ShimSqlConnection.AllInstances.DisposeBoolean = (connection, disposing) =>
{
disposedConnection = connection;
};
SqlCommand disposedCommand = null;
ShimSqlCommand.AllInstances.DisposeBoolean = (command, disposing) =>
{
disposedCommand = command;
};
// Act
APIEmail.ClearNotificationItem(spListItem);
// Assert
Assert.AreSame(createdConnection, openedConnection);
Assert.IsNotNull(executedCommand);
Assert.AreEqual("spNDeleteNotification", executedSpName);
Assert.AreEqual(CommandType.StoredProcedure, executedCommandType);
Assert.AreEqual(2, executedParams.Count);
Assert.AreEqual("@listid", executedParams[0].ParameterName);
Assert.AreEqual("@itemid", executedParams[1].ParameterName);
Assert.AreSame(executedCommand, disposedCommand);
}
/// <summary>
/// Executes the stored procedure with the given name, passes the provided parameters and returns a SqlDataReader
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="sqlParameters">The SQL parameters.</param>
/// <param name="outputParameters">The output parameters.</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string storedProcedureName, Dictionary <string, object> parameters, IEnumerable <SqlParameter> sqlParameters, out SqlParameterCollection outputParameters)
{
SqlCommand sqlCommand = GenerateCommand(storedProcedureName, parameters);
try
{
return(sqlCommand.ExecuteReader());
}
finally
{
outputParameters = sqlCommand.Parameters;
}
}
protected override void PrepareParameters(SqlParameterCollection parameters)
{
Check.NotNull(parameters, "The base class guarantees that the parameter is not null.");
parameters.AddParameterUnsafe("@language", SqlDbType.Char, _culture.TwoLetterISOLanguageName);
}
public override string GetUpdateSQL(SqlParameterCollection parameters)
{
return(Data.DataObjectCommunity.GetUpdateSQL(this, parameters));
}
public override string GetSql(SqlParameterCollection parameters, int joinCount)
{
return(((QueryElMember <int>) this).GetSql(parameters, joinCount));
}
void BaseValues()
{
this.UseStandardPrinter = true;
this.GraphicsUnit = GraphicsUnit.Pixel;
this.Padding = new Padding(5);
this.DefaultFont = GlobalValues.DefaultFont;
this.ReportType = GlobalEnums.ReportType.FormSheet;
this.DataModel = GlobalEnums.PushPullModel.FormSheet;
this.CommandType = System.Data.CommandType.Text;
this.ConnectionString = String.Empty;
this.CommandText = String.Empty;
this.TopMargin = GlobalValues.DefaultPageMargin.Left;
this.BottomMargin = GlobalValues.DefaultPageMargin.Bottom;
this.LeftMargin = GlobalValues.DefaultPageMargin.Left;
this.RightMargin = GlobalValues.DefaultPageMargin.Right;
this.availableFields = new AvailableFieldsCollection();
this.groupingsCollection = new GroupColumnCollection();
this.sortingCollection = new SortColumnCollection();
this.sqlParameters = new SqlParameterCollection();
this.parameterCollection = new ParameterCollection();
this.NoDataMessage = "No Data for this Report";
}
protected override void PrepareParameters(SqlParameterCollection parameters)
{
// Intentionally left blank.
}
public ReportStructure()
{
SqlQueryParameters = new SqlParameterCollection();
}
private static bool IsSqlParameterCollectionsEqual(SqlParameterCollection expected, SqlParameterCollection actual)
{
if (expected == actual || (expected == null && actual == null))
{
return(true);
}
if ((expected != null && actual == null) ||
(expected == null && actual != null) ||
(expected.Count != actual.Count))
{
return(false);
}
return(expected.SequenceEqual(actual));
}
public virtual string ToParametrizedString(SqlParameterCollection list)
{
return ToString();
}
public override string GetSql(SqlParameterCollection parameters, int joinCount)
{
return(IsTrue ? "1=1" : "1=0");
}
SqlParameterCollection CreateSqlParameters(IProcedure procedure)
{
SqlParameterCollection col = new SqlParameterCollection();
SqlParameter par = null;
foreach (var element in procedure.Items) {
DbType dbType = TypeHelpers.DbTypeFromStringRepresenation(element.DataType);
par = new SqlParameter(element.Name,dbType,"",ParameterDirection.Input);
if (element.ParameterMode == ParameterMode.In) {
par.ParameterDirection = ParameterDirection.Input;
} else if (element.ParameterMode == ParameterMode. InOut){
par.ParameterDirection = ParameterDirection.InputOutput;
}
col.Add(par);
}
return col;
}
private static void BuildQueryParameters (IDbCommand cmd,
SqlParameterCollection parameterCollection)
{
if (parameterCollection != null && parameterCollection.Count > 0) {
IDbDataParameter cmdPar = null;
foreach (SqlParameter par in parameterCollection) {
cmdPar = cmd.CreateParameter();
cmdPar.ParameterName = par.ParameterName;
Console.WriteLine("");
Console.WriteLine("BuildQueryParameters {0} - {1}",par.ParameterName,par.ParameterValue);
if (par.DataType != System.Data.DbType.Binary) {
cmdPar.DbType = par.DataType;
cmdPar.Value = par.ParameterValue;
} else {
cmdPar.DbType = System.Data.DbType.Binary;
}
cmdPar.Direction = par.ParameterDirection;
cmd.Parameters.Add(cmdPar);
}
}
}