C# DB.ExecuteScalar方法代码示例

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

		public void AddCommand (WebServiceLogin login, int lane_id, string command, bool always_execute, bool nonfatal, int timeout, int sequence)
		{
			using (DB db = new DB ()) {
				VerifyUserInRole (db, login, Roles.Administrator);
				DBCommand cmd = new DBCommand ();
				cmd.arguments = "-ex {0}";
				cmd.filename = "bash";
				cmd.command = command;
				cmd.lane_id = lane_id;
				cmd.alwaysexecute = always_execute;
				cmd.nonfatal = nonfatal;
				cmd.timeout = 60;
				if (sequence < 0) {
					cmd.sequence = sequence;
				} else {
					cmd.sequence = 10 * (int) (long) (db.ExecuteScalar ("SELECT Count(*) FROM Command WHERE lane_id = " + lane_id.ToString ()));
				}
				cmd.Save (db);
			}
		}

		private static void UpdateBuildLogDB (DB db, DBLane lane, List<DBHost> hosts, List<DBHostLane> hostlanes)
		{
			List<DBRevision> revisions;
			List<DBCommand> commands = null;
			List<DBLaneDependency> dependencies = null;
			DBHostLane hostlane;
			DBWork work;
			bool got_dependencies = false;
			bool got_commands = false;

			try {
				Logger.Log ("Updating build db log... Got {0} hosts", hosts.Count);
				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) {
						Logger.Log ("Lane '{0}' is not configured for host '{1}', not adding any work.", lane.lane, host.host);
						continue;
					} else if (!hostlane.enabled) {
						Logger.Log ("Lane '{0}' is disabled for host '{1}', not adding any work.", lane.lane, host.host);
						continue;
					}

					AddRevisionWork (db, lane, host);

					revisions = db.GetDBRevisionsWithoutWork (lane.id, host.id);

					Logger.Log ("Updating build db log... Got {0} revisions for host {1}", revisions.Count, host.host);

					foreach (DBRevision revision in revisions) {
						bool dependencies_satisfied = true;

						if (!got_commands) {
							commands = db.GetCommands (lane.id);
							got_commands = true;
						}

						if (!got_dependencies) {
							dependencies = DBLaneDependency_Extensions.GetDependencies (db, lane);
							got_dependencies = true;
						}

						if (dependencies != null) {
							Logger.Log ("Lane '{0}', revision '{1}' checking dependencies...", lane.lane, revision.revision);

							foreach (DBLaneDependency dependency in dependencies)
								dependencies_satisfied &= dependency.IsSuccess (db, revision.revision);

							Logger.Log ("Lane '{0}', revision '{1}' dependency checking resulted in: {2}.", lane.lane, revision.revision, dependencies_satisfied);
						}

						int revisionwork_id;
						bool pending_dependencies;

						using (IDbCommand cmd = db.CreateCommand ()) {
							cmd.CommandText = "SELECT add_revisionwork (@lane_id, @host_id, @revision_id);";
							DB.CreateParameter (cmd, "lane_id", lane.id);
							DB.CreateParameter (cmd, "host_id", host.id);
							DB.CreateParameter (cmd, "revision_id", revision.id);
							revisionwork_id = (int) cmd.ExecuteScalar ();
						}

						using (IDbCommand cmd = db.CreateCommand ()) {
							cmd.CommandText = "SELECT state FROM RevisionWork WHERE id = @id;";
							DB.CreateParameter (cmd, "id", revisionwork_id);
							pending_dependencies = (int) DBState.DependencyNotFulfilled == (int) cmd.ExecuteScalar ();
						}

						if (pending_dependencies && !dependencies_satisfied)
							continue;

						Logger.Log ("Pending dependencies: {0}", pending_dependencies);

						foreach (DBCommand command in commands) {
							work = null;
							if (pending_dependencies) {
								using (IDbCommand cmd = db.CreateCommand ()) {
									cmd.CommandText = "SELECT * FROM Work WHERE revisionwork_id = @revisionwork_id AND command_id = @command_id;";
									DB.CreateParameter (cmd, "revisionwork_id", revisionwork_id);
									DB.CreateParameter (cmd, "command_id", command.id);
									using (IDataReader reader = cmd.ExecuteReader ()) {
										if (reader.Read ())
											work = new DBWork (reader);
									}
								}
							}

							if (work == null) {
								work = new DBWork ();
								work.command_id = command.id;
								work.revisionwork_id = revisionwork_id;
							}
							work.State = dependencies_satisfied ? DBState.NotDone : DBState.DependencyNotFulfilled;
							work.Save (db);
							Logger.Log ("Saved revision {0}, host {2}, command {1}", revision.revision, command.command, host.host);
//.........这里部分代码省略.........

		/// <summary>
		/// Returns true if something was added to the database.
		/// </summary>
		/// <param name="db"></param>
		/// <param name="lane"></param>
		/// <param name="host"></param>
		/// <returns></returns>
		public static bool AddRevisionWork (DB db, DBLane lane, DBHost host)
		{
			StringBuilder sql = new StringBuilder ();
			int line_count = 0;

			Logger.Log ("AddRevisionWork ({0} (id: {1}), {2} (id: {3}))", lane.lane, lane.id, host.host, host.id);

			try {
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
SELECT Lane.id AS lid, Revision.id AS rid, Host.id AS hid
FROM Host, Lane
INNER JOIN Revision ON Revision.lane_id = lane.id
WHERE 
	Lane.id = @lane_id AND 
	Host.id = @host_id AND
	NOT EXISTS (
		SELECT 1
		FROM RevisionWork 
		WHERE RevisionWork.lane_id = Lane.id AND RevisionWork.host_id = Host.id AND RevisionWork.revision_id = Revision.id
		);
";
					DB.CreateParameter (cmd, "lane_id", lane.id);
					DB.CreateParameter (cmd, "host_id", host.id);
					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ()) {
							int lane_id = reader.GetInt32 (reader.GetOrdinal ("lid"));
							int host_id = reader.GetInt32 (reader.GetOrdinal ("hid"));
							int revision_id = reader.GetInt32 (reader.GetOrdinal ("rid"));
							line_count++;
							sql.AppendFormat ("INSERT INTO RevisionWork (lane_id, host_id, revision_id) VALUES ({0}, {1}, {2});", lane_id, host_id, revision_id);
						}
					}
				}
				if (line_count > 0) {
					Logger.Log ("AddRevisionWork: Adding {0} records.", line_count);
					db.ExecuteScalar (sql.ToString ());
				} else {
					Logger.Log ("AddRevisionWork: Nothing to add.");
				}
				Logger.Log ("AddRevisionWork [Done]");
				return line_count > 0;
			} catch (Exception ex) {
				Logger.Log ("AddRevisionWork got an exception: {0}\n{1}", ex.Message, ex.StackTrace);
				return false;
			}
		}

        public static void Init()
        {
            Console.WindowWidth = Console.LargestWindowWidth - 25;
            Console.WindowHeight = Console.LargestWindowHeight - 25;
            Console.Title = "SkypeStats - Debug Console";

            Out.WritePlain("SkypeStats by JariZ");
            Out.WriteBlank();

            AddStats();

            Out.WriteLine("Searching for skype databases...");
            string[] Accounts = GetAccounts();

            TaskDialog chooser = new TaskDialog();
            chooser.InstructionText = "The following accounts were found on this computer";
            chooser.Text = "SkypeStats has found several accounts, which one do you want to analyse?";
            chooser.FooterCheckBoxText = "Load entire DB into memory and max out cache";

            string Account = "";
            foreach (string account in Accounts)
            {
                TaskDialogCommandLink link = new TaskDialogCommandLink(Path.GetFileName(account), Path.GetFileName(account));
                link.Click += delegate(object send, EventArgs args) {
                    Account = ((TaskDialogCommandLink)send).Name;
                    chooser.Close();
                };
                chooser.Controls.Add(link);
            }

            chooser.FooterText = "Loading everything into memory might be faster but requires a lot of RAM, only choose this if you know what you're doing";
            chooser.FooterIcon = TaskDialogStandardIcon.Information;

            chooser.Show();

            MaxedOutMode = chooser.FooterCheckBoxChecked;

            Core.Account = Account;
            DatabasePath = SkypeAppData + "\\" + Account + "\\main.db";
            if (!File.Exists(DatabasePath))
                Failure("Database file not found, restart SkypeStats");

            Out.WriteDebug("DB path is " + DatabasePath + " and exists");

            Out.WriteDebug("Connecting to DB...");
            try
            {
                Database = new DB(DatabasePath);
            }
            catch(Exception z)
            {
                Out.WriteDebug("UNABLE TO CONNECT TO DB:");
                Out.WriteError(z.ToString(), z);
                Failure("Unable to connect to database file: " + z.Message);
            }
            Out.WriteLine("Successfully connected to DB!");

            ProgressDialog progress = null;
            if (ProgDiag)
            {
                Out.WriteDebug("Init progressdialog...");
                progress = new ProgressDialog(IntPtr.Zero);
                progress.Title = "SkypeStats";
                progress.Line1 = "Initializing...";
                progress.Line2 = " ";
                progress.Line3 = " ";
                progress.ShowDialog();

                //wait for progdiag to show up
                Thread.Sleep(1000);
            }
            else
            {
                Thread splash = new Thread(SplashThread);
                splash.SetApartmentState(ApartmentState.STA);
                splash.Start();

                while (Splash == null) { }
            }

            Out.WriteDebug("Counting messages....");
            if (ProgDiag)
            {
                progress.Line1 = "Counting messages...";
                progress.Value = 1337;
            }
            else
            {
                Splash.Status = "Counting messages...";
                Splash.Value = 1337;
            }
            MessageCount = Convert.ToInt32(Database.ExecuteScalar("SELECT COUNT(*) FROM Messages"));
            Out.WriteLine(MessageCount + " messages found!");

            Out.WriteDebug("Counting conversations....");
            if (ProgDiag)
            {
                progress.Line1 = "Counting conversations...";
                progress.Value = 1337;
            }
//.........这里部分代码省略.........

		/// <summary>
		/// Returns true if something was added to the database.
		/// </summary>
		/// <param name="db"></param>
		/// <param name="lane"></param>
		/// <param name="host"></param>
		/// <returns></returns>
		public static bool AddRevisionWork (DB db)
		{
			DateTime start = DateTime.Now;
			StringBuilder sql = new StringBuilder ();
			int line_count = 0;

			try {
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
SELECT Lane.id AS lid, Revision.id AS rid, Host.id AS hid
FROM HostLane
INNER JOIN Host ON HostLane.host_id = Host.id
INNER JOIN Lane ON HostLane.lane_id = Lane.id
INNER JOIN Revision ON Revision.lane_id = lane.id
WHERE HostLane.enabled = true AND
	NOT EXISTS (
		SELECT 1
		FROM RevisionWork 
		WHERE RevisionWork.lane_id = Lane.id AND RevisionWork.host_id = Host.id AND RevisionWork.revision_id = Revision.id
		);
";
					using (IDataReader reader = cmd.ExecuteReader ()) {
						int lane_idx = reader.GetOrdinal ("lid");
						int host_idx = reader.GetOrdinal ("hid");
						int revision_idx = reader.GetOrdinal ("rid");
						while (reader.Read ()) {
							int lane_id = reader.GetInt32 (lane_idx);
							int host_id = reader.GetInt32 (host_idx);
							int revision_id = reader.GetInt32 (revision_idx);
							line_count++;
							sql.AppendFormat ("INSERT INTO RevisionWork (lane_id, host_id, revision_id, state) VALUES ({0}, {1}, {2}, 10);\n", lane_id, host_id, revision_id);
						}
					}
				}
				if (line_count > 0) {
					Logger.Log ("AddRevisionWork: Adding {0} records.", line_count);
					db.ExecuteScalar (sql.ToString ());
				} else {
					Logger.Log ("AddRevisionWork: Nothing to add.");
				}
				return line_count > 0;
			} catch (Exception ex) {
				Logger.Log ("AddRevisionWork got an exception: {0}\n{1}", ex.Message, ex.StackTrace);
				return false;
			} finally {
				Logger.Log ("AddRevisionWork [Done in {0} seconds]", (DateTime.Now - start).TotalSeconds);
			}
		}