/// <summary>
/// 获取验证实体类
/// </summary>
/// <param name="XmlTwoHandler"></param>
/// <param name="dtXml"></param>
/// <param name="sourceDataTable"></param>
/// <returns></returns>
private TableValidate GetTableValidate(XmlTwoHandler XmlTwoHandler, DataTable dtXml, DataTable sourceDataTable)
{
string pkName = XmlTwoHandler.GetRootNodeAttr("pk");
string fkName = XmlTwoHandler.GetRootNodeAttr("fk");
string dbTableName = XmlTwoHandler.GetRootNodeAttr("tableName");
string dbPkName = string.IsNullOrWhiteSpace(pkName) ? string.Empty : dtXml.Select(string.Format("cn='{0}'", pkName))[0]["InnerText"].ToString();
string dbFkName = string.IsNullOrWhiteSpace(fkName) ? string.Empty : dtXml.Select(string.Format("cn='{0}'", fkName))[0]["InnerText"].ToString();
TableValidate validate = new TableValidate()
{
SourceTable = sourceDataTable,
PK = pkName,
FK = fkName,
DbPK = dbPkName,
DbFK = dbFkName,
DbTableName = dbTableName
};
return(validate);
}
/// <summary>
/// 将需要验证的数据写入到数据库
/// 同时返回写入到数据库的所有临时表的表名
/// </summary>
/// <param name="dicValidate">需要写入的数据源</param>
/// <param name="strErrorInfo">错误信息</param>
/// <returns>所有临时表的表名</returns>
internal bool SqlBulkCopyInsertDataForValidate(Dictionary <string, TableValidate> dicValidate)
{
dicTempTablesName.Clear();
bool isSuccess = true;
string tempTableName = string.Empty;
List <string> sqlStringList = new List <string>();
string strTemp = string.Empty;
//定义表名
Random rdm = new Random(99);
for (int i = 0; i < dicValidate.Count; i++)
{
Thread.Sleep(1);
tempTableName = String.Format("TempImport{0}{1}{2}{3}{4}", DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second, DateTime.Now.Millisecond, rdm.Next(999));
strTemp = string.Format(@"USE tempdb if object_id(N'{0}',N'U') is not null begin drop TABLE {0} end ", tempTableName);
sqlStringList.Add(strTemp);
strTemp = string.Empty;
TableValidate entity = dicValidate.Values.ElementAt(i);
//DataTable dt = entity.SourceTable;
//foreach (DataColumn c in dt.Columns)
//{
if (!string.IsNullOrEmpty(entity.PK))
{
strTemp = string.Format(", [{0}] varchar(8000)", entity.PK);
}
if (!string.IsNullOrEmpty(entity.FK))
{
strTemp += string.Format(", [{0}] varchar(8000)", entity.FK);
}
//}
if (!string.IsNullOrEmpty(strTemp))
{
strTemp = strTemp.Remove(0, 2);
}
strTemp = string.Format("USE tempdb CREATE TABLE {0} ([Id] [int] IDENTITY(2,1) NOT NULL,{1}) ", tempTableName, strTemp);
sqlStringList.Add(strTemp);
dicTempTablesName.Add(dicValidate.Keys.ElementAt(i), tempTableName);
}
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
ExecuteSqlList(sqlStringList, sqlConnection);//批量执行SQL语句
SqlTransaction trans = sqlConnection.BeginTransaction();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, trans))
{
try
{
for (int i = 0; i < dicValidate.Count; i++)
{
sqlBulkCopy.ColumnMappings.Clear();
sqlBulkCopy.DestinationTableName = dicTempTablesName.Values.ElementAt(i);
TableValidate mapEntity = dicValidate.Values.ElementAt(i);
if (!string.IsNullOrEmpty(mapEntity.PK) || !string.IsNullOrEmpty(mapEntity.FK))
{
if (!string.IsNullOrEmpty(mapEntity.PK))
{
sqlBulkCopy.ColumnMappings.Add(mapEntity.PK, mapEntity.PK);
}
if (!string.IsNullOrEmpty(mapEntity.FK))
{
sqlBulkCopy.ColumnMappings.Add(mapEntity.FK, mapEntity.FK);
}
sqlBulkCopy.BatchSize = dicValidate.Values.ElementAt(i).SourceTable.Rows.Count;
if (dicValidate.Values.ElementAt(i).SourceTable != null && dicValidate.Values.ElementAt(i).SourceTable.Rows.Count != 0)
{
sqlBulkCopy.WriteToServer(dicValidate.Values.ElementAt(i).SourceTable);
}
}
}
trans.Commit();
}
catch (SqlException ex)
{
trans.Rollback();
sqlBulkCopy.Close();
isSuccess = false;
}
finally
{
sqlBulkCopy.Close();
sqlConnection.Close();
}
}
}
return(isSuccess);
}
/// <summary>
/// 验证数据看的表数据有效性
/// </summary>
/// <param name="sourceList">验证的表数据集合</param>
/// <param name="dicTempTablesName">所有表与临时表键值对的集合</param>
/// <param name="mainTableName">主表名</param>
/// <returns></returns>
internal DataSet DatabaseDataValidate(Dictionary <string, TableValidate> sourceList, string mainTableName, bool isDeleteExists = false)
{
deleteSql.Clear();
DataSet dsError = new DataSet();
Dictionary <string, string> sqlStringList = new Dictionary <string, string>();
//临时表中的主表
string strTemp = string.Empty;
StringBuilder strSql = new StringBuilder();
string tempMainTableName = dicTempTablesName[mainTableName];
TableValidate entityMain = sourceList[mainTableName];
strSql.Append(" IF(object_id('tempdb..#Temp') is not null) DROP TABLE #Temp CREATE table #Temp( [数据表名] varchar(200),[验证方式] varchar(200),[行号] varchar(200),[错误原因] varchar(200),[主键] varchar(200))");
sqlStringList.Add("create", strSql.ToString());
if (!string.IsNullOrEmpty(entityMain.PK))
{
strSql.Clear();
//主表sheet中主键为空
strSql.AppendFormat(" INSERT into #Temp SELECT '{2}' 表名,'主键为空验证' 验证方式,a.Id 行号,'[{1}]不能为空' 错误原因,{1} 主键 from tempdb..{0} a where isnull({1}, '')='' ", tempMainTableName, entityMain.PK, mainTableName);
//主表sheet的主键重复
strSql.AppendFormat(" INSERT into #Temp SELECT '{2}' 表名,'主键重复验证' 验证方式,a.Id 行号,'[{1}]不能重复' 错误原因,{1} 主键 from tempdb..{0} a where EXISTS(SELECT TOP 1 1 from tempdb..{0} where id<>a.id AND {1} = a.{1})", tempMainTableName, entityMain.PK, mainTableName);
if (isDeleteExists)
{
deleteSql.AppendFormat(" delete a from [{1}] a where EXISTS(SELECT TOP 1 1 from tempdb..{0} where {2} = a.{3} )", tempMainTableName, entityMain.DbTableName, entityMain.PK, entityMain.DbPK, mainTableName);
}
else
{
//根据主键在数据库中验证是否已经存在
strSql.AppendFormat(" INSERT into #Temp SELECT '{4}' 表名,'已存在数据验证' 验证方式,a.Id 行号,'[{2}]在数据库中已存在' 错误原因,{2} 主键 from tempdb..{0} a where EXISTS(SELECT TOP 1 1 from {1} where a.{2} = {3} )", tempMainTableName, entityMain.DbTableName, entityMain.PK, entityMain.DbPK, mainTableName);
}
strSql.Append(" SELECT * FROM #Temp ORDER BY [行号]");
strSql.Append(" TRUNCATE TABLE #Temp");
sqlStringList.Add(mainTableName, strSql.ToString());
}
if (dicTempTablesName.Count > 1)
{
for (int i = 0; i < dicTempTablesName.Count; i++)
{
if (dicTempTablesName.Keys.ElementAt(i) == mainTableName)
{
continue;
}
strSql.Clear();
string sourceTableName = dicTempTablesName.Keys.ElementAt(i);
string tempTableName = dicTempTablesName.Values.ElementAt(i);
TableValidate entity = sourceList[sourceTableName];
//主键不为空的时候
if (!string.IsNullOrEmpty(entity.PK))
{
}
//外键FK不为空的时候
if (!string.IsNullOrEmpty(entity.FK))
{
if (isDeleteExists)
{
deleteSql.Insert(0, string.Format(" delete a from [{1}] a where EXISTS(SELECT TOP 1 1 from tempdb..{0} where {2} = a.{3} )", tempMainTableName, entity.DbTableName, entityMain.PK, entity.DbFK));
}
//子表中的fk在主表中不存在
strSql.AppendFormat(" INSERT into #Temp SELECT '{4}' 表名,'数据关系验证' 验证方式,a.Id 行号,'[{2}]在主表中不存在' 错误原因,{3} 主键 from tempdb..{0} a where not EXISTS(SELECT TOP 1 1 from tempdb..{1} where a.{2} = {3} )", tempTableName, tempMainTableName, entity.FK, entityMain.PK, sourceTableName);
}
//pk和fk都不为空
if (!string.IsNullOrEmpty(entity.FK) && !string.IsNullOrEmpty(entity.PK))
{
//子表sheet中pk主键为空
strSql.AppendFormat(" INSERT into #Temp SELECT '{2}' 表名,'主键为空验证' 验证方式,a.Id 行号,'[{1}和{3}]不能为空' 错误原因,{1} 主键 from tempdb..{0} a where isnull({1}, '')='' or isnull({3}, '')='' ", tempTableName, entity.PK, sourceTableName, entity.FK);
//子表sheet的主键重复
strSql.AppendFormat(" INSERT into #Temp SELECT '{2}' 表名,'主键重复验证' 验证方式,a.Id 行号,'[{1}]+[{3}]不能重复' 错误原因,{1} 主键 from tempdb..{0} a where EXISTS(SELECT TOP 1 1 from tempdb..{0} where id<>a.id AND {1} = a.{1} and {3} = a.{3})", tempTableName, entity.PK, sourceTableName, entity.FK);
if (!isDeleteExists)
{
//根据pk+fk在数据库中验证是否已经存在
strSql.AppendFormat(" INSERT into #Temp SELECT '{6}' 表名,'已存在数据验证' 验证方式,a.Id 行号,'[{2}+{4}]在数据库中已存在' 错误原因,{2}+'+'+{4} 主键 from tempdb..{0} a where EXISTS(SELECT TOP 1 1 from {1} where a.{2} = {3} and a.{4} = {5} )", tempTableName, entity.DbTableName, entity.FK, entity.DbFK, entity.PK, entity.DbPK, sourceTableName);
}
}
strSql.Append(" select * FROM #Temp order BY [行号]");
strSql.Append(" TRUNCATE TABLE #Temp");
sqlStringList.Add(sourceTableName, strSql.ToString());
}
}
sqlStringList.Add("end", "DROP TABLE #Temp");
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
for (int n = 0; n < sqlStringList.Count; n++)
{
string strsql = sqlStringList.Values.ElementAt(n);
if (strsql.Trim().Length > 1)
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
SqlCommand cmd = new SqlCommand()
{
Connection = sqlConnection, CommandText = strsql
};
if (n == 0 || n == sqlStringList.Count - 1)
{
cmd.ExecuteNonQuery();
//sqlConnection.Close();
}
else
{
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables[0] != null)
{
DataTable dt = ds.Tables[0].Copy();
if (dt.Rows.Count > 0)
{
dt.TableName = sqlStringList.Keys.ElementAt(n);
dsError.Tables.Add(dt);
}
}
}
//sqlConnection.Close();
}
}
}
}
catch (Exception ex)
{
SqlCommand cmd = new SqlCommand()
{
Connection = sqlConnection, CommandText = sqlStringList.Values.ElementAt(sqlStringList.Count - 1)
};
cmd.ExecuteNonQuery();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
//finally
//{
// using (SqlConnection conn = new SqlConnection(connectionString))
// {
// conn.Open();
// try
// {
// SqlCommand cmd = new SqlCommand() { Connection = conn, CommandText = sqlStringList.Values.ElementAt(sqlStringList.Count - 1) };
// cmd.ExecuteNonQuery();
// conn.Close();
// }
// catch
// {
// conn.Close();
// conn.Dispose();
// }
// }
//}
}
return(dsError);
}
/// <summary>
/// 从数据源中获取有效的数据集
/// </summary>
/// <param name="dtList">数据源</param>
/// <param name="xmlPath">XML文件路径</param>
/// <param name="mainTableName">数据源中的主表名</param>
/// <param name="isDeleteExists">是否删除数据库中已存在的数据</param>
/// <returns>有效的数据集信息</returns>
public DataSet ValidateData(string xmlPath, string mainTableName, bool isDeleteExists)
{
if (null == this._allData)
{
return(null);
}
#region 2、验证是否有主表
//如果主表名为空就把第一个数据表作为主表
if (string.IsNullOrEmpty(mainTableName))
{
mainTableName = this._allData.Tables[0].TableName;
}
#endregion
#region 3、数据验证
/*
* 1. 主表中的主键在sheet中重复
* 2. 所有表中主键pk或主外键组合的PK+FK为空的数据忽略，并做提示
* 6. 根据主键在数据库中验证是否已经存在
* 2. 子表中的fk在主表中不存在
* 3. 子表中的fk+pk在sheet中重复
* =====以上数据库处理======
*
* 4. 每个表需要进行数据格式验证的列中的数据是否符合正则
* 5. 每个表的代码列将文本替换为代码（如浙江省替换为330000）
*/
//读取xml,保存每个表的基础信息
for (int i = 0; i < this._allData.Tables.Count; i++)
{
XmlTwoHandler XmlTwoHandler = new XmlTwoHandler(string.Format(@"{0}\{1}.xml", xmlPath, this._allData.Tables[i].TableName));
_dicXmls.Add(this._allData.Tables[i].TableName, new XmlTwoHandler(String.Format("{0}\\{1}.xml", xmlPath, this._allData.Tables[i].TableName))); //获取XMl
DataTable dtXml = XmlTwoHandler.GetAllData();
TableValidate validate = GetTableValidate(XmlTwoHandler, dtXml, this._allData.Tables[i]);
_dicValidate.Add(this._allData.Tables[i].TableName, validate);
}
if (_dicValidate.Count > 0 && _dicValidate.Keys.Contains(mainTableName))
{
//todo:判断sheet中的主表是否有主键列， 判断从表中有没有主键列和外键列
//如果主表主键不为空时，才进行数据库数据的匹配验证
if (!string.IsNullOrEmpty(_dicValidate[mainTableName].PK))
{
//执行数据库验证
if (!_importValidate.SqlBulkCopyInsertDataForValidate(_dicValidate))
{
//执行数据库临时表写入失败
DataRow dr = this._errorInfo.NewRow();
dr[0] = "数据库临时表";
dr[1] = "写入比对数据";
dr[3] = "写入比对数据失败";
this._errorInfo.Rows.Add(dr);
}
else
{
//插入数据库临时表成功后执行数据库已存在记录的比对验证
DataSet dsError = _importValidate.DatabaseDataValidate(_dicValidate, mainTableName, isDeleteExists);
if (dsError.Tables.Count > 0)
{
for (int i = 0; i < dsError.Tables.Count; i++)
{
this._errorInfo.Merge(dsError.Tables[i]);
}
}
}
}
//如果数据库验证通过,则开始做正则验证
if (this._errorInfo.Rows.Count == 0)
{
for (int i = 0; i < this._allData.Tables.Count; i++)
{
XmlTwoHandler XmlTwoHandler = new XmlTwoHandler(string.Format(@"{0}\{1}.xml", xmlPath, this._allData.Tables[i].TableName));
DataTable dtXml = XmlTwoHandler.GetAllData();
Dictionary <string, string> dicRegex = GetDicRegexData(dtXml); //获取xml配置表中所有需要验证正则的数据 <"样本数据","001">
Dictionary <string, Dictionary <string, string> > dicPath = GetDicXmlPathData(dtXml); //获取xml配置表中所有需要通过path获取的对应xml文件数据<列名，<cn名,值>>
//如果有需要进行正则验证的列则进行验证,否则跳过
if (dicRegex.Count > 0 || dicPath.Count > 0)
{
ValidateDataTableData(this._allData.Tables[i], dicRegex, dicPath);
}
}
}
}
#endregion
return(this._allData); //2013.11.27 by sang 暂时返回的是全部数据
}
