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