Exemple #1
0
            /// <summary>
            /// Выполняет запрос выборки набора строк.
            /// </summary>
            /// <param name="sql">Текст запроса к базе данных</param>
            /// <param name="connection">Строка подключения к базе данных</param>
            /// <returns>Возвращает набор строк в DataSet.</returns>
            public static MyResultData SqlReturnDataset(string sql)
            {
                MyResultData result = new MyResultData();

                try
                {
                    string connection = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=cp1251";
                    MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
                    MySql.Data.MySqlClient.MySqlCommand    commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
                    connRC.Open();
                    try
                    {
                        MySql.Data.MySqlClient.MySqlDataAdapter AdapterP = new MySql.Data.MySqlClient.MySqlDataAdapter();
                        AdapterP.SelectCommand = commRC;
                        DataSet ds1 = new DataSet();
                        AdapterP.Fill(ds1);
                        result.ResultData = ds1.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        result.HasError  = true;
                        result.ErrorText = ex.Message;
                    }
                    connRC.Close();
                }
                catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером.
                {
                    result.ErrorText = ex.Message;
                    result.HasError  = true;
                }
                return(result);
            }
Exemple #2
0
            /// <summary>
            /// Выполняет запрос выборки набора строк.
            /// </summary>
            /// <param name="sql">Текст запроса к базе данных</param>
            /// <param name="connection">Строка подключения к базе данных</param>
            /// <returns>Возвращает набор строк в DataSet.</returns>
            public static MyResultData SqlReturnDataset(string sql, string connection)
            {
                MyResultData result = new MyResultData();

                try
                {
                    MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
                    MySql.Data.MySqlClient.MySqlCommand    commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
                    connRC.Open();

                    try
                    {
                        MySql.Data.MySqlClient.MySqlDataAdapter AdapterP = new MySql.Data.MySqlClient.MySqlDataAdapter();
                        AdapterP.SelectCommand = commRC;
                        DataSet ds1 = new DataSet();
                        AdapterP.Fill(ds1);
                        result.ResultData = ds1.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        result.HasError  = true;
                        result.ErrorText = ex.Message;
                    }
                    connRC.Close();
                }
                catch (Exception ex)
                {
                    result.ErrorText = ex.Message;
                    result.HasError  = true;
                }

                return(result);
            }
        public ActionResult Index(FormCollection MyForm)
        {
            //----------------------------------------------------------------------------------------------------------
            // Main
            if (MySourceData == null)
            {
                int    _TmpCnt = 0;
                Random rnd     = new Random();
                //借用具名顏色名稱來產生隨機資料
                string[] colorNames = typeof(Color)
                                      .GetProperties(BindingFlags.Static | BindingFlags.Public)
                                      .Select(o => o.Name).ToArray();
                MySourceData =
                    colorNames
                    .Select(cn => new MyResultData()
                {
                    UserNo   = string.Format("C{0:00000}", rnd.Next(99999)),
                    UserName = cn,
                    RegDate  = DateTime.Today.AddDays(-rnd.Next(1000)),
                    VIP      = _TmpCnt % 2 == 0 ? true : false,
                    Points   = rnd.Next(9999) + _TmpCnt++
                }).ToList();
            }

            try
            {
                if (Request.QueryString["Act"] == "R")
                {
                    //---------------------------------------------------------------------------------------
                    string keywd     = MyForm["keywd"];
                    string dteJoin_B = MyForm["dteJoin_B"];
                    string dteJoin_E = MyForm["dteJoin_E"];
                    string sortField = MyForm["sort[0][field]"];
                    string sortDir   = MyForm["sort[0][dir]"];

                    // 篩選
                    _MyResultDatas = MySourceData.Where(o =>
                                                        string.IsNullOrEmpty(keywd) || o.UserName.Contains(keywd)).AsQueryable();

                    try
                    {
                        DateTime _dteJoin_B = default(DateTime), _dteJoin_E = default(DateTime);

                        DateTime.TryParse(dteJoin_B, out _dteJoin_B);
                        DateTime.TryParse(dteJoin_E, out _dteJoin_E);

                        if (_dteJoin_B != default(DateTime))
                        {
                            _MyResultDatas = _MyResultDatas.Where(o => o.RegDate >= _dteJoin_B);
                        }
                        if (_dteJoin_E != default(DateTime))
                        {
                            _MyResultDatas = _MyResultDatas.Where(o => o.RegDate <= _dteJoin_E);
                        }
                    }
                    catch (Exception)
                    {
                    }

                    try
                    {
                        string sFillter_field    = MyForm["filter[filters][0][field]"].Trim();
                        string sFillter_operator = MyForm["filter[filters][0][operator]"];
                        string sFillter_value    = MyForm["filter[filters][0][value]"].Trim();
                        if (sFillter_field == "UserName")
                        {
                            if (sFillter_operator == "eq")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => o.UserName == sFillter_value);
                            }
                            else if (sFillter_operator == "neq")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => o.UserName != sFillter_value);
                            }
                            else if (sFillter_operator == "contains")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => o.UserName.Contains(sFillter_value));
                            }
                            else if (sFillter_operator == "doesnotcontain")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => !o.UserName.Contains(sFillter_value));
                            }
                            else if (sFillter_operator == "startswith")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => o.UserName.StartsWith(sFillter_value));
                            }
                            else if (sFillter_operator == "endswith")
                            {
                                _MyResultDatas = _MyResultDatas.Where(o => o.UserName.IndexOf(sFillter_value) >= 0 && o.UserName.IndexOf(sFillter_value) == (o.UserName.Length - sFillter_value.Length));
                            }
                        }
                    }
                    catch (Exception)
                    {
                    }

                    // 排序
                    if (!string.IsNullOrEmpty(sortField))
                    {
                        //宣告一個函數可傳回SimMemberInfo之指定屬性值用於依動態欄位排序
                        Func <MyResultData, string, string> GetColString =
                            (o, c) =>
                        {
                            switch (c)
                            {
                            case "UserNo": return(o.UserNo);

                            case "UserName": return(o.UserName);

                            case "RegDate": return(o.RegDate.ToString("yyyyMMdd"));

                            case "Points": return(o.Points.ToString());

                            case "VIP": return(o.VIP.ToString());

                            default: throw new ArgumentException();
                            }
                        };
                        if (sortDir == "asc")
                        {
                            _MyResultDatas = _MyResultDatas.OrderBy(o => GetColString(o, sortField));
                        }
                        else
                        {
                            _MyResultDatas = _MyResultDatas.OrderByDescending(o => GetColString(o, sortField));
                        }
                    }

                    // 分頁
                    int pageSize = 10, take = 10, skip = 0;
                    pageSize = int.TryParse(MyForm["pageSize"], out pageSize) ? pageSize : 10;
                    take     = int.TryParse(MyForm["take"], out take) ? take : 10;
                    skip     = int.TryParse(MyForm["skip"], out skip) ? skip : 0;
                    _MyResultDatas_CurPage = _MyResultDatas.Skip(skip).Take(Math.Min(pageSize, take));
                    //---------------------------------------------------------------------------------------
                }
                else if (Request.QueryString["Act"] == "C")
                {
                    _MyResultData = new MyResultData();

                    //throw new Exception("Test  Err");
                    //---------------------------------------------------------------------------------------
                    _MyResultData.UserNo = "Test" + (MySourceData.Count() + 1).ToString();

                    _MyResultData.UserName = (MyForm["UserName"] ?? "").Trim() == "測試" ? ("測試" + (MySourceData.Count() + 1).ToString()) : (MyForm["UserName"] ?? "");

                    // _MyResultData.RegDate = DateTime.Parse( MyForm["RegDate"].ToString()).ToUniversalTime();
                    _MyResultData.RegDate = DateTime.Parse(MyForm["RegDate"].ToString()); // 同 ToUniversalTime()

                    _MyResultData.Points = MyForm["Points"] != null?int.Parse(MyForm["Points"].ToString()) : -99;

                    bool.TryParse(MyForm["VIP"].ToString(), out _MyResultData.VIP);
                    //---------------------------------------------------------------------------------------

                    MySourceData.Add(_MyResultData);
                }
                else if (Request.QueryString["Act"] == "U")
                {
                    _MyResultData = MySourceData.Where(x => x.UserNo == (MyForm["UserNo"] ?? "")).FirstOrDefault();

                    if (_MyResultData != null)
                    {
                        //throw new Exception("Test  Err");
                        //---------------------------------------------------------------------------------------
                        _MyResultData.SerNo = MyForm["SerNo"] ?? "";
                        _MyResultData.RecNo = MyForm["RecNo"] ?? "";

                        //_MyResultData.UserNo = MyForm["UserNo"] ?? ""; // PKEY

                        _MyResultData.UserName = MyForm["UserName"] ?? "";

                        _MyResultData.RegDate = DateTime.Parse(MyForm["RegDate"].ToString());

                        _MyResultData.Points = MyForm["Points"] != null?int.Parse(MyForm["Points"].ToString()) : -99;

                        bool.TryParse(MyForm["VIP"].ToString(), out _MyResultData.VIP);
                        //---------------------------------------------------------------------------------------
                    }
                }
                else if (Request.QueryString["Act"] == "D")
                {
                    _MyResultData = new MyResultData();

                    //throw new Exception("Test  Err");
                    //---------------------------------------------------------------------------------------
                    _MyResultData.UserNo = MyForm["UserNo"] ?? "";

                    _MyResultData.UserName = MyForm["UserName"] ?? "";

                    _MyResultData.RegDate = DateTime.Parse(MyForm["RegDate"].ToString());

                    _MyResultData.Points = MyForm["Points"] != null?int.Parse(MyForm["Points"].ToString()) : -99;

                    bool.TryParse(MyForm["VIP"].ToString(), out _MyResultData.VIP);
                    //---------------------------------------------------------------------------------------

                    var oDelete = MySourceData.Where(x => x.UserNo == _MyResultData.UserNo).FirstOrDefault();
                    if (oDelete != null)
                    {
                        MySourceData.Remove(oDelete);
                    }
                }
                else
                {
                    throw new Exception("Invalid QueryParm-Act");
                }

                HasErr = false;
                ErrMsg = "";
            }
            catch (Exception e)
            {
                HasErr = true;
                ErrMsg = e.Message.Trim();
            }
            //----------------------------------------------------------------------------------------------------------
            // Result
            if (Request.QueryString["Act"] == "R")
            {
                if (!HasErr)
                {
                    _MyResult = new MyResult()
                    {
                        Data       = _MyResultDatas_CurPage.ToList(), // 目前頁
                        TotalCount = _MyResultDatas.Count(),          // 全部

                        errors = "",

                        ResultCode = 0,
                        ResultMsg  = "作業成功" + " (" + (Request.QueryString["Act"] ?? "") + ")"
                    };
                }
                else
                {
                    _MyResult = new MyResult()
                    {
                        Data       = null,
                        TotalCount = 0,

                        errors = _MyMsg.QueryFail,

                        ResultCode = -1,
                        ResultMsg  = "作業失敗" + " (" + (Request.QueryString["Act"] ?? "") + ") --- " + ErrMsg
                    };
                }
            }
            else if (Request.QueryString["Act"] == "C")
            {
                if (!HasErr)
                {
                    _MyResult = new MyResult()
                    {
                        Data       = _MyResultData,                                     // 目前筆
                        TotalCount = _MyResultDatas != null?_MyResultDatas.Count() : 0, // 全部

                                         errors = "",

                                         ResultCode = 0,
                                         ResultMsg  = "作業成功" + " (" + (Request.QueryString["Act"] ?? "") + ")"
                    };
                }
                else
                {
                    _MyResult = new MyResult()
                    {
                        Data       = null,
                        TotalCount = 0,

                        errors = _MyMsg.CreateFail,

                        ResultCode = -1,
                        ResultMsg  = "作業失敗" + " (" + (Request.QueryString["Act"] ?? "") + ") --- " + ErrMsg
                    };
                }
            }
            else if (Request.QueryString["Act"] == "U")
            {
                if (!HasErr)
                {
                    _MyResult = new MyResult()
                    {
                        Data       = _MyResultData,                                     // 目前筆
                        TotalCount = _MyResultDatas != null?_MyResultDatas.Count() : 0, // 全部

                                         errors = "",

                                         ResultCode = 0,
                                         ResultMsg  = "作業成功" + " (" + (Request.QueryString["Act"] ?? "") + ")"
                    };
                }
                else
                {
                    _MyResult = new MyResult()
                    {
                        Data       = null,
                        TotalCount = 0,

                        errors = _MyMsg.UpdateFail,

                        ResultCode = -1,
                        ResultMsg  = "作業失敗" + " (" + (Request.QueryString["Act"] ?? "") + ") --- " + ErrMsg
                    };
                }
            }
            else if (Request.QueryString["Act"] == "D")
            {
                if (!HasErr)
                {
                    _MyResult = new MyResult()
                    {
                        Data       = _MyResultData,                                     // 目前筆
                        TotalCount = _MyResultDatas != null?_MyResultDatas.Count() : 0, // 全部

                                         errors = "",

                                         ResultCode = 0,
                                         ResultMsg  = "作業成功" + " (" + (Request.QueryString["Act"] ?? "") + ")"
                    };
                }
                else
                {
                    _MyResult = new MyResult()
                    {
                        Data       = null,
                        TotalCount = 0,

                        errors = _MyMsg.DeleteFail,

                        ResultCode = -1,
                        ResultMsg  = "作業失敗" + " (" + (Request.QueryString["Act"] ?? "") + ") --- " + ErrMsg
                    };
                }
            }
            else
            {
                _MyResult = new MyResult()
                {
                    Data       = null,
                    TotalCount = 0, // 全部

                    errors = _MyMsg.OtherFail,

                    ResultCode = -1,
                    ResultMsg  = "作業失敗" + " (" + (Request.QueryString["Act"] ?? "") + ") --- " + ErrMsg
                };
            }
            //----------------------------------------------------------------------------------------------------------
            // Response
            // return View("~/Views/Test/TestKendoUI_CRUD.cshtml");
            return(Json(_MyResult));
            //----------------------------------------------------------------------------------------------------------
        }
Exemple #4
0
 /// <summary>
 /// Выполняет запрос выборки набора строк.
 /// </summary>
 /// <param name="sql">Текст запроса к базе данных</param>
 /// <param name="connection">Строка подключения к базе данных</param>
 /// <returns>Возвращает набор строк в DataSet.</returns>
 public static MyResultData SqlReturnDataset(string sql, string connection)
 {
     MyResultData result = new MyResultData();
     try
     {
         MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection);
         MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC);
         connRC.Open();
         try
         {
             MySql.Data.MySqlClient.MySqlDataAdapter AdapterP = new MySql.Data.MySqlClient.MySqlDataAdapter();
             AdapterP.SelectCommand = commRC;
             DataSet ds1 = new DataSet();
             AdapterP.Fill(ds1);
             result.ResultData = ds1.Tables[0];
         }
         catch (Exception ex)
         {
             result.HasError = true;
             result.ErrorText = ex.Message;
         }
         connRC.Close();
     }
     catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером.
     {
         result.ErrorText = ex.Message;
         result.HasError = true;
     }
     return result;
 }