/// <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); }