private DataTable BuildTable(ConnEnum connEnum, DataTable dt, string tableName, string Columns, string Titles, string CreateId, string CreateName, string datetimeFormatColumns)
{
DataTable dtNew = new DataTable();
string sql = "select * from " + tableName + " where 1 = 0";
dtNew = SQLHelper.CreateSqlHelper(connEnum).ExecuteDataTable(sql);
string[] Col = Columns.Split(',');
string[] Tit = Titles.Split(',');
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dtNew.NewRow();
row["ID"] = FormulaHelper.CreateGuid();
for (int j = 0; j < Col.Length; j++)
{
var count = 0;
foreach (var column in datetimeFormatColumns.Split(','))
{
if (column == Col[j])
{
count++;
break;
}
}
if (count > 0)
{
if (!string.IsNullOrEmpty(dt.Rows[i][Tit[j]].ToString()))
{
row[Col[j]] = ConvertToDateTime(dt.Rows[i][Tit[j]].ToString());
}
}
else
{
if (!string.IsNullOrWhiteSpace(dt.Rows[i][Tit[j]].ToString()))
{
row[Col[j]] = dt.Rows[i][Tit[j]].ToString();
}
}
}
row["CreateUserID"] = CreateId;
row["CreateUser"] = CreateName;
row["CreateDate"] = DateTime.Now.ToString();
row["ModifyUserID"] = CreateId;
row["ModifyUser"] = CreateName;
row["ModifyDate"] = DateTime.Now.ToString();
dtNew.Rows.Add(row);
}
return(dtNew);
}
/// <summary>
/// Execl导入到数据库
/// </summary>
/// <param name="Path">Execl路径</param>
/// <param name="connEnum">Web.config配置文件中数据库信息的Name；例如：Hr</param>
/// <param name="TableName">表的名称</param>
/// <param name="Columns">表除去ID,CreateUserID,CreateUser,CreateDate,ModifyUserID,ModifyUser,ModifyDate之外的所有字段）,例如：C1,C2</param>
/// <param name="Titles">Execl表头,例如:T1,T2,T3</param>
/// <param name="CreateId">创建人Id</param>
/// <param name="CreateName">创建人名字</param>
/// <param name="datetimeFormatTitles">需要格式化的日期字段</param>
/// <returns></returns>
public DataTable Import(string Path, ConnEnum connEnum, string TableName, string Columns, string Titles, string CreateId, string CreateName, string datetimeFormatColumns, string MainTitle)
{
DataTable dtNew = new DataTable();
try
{
DataTable dt = ReadExcel(Path, MainTitle);
dtNew = BuildTable(connEnum, dt, TableName, Columns, Titles, CreateId, CreateName, datetimeFormatColumns);
SqlBulkCopy SqlCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings[connEnum.ToString()].ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
SqlCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnRowsCopied); //订阅复制完成后的方法,参数是 sqlbulk.NotifyAfter的值
SqlCopy.NotifyAfter = dtNew.Rows.Count;
SqlCopy.DestinationTableName = TableName;
SqlCopy.WriteToServer(dtNew);
_totalCount = dtNew.Rows.Count;
}
catch (Formula.Exceptions.BusinessException ex)
{
_error = ex.Message;
}
return(dtNew);
}
public static SQLHelper CreateSqlHelper(ConnEnum connName)
{
return(CreateSqlHelper(connName.ToString()));
}
/// <summary>
/// 建表
/// </summary>
/// <param name="connEnum">连接字符串</param>
/// <param name="dt">数据源</param>
/// <param name="tableName">要插入的表名</param>
/// <param name="titles">标题</param>
/// <param name="createId">创建人ID</param>
/// <param name="createName">创建人</param>
/// <returns>要复制到数据库的表</returns>
private DataTable buildTable(ConnEnum connEnum, DataTable dt, string tableName, string titles, string createId, string createName)
{
SQLHelper shBase = SQLHelper.CreateSqlHelper(ConnEnum.Base);
SQLHelper shProject = SQLHelper.CreateSqlHelper(connEnum);
string sql = "select * from " + tableName + " where 1 = 0";
DataTable dtNew = shProject.ExecuteDataTable(sql);
string[] tit = titles.Split(',');
for (int i = 0; i < dt.Rows.Count; i++)
{
var workNo = dt.Rows[i]["工号"].ToString();
//if (String.IsNullOrWhiteSpace(workNo))
//{
// continue;
//}
const string sqlBase = "SELECT ID,Name FROM S_A_User WHERE WorkNo='{0}'";
var dtUser = shBase.ExecuteDataTable(String.Format(sqlBase, workNo));
object userId;
object userName;
if (dtUser != null && dtUser.Rows.Count > 0)
{
var curRow = dtUser.Rows[0];
userId = curRow["ID"];
userName = curRow["Name"];
}
else
{
continue;
}
foreach (string t in tit)
{
string qualifyType = "";
switch (t)
{
case "项目负责人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["项目负责人"].ToString()))
{
qualifyType = "ProjectManager";
}
break;
case "专业负责人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["专业负责人"].ToString()))
{
qualifyType = "MajorPrinciple";
}
break;
case "审定人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["审定人"].ToString()))
{
qualifyType = "Approver";
}
break;
case "审核人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["审核人"].ToString()))
{
qualifyType = "Auditor";
}
break;
case "校对人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["校对人"].ToString()))
{
qualifyType = "Collactor";
}
break;
case "设计人":
if (!String.IsNullOrWhiteSpace(dt.Rows[i]["设计人"].ToString()))
{
qualifyType = "Designer";
}
break;
default:
continue;
}
if (qualifyType != "")
{
const string sqlOnly = "SELECT COUNT(ID) FROM S_Z_PROJECTUSERQUALIFY WHERE USERID = '{0}' AND QUALIFYTYPE='{1}'";
object obj = shProject.ExecuteScalar(String.Format(sqlOnly, userId, qualifyType));
if (obj != DBNull.Value && Convert.ToInt32(obj) == 0)
{
var flag = true;
for (int j = 0; j < dtNew.Rows.Count; j++)
{
var curRow = dtNew.Rows[j];
if (curRow["USERID"].ToString() == userId.ToString() && curRow["QUALIFYTYPE"].ToString() == qualifyType)
{
flag = false;
break;
}
}
if (flag)
{
DataRow row = dtNew.NewRow();
row["ID"] = FormulaHelper.CreateGuid();
row["USERID"] = userId;
row["USERNAME"] = userName;
row["QUALIFYTYPE"] = qualifyType;
row["CREATEUSERID"] = createId;
row["CREATEUSER"] = createName;
row["CREATEDATE"] = DateTime.Now.ToString(CultureInfo.InvariantCulture);
row["SORTINDEX"] = 0;
row["ISVALIDATE"] = "是";
dtNew.Rows.Add(row);
}
}
}
}
}
return(dtNew);
}
public static void RegistEntities <T>(ConnEnum conn)
{
_entitiesDic[typeof(T).FullName] = conn.ToString();
}
protected Dictionary <string, object> GetDataDicByID(string tableName, string id, ConnEnum connName = ConnEnum.Market, bool withNolock = false)
{
string sql = withNolock ? String.Format("select * from {0} {2} where ID='{1}'", tableName, id, "with(nolock)")
: String.Format("select * from {0} {2} where ID='{1}'", tableName, id, "");
var db = SQLHelper.CreateSqlHelper(connName);
var dt = db.ExecuteDataTable(sql);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
return(FormulaHelper.DataRowToDic(dt.Rows[0]));
}
}
