/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static int DeleteData(string connectionString, string tableName, string pkName, string pkVal)
{
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where " + pkName + " = :ID");
return(OracleHelper.ExecuteNonQuery(connectionString, CommandType.Text, sb.ToString(), new OracleParameter[] { new OracleParameter(":ID", pkVal) }));
}
/// <summary>
/// 执行Sql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("Oracle执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
/// <summary>
/// 通过Dictionary插入数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static int InsertByHashtable(string connectionString, string tableName, Dictionary <string, string> ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(tableName);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (string key in ht.Keys)
{
sb_prame.Append("," + key);
sp.Append(",:" + key);
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
int _object = OracleHelper.ExecuteNonQuery(connectionString, CommandType.Text, sb.ToString(), OracleHelper.GetParameter(ht));
return(_object);
}
