private static int RenameTable(string sourceTblName, string destTblName) { // Sample: ALTER TABLE sourceTblName RENAME TO destTblName; String strQuery = String.Empty; int response = 0; // Create command string strQuery = String.Format("ALTER TABLE {0} RENAME TO {1}", sourceTblName, destTblName ); try { // execute rename table name command response = DBAdapter.Instance().ExecuteNonQuery(strQuery); response = 1; } catch (System.Exception ex) { } return(response); }
internal static bool StartUpdate() { // CREATE TABLE tblNew AS SELECT * FROM Products WHERE 1=2 int response = 0; String strQuery = String.Empty; // Delete temprorary table if exist response = DropTable(TempProductTable); // Create command string strQuery = String.Format("CREATE TABLE {0} " + "AS SELECT * FROM {1} " + "WHERE 1=2", TempProductTable, ProductTable); try { response = DBAdapter.Instance().ExecuteNonQuery(strQuery); response = 1; // Set flag for adding products to temp table inUpdateState = true; } catch (System.Exception ex) { } return(response > 0); }
public bool DeleteProduct(IProduct p) { try { String strQuery = ""; strQuery = String.Format("DELETE FROM {0} WHERE ID ='{0}'", p.Id); DBAdapter.Instance().ExecuteNonQuery(strQuery); return(true); } catch { return(false); } }
internal static IProduct CreateProduct(string name, Department department, decimal price) { Product p = new Product(); p.department = department; p.name = name; p.quantity = 1; p.unit = "ADET"; p.unitPrice = price; if (name.EndsWith("DYNAMIC")) { IProduct existingProduct = null; int index = p.name.LastIndexOf("DYNAMIC"); p.name = p.name.Substring(0, index); try { existingProduct = (Product)FindByName(p.name); } catch { } if (existingProduct != null) { if (existingProduct.Department == p.Department && existingProduct.UnitPrice == p.UnitPrice) { return(existingProduct); } } int dynamicLabel = (int)DBAdapter.Instance().ExecuteQuery(String.Format("SELECT MAX(ID) FROM", ProductTable)); dynamicLabel++; p.id = dynamicLabel; p.valid = true; p.status = ProductStatus.Weighable; p.category = "1"; p.secondaryUnitPrice = 0; Add(p); } return(p); }
private static IProduct SelectProduct(string condition, params object[] args) { Product c = null; String strQuery = ""; strQuery = "SELECT * FROM " + ProductTable; if (!String.IsNullOrEmpty(condition)) { strQuery += " WHERE " + String.Format(condition, args); } DataSet ds = DBAdapter.Instance().GetDataSet(strQuery); if (ds.Tables[0].Rows.Count > 0) { c = Parse(ds.Tables[0].Rows[0]); } return(c); }
private static int DropTable(string tableName) { // Sample: DROP TABLE IF EXISTS TempProducts; String strQuery = String.Empty; int response = 0; // Create command string strQuery = String.Format("DROP TABLE IF EXISTS {0} ", tableName); try { // execute drop command response = DBAdapter.Instance().ExecuteNonQuery(strQuery); response = 1; } catch (System.Exception ex) { } return(response); }
internal static List <IProduct> SearchProductByName(String nameData) { List <IProduct> productList = new List <IProduct>(); string strQuery = ""; strQuery = String.Format("SELECT * FROM {0} ", ProductTable); strQuery += String.Format("WHERE Name Like '%{0}%'", nameData); try { DataSet ds = DBAdapter.Instance().GetDataSet(strQuery); foreach (DataRow row in ds.Tables[0].Rows) { productList.Add(Parse(row)); } } catch { // do nothing } return(productList); }
/* TODO: Try to increase speed of transaction * using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) * { * using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) * { * SQLiteParameter myparam = new SQLiteParameter(); * int n; * * mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; * mycommand.Parameters.Add(myparam); * * for (n = 0; n < 100000; n ++) * { * myparam.Value = n + 1; * mycommand.ExecuteNonQuery(); * } * } * mytransaction.Commit(); * } */ internal static void LoadProductFileToDB(string path, out int successCount, out int failCount) { String line = ""; String tblName = ""; Product p; successCount = 0; failCount = 0; tblName = inUpdateState ? TempProductTable : ProductTable; DataSet dsDocItems = DBAdapter.Instance().GetDataSet("SELECT * FROM " + tblName); using (StreamReader sr = new StreamReader(path, PosConfiguration.DefaultEncoding)) { while ((line = @sr.ReadLine()) != null) { //line[0] == '0' means invalid line //Skip trailing blank lines and invalid lines if (line.Trim().Length == 0 || line[0] == '0') { continue; } p = LineToProduct(line); if (AddToProductTable(dsDocItems.Tables[0], p)) { successCount++; } else { failCount++; } } sr.Close(); } DBAdapter.Instance().UpdateDataSet(dsDocItems); }
private static bool Add(Product p) { try { string strQuery = ""; string tblName = inUpdateState ? TempProductTable : ProductTable; IProduct tempProduct = null; try { tempProduct = FindByLabel(p.id.ToString()); } catch (System.Exception ex) { } CultureInfo ci = CultureInfo.GetCultureInfo("en-US"); if (tempProduct == null) { strQuery += "INSERT INTO " + tblName; strQuery += " (ID,Name,Barcode,DepartmentId,UnitPrice,SecondaryUnitPrice,Unit,ExtraProperty,Valid,CategoryId)"; strQuery += " VALUES "; strQuery += String.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", p.id, p.name.TrimEnd(new char[] { ' ' }), p.barcode, p.department.Id, p.unitPrice.ToString("f", ci), p.secondaryUnitPrice.ToString("f", ci), p.Unit, GetAdditionalProperty(p), p.valid, p.category ); } else { strQuery += "UPDATE " + tblName; strQuery += String.Format(" SET Name = '{0}', Barcode = '{1}', DepartmentId = '{2}',UnitPrice = '{3}'," + "SecondaryUnitPrice = '{4}', Unit = '{5}', ExtraProperty = '{6}'," + "Valid = '{7}', CategoryId = '{8}'", p.name.TrimEnd(new char[] { ' ' }), p.barcode, p.department.Id, p.unitPrice.ToString("f", ci), p.secondaryUnitPrice.ToString("f", ci), p.Unit, GetAdditionalProperty(p), p.valid, p.category ); strQuery += String.Format(" WHERE ID = '{0}'", p.id); } DataSet ds = DBAdapter.Instance().GetDataSet(strQuery); return(DBAdapter.Instance().ExecuteNonQuery(strQuery) > 0); } catch { EZLogger.Log.Error("Ürün eklenemedi : ", p.ToString()); } return(false); }