/////////////////////////////////////////////////////////////////////// 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())); } }