public void TestCaseSensitiveKeyColumn() { var path = Path.GetTempFileName(); try { var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path); sqlite.Open(); var cmd = sqlite.CreateCommand(); cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)"; cmd.ExecuteNonQuery(); cmd.Dispose(); sqlite.Close(); sqlite.Dispose(); using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID")) { var ext = new Envelope(); var ds = new SharpMap.Data.FeatureDataSet(); sq.ExecuteIntersectionQuery(ext, ds); NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count); } } catch (Exception ex) { Assert.Fail("Got exception, should not happen"); } finally { File.Delete(path); } }
public void UpdateDatabaseWithArteProps( string ConnectionString ) { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString ); conn.Open(); System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction(); System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn ); command.Transaction = transaction; foreach ( var a in ArteList ) { string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\""; Console.WriteLine( UpdateNames ); command.CommandText = UpdateNames; command.ExecuteNonQuery(); string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\""; Console.WriteLine( UpdateDescs ); command.CommandText = UpdateDescs; command.ExecuteNonQuery(); if ( a.Type == Arte.ArteType.Generic ) { string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\""; Console.WriteLine( UpdateStatus ); command.CommandText = UpdateStatus; command.ExecuteNonQuery(); } } command.Dispose(); transaction.Commit(); conn.Close(); conn.Dispose(); }
public bool StopTransaction() { transaction.Commit(); transaction.Dispose(); transaction = null; cnn.Close(); cnn.Dispose(); cnn = null; return(true); }
public void TestCaseSensitiveKeyColumn() { var path = Path.GetTempFileName(); try { var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path); sqlite.Open(); var cmd = sqlite.CreateCommand(); cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)"; cmd.ExecuteNonQuery(); cmd.Dispose(); sqlite.Close(); sqlite.Dispose(); using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID")) { var ext = new Envelope(); var ds = new SharpMap.Data.FeatureDataSet(); sq.ExecuteIntersectionQuery(ext, ds); NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count); } } catch (Exception ex) { Assert.Fail("Got exception, should not happen"); } finally { File.Delete(path); } }
private void displayPersonDetail(string personNo) { // System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // string sql_findInfo = string.Format("select * from RentPersonInfo where personCardNum = '{0}'", personNo); // System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_findInfo; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); //借书人姓名 string personName = reader.GetString(0); string personNum = reader.GetString(1); string mobile = reader.GetString(3); label_personDetail.Text = "借书人详细信息:" + "\r\n" + "\r\n" + "姓名:" + personName + "\r\n" + "\r\n" + "身份证号:" + personNum + "\r\n" + "\r\n" + "手机号:" + mobile; } // reader.Close(); cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
// "x:\util\android-sdk-windows\platform-tools\adb.exe" tcpip 5555 // restarting in TCP mode port: 5555 // "x:\util\android-sdk-windows\platform-tools\adb.exe" connect 192.168.1.126:5555 // connected to 192.168.1.126:5555 static ApplicationWebService() { // will this work for android? Console.WriteLine("ApplicationWebService cctor " + new { Environment.CurrentDirectory }); // ApplicationWebService cctor { CurrentDirectory = W:\staging.net.debug } #region setup:QueryExpressionBuilder.WithConnection //if (ScriptCoreLib.Query.Experimental.QueryExpressionBuilder.WithConnection == null) // override the default ScriptCoreLib.Query.Experimental.QueryExpressionBuilder.WithConnection = y => { // relative path? var DataSource = "file:server1.sqlite"; // nuget xsqlite? var cc = new System.Data.SQLite.SQLiteConnection(new System.Data.SQLite.SQLiteConnectionStringBuilder { DataSource = DataSource, Version = 3 }.ConnectionString); cc.Open(); y(cc); cc.Dispose(); }; #endregion }
public void UpdateDatabaseWithArteProps(string ConnectionString) { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(ConnectionString); conn.Open(); System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction(); System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn); command.Transaction = transaction; foreach (var a in ArteList) { string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\""; Console.WriteLine(UpdateNames); command.CommandText = UpdateNames; command.ExecuteNonQuery(); string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString("X6") + "]\""; Console.WriteLine(UpdateDescs); command.CommandText = UpdateDescs; command.ExecuteNonQuery(); if (a.Type == Arte.ArteType.Generic) { string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime(DateTime.UtcNow) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString("X6") + "]\""; Console.WriteLine(UpdateStatus); command.CommandText = UpdateStatus; command.ExecuteNonQuery(); } } command.Dispose(); transaction.Commit(); conn.Close(); conn.Dispose(); }
private void Btn_startPersonInfo_Click(object sender, System.EventArgs e) { listview_personInfos.Items.Clear(); System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // string sql_findInfo = "select * from RentPersonInfo order by personCardNum"; if (cb_personCondition.SelectedIndex == 1) { sql_findInfo = string.Format("select * from RentPersonInfo where personName = '{0}'", tb_personCondition.Text); } else if (cb_personCondition.SelectedIndex == 2) { sql_findInfo = string.Format("select * from RentPersonInfo where personCardNum = '{0}'", tb_personCondition.Text); } else if (cb_personCondition.SelectedIndex == 3) { sql_findInfo = string.Format("select * from RentPersonInfo where mobile = '{0}'", tb_personCondition.Text); } System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_findInfo; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { this.listview_personInfos.BeginUpdate(); while (reader.Read()) { string personName = reader.GetString(0); string personNum = reader.GetString(1); string personCardNo = reader.GetString(2); string mobile = reader.GetString(3); // ListViewItem lvi = new ListViewItem(); lvi.Text = personName; lvi.SubItems.Add(personNum); lvi.SubItems.Add(personCardNo); lvi.SubItems.Add(mobile); if (personName.Contains("已销卡")) { lvi.ForeColor = Color.ForestGreen; } listview_personInfos.Items.Add(lvi); } this.listview_personInfos.EndUpdate(); } // reader.Close(); cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
public void TestConnection() { //Using System.Data.SQLite.dll using (System.Data.SQLite.SQLiteConnection Conn = new System.Data.SQLite.SQLiteConnection(this.ConnStr)) { Conn.Open(); Conn.Close(); Conn.Dispose(); } }
private void Btn_startOutBook_Click(object sender, System.EventArgs e) { System.DateTime dt = System.DateTime.Now; if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1) { //INIhelp.SetValue("username4", "12312345"); //throw new System.Exception("电脑出现故障了."); //return; } if (TextBox_bookName.Text.Length == 0) { System.Windows.Forms.MessageBox.Show("请正确填写书名", "错误提示"); return; } if (TextBox_bookValue.Text.Length == 0) { System.Windows.Forms.MessageBox.Show("请正确填写书籍价格", "错误提示"); return; } if (TextBox_personCardNum.Text.Length != 8) { System.Windows.Forms.MessageBox.Show("请正确填写借阅卡号", "错误提示"); return; } System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); string sql_insert = string.Format("insert into RentBookInfo values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", TextBox_bookISBN.Text, TextBox_personCardNum.Text, TextBox_bookPublisher.Text, TextBox_bookValue.Text, DataPic_bookOutDate.Text, DataPic_bookBackDate.Text, TextBox_bookName.Text); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_insert; cmd.Connection = conn; cmd.ExecuteNonQuery(); // //同时插入rentallbookofperson表中 sql_insert = string.Format("insert into RentAllBookOfPerson values ('{0}','{1}','{2}')", TextBox_bookName.Text, TextBox_bookISBN.Text, TextBox_personCardNum.Text); System.Data.SQLite.SQLiteCommand cmdAll = new System.Data.SQLite.SQLiteCommand(); cmdAll.CommandText = sql_insert; cmdAll.Connection = conn; cmdAll.ExecuteNonQuery(); System.Windows.Forms.MessageBox.Show("新建借书成功", "提示"); //借书成功后,自动清空ISBN,方便扫码 btn_clearISBN.PerformClick(); // cmd.Dispose(); cmdAll.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
private void Btn_checkPersonCanRent_Click(object sender, System.EventArgs e) { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // string sql = string.Format("select bookValue from RentBookInfo where personCardNum = '{0}'", tb_checkPersonCanRent.Text); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); // int iCount = 0; double value = 0.0; if (reader.HasRows) { while (reader.Read()) { value += System.Double.Parse(reader.GetString(0)); ++iCount; } } // reader.Close(); cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); if (value >= 100 && value < 200 && iCount < 3) { string currentval = string.Format("读者当前已经借出总价大于100元的书,但还小于200元({0}元),请注意", value); System.Windows.Forms.MessageBox.Show(currentval, "提示"); return; } else if (value >= 200) { System.Windows.Forms.MessageBox.Show("读者当前已经借出总价大于200元的书,不可再借!", "提示"); return; } else if (iCount >= 3) { System.Windows.Forms.MessageBox.Show("读者当前已经借出3本书,不可再借!", "提示"); return; } else { string currentval = string.Format("读者当前可以借书!({0}元)", value); System.Windows.Forms.MessageBox.Show(currentval, "提示"); return; } }
private void Btn_startReturnBook_Click(object sender, System.EventArgs e) { System.DateTime dt = System.DateTime.Now; if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1) { //INIhelp.SetValue("username4", "12312345"); //throw new System.Exception("电脑出现故障了."); //return; } if (listview_personOfBooks.SelectedItems.Count > 0) { string bookISBN = listview_personOfBooks.SelectedItems[0].SubItems[1].Text; string personCardNo = listview_personOfBooks.SelectedItems[0].SubItems[4].Text; string backDate = listview_personOfBooks.SelectedItems[0].SubItems[3].Text; // // //算出时间差 System.DateTime timeNow = System.DateTime.Now; System.DateTime timeBack = System.DateTime.Parse(backDate); System.TimeSpan timeCut = timeNow - timeBack; int dayCut = timeCut.Days; string sNote = "还书成功"; if (dayCut <= 7 && dayCut > 0) { sNote = string.Format("该图书已归还,但已过期{0}天,需缴纳{1}元罚款!", dayCut, dayCut); } else if (dayCut > 7) { sNote = "该图书已过期大于7天,按规定图书已被购买,请支付书款!"; } // System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // string sql_del = string.Format("delete from RentBookInfo where bookISBN = '{0}' and personCardNum = '{1}'", bookISBN, personCardNo); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_del; cmd.Connection = conn; cmd.ExecuteNonQuery(); MessageBox.Show(sNote, "提示"); // Listview_personInfos_Click(null, null); // cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); } }
private void caculateRentBookData() { arrayRentData.Clear(); System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); for (int i = 0; i < 20; ++i) { DateTime reduce = datePicker.Value - new TimeSpan(1 * i, 0, 0, 0); string sql = string.Format("select * from RentBookInfo where bookOutDate = '{0}'", reduce.GetDateTimeFormats().GetValue(10)); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { int count = 0; while (reader.Read()) { ++count; } arrayRentData.Add(count); } else { arrayRentData.Add(0); } // reader.Close(); cmd.Dispose(); } conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); Invalidate(); }
private void Btn_CheckBook_Click(object sender, System.EventArgs e) { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // int iCount = 0; // string sql_checkbook = string.Format("select * from RentBookInfo where bookISBN = {0}", TextBox_CheckBookISBN.Text); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_checkbook; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { iCount += 1; } } if (iCount == 2) { System.Windows.Forms.MessageBox.Show("此书借出已达到两本,不可借出", "提示"); } else { System.Windows.Forms.MessageBox.Show("此书可借出", "提示"); } // reader.Close(); cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
//开始查询 private void Btn_startFindBooks_Click(object sender, System.EventArgs e) { // listview_books.Items.Clear(); // System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); // int nSel = combo_type.SelectedIndex; string sql_findbook = "select * from RentBookInfo order by personCardNum"; // if (nSel == 1) { sql_findbook = string.Format("select * from RentBookInfo where bookName like '%{0}%' order by personCardNum", tb_condition.Text); } else if (nSel == 2) { sql_findbook = string.Format("select * from RentBookInfo where bookISBN = '{0}' order by personCardNum", tb_condition.Text); } else if (nSel == 3) { sql_findbook = string.Format("select * from RentBookInfo where bookPublisher = '{0}' order by personCardNum", tb_condition.Text); } // System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql_findbook; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { this.listview_books.BeginUpdate(); while (reader.Read()) { string bookName = reader.GetString(6); string bookISBN = reader.GetString(0); string personCardNum = reader.GetString(1); string bookOutDate = reader.GetString(4); string bookBackDate = reader.GetString(5); string bookValue = reader.GetString(3); string publisher = reader.GetString(2); // bool isOver = compareIsOverDue(bookBackDate); // ListViewItem lvi = new ListViewItem(); lvi.Text = bookName; lvi.SubItems.Add(bookISBN); lvi.SubItems.Add(personCardNum); lvi.SubItems.Add(bookOutDate); lvi.SubItems.Add(bookBackDate); lvi.SubItems.Add(bookValue); lvi.SubItems.Add(publisher); if (isOver) { lvi.BackColor = Color.Red;//过期标红色 } listview_books.Items.Add(lvi); } this.listview_books.EndUpdate(); } // reader.Close(); cmd.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); }
private void Listview_personInfos_Click(object sender, System.EventArgs e) { //点击某一个人显示他的所有借书 if (listview_personInfos.SelectedItems.Count > 0) { string sCardNo = listview_personInfos.SelectedItems[0].SubItems[2].Text; // m_listRenting.Clear(); // listview_personOfBooks.Items.Clear(); System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr); conn.Open(); string sql = string.Format("select * from RentBookInfo where personCardNum = '{0}'", sCardNo); // System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandText = sql; cmd.Connection = conn; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { this.listview_personOfBooks.BeginUpdate(); while (reader.Read()) { string bookName = reader.GetString(6); string bookISBN = reader.GetString(0); string bookValue = reader.GetString(3); string bookBack = reader.GetString(5); // bool isOver = compareIsOverDue(bookBack); //为了后面判断是否本书已还 m_listRenting.Add(bookISBN); // ListViewItem lvi = new ListViewItem(); lvi.Text = bookName; lvi.SubItems.Add(bookISBN); lvi.SubItems.Add(bookValue); lvi.SubItems.Add(bookBack); lvi.SubItems.Add(sCardNo); if (isOver) { lvi.BackColor = Color.Red; } lvi.Font = new Font("黑体", 12F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Regular)), System.Drawing.GraphicsUnit.Point, ((byte)(134))); listview_personOfBooks.Items.Add(lvi); } this.listview_personOfBooks.EndUpdate(); } // reader.Close(); cmd.Dispose(); //再查allbook表 listview_allBookOfPerson.Items.Clear(); string sqlAll = string.Format("select * from RentAllBookOfPerson where personCardNum = '{0}'", sCardNo); //显示该人所有已借出和借过 System.Data.SQLite.SQLiteCommand cmdAll = new System.Data.SQLite.SQLiteCommand(); cmdAll.CommandText = sqlAll; cmdAll.Connection = conn; System.Data.SQLite.SQLiteDataReader readerAll = cmdAll.ExecuteReader(); if (readerAll.HasRows) { listview_allBookOfPerson.BeginUpdate(); while (readerAll.Read()) { string bookName = readerAll.GetString(0); string bookISBN = readerAll.GetString(1); // ListViewItem lvi = new ListViewItem(); lvi.Text = bookName; lvi.SubItems.Add(bookISBN); if (m_listRenting.Contains(bookISBN)) { lvi.ForeColor = Color.Red; } else { lvi.ForeColor = Color.Blue; } lvi.Font = new Font("黑体", 12F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Regular)), System.Drawing.GraphicsUnit.Point, ((byte)(134))); // listview_allBookOfPerson.Items.Add(lvi); } listview_allBookOfPerson.EndUpdate(); } // readerAll.Close(); cmdAll.Dispose(); conn.Close(); conn.Dispose(); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); } }
public static void Main(string[] args) { // detect debugger as 2015community? // Managed Compatibility Mode does not support Edit and Continue // lets do a headless simulation. #region setup:QueryExpressionBuilder.WithConnection ScriptCoreLib.Query.Experimental.QueryExpressionBuilder.WithConnection = y => { // relative path? var DataSource = "file:debugger1.sqlite"; // nuget xsqlite? var cc = new System.Data.SQLite.SQLiteConnection(new System.Data.SQLite.SQLiteConnectionStringBuilder { DataSource = DataSource, Version = 3 }.ConnectionString); cc.Open(); y(cc); cc.Dispose(); }; #endregion // https://github.com/dotnet/roslyn/wiki/EnC-Supported-Edits // http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/6015600-edit-continue-in-async-methods // Edit & Continue is one of the best feature of vs bringing huge productivity gain, unfortunatly Edit & Continue does not work in Async methods. new Action(new Program().EditAndContinue).InvokeAndReinvokeIfCodeModified(); //xDebugger.InvokeAndReinvokeIfCodeModified(EditAndContinue); RewriteToUltraApplication.AsProgram.Launch(typeof(Application)); }
/// <summary> /// Close the connection to the DB /// </summary> public static void CloseConnection() { //cleanup sqlCon.Close(); sqlCon.Dispose(); }