/// <summary>
/// 将datatable中的数据导入到数据库中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool importFileToDbFromDt(DataTable dt)
{
ArrayList sqlList = new ArrayList();
DBAccess access = new DBAccess();
string tablename = "";
try
{
if (dt != null && dt.Rows.Count > 0)
{
tablename = dt.TableName;
//新增
sqlList = Common.FormatSql(dt, Common.SQLTYPE.insert.ToString(), DataDownLoad_TJ_Para.czList);
if (sqlList != null)
{
access.ExecuteNonQueryBySqlList(sqlList);
}
}
}
catch (Exception ex)
{
ResultStr = ex.Message;
SetControlTextStr(lblMsg, ResultStr, "Error");
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
return(false);
}
return(true);
}
/// <summary>
/// 将datatable中的数据导入到数据库中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool importFileDelete(DataTable dt)
{
ArrayList sqlList = new ArrayList();
DBAccess access = new DBAccess();
string tablename = "";
try
{
if (dt != null && dt.Rows.Count > 0)
{
tablename = dt.TableName;
if (tablename.ToLower().Equals("t_jk_tjry_txm"))
{
//删除数据
string strWhere = "";
if (DataDownLoad_TJ_Para.czList != null && DataDownLoad_TJ_Para.czList.Length > 0)
{
strWhere = "'" + DataDownLoad_TJ_Para.czList.Replace(",", "','") + "'";
}
//修改了此处，将村修改为p_rgid
string deleteSql = string.Format(@" delete from t_jk_tjry_txm
where exists(
select * FROM T_DA_JKDA_RKXZL
where t_jk_tjry_txm.jkdah=T_DA_JKDA_RKXZL.d_grdabh
and T_DA_JKDA_RKXZL.P_RGID in({0})
)", strWhere);
SetControlTextStr(lblMsg, deleteSql, "INFO");
access.ExecuteNonQueryBySql(deleteSql);
}
else
{
//删除数据
string strWhere = "";
if (DataDownLoad_TJ_Para.czList != null && DataDownLoad_TJ_Para.czList.Length > 0)
{
strWhere = "'" + DataDownLoad_TJ_Para.czList.Replace(",", "','") + "'";
}
sqlList = Common.FormatSql(dt, Common.SQLTYPE.delete.ToString(), strWhere);
if (sqlList != null)
{
SetControlTextStr(lblMsg, string.Format(" [{0}]", sqlList[0]), "INFO");
access.ExecuteNonQueryBySqlList(sqlList);
}
}
}
}
catch (Exception ex)
{
SetControlTextStr(lblMsg, ex.Message, "Error");
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
return(false);
}
return(true);
}
/// <summary>
/// 更新状态
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string update_T_JK_SJSC(DataRow dtRow, string type)
{
try
{
ArrayList returnArrayList = new ArrayList();
//删除用sql
string SqlDele = Common.getSql("sql_T_JK_SJSC_delete", " ");
//追加记录用sql
string SqlAdd = Common.getSql("sql_T_JK_SJSC_insert", "");
//删除用sql
//医疗机构编码
SqlDele = SqlDele.Replace("{YLJGBM}", dtRow["YLJGBM"].ToString());
//健康档案号
SqlDele = SqlDele.Replace("{JKDAH}", dtRow["JKDAH"].ToString());
//年度
SqlDele = SqlDele.Replace("{ND}", DateTime.Now.Year.ToString());
//文档类型
SqlDele = SqlDele.Replace("{TJTYPE}", type);
returnArrayList.Add(SqlDele);
//追加用sql ( '{YLJGBM}','{JKDAH}','{XM}','{SFZH}','{ND}','{GZZBM}','{TJSJ}','{CZY}','{TJTYPE}','{ZT}')
//医疗机构编码
SqlAdd = SqlAdd.Replace("{YLJGBM}", dtRow["YLJGBM"].ToString());
//健康档案号
SqlAdd = SqlAdd.Replace("{JKDAH}", dtRow["JKDAH"].ToString());
//姓名
SqlAdd = SqlAdd.Replace("{XM}", dtRow["XM"].ToString());
//身份证号
SqlAdd = SqlAdd.Replace("{SFZH}", dtRow["SFZH"].ToString());
//年度
SqlAdd = SqlAdd.Replace("{ND}", DateTime.Now.Year.ToString());
//上传时间
SqlAdd = SqlAdd.Replace("{SCSJ}", DateTime.Now.ToString("yyyy-MM-dd"));
//操作员
SqlAdd = SqlAdd.Replace("{CZY}", UserInfo.userId);
//文档类型
SqlAdd = SqlAdd.Replace("{TJTYPE}", type);
//体检状态
SqlAdd = SqlAdd.Replace("{ZT}", Common.ZT.确定状态);
returnArrayList.Add(SqlAdd);
DBAccess dbaccess = new DBAccess();
dbaccess.ExecuteNonQueryBySqlList(returnArrayList);
}
catch (Exception ex)
{
return("update_T_JK_SJSC更新错误！" + ex.Message);
}
return("");
}
/// <summary>
/// 将datatable中的数据导入到数据库中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool importFileToDbFromDt(DataTable dt)
{
ArrayList sqlList = new ArrayList();
DBAccess access = new DBAccess();
string tablename = "";
try
{
if (dt != null && dt.Rows.Count > 0)
{
tablename = dt.TableName;
DataTable dtresult_tem = dt.Copy();
//增加列
//YLJGBM，importDate，importUser，nd
DataColumn dc_YLJGBM = new DataColumn("YLJGBM");
dc_YLJGBM.DefaultValue = UserInfo.Yybm;
dtresult_tem.Columns.Add(dc_YLJGBM);
//YLJGBM，importDate，importUser，nd
DataColumn dc_importDate = new DataColumn("importDate");
dc_importDate.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd");
dtresult_tem.Columns.Add(dc_importDate);
//YLJGBM，importDate，importUser，nd
DataColumn dc_importUser = new DataColumn("importUser");
dc_importUser.DefaultValue = UserInfo.userId;
dtresult_tem.Columns.Add(dc_importUser);
//YLJGBM，importDate，importUser，nd
DataColumn dc_nd = new DataColumn("nd");
dc_nd.DefaultValue = DateTime.Now.Year.ToString();
dtresult_tem.Columns.Add(dc_nd);
//新增
sqlList = Common.FormatSql(dtresult_tem, Common.SQLTYPE.insert.ToString(), "");
if (sqlList != null)
{
access.ExecuteNonQueryBySqlList(sqlList);
}
}
}
catch (Exception ex)
{
ResultStr = ex.Message;
SetControlTextStr(lblMsg, ResultStr, "Error");
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
return(false);
}
return(true);
}
/// <summary>
/// 把指定的文件导入到数据库中
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private bool importFileTo(string filePath)
{
DataSet ds = new DataSet();
DataTable dbRowName = new DataTable();
ArrayList sqllist = new ArrayList();
ArrayList sqllist_sqlserver = new ArrayList();
ArrayList sqlList = new ArrayList();
DBAccess access = new DBAccess();
DataTable dt = new DataTable();
string tablename = "";
//string checkBox_lis_str = "";
try
{
ds = getDsFromExcel(filePath);
if (ds != null && ds.Tables.Count > 0)
{
//进度条
//progressBar_xz.Visible = true;
//progressBar_xz.Maximum = ds.Tables.Count+2;
//progressBar_xz.Minimum = 0;
//progressBar_xz.Value = 0;
//遍历下载的所有数据库表
for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
{
//progressBar_xz.Value++;
Application.DoEvents();
//DataTable dt = new DataTable();
dt = ds.Tables[tableIndex].Copy();
tablename = dt.TableName;
if (dt != null && dt.Rows.Count > 0)
{
////修改datatable列名称
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// if (dt.Rows[0][i] != null && dt.Rows[0][i].ToString().Length > 0)
// {
// dt.Columns[i].ColumnName = dt.Rows[0][i].ToString().ToLower();
// }
//}
//删除数据
string sqlwhere = "";
if (czList.Length > 0)
{
sqlwhere = "'" + czList.Replace(",", "','") + "'";
}
sqlList = Common.FormatSql(dt, Common.SQLTYPE.delete.ToString(), sqlwhere);
if (sqlList != null)
{
access.ExecuteNonQueryBySqlList(sqlList);
}
//新增
sqlList = Common.FormatSql(dt, Common.SQLTYPE.insert.ToString(), czList);
if (sqlList != null)
{
access.ExecuteNonQueryBySqlList(sqlList);
}
}
}
//progressBar_xz.Visible = false;
}
}
catch (Exception ex)
{
//progressBar_xz.Visible = false;
MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
return(false);
}
return(true);
}
/// <summary>
/// 将datatable中的数据导入到数据库中
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool importFileDelete(DataTable dt)
{
ArrayList sqlList = new ArrayList();
DBAccess access = new DBAccess();
string tablename = "";
string deleteWhere = "";
string[] deletecolumn = null;
try
{
if (dt != null && dt.Rows.Count > 0)
{
//设定信息正确
if (dtRow != null && dtRow["dbTableName_from"] != null)
{
//数据库表名称
tablename = dtRow["dbTableName_from"].ToString();
//删除条件
deleteWhere = " ";
if (dtRow["deleteBycolumn"] != null && dtRow["deleteBycolumn"].ToString().Length > 0)
{
deletecolumn = dtRow["deleteBycolumn"].ToString().Split(new char[] { ':' });
for (int i = 0; i < deletecolumn.Length; i++)
{
deleteWhere = deleteWhere + " and " + deletecolumn[i] + "='{" + deletecolumn[i] + "}' ";
}
}
if (deletecolumn == null)
{
sqlList.Add(string.Format("delete from {0} where YLJGBM ='{1}' ", tablename, UserInfo.Yybm));
}
else
{
for (int j = 0; j < dt.Rows.Count; j++)
{
string strWhereTem = deleteWhere;
for (int k = 0; k < deletecolumn.Length; k++)
{
strWhereTem = strWhereTem.Replace("{" + deletecolumn[k] + "}", dt.Rows[j][deletecolumn[k]].ToString());
}
sqlList.Add(string.Format("delete from {0} where 1=1 {1} ", tablename, strWhereTem));
}
}
if (sqlList != null)
{
SetControlTextStr(lblMsg, string.Format(" [{0}]", sqlList[0]), "INFO");
access.ExecuteNonQueryBySqlList(sqlList);
}
}
}
}
catch (Exception ex)
{
SetControlTextStr(lblMsg, ex.Message, "Error");
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
return(false);
}
return(true);
}
/// <summary>
/// 把指定的文件导入到数据库中
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private bool updateDataToTable()
{
////excel转换的数据集合
//ds_result = new DataSet();
string tablename = "";
try
{
if (ds_result != null && ds_result.Tables.Count > 0)
{
//计算数据总条数
dataCountAll = 0;
//遍历数据库表,计算数据总条数
for (int tableIndex = 0; tableIndex < ds_result.Tables.Count; tableIndex++)
{
dataCountAll = dataCountAll + ds_result.Tables[tableIndex].Rows.Count;
}
// 进度条控制
this.msgQf = "2";
//dataCountAll = dataCountAll;
dataCountCrrent = 0;
e_load.Value = 1;
this.OnValueChanged(e_load);
//int commitCount = 1000;
DataTable dt_result = ds_result.Tables[0].Clone();
//设定信息正确
if (dtRow != null && dtRow["dbTableName_to"] != null)
{
//数据库表名称
tablename = dtRow["dbTableName_to"].ToString();
}
//string columnFormat = "";
DataTable dt_column_value = new DataTable();
dt_column_value.Rows.Add();
//DataTable dt_column_where = new DataTable();
//dt_column_where.Rows.Add();
string sqlInsert = "";
string sqlInsertText = "";
string sqlInsertvalue = "";
string sqlUpdate = "";
//dt_column_value 同步字段对应
if (dtRow != null && dtRow["columnSet"] != null)
{
//值转换
string[] columnvalueList = dtRow["columnSet"].ToString().Split(new char[] { '|' });
for (int i = 0; i < columnvalueList.Length; i++)
{
string[] columnvalueListName = columnvalueList[i].Split(new char[] { ':' });
dt_column_value.Columns.Add(columnvalueListName[0]);
dt_column_value.Rows[0][columnvalueListName[0]] = columnvalueListName[1];
sqlInsertText = sqlInsertText + "," + columnvalueListName[1];
sqlInsertvalue = sqlInsertvalue + ",'[" + columnvalueListName[0] + "]'";
sqlUpdate = string.Format("{0}, {1}='[{2}]' ", sqlUpdate, columnvalueListName[1], columnvalueListName[0]);
}
}
string sqlWhere = "";
//dt_column_where updateToColumn
if (dtRow != null && dtRow["updateToColumn"] != null)
{
//值转换
string[] columnvalueList = dtRow["updateToColumn"].ToString().Split(new char[] { '|' });
for (int i = 0; i < columnvalueList.Length; i++)
{
string[] columnvalueListName = columnvalueList[i].Split(new char[] { ':' });
dt_column_value.Columns.Add(columnvalueListName[0]);
dt_column_value.Rows[0][columnvalueListName[0]] = columnvalueListName[1];
sqlWhere = string.Format(" {0} and {1}='[{2}]' ", sqlWhere, columnvalueListName[1], columnvalueListName[0]);
sqlInsertText = sqlInsertText + "," + columnvalueListName[1];
sqlInsertvalue = sqlInsertvalue + ",'[" + columnvalueListName[0] + "]'";
}
}
//插入语句
sqlInsert = string.Format(" insert into {0} ( {1},czy,gzz) values ({2},'{3}','{4}') ", tablename, sqlInsertText.Substring(1), sqlInsertvalue.Substring(1), UserInfo.userId, UserInfo.gzz);
//更新语句
sqlUpdate = string.Format(" update {0} set {1},czy='{3}',gzz='{4}' where 1=1 {2} ", tablename, sqlUpdate.Substring(1), sqlWhere, UserInfo.userId, UserInfo.gzz);
//检索语句
string sqlSelect = string.Format(" select * from {0} where 1=1 {1} ", tablename, sqlWhere);
//体检状态
string delete_tjzt = " delete from T_JK_TJZT where YLJGBM='" + UserInfo.Yybm + "' and JKDAH='[jkdabh]' and TJTYPE='1'";
string insert_tjzt = " insert into T_JK_TJZT (YLJGBM,JKDAH,ND,GZZBM,TJSJ,CZY,TJTYPE,ZT) values ('" + UserInfo.Yybm + "','[jkdabh]','" + DateTime.Now.Year.ToString() + "','" + UserInfo.gzz + "','[检查日期]','" + UserInfo.userId + "','1','1')";
ArrayList sqlArrayList_tem = new ArrayList();
DBAccess access = new DBAccess();
//遍历数据库表
for (int tableIndex = 0; tableIndex < ds_result.Tables.Count; tableIndex++)
{
//dt_result.TableName = tablename;
for (int rowIndex = 0; rowIndex < ds_result.Tables[tableIndex].Rows.Count; rowIndex++)
{
dataCountCrrent++;
string sqlInsert_tem = sqlInsert;
string sqlUpdate_tem = sqlUpdate;
string sqlSelect_tem = sqlSelect;
string delete_tjzt_tem = delete_tjzt;
string insert_tjzt_tem = insert_tjzt;
for (int i = 0; i < dt_column_value.Columns.Count; i++)
{
if ("检查日期".Equals(dt_column_value.Columns[i].ColumnName) == false)
{
string replaceStr = "[" + dt_column_value.Columns[i].ColumnName + "]";
sqlInsert_tem = sqlInsert_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
sqlUpdate_tem = sqlUpdate_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
sqlSelect_tem = sqlSelect_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
delete_tjzt_tem = delete_tjzt_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
insert_tjzt_tem = insert_tjzt_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
}
else
{
string replaceStr = "[" + dt_column_value.Columns[i].ColumnName + "]";
string replaceValue = ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString();
if (replaceValue.Length == 0)
{
replaceValue = DateTime.Now.ToString("yyyy-MM-dd");
}
else if (replaceValue.Length == 8)
{
replaceValue = replaceValue.Insert(6, "-").Insert(4, "-");
}
sqlInsert_tem = sqlInsert_tem.Replace(replaceStr, replaceValue);
sqlUpdate_tem = sqlUpdate_tem.Replace(replaceStr, replaceValue);
sqlSelect_tem = sqlSelect_tem.Replace(replaceStr, replaceValue);
delete_tjzt_tem = delete_tjzt_tem.Replace(replaceStr, replaceValue);
insert_tjzt_tem = insert_tjzt_tem.Replace(replaceStr, replaceValue);
}
}
DataTable dt_exist = access.ExecuteQueryBySql(sqlSelect_tem);
if (dt_exist != null && dt_exist.Rows.Count > 0)
{
sqlArrayList_tem.Add(sqlUpdate_tem);
}
else
{
sqlArrayList_tem.Add(sqlInsert_tem);
sqlArrayList_tem.Add(delete_tjzt_tem);
sqlArrayList_tem.Add(insert_tjzt_tem);
}
}
}
if (sqlArrayList_tem.Count > 0)
{
access.ExecuteNonQueryBySqlList(sqlArrayList_tem);
}
}
}
catch (Exception ex)
{
//progressBar_xz.Visible = false;
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
//throw ex;
ResultStatus = "4";
ResultStr = ex.Message;
return(false);
}
return(true);
}
