/////////////////////////////////////////////////////////////////////// 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 apply_post_insert_rules(int bugid) { var sql = new SQLString(Util.get_setting("UpdateBugAfterInsertBugAspxSql", "")); if (!string.IsNullOrEmpty(sql.ToString())) { sql = sql.AddParameterWithValue("@BUGID", Convert.ToString(bugid)); btnet.DbUtil.execute_nonquery(sql); } }
/////////////////////////////////////////////////////////////////////// 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()); } }
/////////////////////////////////////////////////////////////////////// protected void Page_Load(Object sender, EventArgs e) { Util.do_not_cache(Response); if (User.IsInRole(BtnetRoles.Admin) || User.Identity.GetCanMassEditBugs()) { // } else { Response.Write("You are not allowed to use this page."); Response.End(); } string list = ""; if (!IsPostBack) { Master.Menu.SelectedItem = "admin"; Page.Header.Title = Util.get_setting("AppTitle", "BugTracker.NET") + " - " + "massedit"; if (Request["mass_delete"] != null) { update_or_delete.Value = "delete"; } else { update_or_delete.Value = "update"; } // create list of bugs affected foreach (string var in Request.QueryString) { if (Util.is_int(var)) { if (list != "") { list += ","; } list += var; } ; } bug_list.Value = list; if (update_or_delete.Value == "delete") { update_or_delete.Value = "delete"; sql = new SQLString("delete bug_post_attachments from bug_post_attachments inner join bug_posts on bug_post_attachments.bpa_post = bug_posts.bp_id where bug_posts.bp_bug in (" + list + ")"); sql.Append("\ndelete from bug_posts where bp_bug in (" + list + ")"); sql.Append("\ndelete from bug_subscriptions where bs_bug in (" + list + ")"); sql.Append("\ndelete from bug_relationships where re_bug1 in (" + list + ")"); sql.Append("\ndelete from bug_relationships where re_bug2 in (" + list + ")"); sql.Append("\ndelete from bug_user where bu_bug in (" + list + ")"); sql.Append("\ndelete from bug_tasks where tsk_bug in (" + list + ")"); sql.Append("\ndelete from bugs where bg_id in (" + list + ")"); confirm_href.InnerText = "Confirm Delete"; } else { update_or_delete.Value = "update"; sql = new SQLString("update bugs \nset "); string updates = ""; string val; val = Request["mass_project"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_project = " + val; } val = Request["mass_org"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_org = " + val; } val = Request["mass_category"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_category = " + val; } val = Request["mass_priority"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_priority = " + val; } val = Request["mass_assigned_to"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_assigned_to_user = "******"mass_reported_by"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_reported_user = "******"mass_status"]; if (val != "-1" && Util.is_int(val)) { if (updates != "") { updates += ",\n"; } updates += "bg_status = " + val; } sql.Append(updates + "\nwhere bg_id in (" + list + ")"); confirm_href.InnerText = "Confirm Update"; } sql_text.InnerText = sql.ToString(); } else // postback { list = bug_list.Value; if (update_or_delete.Value == "delete") { string upload_folder = Util.get_upload_folder(); if (upload_folder != null) { // double check the bug_list string[] ints = bug_list.Value.Split(','); for (int i = 0; i < ints.Length; i++) { if (!btnet.Util.is_int(ints[i])) { Response.End(); } } var sql2 = new SQLString(@"select bp_bug, bp_id, bp_file from bug_posts where bp_type = 'file' and bp_bug in (" + bug_list.Value + ")"); DataSet ds = btnet.DbUtil.get_dataset(sql2); foreach (DataRow dr in ds.Tables[0].Rows) { // create path StringBuilder path = new StringBuilder(upload_folder); path.Append("\\"); path.Append(Convert.ToString(dr["bp_bug"])); path.Append("_"); path.Append(Convert.ToString(dr["bp_id"])); path.Append("_"); path.Append(Convert.ToString(dr["bp_file"])); if (System.IO.File.Exists(path.ToString())) { System.IO.File.Delete(path.ToString()); } } } } btnet.DbUtil.execute_nonquery(new SQLString(sql_text.InnerText)); Response.Redirect("search.aspx"); } }
/////////////////////////////////////////////////////////////////////// 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())); } }