static void Main(string[] args)
{
String fileName = @"D:\test.db";
// create a database "context" object t
String connectionString = String.Format("Data Source={0};Version=3", fileName);
DbProviderFactory sqlFactory = new System.Data.SQLite.SQLiteFactory();
PetaPoco.Database db = new PetaPoco.Database(connectionString, sqlFactory);
#region 创建测试表1
//string dropQuery = "DROP TABLE qrtz_operatelog";
//db.Execute(dropQuery);
//String createQuery =
// @"CREATE TABLE IF NOT EXISTS
// [User] (
// [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
// [Name] VARCHAR(20) NOT NULL DEFAULT 'tww',
// [Phone] VARCHAR(20) NOT NULL,
// [Email] VARCHAR(20) NOT NULL,
// [Password] VARCHAR(20) NOT NULL,
// [Active] INTEGER NOT NULL DEFAULT 1,
// [CreateTime] TEXT NOT NULL
// )";
String createQuery =
@"DROP TABLE qrtz_operatelog;
CREATE TABLE IF NOT EXISTS
[qrtz_operatelog] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[TableName] VARCHAR(255) ,
[Describe] VARCHAR(255) ,
[Type] INTEGER NOT NULL,
[CreateTime] datetime NOT NULL,
[UpdateTime] datetime
)";
db.Execute(createQuery);
createQuery =
@"DROP TABLE Schedule;
CREATE TABLE IF NOT EXISTS
[Schedule] (
[JobId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[JobName] VARCHAR(255) ,
[JobGroup] VARCHAR(255) ,
[JobStatus] INTEGER NOT NULL,
[Cron] varchar(50) ,
[AssemblyName] varchar(50) NOT NULL,
[ClassName] varchar(50) NOT NULL,
[Remark] VARCHAR(255) ,
[CreateTime] datetime NOT NULL,
[UpdateTime] datetime ,
[RunTimes] INTEGER NOT NULL,
[BeginTime] datetime NOT NULL,
[EndTime] datetime ,
[TriggerType] INTEGER NOT NULL,
[IntervalSecond] INTEGER NOT NULL,
[Url] VARCHAR(255)
)";
db.Execute(createQuery);
}
private void ProcessResendPriceUpdateToWehkampProducts(IEnumerable <ProductInformation> products, int vendorID)
{
var productIDs = products.Where(p => p.ResendPriceUpdateToWehkamp != null && p.ResendPriceUpdateToWehkamp.ToLowerInvariant() == "true").Select(product => product.ProductID).ToList();
if (productIDs.Count == 0)
{
return;
}
try
{
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 600; //10 minutes
var sql = string.Format("UPDATE VendorPrice SET LastUpdated = GETDATE() WHERE VendorAssortmentID IN (SELECT VendorAssortmentID FROM VendorAssortment va INNER JOIN Product p ON p.ProductID = va.ProductID WHERE p.ParentProductID IN ({0}) AND va.VendorID = {1})", string.Join(",", productIDs.ToArray()), vendorID);
db.Execute(sql);
sql = string.Format("DELETE FROM ProductAttributeValue WHERE AttributeID = {0} AND ProductID IN ({1})", VendorSettingsHelper.GetResendPriceUpdateToWehkampAttributeID(), string.Join(",", productIDs.ToArray()));
db.Execute(sql);
}
}
catch (Exception ex)
{
log.AuditError(String.Format("ProcessResendPriceUpdateToWehkampProducts. ProductID's : {0}", string.Join(",", productIDs.ToArray())), ex);
}
}
public void DeleteInventory(object parameter)
{
var selectedInventory = (InventoryModel)parameter;
if (selectedInventory == null)
{
return;
}
if (MessageBox.Show("Soll die ausgewählte Inventurliste gelöscht werden?", "Frage", MessageBoxButton.OKCancel, MessageBoxImage.Question) == MessageBoxResult.Cancel)
{
return;
}
try
{
using (var db = new PetaPoco.Database("db"))
{
db.Execute(sql: "DELETE FROM Inventories WHERE inventoryId =@0;", selectedInventory.Id);
db.Execute(sql: "DELETE FROM InventoryLines WHERE InventoryId =@0;", selectedInventory.Id);
InventoryModels.Remove(selectedInventory);
InventoryLineModels.Clear();
MessageBox.Show("Ausgewählte Bestückungsliste wurde gelöscht.");
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht abgerufen werden:\n\n" + ex.Message), "Fehler");
}
}
private void ProcessResendProductInformationToWehkampProducts(IEnumerable <ProductInformationSize> products)
{
var productIDs = products.Where(p => p.ResendProductInformationToWehkamp != null && p.ResendProductInformationToWehkamp.ToLowerInvariant() == "true").Select(product => product.ProductID).ToList();
if (productIDs.Count == 0)
{
return;
}
var sql = string.Empty;
try
{
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 600; //10 minutes
sql = string.Format("UPDATE ProductAttributeValue SET Value = 'false' WHERE AttributeID = (SELECT AttributeID FROM ProductAttributeMetaData pamd WHERE pamd.AttributeCode = 'SentToWehkamp') AND ProductID IN (SELECT DISTINCT ParentProductID FROM Product WHERE ProductID IN ({0}))", string.Join(",", productIDs.ToArray()));
db.Execute(sql);
sql = string.Format("DELETE FROM ProductAttributeValue WHERE AttributeID = {0} AND ProductID IN (SELECT DISTINCT ParentProductID FROM Product WHERE ProductID IN ({1}))", VendorSettingsHelper.GetResendProductInformationToWehkampAttributeID(), string.Join(",", productIDs.ToArray()));
db.Execute(sql);
}
}
catch (Exception ex)
{
log.AuditError(String.Format("ProcessResendPriceUpdateToWehkampProducts. ProductID's : {0}; SQL used: {1}", string.Join(",", productIDs.ToArray()), sql), ex);
}
}
/// <summary>
/// 交換分類排序
/// </summary>
/// <param name="sID">ID</param>
/// <param name="sDirection">方向</param>
void IBrandTypeRepository.MoveTypeUpDown(long sID, string sDirection)
{
_db.Execute("EXEC procTypeSort @tablename, @inputId, @inputAct",
new
{
tablename = _typeTableName,
inputId = sID,
inputAct = sDirection
});
}
private static void RemoveSentAsDummy(List <int> productIDs)
{
var sentToWehkampAsDummyAttributeID = VendorSettingsHelper.GetSentToWehkampAsDummyAttributeID();
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 30;
var sql = string.Format("DELETE FROM ProductAttributeValue WHERE AttributeID = {0} AND ProductID IN ({1})", sentToWehkampAsDummyAttributeID, string.Join(",", productIDs.ToArray()));
db.Execute(sql);
var sqlParents = string.Format("DELETE FROM ProductAttributeValue WHERE AttributeID = {0} AND ProductID IN (SELECT ParentProductID FROM Product WHERE ProductID IN ({1}))", sentToWehkampAsDummyAttributeID, string.Join(",", productIDs.ToArray()));
db.Execute(sqlParents);
}
}
private static void SetReturnRequestsAsExportedToWehkamp(List <StockReturnRequest> returns)
{
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 30;
//All OrderID's
var sqlOrder = string.Format("UPDATE [Order] SET IsDispatched = 1 WHERE OrderID IN ({0})", string.Join(",", returns.Select(o => o.OrderID).Distinct().ToArray()));
db.Execute(sqlOrder);
//All OrderLineID's
var sql = string.Format("UPDATE [OrderLine] SET IsDispatched = 1 WHERE OrderLineID IN (SELECT OrderLineID FROM OrderLine WHERE OrderID IN ({0}))", string.Join(",", returns.Select(o => o.OrderID).Distinct().ToArray()));
db.Execute(sql);
}
}
/// <summary>
/// 删除掉帖子的所有标签
/// </summary>
/// <param name="fid"></param>
public static void DeleteTarg(int fid)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
db.Execute("DELETE FROM jexus_tags_relation WHERE fid=@0", fid);
}
}
/// <summary>
/// 删除掉帖子的所有标签
/// </summary>
/// <param name="fid"></param>
public static void DeleteTarg(int fid)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
db.Execute("DELETE FROM jexus_tags_relation WHERE fid=@0", fid);
}
}
private static int Execute(SQLiteConnection connection, string sql, params object[] args)
{
using (var database = new PetaPoco.Database(connection))
{
return(database.Execute(sql, args));
}
}
public void InitDataBase(string connectionString)
{
var db = new PetaPoco.Database(connectionString, "System.Data.SqlClient");
db.Execute(Const.DBCreateScript);
foreach (var province in ProvinceData.GetProvinces())
{
db.Insert("Province", "Id", new{Name = province.Name, Code = province.Code});
}
var provinces = db.Query<dynamic>(@"SELECT *
from Province").ToList();
BulkUploadToSql bulk =
BulkUploadToSql.Load(
HomeData.GetHomes()
.Select(
i =>
new Bulk.Home
{
AddTime = DateTime.Now,
BuildYear = i.BuildYear,
City = i.City,
Description = i.Description,
Price = i.Price,
Surface = i.Surface,
ProvinceId = provinces.First(j => j.Code == i.HomeProvince.Code).Id,
}), "Home", 10000, connectionString);
bulk.Flush();
}
public void Update(ArticleModel model)
{
var sql = "update dbo.Content set 内容=@0 where id=@1 ";
var poco = new PetaPoco.Database("sitecontent");
poco.Execute(sql, model.HeadContent, model.Id);
}
public static void EnsureDatabase(this PetaPoco.Database database)
{
if (database.ExecuteScalar <bool>(_SQL_check_if_tables_exists) == false)
{
database.Execute(_SQL_create_tables);
}
}
public static void Insert()
{
var db = new PetaPoco.Database("OwnCon");
var sw = new Stopwatch();
sw.Start();
for (var i = 0; i < 10000; i++)
{
var sql = $@"
INSERT INTO dbo.Student
( Name ,
Age ,
Sex ,
Birthday ,
Address
)
VALUES ( N'james' , -- Name - nvarchar(20)
{i}, -- Age - int
N'男' , -- Sex - nvarchar(5)
GETDATE()+{i} ,
'Petapocoa'
) ";
db.Execute(sql);
}
sw.Stop();
Console.WriteLine($"Petapoco插入10000条耗时:{sw.ElapsedMilliseconds}毫秒");
}
public void ClearLocation(string scenenName, int locationId)
{
var db = new PetaPoco.Database("ccs");
try
{
db.BeginTransaction();
db.Execute("update location set if_cargo=0 where id=" + locationId + " and scene_name='" + scenenName + "'");
db.Execute("DELETE FROM cargo_material WHERE location_id=@0 and scene_name=@1", locationId, scenenName);
db.Execute("DELETE FROM cargo_item WHERE location_id=@0 and scene_name=@1", locationId, scenenName);
db.CompleteTransaction();
}
catch (Exception ex)
{
db.AbortTransaction();
}
}
public bool DeleteStaff(long staffId)
{
try
{
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
var staff = DBConnectionString.Staff.SingleOrDefault(staffId);
db.Execute("delete from RoleUser where User_UserId = @0", staff.UserId);
db.Execute("delete from [Profile] where UserId = @0", staff.UserId);
db.Execute("delete from TeacherSubjects where UserId = @0", staff.UserId);
db.Execute("delete from Staff where StaffId = @0", staffId);
db.Execute("delete from Users where UserId = @0", staff.UserId);
return(true);
}
catch
{
return(false);
}
}
public ActionResult DeleteConfirm(long id)
{
DBConnectionString.Student student = DBConnectionString.Student.SingleOrDefault(id);
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
db.Execute("Delete from UserGroup where UserId=@0", student.UserId);
db.Execute("Delete from UserSubjects where UserId=@0", student.UserId);
db.Execute("Delete from ELetter where UserId=@0", student.UserId);
db.Execute("Delete from Profile where UserId=@0", student.UserId);
db.Execute("Delete from Users where UserId=@0", student.UserId);
if (student != null)
{
student.Delete();
}
return RedirectToAction("Index");
}
public ActionResult DeleteConfirm(long id)
{
DBConnectionString.Student student = DBConnectionString.Student.SingleOrDefault(id);
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
db.Execute("Delete from UserGroup where UserId=@0", student.UserId);
db.Execute("Delete from UserSubjects where UserId=@0", student.UserId);
db.Execute("Delete from ELetter where UserId=@0", student.UserId);
db.Execute("Delete from Profile where UserId=@0", student.UserId);
db.Execute("Delete from Users where UserId=@0", student.UserId);
if (student != null)
{
student.Delete();
}
return(RedirectToAction("Index"));
}
public static bool BatchDeleteTopic(string fids)
{
using (PetaPoco.Database db = new PetaPoco.Database("sqlconnection"))
{
var sql = "UPDATE forums SET is_hidden=1 WHERE fid in(" + fids + ") ";
var result = db.Execute(sql);
return(result > 0);
}
}
public override void StopTrace(string traceName)
{
var trace = Path.Combine(_traceDir, string.Format(TraceFileFormat, traceName));
_db.Execute(@"
DECLARE @@TraceID INT;
SELECT @@TraceID = id FROM sys.traces Where [path] = '" + trace + @"'
exec sp_trace_setstatus @@TraceID, 0 -- stop
exec sp_trace_setstatus @@TraceID, 2 -- delete");
}
private static void SetProcessedOrderLinesAsDispatched(List <int> orderLineIDs, int vendorID)
{
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 5 * 60;
var sql = string.Format("UPDATE [OrderLine] SET IsDispatched = 1, DispatchedToVendorID = {1} WHERE OrderLineID IN ({0})", string.Join(",", orderLineIDs.ToArray()), vendorID);
db.Execute(sql);
}
}
public ActionResult enable(string[] dulieu,int trang)
{
var db = new PetaPoco.Database("MyConnection");
for (int i = 0; i < dulieu.Length; i++)
{
string qr = string.Format("update hoivien set Disable = '0' where APK = '{0}'", dulieu[i]);
db.Execute(qr);
}
return LoadDefault(trang);
}
public int DeleteSignature(string residentId)
{
int sigDeleteId = 0;
var db = new PetaPoco.Database("Olgarsconnection");
sigDeleteId = db.Execute("delete from DigiSignature where ResidentId = @0 ", residentId);
return(sigDeleteId);
}
public int Execute(string sql, params object[] args)
{
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
return(database.Execute(sql, args));
}
}
}
public bool DeleteUserGroup(long userId, long groupId)
{
int recAffected = 0;
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
recAffected = db.Execute("delete from UserGroup where UserId = @0 and GroupId = @1", userId, groupId);
if (recAffected > 0)
{
return true;
}
return false;
}
public bool DeleteUserSubject(long userId, long subjectId)
{
int recAffected = 0;
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
recAffected = db.Execute("delete from UserSubjects where UserId = @0 and SubjectId = @1", userId, subjectId);
if (recAffected > 0)
{
return(true);
}
return(false);
}
public void UpdateVendorStockQuantity(int productID, int vendorID, int quantityOnHand)
{
using (var db = new PetaPoco.Database(Connection, "System.Data.SqlClient"))
{
db.Execute(string.Format(@"
UPDATE VendorStock
SET QuantityOnHand = {0}
WHERE ProductID = {1}
AND VendorID = {2}
", quantityOnHand, productID, vendorID));
}
}
public object DELETE(int id) {
try {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("DELETE FROM Tema")
.Append("WHERE TemaID= @0", id);
db.Execute(sql);
return (new JsonResult() { Data = new { ok = true, error = "", sql = sql }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
} catch (Exception e) {
return (new JsonResult() { Data = new { ok = false, error = e.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
}
public object Post([FromBody] SubEstadoContacto item) {
try {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append(" INSERT INTO SubEstadoContacto (EstadoContactoID,Descripcion,TipoCuentaID,GrupoID)")
.Append(" VALUES(@0,@1,@2)",item.EstadoContactoID, item.Descripcion, item.TipoCuentaID, item.GrupoID);
db.Execute(sql);
return (new JsonResult() { Data = new { ok = true, error = "", sql = sql }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
} catch (Exception e) {
return (new JsonResult() { Data = new { ok = false, error = e.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
}
private static void MigrateProductTypeJson()
{
Console.WriteLine("Migrate producttype json");
using (var db = new PetaPoco.Database(Environments.Current.Connection, PetaPoco.Database.MsSqlClientProvider))
{
var defaultLanguageID = db.SingleOrDefault <int?>(@"select LanguageID from Language where Name = @0", _defaultLanguageName);
if (defaultLanguageID == null)
{
Console.WriteLine(string.Format("Language {0} does not exist", _defaultLanguageName));
Console.Read();
return;
}
var vendorSetting = db.SingleOrDefault <string>("Select Value from VendorSetting where VendorID = @0 and SettingKey = @1", _vendorID, _settingKeyProductType);
if (vendorSetting == null)
{
Console.WriteLine(string.Format("Vendorsetting {0} for vendor {1} does not exist", _settingKeyProductType, _vendorID));
Console.Read();
return;
}
var productTypeDescriptionValue = vendorSetting;
var oldModel = productTypeDescriptionValue.FromJson <List <ProductTypeModelOld> >();
var newList = new List <ProductTypeModelNew>();
foreach (var value in oldModel)
{
var newObject = new ProductTypeModelNew();
newObject.Type = value.Type;
newObject.IsBra = value.IsBra;
newObject.ProductType = value.ProductType;
newObject.Translations = new Dictionary <int, string>();
newObject.Translations.Add(defaultLanguageID.Value, value.Translation);
newList.Add(newObject);
}
var newJsonValue = newList.ToJson();
db.Execute("Update VendorSetting set Value = @0 where SettingKey = @1 and VendorID = @2", newJsonValue, _settingKeyProductType, _vendorID);
Console.WriteLine("Done");
}
}
static BankAccountEventHandler()
{
using (var db = new PetaPoco.Database("DemoConnectionString"))
{
var count = db.ExecuteScalar <int>(@"select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BankAccountReadModel'");
if (count > 0)
{
return;
}
db.Execute("CREATE TABLE BankAccountReadModel ([Id] [uniqueidentifier] NOT NULL,[CurrentBalance] [float] NOT NULL,PRIMARY KEY CLUSTERED ([Id] ASC))");
}
}
/// <summary>
/// Creates tables if they don't already exist in the database
/// </summary>
/// <param name="tableName">Name of the table to create</param>
/// <param name="sql">SQL statement for table creation</param>
/// <param name="connectionString">SQL connection string</param>
private static void CreateTable(string tableName, string sql, string connectionString)
{
using (var db = new PetaPoco.Database(connectionString))
{
var count = db.ExecuteScalar <int>(TableExists, tableName);
if (count > 0)
{
return;
}
db.Execute(sql);
}
}
public object update([FromBody] EstadoContacto item) {
try {
var db = new PetaPoco.Database("jlapc");
var sql = PetaPoco.Sql.Builder
.Append("UPDATE EstadoContacto")
.Append(" SET Descripcion= @0", item.Descripcion)
.Append("WHERE EstadoContactoID = @0", item.EstadoContactoID);
db.Execute(sql);
return (new JsonResult() { Data = new { ok = true, sql = sql }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
} catch (Exception e) {
return (new JsonResult() { Data = new { ok = false, error = e.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet }).Data;
}
}
private static void SetProductsAsSentToWehkamp(List <int> productIDs)
{
var sentToWehkampAttributeID = VendorSettingsHelper.GetSentToWehkampAttributeID();
using (var db = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
db.CommandTimeout = 30;
foreach (var productID in productIDs)
{
var sql = string.Format("IF EXISTS (SELECT * FROM [ProductAttributeValue] WHERE AttributeID = {0} AND ProductID = {1}) UPDATE [ProductAttributeValue] SET [Value] = 'true' WHERE AttributeID = {0} AND ProductID = {1} ELSE INSERT INTO [ProductAttributeValue] (AttributeID, ProductID, [Value], CreatedBy, CreationTime) VALUES ({0}, {1}, 'true', 1, getdate())", sentToWehkampAttributeID, productID);
db.Execute(sql);
}
}
}
public bool enable(string[] dulieu)
{
var db = new PetaPoco.Database("MyConnection");
try{
for (int i = 0; i < dulieu.Length; i++)
{
string qr = string.Format("update hoivien set Disable = '0' where APK = '{0}'", dulieu[i]);
db.Execute(qr);
}return true;
}
catch (Exception e)
{
return false;
};
}
public static void IncrementSalesSlipNumber(ref int currentSalesSlip, int vendorID, string salesSlipNumberSetting = "")
{
if (currentSalesSlip == 9999)
{
currentSalesSlip = 0;
}
currentSalesSlip++;
using (var pDb = new PetaPoco.Database(Environments.Current.Connection, "System.Data.SqlClient"))
{
pDb.Execute(@"if exists (select * from vendorsetting where vendorid = @1 and settingkey = @2)
update VendorSetting set value = @0 where vendorid = @1 and settingkey = @2
else
insert into VendorSetting (VendorID, SettingKey, Value) values (@1, @2, @0)", currentSalesSlip, vendorID, string.IsNullOrEmpty(salesSlipNumberSetting) ? MANCO_SALESSLIP_NUMBER_SETTINGKEY : salesSlipNumberSetting);
}
}
public int deleteCustId(string custIdNo)
{
int IsUserOwner = 0;
int tripLegDeleteId = 0;
IsUserOwner = checkIfIdExists(custIdNo);
if (IsUserOwner != 0)
{
var db = new PetaPoco.Database("Olgarsconnection");
tripLegDeleteId = db.Execute("Delete [DigiSignature] Where ResidentId = @0", custIdNo);
}
else
{
tripLegDeleteId = -1;
}
return(tripLegDeleteId);
}
public int updateDigiField(string custIdNo, string fieldName, string fieldValue)
{
int IsUserOwner = 0;
int tripLegDeleteId = 0;
IsUserOwner = checkIfIdExists(custIdNo);
string sqlExec = "update DigiSignature set " + fieldName + "=@0 where ResidentId = @1";
if (IsUserOwner != 0)
{
var db = new PetaPoco.Database("Olgarsconnection");
tripLegDeleteId = db.Execute(sqlExec, fieldValue, custIdNo);
}
else
{
tripLegDeleteId = -1;
}
return(tripLegDeleteId);
}
public void SaveRestockLine(object parameter)
{
try
{
using (var db = new PetaPoco.Database("db"))
{
using (var scope = db.GetTransaction())
{
var vm = (RestockViewModel)parameter;
if (vm.SelectedRestockLineModel == null)
{
return;
}
var selectedRL = vm.SelectedRestockLineModel;
if (selectedRL.Amt == 0)
{
db.Execute("DELETE FROM RestockLines WHERE RestockLineId = @0", selectedRL.RestockLineId);
}
var changes = db.ExecuteScalar <int>("Update RestockLines SET Amt = @0 WHERE RestockLineId = @1 AND RestockId =@2;Select Changes()", selectedRL.Amt, selectedRL.RestockLineId, selectedRL.RestockId);
if (changes == 0)
{
var lastid = db.ExecuteScalar <int>("Select MAX(RestockLineId) FROM RestockLines");
selectedRL.RestockLineId = db.ExecuteScalar <int>("Insert Into RestockLines(RestockLineId,RestockId, Pos, ArtId, Amt) VALUES(@0, @1, @2, @3,@4);SELECT last_insert_rowid();", lastid + 1, selectedRL.RestockId, selectedRL.Pos, selectedRL.ArtId, selectedRL.Amt);
}
scope.Complete();
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Daten konnten nicht gespeichert werden:\n\n" + ex.Message), "Fehler", MessageBoxButton.OK, MessageBoxImage.Asterisk);
}
}
public bool DeleteStaff(long staffId)
{
try
{
PetaPoco.Database db = new PetaPoco.Database("DBConnectionString");
var staff = DBConnectionString.Staff.SingleOrDefault(staffId);
db.Execute("delete from RoleUser where User_UserId = @0", staff.UserId);
db.Execute("delete from [Profile] where UserId = @0", staff.UserId);
db.Execute("delete from TeacherSubjects where UserId = @0", staff.UserId);
db.Execute("delete from Staff where StaffId = @0", staffId);
db.Execute("delete from Users where UserId = @0", staff.UserId);
return true;
}
catch
{
return false;
}
}
public int Execute(string sql, params object[] args)
{
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
return database.Execute(sql, args);
}
}
}
/// <summary>
/// Creates tables if they don't already exist in the database
/// </summary>
/// <param name="tableName">Name of the table to create</param>
/// <param name="sql">SQL statement for table creation</param>
/// <param name="connectionString">SQL connection string</param>
private static void CreateTable(string tableName, string sql, string connectionString)
{
using (var db = new PetaPoco.Database(connectionString))
{
var count = db.ExecuteScalar<int>(TableExists, tableName);
if (count > 0)
{
return;
}
db.Execute(sql);
}
}
public ActionResult xoa(string[] dulieu, int trang)
{
var db = new PetaPoco.Database("MyConnection");
for (int i = 0; i < dulieu.Length; i++)
{
string qr = string.Format("delete from hoivien where APK = '{0}'",dulieu[i]);
db.Execute(qr);
}
return LoadDefault(trang);
}
private void CheckForTable()
{
if (!File.Exists(_dbFile))
{
using (var db = new PetaPoco.Database(@"Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;", "System.Data.SqlClient"))
{
db.Execute(string.Format(@"CREATE DATABASE [Queue] ON PRIMARY (NAME = Queue_Data, FILENAME = '{0}')", _dbFile));
db.Execute("EXEC sp_detach_db 'Queue', 'true'");
}
}
using (var db = GetDb())
{
db.Execute(@"
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Jobs')) BEGIN
CREATE TABLE [Jobs] (Id varchar(50) PRIMARY KEY, Created datetime, Processed datetime NULL, Completed datetime NULL, [Type] varchar(MAX) NULL, [Data] varchar(MAX) NULL);
END
");
}
}
public bool xoa(string[] dulieu)
{
var db = new PetaPoco.Database("MyConnection");
try{
for (int i = 0; i < dulieu.Length; i++)
{
string qr = string.Format("delete from hoivien where APK = '{0}'", dulieu[i]);
db.Execute(qr);
}return true;
}
catch (Exception e)
{
return false;
};
}