C# SQLHelper.OpenConnection方法代码示例

    /// <summary>
    /// 新建筆記
    /// </summary>
    /// <param name="NoteItem"></param>
    /// <param name="New"></param>
    /// <returns></returns>
    public string UpdateNote(DataTable NoteItem, bool New)
    {
        string rtn = "-1";
        if (NoteItem.Rows.Count == 0)
            return rtn;
        string user_no = NoteItem.Rows[0]["user_no"].ToString();
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();
            conn._Transaction = conn._Connection.BeginTransaction();

            try
            {
                if (New)
                {
                    // 新筆記
                    RecordSet RecH = conn.OpenDataTable("lrtdnote", tagUpdateMode.RT_Insert);
                    RecH.PreInsert();
                    RecH.CopyData(NoteItem.Rows[0]);
                    RecH.Update(user_no);
                    rtn = RecH["uid"].ToString();
                }
                else
                {
                    // 修改衝刺
                    RecordSet RecH = conn.OpenDataTable("select * from lrtdnote where uid='" + NoteItem.Rows[0]["uid"].ToString() + "'", tagUpdateMode.RT_Update);
                    if (RecH.Read())
                    {
                        RecH.PreUpdate();
                        RecH.CopyData(NoteItem.Rows[0]);
                        RecH.Update(user_no);
                        rtn = NoteItem.Rows[0]["uid"].ToString();
                    }
                }
                conn._Transaction.Commit();
                conn.CloseConnection();
            }
            catch
            {
                conn._Transaction.Rollback();
                conn.CloseConnection();
            }
        }
        return rtn;
    }

 //Added by Donnie on 2016/01/17
 /// <summary>
 /// 得到樂信公告欄數據
 /// </summary>
 /// <returns></returns>
 public DataTable GetLxAdvData()
 {
     DataTable dtADV = new DataTable();
     try
     {
         using (SQLHelper conn = new SQLHelper())
         {
             if (conn.OpenConnection())
             {
                 string SQL = "select adv_img as img,adv_link as link,adv_title as title from lrlxadv (nolock) order by adv_seq";
                 dtADV = conn.OpenDataTable(SQL, CommandType.Text);
             }
             conn.CloseConnection();
         }
         //返回Client
         return dtADV;
     }
     catch
     {
         return dtADV;
     }
 }

    public void UpdateToDoItems(DataTable UpdateItems, DataTable members, DataTable tags, string userid)
    {
        string SQL = "";
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();
            try
            {
                #region 資料及臨時表準備
                string tmpExecutor = "tmp_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");//原來的執行者
                string tmp_LRTD00H = "tmp_LRTD00H_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                string tmp_LRTD00D1 = "tmp_LRTD00D1_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                string tmp_LRTD00D2 = "tmp_LRTD00D2_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                //string tmp_LRTDFILE = "tmp_LRTDFILE_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");

                SQLHelper connTemp = new SQLHelper();
                //開啟tempdb連接
                connTemp.OpenConnection("tempdb");
                connTemp.ExecuteSQL("create table " + tmp_LRTD00H + "(" + conn.GetTableStruct("lrtd00h") + ",justread varchar(1) not null default '',canback varchar(1) not null default '',td_dir varchar(10) not null default '')");
                connTemp.ExecuteSQL("create table " + tmp_LRTD00D1 + "(" + conn.GetTableStruct("lrtd00d1") + ")");
                connTemp.ExecuteSQL("create table " + tmp_LRTD00D2 + "(" + conn.GetTableStruct("lrtd00d2") + ")");
                //connTemp.ExecuteSQL("create table " + tmp_LRTDFILE +"("+ conn.GetTableStruct("lrtdfile")+")");
                connTemp.ExecuteSQL("create table " + tmpExecutor + "(td_no varchar(20) not null default '',td_executor varchar(50) not null default '',send_msg varchar(1) not null default '')");
                connTemp.CloseConnection();

                tmp_LRTD00H = "tempdb.." + tmp_LRTD00H;
                tmp_LRTD00D1 = "tempdb.." + tmp_LRTD00D1;
                tmp_LRTD00D2 = "tempdb.." + tmp_LRTD00D2;
                //tmp_LRTDFILE = "tempdb.." + tmp_LRTDFILE;
                tmpExecutor = "tempdb.." + tmpExecutor;

                foreach (DataRow dr in UpdateItems.Rows)
                {
                    string _td_sylog = dr["td_sylog"].ToString();
                    SQL =
                        "insert into " + tmp_LRTD00H + "(td_no,td_name,td_owner,td_description,td_prdate,td_prtime," +
                        "td_fno,td_priority,td_code,td_fdate,td_rsdate,td_rstime,td_redate," +
                        "td_retime,td_state,td_sylog,td_relno,td_type,td_ctype," +
                        "td_prsdate,td_predate,td_sdate,td_edate,td_top,td_hold,canback,td_dir,justread,td_flag) " +

                        "select '" + dr["td_no"].ToString() + "',N'" + dr["td_name"].ToString() + "'," +
                        "'" + dr["td_owner"].ToString() + "',N'" + dr["td_description"].ToString() + "'," +
                        "'" + dr["td_prdate"].ToString() + "','" + dr["td_prtime"].ToString() + "'," +
                        "'" + dr["td_fno"].ToString() + "','" + dr["td_priority"].ToString() + "'," +
                        "'" + dr["td_code"].ToString() + "','" + dr["td_fdate"].ToString() + "'," +
                        "'" + dr["td_rsdate"].ToString() + "','" + dr["td_rstime"].ToString() + "'," +
                        "'" + dr["td_redate"].ToString() + "','" + dr["td_retime"].ToString() + "'," +
                        "'" + dr["td_state"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," +
                        "'" + dr["td_relno"].ToString() + "','" + dr["td_type"].ToString() + "'," +
                        "'" + dr["td_ctype"].ToString() + "','" + dr["td_prsdate"].ToString() + "'," +
                        "'" + dr["td_predate"].ToString() + "','" + dr["td_sdate"].ToString() + "'," +
                        "'" + dr["td_edate"].ToString() + "','" + dr["td_top"].ToString() + "'," +
                        "'" + dr["td_hold"].ToString() + "','" + dr["canback"].ToString() + "'," +
                        "'" + dr["td_dir"].ToString() + "','" + dr["justread"].ToString() + "','" + dr["td_flag"].ToString() + "'";

                    conn.ExecuteSQL(SQL);
                }

                foreach (DataRow dr in members.Rows)
                {
                    string _td_sylog = dr["td_sylog"].ToString();

                    SQL =
                        "insert into " + tmp_LRTD00D1 + "(td_no,td_member,td_role,td_rsdate,td_rstime,td_redate," +
                        "td_retime,td_state,td_prsdate,td_predate,td_sdate,td_edate,td_top,td_sylog,td_hold,td_flag) " +
                        "select '" + dr["td_no"].ToString() + "','" + dr["td_member"].ToString() + "'," +
                        "'" + dr["td_role"].ToString() + "','" + dr["td_rsdate"].ToString() + "'," +
                        "'" + dr["td_rstime"].ToString() + "','" + dr["td_redate"].ToString() + "'," +
                        "'" + dr["td_retime"].ToString() + "','" + dr["td_state"].ToString() + "'," +
                        "'" + dr["td_prsdate"].ToString() + "','" + dr["td_predate"].ToString() + "'," +
                        "'" + dr["td_sdate"].ToString() + "','" + dr["td_edate"].ToString() + "'," +
                        "'" + dr["td_top"].ToString() + "','" + _td_sylog.Substring(0, _td_sylog.Length > 500 ? 500 : _td_sylog.Length) + "'," +
                        "'" + dr["td_hold"].ToString() + "','" + dr["td_flag"].ToString() + "'";

                    conn.ExecuteSQL(SQL);
                }

                foreach (DataRow dr in tags.Rows)
                {
                    SQL =
                        "insert into " + tmp_LRTD00D2 + "(td_tag,td_no) " +
                        "select N'" + dr["td_tag"].ToString() + "','" + dr["td_no"].ToString() + "'";

                    conn.ExecuteSQL(SQL);
                }

                //foreach (DataRow dr in attachements.Rows)
                //{
                //    SQL =
                //        "insert into " + tmp_LRTDFILE + "(td_no,td_file,td_guid,td_type,td_remk) " +
                //        "select '" + dr["td_no"].ToString() + "',N'" + dr["td_file"].ToString() + "'," +
                //        "'" + dr["td_guid"].ToString() + "','" + dr["td_type"].ToString() + "',N'" + dr["td_remk"].ToString() + "'";

                //    conn.ExecuteSQL(SQL);
                //}

                conn.ExecuteSQL(
                    "insert into " + tmpExecutor + "(td_no,td_executor) " + "\r\n" +
                    "select a.td_no,isnull(b.td_member,a.td_owner) td_executor " + "\r\n" +
                    "from lrtd00h a " + "\r\n" +
//.........这里部分代码省略.........

    public DataSet GetContactAndMembers(string userid)
    {
        DataSet dSet = new DataSet();
        try
        {
            using (SQLHelper conn = new SQLHelper())
            {
                if (conn.OpenConnection())
                {
                    string SQL =
                        " select a.user_no,a.contact_id,isnull(b.user_email,a.contact_id) contact_email,a.contact_name," +
                        " a.contact_company,a.contact_position,a.contact_sex,a.contact_img," +
                        " case when b.uid is null then 1 else 0 end contact_register " +
                        " from lrcontact a " +
                        " left join lrtduser b(nolock) on a.contact_id = b.user_no " +
                        " where a.user_no = '" + userid + "'";

                    DataTable GroupData = conn.OpenDataTable(SQL, CommandType.Text);
                    GroupData.TableName = "GroupData";
                    dSet.Tables.Add(GroupData.Copy());

                    SQL =
                         "select a.group_id,a.contact_id,b.user_no " +
                         "from lrgmember a " +
                         "join lrgroup b on a.group_id=b.group_id " +
                         "where b.user_no = '" + userid + "'";

                    DataTable MemberData = conn.OpenDataTable(SQL, CommandType.Text);

                    MemberData.TableName = "MemberData";
                    dSet.Tables.Add(MemberData.Copy());
                    dSet.AcceptChanges();
                }
                conn.CloseConnection();
            }
            //返回Client
            return dSet;
        }
        catch
        {
            return dSet;
        }
    }

    public DataTable GetToDoMsg(string user_id, DataTable MessageLocal)
    {
        string SQL = "";

        using (SQLHelper conn = new SQLHelper())
        {
            try
            {
                conn.OpenConnection();

                //開啟tempdb連接
                #region 臨時數據
                string tmp_LRTDMSG = "tmp_LRTDMSG_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                SQLHelper connTemp = new SQLHelper();
                connTemp.OpenConnection("tempdb");
                connTemp.ExecuteSQL("create table " + tmp_LRTDMSG + "(msg_no varchar(100) not null default '')"); ;
                tmp_LRTDMSG = "tempdb.." + tmp_LRTDMSG;
                connTemp.CloseConnection();
                #endregion 臨時數據

                foreach (DataRow dr in MessageLocal.Rows)
                {
                    SQL =
                        "insert into " + tmp_LRTDMSG + "(msg_no) " +
                        "select '" + dr["msg_no"].ToString() + "'";
                    conn.ExecuteSQL(SQL);
                }

                //msg_state 0 標示未讀  1 標示已讀
                SQL =
                    " select a.td_no,b.msg_from,isnull(c.user_name,isnull(d.contact_name,b.msg_from)) msg_fromname, " + "\r\n" +
                    " b.msg_to,isnull(e.user_name,isnull(f.contact_name,'')) msg_toname," +
                    " b.msg_date,b.msg_time,b.msg_text,isnull(g.msg_state,'1') msg_state,b.msg_no,b.msg_type " + "\r\n" +
                    " from lrtdmsg b(nolock) " + "\r\n" +
                    " join lrtd00h a(nolock) on a.td_no=b.td_no " + "\r\n" +
                    " left join lrmsgstate g(nolock) on b.msg_no=g.msg_no and g.msg_to='" + user_id + "' " + "\r\n" +
                    //--from
                    " left join lrtduser c(nolock) on b.msg_from=c.user_no " + "\r\n" +
                    " left join lrcontact d(nolock) on b.msg_from=d.contact_id and d.user_no='" + user_id + "' " + "\r\n" +
                    //--to
                    " left join lrtduser e(nolock) on b.msg_to=e.user_no " + "\r\n" +
                    " left join lrcontact f(nolock) on b.msg_to=f.user_no " + "\r\n" +
                    " where (a.td_owner = '" + user_id + "'" +
                    " or exists(select 1 from lrtd00d1 x where x.td_member = '" + user_id + "' and x.td_no = a.td_no)) " + "\r\n" +
                    " and not exists(select 1 from " + tmp_LRTDMSG + " x where x.msg_no = b.msg_no)" + "\r\n" +
                    " order by a.td_no,b.msg_date,b.msg_time";

                DataTable msgTable = conn.OpenDataTable(SQL, CommandType.Text);

                //資料被拉過去
                //就已讀
                SQL =
                    " update g set g.msg_state = '1' " + "\r\n" +
                    " from lrtdmsg a(nolock) " + "\r\n" +
                    " join lrtd00h b(nolock) on a.td_no = b.td_no " + "\r\n" +
                    " join lrmsgstate g(nolock) on a.msg_no=g.msg_no and g.msg_to='" + user_id + "' " + "\r\n" +
                    " where (b.td_owner = '" + user_id + "'" +
                    " or exists(select 1 from lrtd00d1 x where x.td_member = '" + user_id + "' and x.td_no = b.td_no)) and g.msg_state='0'";

                conn.ExecuteSQL(SQL);
                conn.CloseConnection();

                return msgTable;
            }
            catch (System.Exception ex)
            {
                conn.CloseConnection();
                throw ex;
            }
        }
    }

 public bool UpdateActKey(string user_id)
 {
     bool rtn = false;
     try
     {
         string SQL = "select user_actkey from lrtduser (nolock) where user_no ='" + user_id + "'";
         using (SQLHelper conn = new SQLHelper())
         {
             conn.OpenConnection();
             try
             {
                 DataTable account = conn.OpenDataTable(SQL, CommandType.Text);
                 if (account.Rows.Count > 0)
                 {
                     conn._Transaction = conn._Connection.BeginTransaction();
                     SQL = "update a set a.user_actkey = '" + Guid.NewGuid().ToString().Replace("-", "") + "' from lrtduser a where user_no ='" + user_id + "'";
                     conn.ExecuteSQL(SQL);
                     conn._Transaction.Commit();
                     rtn = true;
                 }
                 account.Dispose();
                 account = null;
                 conn.CloseConnection();
             }
             catch (System.Exception ex)
             {
                 conn._Transaction.Rollback();
                 conn.CloseConnection();
                 throw ex;
             }
             //返回Client
             return rtn;
         }
     }
     catch
     {
     }
     return rtn;
 }

    /// <summary>
    /// DataTable 里存放的是有異動的資料
    /// </summary>
    /// <param name="Contacts"></param>
    /// <param name="GMembers"></param>
    /// <param name="userid"></param>
    public void UpdateContacts(DataTable Contacts, DataTable GMembers, string userid)
    {
        string SQL = "";
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();
            try
            {
                string tmp_LRCONTACT = "tmp_LRCONTACT_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                string tmp_LRGMEMBER = "tmp_LRGMEMBER_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                SQLHelper connTemp = new SQLHelper();
                //開啟tempdb連接
                connTemp.OpenConnection("tempdb");
                connTemp.ExecuteSQL("create table " + tmp_LRCONTACT + "(" + conn.GetTableStruct("lrcontact") + ",contact_modify varchar(1) not null default ('9'))");
                connTemp.ExecuteSQL("create table " + tmp_LRGMEMBER + "(" + conn.GetTableStruct("lrgmember") + ")");
                connTemp.CloseConnection();

                tmp_LRCONTACT = "tempdb.." + tmp_LRCONTACT;
                tmp_LRGMEMBER = "tempdb.." + tmp_LRGMEMBER;

                foreach (DataRow dr in Contacts.Rows)
                {
                    byte[] by;

                    if (dr["contact_img"] == null)
                    {
                        by = (byte[])dr["contact_img"];

                        SQL =
                            "insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
                            "contact_position,contact_modify,contact_sex,contact_img) " + "\r\n" +
                            " select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "',@image";

                        conn.ExecuteSQLImage(SQL, "@image", by);
                    }
                    else
                    {
                        SQL =
                        "insert into " + tmp_LRCONTACT + "(user_no,contact_id,contact_name,contact_company," +
                        "contact_position,contact_modify,contact_sex) " + "\r\n" +
                        " select '" + dr["user_no"].ToString() + "','" + dr["contact_id"].ToString() + "','" + dr["contact_name"].ToString() + "','" + dr["contact_company"].ToString() + "','" + dr["contact_position"].ToString() + "','" + dr["contact_modify"].ToString() + "','" + dr["contact_sex"].ToString() + "'";

                        conn.ExecuteSQL(SQL);
                    }
                }

                foreach (DataRow dr in GMembers.Rows)
                {
                    SQL =
                       "insert into " + tmp_LRGMEMBER + "(contact_id,group_id) " + "\r\n" +
                       "select '" + dr["contact_id"].ToString() + "','" + dr["group_id"].ToString() + "'";

                    conn.ExecuteSQL(SQL);
                }

                conn._Transaction = conn._Connection.BeginTransaction();

                string ymd = DateTime.Now.ToString("yyyy/MM/dd");
                string time = DateTime.Now.ToString("HH:mm:ss");

                //Add = 0,//新增的
                //Delete =1,//刪除的
                //Update = 2,//有更新的
                //1.刪除要標記為Delete的資料
                SQL =
                    " delete a " + "\r\n" +
                    " from lrcontact a " + "\r\n" +
                    " join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
                    " where b.contact_modify = '1'";

                conn.ExecuteSQL(SQL);

                //2.更新要標記為Update的資料的資料
                SQL =
                    " update a set " + "\r\n" +
                    " a.contact_id = b.contact_id," + "\r\n" +
                    " a.contact_name = b.contact_name," + "\r\n" +
                    " a.contact_company = b.contact_company," + "\r\n" +
                    " a.contact_position = b.contact_position," + "\r\n" +
                    " a.contact_sex = b.contact_sex," + "\r\n" +
                    " a.contact_img = b.contact_img," + "\r\n" +
                    " a.ie_lymd ='" + ymd + "'," + "\r\n" +
                    " a.ie_ltime ='" + time + "'," + "\r\n" +
                    " a.ie_luser ='" + userid + "' " + "\r\n" +
                    " from lrcontact a " + "\r\n" +
                    " join " + tmp_LRCONTACT + " b on a.contact_id = b.contact_id and a.user_no = b.user_no " + "\r\n" +
                    " where b.contact_modify = '2'";

                conn.ExecuteSQL(SQL);

                //3.新增要標記為Add的資料
                SQL =
                    " insert into lrcontact(ie_ymd,ie_time,ie_user,user_no,contact_id,contact_name," + "\r\n" +
                    " contact_company,contact_position,contact_sex) " + "\r\n" +
//.........这里部分代码省略.........

    /// <summary>
    /// 我監督的數據
    /// </summary>
    /// <param name="user_id"></param>
    /// <returns></returns>
    public DataTable GetSupervisePageData(string user_id)
    {
        DataTable dtRtn = new DataTable();
        //int Rows = 10; // 前10條

        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();

            dtRtn = this.GetSuperviseData(conn, user_id);

            conn.CloseConnection();
        }

        return dtRtn;
    }

 /// <summary>
 /// 註冊或修改用戶
 /// </summary>
 /// <param name="newAccount"></param>
 /// <param name="AccountData"></param>
 /// <param name="Editor"></param>
 /// <returns></returns>
 public bool UpdateAccountData(bool newAccount, DataTable AccountData, string Editor)
 {
     bool rtn = false;
     try
     {
         using (SQLHelper conn = new SQLHelper())
         {
             if (conn.OpenConnection())
             {
                 try
                 {
                     conn._Transaction = conn._Connection.BeginTransaction();
                     if (newAccount)
                     {
                         rtn = conn.OpenDataTable("select user_no from lrtduser (nolock) where user_no='" + AccountData.Rows[0]["user_no"].ToString() + "'",
                                                  CommandType.Text).Rows.Count == 0;
                         if (rtn)
                         {
                             //Add new
                             RecordSet RecH = conn.OpenDataTable("lrtduser", tagUpdateMode.RT_Insert);
                             RecH.PreInsert();
                             RecH.CopyData(AccountData.Rows[0]);
                             RecH.Update(Editor);
                             rtn = true;
                         }
                     }
                     else
                     {
                         // Edit
                         // 修改任務
                         RecordSet RecH = conn.OpenDataTable("select * from lrtduser where user_no='" + AccountData.Rows[0]["user_no"].ToString() + "'", tagUpdateMode.RT_Update);
                         if (RecH.Read())
                         {
                             RecH.PreUpdate();
                             RecH.CopyData(AccountData.Rows[0]);
                             RecH.Update(Editor);
                             rtn = true;
                         }
                         else
                         {
                             rtn = false;
                         }
                     }
                     conn._Transaction.Commit();
                 }
                 catch
                 {
                     rtn = false;
                     conn._Transaction.Rollback();
                 }
             }
             conn.CloseConnection();
         }
         //返回Client
         return rtn;
     }
     catch
     {
     }
     return rtn;
 }

    /// <summary>
    /// 主頁數據
    /// </summary>
    /// <param name="user_id"></param>
    /// <returns></returns>
    public DataSet GetHomePageData(string user_id)
    {
        DataSet dSet = new DataSet();
        int Rows = 10; // 前10條
        string SQL = "";
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();

            // 我要做的(置頂先優表身,再表頭)
            SQL = "select * from " + "\r\n" +
                  "( " + "\r\n" +
                  "  select row_number() over(order by case when ISNULL(x.td_top,a.td_top) = 'Y' then '9999/99/99' " + "\r\n" +
                  "						                    when datediff(day,getdate(),a.td_prdate) < 0 and a.td_prdate > '' then '8888/88/88' " + "\r\n" +
                  "						                    else ISNULL(x.td_prsdate,a.td_prsdate) end desc,ISNULL(x.td_prsdate,a.td_prsdate) desc,a.td_no desc) as row, " + "\r\n" +
                  "          a.td_no,a.td_name,a.td_description,isnull(d.user_name,a.td_owner) as user_name,ISNULL(x.td_prsdate,a.td_prsdate) as td_date, " + "\r\n" +
                  "          ISNULL(x.td_top,a.td_top) as td_top,case when datediff(day,getdate(),a.td_prdate) < 0 and a.td_prdate > '' then 'Y' else 'N' end as td_overdue,case when a.td_owner='" + user_id + "' then '1' else '2' end as todo_type,a.td_state,a.td_seq,a.td_flag,a.td_hold " + "\r\n" +
                  "  from lrtd00h a (nolock) " + "\r\n" +
                  "  left join lrtd00d1 x (nolock) on x.td_no = a.td_no and x.td_role='2' " + "\r\n" +
                  "  left join lrtduser d (nolock) on a.td_owner = d.user_no " + "\r\n" +
                  "  where (a.td_owner = '" + user_id + "' and not exists(select 1 from lrtd00d1 x where x.td_no = a.td_no and x.td_role='2') " + "\r\n" +
                  "          or a.td_owner <> '" + user_id + "' and exists(select 1 from lrtd00d1 x where x.td_member = '" + user_id + "' and x.td_no = a.td_no and x.td_role='2') " + "\r\n" +
                  "        ) and a.td_code <> 'Y' " + "\r\n" +
                  ") a " + "\r\n" +
                  "where a.row <= " + Rows.ToString() + "\r\n" +
                  "order by a.row";
            DataTable table = conn.OpenDataTable(SQL, CommandType.Text);
            table.TableName = "t1";
            dSet.Tables.Add(table.Copy());

            // 我發出的(置頂抓表頭)
            SQL = "select * from " + "\r\n" +
                  "( " + "\r\n" +
                  "  select row_number() over(order by case when a.td_top = 'Y' then '9999/99/99' " + "\r\n" +
                  "                                         when datediff(day,getdate(),a.td_prdate) < 0 and a.td_prdate > '' then '8888/88/88' " + "\r\n" +
                  "                                         else a.ie_ymd end desc,a.ie_ymd desc,a.ie_time desc) as row, " + "\r\n" +
                  " 	    a.td_no,a.td_name,a.td_description, isnull(d.user_name,a.td_owner) as user_name,a.ie_ymd as td_date, " + "\r\n" +
                  "         a.td_top,case when datediff(day,getdate(),a.td_prdate) < 0 and a.td_prdate > '' then 'Y' else 'N' end as td_overdue,a.td_state,a.td_seq,a.td_flag,a.td_hold " + "\r\n" +
                  "  from lrtd00h a (nolock) " + "\r\n" +
                  "  join lrtd00d1 x (nolock) on x.td_no = a.td_no and x.td_role='2' " + "\r\n" +
                  "  left join lrtduser d (nolock) on x.td_member = d.user_no " + "\r\n" +
                  "  where a.td_owner = '" + user_id + "' and a.td_code <> 'Y'  " + "\r\n" +
                  ") a " + "\r\n" +
                  "where a.row <= " + Rows.ToString() + "\r\n" +
                  "order by a.row";
            table = conn.OpenDataTable(SQL, CommandType.Text);
            table.TableName = "t2";

            dSet.Tables.Add(table.Copy());

            dSet.AcceptChanges();

            conn.CloseConnection();
        }

        return dSet;
    }

    /// <summary>
    /// 我要做的數據
    /// </summary>
    /// <param name="user_id"></param>
    /// <returns></returns>
    public DataTable GetMyToDoPageData(string user_id)
    {
        DataTable dtRtn = new DataTable();
        //int Rows = 10; // 前10條
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();

            // 我要做的(置頂先優表身,再表頭)
            dtRtn = GetMyTodoData(conn, user_id);

            conn.CloseConnection();
        }

        return dtRtn;
    }

 /// <summary>
 /// 記事本數據
 /// </summary>
 /// <param name="user_id"></param>
 /// <returns></returns>
 public DataTable GetNoteDataByUser(string user_id)
 {
     DataTable rtn = new DataTable();
     try
     {
         using (SQLHelper conn = new SQLHelper())
         {
             if (conn.OpenConnection())
             {
                 rtn = conn.OpenDataTable("select a.*,b.user_name from lrtdnote a(nolock) left join lrtduser b(nolock) on a.user_no=b.user_no where a.user_no='" + user_id + "'", CommandType.Text);
                 //rtn = conn.OpenDataTable("select * from lrtdnote (nolock) where user_no='" + user_id + "'", CommandType.Text);
             }
             conn.CloseConnection();
         }
         //返回Client
         return rtn;
     }
     catch
     {
     }
     return rtn;
 }

    public DataTable AddToDoItems(DataTable AddItems, DataTable members, DataTable tags)
    {
        string curId = "";
        DataTable rtnTable = new DataTable();
        rtnTable.TableName = "myRtn";
        rtnTable.Columns.Add("guid", typeof(string));
        rtnTable.Columns.Add("td_no", typeof(string));
        rtnTable.Columns.Add("td_seq", typeof(string));

        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();
            try
            {
                conn._Transaction = conn._Connection.BeginTransaction();

                foreach (DataRow dr in AddItems.Rows)
                {
                    DataRow[] _todoItem = AddItems.Select("td_no = '" + dr["td_no"].ToString() + "'");
                    DataRow[] _Members = members.Select("td_no = '" + dr["td_no"].ToString() + "'");
                    DataRow[] _tags = tags.Select("td_no = '" + dr["td_no"].ToString() + "'");
                    //DataRow[] _attachments = attachements.Select("td_no = '" + dr["td_no"].ToString() + "'");
                    int maxSeq;
                    curId = AddToDoItem(conn, _todoItem, _Members, _tags, out maxSeq);
                    DataRow newRow = rtnTable.NewRow();
                    newRow["td_no"] = curId;
                    newRow["guid"] = dr["td_no"];
                    newRow["td_seq"] = maxSeq;
                    rtnTable.Rows.Add(newRow);
                }

                conn._Transaction.Commit();
                conn.CloseConnection();
            }
            catch (System.Exception ex)
            {
                conn._Transaction.Rollback();
                conn.CloseConnection();
                throw ex;
            }

            return rtnTable;
        }
    }

    public bool DeleteNote(string NoteID, string user_id)
    {
        bool rtn = false;
        using (SQLHelper conn = new SQLHelper())
        {
            conn.OpenConnection();
            conn._Transaction = conn._Connection.BeginTransaction();

            try
            {
                conn.ExecuteSQL("delete from lrtdnote where uid = " + NoteID + " and user_no='" + user_id + "'");
                conn._Transaction.Commit();
                conn.CloseConnection();
                rtn = true;
            }
            catch
            {
                conn._Transaction.Rollback();
                conn.CloseConnection();
            }
        }
        return rtn;
    }

 public DataTable GetRelatedUsers(string user_id)
 {
     DataTable UsersData = new DataTable("UsersData");
     try
     {
         using (SQLHelper conn = new SQLHelper())
         {
             if (conn.OpenConnection())
             {
                 string SQL = "select a.td_user,b.user_name from" + "\r\n" +
                              "(" + "\r\n" +
                              "   --我參與事項的owner" + "\r\n" +
                              "   select td_owner as td_user from lrtd00h (nolock) where td_no in " + "\r\n" +
                              "   (select td_no from lrtd00d1 (nolock) where td_member='" + user_id + "') and td_owner<>'" + user_id + "'" + "\r\n" +
                              "   union" + "\r\n" +
                              "   --我參與事項中的其他人" + "\r\n" +
                              "   select td_member as td_user from lrtd00d1 (nolock) where td_no in " + "\r\n" +
                              "   (select td_no from lrtd00d1 (nolock) where td_member='" + user_id + "') and td_member<>'" + user_id + "'" + "\r\n" +
                              "   union" + "\r\n" +
                              "   --我是owner" + "\r\n" +
                              "   select td_member as td_user from lrtd00d1 (nolock) where td_no in " + "\r\n" +
                              "   (select td_no from lrtd00h(nolock) where td_owner='" + user_id + "') and td_member<>'" + user_id + "'" + "\r\n" +
                              ") a " + "\r\n" +
                              "left join lrtduser b (nolock) on a.td_user=b.user_no" + "\r\n" +
                              "order by b.user_name";
                 UsersData = conn.OpenDataTable(SQL, CommandType.Text);
             }
             conn.CloseConnection();
         }
         //返回Client
         return UsersData;
     }
     catch
     {
         return UsersData;
     }
 }