private void init(string connString)
{
connection = new SQLiteConnection(connString);
connection.Open();
//connection.ChangeDatabase(Server.MySQLDatabaseName);
transaction = connection.BeginTransaction();
}
public void Dispose()
{
using (var conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
conn.Execute("create table Test (Id int primary key);");
using (var trans = conn.BeginTransaction())
{
conn.Execute("insert into Test(Id) values(1)", transaction: trans);
}
using (var cmd = new SQLiteCommand(@"select count(Id) from Test", conn))
Assert.AreEqual(0L, (long) cmd.ExecuteScalar());
}
}
async void Save_Clicked(object sender, EventArgs e)
{
Repository <Persona> repo = new Repository <Persona>();
Repository <Capitas> repo2 = new Repository <Capitas>();
Persona per = new Persona();
Capitas cap = new Capitas();
using (var conn = new SQLiteConnection(Constants.DatabasePath, Constants.Flags))
{
conn.BusyTimeout = new TimeSpan(30000000); //espera 1 segundo en caso de que la BBDD esté ocupada por otro hilo
conn.BeginTransaction();
try
{
per.Nombre = txtNombre.Text;
per.Apellido = txtApellido.Text;
per.Telfono = txtTelefono.Text;
per.Email = txtEmail.Text;
if (string.IsNullOrEmpty(per.Nombre) || string.IsNullOrEmpty(per.Apellido) || string.IsNullOrEmpty(per.Telfono) || string.IsNullOrEmpty(per.Email))
{
await DisplayAlert("Guardar", "Debe Llenar Todos Los Campos", "OK");
}
else
{
if (await repo.AddItem(per))
{
cap.IdPersona = per.ID;
cap.Enero = 0.00M;
cap.Febrero = 0.00M;
cap.Marzo = 0.00M;
cap.Abril = 0.00M;
cap.Mayo = 0.00M;
cap.Junio = 0.00M;
cap.Julio = 0.00M;
cap.Agosto = 0.00M;
cap.Septiembre = 0.00M;
cap.Octubre = 0.00M;
cap.Noviembre = 0.00M;
cap.Diciembre = 0.00M;
if (await repo2.AddItem(cap))
{
await DisplayAlert("Guardar", "Se Guardo Registro", "OK");
conn.Commit();
}
else
{
conn.Rollback();
await DisplayAlert("Guardar", "No Se Guardo Registro", "OK");
}
}
else
{
conn.Rollback();
await DisplayAlert("Guardar", "No Se Guardo Registro", "OK");
}
}
}
catch (SQLiteException ex)
{
SQLite3.Result result = ex.Result;
if (result == SQLite.SQLite3.Result.Busy || result == SQLite.SQLite3.Result.Locked)
{
}
conn.Rollback();
}
catch (Exception ex)
{
conn.Rollback();
await DisplayAlert("Error", ex.Message, "OK");
}
}
await Navigation.PopModalAsync();
}
/// <summary>
/// Update the database with the deserialised json returned from the connector
/// </summary>
/// <param name="projectRoot"></param>
public static void ProcessJSON(Project projectRoot)
{
//Insert JSON into database
using (SQLiteConnection conn = new SQLiteConnection(Preferences.Get("databaseLocation", "")))
{
if (projectRoot != null)
{
MessagingCenter.Send(new DataDAO(), "SyncMessage", "Creating project");
try
{
//Test to see if database schema exists
var projTableTest = conn.Table <Project>().Select(g => g).FirstOrDefault();
var projTableTest2 = conn.Table <ReferenceGeometry>().Select(g => g).FirstOrDefault();
//var proj = conn.Table<Project>().ToList();
}
catch (Exception e)
{
Console.WriteLine(e);
conn.CreateTable <Project>();
conn.CreateTable <ReferenceGeometry>();
conn.CreateTable <Record>();
conn.CreateTable <TextData>();
conn.CreateTable <NumericData>();
conn.CreateTable <BooleanData>();
conn.CreateTable <Layer>();
conn.CreateTable <Form>();
conn.CreateTable <FormField>();
conn.CreateTable <FieldChoice>();
}
try
{
//Add project
var projNew = projectRoot as Project;
projNew.lastSync = DateTime.Now;
var existingProject = conn.Table <Project>().Where(p => p.projectId == projNew.projectId).FirstOrDefault();
if (existingProject == null)
{
conn.Insert(projNew);
}
else
{
conn.Update(projNew);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
var project = Project.FetchProject(projectRoot.projectId);
foreach (var geom in projectRoot.geometries)
{
try
{
var existinggeom = geom;
geom.project_fk = project.Id;
var exgeom = conn.Table <ReferenceGeometry>().Select(g => g).Where(ReferenceGeometry => ReferenceGeometry.geometryId == geom.geometryId).FirstOrDefault();
if (exgeom != null)
{
existinggeom = conn.GetWithChildren <ReferenceGeometry>(exgeom.Id);
var id = existinggeom.Id;
existinggeom = geom;
existinggeom.Id = id;
existinggeom.status = geom.status;
conn.Update(existinggeom);
}
else if (geom.status != 3)
{
conn.Insert(geom);
}
//Geometry related records
foreach (var rec in geom.records)
{
try
{
var existingrec = conn.Table <Record>().Select(g => g).Where(Record => Record.recordId == rec.recordId).FirstOrDefault();
if (existingrec != null)
{
var id = existingrec.Id;
existingrec = rec;
existingrec.project_fk = project.Id;
existingrec.Id = id;
existingrec.status = rec.status;
existingrec.geometry_fk = geom.Id;
conn.Update(existingrec);
}
else if (rec.status != 3)
{
rec.project_fk = project.Id;
conn.Insert(rec);
}
if (rec.status != 3)
{
try
{
//Add text records
foreach (var txt in rec.texts)
{
if (txt.title == null)
{
txt.title = "";
}
try
{
conn.InsertOrReplace(txt);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//Add numeric records
foreach (var num in rec.numerics)
{
if (num.title == null)
{
num.title = "";
}
try
{
conn.InsertOrReplace(num);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//Add boolean records
foreach (var onebool in rec.booleans)
{
if (onebool.title == null)
{
onebool.title = "";
}
try
{
conn.InsertOrReplace(onebool);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
rec.texts = rec.texts;
rec.numerics = rec.numerics;
rec.booleans = rec.booleans;
conn.UpdateWithChildren(rec);
Console.WriteLine("Added record children: " + DateTime.Now.ToLongTimeString());
}
catch (Exception e)
{
Console.WriteLine(e);
}
conn.InsertOrReplaceAllWithChildren(geom.records);
/*foreach (var r in geom.records)
* {
* var geomrec = existinggeom.records.Where(gr => gr.recordId == r.recordId).FirstOrDefault();
* existinggeom.records.Remove(geomrec);
* existinggeom.records.Add(r);
* }*/
conn.UpdateWithChildren(existinggeom);
Console.WriteLine("Added record: " + DateTime.Now.ToLongTimeString());
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
// Add project related records
foreach (var rec in projectRoot.records)
{
try
{
rec.project_fk = project.Id;
conn.InsertOrReplaceWithChildren(rec);
if (rec.status != 3)
{
try
{
//Add text records
foreach (var txt in rec.texts)
{
if (txt.title == null)
{
txt.title = "";
}
try
{
var existingtxt = conn.Table <TextData>().Select(g => g).Where(TextData => TextData.textId == txt.textId).FirstOrDefault();
if (existingtxt != null)
{
var id = existingtxt.Id;
existingtxt = txt;
existingtxt.Id = id;
conn.Update(existingtxt);
}
else
{
conn.Insert(txt);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//Add numeric records
foreach (var num in rec.numerics)
{
if (num.title == null)
{
num.title = "";
}
try
{
var existingnum = conn.Table <NumericData>().Select(g => g).Where(NumericData => NumericData.numericId == num.numericId).FirstOrDefault();
if (existingnum != null)
{
var id = existingnum.Id;
existingnum = num;
existingnum.Id = id;
conn.Update(existingnum);
}
else
{
conn.Insert(num);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//Add boolean records
foreach (var onebool in rec.booleans)
{
if (onebool.title == null)
{
onebool.title = "";
}
try
{
var existingbool = conn.Table <BooleanData>().Select(g => g).Where(BooleanData => BooleanData.booleanId == onebool.booleanId).FirstOrDefault();
if (existingbool != null)
{
var id = existingbool.Id;
existingbool = onebool;
existingbool.Id = id;
conn.Update(existingbool);
}
else
{
conn.Insert(onebool);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
var queriedrec = rec;// conn.Table<Record>().Select(g => g).Where(Record => Record.recordId == rec.recordId).FirstOrDefault();
queriedrec.texts = rec.texts;
queriedrec.numerics = rec.numerics;
queriedrec.booleans = rec.booleans;
conn.UpdateWithChildren(queriedrec);
Console.WriteLine("Added record children: " + DateTime.Now.ToLongTimeString());
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
// Add project related forms
foreach (var form in projectRoot.forms)
{
try
{
form.project_fk = project.Id;
var existingform = conn.Table <Form>().Select(g => g).Where(Form => Form.formId == form.formId).Where(Form => Form.project_fk == project.Id).FirstOrDefault();
if (existingform != null)
{
//Delete the full form and replace it
var fullForm = conn.GetWithChildren <Form>(existingform.Id, true);
conn.Delete(fullForm);
}
if (form.status < 3)
{
try
{
conn.Insert(form);
}
catch (Exception e)
{
if (e.Message == "table Form has no column named status")
{
//MIGRATE DB - Add status
try
{
conn.BeginTransaction();
conn.Execute("ALTER TABLE Form ADD status int NOT NULL DEFAULT(0);");
conn.Commit();
conn.Insert(form);
}
catch (Exception e2)
{
App.Current.MainPage.DisplayAlert("Wir sind auf ein Problem gestossen", "Ihr Formular konnte nicht synchronisiert werden. Bitte kontaktieren Sie den Support.", "Ok");
}
}
}
}
//Add form fields
foreach (var formfield in form.formFields)
{
try
{
formfield.form_fk = form.Id;
conn.Insert(formfield);
//Add field choices
foreach (var fieldChoice in formfield.fieldChoices)
{
try
{
fieldChoice.formField_fk = formfield.Id;
conn.Insert(fieldChoice);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
var queriedfield = conn.Table <FormField>().Select(g => g).Where(FormField => FormField.fieldId == formfield.fieldId).Where(FormField => FormField.form_fk == formfield.form_fk).FirstOrDefault();
queriedfield.fieldChoices = formfield.fieldChoices;
conn.UpdateWithChildren(queriedfield);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
var queriedform = conn.Table <Form>().Select(g => g).Where(Form => Form.formId == form.formId).Where(Form => Form.project_fk == project.Id).FirstOrDefault();
queriedform.formFields = form.formFields;
conn.UpdateWithChildren(queriedform);
Console.WriteLine("Added form: " + DateTime.Now.ToLongTimeString());
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
//Add project related layers
try
{
//Delete existing layers (need to delete all layers to start, as we are not informed when a layer is removed from the project)
var existingLayers = conn.Table <Layer>().Select(g => g).Where(Layer => Layer.project_fk == project.Id);
foreach (var existingLayer in existingLayers)
{
conn.Delete(existingLayer);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
//add new layers
foreach (var layer in projectRoot.layers)
{
try
{
layer.project_fk = project.Id;
var existingLayer = conn.Table <Layer>().Select(g => g).Where(Layer => Layer.layerId == layer.layerId).FirstOrDefault();
if (existingLayer != null)
{
conn.Delete(existingLayer);
}
conn.Insert(layer);
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
try
{
project.geometries = conn.Table <ReferenceGeometry>().Select(g => g).Where(g => g.project_fk == project.Id).ToList();
project.records = conn.Table <Record>().Select(g => g).Where(g => g.project_fk == project.Id).ToList();
project.forms = conn.Table <Form>().Select(g => g).Where(g => g.project_fk == project.Id).ToList();
project.layers = conn.Table <Layer>().Select(g => g).Where(g => g.project_fk == project.Id).ToList();
conn.UpdateWithChildren(project);
Console.WriteLine("Added records geometries and layers: " + DateTime.Now.ToLongTimeString());
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
MessagingCenter.Send <Application, string>(Application.Current, "SyncMessage", "");
}
public override void HandleRequest()
{
CheckParameter("file_id", "label_id");
var file_ids = Parameters["file_id"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(x => new Guid(x)).ToList();
var label_id = new Guid(Parameters["label_id"]);
using (var conn = new SQLiteConnection(MyDbContext.ConnectionString))
{
conn.Open();
using (var query = conn.CreateCommand())
using (var insert = conn.CreateCommand())
{
query.CommandText = "select 1 from [LabelFiles] where label_id = @label and file_id = @file";
query.Prepare();
insert.CommandText = "insert into [LabelFiles] (label_id, file_id) values (@label, @file)";
insert.Prepare();
using (var trans = conn.BeginTransaction())
{
bool inserted = false;
foreach (var file_id in file_ids)
{
var param_file = new SQLiteParameter("@file", file_id);
var param_label = new SQLiteParameter("@label", label_id);
query.Parameters.Clear();
query.Parameters.Add(param_file);
query.Parameters.Add(param_label);
var alreadyTagged = (query.ExecuteScalar() != null);
if (alreadyTagged)
{
continue;
}
insert.Parameters.Clear();
insert.Parameters.Add(param_file);
insert.Parameters.Add(param_label);
insert.ExecuteNonQuery();
inserted = true;
}
if (inserted)
{
using (var update = conn.CreateCommand())
{
update.CommandText = "update [Labels] set seq = @seq where label_id = @label";
update.Parameters.Add(new SQLiteParameter("@label", label_id));
update.Parameters.Add(new SQLiteParameter("@seq", (object)SeqNum.GetNextSeq()));
update.ExecuteNonQuery();
}
}
trans.Commit();
}
}
}
respondSuccess();
}
bool IPureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if (fCreated)
{
try {
using (SQLiteConnection cn = new SQLiteConnection()) {
cn.ConnectionString = fConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction()) {
try {
using (DbCommand cmd = cn.CreateCommand()) {
cmd.Transaction = tr;
cmd.CommandText = SingleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand()) {
cmd.Transaction = tr;
cmd.CommandText = SingleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
} catch (Exception ex) {
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex);
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if (Interlocked.Increment(ref fPreAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
} catch (Exception ex) {
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex);
ret = false;
}
}
return(ret);
}
private bool WriteTracks(IITTrackCollection tracks, string sourceName)
{
try {
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + databaseFileName;
connection.Open();
SQLiteCommand command = new SQLiteCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
using (SQLiteTransaction transaction = connection.BeginTransaction()) {
command.CommandText = "INSERT INTO iTunesLibrary (Name, Album, Artist, Compilation, DateAdded, DiscCount, DiscNumber, Genre, Kind, PlayedCount, PlayedDate, Rating, Time, TrackCount, TrackNumber, Year, Source) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
command.Parameters.Add(new SQLiteParameter("paramName"));
command.Parameters.Add(new SQLiteParameter("paramAlbum"));
command.Parameters.Add(new SQLiteParameter("paramArtist"));
command.Parameters.Add(new SQLiteParameter("paramCompilation"));
command.Parameters.Add(new SQLiteParameter("paramDateAdded"));
command.Parameters.Add(new SQLiteParameter("paramDiscCount"));
command.Parameters.Add(new SQLiteParameter("paramDiscNumber"));
command.Parameters.Add(new SQLiteParameter("paramGenre"));
command.Parameters.Add(new SQLiteParameter("paramKind"));
command.Parameters.Add(new SQLiteParameter("paramPlayedCount"));
command.Parameters.Add(new SQLiteParameter("paramPlayedDate"));
command.Parameters.Add(new SQLiteParameter("paramRating"));
command.Parameters.Add(new SQLiteParameter("paramTime"));
command.Parameters.Add(new SQLiteParameter("paramTrackCount"));
command.Parameters.Add(new SQLiteParameter("paramTrackNumber"));
command.Parameters.Add(new SQLiteParameter("paramYear"));
command.Parameters.Add(new SQLiteParameter("paramSource"));
command.Parameters["paramSource"].Value = sourceName;
foreach (IITTrack track in tracks)
{
if (track.KindAsString != "QuickTime movie file" && track.KindAsString != "PDF document")
{
command.Parameters["paramName"].Value = track.Name;
command.Parameters["paramAlbum"].Value = track.Album;
command.Parameters["paramArtist"].Value = track.Artist;
command.Parameters["paramCompilation"].Value = track.Compilation;
command.Parameters["paramDateAdded"].Value = track.DateAdded;
command.Parameters["paramDiscCount"].Value = track.DiscCount;
command.Parameters["paramDiscNumber"].Value = track.DiscNumber;
command.Parameters["paramGenre"].Value = track.Genre;
command.Parameters["paramKind"].Value = track.KindAsString;
command.Parameters["paramPlayedCount"].Value = track.PlayedCount;
command.Parameters["paramPlayedDate"].Value = track.PlayedDate;
command.Parameters["paramRating"].Value = track.Rating;
command.Parameters["paramTime"].Value = track.Time;
command.Parameters["paramTrackCount"].Value = track.TrackCount;
command.Parameters["paramTrackNumber"].Value = track.TrackNumber;
command.Parameters["paramYear"].Value = track.Year;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
command.Dispose();
connection.Close();
connection.Dispose();
return(true);
} catch (Exception ex) {
Console.WriteLine("Unable to write tracks.");
Console.WriteLine("Error message: " + ex.Message);
return(false);
}
}
private void PostDbParams(string inName, SQLiteConnection updConn)
#endif
{
_dbParams["version"] = _dbParams["version.major"] + "." + _dbParams["version.minor"];
_dbParams["ot"] = inName.EndsWith(".ont") ? "1" : "0";
_dbParams["nt"] = "1";
_dbParams["strong"] = "0";
if (!_dbParams.ContainsKey("rtl"))
{
_dbParams["rtl"] = "0";
}
var detailTrx = updConn.BeginTransaction();
const string map = "Description=description,Abbreviation=short.title,Comments=about,Version=version,VersionDate=version.date,PublishDate=publish.date,RightToLeft=rtl,OT=ot,NT=nt,Strong=strong";
var mapPat = new Regex(@"([A-Za-z]+)=([\.a-z]+)");
foreach (Match match in mapPat.Matches(map))
{
var key = match.Groups[2].Value;
if (_dbParams.ContainsKey(key))
{
var detailCmd = updConn.CreateCommand();
var newValue = string.Format(@"""{0}""", _dbParams[key]);
newValue = ParseDateIfNecessary(key, newValue);
detailCmd.CommandText = string.Format("update Details SET {0} = {1}", match.Groups[1].Value, newValue);
int result = detailCmd.ExecuteNonQuery();
Debug.Assert(result == 1, match.Groups[0].Value);
}
}
detailTrx.Commit();
}
public void SelectTarget(SQLiteConnection con)
{
Program.MainForm.Log("Loading Peaks..");
LoadPeaks(con);
//Number of files
SQLiteCommand Select = new SQLiteCommand("Select Count(*) from Files", con);
SQLiteDataReader Reader = Select.ExecuteReader();
Reader.Read();
FileCount = Reader.GetInt32(0);
Reader.Close();
Select = new SQLiteCommand("Select Distinct Mode from Files", con);
Reader = Select.ExecuteReader();
Reader.Read();
string Mode = Reader.GetInt32(0) > 0 ? "+" : "-";
List <Peak[]> Rec = new List <Peak[]>();
Program.MainForm.Log("Pairing Peaks..");
//Pairing
Peaks.Sort(new Peak.byMZ());
for (int i = 0; i < Peaks.Count; i++)
{
for (int j = i; j >= 0; j--) //пейрить нужно и с собой тоже!!
{
if (Peaks[i].Mode != Peaks[j].Mode)
{
continue;
}
//MassDiff is mass difference in ppm
double MassDiff = Math.Abs(((Peaks[i].MeanMass - Peaks[j].MeanMass) / (Peaks[i].MeanMass / 2.0 + Peaks[j].MeanMass / 2.0)) * 1000000.0);
if (MassDiff > Properties.Settings.Default.Mass_Accuracy)
{
break;
}
//RTDiff is retention time difference
double RTDiff = Math.Abs(Peaks[i].Apex - Peaks[j].Apex);
if (RTDiff < Properties.Settings.Default.RTError)
{
Peaks[i].Paired.Add(Peaks[j]);
Peaks[j].Paired.Add(Peaks[i]);
Peaks[i].Distances.Add(RTDiff);
Peaks[j].Distances.Add(RTDiff);
}
}
for (int j = i + 1; j < Peaks.Count; j++)
{
if (Peaks[i].Mode != Peaks[j].Mode)
{
continue;
}
double MassDiff = Math.Abs(((Peaks[i].MeanMass - Peaks[j].MeanMass) / (Peaks[i].MeanMass / 2.0 + Peaks[j].MeanMass / 2.0)) * 1000000.0);
if (MassDiff > Properties.Settings.Default.Mass_Accuracy)
{
break;
}
double RTDiff = Math.Abs(Peaks[i].Apex - Peaks[j].Apex);
if (RTDiff < Properties.Settings.Default.RTError)
{
Peaks[i].Paired.Add(Peaks[j]);
Peaks[j].Paired.Add(Peaks[i]);
Peaks[i].Distances.Add(RTDiff);
Peaks[j].Distances.Add(RTDiff);
}
}
Application.DoEvents();
}
Program.MainForm.Log("Grouping Peaks..");
//grouping to isolated subsets
List <List <Peak> > Subsets = new List <List <Peak> >();
while (Peaks.Count > 0)
{
if (Peaks[0].Paired == null || Peaks[0].Paired.Count == 0)
{
Peaks.RemoveAt(0);
continue;
}
List <Peak> Subset = new List <Peak>();
Subset.Add(Peaks[0]);
Peaks.RemoveAt(0);
for (int i = 0; i < Subset.Count; i++)
{
for (int j = 0; j < Subset[i].Paired.Count; j++)
{
if (!Subset.Contains(Subset[i].Paired[j]))
{
Subset.Add(Subset[i].Paired[j]);
Peaks.Remove(Subset[i].Paired[j]);
}
}
}
//for speed optimization a number of Peaks.remove can be
//substituted with single copying peaks to another list with exclusion to peaks of subset
//subsets can be processed in paralel
Subsets.Add(Subset);
}
Program.MainForm.Log(String.Format("Grouping Peaks - {0} groups found", Subsets.Count));
Program.MainForm.Log("Define Targets...");
List <Peak[]> Targets = new List <Peak[]>();
for (int m = 0; m < Subsets.Count; m++)
{
//Take current subset
List <Peak> Subset = Subsets[m];
//Distance sorting
DistanceSorting(Subset);
//Targets Selection
while (Subset.Count > 0)
{
Application.DoEvents();
//That is a peak array serves to build a target
Peak[] T = new Peak[FileCount];
int NullCount = 0;
for (int i = 0; i < FileCount; i++)
{
T[i] = Subset[0].Closest[i];
if (Subset[0].Closest[i] == null)
{
NullCount++;
}
else
{
Subset[0].Closest[i].Used = true;
}
}
if (FileCount - NullCount < Properties.Settings.Default.Commons)
{
break;
}
Targets.Add(T);
Subset.RemoveAt(0);
for (int i = Subset.Count - 1; i >= 0; i--)
{
if (Subset[i].Used)
{
Subset.RemoveAt(i);
}
}
DistanceSorting(Subset);
}
}
SQLiteCommand Command = new SQLiteCommand("Update Features Set IonID = NULL", con);
Command.ExecuteNonQuery();
Program.MainForm.Log("Save Targets...");
//Save Targets
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO Targets (TargetID, Name, Desc, MZ, RT, RTMin, RTMax, C13ToCheck, N15ToCheck, Candidates, Ambigous) " +
"Values ( @ID, @Name, @Desc, @MZ, @RT, @RTMin, @RTMax," +
Properties.Settings.Default.C13_to_Check.ToString() + ", 0, 0, 0 ); " +
"INSERT INTO Ions (TargetID, IonID, Adduct, Mode, MZ, Strongs, Candidates, Ambigous) " +
"Values ( @ID, @ID, \"\", @Mode, @MZ, 0, 0, 0);", con);
SQLiteParameter ID = new SQLiteParameter("@ID");
Insert.Parameters.Add(ID);
SQLiteParameter Name = new SQLiteParameter("@Name");
Insert.Parameters.Add(Name);
SQLiteParameter Desc = new SQLiteParameter("@Desc");
Insert.Parameters.Add(Desc);
SQLiteParameter MZP = new SQLiteParameter("@MZ");
Insert.Parameters.Add(MZP);
SQLiteParameter RTP = new SQLiteParameter("@RT");
Insert.Parameters.Add(RTP);
SQLiteParameter RTMinP = new SQLiteParameter("@RTMin");
Insert.Parameters.Add(RTMinP);
SQLiteParameter RtMaxP = new SQLiteParameter("@RTMax");
Insert.Parameters.Add(RtMaxP);
SQLiteParameter ModeP = new SQLiteParameter("@Mode");
Insert.Parameters.Add(ModeP);
SQLiteTransaction tr = con.BeginTransaction();
for (int i = 0; i < Targets.Count; i++)
{
//benefit to recognised peaks
int Count = 0;
double MZ = 0.0;
List <Peak> PeaksForTarget = new List <Peak>();
List <Peak> TracesForTarget = new List <Peak>();
for (int j = 0; j < FileCount; j++)
{
if (Targets[i][j] != null)
{
MZ += Targets[i][j].MeanMass;
Count++;
if (Targets[i][j].Source == PeakOrTrace.Trace)
{
TracesForTarget.Add(Targets[i][j]);
}
else
{
PeaksForTarget.Add(Targets[i][j]);
}
}
}
MZ = MZ / (double)Count;
if (PeaksForTarget.Count == 0)
{
PeaksForTarget = TracesForTarget;
}
double RT = 0.0, RTLeft = 1000000.0, RTRight = 0.0;
for (int j = 0; j < PeaksForTarget.Count; j++)
{
RT += PeaksForTarget[j].Apex / ((double)PeaksForTarget.Count);
if (PeaksForTarget[j].Left < RTLeft)
{
RTLeft = PeaksForTarget[j].Left;
}
if (PeaksForTarget[j].Right > RTRight)
{
RTRight = PeaksForTarget[j].Right;
}
}
ID.Value = i;
Name.Value = String.Format("Target #{0}", i);
//introdused for benchmarking
//Desc.Value = String.Format("RT - {0}, MZ - {1}", RT, MZ);
string Inc = "";
for (int j = 0; j < FileCount; j++)
{
Inc += (Targets[i][j] == null)?"0;":"1;";
}
Desc.Value = "Found in files: " + Inc;
MZP.Value = MZ;
RTP.Value = RT;
RTMinP.Value = RTLeft;
RtMaxP.Value = RTRight;
ModeP.Value = Mode;
Insert.ExecuteNonQuery();
//change peaks and traces to this query
Target TargetForSave = new Target();
TargetForSave.ID = i;
TargetForSave.IonID = i;
for (int j = 0; j < PeaksForTarget.Count; j++)
{
TargetForSave.SetPeakToTarget(PeaksForTarget[j], con);
}
for (int j = 0; j < TracesForTarget.Count; j++)
{
TargetForSave.SetPeakToTarget(TracesForTarget[j], con);
}
}
tr.Commit();
Program.MainForm.Log("Targets definition has been finished...");
}
private void ThreadProc2(object state)
{
var barrier = (Barrier) state;
using (var conn = new SQLiteConnection(m_csb.ConnectionString))
{
barrier.SignalAndWait();
conn.Open();
barrier.SignalAndWait();
using (var trans = conn.BeginTransaction())
{
conn.Execute("select Id from Test", transaction: trans);
conn.Execute("update Test set Id = 3;", transaction: trans);
trans.Commit();
}
}
}
private void ThreadProc1(object state)
{
var barrier = (Barrier) state;
using (var conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute("select Id from Test", transaction: trans);
barrier.SignalAndWait();
conn.Execute("update Test set Id = 2;", transaction: trans);
barrier.SignalAndWait();
// give the other thread time to attempt begin the transaction, which will hang if both threads
// try to write concurrently
Thread.Sleep(TimeSpan.FromSeconds(2));
trans.Commit();
}
}
}
//: db change
void myEventLog_EntryWrittenDb(object sender, EntryWrittenEventArgs e)
{
int count = 0;
EventEntry ee = null;
if(e.Entry.Message.ToLower().Contains("registry"))
{
ee = new EventEntry(e.Entry);
}
else
{
// open or access attempt
if ((e.Entry.InstanceId == 560) || (e.Entry.InstanceId == 567))
{
int handle = GetHandle(e.Entry);
if (handle != 0)
{
EventLogEntry counterPart = CheckIfHandleExists(handle);
if (counterPart != null)
{
ee = CombineEvents(handle, e.Entry, counterPart);
}
else
{
if (e.Entry.InstanceId == 560 && !"".Equals(getFileName(e.Entry.Message)))
{
handleToEvent.Add(handle, e.Entry);
//make the action as blank as this is the place where handle was granted. no action has taken place.
handleToAction.Add(handle, "");
//increment count
handleToCount.TryGetValue(handle, out count);
count++;
handleToCount[handle] = count;
//TODO: removed for removing client user
//NEW addition for client side file access:
//since 567 is not logged we put 560 event caused by client in the tree
/*if (fromClient(e.Entry))
{
ee = new EventEntry(e.Entry);
}*/
}
}
}
}
}
//remove event from dictonary
if (e.Entry.InstanceId == 562)
{
count = 0;
int handle = GetHandle(e.Entry);
if (handle != 0)
{
try
{
if (handleToCount[handle] == 0)
{
handleToEvent.Remove(handle);
handleToCount.Remove(handle);
handleToAction.Remove(handle);
}
else
{
count--;
handleToCount[handle] = count;
}
}
catch (KeyNotFoundException)
{
}
}
}
if (ee != null)
{
//also check if the db table is present first time the app is run. If not we need to create it.
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + System.Environment.GetEnvironmentVariable("windir") + "\\system\\dpfam.db"))
{
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand())
{
comm.Connection = conn;
//comm.CommandText = "CREATE TABLE IF NOT EXISTS FAM (time DATETIME, machine TEXT, filename TEXT, user TEXT, clientUser TEXT, action TEXT, dataindex INTEGER, category TEXT, result TEXT, msg TEXT, msgSrc TEXT, instanceId INTEGER, handleId INTEGER);";
try
{
//create db if not present
// comm.ExecuteNonQuery();
//Check if the event is to be inserted.
//insert the passed event in the db.
using (SQLiteTransaction tran = conn.BeginTransaction())
//TODO: using transactions dont help performance as only one entry is being inserted each time.
{
insertData(ee, comm);
tran.Commit();
}
}
catch (SQLiteException)
{
//MessageBox.Show("Insert Data Failed", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
conn.Close();
}
//TODO: error when auto update is off
//check if it fits in the filters --> if yes, add it to grid and actusedtree as well!
if (this.FitsToFilters(ee))
{
try
{
if (Changed != null)
Changed(ee, EventArgs.Empty);
System.Console.WriteLine("event written\n");
}
catch (NullReferenceException)
{
// This catches the null reference which is caused when we deregister the handler on making the auto update off.
// wont come here as null is being checked before calling.
}
}
//TODO: add to actual tree being used. As of now i plan to use only one tree throughout the appln to maintain the
//grid display.
}
}
public void stubInsertData(EventEntry e)
{
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + System.Environment.GetEnvironmentVariable("windir") + "\\system\\dpfam.db"))
{
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand())
{
comm.Connection = conn;
//comm.CommandText = "CREATE TABLE IF NOT EXISTS FAM (time DATETIME, machine TEXT, filename TEXT, user TEXT, clientUser TEXT, action TEXT, dataindex INTEGER, category TEXT, result TEXT, msg TEXT, msgSrc TEXT, instanceId INTEGER, handleId INTEGER);";
try
{
//create db if not present
// comm.ExecuteNonQuery();
//Check if the event is to be inserted.
//insert the passed event in the db.
using (SQLiteTransaction tran = conn.BeginTransaction())
//TODO: using transactions dont help performance as only one entry is being inserted each time.
{
insertData(e, comm);
tran.Commit();
}
}
catch (SQLiteException)
{
//MessageBox.Show("Insert Data Failed", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
conn.Close();
}
}
/*
public void setRequestedDataType(byte dataType)
{
this.requestedDataType = dataType;
}
*/
public BinarySearchTree<EventEntry> startup()
{
if (runningAsServer == false)
return null;
//get the latest time date entry from the db.
DateTime latestDate = GetLatestEntryFromDb();
int count = 0;
EventLog myEventLog = new EventLog("Security", ".");
EventLogEntryCollection myLogEntryCollection = myEventLog.Entries;
events = new BinarySearchTree<EventEntry>(new CompareFile()); //new List<EventEntry>();
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + System.Environment.GetEnvironmentVariable("windir") + "\\system\\dpfam.db"))
{
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand())
{
comm.Connection = conn;
//comm.CommandText = "CREATE TABLE IF NOT EXISTS FAM (time DATETIME, machine TEXT, filename TEXT, user TEXT, clientUser TEXT, action TEXT, dataindex INTEGER, category TEXT, result TEXT, msg TEXT, msgSrc TEXT, instanceId INTEGER, handleId INTEGER);";
try
{
//create db if not present
// comm.ExecuteNonQuery();
//Check if the event is to be inserted.
//insert the passed event in the db.
using (SQLiteTransaction tran = conn.BeginTransaction())
//TODO: using transactions dont help performance as only one entry is being inserted each time.
{
foreach (EventLogEntry entry in myLogEntryCollection)
{
//check if time of entry greater than the max time we got from db.
//if yes: consider this event to be shown. insert in db and update grid.
if (latestDate.CompareTo(entry.TimeWritten) < 0)
{
EventEntry ee = null;
if (entry.Message.ToLower().Contains("\\registry"))
{
ee = new EventEntry(entry);
ee.setAction("Set Key Value");
}
else if ((entry.InstanceId == 560) || (entry.InstanceId == 567))
{
int handle = GetHandle(entry);
if (handle != 0)
{
EventLogEntry counterPart = CheckIfHandleExists(handle);
// if handle exists combine the two events.
if (counterPart != null)
{
ee = CombineEvents(handle, entry, counterPart);
}
// else add handle to the handles list.
//(irrespective of whether a 560 or 567 event occurs 1st record it)
else
{
if (entry.InstanceId == 560 && !"".Equals(getFileName(entry.Message)))
{
handleToEvent.Add(handle, entry);
//make the action as blank as this is the place where handle was granted. no action has taken place.
handleToAction.Add(handle, "");
// increment count
handleToCount.TryGetValue(handle,out count);
count++;
//handleToCount[handle] = count;
handleToCount.Add(handle, count);
//TODO: removed for removing client user
//NEW addition for client side file access:
//since 567 is not logged we put 560 event caused by client in the tree
/*if (fromClient(entry))
{
ee = new EventEntry(entry);
}*/
}
}
}
}
//TODO: can see some 562 entries without their 560 in the log. Handle them by ignore them ?
// if it is a remove
else if (entry.InstanceId == 562)
{
count = 0;
int handle = GetHandle(entry);
if (handle != 0)
{
try
{
if (handleToCount[handle] == 0)
{
handleToEvent.Remove(handle);
handleToCount.Remove(handle);
handleToAction.Remove(handle);
}
else
{
count--;
handleToCount[handle] = count;
}
}
catch (KeyNotFoundException)
{
}
}
}
//add event in the file tree
if (ee != null)
{
//currently not used
events.Add(ee);
//add the new entries to the db
insertData(ee, comm);
}
}
//else ignore the event altogether.
else
{
continue;
}
}
tran.Commit();
}
}
catch (SQLiteException)
{
//MessageBox.Show("Insert Data Failed", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
conn.Close();
}
//TODO: put this at the bottom.. so that entries are not written twice.
myEventLog.EntryWritten += new EntryWrittenEventHandler(myEventLog_EntryWrittenDb);
myEventLog.EnableRaisingEvents = true;
//TODO: add code to read from the log file. Compare the date with the latest time stamp. If the entry is new enter in db and update the screen.
// other data structs like handleToEvent etc also have to be maintained but since the kernel wont log any actions with handle id it needs to be
// decided what action has to be taken.
//Code to add a watcher to the file which is being written to by the kernel.
//Added a component in File overview which takes care of changes. Just need to make sure that can read entries and insert them into db before nw
//accesses are done.
return events;
}
//private void writeSQLTable(string csvFileName)
private void writeSQLTable(DataTable dt)
{
SQLiteConnection dbConnection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbFile));
dbConnection.Open();
sbrpt.AppendLine("*** Inserting records into db table ***");
sbrpt.AppendLine("master datatable record count: " + dt.Rows.Count.ToString());
IDbTransaction transaction1 = dbConnection.BeginTransaction();
for (int i = 0; i < dt.Rows.Count; i++)
{
StringBuilder sbColNames = new StringBuilder("insert into " + "NHDv21Attributes" + " (");
StringBuilder sbValues = new StringBuilder(" values (");
int count = 0;
foreach (DataColumn dc in dt.Columns)
{
if (count > 0)
{
sbColNames.Append(", ");
sbValues.Append(", ");
}
sbColNames.Append("[" + dc.ColumnName + "]");
sbValues.Append("'" + dt.Rows[i][dc].ToString() + "'");
count++;
}
sbColNames.Append(")");
sbValues.Append(")");
string sql = sbColNames.ToString() + sbValues.ToString();
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
}
transaction1.Commit();
transaction1.Dispose();
dbConnection.Close();
sbrpt.AppendLine("db data records inserted: " + dt.Rows.Count.ToString());
string infile = string.Empty;
if (File.Exists(procreport))
{
StreamReader sr = new StreamReader(procreport);
infile = sr.ReadToEnd();
sr.Close();
}
sbrpt.AppendLine("");
sbrpt.AppendLine("");
string outfile = infile + sbrpt.ToString();
StreamWriter sw = new StreamWriter(procreport);
sw.Write(outfile);
sw.Close();
sw.Dispose();
MessageBox.Show("Done", "AttaBoy", MessageBoxButtons.OK);
}
public async Task LoadCodes()
{
try
{
Status = "";
if (CodeGroups != null)
{
await Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(
() =>
{
CodeGroups.Clear();
}));
}
await Task.Factory.StartNew(async() =>
{
using (var conn = new SQLiteConnection(Connection.ConnString))
{
conn.Open();
using (var trns = conn.BeginTransaction())
{
using (var comm = new SQLiteCommand(conn))
{
comm.CommandText =
"SELECT code_lib_groups.rowid, code_lib_groups.title, code_lib_groups.desc, code_lib_groups.desc_prev, code_lib_groups.star " +
"FROM code_lib_groups " +
"JOIN code_lib_langs ON code_lib_groups.langid = code_lib_langs.rowid " +
"WHERE code_lib_groups.userid = (SELECT userid FROM users WHERE active) " +
"AND code_lib_groups.langid = (SELECT active_langid FROM code_lib_settings WHERE active_langid)";
using (var reader = comm.ExecuteReader())
{
if (reader.HasRows)
{
var i = 0;
while (reader.Read())
{
var groupadd = new CodeGroups()
{
RowId = Convert.ToInt64(reader["rowid"]),
Title = reader["title"].ToString(),
Desc = reader["desc"].ToString(),
DescPrev = reader["desc_prev"].ToString(),
Star = Convert.ToBoolean(Convert.ToInt32(reader["star"] != null ? reader["star"].ToString() : "0")),
Codes = new ObservableCollection <Codes>()
};
await Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(
() =>
{
CodeGroups.Add(groupadd);
}));
using (var commforcodes = new SQLiteCommand(conn))
{
commforcodes.CommandText =
"SELECT rowid,* FROM code_lib_codes " +
"WHERE code_lib_codes.userid = (SELECT userid FROM users WHERE active) " +
"AND code_lib_codes.groupid = '" + reader["rowid"] + "' " +
"ORDER BY code_lib_codes.date";
using (var readerforcodes = commforcodes.ExecuteReader())
{
if (readerforcodes.HasRows)
{
var icodecount = 0;
while (readerforcodes.Read())
{
icodecount++;
var codeadd = new Codes()
{
RowId = Convert.ToInt64(readerforcodes["rowid"]),
OrderNum = icodecount,
Title = readerforcodes["title"].ToString(),
Desc = readerforcodes["desc"].ToString(),
DescPrev = readerforcodes["desc_prev"].ToString(),
GroupId = Convert.ToInt64(readerforcodes["groupid"].ToString()),
Color = readerforcodes["color"].ToString(),
InvertColor = readerforcodes["invert_color"].ToString(),
};
var ii = i;
await Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(
() =>
{
CodeGroups[ii].Codes.Add(codeadd);
}));
}
}
}
}
i++;
}
}
}
}
trns.Commit();
}
SelectedCodeGroup = CodeGroups[0];
conn.Close();
}
}).ContinueWith(async(T) =>
{
await LoadColors();
});
}
catch (Exception exp)
{
Debug.WriteLine(exp.ToString());
}
}
/// <summary>
/// 艦娘情報を一括で挿入・上書きする
/// </summary>
/// <param name="kammusuList">艦娘と初期装備のペアのリスト</param>
/// <param name="forceFlg">既存データがある場合上書きしないならfalse</param>
public void SaveAll(List <KeyValuePair <Kammusu, List <int> > > kammusuList, bool forceFlg)
{
// 上書きしないオプションを反映する
var kammusuList2 = kammusuList;
if (!forceFlg)
{
string idList = string.Join(",", kammusuList.Select(w => w.Key.Id.ToString()));
var temp = ExecuteReader($"SELECT id FROM kammusu WHERE id IN ({idList})");
var temp2 = temp.Select(r => (int)r["id"]).ToHashSet();
kammusuList2 = new List <KeyValuePair <Kammusu, List <int> > >();
foreach (var pair in kammusuList)
{
if (!temp2.Contains(pair.Key.Id))
{
kammusuList2.Add(pair);
}
}
}
// トランザクションを実行する
using (var con = new SQLiteConnection(CONNECTION_STRING)) {
con.Open();
using (var trans = con.BeginTransaction()) {
try {
foreach (var pair in kammusuList2)
{
Console.WriteLine(pair.Key.Name);
// クエリを作成する
string query = string.Format("REPLACE INTO kammusu VALUES ({0},'{1}',{2},{3},{4}",
pair.Key.Id, pair.Key.Name, (int)pair.Key.Type, pair.Key.AntiAir, pair.Key.SlotList.Count);
for (int i = 0; i < pair.Key.SlotList.Count; ++i)
{
query += $",{pair.Key.SlotList[i]}";
}
for (int i = pair.Key.SlotList.Count; i < MAX_SLOT_COUNT; ++i)
{
query += ",0";
}
for (int i = 0; i < pair.Value.Count; ++i)
{
query += $",{pair.Value[i]}";
}
for (int i = pair.Value.Count; i < MAX_SLOT_COUNT; ++i)
{
query += ",0";
}
query += $",{(pair.Key.KammusuFlg ? 1 : 0)});";
// クエリを実行する
using (var cmd = con.CreateCommand()) {
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
} catch (Exception e) {
Console.WriteLine(e);
trans.Rollback();
}
}
}
return;
}
public override string FileToDb(DTDParameter parameter)
{
DataTable dt = ConvertToDataTable(parameter.filename);
int parentId = 0;
BLL.query_list bll_list = new BLL.query_list();
Model.query_list model_list = new Model.query_list();
model_list = bll_list.GetModel(parameter.list_id);
if (model_list.is_import)
{
return("不能重复导入对象表！");
}
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
SQLiteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
object obj;
//如果原来已经导入过数据，则先把原来导入的数据删除。人员对象表与反馈数据有一对多关联，删除会导致数据关联出错。
//if (model_list.feedback_security)
//{
// cmd.CommandText = "delete from query_detail where list_id=@list_id";
// SQLiteParameter para = new SQLiteParameter("@list_id", DbType.Int32, 4);
// para.Value = parameter.list_id;
// cmd.Parameters.Add(para);
// cmd.CommandType = CommandType.Text;//cmdType;
// cmd.ExecuteNonQuery();
// cmd.Parameters.Clear();
//}
for (int i = 1; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
Model.query_detail model = new Model.query_detail();
model.full_name = dr[0].ToString();
model.sex = dr[1].ToString();
model.card_no = dr[2].ToString();
cmd.CommandText = "select code_id from vGetCodeList where code_name='" + dr[3].ToString() + "'";
cmd.CommandType = CommandType.Text;//cmdType;
obj = cmd.ExecuteScalar();
if (obj != null)
{
model.appellation = obj.ToString();
}
else
{
return(model.full_name + "称谓信息不完整");
}
if (dr.IsNull(4) || dr[4].ToString() == "否")
{
model.is_together = false;
}
else
{
model.is_together = true;
}
if (model.appellation == "301" || model.appellation == "302")
{
model.is_together = true;
}
model.parent_id = parentId;
model.list_id = parameter.list_id;
model.add_user = "******";
model.add_time = DateTime.Now;
//查找对应任务表中是否已经有记录，如果有则更新，没有则添加
cmd.CommandText = "select id from query_detail where card_no=@card_no and list_id=@list_id";
SQLiteParameter[] paras =
{
new SQLiteParameter("@card_no", DbType.String, 18),
new SQLiteParameter("@list_id", DbType.Int32, 8)
};
paras[0].Value = model.card_no;
paras[1].Value = model.list_id;
foreach (SQLiteParameter parm in paras)
{
cmd.Parameters.Add(parm);
}
cmd.CommandType = CommandType.Text;//cmdType;
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (obj != null)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update query_detail set ");
strSql.Append("list_id=@list_id,");
strSql.Append("full_name=@full_name,");
strSql.Append("sex=@sex,");
strSql.Append("card_no=@card_no,");
strSql.Append("parent_id=@parent_id,");
strSql.Append("appellation=@appellation,");
strSql.Append("is_together=@is_together,");
strSql.Append("add_user=@add_user,");
strSql.Append("add_time=@add_time");
strSql.Append(" where id=@id");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@list_id", DbType.Int32, 8),
new SQLiteParameter("@full_name", DbType.String, 50),
new SQLiteParameter("@sex", DbType.String, 2),
new SQLiteParameter("@card_no", DbType.String, 18),
new SQLiteParameter("@parent_id", DbType.Int32, 8),
new SQLiteParameter("@appellation", DbType.String, 4),
new SQLiteParameter("@is_together", DbType.Boolean),
new SQLiteParameter("@add_user", DbType.String, 50),
new SQLiteParameter("@add_time", DbType.DateTime),
new SQLiteParameter("@id", DbType.Int32, 8)
};
parameters[0].Value = model.list_id;
parameters[1].Value = model.full_name;
parameters[2].Value = model.sex;
parameters[3].Value = model.card_no;
parameters[4].Value = model.parent_id;
parameters[5].Value = model.appellation;
parameters[6].Value = model.is_together;
parameters[7].Value = model.add_user;
parameters[8].Value = model.add_time;
parameters[9].Value = model.id;
try
{
cmd.CommandText = strSql.ToString();
cmd.CommandType = CommandType.Text;//cmdType;
foreach (SQLiteParameter parm in parameters)
{
cmd.Parameters.Add(parm);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
}
else
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into query_detail(");
strSql.Append("list_id,full_name,sex,card_no,work_unit,post,pol_status,parent_id,appellation,is_together,is_compare,is_match," +
"uncompare_items,add_user,add_time)");
strSql.Append(" values (");
strSql.Append("@list_id,@full_name,@sex,@card_no,@work_unit,@post,@pol_status,@parent_id,@appellation,@is_together,@is_compare,@is_match,@uncompare_items," +
"@add_user,@add_time)");
strSql.Append(";select LAST_INSERT_ROWID()");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@list_id", DbType.Int32, 8),
new SQLiteParameter("@full_name", DbType.String, 50),
new SQLiteParameter("@sex", DbType.String, 2),
new SQLiteParameter("@card_no", DbType.String, 18),
new SQLiteParameter("@work_unit", DbType.String, 255),
new SQLiteParameter("@post", DbType.String, 50),
new SQLiteParameter("@pol_status", DbType.String, 50),
new SQLiteParameter("@parent_id", DbType.Int32, 8),
new SQLiteParameter("@appellation", DbType.String, 4),
new SQLiteParameter("@is_together", DbType.Boolean),
new SQLiteParameter("@is_compare", DbType.Boolean),
new SQLiteParameter("@is_match", DbType.Boolean),
new SQLiteParameter("@uncompare_items", DbType.String, 100),
new SQLiteParameter("@add_user", DbType.String, 50),
new SQLiteParameter("@add_time", DbType.DateTime)
};
parameters[0].Value = model.list_id;
parameters[1].Value = model.full_name;
parameters[2].Value = model.sex;
parameters[3].Value = model.card_no;
parameters[4].Value = model.work_unit;
parameters[5].Value = model.post;
parameters[6].Value = model.pol_status;
parameters[7].Value = model.parent_id;
parameters[8].Value = model.appellation;
parameters[9].Value = model.is_together;
parameters[10].Value = model.is_compare;
parameters[11].Value = model.is_match;
parameters[12].Value = model.uncompare_items;
parameters[13].Value = model.add_user;
parameters[14].Value = model.add_time;
try
{
cmd.CommandText = strSql.ToString();
cmd.CommandType = CommandType.Text;//cmdType;
foreach (SQLiteParameter parm in parameters)
{
cmd.Parameters.Add(parm);
}
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
//如果是本人，则在插入数据后立即将parentId设置为当前插入数据库的信息的id，后面记录只要不出现
//称谓为本人的情况，则其他称谓的记录的parentId都会是该parentId。
if (parentId == 0)
{
parentId = Convert.ToInt32(obj);
}
}
}
tx.Commit();
}
catch (System.Data.SQLite.SQLiteException E)
{
tx.Rollback();
return(E.Message);
}
}
//导入成功后，任务表里的是否导入选项更新为true
model_list.is_import = true;
bll_list.Update(model_list);
return("导入成功！");
}
internal void delete_test(CoTest comtest)
{
try
{
sqliteConn = new SQLiteConnection(connectionString);
sqliteConn.Open();
sqliteTransaction = sqliteConn.BeginTransaction();
sqliteCmd = new SQLiteCommand(sqliteConn);
sqliteCmd.CommandText = "delete from usuario where id=@id";
sqliteCmd.Parameters.Add(new SQLiteParameter("@id")
{
Value = comtest.id
});
sqliteCmd.Transaction = sqliteTransaction;
sqliteCmd.ExecuteNonQuery();
sqliteTransaction.Commit();
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (sqliteTransaction != null)
{
try
{
sqliteTransaction.Rollback();
}
catch (SQLiteException ex2)
{
Console.WriteLine("Transaction rollback failed.");
Console.WriteLine("Error: {0}", ex2.ToString());
}
finally
{
sqliteTransaction.Dispose();
}
}
}
finally
{
if (sqliteCmd != null)
{
sqliteCmd.Dispose();
}
if (sqliteTransaction != null)
{
sqliteTransaction.Dispose();
}
if (sqliteConn != null)
{
try
{
sqliteConn.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine("Closing connection failed.");
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
sqliteConn.Dispose();
}
}
}
}
public void CancelExecuteReader(int milliseconds)
{
using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
conn.Execute(@"create table Test (Id integer primary key);");
using (var trans = conn.BeginTransaction())
{
for (long value = 0; value < 1000; value++)
conn.Execute(@"insert into Test(Id) values(@value)", new { value }, trans);
trans.Commit();
}
CancellationTokenSource source = new CancellationTokenSource(TimeSpan.FromMilliseconds(milliseconds));
using (var cmd = new SQLiteCommand(@"select a.Id, b.Id, c.Id, d.Id from Test a inner join Test b inner join Test c inner join Test d", conn))
using (var reader = cmd.ExecuteReaderAsync(source.Token).Result)
{
Task<bool> task;
do
{
task = reader.ReadAsync(source.Token);
Assert.IsTrue(task.IsCanceled || task.Result);
} while (!task.IsCanceled);
Assert.IsTrue(task.IsCanceled);
}
}
}
public void insert_test(CoTest comtest)
{
try
{
sqliteConn = new SQLiteConnection(connectionString);
sqliteConn.Open();
sqliteTransaction = sqliteConn.BeginTransaction();
sqliteCmd = new SQLiteCommand("INSERT INTO usuario(id,name) VALUES(?,?)", sqliteConn);
sqliteCmd.Parameters.Add(new SQLiteParameter("@id")
{
Value = comtest.id
});
sqliteCmd.Parameters.Add(new SQLiteParameter("@name")
{
Value = comtest.name
});
sqliteCmd.Transaction = sqliteTransaction;
sqliteCmd.ExecuteNonQuery();
sqliteTransaction.Commit();
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (sqliteTransaction != null)
{
try
{
sqliteTransaction.Rollback();
}
catch (SQLiteException ex2)
{
Console.WriteLine("Transaction rollback failed.");
Console.WriteLine("Error: {0}", ex2.ToString());
}
finally
{
sqliteTransaction.Dispose();
}
}
}
finally
{
if (sqliteCmd != null)
{
sqliteCmd.Dispose();
}
if (sqliteTransaction != null)
{
sqliteTransaction.Dispose();
}
if (sqliteConn != null)
{
try
{
sqliteConn.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine("Closing connection failed.");
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
sqliteConn.Dispose();
}
}
}
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try {
if (File.Exists(file))
{
using (SQLiteConnection cn = new SQLiteConnection()) {
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction()) {
bool?NoCacheTimeColumn;
try {
using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn)) {
cmd.Transaction = tr;
using (DbDataReader rd = cmd.ExecuteReader()) {
rd.Close();
}
NoCacheTimeColumn = false;
}
} catch (Exception ex) {
if (ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try {
if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using (DbCommand cmd = cn.CreateCommand()) {
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
} catch (Exception exx) {
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx);
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
} catch (Exception ex) {
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex);
ret = false;
}
return(ret);
}
internal DataTable trae_registro()
{
DataSet ds = new DataSet();
try
{
sqliteConn = new SQLiteConnection(connectionString);
sqliteConn.Open();
sqliteTransaction = sqliteConn.BeginTransaction();
sqliteCmd = new SQLiteCommand(sqliteConn);
sqliteCmd.CommandText = "select * from usuario";
sqliteadapter = new SQLiteDataAdapter(sqliteCmd);
ds = new DataSet();
sqliteadapter.Fill(ds);
sqliteTransaction.Commit();
}
catch (SQLiteException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
if (sqliteTransaction != null)
{
try
{
sqliteTransaction.Rollback();
}
catch (SQLiteException ex2)
{
Console.WriteLine("Transaction rollback failed.");
Console.WriteLine("Error: {0}", ex2.ToString());
}
finally
{
sqliteTransaction.Dispose();
}
}
}
finally
{
if (sqliteCmd != null)
{
sqliteCmd.Dispose();
}
if (sqliteTransaction != null)
{
sqliteTransaction.Dispose();
}
if (sqliteConn != null)
{
try
{
sqliteConn.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine("Closing connection failed.");
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
sqliteConn.Dispose();
}
}
}
return(ds.Tables[0]);
}
public void Sync_CopyFrom(string tableName, List <DatabaseColumn> columns, DbDataReader syncReader,
SyncLogMessageCallback onMessage)
{
tableName = tableName.MakeFullyQualifiedTableName();
SQLiteConnection connection = GetConnectionForTable(tableName);
SQLiteCommand cmd = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} (", tableName);
for (int i = 0; i < columns.Count; i++)
{
sb.AppendFormat("{0}", columns[i].ColumnName);
if (i != columns.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(") VALUES (");
for (int i = 0; i < columns.Count; i++)
{
sb.AppendFormat("{0}", columns[i].ParameterName);
cmd.Parameters.Add(columns[i].ParameterName, columns[i].DbType);
if (i != columns.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(")");
cmd.CommandText = sb.ToString();
SQLiteTransaction transaction = connection.BeginTransaction();
DatabaseColumn idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("id"));
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("screenid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("uid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("scalerid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("char_id"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("serial"));
}
int successful = 0;
while (syncReader.Read())
{
for (int i = 0; i < columns.Count; i++)
{
DatabaseColumn currentColumn = columns[i];
if (currentColumn.DbType == DbType.Time)
{
TimeSpan copyTimespan = (TimeSpan)syncReader.GetValue(i);
DateTime copyTimespan2 = DateTime.MinValue;
copyTimespan2 += copyTimespan;
cmd.Parameters[currentColumn.ParameterName].Value = copyTimespan2;
}
else if (currentColumn.DbType == DbType.Binary)
{
cmd.Parameters[currentColumn.ParameterName].Value = DBNull.Value;
byte[] inBuffer = syncReader.GetByteArray(i);
if (inBuffer != null && inBuffer.Length > 0)
{
cmd.Parameters[currentColumn.ParameterName].Value = inBuffer;
}
}
else
{
object copyObject = syncReader.GetValue(i);
cmd.Parameters[currentColumn.ParameterName].Value = copyObject;
}
}
int result = cmd.ExecuteNonQuery();
if (result == 0)
{
throw new SyncException("insert failed");
}
if (successful++ % 100 == 0)
{
onMessage.Invoke(String.Format("{0} inserts into {1}", successful, tableName));
}
}
syncReader.Close();
syncReader.Dispose();
transaction.Commit();
transaction.Dispose();
}
public int ExportMemUsage2CSV2(string sFileCSV, string strIP)
{
//pause data read thread (socksrv)?
sql_cmd = new SQLiteCommand();
sql_con = new SQLiteConnection();
connectDB();
if (sql_con.State != ConnectionState.Open)
{
sql_con.Close();
sql_con.Open();
}
sql_cmd = sql_con.CreateCommand();
int iCnt = 0;
sql_cmd.CommandText = "select * from vmUsage";
SQLiteDataReader rdr = null;
OnNewMessage("Creating new table with process names...");
//although exporting data in normal format is not to bad
//better export by using a different layout
// time \ nameX nameY
// 00:00 memuseX memuseY
// 00:01 memuseX memuseY
// ...
// so we need a list of unique names excluding 'Slot x: empty' values
List <string> lNames = new List <string>();
sql_cmd.CommandText = "select distinct [Name] from [VMUsage] where [Name] not like 'Slot %' ORDER BY [Name];";
try
{
rdr = sql_cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read())
{
//do not save duplicate field names!
if (!lNames.Contains(rdr["Name"].ToString().ToLower()))
{
lNames.Add(rdr["Name"].ToString().ToLower());
}
}
}
catch (Exception ex)
{
MessageBox.Show("Exception in 'select distinct...': " + ex.Message);
}
finally
{
rdr.Close();
}
if (lNames.Count == 0)
{
OnNewMessage("No names found");
goto exit_cvs2;
}
OnNewMessage("... found " + lNames.Count.ToString() + "processes");
string tempTableName = "[VMusageTEMP]";
//create a table with the names as fields plus a field for the time
long lRes = executeNonQuery("DROP TABLE IF EXISTS " + tempTableName + ";");
// Define the SQL Create table statement, IF NOT EXISTS
string createAppUserTableSQL = "CREATE TABLE IF NOT EXISTS " + tempTableName + " (" +
"[Time] INTEGER NOT NULL, ";
//add the fields with names
foreach (string sFieldName in lNames)
{
createAppUserTableSQL += "[" + sFieldName + "] INTEGER DEFAULT 0, ";
}
//add RemoteIP
createAppUserTableSQL += "[RemoteIP] TEXT DEFAULT '', ";
//add idx field
createAppUserTableSQL += "[idx] INTEGER PRIMARY KEY AUTOINCREMENT )";
//create temp Table
lRes = executeNonQuery(createAppUserTableSQL);
//add index
string SqlIndex = "CREATE INDEX IF NOT EXISTS [Time] on VMUsage (Name ASC);";
lRes = executeNonQuery(SqlIndex);
//### get all distinct times
List <ulong> lTimes = new List <ulong>();
sql_cmd.CommandText = "Select DISTINCT Time from [VMusage] order by Time;";
rdr = sql_cmd.ExecuteReader();
OnNewMessage("creating time table...");
while (rdr.Read())
{
lTimes.Add(Convert.ToUInt64(rdr["Time"]));
Application.DoEvents();
}
rdr.Close();
//### get all process, memusage data
List <MEMORY_USAGE_IP> lMemoryUsages = new List <MEMORY_USAGE_IP>();
if (strIP != "")
{
sql_cmd.CommandText = "Select RemoteIP, Name, MemUsage, Time from VMusage WHERE [RemoteIP]='" + strIP + "' order by Time";
}
else
{
sql_cmd.CommandText = "Select RemoteIP, Name, MemUsage, Time from VMusage order by Time";
}
rdr = sql_cmd.ExecuteReader();
while (rdr.Read())
{
string sIP = (string)rdr["RemoteIP"];
string sName = (string)rdr["Name"];
int iMemUsage = Convert.ToInt32(rdr["MemUsage"]);
ulong uTI = Convert.ToUInt64(rdr["Time"]);
lMemoryUsages.Add(new MEMORY_USAGE_IP(sIP, sName, iMemUsage, uTI));
Application.DoEvents();
}
rdr.Close();
OnNewMessage("created temp table");
//now iterate thru all times and get the names and memuse values
string sUpdateCommand = "";
SQLiteTransaction tr = sql_con.BeginTransaction();
//sql_cmd.CommandText = "insert into [ProcUsage] (Time, [device.exe]) SELECT Time, User from [Processes] WHERE Time=631771077815940000 AND Process='device.exe';";
int lCnt = 0;
OnNewMessage("processing " + lTimes.Count.ToString() + " time entries...");
foreach (ulong uTime in lTimes)
{
System.Diagnostics.Debug.WriteLine("Updating for Time=" + uTime.ToString());
OnNewMessage("Updating for Time=" + uTime.ToString());
//insert an empty row
sql_cmd.CommandText = "Insert Into " + tempTableName + " (RemoteIP, Time) VALUES('0.0.0.0', " + uTime.ToString() + ");";
lCnt = sql_cmd.ExecuteNonQuery();
foreach (string sPName in lNames)
{
OnNewMessage("processing " + sPName);
Application.DoEvents();
//is there already a line?
//lCnt = executeNonQuery("Select Time " + "From ProcUsage Where Time="+uTime.ToString());
// http://stackoverflow.com/questions/4495698/c-sharp-using-listt-find-with-custom-objects
MEMORY_USAGE_IP pm = lMemoryUsages.Find(x => x.procname == sPName && x.timestamp == uTime);
if (pm != null)
{
System.Diagnostics.Debug.WriteLine("\tUpdating Memory=" + pm.memusage + " for Process=" + sPName);
//update values
sUpdateCommand = "Update " + tempTableName + " SET " +
"[" + sPName + "]=" + pm.memusage +
", [RemoteIP]='" + pm.sRemoteIP + "'" +
//"(SELECT User from [Processes]
" WHERE Time=" + uTime.ToString() + //" AND Process=" + "'" + sPro + "'"+
";";
sql_cmd.CommandText = sUpdateCommand;
//System.Diagnostics.Debug.WriteLine(sUpdateCommand);
try
{
lCnt = sql_cmd.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
System.Diagnostics.Debug.WriteLine("export2CSV2()-SQLiteException: " + ex.Message + " for " + sUpdateCommand);
MessageBox.Show("export2CSV2()-SQLiteException: " + ex.Message + " for " + sUpdateCommand);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("export2CSV2()-Exception: " + ex.Message + " for " + sUpdateCommand);
MessageBox.Show("export2CSV2()-Exception: " + ex.Message + " for " + sUpdateCommand);
}
//lCnt = executeNonQuery(sInsertCommand);
//"insert into [ProcUsage] (Time, [device.exe]) SELECT Time, User from [Processes] WHERE Time=631771077815940000 AND Process='device.exe';"
}
}
}
tr.Commit();
OnNewMessage("table update done");
//export the table to CSV
lCnt = 0;
rdr = null;
OnNewMessage("exporting rotated table...");
System.IO.StreamWriter sw = null;
try
{
sw = new System.IO.StreamWriter(sFileCSV);
//sw.WriteLine("RemoteIP;" + strIP);
string sFields = "";
List <string> lFields = new List <string>();
lFields.Add("RemoteIP");
lFields.Add("Time");
lFields.AddRange(lNames);
foreach (string ft in lFields)
{
sFields += ("'" + ft + "'" + ";");
}
sFields.TrimEnd(new char[] { ';' });
sw.Write(sFields + "\r\n");
sql_cmd.CommandText = "Select * from " + tempTableName + ";";
rdr = sql_cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read())
{
Application.DoEvents();
lCnt++;
sFields = "";
//Console.WriteLine(rdr["ProcID"] + " " + rdr["User"]);
foreach (string ft in lFields)
{
sFields += rdr[ft] + ";";
}
sFields.TrimEnd(new char[] { ';' });
sw.Write(sFields + "\r\n");
sw.Flush();
}
}
catch (Exception ex) {
MessageBox.Show("Exception in Select * from " + tempTableName + "; " + ex.Message);
}
finally
{
sw.Close();
rdr.Close();
}
OnNewMessage("export done");
exit_cvs2:
sql_con.Close();
return(iCnt);
}
/// <summary>将 DataTable 的数据批量插入到数据库中。
/// 将 DataTable 的数据批量插入到数据库中。
/// </summary>
/// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>
/// <param name="batchSize">每批次写入的数据量。</param>
public void BatchInsert(DataTable dataTable, int batchSize = 10000)
{
if (dataTable == null || dataTable.Rows.Count == 0)
{
return;
}
DbTransaction transcation = null;
try
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
transcation = _conn.BeginTransaction();
using (var command = new SQLiteCommand(_conn))
{
command.CommandText = GenerateInserSql(dataTable);
if (command.CommandText == string.Empty)
{
return;
}
List <string> lstName = new List <string>();
List <string> lstAutoName = new List <string>();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i].AutoIncrement)
{
lstAutoName.Add(dataTable.Columns[i].ToString());
}
else
{
lstName.Add(dataTable.Columns[i].ColumnName);
}
}
foreach (string t in lstAutoName)
{
dataTable.Columns.Remove(t);
}
SQLiteParameter[] paras = new SQLiteParameter[lstName.Count];
for (int i = 0; i < paras.Length; i++)
{
paras[i] = command.CreateParameter();
paras[i].ParameterName = lstName[i];
}
command.Parameters.AddRange(paras);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
foreach (SQLiteParameter t in paras)
{
t.Value = dataTable.Rows[i][t.ParameterName];
}
command.ExecuteNonQuery();
}
}
transcation.Commit();
}
catch (Exception)
{
if (transcation != null)
{
transcation.Rollback();
}
throw;
}
finally
{
if (_conn.State == ConnectionState.Open)
{
_conn.Close();
}
}
}
public bool Add2DB(List <SystemClass> eddbsystems, List <StationClass> eddbstations)
{
SQLiteDBClass db = new SQLiteDBClass();
db.Connect2DB();
Stopwatch sw = new Stopwatch();
sw.Start();
using (SQLiteConnection cn = new SQLiteConnection(SQLiteDBClass.ConnectionString))
{
cn.Open();
int nr = 0;
using (var tra = cn.BeginTransaction())
{
try
{
foreach (SystemClass sys in eddbsystems)
{
SystemClass sysdb = SystemData.GetSystem(sys.name);
if (sysdb != null) // Update system
{
if (sysdb.eddb_updated_at != sys.eddb_updated_at || sysdb.population != sys.population)
{
sysdb.id_eddb = sys.id_eddb;
sysdb.faction = sys.faction;
sysdb.population = sys.population;
sysdb.government = sys.government;
sysdb.allegiance = sys.allegiance;
sysdb.state = sys.state;
sysdb.security = sys.security;
sysdb.primary_economy = sys.primary_economy;
sysdb.needs_permit = sys.needs_permit;
sysdb.eddb_updated_at = sys.eddb_updated_at;
sysdb.Update(cn, sysdb.id, tra);
nr++;
}
sysdb = null;
}
else
{
System.Diagnostics.Trace.WriteLine("New system " + sys.name);
sys.Store(cn, tra);
}
}
System.Diagnostics.Trace.WriteLine("Add2DB " + nr.ToString() + " eddb systems: " + sw.Elapsed.TotalSeconds.ToString("0.000s"));
tra.Commit();
sw.Stop();
System.Diagnostics.Trace.WriteLine("Add2DB " + nr.ToString() + " eddb systems: " + sw.Elapsed.TotalSeconds.ToString("0.000s"));
}
catch (Exception ex)
{
tra.Rollback();
System.Diagnostics.Trace.WriteLine("Add2DB error: {0}" + ex.Message);
throw;
}
}
}
return(true);
}
/// <summary>
/// 打包瓦片
/// </summary>
private void toolStripButtonPack_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "瓦片集合|*.mbtile|所有文件|*.*";
if (dialog.ShowDialog(this) != DialogResult.OK)
{
return;
}
//
string mbtiles = dialog.FileName;
SQLiteHelper.SetConnectionString = string.Format("Data Source=\"{0}\"", mbtiles);
InitDB();
// 必须使用事务 否则太慢了
SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source=\"{0}\"", mbtiles));
connection.Open();
SQLiteTransaction transaction = connection.BeginTransaction();
//
int num = 0;
foreach (var item in listBoxFiles.Items)
{
if (num % 100 == 0)
{
toolStripStatusLabelStatus.Text = string.Format("[{0}/{1}] {2:##}%", num, listBoxFiles.Items.Count, 100.0 * num / listBoxFiles.Items.Count);
Application.DoEvents();
}
num++;
string filename = Convert.ToString(item);
double[] colrowzoom = GetColRowZoom(filename);
if (colrowzoom == null || colrowzoom.Length != 3)
{
continue;
}
// 插入数据库
byte[] data = File.ReadAllBytes(filename);
string sql = @"
replace into TILES(TILE_COLUMN, TILE_ROW, ZOOM_LEVEL, TILE_DATA)
values(@TILE_COLUMN, @TILE_ROW, @ZOOM_LEVEL, @TILE_DATA)";
SQLiteParameter[] args = new SQLiteParameter[4];
args[0] = new SQLiteParameter("@TILE_COLUMN", colrowzoom[0]);
args[1] = new SQLiteParameter("@TILE_ROW", colrowzoom[1]);
args[2] = new SQLiteParameter("@ZOOM_LEVEL", colrowzoom[2]);
args[3] = new SQLiteParameter("@TILE_DATA", data);
//
int result = SQLiteHelper.ExecuteNonQuery(transaction, CommandType.Text, sql, args);
}
transaction.Commit();
connection.Close();
toolStripStatusLabelStatus.Text = "就绪";
MessageBox.Show("完成", "数据打包", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public SQLiteTransaction BeginTransaction()
{
return(Conn.BeginTransaction());
}
/// <summary>
/// Creates a new <see cref="SQLiteTransaction"/> if one isn't already active on the connection.
/// <remarks>
/// Unspecified will use the default isolation level specified in the connection string.
/// If no isolation level is specified in the connection string, Serializable is used.
/// Serializable transactions are the default. In this mode, the engine gets an immediate
/// lock on the database, and no other threads may begin a transaction. Other threads may
/// read from the database, but not write. With a ReadCommitted isolation level, locks are
/// deferred and elevated as needed. It is possible for multiple threads to start a transaction
/// in ReadCommitted mode, but if a thread attempts to commit a transaction while another thread
/// has a ReadCommitted lock, it may timeout or cause a deadlock on both threads until both threads'
/// CommandTimeout's are reached.
/// </remarks>
/// </summary>
/// <param name="isolationLevel">Supported isolation levels are Serializable, ReadCommitted and Unspecified.</param>
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return(Connection.BeginTransaction(isolationLevel));
}
private void CreateDB()
{
//create and write the sqlite db file from the csv file just written.
//read it into a datatable
DataTable dt = ReadDBCSVFile("db.csv");
string dbFile = "CropChemApplicationV2.s3db";
if (File.Exists(dbFile))
{
File.Delete(dbFile);
}
//DirectoryInfo di = Directory.GetParent(dbFile);
//if (!Directory.Exists(di.FullName))
// Directory.CreateDirectory(di.FullName);
SQLiteConnection.CreateFile(dbFile);
SQLiteConnection dbConnection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbFile));
dbConnection.Open();
//Create sqlite table
IDbTransaction transaction1 = dbConnection.BeginTransaction();
string sql = GetCreateTableSQL();
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
transaction1.Commit();
//Insert records into table
transaction1 = dbConnection.BeginTransaction();
for (int i = 0; i < dt.Rows.Count; i++)
{
StringBuilder sbColNames = new StringBuilder("insert into CCA (");
StringBuilder sbValues = new StringBuilder(" values (");
int count = 0;
foreach (DataColumn dc in dt.Columns)
{
if (count > 0)
{
sbColNames.Append(", ");
sbValues.Append(", ");
}
sbColNames.Append("[" + dc.ColumnName + "]");
sbValues.Append("'" + dt.Rows[i][dc].ToString() + "'");
count++;
}
sbColNames.Append(")");
sbValues.Append(")");
sql = sbColNames.ToString() + sbValues.ToString();
command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
}
transaction1.Commit();
MessageBox.Show("Done", "AttaBoy", MessageBoxButtons.OK);
}
public string SaveConfigurationWithOverwrite(string name)
{
try
{
using (SQLiteTransaction transaction = _conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(_conn))
{
//if configuration name already exists, delete configuration and associated services first
//delete services
cmd.CommandText = "DELETE FROM " + CONST_strTableNameServices + " WHERE configuration='" + name + "'";
cmd.ExecuteNonQuery();
//delete configuration
cmd.CommandText = "DELETE FROM " + CONST_strTableNameConfigurations + " WHERE name='" + name + "'";
cmd.ExecuteNonQuery();
//delete ArcGISDynamicMapService params
cmd.CommandText = "DELETE FROM " + CONST_strTableNameAgsDynamicMapServiceParams + " WHERE configuration='" + name + "'";
cmd.ExecuteNonQuery();
//save configuration
cmd.CommandText = "INSERT INTO " + CONST_strTableNameConfigurations + " VALUES (@name,@servicecount,@created)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@servicecount", ServerManager.ServicesInServer.Count);
cmd.Parameters.AddWithValue("@created", DateTime.Now);
cmd.ExecuteNonQuery();
foreach (GISServiceEntity service in ServerManager.ServicesInServer)
{
//save services
cmd.CommandText = "INSERT INTO " + CONST_strTableNameServices + " VALUES (@configuration,@name,@port,@type,@tilingschemepath,@using3857,@datasourcepath,@allowmemcache,@disableclientcache,@displaynodatatile,@visualstyle)";
cmd.Parameters.AddWithValue("@configuration", name);
cmd.Parameters.AddWithValue("@name", service.ServiceName);
cmd.Parameters.AddWithValue("@port", service.Port);
cmd.Parameters.AddWithValue("@type", service.DataSource.Type.ToString());
cmd.Parameters.AddWithValue("@tilingschemepath", service.DataSource.TilingScheme.Path);
cmd.Parameters.AddWithValue("@using3857", service.DataSource.TilingScheme.WKID == 102100 || service.DataSource.TilingScheme.WKID == 3857 ? true : false);
cmd.Parameters.AddWithValue("@datasourcepath", service.DataSource.Path);
cmd.Parameters.AddWithValue("@allowmemcache", service.AllowMemCache);
cmd.Parameters.AddWithValue("@disableclientcache", service.DisableClientCache);
cmd.Parameters.AddWithValue("@displaynodatatile", service.DisplayNoDataTile);
cmd.Parameters.AddWithValue("@visualstyle", service.Style.ToString());
cmd.ExecuteNonQuery();
//if is ArcGISDynamicMapService, save additional params.
if (service.DataSource.Type == sara.gisserver.console.gis.datasource.DataSourceTypePredefined.ArcGISDynamicMapService.ToString())
{
cmd.CommandText = "INSERT INTO " + CONST_strTableNameAgsDynamicMapServiceParams + " VALUES (@configuration,@name,@layers,@layerDefs,@time,@layerTimeOptions)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@configuration", name);
cmd.Parameters.AddWithValue("@name", service.ServiceName);
cmd.Parameters.AddWithValue("@layers", (service.DataSource as DataSourceArcGISDynamicMapService).exportParam_layers);
cmd.Parameters.AddWithValue("@layerDefs", (service.DataSource as DataSourceArcGISDynamicMapService).exportParam_layerDefs);
cmd.Parameters.AddWithValue("@time", (service.DataSource as DataSourceArcGISDynamicMapService).exportParam_time);
cmd.Parameters.AddWithValue("@layerTimeOptions", (service.DataSource as DataSourceArcGISDynamicMapService).exportParam_layerTimeOptions);
cmd.ExecuteNonQuery();
}
}
}
transaction.Commit();
}
_configurations.Add(new ConfigurationEntity(name, ServerManager.ServicesInServer.Count, DateTime.Now));
return(string.Empty);
}
catch (Exception e)
{
return(e.Message);
}
}
public static void InsertData(string databasePath, string databaseName, DataTable dt)
{
if (dt != null)
{
if (!databasePath.EndsWith(@"\"))
{
databasePath += @"\";
}
try
{
IEnumerable <ColumnDefinition> columnDefs = ColumnDefinition.GetColumnDefinitions(dt);
if (columnDefs == null || !columnDefs.Any())
{
throw new DataException("No column definitions extrapolated from data.");
}
string connectionString = "Data Source=" + databasePath + databaseName + ".db;Version=3;";
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
try
{
conn.Open();
CreateTable(conn, dt, columnDefs);
if (dt.Rows.Count > 0)
{
using (SQLiteTransaction trans = conn.BeginTransaction())
{
try
{
foreach (DataRow row in dt.Rows)
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GenerateInsertStatement(dt, columnDefs);
foreach (ColumnDefinition kvp in columnDefs)
{
cmd.Parameters.Add(new SQLiteParameter(kvp.ParameterName, row[kvp.ColumnName] ?? DBNull.Value));
}
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
GC.Collect();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public async Task AddLang(object parameter)
{
try
{
Status = "";
await Task.Factory.StartNew(async() =>
{
AddLangIsEnabled = false;
using (var conn = new SQLiteConnection(Connection.ConnString))
{
conn.Open();
using (var trns = conn.BeginTransaction())
{
AddLangIsEnabled = false;
using (var comm = new SQLiteCommand(conn))
{
comm.CommandText = "INSERT INTO code_lib_langs (userid, title, desc, desc_prev) values(?,?,?,?)";
comm.Parameters.AddWithValue("@0", User.GetUserId());
comm.Parameters.AddWithValue("@1", (string)parameter);
comm.Parameters.AddWithValue("@2",
!string.IsNullOrEmpty(AddLangDesc) && AddLangDesc.Trim() !=
"Bu kısma kod bloğu hakkında küçük bir not veya URL adersi eklenebilir."
? AddLangDesc
: string.Empty);
comm.Parameters.AddWithValue("@3",
!string.IsNullOrEmpty(AddLangDesc) && AddLangDesc.Trim() !=
"Bu kısma kod bloğu hakkında küçük bir not veya URL adersi eklenebilir."
? (AddLangDesc.Length >= 200 ? AddLangDesc.Substring(0, 200) : AddLangDesc)
: string.Empty);
var rows = comm.ExecuteNonQuery();
if (rows > 0)
{
var rowid = conn.LastInsertRowId;
if (CodeLangs.Any(Lang => Lang.RowId != rowid))
{
//if (CodeLangs.Any(Lang => Lang.RowId != rowid))
//{
// CodeLangs.Add(new CodeLangs()
// {
// RowId = conn.LastInsertRowId,
// Title = (string)parameter,
// Desc = !string.IsNullOrEmpty(AddLangDesc) && AddLangDesc !=
// "Bu kısma kod bloğu hakkında küçük bir not veya URL adersi eklenebilir."
// ? AddLangDesc
// : string.Empty,
// DescPrev =
// !string.IsNullOrEmpty(AddLangDesc) && AddLangDesc !=
// "Bu kısma kod bloğu hakkında küçük bir not veya URL adersi eklenebilir."
// ? (AddLangDesc.Length >= 200
// ? AddLangDesc.Substring(0, 200)
// : AddLangDesc)
// : string.Empty
// });
//}
var newitem = new CodeLangs()
{
RowId = conn.LastInsertRowId,
Title = (string)parameter,
DescPrev =
!string.IsNullOrEmpty(AddLangDesc) && AddLangDesc !=
"Bu kısma kod bloğu hakkında küçük bir not veya URL adersi eklenebilir."
? (AddLangDesc.Length >= 200
? AddLangDesc.Substring(0, 200)
: AddLangDesc)
: string.Empty
};
await Task.Delay(1000);
await Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(
() =>
{
CodeLangs.Add(newitem);
}));
SelectedLang = newitem;
}
}
}
trns.Commit();
}
conn.Close();
}
await Task.Delay(1000);
AddLangIsEnabled = true;
});
}
catch (Exception exp)
{
AddLangIsEnabled = true;
Debug.WriteLine(exp.ToString());
}
}
public static void UpdateMessages(DataSet dataSet)
{
if (dataSet == null)
{
return;
}
try
{
using (var connection = new SQLiteConnection(LoadConnectionString()))
{
connection.Open();
var dataTable = dataSet.Tables[0];
var command = connection.CreateCommand();
// command.CommandText = "SELECT * FROM Message";
// var adapter = new SQLiteDataAdapter(command);
// new SQLiteCommandBuilder(adapter);
// adapter.Update(dataTable);
using (var transaction = connection.BeginTransaction())
{
foreach (DataRow row in dataTable.Rows)
{
long id = (long)row["id"];
if (id == 0)
{
command.CommandText =
$"insert into Message (testDone, testDoneTime, position, messageText, comment, " +
$"messageNumber, projectId) values (" +
$"'{row["testDone"]}', " +
$"'{row["testDoneTime"]}', " +
$"'{row["position"]}', " +
$"'{row["messageText"]}', " +
$"'{row["comment"]}', " +
$"'{row["messageNumber"]}', " +
$"'{row["projectId"]}'" +
$");";
}
else
{
command.CommandText =
$"update Message set " +
$"testDone = '{row["testDone"]}', " +
$"testDoneTime = '{row["testDoneTime"]}', " +
$"position = '{row["position"]}', " +
$"messageText = '{row["messageText"]}', " +
$"comment = '{row["comment"]}', " +
$"messageNumber = '{row["messageNumber"]}', " +
$"projectId = '{row["projectId"]}' " +
$"where id = {row["id"]};";
Log.Debug("CommandText := " + command.CommandText);
}
command.ExecuteNonQuery();
}
transaction.Commit();
}
connection.Close();
}
}
catch (Exception exception)
{
Log.Error(exception.Message);
Log.Debug(exception.StackTrace);
MessageBox.Show(exception.Message);
}
}
private async void AddArtworkInBackgroundAsync()
{
LogClient.Info("+++ STARTED ADDING ARTWORK IN THE BACKGROUND +++");
this.canIndexArtwork = true;
this.isIndexingArtwork = true;
long numberAdded = 0;
DateTime startTime = DateTime.Now;
await Task.Run(async() =>
{
using (SQLiteConnection conn = this.factory.GetConnection())
{
try
{
conn.BeginTransaction();
List <Album> albumsToCheck = conn.Table <Album>().ToList().Where(a => string.IsNullOrEmpty(a.ArtworkID)).ToList();
List <long> albumIds = new List <long>();
List <Album> albumsToIndex = conn.Table <Album>().ToList().Where(a => a.NeedsIndexing == 1).ToList();
foreach (Album alb in albumsToIndex)
{
if (!this.canIndexArtwork)
{
try
{
LogClient.Info("+++ ABORTED ADDING ARTWORK IN THE BACKGROUND. Time required: {0} ms +++", Convert.ToInt64(DateTime.Now.Subtract(startTime).TotalMilliseconds));
conn.Commit(); // Makes sure we commit what we already processed
this.AlbumArtworkAdded(this, new AlbumArtworkAddedEventArgs()
{
AlbumIds = albumIds
}); // Update UI
}
catch (Exception ex)
{
LogClient.Error("Failed to commit changes while aborting adding artwork in background. Exception: {0}", ex.Message);
}
this.isIndexingArtwork = false;
return;
}
try
{
Track trk = this.GetLastModifiedTrack(alb);
alb.ArtworkID = await this.cacheService.CacheArtworkAsync(IndexerUtils.GetArtwork(alb, trk.Path));
if (!string.IsNullOrEmpty(alb.ArtworkID))
{
albumIds.Add(alb.AlbumID);
alb.DateLastSynced = DateTime.Now.Ticks;
conn.Update(alb);
numberAdded += 1;
if (albumIds.Count >= 20)
{
conn.Commit();
List <long> eventAlbumIds = new List <long>(albumIds);
albumIds.Clear();
this.AlbumArtworkAdded(this, new AlbumArtworkAddedEventArgs()
{
AlbumIds = eventAlbumIds
}); // Update UI
}
}
}
catch (Exception ex)
{
LogClient.Error("There was a problem while updating the cover art for Album {0}/{1}. Exception: {2}", alb.AlbumTitle, alb.AlbumArtist, ex.Message);
}
try
{
alb.NeedsIndexing = 0;
conn.Update(alb);
}
catch (Exception ex)
{
LogClient.Error("There was a problem while updating the cover art for Album {0}/{1}. Exception: {2}", alb.AlbumTitle, alb.AlbumArtist, ex.Message);
}
}
try
{
conn.Commit();
this.AlbumArtworkAdded(this, new AlbumArtworkAddedEventArgs()
{
AlbumIds = albumIds
}); // Update UI
}
catch (Exception ex)
{
LogClient.Error("Failed to commit changes while finishing adding artwork in background. Exception: {0}", ex.Message);
}
}
catch (Exception ex)
{
LogClient.Error("Unexpected error occurred while updating artwork in the background. Exception: {0}", ex.Message);
}
}
});
this.isIndexingArtwork = false;
LogClient.Error("+++ FINISHED ADDING ARTWORK IN THE BACKGROUND. Time required: {0} ms +++", Convert.ToInt64(DateTime.Now.Subtract(startTime).TotalMilliseconds));
}
bool CombineMap(string sourcefile, string destfile, IProgress <double> progress, CancellationTokenSource cancelTokenSource)
{
bool bRtn = true;
SQLiteConnection sqlSource = new SQLiteConnection("Data Source=" + sourcefile + ";Version=3;");
sqlSource.Open();
using (SQLiteCommand cmdSource = new SQLiteCommand(sqlSource))
{
// 开启一个新事务，加快操作速度。如果操作还是慢，需要为表 map 的 tile_id 列创建主键。
SQLiteTransaction transSource = sqlSource.BeginTransaction();
cmdSource.Transaction = transSource;
int recTotal = 0;
cmdSource.CommandText = "SELECT COUNT(*) FROM map";
using (SQLiteDataReader readerSource = cmdSource.ExecuteReader())
{
if (readerSource.Read())
{
this.Dispatcher.Invoke(new Action(() => lblTotalRec.Content = recTotal = readerSource.GetInt32(0)));
}
}
cmdSource.CommandText = "SELECT * FROM map";
using (SQLiteDataReader readerSource = cmdSource.ExecuteReader())
{
SQLiteConnection sqlDest = new SQLiteConnection("Data Source=" + destfile + ";Version=3;");
sqlDest.Open();
using (SQLiteCommand cmdDest = new SQLiteCommand(sqlDest))
{
SQLiteTransaction transDest = sqlDest.BeginTransaction();
cmdDest.Transaction = transDest;
int recCnt = 1;
while (readerSource.Read())
{
string tile_id = (string)readerSource["tile_id"];
cmdDest.CommandText = "SELECT tile_id FROM map WHERE tile_id = '" + tile_id + "'";
using (SQLiteDataReader readerDest = cmdDest.ExecuteReader())
{
if (!readerDest.Read())
{
long zoom_level = (long)readerSource["zoom_level"];
long tile_column = (long)readerSource["tile_column"];
long tile_row = (long)readerSource["tile_row"];
readerDest.Close();
cmdDest.CommandText = "INSERT INTO map VALUES(@zoom_level, @tile_column, @tile_row, @tile_id)";
SQLiteParameter[] sqlParams =
{
new SQLiteParameter("@zoom_level", DbType.Int64),
new SQLiteParameter("@tile_column", DbType.Int64),
new SQLiteParameter("@tile_row", DbType.Int64),
new SQLiteParameter("@tile_id", DbType.String)
};
sqlParams[0].Value = zoom_level;
sqlParams[1].Value = tile_column;
sqlParams[2].Value = tile_row;
sqlParams[3].Value = tile_id;
cmdDest.Parameters.AddRange(sqlParams);
cmdDest.ExecuteNonQuery();
}
this.Dispatcher.Invoke(new Action(() => lblCurrentRec.Content = recCnt++));
progress.Report((double)recCnt * 100.0 / (double)recTotal);
if (cancelTokenSource.IsCancellationRequested)
{
bRtn = false;
break;
}
}
}
transDest.Commit();
sqlDest.Close();
}
}
transSource.Commit();
}
sqlSource.Close();
return(bRtn);
}
private void UpdateScripture(SQLiteConnection updConn, bool ot)
#endif
{
var books = _vrs.SelectNodes("//bk");
Debug.Assert(books != null);
for (var bkn = ot ? 0 : 39; bkn < 66; )
{
var bk = books[bkn];
bkn += 1;
var matches = _vrsPat.Matches(bk.InnerText);
for (var chn = 1; chn <= matches.Count; chn++)
{
var trx = updConn.BeginTransaction();
var vrsCount = int.Parse(matches[chn - 1].Groups[1].Value);
for (var vrsn = 1; vrsn <= vrsCount; vrsn++)
{
var line = _sr.ReadLine();
Debug.Assert(line != null);
var cmd = updConn.CreateCommand();
line = line.Replace("\"", "\"\"");
cmd.CommandText = string.Format(@"update Bible SET Scripture = ""{0}"" WHERE Book = {1} AND Chapter = {2} AND Verse = {3};", line, bkn, chn, vrsn);
int result = cmd.ExecuteNonQuery();
Debug.Assert(result == 1);
}
trx.Commit();
}
}
}
bool CalcWriteMap(string sourcefile, string destfile, IProgress <double> progress, CancellationTokenSource cancelTokenSource)
{
bool bRtn = true;
SQLiteConnection sqlDest = new SQLiteConnection("Data Source=" + destfile + ";Version=3;");
sqlDest.Open();
SQLiteCommand cmdDest = new SQLiteCommand("SELECT tile_id FROM images", sqlDest);
SQLiteTransaction transDest = sqlDest.BeginTransaction();
cmdDest.Transaction = transDest;
SQLiteDataReader readerDest = cmdDest.ExecuteReader();
int recTotal = 0;
List <string> tile_ids = new List <string>();
while (readerDest.Read())
{
tile_ids.Add((string)readerDest["tile_id"]);
this.Dispatcher.Invoke(new Action(() => lblTotalRec.Content = ++recTotal));
if (cancelTokenSource.IsCancellationRequested)
{
bRtn = false;
break;
}
}
readerDest.Close();
int recCnt = 1;
foreach (string tile_id in tile_ids)
{
cmdDest.CommandText = "SELECT tile_id FROM map WHERE tile_id = '" + tile_id + "'";
readerDest = cmdDest.ExecuteReader();
if (readerDest.Read())
{
readerDest.Close();
}
else
{
readerDest.Close();
cmdDest.CommandText = "INSERT INTO map VALUES(@zoom_level, @tile_column, @tile_row, @tile_id)";
SQLiteParameter[] sqlParams =
{
new SQLiteParameter("@zoom_level", DbType.Int64),
new SQLiteParameter("@tile_column", DbType.Int64),
new SQLiteParameter("@tile_row", DbType.Int64),
new SQLiteParameter("@tile_id", DbType.String),
};
string[] paramStr = tile_id.Split('-');
if (paramStr.Length != 2)
{
bRtn = false;
break;
}
int len = paramStr[0].Length;
string zoom_s = paramStr[0].Substring(0, len - 6).TrimStart('L');
if (!long.TryParse(zoom_s, out long zoom_level))
{
bRtn = false;
break;
}
if (!long.TryParse(paramStr[1], out long tile_column))
{
bRtn = false;
break;
}
if (long.TryParse(paramStr[0].Substring(len - 6), out long tile_row))
{
bRtn = false;
break;
}
// tile_row = (1 << (int)zoom_level) - tile_row; // 如果是水经注老版格式，需要取消注释此行。
sqlParams[0].Value = zoom_level;
sqlParams[1].Value = tile_column;
sqlParams[2].Value = tile_row;
sqlParams[3].Value = tile_id;
cmdDest.Parameters.AddRange(sqlParams);
cmdDest.ExecuteNonQuery();
readerDest.Close();
}
this.Dispatcher.Invoke(new Action(() => lblCurrentRec.Content = recCnt++));
progress.Report((double)recCnt * 100.0 / (double)recTotal);
if (cancelTokenSource.IsCancellationRequested)
{
bRtn = false;
break;
}
}
transDest.Commit();
sqlDest.Close();
return(bRtn);
}
bool CombineImages(string sourcefile, string destfile, IProgress <double> progress, CancellationTokenSource cancelTokenSource)
{
bool bRtn = true;
SQLiteConnection sqlSource = new SQLiteConnection("Data Source=" + sourcefile + ";Version=3;");
sqlSource.Open();
SQLiteCommand cmdSource = new SQLiteCommand(sqlSource);
SQLiteTransaction transSource = sqlSource.BeginTransaction(); // 开启一个新事务，加快操作速度。
cmdSource.Transaction = transSource;
cmdSource.CommandText = "SELECT COUNT(*) FROM images";
SQLiteDataReader readerSource = cmdSource.ExecuteReader();
int recTotal = 0;
if (readerSource.Read())
{
this.Dispatcher.Invoke(new Action(() => lblTotalRec.Content = recTotal = readerSource.GetInt32(0)));
}
readerSource.Close();
cmdSource.CommandText = "SELECT * FROM images";
readerSource = cmdSource.ExecuteReader();
SQLiteConnection sqlDest = new SQLiteConnection("Data Source=" + destfile + ";Version=3;");
sqlDest.Open();
SQLiteCommand cmdDest = new SQLiteCommand(sqlDest);
SQLiteTransaction transDest = sqlDest.BeginTransaction();
cmdDest.Transaction = transDest;
int recCnt = 1;
while (readerSource.Read())
{
string tile_id = (string)readerSource["tile_id"];
byte[] tile_data = (byte[])readerSource["tile_data"];
cmdDest.CommandText = "SELECT tile_id FROM images WHERE tile_id = '" + tile_id + "'";
SQLiteDataReader readerDest = cmdDest.ExecuteReader();
if (readerDest.Read())
{
readerDest.Close();
}
else
{
readerDest.Close(); // 执行新SQL命令前，先关闭前执行的命令。
cmdDest.CommandText = "INSERT INTO images VALUES(@tile_data, @tile_id)";
SQLiteParameter[] sqlParams =
{
new SQLiteParameter("@tile_data", DbType.Object),
new SQLiteParameter("@tile_id", DbType.String)
};
sqlParams[0].Value = tile_data;
sqlParams[1].Value = tile_id;
cmdDest.Parameters.AddRange(sqlParams);
cmdDest.ExecuteNonQuery();
readerDest.Close(); // 关闭SQL命令，为下次执行做准备。
}
this.Dispatcher.Invoke(new Action(() => lblCurrentRec.Content = recCnt++));
progress.Report((double)recCnt * 100.0 / (double)recTotal);
if (cancelTokenSource.IsCancellationRequested)
{
bRtn = false;
break;
}
}
readerSource.Close();
transDest.Commit();
sqlDest.Close();
transSource.Commit(); // 提交当前事务
sqlSource.Close();
return(bRtn);
}
public void DapperTransaction()
{
using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
conn.Execute(@"create table Test (Id integer primary key, String text); insert into Test(Id, String) values(1, 'one'), (2, 'two'), (3, 'three');", transaction: trans);
trans.Commit();
}
var results = conn.Query<long>("select Id from Test where length(String) = @len", new { len = 3 }).ToList();
CollectionAssert.AreEqual(new long[] { 1, 2 }, results);
}
}
