///////////////////////////////////////////////////////////////////////
public static DataSet get_dataset(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
DataSet ds = new DataSet();
using (SqlConnection conn = GetConnection())
{
using (SqlDataAdapter da = new SqlDataAdapter( sql.ToString(), conn))
{
da.SelectCommand.Parameters.AddRange(sql.GetParameters().ToArray());
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
da.Fill(ds);
stopwatch.Stop();
log_stopwatch_time(stopwatch);
conn.Close(); // redundant, but just to be clear
return ds;
}
}
}
public SQLString Append(SQLString toAppend)
{
_value += toAppend.ToString();
foreach (var param in toAppend.GetParameters())
_parameters.Add(param);
return this;
}
public SQLString Append(SQLString toAppend)
{
_value += toAppend.ToString();
foreach (var param in toAppend.GetParameters())
{
_parameters.Add(param);
}
return(this);
}
///////////////////////////////////////////////////////////////////////
public static void execute_nonquery_without_logging(SQLString sql)
{
using (SqlConnection conn = GetConnection())
{
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
cmd.ExecuteNonQuery();
conn.Close(); // redundant, but just to be clear
}
}
///////////////////////////////////////////////////////////////////////
public static void execute_nonquery(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
using (SqlConnection conn = GetConnection())
{
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
cmd.ExecuteNonQuery();
conn.Close(); // redundant, but just to be clear
}
}
///////////////////////////////////////////////////////////////////////
public static object execute_scalar(SQLString sql)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
using (SqlConnection conn = GetConnection())
{
object returnValue;
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
returnValue = cmd.ExecuteScalar();
conn.Close(); // redundant, but just to be clear
return returnValue;
}
}
///////////////////////////////////////////////////////////////////////
public static SqlDataReader execute_reader(SQLString sql, CommandBehavior behavior)
{
if (Util.get_setting("LogSqlEnabled", "1") == "1")
{
Util.write_to_log("sql=\n" + sql);
}
SqlConnection conn = GetConnection();
try
{
using (SqlCommand cmd = new SqlCommand(sql.ToString(), conn))
{
cmd.Parameters.AddRange(sql.GetParameters().ToArray());
return cmd.ExecuteReader(behavior | CommandBehavior.CloseConnection);
}
}
catch
{
conn.Close();
throw;
}
}
///////////////////////////////////////////////////////////////////////
public static SQLString alter_sql_per_project_permissions(SQLString sql, IIdentity identity)
{
int userId = identity.GetUserId();
int organizationId = identity.GetOrganizationId();
string project_permissions_sql;
string dpl = Util.get_setting("DefaultPermissionLevel","2");
if (dpl == "0")
{
project_permissions_sql = @" (bugs.bg_project in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level > 0)) ";
}
else
{
project_permissions_sql = @" (bugs.bg_project not in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level = 0)) ";
}
if (identity.GetCanOnlySeeOwnReportedBugs())
{
project_permissions_sql += @"
and bugs.bg_reported_user = $user ";
}
else
{
if (identity.GetOtherOrgsPermissionLevels() == 0)
{
project_permissions_sql += @"
and bugs.bg_org = $user.org ";
}
}
project_permissions_sql
= project_permissions_sql.Replace("$user.org",Convert.ToString(organizationId));
project_permissions_sql
= project_permissions_sql.Replace("$user",Convert.ToString(userId));
// Figure out where to alter sql for project permissions
// I've tried lots of different schemes over the years....
int alter_here_pos = sql.ToString().IndexOf("$ALTER_HERE"); // places - can be multiple - are explicitly marked
if (alter_here_pos != -1)
{
return new SQLString(sql.ToString().Replace("$ALTER_HERE", "/* ALTER_HERE */ " + project_permissions_sql), sql.GetParameters());
}
else
{
string bug_sql;
var rawSQL = sql.ToString();
int where_pos = rawSQL.IndexOf("WhErE"); // first look for a "special" where, case sensitive, in case there are multiple where's to choose from
if (where_pos == -1)
where_pos = rawSQL.ToUpper().IndexOf("WHERE");
int order_pos = rawSQL.IndexOf("/*ENDWHR*/"); // marker for end of the where statement
if (order_pos == -1)
order_pos = rawSQL.ToUpper().LastIndexOf("ORDER BY");
if (order_pos < where_pos)
order_pos = -1; // ignore an order by that occurs in a subquery, for example
if (where_pos != -1 && order_pos != -1)
{
// both WHERE and ORDER BY clauses
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - both */ ( "
+ rawSQL.Substring(where_pos + 5, order_pos - (where_pos + 5))
+ " ) AND ( "
+ project_permissions_sql
+ " ) "
+ rawSQL.Substring(order_pos);
}
else if (order_pos == -1 && where_pos == -1)
{
// Neither
bug_sql = rawSQL + " /* altered - neither */ WHERE " + project_permissions_sql;
}
else if (order_pos == -1)
{
// WHERE, without order
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - just where */ ( "
+ rawSQL.Substring(where_pos + 5)
+ " ) AND ( "
+ project_permissions_sql + " )";
}
else
{
// ORDER BY, without WHERE
bug_sql = rawSQL.Substring(0, order_pos)
+ " /* altered - just order by */ WHERE "
+ project_permissions_sql
+ rawSQL.Substring(order_pos);
}
return new SQLString(bug_sql, sql.GetParameters());
}
}
///////////////////////////////////////////////////////////////////////
public static SQLString alter_sql_per_project_permissions(SQLString sql, IIdentity identity)
{
int userId = identity.GetUserId();
int organizationId = identity.GetOrganizationId();
string project_permissions_sql;
string dpl = Util.get_setting("DefaultPermissionLevel", "2");
if (dpl == "0")
{
project_permissions_sql = @" (bugs.bg_project in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level > 0)) " ;
}
else
{
project_permissions_sql = @" (bugs.bg_project not in (
select pu_project
from project_user_xref
where pu_user = $user
and pu_permission_level = 0)) " ;
}
if (identity.GetCanOnlySeeOwnReportedBugs())
{
project_permissions_sql += @"
and bugs.bg_reported_user = $user " ;
}
else
{
if (identity.GetOtherOrgsPermissionLevels() == 0)
{
project_permissions_sql += @"
and bugs.bg_org = $user.org " ;
}
}
project_permissions_sql
= project_permissions_sql.Replace("$user.org", Convert.ToString(organizationId));
project_permissions_sql
= project_permissions_sql.Replace("$user", Convert.ToString(userId));
// Figure out where to alter sql for project permissions
// I've tried lots of different schemes over the years....
int alter_here_pos = sql.ToString().IndexOf("$ALTER_HERE"); // places - can be multiple - are explicitly marked
if (alter_here_pos != -1)
{
return(new SQLString(sql.ToString().Replace("$ALTER_HERE", "/* ALTER_HERE */ " + project_permissions_sql), sql.GetParameters()));
}
else
{
string bug_sql;
var rawSQL = sql.ToString();
int where_pos = rawSQL.IndexOf("WhErE"); // first look for a "special" where, case sensitive, in case there are multiple where's to choose from
if (where_pos == -1)
{
where_pos = rawSQL.ToUpper().IndexOf("WHERE");
}
int order_pos = rawSQL.IndexOf("/*ENDWHR*/"); // marker for end of the where statement
if (order_pos == -1)
{
order_pos = rawSQL.ToUpper().LastIndexOf("ORDER BY");
}
if (order_pos < where_pos)
{
order_pos = -1; // ignore an order by that occurs in a subquery, for example
}
if (where_pos != -1 && order_pos != -1)
{
// both WHERE and ORDER BY clauses
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - both */ ( "
+ rawSQL.Substring(where_pos + 5, order_pos - (where_pos + 5))
+ " ) AND ( "
+ project_permissions_sql
+ " ) "
+ rawSQL.Substring(order_pos);
}
else if (order_pos == -1 && where_pos == -1)
{
// Neither
bug_sql = rawSQL + " /* altered - neither */ WHERE " + project_permissions_sql;
}
else if (order_pos == -1)
{
// WHERE, without order
bug_sql = rawSQL.Substring(0, where_pos + 5)
+ " /* altered - just where */ ( "
+ rawSQL.Substring(where_pos + 5)
+ " ) AND ( "
+ project_permissions_sql + " )";
}
else
{
// ORDER BY, without WHERE
bug_sql = rawSQL.Substring(0, order_pos)
+ " /* altered - just order by */ WHERE "
+ project_permissions_sql
+ rawSQL.Substring(order_pos);
}
return(new SQLString(bug_sql, sql.GetParameters()));
}
}
