/// <summary> /// 新增記錄 /// </summary> /// <param name="dr"></param> public virtual void Add(DataRow dr) { tf = new TableFieldInfo(SysModel.TableName, this.cnKey); DataTable dtCol = tf.dtFields; StringBuilder strInsert = new StringBuilder(); strInsert.Append("insert into " + SysModel.TableName + "("); string strFiledName1 = ""; string strFiledName2 = ""; SqlParameter[] parameters = new SqlParameter[dtCol.Rows.Count]; for (int i = 0; i < dtCol.Rows.Count; i++) { parameters[i] = new SqlParameter("@" + dtCol.Rows[i]["Name"].ToString(), tf.GetColType(dtCol.Rows[i]["Name"].ToString()), tf.GetTypeLength(dtCol.Rows[i]["Name"].ToString())); parameters[i].Value = dr[dtCol.Rows[i]["Name"].ToString()]; if (dtCol.Rows[i]["colstat"].ToString() != "1") { if (strFiledName1.Length == 0) { strFiledName1 = dtCol.Rows[i]["Name"].ToString(); strFiledName2 = "@" + dtCol.Rows[i]["Name"].ToString(); } else { strFiledName1 += "," + dtCol.Rows[i]["Name"].ToString(); strFiledName2 += ",@" + dtCol.Rows[i]["Name"].ToString(); } } } strInsert.Append(strFiledName1); strInsert.Append(") values( "); strInsert.Append(strFiledName2); strInsert.Append(") "); ia.ExecuteNonQuerySql(strInsert.ToString(), parameters); }
/// <summary> /// 更新資料,可加入自定義查詢條件 /// </summary> /// <param name="dr"></param> /// <param name="oldID"></param> /// <param name="filter"></param> public virtual void Update(DataRow dr, string oldID, string filter) { tf = new TableFieldInfo(SysModel.TableName, this.cnKey); DataTable dtCol = tf.dtFields; StringBuilder strInsert = new StringBuilder(); strInsert.Append("update " + SysModel.TableName + " set "); string strFiledName1 = ""; SqlParameter[] parameters = new SqlParameter[dtCol.Rows.Count + 1]; for (int i = 0; i < dtCol.Rows.Count; i++) { parameters[i] = new SqlParameter("@" + dtCol.Rows[i]["Name"].ToString(), tf.GetColType(dtCol.Rows[i]["Name"].ToString()), tf.GetTypeLength(dtCol.Rows[i]["Name"].ToString())); parameters[i].Value = dr[dtCol.Rows[i]["Name"].ToString()]; if (dtCol.Rows[i]["colstat"].ToString() != "1") { if (strFiledName1.Length == 0) strFiledName1 = dtCol.Rows[i]["Name"].ToString() + " = @" + dtCol.Rows[i]["Name"].ToString(); else strFiledName1 += "," + dtCol.Rows[i]["Name"].ToString() + " = @" + dtCol.Rows[i]["Name"].ToString(); } } strInsert.Append(strFiledName1); strInsert.Append(" where " + filter + " and " + SysModel.KeyField + "=@Old" + SysModel.KeyField + ""); parameters[dtCol.Rows.Count] = new SqlParameter("@Old" + SysModel.KeyField, SqlDbType.NVarChar); parameters[dtCol.Rows.Count].Value = oldID; ia.ExecuteNonQuerySql(strInsert.ToString(), parameters); }
/// <summary> /// 不用Tree_List中的固定Where條件是調用 /// </summary> /// <param name="filter"></param> /// <returns></returns> public virtual DataTable GetRecordNoWhere(string filter) { if (this.cnKey == null) tf = new TableFieldInfo(SysModel.TableName); else tf = new TableFieldInfo(SysModel.TableName, this.cnKey); string strSql = string.Format("select * from {0} where {1}", SysModel.TableName, filter); DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count == 0) { DataRow dr = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { switch (tf.GetColType(dt.Columns[i].ColumnName)) { case SqlDbType.TinyInt: dr[i] = byte.Parse("0"); break; case SqlDbType.Int: dr[i] = int.Parse("0"); break; case SqlDbType.Bit: dr[i] = false; break; case SqlDbType.Decimal: dr[i] = decimal.Parse("0"); break; case SqlDbType.DateTime: //dr[i] = DateTime.Parse("0001/01/01"); break; case SqlDbType.NVarChar: dr[i] = ""; break; } } dt.Rows.Add(dr); } return dt; }
/// <summary> /// 上下筆移動時獲取記錄 /// </summary> /// <param name="move"></param> /// <param name="ID"></param> /// <returns></returns> public virtual DataTable GetRecord(string move, string ID) { if (this.cnKey == null) tf = new TableFieldInfo(SysModel.TableName); else tf = new TableFieldInfo(SysModel.TableName, this.cnKey); string strSql = string.Format("select Top 1 * from {0} where {1} ", SysModel.ViewName, SysModel.strWhere); if (move == "F") strSql += string.Format(" Order by {0}", SysModel.KeyField); else if (move == "P") strSql += string.Format(" and {0}<'{1}' Order by {0} Desc", SysModel.KeyField, ID); else if (move == "N") strSql += string.Format(" and {0}>'{1}' Order by {0}", SysModel.KeyField, ID); else strSql += string.Format(" Order by {0} Desc", SysModel.KeyField); DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; return dt; }
/// <summary> /// 獲取記錄,用原表名,加排序 /// </summary> /// <param name="filter"></param> /// <returns></returns> public virtual DataTable GetOrderByRecord(string filter,string orderby) { if (this.cnKey == null) tf = new TableFieldInfo(SysModel.TableName); else tf = new TableFieldInfo(SysModel.TableName, this.cnKey); string strSql = string.Format("select * from {0} where {1} and {2} and RecordID in (SELECT MAX(RecordID) FROM {0} GROUP BY UseUnit, FileType, FileName)", SysModel.TableName, SysModel.strWhere, filter); if (orderby == "") strSql += string.Format(" Order by {0}", SysModel.OrderField); else strSql += string.Format(" Order by {0}", orderby); DataTable dt = ia.ExecuteDataSetSql(strSql).Tables[0]; if (dt.Rows.Count == 0) { DataRow dr = dt.NewRow(); for (int i = 0; i < dt.Columns.Count; i++) { switch (tf.GetColType(dt.Columns[i].ColumnName)) { case SqlDbType.TinyInt: dr[i] = byte.Parse("0"); break; case SqlDbType.Int: dr[i] = int.Parse("0"); break; case SqlDbType.Bit: dr[i] = false; break; case SqlDbType.Decimal: dr[i] = decimal.Parse("0"); break; case SqlDbType.DateTime: //dr[i] = DateTime.Parse("0001/01/01"); break; case SqlDbType.NVarChar: dr[i] = ""; break; } } dt.Rows.Add(dr); } return dt; }
/// <summary> /// 參考 Web\WebUI\Label\OrderEdit.aspx調用 /// 動態生成 /// init_subColsName1() 初始化明細1 /// init_subColsName2() 初始化明細2 /// ... /// </summary> /// <param name="strctrlID">strctrlID 以sub1xxRowID 為例說明 xx 為 xx 時不區分功能都顯示, 當xx 為 BU 剛 只有BU塊才顯示</param> /// <param name="strTableName">對應明細資料表</param> public void InitSubCols(string FormID, string cnKey, string strctrlID, string strTableName) { // colname,colwidth,type,readonly //Text="(序號) ,40 ,text,1 " //String cstext1 = "var test = '" + subColsName1.Controls.Count + "'"; String cstext1 = "var row1 = new Object();"; String cstext2 = "var row2 = new Object();"; String cstext3 = "var row3 = new Object();"; String cstext4 = ""; //subColsName1.Controls.Count; Js.DAO.TableFieldInfo tf = new Js.DAO.TableFieldInfo(strTableName, cnKey); string[] arr; System.Web.UI.Control ctrl = this.FindControl(strctrlID); for (int i = 0; i < ctrl.Controls.Count; i++) { if (ctrl.Controls[i].ID != null) { arr = ((System.Web.UI.WebControls.Literal)ctrl.Controls[i]).Text.Split(','); if (ctrl.Controls[i].ID.Substring(4, 2).ToLower() != "xx" && ctrl.Controls[i].ID.Substring(4, 2) != Session["UserType"].ToString()) cstext4 = cstext4 + "isShowColumn(\"Detail" + strctrlID.Substring(strctrlID.Length - 1, 1) + "_Col_" + ctrl.Controls[i].ID.Substring(6) + "\", false);"; cstext1 = cstext1 + "row1." + ctrl.Controls[i].ID.Substring(6) + "=\"" + arr[0] + "\";"; cstext2 = cstext2 + "row2." + ctrl.Controls[i].ID.Substring(6) + "=\"" + arr[1] + "\";"; if ("label" == arr[2].Trim().ToLower() || "date" == arr[2].Trim().ToLower() || "datetime" == arr[2].Trim().ToLower()) cstext3 = cstext3 + "row3." + ctrl.Controls[i].ID.Substring(6) + "=\"" + arr[2] + "\";"; //else if ("dropdown" == arr[2].Trim().ToLower()) // cstext3 = cstext3 + "row3." + ctrl.Controls[i].ID.Substring(6) + "=\"" + arr[2] + "\";"; else if ("checkbox" == arr[2].Trim().ToLower() || "dropdown" == arr[2].Trim().ToLower()) cstext3 = cstext3 + "row3." + ctrl.Controls[i].ID.Substring(6) + "=\"type=\\\"" + arr[2] + "\\\" " + (arr[3].Trim() == "1" ? "disabled=\\\"disabled\\\"" : "") + "\";"; else cstext3 = cstext3 + "row3." + ctrl.Controls[i].ID.Substring(6) + "=\"type=\\\"" + arr[2] + "\\\" " + (arr[3].Trim() == "1" ? "readonly=\\\"true\\\"" : "") + " " + (arr[2].ToLower() == "text" && arr[3].Trim() != "1" ? "maxlength=\\\"" + tf.GetTypeLength(ctrl.Controls[i].ID.Substring(6)) + "\\\"" : "") + "\";"; //} } } cstext1 = cstext1 + "tbColHeadText.push(row1);"; cstext2 = cstext2 + "tbColWidth.push(row2);"; cstext3 = cstext3 + "tbColType.push(row3);"; String cstext = " function init_" + strctrlID + "(){ " + cstext1 + cstext2 + cstext3 + " } " + " function isShowColumn_" + strctrlID + "(){ " + cstext4 + " } "; this.Page.ClientScript.RegisterClientScriptBlock(base.GetType(), "subinit", cstext.ToString(), true); }
public void Save(DataTable dt, string strWhere) { try { string TableName = dt.TableName; string strSql = ""; if (!string.IsNullOrEmpty(strWhere)) { strSql = string.Format("delete from {0} where {1}", TableName, strWhere); ia.ExecuteNonQuerySql(strSql); } TableFieldInfo tf = new TableFieldInfo(TableName, cnKey); DataTable dtCol = tf.dtFields; StringBuilder strInsert = new StringBuilder(); strInsert.AppendFormat("insert into {0} (",TableName); string strFiledName = ""; int count = 0; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") strFiledName += dtCol.Rows[i]["Name"].ToString() + ","; else count = 1; } if (strFiledName.Length > 0) strFiledName = strFiledName.Substring(0, strFiledName.LastIndexOf(",")) + ")"; strInsert.Append(strFiledName); strInsert.Append(" values( "); strFiledName = ""; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") strFiledName += "@" + dtCol.Rows[i]["Name"].ToString() + ","; } if (strFiledName.Length > 0) strFiledName = strFiledName.Substring(0, strFiledName.LastIndexOf(",")) + ")"; strInsert.Append(strFiledName); for (int j = 0; j < dt.Rows.Count; j++) { if (dt.Rows[j].RowState == DataRowState.Deleted) //shj continue; SqlParameter[] parameters = new SqlParameter[dtCol.Rows.Count - count]; int k = 0; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") { parameters[k] = new SqlParameter("@" + dtCol.Rows[i]["Name"].ToString(), tf.GetColType(dtCol.Rows[i]["Name"].ToString()), tf.GetTypeLength(dtCol.Rows[i]["Name"].ToString())); if (dt.Rows[j][dtCol.Rows[i]["Name"].ToString()] != null && dt.Rows[j][dtCol.Rows[i]["Name"].ToString()].ToString() != "") { if (dt.Rows[j][dtCol.Rows[i]["Name"].ToString()].GetType().Name == "DateTime") { parameters[k].Value = Common.ReDateTime((DateTime)dt.Rows[j][dtCol.Rows[i]["Name"].ToString()]); if (parameters[k].Value == null) parameters[k].Value = DBNull.Value; } else parameters[k].Value = dt.Rows[j][dtCol.Rows[i]["Name"].ToString()]; } else { switch (tf.GetColType(dtCol.Rows[i]["Name"].ToString())) { case SqlDbType.TinyInt: parameters[k].Value = 0; break; case SqlDbType.Int: parameters[k].Value = 0; break; case SqlDbType.DateTime: parameters[k].IsNullable = true; parameters[k].Value = DBNull.Value; break; case SqlDbType.Bit: parameters[k].Value = false; break; case SqlDbType.Decimal: parameters[k].Value = 0; break; case SqlDbType.SmallInt: parameters[k].Value = 0; break; case SqlDbType.NVarChar: parameters[k].Value = ""; break; default: parameters[k].Value = DBNull.Value; break; } } k++; } } ia.ExecuteNonQuerySql(strInsert.ToString(), parameters); } } catch (Exception e) { throw e; } }
/// <summary> /// 傳入where条件时,顺序为:欄位名称,欄位值。如strwhere[0]欗位名称,strwhere[1]欗位值。 /// </summary> /// <param name="dt"></param> /// <param name="strWhere"></param> public void Save(DataTable dt, object[] strWhere) { try { string TableName = dt.TableName; string strSql = ""; if (!(strWhere == null || strWhere.Length == 0)) { string []str =new string[strWhere.Length/2]; for (int i = 0; i < strWhere.Length / 2; i++) { if (Common.GetObjectType(strWhere[i * 2 + 1]) == SqlDbType.Int || Common.GetObjectType(strWhere[i * 2 + 1]) == SqlDbType.Decimal || Common.GetObjectType(strWhere[i * 2 + 1]) == SqlDbType.Bit) { str[i] = strWhere[i * 2] + "=" + strWhere[i * 2 + 1]; } else { str[i] = strWhere[i * 2] + "='" + strWhere[i * 2 + 1].ToString().Replace("'", "''") + "'"; } } string where = ""; for (int i = 0; i < str.Length; i++) { if (i == 0) where += str[i]; else where += " and " + str[i]; } strSql = string.Format("delete from {0} where {1}", TableName, strWhere); ia.ExecuteNonQuerySql(strSql); } TableFieldInfo tf = new TableFieldInfo(TableName, cnKey); DataTable dtCol = tf.dtFields; StringBuilder strInsert = new StringBuilder(); strInsert.Append("insert into " + TableName + "("); string strFiledName = ""; int count = 0; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") strFiledName += dtCol.Rows[i]["Name"].ToString() + ","; else count = 1; } if (strFiledName.Length > 0) strFiledName = strFiledName.Substring(0, strFiledName.LastIndexOf(",")) + ")"; strInsert.Append(strFiledName); strInsert.Append(" values( "); strFiledName = ""; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") strFiledName += "@" + dtCol.Rows[i]["Name"].ToString() + ","; } if (strFiledName.Length > 0) strFiledName = strFiledName.Substring(0, strFiledName.LastIndexOf(",")) + ")"; strInsert.Append(strFiledName); for (int j = 0; j < dt.Rows.Count; j++) { SqlParameter[] parameters = new SqlParameter[dtCol.Rows.Count-count]; int k = 0; for (int i = 0; i < dtCol.Rows.Count; i++) { if (dtCol.Rows[i]["colstat"].ToString() != "1") { parameters[k] = new SqlParameter("@" + dtCol.Rows[i]["Name"].ToString(), tf.GetColType(dtCol.Rows[i]["Name"].ToString()), tf.GetTypeLength(dtCol.Rows[i]["Name"].ToString())); if (dt.Rows[j][dtCol.Rows[i]["Name"].ToString()] != null && dt.Rows[j][dtCol.Rows[i]["Name"].ToString()].ToString() != "") { if (dt.Rows[j][dtCol.Rows[i]["Name"].ToString()].GetType().Name == "DateTime") parameters[k].Value = Common.ReDateTime((DateTime)dt.Rows[j][dtCol.Rows[i]["Name"].ToString()]); else parameters[k].Value = dt.Rows[j][dtCol.Rows[i]["Name"].ToString()]; } else { switch (tf.GetColType(dtCol.Rows[i]["Name"].ToString())) { case SqlDbType.TinyInt: parameters[k].Value = 0; break; case SqlDbType.Int: parameters[k].Value = 0; break; case SqlDbType.DateTime: parameters[k].Value = DBNull.Value; break; case SqlDbType.Bit: parameters[k].Value = false; break; case SqlDbType.Decimal: parameters[k].Value = 0; break; case SqlDbType.SmallInt: parameters[k].Value = 0; break; case SqlDbType.NVarChar: parameters[k].Value = ""; break; default: parameters[k].Value = DBNull.Value; break; } } k++; } } ia.ExecuteNonQuerySql(strInsert.ToString(), parameters); } } catch (Exception e) { throw e; } }