/// <summary>
/// Global execute query.
/// </summary>
/// <param name="passKey">The pass key.</param>
/// <param name="username">The username.</param>
/// <param name="procName">Name of the proc.</param>
/// <param name="paramList">The parameter list.</param>
/// <returns>DataSet.</returns>
public DataSet ws_GlobalExecuteQuery(string passKey, string username, string procName, SPParam[] paramList)
{
if (!ValidationAndEncryptDecrypt.ValidateKey(passKey))
{
return(null);
}
DataSet dsCheck = SpParamHelpers.CheckSpParams(procName, "", paramList);
if (dsCheck != null)
{
return(dsCheck);
}
SqlConnection dbConn = null;
SqlCommand sqlCmd = null;
SqlDataAdapter sqlAdapter = null;
DataSet ds = null;
try
{
dbConn = GetOpenSqlConnection("");
sqlCmd = new SqlCommand(procName, dbConn)
{
CommandType = CommandType.StoredProcedure, CommandTimeout = 0
};
foreach (SPParam p in paramList)
{
SqlParameter param = sqlCmd.Parameters.AddWithValue(p.Name, p.Value);
if (p.Value == null)
{
param.IsNullable = true;
param.Value = DBNull.Value;
}
}
ds = new DataSet();
sqlAdapter = new SqlDataAdapter {
SelectCommand = sqlCmd
};
sqlAdapter.Fill(ds);
return(ds);
//}
}
catch (Exception e)
{
WriteEventLogEntry(e, procName, paramList);
return(HandleExceptionHelper.HandleSqlException(e, procName, paramList));
}
finally
{
ds?.Dispose();
sqlAdapter?.Dispose();
sqlCmd?.Dispose();
CloseConnection(dbConn);
}
}
/// <summary>
/// Execute non query.
/// </summary>
/// <param name="passKey">The pass key.</param>
/// <param name="dbName">Name of the database.</param>
/// <param name="username">The username.</param>
/// <param name="procName">Name of the proc.</param>
/// <param name="paramList">The parameter list.</param>
/// <returns>System.Int32.</returns>
public int ws_ExecuteNonQuery(string passKey, string dbName, string username, string procName, SPParam[] paramList)
{
if (!ValidationAndEncryptDecrypt.ValidateKey(passKey))
{
return(0);
}
if (SpParamHelpers.CheckSpParams(procName, dbName, paramList) != null)
{
return(-10);
}
SqlConnection dbConn = null;
SqlCommand sqlCmd = null;
try
{
dbConn = GetOpenSqlConnection(dbName);
sqlCmd = new SqlCommand(procName, dbConn)
{
CommandType = CommandType.StoredProcedure
};
foreach (SPParam p in paramList)
{
SqlParameter param = sqlCmd.Parameters.AddWithValue(p.Name, p.Value);
if (p.Value == null)
{
param.IsNullable = true;
param.Value = DBNull.Value;
}
}
return(sqlCmd.ExecuteNonQuery());
}
catch (Exception e)
{
WriteEventLogEntry(e, procName, paramList);
return(-11);
}
finally
{
sqlCmd?.Dispose();
CloseConnection(dbConn);
}
}