/// <summary>
/// 摘要：创建存储过程（交换系统）
/// </summary>
/// <param name="TaskName">任务名称</param>
/// <param name="Table">表名</param>
/// <param name="Column">列名</param>
/// <param name="TableKey">主键名</param>
public void CreateLoadProcedure(string TaskName, string Table, string Columns, string TableKey)
{
StringBuilder sbSpSql = new StringBuilder(); //构建存储过程SQL
StringBuilder sbTotalSpSql = new StringBuilder(); //构建整合存储过程SQL
string stCloumns = string.Empty; //列
string stKey = string.Empty;
string stWhereCondition = string.Empty;
string stUpdateCondition = string.Empty;
string stColumn = string.Empty;
//验证是否存在
//表头
sbTotalSpSql.AppendLine(string.Format("Create or Replace PROCEDURE {0}", "P_" + TaskName));
sbTotalSpSql.AppendLine("(");
sbTotalSpSql.AppendLine("Cloumn_Batch Varchar , resultflag out Varchar ");
sbTotalSpSql.AppendLine(")");
sbTotalSpSql.AppendLine("AS Begin");
//循环表
for (int i = 0; i < Table.Split(',').Length; i++)
{
//列集合
stCloumns = Columns.Split(';')[i].ToString();
//where 条件
stWhereCondition = string.Empty;
for (int j = 0; j < TableKey.Split(';')[i].Split(',').Length; j++)
{
if (j == 0)
{
stWhereCondition += string.Format(" {1}=t.{1} ", Table.Split(',')[i], TableKey.Split(';')[i].Split(',')[j]);
}
else
{
stWhereCondition += string.Format(" and {1}=t.{1} ", Table.Split(',')[i], TableKey.Split(';')[i].Split(',')[j]);
}
}
//插入新增数据
sbTotalSpSql.AppendLine(string.Format("insert into {0} ({1}) select {1} from {2} where Cloumn_Batch=:Cloumn_Batch and ColumnFlag='1'; ", Table.Split(',')[i], stCloumns, "T_" + TaskName + "_" + Table.Split(',')[i]));
//删除数据
sbTotalSpSql.AppendLine(string.Format("delete from {0} t where exists (select 1 from {1} where {2} ) and Cloumn_Batch=:Cloumn_Batch and ColumnFlag='3' ;", Table.Split(',')[i], "T_" + TaskName + "_" + Table.Split(',')[i], stWhereCondition));
//修改数据
sbTotalSpSql.AppendLine(string.Format("update {0} t set ({1})=(select {1} from {2} where {3} and Cloumn_Batch=:Cloumn_Batch and ColumnFlag='2') where exists (select 1 from {2} where {3} and Cloumn_Batch=:Cloumn_Batch and ColumnFlag='2') ;"));
}
sbTotalSpSql.AppendLine("resultflag:=1;");
sbTotalSpSql.AppendLine("exception when others then");
sbTotalSpSql.AppendLine(" resultflag:=0;");
sbTotalSpSql.AppendLine(" rollback;");
sbTotalSpSql.AppendLine("End;");
ExecuteSql.ExeComSqlForNonQuery(sbTotalSpSql.ToString());
//sbTotalSpSql.Clear();
}
public bool DeleteSaleDtl(Int32 Sale_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Sale_Detail ");
strSql.Append(" where Sale_ID =" + Sale_ID);
int rows = ExecuteSql.ExeComSqlForNonQuery(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 摘要：创建存储过程（交换系统）
/// </summary>
/// <param name="TaskName">任务名称</param>
/// <param name="Table">表名</param>
/// <param name="Column">列名</param>
/// <param name="TableKey">主键名</param>
public void CreateLoadProcedure(string TaskName, string Table, string Columns, string TableKey)
{
StringBuilder sbSpSql = new StringBuilder(); //构建存储过程SQL
StringBuilder sbTotalSpSql = new StringBuilder(); //构建整合存储过程SQL
string stCloumns = string.Empty; //列
string stKey = string.Empty;
string stWhereCondition = string.Empty;
string stUpdateCondition = string.Empty;
string stColumn = string.Empty;
//验证是否存在
sbTotalSpSql.AppendLine(string.Format("drop PROCEDURE if EXISTS {0}", "P_" + TaskName));//删除存储过程
//头
sbTotalSpSql.AppendLine(string.Format("Create procedure {0} ( in_Cloumn_Batch Varchar(10) ,OUT resultflag Varchar(1) )"));
sbTotalSpSql.AppendLine("Begin");
sbTotalSpSql.AppendLine("START TRANSACTION;");//开启事务
//循环表
for (int i = 0; i < Table.Split(',').Length; i++)
{
//列集合
stCloumns = Columns.Split(';')[i].ToString();
//更新条件集合
stUpdateCondition = string.Empty;
for (int j = 0; j < Columns.Split(';')[i].Split(',').Length; j++)
{
if (j == 0)
{
stUpdateCondition += Columns.Split(';')[i].Split(',')[j] + "=t." + Columns.Split(';')[i].Split(',')[j];
}
else
{
stUpdateCondition += "," + Columns.Split(';')[i].Split(',')[j] + "=t." + Columns.Split(';')[i].Split(',')[j];
}
}
//where 条件
stWhereCondition = string.Empty;
for (int j = 0; j < TableKey.Split(';')[i].Split(',').Length; j++)
{
if (j == 0)
{
stWhereCondition += string.Format(" {0}.{1}=t.{1} ", Table.Split(',')[i], TableKey.Split(';')[i].Split(',')[j]);
}
else
{
stWhereCondition += string.Format(" and {0}.{1}=t.{1} ", Table.Split(',')[i], TableKey.Split(';')[i].Split(',')[j]);
}
}
//插入新增数据
sbTotalSpSql.AppendLine(string.Format("insert into {0} ({1}) select {1} from {2} where Cloumn_Batch=in_Cloumn_Batch and ColumnFlag='1' ", Table.Split(',')[i], stCloumns, "T_" + TaskName + "_" + Table.Split(',')[i]));
//删除数据
sbTotalSpSql.AppendLine(string.Format("delete {0} from {1} t where {2} and Cloumn_Batch=in_Cloumn_Batch and ColumnFlag='3'", Table.Split(',')[i], "T_" + TaskName + "_" + Table.Split(',')[i], stWhereCondition));
//修改数据
sbTotalSpSql.AppendLine(string.Format("update {0} set {1} from {0},{2} t where {3} and Cloumn_Batch=in_Cloumn_Batch and ColumnFlag='2'", Table.Split(',')[i], stUpdateCondition, "T_" + TaskName + "_" + Table.Split(',')[i], stWhereCondition));
}
sbTotalSpSql.AppendLine("COMMIT;");//事务提交
sbTotalSpSql.AppendLine("End");
ExecuteSql.ExeComSqlForNonQuery(sbTotalSpSql.ToString());
//sbTotalSpSql.Clear();
}