protected override void BeforeEachExample()
{
base.BeforeEachExample();
dbConnection = new SqlCeConnection(DatabaseIntegrationSetup.TestDBConnectionString);
dbConnection.Open();
transaction = dbConnection.BeginTransaction();
}
public static void DeleteAllFileNames(SqlCeTransaction transaction, SqlCeConnection conn)
{
SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandDeleteAllFolderNames, conn);
command.Transaction = transaction;
command.Connection = conn;
command.ExecuteNonQuery();
}
public SqlCeCommand CreateAddProductCommand(SqlCeConnection conn, SqlCeTransaction transaction)
{
var cmd = new SqlCeCommand(_sql, conn, transaction);
CreateParameters(cmd);
return cmd;
}
public void commitTransaction()
{
if ((con != null) && (trans != null) && (con.State == System.Data.ConnectionState.Open))
{
trans.Commit();
trans = null;
}
}
/// <summary>
/// Starts a Transaction session on the current connection.
/// </summary>
public void BeginTran()
{
if (oConn.State == ConnectionState.Open)
{
oTran = oConn.BeginTransaction();
blnTranActive = true;
}
}
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 static void DeletAllRespectiveCapturePointsForTextConversion(int captureEventId, SqlCeConnection conn, SqlCeTransaction transaction)
{
//pointRecId
int value = 0;
SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandRemoveAllCapturePointsByRecIdTextConv, conn);
command.Transaction = transaction;
command.Parameters.Add("@pointRecId", captureEventId);
value = Convert.ToInt32(command.ExecuteNonQuery());
}
protected DataBaseCompactConnection()
{
connectionConfig = new Encrypter().DecryptText(
ConfigurationManager.ConnectionStrings["RopSqlConnStr"].ConnectionString);
cultureAcronym = ConfigurationManager.AppSettings["RopSqlCulture"];
connection = new SqlCeConnection(connectionConfig);
transactionControl = null;
}
/// <summary>
/// Starts a Transaction using the Connection instance
/// </summary>
/// <returns>
/// The <see cref="SqlCeTransaction" />.
/// </returns>
public SqlCeTransaction BeginTransaction()
{
if (this.transaction != null)
{
throw new InvalidOperationException(
"A transaction has already been started. Only one transaction is allowed");
}
this.transaction = EntityBase.Connection.BeginTransaction();
this.UsersDB.Transaction = this.transaction;
return this.transaction;
}
public static void SaveReplacementEventForTextConversion(ReplacementEvent replacementEvent, SqlCeConnection conn, SqlCeTransaction transaction)
{
SqlCeCommand command = new SqlCeCommand(Advanced_Replacements_TextConv_SQL.commandUpdateReplacementByIdTextConv, conn);
command.Transaction = transaction;
command.Parameters.Add("@name", replacementEvent.name);
command.Parameters.Add("@description", replacementEvent.description);
command.Parameters.Add("@value", replacementEvent.Value);
command.Parameters.Add("@typeId", replacementEvent.typeId);
command.Parameters.Add("@capturePointId", replacementEvent.capturePointId);
command.Parameters.Add("@userId", replacementEvent.userId);
command.Parameters.Add("@usageCount", replacementEvent.usageCount);
command.Parameters.Add("@id", replacementEvent.id);
command.ExecuteNonQuery();
}
public static void beginTransactionCe()
{
try
{
if ((sqlCeConn.State == ConnectionState.Open))
{
transacaoCe = sqlCeConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
catch (Exception ex)
{
throw new Exception("Ocorre um problema na conexão com a base de dados." + Constants.vbCrLf + "Erro : " + ex.Message);
}
}
public static void InsertFolderName(int index, string folderName, object parentId, string generatedID, SqlCeTransaction transaction, SqlCeConnection conn)
{
try {
SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandInsertFolderName, conn);
command.Parameters.Add("@id", index);
command.Parameters.Add("@folderName", folderName);
command.Parameters.Add("@parentId", parentId);
command.Parameters.Add("@generatedID", generatedID);
command.Transaction = transaction;
command.Connection = conn;
command.ExecuteNonQuery();
} catch (Exception ex) {
CommonUtils.LogError(ex.Message, ex);
}
}
public static void InsertUpdatedFileNames(List<TreeNode> treeNodes, SqlCeTransaction transaction, SqlCeConnection conn)
{
try {
for (int i = 0; i < treeNodes.Count; i++) {
int parentId = treeNodes[i].Parent == null ? -1 : treeNodes.IndexOf(treeNodes[i].Parent);
if (parentId != -1) {
InsertFolderName(i, treeNodes[i].Text, parentId, treeNodes[i].Tag.ToString(), transaction, conn);
} else {
InsertFolderName(i, treeNodes[i].Text, DBNull.Value, treeNodes[i].Tag.ToString(), transaction, conn);
}
}
} catch (Exception ex) {
CommonUtils.LogError(ex.Message,ex);
}
}
public static void commit()
{
try
{
if (tx != null)
{
tx.Commit();
tx = conexao.BeginTransaction();
}
}
catch (Exception err)
{
throw err;
}
}
public static void rollback()
{
try
{
if (tx != null)
{
tx.Rollback();
tx = conexao.BeginTransaction();
}
}
catch (Exception err)
{
throw err;
}
}
private void OpenConnection(SqlCeConnection aSqlCeConnection, SqlCeTransaction aSqlCeTransaction)
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT s.Id_Session, s.Session, s.[DateTime] FROM Session AS s", aSqlCeConnection, aSqlCeTransaction))
{
sqlCommand.CommandType = CommandType.Text;
using (SqlCeDataReader rdr = sqlCommand.ExecuteReader())
{
if (rdr.Read())
{
FId_Session = (Int32)rdr["Id_Session"];
FSession = (Guid)rdr["Session"];
FDateTime = (DateTime)rdr["DateTime"];
}
}
}
}
/// <summary>
/// A SqlCeConnection extension method that executes the reader operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A SqlCeDataReader.</returns>
public static SqlCeDataReader SqlCeExecuteReader(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
return command.ExecuteReader();
}
}
/// <summary>
/// A SqlCeConnection extension method that executes the non query operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
public static void ExecuteNonQuery(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
command.ExecuteNonQuery();
}
}
public static int InserNewCategory(ComparisonCategory comparisonCategory, SqlCeTransaction transaction, SqlCeConnection conn)
{
SqlCeCommand command = new SqlCeCommand(Env_Comparison_Categories_SQL.commandInsertNewEnvComparisonCategory, conn);
command.Transaction = transaction;
command.Parameters.Add("@id", comparisonCategory.categoryId);
command.Parameters.Add("@name", comparisonCategory.categoryName);
command.Parameters.Add("@description", comparisonCategory.categoryDescription);
command.Parameters.Add("@path", comparisonCategory.categoryPath);
if (comparisonCategory.categoryParentId == -1) {
//DBNull.Value
command.Parameters.Add("@parentId", DBNull.Value);
} else {
command.Parameters.Add("@parentId", comparisonCategory.categoryParentId);
}
int numberAffectedRows = Convert.ToInt32(command.ExecuteNonQuery());
return numberAffectedRows;
}
public int Excluir(SqlCeTransaction bdTrans)
{
//string sqlUpdateEstoque = "";
//Código temporariamente comentado por estar com erro 19/03/2009
// foreach (GradeItem gi in LstGradeItem)
// {
// //Recupera a quantidade do estoque
// string sqlPedidoOriginalQuantidade = @"
// select
// quantidade
// from
// item_pedido_grade
// where
// id_pedido=" + Globals.Pedido.Id + " and id_produto=" + Produto.Id +
// @" and id_grade = " + Produto.IdGrade.V +
// @" and id_item_grade = " + gi.IdItemGrade +
// @" and id_atributo = " + gi.IdAtributo +
// @" and id_item_atributo = " + gi.IdItemAtributo;
// IntN quantidadeOriginal = Globals.Bd.IntN(sqlPedidoOriginalQuantidade, bdTrans);
// if (quantidadeOriginal.Iniciada)
// {
// sqlUpdateEstoque = @"
// update
// saldo_grade
// set
// estoque=estoque + " + quantidadeOriginal.V + @"
// where
// id_produto=" + Produto.Id +
// @" and id_grade = " + Produto.IdGrade.V +
// @" and id_item_grade = " + gi.IdItemGrade +
// @" and id_atributo = " + gi.IdAtributo +
// @" and id_item_atributo = " + gi.IdItemAtributo;
// Globals.Bd.ExecuteNonQuery(sqlUpdateEstoque, bdTrans);
// }
// }
int rf;
string sqlDeleteItemGrade = @"
delete from
item_pedido_grade
where id_produto=" + Produto.Id + " and id_pedido='" + Produto.Pedido.Id + "'";
rf = D.Bd.ExecuteNonQuery(sqlDeleteItemGrade, bdTrans);
return rf;
}
public static void SetPrimaryKey(SqlCeTransaction trans, SqlCeRowUpdatedEventArgs e)
{
// If this is an INSERT operation...
if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey;
// and a primary key PK column exists...
if (pk != null && pk.Length == 1)
{
//Set up the post-update query to fetch new @@Identity
SqlCeCommand cmdGetIdentity = new SqlCeCommand("SELECT @@IDENTITY", (SqlCeConnection)trans.Connection, trans);
//Execute the command and set the result identity value to the PK
e.Row[pk[0]] = Convert.ToInt32(cmdGetIdentity.ExecuteScalar());
e.Row.AcceptChanges();
}
}
}
public Command(string queryString)
{
this._connection = new SqlCeConnection(_connectionString);
this.SqlCommand = new SqlCeCommand(queryString, this._connection);
this.SqlCommand.Transaction = this._transaction;
try
{
this._connection.Open();
this._transaction = this._connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
catch (Exception ex)
{
if (this._transaction != null)
{
this._transaction.Rollback();
}
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable<dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
using (IDataReader reader = command.ExecuteReader())
{
return reader.ToExpandoObjects();
}
}
}
public override bool Save(SqlCeTransaction tr)
{
bool res = true;
try
{
//save this
switch (Status)
{
case Status.Added:
if (string.IsNullOrWhiteSpace(Id))
this.Id = Generator.GenerateID(CConstants.COLL);
res = AddValue(this, tr);
Status = Common.Interfaces.Status.Normal;
break;
case Status.Normal:
break;
case Status.Updated:
res = UpdateValue(this);
Status = Common.Interfaces.Status.Normal;
break;
case Status.Deleted:
res = DeleteValue(this.Id);
break;
default:
Status = Common.Interfaces.Status.Normal;
break;
}
}
catch (Exception ex)
{
res = false;
}
if (res)
{
Status = Common.Interfaces.Status.Normal;
}
return res;
}
/// <summary>
/// A SqlCeConnection extension method that executes the data set operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A DataSet.</returns>
public static DataSet ExecuteDataSet(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
var ds = new DataSet();
using (var dataAdapter = new SqlCeDataAdapter(command))
{
dataAdapter.Fill(ds);
}
return ds;
}
}
public static int InsertCapturePointsAsTransactionForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList, SqlCeConnection conn, SqlCeTransaction transaction)
{
int returnCode = -1;
List<int> capturePointsIds = new List<int>();
for (int i = 0; i < customNodesList.Count; i++) {
SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn);
command.Transaction = transaction;
//@pointText, @pointUsedAttributes, @pointParentNode, @pointUsedAttribValues, @pointRecId
command.Parameters.Add("@pointText", customNodesList[i].Text);
command.Parameters.Add("@pointUsedAttributes", BackEndUtils.GetUsedAttributes(customNodesList[i].customizedAttributeCollection));
command.Parameters.Add("@pointParentNode", (customNodesList[i].Parent == null ? "" : customNodesList[i].Parent.Text));
command.Parameters.Add("@pointUsedAttribValues", BackEndUtils.GetUsedAttributesValues(customNodesList[i].customizedAttributeCollection));
command.Parameters.Add("@pointRecId", RecommendationId);
command.Parameters.Add("@Level", customNodesList[i].Level);
command.Parameters.Add("@ItemIndex", customNodesList[i].Index);
command.Parameters.Add("@parentLevel", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Level);
command.Parameters.Add("@parentIndex", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Index);
returnCode = Convert.ToInt32(command.ExecuteNonQuery());
SqlCeCommand commandMaxId = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandMaxCapturePointIdTextConv, conn);
capturePointsIds.Add(Convert.ToInt32(commandMaxId.ExecuteScalar()));
}
return returnCode;
}
/// <summary>
/// A SqlCeConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static T ExecuteScalarAs <T>(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, SqlCeTransaction transaction)
{
return(@this.ExecuteScalarAs <T>(cmdText, parameters, CommandType.Text, transaction));
}
/// <summary>
/// A SqlCeConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static T ExecuteScalarAs <T>(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
return((T)command.ExecuteScalar());
}
}
/// <summary>
/// A SqlCeConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static T ExecuteScalarAs <T>(this SqlCeConnection @this, string cmdText, CommandType commandType, SqlCeTransaction transaction)
{
return(@this.ExecuteScalarAs <T>(cmdText, null, commandType, transaction));
}
/// <summary>
/// A SqlCeConnection extension method that executes the expando object operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A dynamic.</returns>
public static dynamic ExecuteExpandoObject(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
{
using (SqlCeCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
using (IDataReader reader = command.ExecuteReader())
{
reader.Read();
return(reader.ToExpandoObject());
}
}
}
private void DisposeTransaction()
{
m_Transaction.Dispose();
m_Transaction = null;
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, SqlCeTransaction transaction)
{
return(@this.ExecuteExpandoObjects(cmdText, parameters, CommandType.Text, transaction));
}
public SQLCETransaction(ISQLDatabase database, IDbConnection connection, SqlCeTransaction transaction)
{
_database = database;
_connection = connection;
_transaction = transaction;
}
/// <summary>
/// A SqlCeConnection extension method that executes the data set operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A DataSet.</returns>
public static DataSet ExecuteDataSet(this SqlCeConnection @this, string cmdText, SqlCeTransaction transaction)
{
return @this.ExecuteDataSet(cmdText, null, CommandType.Text, transaction);
}
/// <summary>
/// UpdateCustomer
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool UpdateCustomer(Customer item)
{
var sql = @"UPDATE Customer
SET Name = @Name
,Id = @Id
,Phone = @Phone
,IM = @IM
,Remark = @Remark
,Discount = @Discount
,UpdateTime = @UpdateTime
WHERE Id = @Id";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Name", SqlDbType.NVarChar, 100)
{
Value = item.Name
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = item.Id
},
new SqlCeParameter("Phone", SqlDbType.NVarChar, 100)
{
Value = item.Phone
},
new SqlCeParameter("IM", SqlDbType.NVarChar, 150)
{
Value = item.IM
},
//new SqlCeParameter("Deposit", SqlDbType.Decimal) { Value = item.Deposit }, 注意去掉了 sql 的 Deposit
new SqlCeParameter("Remark", SqlDbType.NVarChar, 300)
{
Value = item.Remark
},
new SqlCeParameter("Discount", SqlDbType.Int)
{
Value = item.Discount
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = item.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
/// <summary>
/// TopUpCustomerDeposit
/// </summary>
/// <param name="Id"></param>
/// <param name="Deposit"></param>
/// <returns></returns>
public static bool TopUpCustomerDeposit(string Id, decimal Deposit)
{
var sql = @"UPDATE Customer SET Deposit = (Deposit + @Deposit) WHERE Id = @Id";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = Id
},
new SqlCeParameter("Deposit", SqlDbType.Decimal)
{
Value = Deposit
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
sql = @"INSERT INTO DepositDetail (CustomerId,Detail,ItemSalesId,UpdateTime)
VALUES (@CustomerId,@Detail,@ItemSalesId,@UpdateTime)";
command = conn.CreateCommand();
parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = Id
},
new SqlCeParameter("Detail", SqlDbType.NVarChar, 200)
{
Value = "增值---" + Deposit + " 元"
},
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ""
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
/// <summary>
/// A SqlCeConnection extension method that executes the non query operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
public static void ExecuteNonQuery(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, SqlCeTransaction transaction)
{
@this.ExecuteNonQuery(cmdText, parameters, CommandType.Text, transaction);
}
/// <summary>
/// InsertItemSales
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static bool InsertItemSales(List <ItemSales> list)
{
var sql = @"INSERT INTO ItemSales(ItemSalesId,ItemMasterId,ItemName,CustomerId,SalesType,SalesCount,SoldPirce,UpdateTime)
VALUES (@ItemSalesId,@ItemMasterId,@ItemName,@CustomerId,@SalesType,@SalesCount,@SoldPirce,@UpdateTime)";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
//var ItemSalesId = Guid.NewGuid().ToString();
var ItemSalesId = DateTime.Now.ToString("yyyyMMddHHmmssfff");
list.ForEach(s =>
{
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ItemSalesId
},
new SqlCeParameter("ItemMasterId", SqlDbType.NVarChar, 50)
{
Value = s.Id
},
new SqlCeParameter("ItemName", SqlDbType.NVarChar, 250)
{
Value = s.ItemName
},
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = s.CustomerId ?? ""
},
new SqlCeParameter("SalesType", SqlDbType.Int)
{
Value = s.SalesType
},
new SqlCeParameter("SalesCount", SqlDbType.Int)
{
Value = s.SalesCount
},
new SqlCeParameter("SoldPirce", SqlDbType.Decimal)
{
Value = s.SoldPirce
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
});
var cusid = list.FirstOrDefault().CustomerId;
if (!string.IsNullOrEmpty(cusid))
{
sql = "UPDATE Customer SET Deposit = @Deposit, UpdateTime = @UpdateTime WHERE Id = @Id";
var deposit = list.FirstOrDefault().DepositForUpdate - list.Sum(s => s.SoldPirce);
if (deposit < 0)
{
deposit = 0;
}
;
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Deposit", SqlDbType.Decimal)
{
Value = deposit
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = cusid
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
sql = @"INSERT INTO DepositDetail (CustomerId,Detail,ItemSalesId,UpdateTime)
VALUES (@CustomerId,@Detail,@ItemSalesId,@UpdateTime)";
command = conn.CreateCommand();
parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = cusid
},
new SqlCeParameter("Detail", SqlDbType.NVarChar, 200)
{
Value = "消费---消费前还有 " + list.FirstOrDefault().DepositForUpdate + " 元，消费了 " + list.Sum(s => s.SoldPirce) + " 元，还剩 " + deposit + " 元"
},
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ItemSalesId
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
}
sql = @"UPDATE ItemMaster SET StockCount = (StockCount - @SalesCount), UpdateTime = @UpdateTime WHERE Id = @Id";
list.ForEach(s =>
{
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("SalesCount", SqlDbType.Int)
{
Value = s.SalesCount
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = s.Id
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
});
tx.Commit();
ret = true;
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
//public static bool Insert(string sql)
//{
// var ret = false;
// using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
// {
// conn.Open();
// SqlCeCommand command = new SqlCeCommand(sql, conn);
// ret = command.ExecuteNonQuery() > 0;
// conn.Close();
// }
// return ret;
//}
/// <summary>
/// InsertItemMaster
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public static bool InsertItemMaster(ItemMaster item)
{
var sql = @"INSERT INTO ItemMaster(ItemId,ItemName,ItemSize,ItemType,StockCount,StockPrice,Price,Id,UpdateTime,Color)
VALUES (@ItemId,@ItemName,@ItemSize,@ItemType,@StockCount,@StockPrice,@Price,@Id,@UpdateTime,@Color)";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("ItemId", SqlDbType.NVarChar, 100)
{
Value = item.ItemId
},
new SqlCeParameter("ItemName", SqlDbType.NVarChar, 250)
{
Value = item.ItemName
},
new SqlCeParameter("ItemSize", SqlDbType.Int)
{
Value = item.ItemSize
},
new SqlCeParameter("ItemType", SqlDbType.Int)
{
Value = item.ItemType
},
new SqlCeParameter("StockCount", SqlDbType.Int)
{
Value = item.StockCount
},
new SqlCeParameter("StockPrice", SqlDbType.Decimal)
{
Value = item.StockPrice
},
new SqlCeParameter("Price", SqlDbType.Decimal)
{
Value = item.Price
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = item.Id
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = item.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
},
new SqlCeParameter("Color", SqlDbType.NVarChar, 50)
{
Value = item.Color ?? ""
}
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
/// <summary>
/// Create a SqlCeCommand instance using the global SQL CE Connection instance and associate this with a transaction
/// </summary>
/// <param name="transaction">
/// SqlCeTransaction to be used for the SqlCeCommand
/// </param>
/// <returns>
/// The <see cref="SqlCeCommand"/>.
/// </returns>
public static SqlCeCommand CreateCommand(SqlCeTransaction transaction)
{
// TODO come up with a better way to check for the database file
SqlCeCommand command;
if (!File.Exists(ConfigurationSettings.AppSettings["DB_File"]))
{
DatabaseFile.CreateDatabase();
}
command = Connection.CreateCommand();
//try
//{
// command = Connection.CreateCommand();
//}
//catch (SqlCeException notFound)
//{
// if (notFound.Message.Contains("be found"))
// {
// }
// command = Connection.CreateCommand();
//}
command.Transaction = transaction;
return command;
}
/// <summary>
/// A SqlCeConnection extension method that executes the entity operation.
/// </summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A T.</returns>
public static T ExecuteEntity <T>(this SqlCeConnection @this, string cmdText, SqlCeTransaction transaction) where T : new()
{
return(@this.ExecuteEntity <T>(cmdText, null, CommandType.Text, transaction));
}
private static void InsertUpdatedCategories(List<ComparisonCategoryTreeNode> treeNodes, SqlCeTransaction transaction, SqlCeConnection conn)
{
for (int i = 0; i < treeNodes.Count; i++) {
int parentId = treeNodes[i].Parent == null ? -1 : treeNodes.IndexOf(treeNodes[i].Parent as ComparisonCategoryTreeNode);
if (parentId != -1) {
ComparisonCategory comparisonCategory = treeNodes[i].comparisonCategory;
comparisonCategory.categoryParentId = parentId;
comparisonCategory.categoryId = i;
InserNewCategory(comparisonCategory, transaction, conn);
// BackEndUtils.InsertFolderName(i, treeNodes[i].Text, parentId, transaction, conn);
} else {
ComparisonCategory comparisonCategory = treeNodes[i].comparisonCategory;
comparisonCategory.categoryParentId = parentId;
comparisonCategory.categoryId = i;
InserNewCategory(comparisonCategory, transaction, conn);
}
}
}
private void InitializeDatabase()
{
string connectionString = ConnectionString;
Debug.AssertStringNotEmpty(connectionString);
string dbFilePath = ConnectionStringHelper.GetDataSourceFilePath(connectionString);
if (File.Exists(dbFilePath))
{
return;
}
using (SqlCeEngine engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand())
{
conn.Open();
SqlCeTransaction transaction = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandText = @"
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ELMAH_Error'";
object obj = cmd.ExecuteScalar();
if (obj == null)
{
cmd.CommandText = @"
CREATE TABLE ELMAH_Error (
[ErrorId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(),
[Application] NVARCHAR(60) NOT NULL,
[Host] NVARCHAR(50) NOT NULL,
[Type] NVARCHAR(100) NOT NULL,
[Source] NVARCHAR(60) NOT NULL,
[Message] NVARCHAR(500) NOT NULL,
[User] NVARCHAR(50) NOT NULL,
[StatusCode] INT NOT NULL,
[TimeUtc] DATETIME NOT NULL,
[Sequence] INT IDENTITY (1, 1) NOT NULL,
[AllXml] NTEXT NOT NULL
)";
cmd.ExecuteNonQuery();
cmd.CommandText = @"
CREATE NONCLUSTERED INDEX [IX_Error_App_Time_Seq] ON [ELMAH_Error]
(
[Application] ASC,
[TimeUtc] DESC,
[Sequence] DESC
)";
cmd.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
/// <summary>
/// Crea un nuevo Comando para el proveedor actual
/// </summary>
/// <param name="cmdText">Cadena con el comando SQL</param>
/// <param name="connection">Conexión a usar</param>
/// <param name="transaction">Transacción a usar</param>
/// <returns>Un nuevo comando</returns>
public override System.Data.SqlServerCe.SqlCeCommand GetNewCommand(string cmdText, SqlCeConnection connection, SqlCeTransaction transaction)
{
return(new SqlCeCommand(cmdText, (SqlCeConnection)connection, (SqlCeTransaction)transaction));
}
/// <summary>
/// InsertCustomer
/// </summary>
/// <param name="customer"></param>
/// <returns></returns>
public static bool InsertCustomer(Customer customer)
{
var sql = @"INSERT INTO Customer(Name,Id,Phone,IM,Deposit,Remark,Discount,UpdateTime)
VALUES (@Name,@Id,@Phone,@IM,@Deposit,@Remark,@Discount,@UpdateTime)";
var ret = false;
using (SqlCeConnection conn = new SqlCeConnection(SQLCONN))
{
SqlCeTransaction tx = null;
try
{
conn.Open();
tx = conn.BeginTransaction();
SqlCeCommand command = conn.CreateCommand();
var parameters = new[]
{
new SqlCeParameter("Name", SqlDbType.NVarChar, 100)
{
Value = customer.Name
},
new SqlCeParameter("Id", SqlDbType.NVarChar, 50)
{
Value = customer.Id
},
new SqlCeParameter("Phone", SqlDbType.NVarChar, 100)
{
Value = customer.Phone ?? ""
},
new SqlCeParameter("IM", SqlDbType.NVarChar, 150)
{
Value = customer.IM ?? ""
},
new SqlCeParameter("Deposit", SqlDbType.Decimal)
{
Value = customer.Deposit
},
new SqlCeParameter("Remark", SqlDbType.NVarChar, 300)
{
Value = customer.Remark ?? ""
},
new SqlCeParameter("Discount", SqlDbType.Int)
{
Value = customer.Discount
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = customer.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
ret = command.ExecuteNonQuery() > 0;
if (customer.Deposit > 0)
{
sql = @"INSERT INTO DepositDetail (CustomerId,Detail,ItemSalesId,UpdateTime)
VALUES (@CustomerId,@Detail,@ItemSalesId,@UpdateTime)";
command = conn.CreateCommand();
parameters = new[]
{
new SqlCeParameter("CustomerId", SqlDbType.NVarChar, 50)
{
Value = customer.Id
},
new SqlCeParameter("Detail", SqlDbType.NVarChar, 200)
{
Value = "增值---" + customer.Deposit + " 元"
},
new SqlCeParameter("ItemSalesId", SqlDbType.NVarChar, 50)
{
Value = ""
},
new SqlCeParameter("UpdateTime", SqlDbType.DateTime)
{
Value = customer.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss")
},
};
command.Parameters.AddRange(parameters);
command.CommandText = sql;
command.ExecuteNonQuery();
}
tx.Commit();
}
catch (Exception ex)
{
tx.Rollback();
}
finally
{
conn.Close();
}
}
return(ret);
}
/// <summary>
/// Función que guarda un ServiceCategoryEntity en la base de datos.
/// </summary>
/// <param name="serviceCategory">ServiceCategoryEntity a guardar</param>
/// <param name="scope">Estructura interna para evitar problemas con referencias circulares</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="serviceCategory"/> no es un <c>ServiceCategoryEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Save(ServiceCategoryEntity serviceCategory, Dictionary <string, IEntity> scope)
{
if (serviceCategory == null)
{
throw new ArgumentException("The argument can't be null");
}
// Crear una clave unica para identificar el objeto dentro del scope interno
string scopeKey = serviceCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "ServiceCategory";
if (scope != null)
{
// Si se encuentra dentro del scope lo retornamos
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Crea un nuevo scope si este no fue enviado
scope = new Dictionary <string, IEntity>();
}
try
{
// Crea una nueva conexion y una nueva transaccion si no hay una a nivel superior
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Verifica si se debe hacer una actualización o una inserción
if (serviceCategory.IsNew || !DataAccessConnection.ExistsEntity(serviceCategory.Id, "ServiceCategory", "idServiceCategory", dbConnection, dbTransaction))
{
commandName = "INSERT INTO [ServiceCategory] (idServiceCategory, IDSERVICE, IDCATEGORY, [TIMESTAMP] ) VALUES( @idServiceCategory, @idService,@idCategory, GETDATE()); ";
}
else
{
isUpdate = true;
commandName = "UPDATE [ServiceCategory] SET idService = @idService, idCategory = @idCategory , timestamp=GETDATE() WHERE idServiceCategory = @idServiceCategory";
}
// Se crea un command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
// Agregar los parametros del command .
SqlCeParameter parameter;
if (!isUpdate && serviceCategory.Id == 0)
{
serviceCategory.Id = DataAccessConnection.GetNextId("idServiceCategory", "ServiceCategory", dbConnection, dbTransaction);
}
parameter = dataAccess.GetNewDataParameter("@idServiceCategory", DbType.Int32);
parameter.Value = serviceCategory.Id;
sqlCommand.Parameters.Add(parameter);
FillSaveParameters(serviceCategory, sqlCommand);
// Ejecutar el command
sqlCommand.ExecuteNonQuery();
scopeKey = serviceCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "ServiceCategory";
// Agregar la entidad al scope actual
scope.Add(scopeKey, serviceCategory);
// Guarda las colecciones de objetos relacionados.
// Guardar objetos relacionados con la entidad actual
// Actualizar
// Cierra la conexión si fue abierta en la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Actualizar los campos new y changed
serviceCategory.IsNew = false;
serviceCategory.Changed = false;
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue inicializada
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
/// <summary>
/// Enumerates execute expando objects in this collection.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// An enumerator that allows foreach to be used to process execute expando objects in this collection.
/// </returns>
public static IEnumerable <dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, CommandType commandType, SqlCeTransaction transaction)
{
return(@this.ExecuteExpandoObjects(cmdText, null, commandType, transaction));
}
/// <summary>
/// Speichert die Person/Gruppe.
/// </summary>
/// <param name="db"></param>
public void Save(DataBase dataBase)
{
SqlCeTransaction trans = dataBase.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
Big3.Hitbase.DataBaseEngine.PersonGroupDataSetTableAdapters.PersonGroupTableAdapter pgta = new Big3.Hitbase.DataBaseEngine.PersonGroupDataSetTableAdapters.PersonGroupTableAdapter(dataBase);
pgta.Transaction = trans;
PersonGroupDataSet.PersonGroupDataTable dt = pgta.GetDataById(id);
PersonGroupDataSet.PersonGroupRow row = null;
bool isNew = false;
int personGroupId = 0;
if (dt.Rows.Count == 1)
{
row = dt[0];
personGroupId = dt[0].PersonGroupID;
}
else
{
row = dt.NewPersonGroupRow();
isNew = true;
}
row.Name = Name;
row.SaveAs = SaveAs;
row.Type = (int)Type;
row.Sex = (int)Sex;
row.Country = Country;
row.BirthDay = Birthday;
row.DayOfDeath = DayOfDeath;
row.ImageFilename = ImageFilename;
row.URL = Homepage;
row.Comment = Comment;
if (isNew)
{
dt.AddPersonGroupRow(row);
}
pgta.Update(dt);
if (isNew)
{
personGroupId = (int)(decimal)dataBase.ExecuteScalar("SELECT @@IDENTITY", trans);
}
// Urls speichern
string sql = string.Format("DELETE FROM Url Where ReferenceID = {0}", personGroupId);
dataBase.ExecuteScalar(sql);
UrlDataSetTableAdapters.UrlTableAdapter urlta = new UrlDataSetTableAdapters.UrlTableAdapter(dataBase);
urlta.Transaction = trans;
UrlDataSet.UrlDataTable urlDataTable = new UrlDataSet.UrlDataTable();
foreach (Url url in Urls)
{
urlDataTable.AddUrlRow(personGroupId, 0, url.UrlType, url.Link);
}
urlta.Update(urlDataTable);
// Jetzt noch die Mitwirkenden der Gruppe
sql = string.Format("DELETE FROM GroupParticipant Where PersonGroupID = {0}", personGroupId);
dataBase.ExecuteScalar(sql);
GroupParticipantDataSetTableAdapters.GroupParticipantTableAdapter participantTableAdapter = new GroupParticipantDataSetTableAdapters.GroupParticipantTableAdapter(dataBase);
participantTableAdapter.Transaction = trans;
GroupParticipantDataSet.GroupParticipantDataTable participantsDataTable = new GroupParticipantDataSet.GroupParticipantDataTable();
foreach (GroupParticipant gp in this.Participants)
{
if (!string.IsNullOrEmpty(gp.Name))
{
int participantId = dataBase.GetPersonGroupByName(gp.Name, true).ID;
int roleId = dataBase.GetRoleByName(gp.Role, true).RoleID;
participantsDataTable.AddGroupParticipantRow(personGroupId, participantId, roleId, gp.Begin == null ? "" : gp.Begin, gp.End == null ? "" : gp.End);
}
}
participantTableAdapter.Update(participantsDataTable);
trans.Commit();
}
private static void DeleteEnvResultsCategories(SqlCeTransaction transaction, SqlCeConnection conn)
{
SqlCeCommand command = new SqlCeCommand(Env_Comparison_Categories_SQL.commandDeleteAllEnvCategories, conn);
command.Transaction = transaction;
command.Connection = conn;
command.ExecuteNonQuery();
}
/// <summary>
/// A SqlCeConnection extension method that executes the expando object operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>A dynamic.</returns>
public static dynamic ExecuteExpandoObject(this SqlCeConnection @this, string cmdText, SqlCeTransaction transaction)
{
return(@this.ExecuteExpandoObject(cmdText, null, CommandType.Text, transaction));
}
public void BeginTransation()
{
m_Transaction = m_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
}
/// <summary>
/// Función que elimina un ServiceCategoryEntity de la base de datos.
/// </summary>
/// <param name="serviceCategory">ServiceCategoryEntity a eliminar</param>
/// <param name="scope">Estructura interna para evitar problemas de referencia circular.</param>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="serviceCategory"/> no es un <c>ServiceCategoryEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public void Delete(ServiceCategoryEntity serviceCategory, Dictionary <string, IEntity> scope)
{
if (serviceCategory == null)
{
throw new ArgumentException("The argument can't be null");
}
try
{
// Abrir una nueva conexión e inicializar una transacción si es necesario
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
// Carga la entidad para garantizar eliminar todos los datos antiguos.
serviceCategory = this.Load(serviceCategory.Id, true);
if (serviceCategory == null)
{
throw new UtnEmallDataAccessException("Error al recuperar datos al intentar eliminar.");
}
// Crea un nuevo command para eliminar
string cmdText = "DELETE FROM [ServiceCategory] WHERE idServiceCategory = @idServiceCategory";
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Agrega los valores de los parametros
SqlCeParameter parameterID = dataAccess.GetNewDataParameter("@idServiceCategory", DbType.Int32);
parameterID.Value = serviceCategory.Id;
sqlCommand.Parameters.Add(parameterID);
// Ejecuta el comando
sqlCommand.ExecuteNonQuery();
// Elimina los objetos relacionados
// Confirma la transacción si se inicio dentro de la función
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Eliminamos la entidad de la lista de entidades cargadas en memoria
inMemoryEntities.Remove(serviceCategory.Id);
// Eliminamos la entidad del scope
if (scope != null)
{
string scopeKey = serviceCategory.Id.ToString(NumberFormatInfo.InvariantInfo) + "ServiceCategory";
scope.Remove(scopeKey);
}
}
catch (DbException dbException)
{
// Anula la transaccion
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Relanza una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue abierta dentro de la Función
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
