private void SaveReturnArchiveInfo(List <GridRow> list) { using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource)) { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = conn; try { foreach (GridRow gr in list) { ReturnArchive ai = GridCellMapToReturnArchive(gr); cmd.CommandText = GenReturnSQL(ai); cmd.ExecuteNonQuery(); if (ai.Id == 0) { cmd.CommandText = NotifyArchiveRemaining(ai); cmd.ExecuteNonQuery(); ReturnArchiveRetreeIdFixRowCellReadonly(gr, ai); gr["gcReturnCount"].AllowEdit = false; } } } catch (System.Data.SQLite.SQLiteException E) { MessageBox.Show(cmd.CommandText + Environment.NewLine + E.Message); } finally { conn.Close(); } } }
private string GenReturnSQL(ReturnArchive ai) { if (ai.Id == 0) { return("insert into ReturnArchive(archiveName, ReturnDate, Copies, Handler, DamageOrLost, Remark, Returner, ArchId) values ('" + ai.ArchiveName + "','" + ai.ReturnDate + "'," + ai.Copies + ",'" + ai.Handler + "'," + ai.DamageOrLost + ",'" + ai.Remark + "','" + ai.Returner + "'," + CurrentArchiveInfo.Id + ")"); } return("update ReturnArchive set archiveName='" + ai.ArchiveName + "', ReturnDate='" + ai.ReturnDate + "', Copies=" + ai.Copies + ", Handler='" + ai.Handler + "', DamageOrLost=" + ai.DamageOrLost + ", Remark='" + ai.Remark + "', Returner='" + ai.Returner + "' where id =" + ai.Id); }
private string NotifyArchiveRemaining(ReturnArchive ai) { int remaining = 0; using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource)) { conn.Open(); SQLiteCommand sql_cmd = conn.CreateCommand(); sql_cmd.CommandText = string.Format("select copies - ifnull((select sum(copies) from lendArchive where archId={0} and id <> 0),0) + ifnull((select sum(copies) from ReturnArchive where archId={0} and id <> 0),0) as c from archiveInfo where id = {0}", ai.ArchId); remaining = Convert.ToInt32(sql_cmd.ExecuteScalar()); conn.Close(); } return("update ArchiveInfo set Remaining = " + remaining + " where Id='" + CurrentArchiveInfo.Id + "'"); }
private void ReturnArchiveRetreeIdFixRowCellReadonly(GridRow gr, ReturnArchive ai) { gr.RowDirty = false; if (ai.Id == 0) { using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource)) { conn.Open(); SQLiteCommand sql_cmd = conn.CreateCommand(); sql_cmd.CommandText = "select seq from sqlite_sequence where name='ReturnArchive'; "; ai.Id = Convert.ToInt32(sql_cmd.ExecuteScalar()); gr["gcId"].Value = ai.Id; conn.Close(); } } }