/// <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> /// Todo1データを削除する /// </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; }