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