C# DB.ExecuteNonQuery方法代码示例

 public static int SExecuteNonQuery(string sql, params object[] parameter)
 {
     using (DB db = new DB())
     {
         return db.ExecuteNonQuery(sql, parameter);
     }
 }

		public static void Clean (DB db)
		{
			using (IDbTransaction transaction = db.BeginTransaction ()) {
				db.ExecuteNonQuery (@"
DELETE FROM Login;
DELETE FROM Person;
DELETE FROM WorkFile;
DELETE FROM Work;
DELETE FROM RevisionWork;
DELETE FROM Revision;
DELETE FROM File;
DELETE FROM Lanefiles;
DELETE FROM Lanefile;
DELETE FROM HostLane;
DELETE FROM Command;
DELETE FROM LaneDependency;
DELETE FROM LaneDeletionDirective;
DELETE FROM FileDeletionDirective;
DELETE FROM EnvironmentVariable;
DELETE FROM Lane;
DELETE FROM MasterHost;
DELETE FROM Host;

INSERT INTO Person (login, password, roles) VALUES ('scheduler', 'hithere', 'Administrator');
");
				transaction.Commit ();
			}
		}

		private static void CheckDependencies (DB db, List<DBHost> hosts, List<DBLane> lanes, List<DBHostLane> hostlanes)
		{
			DateTime start = DateTime.Now;
			StringBuilder sql = new StringBuilder ();
			List<DBLaneDependency> dependencies;

			try {
				dependencies = DBLaneDependency_Extensions.GetDependencies (db, null);

				log.InfoFormat ("CheckDependencies: Checking {0} dependencies", dependencies == null ? 0 : dependencies.Count);

				if (dependencies == null || dependencies.Count == 0)
					return;

				/* Check that there is only 1 dependency per lane and only DependentLaneSuccess condition */
				foreach (DBLaneDependency dep in dependencies) {
					if (dependencies.Any (dd => dep.id != dd.id && dep.lane_id == dd.lane_id)) {
						CheckDependenciesSlow (db, hosts, lanes, hostlanes, dependencies);
						return;
					}
					if (dep.Condition != DBLaneDependencyCondition.DependentLaneSuccess && dep.Condition != DBLaneDependencyCondition.DependentLaneIssuesOrSuccess) {
						CheckDependenciesSlow (db, hosts, lanes, hostlanes, dependencies);
						return;
					}
				}

				foreach (DBLaneDependency dependency in dependencies) {
					log.InfoFormat ("CheckDependencies: Checking dependency {0} for lane {1}", dependency.id, dependency.lane_id);
					/* Find the revision works which has filfilled dependencies */
					using (IDbCommand cmd = db.CreateCommand ()) {
						cmd.CommandText = @"
SELECT RevisionWork.id
FROM RevisionWork
INNER JOIN Lane ON Lane.id = RevisionWork.lane_id
INNER JOIN Host ON Host.id = RevisionWork.host_id
INNER JOIN Revision ON Revision.id = RevisionWork.revision_id
INNER JOIN LaneDependency ON LaneDependency.lane_id = RevisionWork.lane_id
WHERE
	RevisionWork.lane_id = @lane_id AND RevisionWork.state = 9 AND
	EXISTS (
		SELECT SubRevisionWork.id
		FROM RevisionWork SubRevisionWork
		INNER JOIN Revision SubRevision ON SubRevisionWork.revision_id = SubRevision.id
		WHERE SubRevisionWork.completed = true AND ";
			if (dependency.Condition == DBLaneDependencyCondition.DependentLaneSuccess) {
				cmd.CommandText += "SubRevisionWork.state = 3 ";
			} else if (dependency.Condition == DBLaneDependencyCondition.DependentLaneIssuesOrSuccess) {
				cmd.CommandText += "(SubRevisionWork.state = 3 OR SubRevisionWork.state = 8) ";
			}
			
			cmd.CommandText +=
			@"AND SubRevision.revision = Revision.revision 
			AND SubRevisionWork.lane_id = @dependent_lane_id";

						if (dependency.dependent_host_id.HasValue) {
							DB.CreateParameter (cmd, "dependent_host_id", dependency.dependent_host_id.Value);
							cmd.CommandText += @"
			AND SubRevisionWork.host_id = @dependent_host_id";
						}

						cmd.CommandText += @"
		);";
						DB.CreateParameter (cmd, "lane_id", dependency.lane_id);
						DB.CreateParameter (cmd, "dependent_lane_id", dependency.dependent_lane_id);

						sql.Length = 0;
						using (IDataReader reader = cmd.ExecuteReader ()) {
							while (reader.Read ()) {
								int rw_id = reader.GetInt32 (0);
								sql.AppendFormat ("UPDATE Work SET state = 0 WHERE revisionwork_id = {0} AND state = 9;\n", rw_id);
								sql.AppendFormat ("UPDATE RevisionWork SET state = 0 WHERE id = {0} AND state = 9;\n", rw_id);
							}
						}
						db.ExecuteNonQuery (sql.ToString ());
					}


				}
			} catch (Exception ex) {
				log.ErrorFormat ("CheckDependencies: There was an exception while checking dependencies db: {0}", ex);
			} finally {
				log.InfoFormat ("CheckDependencies: [Done in {0} seconds]", (DateTime.Now - start).TotalSeconds);
			}
		}

		private static void AddWork (DB db, List<DBHost> hosts, List<DBLane> lanes, List<DBHostLane> hostlanes)
		{
			DateTime start = DateTime.Now;
			List<DBCommand> commands = null;
			List<DBLaneDependency> dependencies = null;
			List<DBCommand> commands_in_lane;
			List<DBRevisionWork> revisionwork_without_work = new List<DBRevisionWork> ();
			DBHostLane hostlane;
			StringBuilder sql = new StringBuilder ();
			bool fetched_dependencies = false;
			int lines = 0;

			try {
				/* Find the revision works which don't have work yet */
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = "SELECT * FROM RevisionWork WHERE state = 10;";
					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ()) {
							revisionwork_without_work.Add (new DBRevisionWork (reader));
						}
					}
				}

				log.InfoFormat ("AddWork: Got {0} hosts and {1} revisionwork without work", hosts.Count, revisionwork_without_work.Count);

				foreach (DBLane lane in lanes) {
					commands_in_lane = null;

					foreach (DBHost host in hosts) {
						hostlane = null;
						for (int i = 0; i < hostlanes.Count; i++) {
							if (hostlanes [i].lane_id == lane.id && hostlanes [i].host_id == host.id) {
								hostlane = hostlanes [i];
								break;
							}
						}

						if (hostlane == null) {
							log.DebugFormat ("AddWork: Lane '{0}' is not configured for host '{1}', not adding any work.", lane.lane, host.host);
							continue;
						} else if (!hostlane.enabled) {
							log.DebugFormat ("AddWork: Lane '{0}' is disabled for host '{1}', not adding any work.", lane.lane, host.host);
							continue;
						}

						log.InfoFormat ("AddWork: Lane '{0}' is enabled for host '{1}', adding work!", lane.lane, host.host);

						foreach (DBRevisionWork revisionwork in revisionwork_without_work) {
							bool has_dependencies;

							/* revisionwork_without_work contains rw for all hosts/lanes, filter out the ones we want */
							if (revisionwork.host_id != host.id || revisionwork.lane_id != lane.id)
								continue;

							/* Get commands and dependencies for all lanes only if we know we'll need them */
							if (commands == null)
								commands = db.GetCommands (0);
							if (commands_in_lane == null) {
								commands_in_lane = new List<DBCommand> ();
								CollectWork (commands_in_lane, lanes, lane, commands);
							}

							if (!fetched_dependencies) {
								fetched_dependencies = true;
								dependencies = DBLaneDependency_Extensions.GetDependencies (db, null);
							}

							has_dependencies = dependencies != null && dependencies.Any (dep => dep.lane_id == lane.id);

							log.DebugFormat ("AddWork: Lane '{0}', revisionwork_id '{1}' has dependencies: {2}", lane.lane, revisionwork.id, has_dependencies);

							foreach (DBCommand command in commands_in_lane) {
								int work_state = (int) (has_dependencies ? DBState.DependencyNotFulfilled : DBState.NotDone);

								sql.AppendFormat ("INSERT INTO Work (command_id, revisionwork_id, state) VALUES ({0}, {1}, {2});\n", command.id, revisionwork.id, work_state);
								lines++;


								log.DebugFormat ("Lane '{0}', revisionwork_id '{1}' Added work for command '{2}'", lane.lane, revisionwork.id, command.command);

								if ((lines % 100) == 0) {
									db.ExecuteNonQuery (sql.ToString ());
									sql.Clear ();
									log.DebugFormat ("AddWork: flushed work queue, added {0} items now.", lines);
								}
							}

							sql.AppendFormat ("UPDATE RevisionWork SET state = {0} WHERE id = {1} AND state = 10;", (int) (has_dependencies ? DBState.DependencyNotFulfilled : DBState.NotDone), revisionwork.id);

						}
					}
				}
				if (sql.Length > 0)
					db.ExecuteNonQuery (sql.ToString ());
			} catch (Exception ex) {
				log.ErrorFormat ("AddWork: {0}", ex);
			}
			log.InfoFormat ("AddWork: [Done in {0} seconds]", (DateTime.Now - start).TotalSeconds);
		}