C# DB.BeginTransaction方法代码示例

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

		/// <summary>
		/// 
		/// </summary>
		/// <param name="db"></param>
		/// <param name="lane"></param>
		/// <param name="host"></param>
		/// <param name="limit"></param>
		/// <param name="page">First page = 0, second page = 1, etc.</param>
		/// <returns></returns>
		public static List<DBRevisionWorkView> Query (DB db, DBLane lane, DBHost host, int limit, int page)
		{
			Console.WriteLine ("Query {0} {1} {2} {3}", lane, host, limit, page);
			List<DBRevisionWorkView> result = new List<DBRevisionWorkView> ();
			using (IDbTransaction transaction = db.BeginTransaction ()) {
				using (IDbCommand cmd = db.CreateCommand ()) {
					// copy&paste from CustomTypes.sql
					cmd.CommandText = @"
SELECT RevisionWork.id, Revision.revision 
	INTO TEMP revisionwork_temptable
	FROM RevisionWork 
	INNER JOIN Revision on RevisionWork.revision_id = Revision.id 
	WHERE RevisionWork.lane_id = @lane_id AND RevisionWork.host_id = @host_id
	ORDER BY Revision.date DESC LIMIT @limit OFFSET @offset;

-- For some reason postgresql thinks the temp table has 1230 entries, while it usually has about 20 entries,
-- and decides to do a sequential scan on the work tabe. Analyze it to make it realize its untrue presumptions.
ANALYZE revisionwork_temptable; 

	SELECT 
		Work.id, Work.command_id, Work.state, Work.starttime, Work.endtime, Work.duration, Work.logfile, Work.summary, 
		Host.host, 
		Lane.lane, 
		Revision.author, Revision.revision, 
		Command.command, 
		Command.nonfatal, Command.alwaysexecute, Command.sequence, Command.internal,
		RevisionWork.lane_id, RevisionWork.host_id, RevisionWork.revision_id, 
		RevisionWork.state AS revisionwork_state,
		WorkHost.host AS workhost
	FROM Work
	INNER JOIN RevisionWork ON Work.revisionwork_id = RevisionWork.id
	INNER JOIN Revision ON RevisionWork.revision_id = Revision.id 
	INNER JOIN Lane ON RevisionWork.lane_id = Lane.id 
	INNER JOIN Host ON RevisionWork.host_id = Host.id 
	LEFT JOIN Host AS WorkHost ON RevisionWork.workhost_id = WorkHost.id
	INNER JOIN Command ON Work.command_id = Command.id
	WHERE
		Work.revisionwork_id IN (SELECT id FROM revisionwork_temptable)
	ORDER BY Revision.date DESC; 
";

					DB.CreateParameter (cmd, "lane_id", lane.id);
					DB.CreateParameter (cmd, "host_id", host.id);
					DB.CreateParameter (cmd, "limit", limit);
					DB.CreateParameter (cmd, "offset", page * limit);

					using (IDataReader reader = cmd.ExecuteReader ()) {
						while (reader.Read ()) {
							result.Add (new DBRevisionWorkView (reader));
						}
					}
				}

				return result;
			}
		}

		public static void Delete (DB db, int lane_id)
		{
			using (IDbTransaction transaction = db.BeginTransaction ()) {
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
DELETE FROM RevisionWork WHERE lane_id = @id;
DELETE FROM Revision WHERE lane_id = @id;
DELETE FROM LaneFiles WHERE lane_id = @id;
DELETE FROM Command WHERE lane_id = @id;
DELETE FROM HostLane WHERE lane_id = @id;
DELETE FROM EnvironmentVariable WHERE lane_id = @id;
DELETE FROM LaneDeletionDirective WHERE lane_id = @id;
DELETE FROM Lane WHERE id = @id;
";
					DB.CreateParameter (cmd, "id", lane_id);
					cmd.ExecuteNonQuery ();
				}
				transaction.Commit ();
			}
		}

		public static void Delete (DB db, int host_id)
		{
			using (IDbTransaction transaction = db.BeginTransaction ()) {
				using (IDbCommand cmd = db.CreateCommand ()) {
					cmd.CommandText = @"
DELETE FROM RevisionWork WHERE host_id = @id;
DELETE FROM RevisionWork WHERE workhost_id = @id;
DELETE FROM HostLane WHERE host_id = @id;
DELETE FROM EnvironmentVariable WHERE host_id = @id;
DELETE FROM LaneDependency WHERE dependent_host_id = @id;
DELETE FROM MasterHost WHERE host_id = @id;
DELETE FROM MasterHost WHERE master_host_id = @id;
DELETE FROM Host WHERE id = @id;
";
					DB.CreateParameter (cmd, "id", host_id);
					cmd.ExecuteNonQuery ();
				}
				transaction.Commit ();
			}
		}

		public static int CompressFiles ()
		{
			byte [] buffer = new byte [1024];
			int read;
			long saved_space = 0;

			using (DB db = new DB (true)) {
				using (DB db_save = new DB (true)) {
					using (IDbCommand cmd = db.CreateCommand ()) {
						cmd.CommandText = @"
SELECT File.*
FROM File
WHERE (File.compressed_mime = '' OR File.compressed_mime IS NULL) 
	AND File.size <> 0 
	AND File.id IN 
		(SELECT WorkFile.file_id FROM WorkFile WHERE WorkFile.file_id = File.id)
LIMIT 10 
";
						using (IDataReader reader = cmd.ExecuteReader ()) {
							while (reader.Read ()) {
								DBFile file = new DBFile (reader);
								long srclength;
								long destlength = -1;
								string tmpfile = Path.GetTempFileName ();
								string tmpfilegz;

								Console.Write ("Downloading {0} = {1} with size {2}... ", file.id, file.filename, file.size);

								using (Stream stream_reader = db_save.Download (file)) {
									using (FileStream stream_writer = new FileStream (tmpfile, FileMode.Create, FileAccess.Write)) {
										while (0 < (read = stream_reader.Read (buffer, 0, buffer.Length))) {
											stream_writer.Write (buffer, 0, read);
										}
									}
								}

								srclength = new FileInfo (tmpfile).Length;
								Console.Write ("Compressing file {0} with size {1}... ", tmpfile, srclength);

								tmpfilegz = FileUtilities.GZCompress (tmpfile);

								if (tmpfilegz == null) {
									Console.WriteLine ("Compression didn't succeed.");
								} else {
									destlength = new FileInfo (tmpfilegz).Length;
									Console.WriteLine ("Success, compressed size: {0} ({1}%)", destlength, 100 * (double) destlength / (double) srclength);

									using (IDbTransaction transaction = db_save.BeginTransaction ()) {
										// Upload the compressed file. 
										// Npgsql doesn't seem to have a way to truncate a large object,
										// so we just create a new large object and delete the old one.
										int file_id = file.file_id.Value;
										int gzfile_id = db_save.Manager.Create (LargeObjectManager.READWRITE);
										LargeObject gzfile = db_save.Manager.Open (gzfile_id, LargeObjectManager.READWRITE);

										using (FileStream st = new FileStream (tmpfilegz, FileMode.Open, FileAccess.Read, FileShare.Read)) {
											while (0 < (read = st.Read (buffer, 0, buffer.Length)))
												gzfile.Write (buffer, 0, read);
										}
										gzfile.Close ();

										// Save to our File record
										file.file_id = gzfile_id;
										file.compressed_mime = "application/x-gzip";
										file.Save (db_save);

										// Delete the old large object
										db_save.Manager.Delete (file_id);

										transaction.Commit ();

										saved_space += (srclength - destlength);
									}
								}

								if (File.Exists (tmpfilegz)) {
									try {
										File.Delete (tmpfilegz);
									} catch {
										// Ignore
									}
								}
								if (File.Exists (tmpfile)) {
									try {
										File.Delete (tmpfile);
									} catch {
										// Ignore
									}
								}
							}
						}
						//}
					}
				}
			}

			Console.WriteLine ("Saved {0} bytes.", saved_space);

			return 0;
		}

		public static int MoveFilesToFileSystem ()
		{
			long moved_bytes = 0;

			Manager.log.Info ("MoveFilesToFileSystem: [START]");

			using (DB db = new DB ()) {
				using (DB download_db = new DB ()) {
					while (true) {
						using (IDbCommand cmd = db.CreateCommand ()) {
							// execute this in chunks to avoid huge data transfers and slowdowns.
							cmd.CommandText = "SELECT * FROM File WHERE NOT file_id IS NULL LIMIT 100";
							using (IDataReader reader = cmd.ExecuteReader ()) {
								if (!reader.Read ())
									break;

								do {
									DBFile file = new DBFile (reader);
									byte [] buffer = new byte [1024];
									int oid = file.file_id.Value;
									int read;
									string fn = FileUtilities.CreateFilename (file.md5, file.compressed_mime == MimeTypes.GZ, true);
									using (FileStream writer = new FileStream (fn, FileMode.Create, FileAccess.Write, FileShare.Read)) {
										using (Stream str = download_db.Download (file)) {
											while ((read = str.Read (buffer, 0, buffer.Length)) != 0)
												writer.Write (buffer, 0, read);
										}
									}

									IDbTransaction transaction = download_db.BeginTransaction ();
									download_db.Manager.Delete (oid);
									file.file_id = null;
									file.Save (download_db);
									transaction.Commit ();

									moved_bytes += file.size;
									log.InfoFormat ("MoveFilesToFileSystem: Moved oid {0} to {1} ({2} bytes, {3} total bytes moved)", oid, fn, file.size, moved_bytes);
								} while (reader.Read ());
							}
						}
					}

					while (true) {
						using (IDbCommand cmd = db.CreateCommand ()) {
							// execute this in chunks to avoid huge data transfers and slowdowns.
							cmd.CommandText = "SELECT * FROM Revision WHERE (diff_file_id IS NULL AND NOT diff = '') OR (log_file_id IS NULL AND NOT log = '') LIMIT 100";
							using (IDataReader reader = cmd.ExecuteReader ()) {
								if (!reader.Read ())
									break;

								do {
									DBRevision revision = new DBRevision (reader);
									string tmpfile = null;

									if (!string.IsNullOrEmpty (revision.diff)) {
										int length = 0;
										if (revision.diff_file_id == null) {
											try {
												length = revision.diff.Length;
												tmpfile = Path.GetTempFileName ();
												File.WriteAllText (tmpfile, revision.diff);
												DBFile diff = download_db.Upload (tmpfile, ".log", false, null);
												revision.diff_file_id = diff.id;
												revision.diff = null;
											} finally {
												try {
													if (File.Exists (tmpfile))
														File.Delete (tmpfile);
												} catch (Exception ex) {
													log.ErrorFormat ("error deleting temp file: {0}", ex);
												}
											}
											moved_bytes += length;
											log.InfoFormat ("MoveFilesToFileSystem: Moved revision {0}'s diff to db/filesystem ({1} bytes, {2} total bytes moved)", revision.id, length, moved_bytes);
										}
									}

									if (!string.IsNullOrEmpty (revision.log)) {
										int length = 0;
										if (revision.log_file_id == null) {
											try {
												length = revision.log.Length;
												tmpfile = Path.GetTempFileName ();
												File.WriteAllText (tmpfile, revision.log);
												DBFile log = download_db.Upload (tmpfile, ".log", false, null);
												revision.log_file_id = log.id;
												revision.log = null;
											} finally {
												try {
													if (File.Exists (tmpfile))
														File.Delete (tmpfile);
												} catch (Exception ex) {
													log.ErrorFormat ("error deleting temp file: {0}", ex);
												}
											}
											moved_bytes += length;
											Manager.log.InfoFormat ("MoveFilesToFileSystem: Moved revision {0}'s log to db/filesystem ({1} bytes, {2} total bytes moved)", revision.id, length, moved_bytes);
										}
										revision.log = null;
									}
//.........这里部分代码省略.........

		public ReportBuildStateResponse ReportBuildState (WebServiceLogin login, DBWork work)
		{
			ReportBuildStateResponse response = new ReportBuildStateResponse ();

			using (DB db = new DB ())
			using (var transaction = db.BeginTransaction ()) {
				VerifyUserInRole (db, login, Roles.BuildBot, true);
				log.DebugFormat ("ReportBuildState, state: {0}, start time: {1}, end time: {2}", work.State, work.starttime, work.endtime);
				if (work.starttime > new DateTime (2000, 1, 1) && work.endtime < work.starttime) {
					// the issue here is that the server interprets the datetime as local time, while it's always as utc.
					try {
						using (IDbCommand cmd = db.CreateCommand ()) {
							cmd.CommandText = "SELECT starttime FROM Work WHERE id = " + work.id;
							var value = cmd.ExecuteScalar ();
							if (value is DateTime)
								work.starttime = (DateTime) value;
						}
					} catch (Exception ex) {
						log.ErrorFormat ("ReportBuildState: Exception while fixing timezone data: {0}", ex);
					}
				}
				work.Save (db);
				work.Reload (db);

				response.Work = work;

				DBRevisionWork rw = DBRevisionWork_Extensions.Create (db, work.revisionwork_id);
				bool was_completed = rw.completed;
				rw.UpdateState (db);

				if (rw.startedtime == null) {
					rw.startedtime = work.starttime;
					rw.Save (db);
				}

				if (!was_completed && rw.completed) {
					rw.endtime = DBRecord.DatabaseNow;
					rw.Save (db);
				}

				Notifications.Notify (work, rw);

				if (!was_completed && rw.completed) {
					var notifyInfo = new GenericNotificationInfo ();
					notifyInfo.laneID = rw.lane_id;
					notifyInfo.hostID = rw.host_id;
					notifyInfo.revisionID = rw.revision_id;
					notifyInfo.message = "Completed";
					notifyInfo.state = rw.State;

					Notifications.NotifyGeneric (notifyInfo);
				}
				response.RevisionWorkCompleted = rw.completed;

				using (var cmd = db.CreateCommand ()) {
					cmd.CommandText = "UPDATE Lane SET changed_date = @date WHERE id = @id;";
					DB.CreateParameter (cmd, "date", DateTime.UtcNow);
					DB.CreateParameter (cmd, "id", rw.lane_id);
					cmd.ExecuteNonQuery ();
				}

				// Check if any other lane depends on this one
				if (response.RevisionWorkCompleted) {
					using (IDbCommand cmd = db.CreateCommand ()) {
						cmd.CommandText = "SELECT 1 FROM LaneDependency WHERE dependent_lane_id = @lane_id LIMIT 1;";
						DB.CreateParameter (cmd, "lane_id", rw.lane_id);

						object value = cmd.ExecuteScalar ();
						if (value is int) {
							// If so, run the scheduler
							MonkeyWrench.Scheduler.Scheduler.ExecuteSchedulerAsync (false);
						}
					}
				}

				transaction.Commit ();
				return response;
			}
		}

		public void EditHostWithPassword (WebServiceLogin login, DBHost host, string password)
		{
			using (DB db = new DB ()) {
				using (IDbTransaction transaction = db.BeginTransaction ()) {
					VerifyUserInRole (db, login, Roles.Administrator);

					var oldHost = FindHost (db, host.id, null);
					host.Save (db);

					// NOTE: it is possible to change the password of an existing account by creating 
					// a host with the same name and specify the password. Given that admin rights
					// are required to create/modify hosts, it shouldn't pose a security issue.

					// TODO: if host changed name, delete the old user account.
					DBPerson person = FindPerson (db, host.host);

					if (person == null) {
						person = new DBPerson ();
						person.login = host.host;
						person.roles = Roles.BuildBot;
					} else {
						if (person.roles != Roles.BuildBot)
							throw new ArgumentException ("The hosts entry in the person table must have its roles set to 'BuildBot'.");
					}
					person.password = password;
					person.Save (db);
					transaction.Commit ();

					Audit (login, "edited host `{0}` -> `{1}`",
						Newtonsoft.Json.JsonConvert.SerializeObject(oldHost),
						Newtonsoft.Json.JsonConvert.SerializeObject(host)
					);
				}
			}
		}

		public void EditLaneWithTags (WebServiceLogin login, DBLane lane, string[] tags)
		{
			using (DB db = new DB ())
			using (var transaction = db.BeginTransaction()) {
				VerifyUserInRole (db, login, Roles.Administrator);

				var oldLane = FindLane (db, lane.id, null);
				lane.Save (db);

				using (var cmd = db.CreateCommand ()) {
					cmd.CommandText = "DELETE FROM LaneTag WHERE lane_id = @lane_id;";
					DB.CreateParameter (cmd, "lane_id", lane.id);
					cmd.ExecuteNonQuery ();

					if (tags != null) {
						cmd.CommandText = "INSERT INTO LaneTag (lane_id, tag) VALUES (@lane_id, @tag);";
						var tagParam = cmd.CreateParameter ();
						tagParam.ParameterName = "tag";
						cmd.Parameters.Add (tagParam);

						foreach (var tag in tags) {
							tagParam.Value = tag;
							cmd.ExecuteNonQuery ();
						}
					}
				}

				transaction.Commit ();

				Audit (login, "edited lane `{0}` -> `{1}`",
					Newtonsoft.Json.JsonConvert.SerializeObject(oldLane),
					Newtonsoft.Json.JsonConvert.SerializeObject(lane)
				);
			}
		}

        private void doInsertToDB()
        {
            using (DB db = new DB())
            {
                try
                {
                    db.BeginTransaction();

                    //TRUNCATE table Raw_Transaction
                    SqlParameter[] par = new SqlParameter[1];
                    par[0] = new SqlParameter("@batchNo", _batchNo);

                    db.ExecNonQuerySP("DeleteRawDataByFileName", par);

                    //Insert transaction records
                    foreach (string txVals in _alTX)
                    {
                        db.ExecNonQuery(string.Format("INSERT Raw_Transaction VALUES ({0})", txVals));
                    }

                    //Insert detail records
                    foreach (var detail in _dicDetail)
                    {
                        db.ExecNonQuery(string.Format("INSERT Raw_Detail VALUES ('{0}',N'{1}')", detail.Key, detail.Value.Replace("'", "''")));
                    }

                    //Insert BatchInfo
                    SqlParameter[] par1 = new SqlParameter[3];
                    par1[0] = new SqlParameter("batchNo", _batchNo);
                    par1[1] = new SqlParameter("fileName", _fileName);
                    par1[2] = new SqlParameter("inputBy", GlobalParam.Inst.gsUserID);
                    db.ExecNonQuerySP("InsertBatchInfo", par1);

                    db.CommitTransaction();
                    status.Text = "Inserted to DB.";
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    status.Text = ex.Message;
                    Log.LogErr(ex);
                }
            }
        }