/// <summary>
/// Execute database structure creating script.
/// </summary>
/// <param name="sqlCmd">SQL command.</param>
/// <param name="script">Script text.</param>
private void _ExecuteScript(SqlCeCommand sqlCmd, string script)
{
Debug.Assert(sqlCmd != null);
Debug.Assert(script != null);
SqlCeConnection conn = sqlCmd.Connection;
string[] commands = Regex.Split(script, CMD_DELIMITER);
SqlCeTransaction trans = conn.BeginTransaction();
try
{
foreach (string cmdStr in commands)
{
// If command exists - execute it.
if (cmdStr.Trim().Length > 0)
{
sqlCmd.CommandText = cmdStr;
sqlCmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
/// <summary>
/// 完了したタスクを元に戻す
/// </summary>
/// <param name="id">元に戻す完了したタスクのID</param>
public void RestoreCompleteTask(int id)
{
try
{
using (SqlCeTransaction trans = this.con.BeginTransaction())
{
using (SqlCeCommand cmd = this.con.CreateCommand())
{
string sql = @"UPDATE [ToDo] "
+ "SET [deleted] = @deleted, [deletedtime] = @deletedtime "
+ "WHERE id = @id;";
cmd.CommandText = sql;
cmd.Parameters.Add("deleted", System.Data.DbType.Int32);
cmd.Parameters.Add("deletedtime", System.Data.DbType.DateTime);
cmd.Parameters.Add("id", System.Data.DbType.Int32);
cmd.Parameters["deleted"].Value = 0;
cmd.Parameters["deletedtime"].Value = new DateTime(0).ToString("yyyy-MM-dd");
cmd.Parameters["id"].Value = id;
cmd.Prepare();
cmd.ExecuteNonQuery();
trans.Commit();
}
}
}
catch
{
throw;
}
}
private static void DeleteConfigurationDB()
{
string connectionString = ConfigurationManager.AppSettings.Get("StorageProviderConnection");
string commandText = "Delete tConfiguration";
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
SqlCeCommand command = new SqlCeCommand(commandText, connection, transaction);
command.CommandType = CommandType.Text;
command.Prepare();
command.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlCeException exception)
{
transaction.Rollback();
throw exception;
}
finally
{
connection.Close();
}
}
}
}
public void FillBinCode(String aBinCode)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("UPDATE ActivityLine SET BinCode = @aBinCode " +
"WHERE BinCode = N''", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@aBinCode", aBinCode));
sqlCommand.ExecuteNonQuery();
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public int ExecuteSQL(string[] sql)
{
try
{
ConcurrentBag <int> rowCount = new ConcurrentBag <int>();
using (SqlCeTransaction trans = conn.BeginTransaction())
{
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
try
{
foreach (var item in sql)
{
cmd.CommandText = item;
int count = cmd.ExecuteNonQuery();
rowCount.Add(count);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
}
}
return(rowCount.Sum());
}
catch { return(-1); }
}
public void InsertDocumentNumbers(XmlResponseGetDocNumbersResult aResultGetDocNumbers)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO WarehouseReceipt(No) " +
"VALUES (@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@No";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aResultGetDocNumbers.Documents.Count; k++)
{
sqlCommand.Parameters["@No"].Value = aResultGetDocNumbers.Documents.Document(k).No;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
private async Task <bool> ExecuteSql(string connectionString)
{
using (SqlCeConnection sqlCeConnection = new SqlCeConnection(connectionString))
{
sqlCeConnection.Open();
using (SqlCeTransaction sqlCeTransaction = sqlCeConnection.BeginTransaction())
{
string sqlData = await SqlEngineProvider.GetSqlDataAsync();
string[] sqlStatements = sqlData.Split(_sqlSeparator, StringSplitOptions.RemoveEmptyEntries);
foreach (var sqlStatement in sqlStatements)
{
using (SqlCeCommand sqlCeCommand = sqlCeConnection.CreateCommand())
{
sqlCeCommand.Transaction = sqlCeTransaction;
sqlCeCommand.CommandText = sqlStatement;
sqlCeCommand.ExecuteNonQuery();
}
}
sqlCeTransaction.Commit();
}
return(true);
}
}
protected override int SaveImpl(string commandString, params T[] models)
{
int count = 0;
using (SqlCeConnection connection = (SqlCeConnection)GetConnection())
{
connection.Open();
using (SqlCeTransaction transaction = connection.BeginTransaction())
using (SqlCeCommand command = new SqlCeCommand("", connection, transaction))
{
try
{
models.ForEach(m =>
{
SaveModel(command, m);
count++;
});
transaction.Commit();
}
catch
{
if (transaction != null && connection.State != System.Data.ConnectionState.Closed)
{
count = 0;
transaction.Rollback();
}
throw;
}
}
}
return(count);
}
/// <summary>
/// Upgrades the database to the current version.
/// </summary>
/// <param name="user">The user.</param>
/// <param name="currentVersion">The current version of the database.</param>
/// <returns>
/// [true] if success.
/// </returns>
/// <remarks>
/// Documented by AAB, 30.04.2009.
/// </remarks>
internal static bool UpgradeDatabase(IUser user, Version currentVersion)
{
using (SqlCeConnection connection = GetConnection(user))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlCeTransaction upgradeTransaction = connection.BeginTransaction();
try
{
if (currentVersion == new Version(1, 0, 0))
{
UpgradeDatabase_100_101(connection);
currentVersion = new Version(1, 0, 1);
}
if (currentVersion == new Version(1, 0, 1))
{
UpgradeDatabase_101_102(connection);
currentVersion = new Version(1, 0, 2);
}
ApplyIndicesToDatabase(connection);
upgradeTransaction.Commit(CommitMode.Immediate);
return(true);
}
catch (Exception)
{
upgradeTransaction.Rollback();
}
}
return(false);
}
private static async Task <bool> InsertImagesAsync(string connectionString)
{
string sqlCommand = await SqlEngineProvider.GetImageInsertCommandAsync();
using (SqlCeConnection sqlCeConnection = new SqlCeConnection(connectionString))
{
sqlCeConnection.Open();
using (SqlCeTransaction sqlCeTransaction = sqlCeConnection.BeginTransaction())
{
// dark souls3
byte[] darkSouls3Resource = await ImageResource.GetDarkSouls3ImageResourceAsync();
ExecuteSqlCommandWithParameters(DarkSouls3GameName, darkSouls3Resource, sqlCeConnection, sqlCommand, sqlCeTransaction);
// Sekiro
byte[] sekiroResource = await ImageResource.GetSekiroImageResourceAsync();
ExecuteSqlCommandWithParameters(SekiroGameName, sekiroResource, sqlCeConnection, sqlCommand, sqlCeTransaction);
sqlCeTransaction.Commit();
return(true);
}
}
}
public bool Action(Order objorder)
{
bool ret = false;
SqlCeTransaction tx = order.DBase.Connection.BeginTransaction(); //Добавлять уровень блокировок и транзакции при чтении
order.DBase.Transaction = tx;
//order.DBase.Transaction.
///IsolationLevel.
try
{
// Изменить запись Заказа
order.DBase.CommandText = "UPDATE " + order.TableName + " Set NDock=@NDockOrder, Summ=@SummOrder, Status=@StatusOrder," +
" NameType=@NameTypeInOrder, Perc=@PercOrder " +
" where Id=@IdOrder; \r\n";
order.DBase.Parameters["IdOrder"].Value = objorder.Id;
order.DBase.Parameters["NDockOrder"].Value = objorder.NuberDockument;
order.DBase.Parameters["SummOrder"].Value = objorder.Summ;
order.DBase.Parameters["StatusOrder"].Value = objorder.Status;
order.DBase.Parameters["NameTypeInOrder"].Value = objorder.NameTypeOrder;
order.DBase.Parameters["PercOrder"].Value = objorder.Percent;
int n = order.DBase.ExecuteNonQuery();
// Удалить все старые поля
pole.DBase.CommandText = "DELETE " + pole.TableName + " Where IdOrder=@IdOrderOrderPole;\r\n";
pole.DBase.Parameters["IdOrderOrderPole"].Value = objorder.Id;
n = pole.DBase.ExecuteNonQuery();
// Сохранить новые поля
for (int i = 0; i < objorder.ListPole.Count; i++)
{
objorder.ListPole[i].IdOrder = objorder.Id;
pole.DBase.CommandText = "INSERT INTO " + pole.TableName + " (IdOrder,Number,NamePole,Text)" +
" VALUES (@IdOrderOrderPole,@NumberOrderPole,@NamePoleOrderPole,@TextPoleOrderPole);\r\n";
pole.DBase.Parameters["IdOrderOrderPole"].Value = objorder.ListPole[i].IdOrder;
pole.DBase.Parameters["NumberOrderPole"].Value = objorder.ListPole[i].Number;
pole.DBase.Parameters["NamePoleOrderPole"].Value = objorder.ListPole[i].NamePole;
pole.DBase.Parameters["TextPoleOrderPole"].Value = objorder.ListPole[i].Text;
n = pole.DBase.ExecuteNonQuery();
pole.DBase.CommandText = "SELECT @@IDENTITY;";
int r = Convert.ToInt32(pole.DBase.ExecuteScalar());
objorder.ListPole[i].Id = r;
}
ret = true;
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
MessageBox.Show(e.Message, "Внимание!");
}
finally
{
order.DBase.Transaction = null;
}
return(ret);
}
public bool Action(int id)
{
bool ret = false;
SqlCeTransaction tx = typeorder.DBase.Connection.BeginTransaction();
typeorder.DBase.Transaction = tx;
try
{
// Удаляем все поля
pole.DBase.CommandText = "DELETE " + pole.TableName + " Where IdTypeOrder=@IdTypeOrder;\r\n";
pole.DBase.Parameters["IdTypeOrder"].Value = id;
int i = pole.DBase.ExecuteNonQuery();
// Удаляем запись типа заказа
typeorder.DBase.CommandText = "DELETE " + typeorder.TableName + " Where Id=@IdType;\r\n";
typeorder.DBase.Parameters["IdType"].Value = id;
i = typeorder.DBase.ExecuteNonQuery();
ret = true;
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
MessageBox.Show(e.Message, "Внимание!");
}
finally
{
typeorder.DBase.Transaction = null;
}
return(ret);
}
/// <summary>
/// Запуск запита, який не вертає результат(UPDATE,INSERT)
/// </summary>
/// <param name="parSQL">Рядок з запитом</param>
/// <returns>Кількість змінених рядків</returns>
public int ExecuteNonQuery(string parSQL)
{
int varRez = 0;
varCodeLastErrror = 0;
varStrLstErrror = "";
try
{
using (varTransaction = varSqlConnect.BeginTransaction())
{
using (varCommand = new SqlCeCommand(parSQL, varSqlConnect, varTransaction))
{
foreach (SqlCeParameter par in varListSqlParameter)
{
varCommand.Parameters.Add(par);
}
varRez = varCommand.ExecuteNonQuery();
varTransaction.Commit();
return(varRez);
}
}
}
catch (Exception e)
{
varCodeLastErrror = 1;
varStrLstErrror = e.Message;
}
return(varRez);
}
/// <summary>
/// Sets a single property value for a media object.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="property">The property.</param>
/// <param name="value">The value.</param>
/// <remarks>Documented by Dev02, 2008-08-07</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public void SetPropertyValue(int id, MLifter.DAL.Interfaces.MediaProperty property, string value)
{
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
SqlCeTransaction tran = cmd.Connection.BeginTransaction();
if (GetPropertyValue(id, property) == null)
{
cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);";
}
else
{
cmd.CommandText = "UPDATE MediaProperties SET value=@value WHERE media_id=@media_id AND property=@property;";
}
Dictionary <MediaProperty, string> properties = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.MediaProperties, id)] as Dictionary <MediaProperty, string>;
if (properties == null)
{
properties = new Dictionary <MediaProperty, string>();
}
properties[property] = value;
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.MediaProperties, id, new TimeSpan(1, 0, 0))] = properties;
cmd.Parameters.Add("@media_id", id);
cmd.Parameters.Add("@property", property.ToString());
cmd.Parameters.Add("@value", value);
MSSQLCEConn.ExecuteNonQuery(cmd);
tran.Commit();
}
/// <summary>
/// Sets the properties for a media object.
/// </summary>
/// <param name="id">The id of the media object.</param>
/// <param name="properties">The properties for the media object.</param>
/// <remarks>Documented by Dev03, 2008-08-05</remarks>
/// <remarks>Documented by Dev03, 2009-01-13</remarks>
public void SetProperties(int id, Dictionary <MLifter.DAL.Interfaces.MediaProperty, string> properties)
{
SqlCeCommand cmd1 = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
SqlCeTransaction tran = cmd1.Connection.BeginTransaction();
cmd1.CommandText = "DELETE FROM MediaProperties WHERE media_id=@id;";
cmd1.Parameters.Add("@id", id);
MSSQLCEConn.ExecuteNonQuery(cmd1);
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "INSERT INTO MediaProperties (media_id, property, value) VALUES (@media_id, @property, @value);";
cmd.Parameters.Add("@media_id", SqlDbType.Int, 4);
cmd.Parameters.Add("@property", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@value", SqlDbType.NVarChar, 100);
foreach (KeyValuePair <MediaProperty, string> item in properties)
{
cmd.Parameters["@media_id"].Value = id;
cmd.Parameters["@property"].Value = item.Key.ToString();
cmd.Parameters["@value"].Value = item.Value;
MSSQLCEConn.ExecuteNonQuery(cmd);
}
tran.Commit();
}
/// <summary>
/// Todo１データを削除する
/// </summary>
/// <param name="id">対象ToDoのId</param>
public void DeleteToDo(int id)
{
try
{
using (SqlCeTransaction trans = this.con.BeginTransaction())
{
using (SqlCeCommand cmd = this.con.CreateCommand())
{
string sql = "UPDATE [ToDo] SET [deleted] = 1, [deletedtime] = @deletedtime WHERE [id] = @id;";
cmd.CommandText = sql;
cmd.Parameters.Add("id", System.Data.DbType.Int32);
cmd.Parameters.Add("deletedtime", System.Data.DbType.DateTime);
cmd.Parameters["id"].Value = id;
cmd.Parameters["deletedtime"].Value = DateTime.Now.ToString("yyyy-MM-dd");
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
catch
{
throw;
}
}
//сохраняет лог
public void SaveLog(ref ScanLog slog)
{
string insertCommandText = @" INSERT INTO ScanLog(ArtCode, id_gamma, Dt, Qty, Barcode, ActionType)
VALUES(@artcode, @id_gamma, @Dt, @Qty, @Barcode, @ActionType) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
SqlCeTransaction tx = connect.BeginTransaction();
SqlCeCommand command = connect.CreateCommand();
command.CommandText = insertCommandText;
command.Parameters.Add("artcode", SqlDbType.Int).Value = slog.ArtCode;
command.Parameters.Add("id_gamma", SqlDbType.Int).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("Dt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Parameters.Add("Qty", SqlDbType.Int).Value = slog.Qty;
command.Parameters.Add("Barcode", SqlDbType.NVarChar).Value = slog.Barcode;
command.Parameters.Add("ActionType", SqlDbType.Int).Value = (int)slog.ActType;
command.Transaction = tx;
try
{
int rowsAffected = command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
tx.Rollback();
}
}
}
public void CommitTransaction()
{
CommitMode m_Mode = (InmediateCommit ? CommitMode.Immediate : CommitMode.Deferred);
m_Transaction.Commit(m_Mode);
DisposeTransaction();
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="sql">执行sql</param>
public void addOneData(String sql)
{
if (sql == null || sql == "")
{
return;
}
con.Open();
SqlCeTransaction tx = con.BeginTransaction();
SqlCeCommand cmd = con.CreateCommand();
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
}
finally
{
cmd.Dispose();
con.Close();
}
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Boolean</returns>
public Boolean ExecuteCommand(ExecuteObject sender)
{
Boolean flag;
using (SqlCeConnection connection = new SqlCeConnection(this.ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand())
{
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
using (SqlCeTransaction transaction = command.Connection.BeginTransaction())
{
try
{
command.Transaction = transaction;
int num = command.ExecuteNonQuery();
transaction.Commit();
flag = num >= 0;
}
catch (Exception ex)
{
transaction.Rollback();
throw new ApplicationException(ex.Message);
}
finally { }
}
}
}
return(flag);
}
public void SupprimerContenuTable(String table)
{
SqlCeTransaction transaction = connection.BeginTransaction();
SqlCeCommand myCommand_delete = connection.CreateCommand();
myCommand_delete.Transaction = transaction;
try
{
myCommand_delete.CommandText = "DELETE FROM " + table;
myCommand_delete.ExecuteNonQuery();
//myCommand_delete.CommandText = "DBCC CHECKIDENT (" + table + ", reseed, 1)";
//myCommand_delete.ExecuteNonQuery();
//myCommand_delete.CommandText = "ALTER TABLE " + table + " AUTO_INCREMENT=0";
//myCommand_delete.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
/// <summary>
/// Sets the chapter for a card.
/// </summary>
/// <param name="id">The card id.</param>
/// <param name="chapter">The chapter id.</param>
/// <remarks>Documented by Dev03, 2008-08-06</remarks>
/// <remarks>Documented by Dev08, 2009-01-09</remarks>
public void SetChapter(int id, int chapter)
{
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
SqlCeTransaction transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "SELECT count(*) FROM \"Chapters\" WHERE id=@chapterid";
cmd.Parameters.Add("chapterid", chapter);
if (Convert.ToInt32(MSSQLCEConn.ExecuteScalar(cmd)) < 1)
{
throw new IdAccessException(chapter);
}
Dictionary <int, int> cardChapterCache = parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.CardChapterList, 0)] as Dictionary <int, int>;
if (cardChapterCache != null)
{
cardChapterCache[id] = chapter;
}
using (SqlCeCommand cmd2 = MSSQLCEConn.CreateCommand(parent.CurrentUser))
{
cmd2.CommandText = "DELETE FROM \"Chapters_Cards\" WHERE cards_id=@id; ";
cmd2.CommandText += "INSERT INTO \"Chapters_Cards\" (chapters_id, cards_id) VALUES (@chapterid, @id);";
cmd2.CommandText += "UPDATE Cards SET chapters_id=@chapterid WHERE id=@id;";
cmd2.Parameters.Add("@chapterid", chapter);
cmd2.Parameters.Add("@id", id);
MSSQLCEConn.ExecuteNonQuery(cmd2);
}
transaction.Commit();
}
}
private void AddFilesInternal(List <string> files, Action <string> UpdateAction)
{
List <string> filesInDB = new List <string>();
List <string> filesNotInDB = new List <string>();
ComputeDiffsWithDB(files, ref filesInDB, ref filesNotInDB, UpdateAction);
SqlCeTransaction transaction = m_connection.BeginTransaction();
int c = filesNotInDB.Count;
for (int i = 0; i < c; i++)
{
if (m_bStop)
{
break;
}
FileInfo fi = new FileInfo(filesNotInDB[i]);
string fileName = fi.Name;
string filePath = fi.DirectoryName;
UpdateAction.Invoke($"Adding file {i}/{c} {filesNotInDB[i]} ");
string fileExtension = fi.Extension;
string fileChecksum = "";
DateTime fileEpoch = fi.CreationTime;
long fileSize = fi.Length;
SqlCeCommand cmd = new SqlCeCommand($@"INSERT INTO Files (name, path, size, checksum, extension, epoch) VALUES (@name, @path, @size, @checksum, @extension, @epoch)", m_connection);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar).Value = fileName;
cmd.Parameters.Add("@path", System.Data.SqlDbType.NVarChar).Value = filePath;
cmd.Parameters.Add("@size", System.Data.SqlDbType.BigInt).Value = fileSize;
cmd.Parameters.Add("@checksum", System.Data.SqlDbType.NVarChar).Value = fileChecksum;
cmd.Parameters.Add("@extension", System.Data.SqlDbType.NVarChar).Value = fileExtension;
cmd.Parameters.Add("@epoch", System.Data.SqlDbType.DateTime).Value = fileEpoch;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
private void UpdateExistingCompanies(List <Company> toUpdate)
{
SqlCeConnection connection = database_.GetConnection();
using (var cmd = new SqlCeCommand("UPDATE CompanyInfo SET TaxAuthority = @tax WHERE CompanyID = @id", connection))
{
cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
foreach (Company item in toUpdate)
{
cmd.Parameters[0].Value = item.tax;
cmd.Parameters[1].Value = item.id;
cmd.ExecuteNonQuery();
}
transaction.Commit();
textBoxMessage.AppendText("\n成功更新" + toUpdate.Count + "条已有公司信息");
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.AppendText("\n更新已有公司信息失败。详细信息：" + ex.Message);
}
}
}
private void InsertNewCompanies(List <Company> toInsert)
{
SqlCeConnection connection = database_.GetConnection();
using (var cmd = new SqlCeCommand("INSERT INTO CompanyInfo VALUES (?, ?, ?, ?, ?, ?)", connection))
{
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
cmd.Parameters.Add("@name", SqlDbType.NVarChar);
cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
cmd.Parameters.AddWithValue("@union", DBNull.Value);
cmd.Parameters.AddWithValue("@system", DBNull.Value);
cmd.Parameters.AddWithValue("@industry", DBNull.Value);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
foreach (Company item in toInsert)
{
cmd.Parameters[0].Value = item.id;
cmd.Parameters[1].Value = item.name;
cmd.Parameters[2].Value = item.tax;
cmd.ExecuteNonQuery();
}
transaction.Commit();
textBoxMessage.Text = "成功添加" + toInsert.Count + "条新公司信息";
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.Text = "添加新公司信息失败。详细信息：" + ex.Message;
}
}
}
// обновляет orderItem | возвращает количество затронутіх записей
public int UpdateScan(Scan scan)
{
string updateCommandText = @" UPDATE scan SET Qty = @qty, EndDt = @EndDt WHERE ArtCode = @artcode and id_gamma = @id_gamma ";
int rowsAffected = 0;
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
SqlCeTransaction tx = connect.BeginTransaction();
SqlCeCommand command = connect.CreateCommand();
command.CommandText = updateCommandText;
command.Parameters.Add("artcode", SqlDbType.Int).Value = scan.ArtCode;
command.Parameters.Add("id_gamma", SqlDbType.Int).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("qty", SqlDbType.Int).Value = scan.Qty;
command.Parameters.Add("EndDt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Transaction = tx;
try
{
rowsAffected = command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
return(0);
}
}
return(rowsAffected);
}
public void RecordIsUpdatedWhenCommandTextAndCommandTypeWithCommittedTransaction()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
commandString = "Select ItemDescription, Price from Items order by ItemID";
dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
trans.Commit();
conn.Close();
trans.Dispose();
conn.Dispose();
Assert.AreEqual <int>(3, dsActualResult.Tables[0].Rows.Count, "Mismatch in number of rows in the returned dataset. Problem with the test data or with the execute dataset.");
Assert.AreEqual("Digital Image Pro", dsActualResult.Tables[0].Rows[0][0].ToString().Trim());
Assert.AreEqual("38.95", dsActualResult.Tables[0].Rows[0][1].ToString().Trim());
Assert.AreEqual("Excel 2003", dsActualResult.Tables[0].Rows[1][0].ToString().Trim());
Assert.AreEqual("5000", dsActualResult.Tables[0].Rows[1][1].ToString().Trim());
Assert.AreEqual("Infopath", dsActualResult.Tables[0].Rows[2][0].ToString().Trim());
Assert.AreEqual("89", dsActualResult.Tables[0].Rows[2][1].ToString().Trim());
}
/// <summary>
/// Commits the currently active Transaction (if any).
/// </summary>
public void CommitTran()
{
if ((oConn.State == ConnectionState.Open) && blnTranActive)
{
oTran.Commit();
blnTranActive = false;
}
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
}
Dispose();
}
public override void CommitTransaction()
{
if (_transaction != null)
{
_transaction.Commit();
}
GetConnectionBase().EndUse();
}
public bool deleteDocCheckProduct(string _DCode)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProducts where DCode ='"+_DCode+"'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProductDetails where DCode ='" + _DCode + "'");
string sqldelete;
sqldelete = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqldelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool editNumberCheckProduct(string _id,string _dcode,string _pcode,int numberedit)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update DocCheckProductDetails ");
sb.Append(" set NumProduct ="+numberedit+"");
sb.Append(" where DCode ='"+_dcode+"'");
sb.Append(" and PCode='"+_pcode+"'");
sb.Append(" and ID='" + _id + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveDocCheckProduct(DocCheckProduct docCheckProduct)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProducts(DCode,Warehouse,Location,CreateDate)");
sb.Append(" VALUES (@DCode,@Warehouse,@Location,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProduct.Code;
com.Parameters.Add("@Warehouse", SqlDbType.NVarChar).Value = docCheckProduct.Warehouse;
com.Parameters.Add("@Location", SqlDbType.NVarChar).Value = docCheckProduct.Location;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool updateProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update Products ");
sb.Append(" set Name ='" + product.Name + "',");
sb.Append(" Code ='" + product.Code + "',");
sb.Append(" BarCode ='" + product.BarCode + "',");
sb.Append(" Unit ='" + product.Unit + "',");
sb.Append(" Price1 ='" + product.Price1 + "',");
sb.Append(" Price3 ='" + product.Price3 + "',");
sb.Append(" Price4 ='" + product.Price4 + "',");
sb.Append(" CreateDate ='" + DatetimeUtil.convertToDateThai2(product.CreateDate.ToString("dd/MM/yyyy")) + "'");
sb.Append(" where BarCode ='" + product.BarCode + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO Products(Name,Code,BarCode,Unit,Price1,Price3,Price4,CreateDate)");
sb.Append(" VALUES (@Name,@Code,@BarCode,@Unit,@Price1,@Price3,@Price4,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = product.Name;
com.Parameters.Add("@Code", SqlDbType.NVarChar).Value = product.Code;
com.Parameters.Add("@BarCode", SqlDbType.NVarChar).Value = product.BarCode;
com.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = product.Unit;
com.Parameters.Add("@Price1", SqlDbType.NVarChar).Value = product.Price1;
com.Parameters.Add("@Price3", SqlDbType.NVarChar).Value = product.Price3;
com.Parameters.Add("@Price4", SqlDbType.NVarChar).Value = product.Price4;
com.Parameters.Add("@CreateDate", SqlDbType.NVarChar).Value = product.CreateDate;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveDocCheckProductDetail(DocCheckProductDetail docCheckProductDetail)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProductDetails(DCode,PCode,PName,PUnit,NumProduct,CreateDate)");
sb.Append(" VALUES (@DCode,@PCode,@PName,@PUnit,@NumProduct,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProductDetail.DocCheckProduct.Code;
com.Parameters.Add("@PCode", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.BarCode;
com.Parameters.Add("@PName", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Name;
com.Parameters.Add("@PUnit", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Unit;
com.Parameters.Add("@NumProduct", SqlDbType.NVarChar).Value = docCheckProductDetail.NumProduct;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}