Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        /// <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;
        }
Beispiel #4
0
        /// <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;
        }
Beispiel #5
0
        /// <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;
        }
Beispiel #6
0
    /// <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);
    }
Beispiel #7
0
        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;
            }
        }
Beispiel #8
0
        /// <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;
            }
        }