/// <summary>
/// 处理分页拼接数据
/// </summary>
/// <param name="Billno">单据编号</param>
/// <param name="strWhere">筛选条件</param>
/// <param name="TableName">处理数据单据体</param>
/// <param name="pageIndex">分页</param>
/// <param name="pageSize">当前分页数</param>
/// <param name="newDict">新增数据</param>
public static bool PageInsert(string Billno, string strWhere, string TableName, int pageIndex, int pageSize, List <Dictionary <string, object> > newDict)
{
string connectionString = "";
if (strWhere.Length < 1)
{
strWhere = " 1=1";
}
else
{
strWhere = strGdseq(TableName, Billno, strWhere);
}
DataTable Res = new DataTable();
if (pageIndex > 1)
{
string strSql = string.Format("SELECT ERP.*,ROWNUM ERPNO FROM {0} ERP WHERE ERP.SEQNO = '{1}' AND ROWNUM <= ({2} - 1)*{3} AND {4}", TableName, Billno, pageIndex, pageSize, strWhere);
Res = DbHelperOra.Query(strSql).Tables[0];
}
else
{
Res = DbHelperOra.Query(string.Format("SELECT ERP.*,ROWNUM ERPNO FROM {0} ERP WHERE 1=2", TableName)).Tables[0];
}
for (int i = 0; i < newDict.Count; i++)
{
DataRow newrow = Res.NewRow();
newrow["ERPNO"] = Res.Rows.Count + 1;
newrow["SEQNO"] = Billno;
foreach (DataColumn dtcol in Res.Columns)
{
try
{
if (newDict[0].ContainsKey(dtcol.ToString()))
{
if (newDict[i][dtcol.ToString()] == null || string.IsNullOrWhiteSpace(newDict[i][dtcol.ToString()].ToString()))
{
newrow[dtcol] = DBNull.Value;
}
else
{
newrow[dtcol] = newDict[i][dtcol.ToString()];
}
}
}
catch
{ }
}
Res.Rows.Add(newrow);
}
//第二部分数据
string strSql2 = string.Format("SELECT * FROM (SELECT ERP.*,ROWNO ERPNO FROM {0} ERP WHERE SEQNO = '{1}' AND {4}) WHERE ERPNO > ({2} + 1)*{3}", TableName, Billno, pageIndex, pageSize, strWhere);
DataTable dt2 = DbHelperOra.Query(strSql2).Tables[0];
foreach (DataRow dr in dt2.Rows)
{
dr["ERPNO"] = Res.Rows.Count + 1;
Res.Rows.Add(dr.ItemArray);
}
//第三部分数据
string strSql3 = string.Format("SELECT ERP.*,ROWNO ERPNO FROM {0} ERP WHERE SEQNO = '{1}' AND NOT EXISTS (SELECT 1 FROM {0} WHERE SEQNO = ERP.SEQNO AND ROWNO = ERP.ROWNO AND {4})", TableName, Billno, pageIndex, pageSize, strWhere);
DataTable dt3 = DbHelperOra.Query(strSql3).Tables[0];
foreach (DataRow dr in dt3.Rows)
{
dr["ERPNO"] = Res.Rows.Count + 1;
Res.Rows.Add(dr.ItemArray);
}
try
{
Res.Columns.Remove("ROWNO");
Res.Columns["ERPNO"].ColumnName = "ROWNO";
}
catch
{
Res.Columns.Remove("ERPNO");
}
List <CommandInfo> cmdList = new List <CommandInfo>();
cmdList.Add(new CommandInfo(string.Format("DELETE {0} WHERE SEQNO='{1}'", TableName, Billno), null));
//cmdList.Add(new CommandInfo(string.Format("DELETE {0} WHERE SEQNO='{1}'", TableName.Substring(0, TableName.Length - 3) + "DOC", Billno), null));
DbHelperOra.ExecuteSqlTran(cmdList);
if (ConfigurationManager.ConnectionStrings["OracleConnString"] != null)
{
connectionString = ConfigurationManager.ConnectionStrings["OracleConnString"].ConnectionString;
}
else
{
throw new Exception(string.Format("从配置文件webconfig中找不到 {0} 的链接字符串配置项!", "OracleConnString"));
}
if (Res.Rows.Count < 1)
{
return(false);
}
#region Oracle.ManagedDataAccess.dll没有bulk方法,替换掉
//using (OracleConnection connection = new OracleConnection(connectionString))
//{
// using (OracleBulkCopy orabulkcopy = new OracleBulkCopy(connectionString, OracleBulkCopyOptions.UseInternalTransaction))
// {
// try
// {
// orabulkcopy.DestinationTableName = TableName;
// for (int i = 0; i < Res.Columns.Count; i++)
// {
// orabulkcopy.ColumnMappings.Add(Res.Columns[i].ColumnName, Res.Columns[i].ColumnName);
// }
// orabulkcopy.WriteToServer(Res);
// }
// catch (System.Exception ex)
// {
// return false;
// }
// }
//}
DataTable tableSchema = ApiClientUtil.GetTableSchema(TableName);
string columnNames = "";
string columnValues = "";
string sql = "";
StringBuilder builder = new StringBuilder();
builder.Append(" BEGIN ");
foreach (DataRow dr in Res.Rows)
{
columnNames = "";
columnValues = "";
foreach (DataColumn dc in tableSchema.Columns)
{
string colType = dc.DataType.ToString();
string colName = dc.ColumnName;
columnNames += colName + ",";
if (dr[colName] == null)
{
columnValues += "null,";
}
else if ("System.String".Equals(colType))
{
columnValues += "'" + dr[colName].ToString() + "',";
}
else if ("System.DateTime".Equals(colType))
{
columnValues += "TO_DATE('" + dr[colName].ToString() + "','mm/dd/yyyy hh24:mi:ss'),";
}
else if ("System.Decimal".Equals(colType))
{
if (!string.IsNullOrWhiteSpace(dr[colName].ToString()))
{
columnValues += dr[colName].ToString() + ",";
}
else
{
columnValues += 0 + ",";
}
}
else if ("System.Int32".Equals(colType))
{
if (!string.IsNullOrWhiteSpace(dr[colName].ToString()))
{
columnValues += dr[colName].ToString() + ",";
}
else
{
columnValues += 0 + ",";
}
}
else
{
columnValues += "'" + dr[colName].ToString() + "',";
}
}
sql = "INSERT INTO " + TableName + " (";
sql += columnNames.TrimEnd(',');
sql += ") VALUES (";
sql += columnValues.TrimEnd(',') + ");";
builder.Append(sql);
}
builder.Append(" END; ");
List <CommandInfo> cmdList2 = new List <CommandInfo>();
cmdList2.Add(new CommandInfo(builder.ToString(), null));
try
{
bool oraResult = DbHelperOra.ExecuteSqlTran(cmdList2);
if (!oraResult)
{
return(false);
}
}
catch
{
return(false);
}
#endregion
return(true);
}
/// <summary>
/// 批量数据插入
/// </summary>
/// <param name="connectionString"></param>
/// <param name="TableName">Oracle里的表名</param>
/// <param name="dt">临时表（包含数据)表结构要和数据库表一致</param>
/// <returns></returns>
public static string ExecuteSqlTranWithSqlBulkCopy(string connectionString, string TableName, DataTable dt, string[] Columns)
{
#region Oracle.ManagedDataAccess.dll没有bulk方法,替换掉
//增加数据验证
//using (OracleConnection connection = new OracleConnection(connectionString))
//{
// using (OracleBulkCopy orabulkcopy = new OracleBulkCopy(connectionString, OracleBulkCopyOptions.UseInternalTransaction))
// {
// try
// {
// orabulkcopy.DestinationTableName = TableName;
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// orabulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
// }
// orabulkcopy.WriteToServer(dt);
// return "";
// }
// catch (System.Exception ex)
// {
// return "FALSE";
// }
// }
//}
#endregion
DataTable tableSchema = ApiClientUtil.GetTableSchema(TableName);
string columnNames = "";
string columnValues = "";
string sql = "";
StringBuilder builder = new StringBuilder();
builder.Append(" BEGIN ");
foreach (DataRow dr in dt.Rows)
{
columnNames = "";
columnValues = "";
foreach (DataColumn dc in tableSchema.Columns)
{
string colType = dc.DataType.ToString();
string colName = dc.ColumnName;
columnNames += colName + ",";
if (dr[colName] == null)
{
columnValues += "null,";
}
else if ("System.String".Equals(colType))
{
columnValues += "'" + dr[colName].ToString() + "',";
}
else if ("System.DateTime".Equals(colType))
{
columnValues += "TO_DATE('" + dr[colName].ToString() + "','mm/dd/yyyy hh24:mi:ss'),";
}
else if ("System.Decimal".Equals(colType))
{
columnValues += dr[colName].ToString() + ",";
}
else if ("System.Int32".Equals(colType))
{
columnValues += dr[colName].ToString() + ",";
}
else
{
columnValues += "'" + dr[colName].ToString() + "',";
}
}
sql = "INSERT INTO " + TableName + " (";
sql += columnNames.TrimEnd(',');
sql += ") VALUES (";
sql += columnValues.TrimEnd(',') + ");";
builder.Append(sql);
}
builder.Append(" END; ");
List <CommandInfo> cmdList = new List <CommandInfo>();
cmdList.Add(new CommandInfo(builder.ToString(), null));
try
{
bool oraResult = DbHelperOra.ExecuteSqlTran(cmdList);
if (oraResult)
{
return("");
}
else
{
return("FALSE");
}
}
catch
{
return("FALSE");
}
}