コード例 #1
0
ファイル: ProductEntity.cs プロジェクト: Dann1228/TakeFive
        //傳入Pid(產品編號),取得照片檔案
        public byte[] GetPicture(String PId)
        {
            using (DataTable dt = new DataTable())
            {
                string sqlTxt = @"select picture from producttbl where pid=@pid";

                string connStr = DBConnClass.GetDBConn();
                using (SqlConnection sqlConn = new SqlConnection(connStr))
                    using (SqlCommand cmd = new SqlCommand(sqlTxt, sqlConn)
                    {
                        CommandType = CommandType.Text
                    })
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            sqlConn.Open();
                            cmd.Parameters.Add("@pid", SqlDbType.Int).Value = PId;
                            da.Fill(dt);
                        }


                if (dt.Rows.Count > 0)
                {
                    Byte[] data = new Byte[0];
                    data = (Byte[])(dt.Rows[0]["picture"]);

                    return(data);
                }
                else
                {
                    return(null);
                }
            }
        }
コード例 #2
0
ファイル: ProductEntity.cs プロジェクト: Dann1228/TakeFive
        //傳入ViewModel,修改資料
        public Boolean Update(ProductViewModels vModel)
        {
            string        connStr       = DBConnClass.GetDBConn();
            SqlConnection sqlConnection = new SqlConnection(connStr);
            SqlCommand    cmd           = new SqlCommand();

            try
            {
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText =
                    @"update producttbl set 
                  kind=@kind,
                  category=@category,
                  mid=@mid,
                  name=@name,
                  intro=@intro,
                  price=@price,
                  state=@state,
                  score=@score,
                  picture=@picture
                  where pid=@pid";
                //vidiourl=@vidiourl
                cmd.Connection = sqlConnection;
                cmd.Parameters.Add("@pid", SqlDbType.Int).Value           = vModel.PId;
                cmd.Parameters.Add("@kind", SqlDbType.Int).Value          = vModel.SelectedKinds;
                cmd.Parameters.Add("@category", SqlDbType.Int).Value      = vModel.SelectedCategories;
                cmd.Parameters.Add("@mid", SqlDbType.NVarChar).Value      = vModel.MId;
                cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value     = vModel.Name;
                cmd.Parameters.Add("@intro", SqlDbType.NVarChar).Value    = vModel.Intro;
                cmd.Parameters.Add("@price", SqlDbType.Int).Value         = vModel.Price;
                cmd.Parameters.Add("@state", SqlDbType.Int).Value         = vModel.State;
                cmd.Parameters.Add("@score", SqlDbType.Int).Value         = vModel.Score;
                cmd.Parameters.Add("@picture", SqlDbType.VarBinary).Value = vModel.Picture ?? null;
                // cmd.Parameters.Add("@vidiourl", SqlDbType.NVarChar).Value = vModel.VidioUrl ?? null;


                sqlConnection.Open();
                int effectrows;
                effectrows = cmd.ExecuteNonQuery();

                if (effectrows <= 0)
                {
                    return(false);
                }
                else
                {
                    return(true);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
        }
コード例 #3
0
ファイル: PaymentEntity.cs プロジェクト: Dann1228/TakeFive
        //新增一筆 ProductViewModels
        public int Insert(OrderViewModel vModel,
                          string userId,
                          Decimal totalPay,
                          int payKind)
        {
            string        connStr       = DBConnClass.GetDBConn();
            SqlConnection sqlConnection = new SqlConnection(connStr);
            SqlCommand    cmd           = new SqlCommand();
            int           insertedPid   = -1;

            try
            {
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText =
                    @"insert paymenttbl
                 (mid,name,phone,addr,pay,paydate,paykind,
                  creditkind,creaditno,vaildmonth,vaildyear,vaildcode,
                  memo) 
                 output inserted.payid 
                 VALUES 
                 (@mid,@name,@phone,@addr,@pay,@paydate,@paykind,
                  @creditkind,@creaditno,@vaildmonth,@vaildyear,@vaildcode,
                  @memo)";

                cmd.Connection = sqlConnection;
                cmd.Parameters.Add("@mid", SqlDbType.NVarChar).Value       = userId;
                cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value      = vModel.MName;
                cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value     = vModel.MPhone;
                cmd.Parameters.Add("@addr", SqlDbType.NVarChar).Value      = vModel.MAddr;
                cmd.Parameters.Add("@pay", SqlDbType.Int).Value            = totalPay;
                cmd.Parameters.Add("@paydate", SqlDbType.DateTime).Value   = DateTime.Now;
                cmd.Parameters.Add("@paykind", SqlDbType.Int).Value        = vModel.CreditKind;
                cmd.Parameters.Add("@creditkind", SqlDbType.Int).Value     = payKind;
                cmd.Parameters.Add("@creaditno", SqlDbType.NVarChar).Value =
                    vModel.CreditNumber1 + vModel.CreditNumber2 + vModel.CreditNumber3 + vModel.CreditNumber4;

                cmd.Parameters.Add("@vaildmonth", SqlDbType.NVarChar).Value = vModel.VaildMonth;
                cmd.Parameters.Add("@vaildyear", SqlDbType.NVarChar).Value  = vModel.VaildYear;
                cmd.Parameters.Add("@vaildcode", SqlDbType.NVarChar).Value  = vModel.VaildCode;
                cmd.Parameters.Add("@memo", SqlDbType.NText).Value          = vModel.Memo ?? "";


                sqlConnection.Open();
                insertedPid = (int)cmd.ExecuteScalar();

                return(insertedPid);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
        }
コード例 #4
0
ファイル: ProductEntity.cs プロジェクト: Dann1228/TakeFive
        //利用 Pid(產品編號) 回傳,該筆(一筆) ProductViewModels
        public IEnumerable <ProductViewModels> QueryByProductId(String Pid)
        {
            IEnumerable <ProductViewModels> vModel = Enumerable.Empty <ProductViewModels>();

            using (DataTable dt = new DataTable())
            {
                string sqlTxt = @"select * from producttbl where pid=@pid";

                string connStr = DBConnClass.GetDBConn();
                using (SqlConnection sqlConn = new SqlConnection(connStr))
                    using (SqlCommand cmd = new SqlCommand(sqlTxt, sqlConn)
                    {
                        CommandType = CommandType.Text
                    })
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            sqlConn.Open();
                            cmd.Parameters.Add("@pid", SqlDbType.Int).Value = Pid;
                            da.Fill(dt);
                        }

                if (dt.Rows.Count == 1)
                {
                    var query = from r in dt.AsEnumerable()
                                select new ProductViewModels
                    {
                        PId                = Convert.ToInt32(r.Field <int>("pid")),
                        SelectedKinds      = Convert.ToInt32(r.Field <int>("kind")),
                        SelectedCategories = Convert.ToInt32(r.Field <int>("category")),
                        MId                = (r.Field <String>("mid") ?? "").Trim(),
                        Name               = (r.Field <String>("name") ?? "").Trim(),
                        Intro              = (r.Field <String>("intro") ?? "").Trim(),
                        Price              = Convert.ToInt32(r.Field <int>("price")),
                        State              = Convert.ToInt32(r.Field <int>("state")),
                        Score              = Convert.ToInt32(r.Field <int>("score")),
                        Picture            = (r.Field <Byte[]>("picture")).ToArray(),
                        VidioUrl           = (r.Field <String>("vidiourl") ?? "").Trim(),
                    };
                    vModel = query;
                }
                else
                {
                    vModel = null;
                }

                return(vModel);
            }
        }
コード例 #5
0
ファイル: PaymentEntity.cs プロジェクト: Dann1228/TakeFive
        //傳入ViewModel,修改資料
        public Boolean CancelOrder(string pId, string cancelReason)
        {
            string        connStr       = DBConnClass.GetDBConn();
            SqlConnection sqlConnection = new SqlConnection(connStr);
            SqlCommand    cmd           = new SqlCommand();

            try
            {
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText =
                    @"update paymenttbl set 
                  status=@status,
                  cancelreason=@cancelreason,
                  canceldate=@canceldate 
                  where payid=@payid";

                cmd.Connection = sqlConnection;
                cmd.Parameters.Add("@payid", SqlDbType.Int).Value           = pId;
                cmd.Parameters.Add("@status", SqlDbType.Int).Value          = 1;
                cmd.Parameters.Add("@cancelreason", SqlDbType.NChar).Value  = cancelReason;
                cmd.Parameters.Add("@canceldate", SqlDbType.DateTime).Value = DateTime.Now;

                sqlConnection.Open();
                int effectrows;
                effectrows = cmd.ExecuteNonQuery();

                if (effectrows <= 0)
                {
                    return(false);
                }
                else
                {
                    return(true);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
        }
コード例 #6
0
ファイル: PaymentEntity.cs プロジェクト: Dann1228/TakeFive
        public IEnumerable <OrderViewModel> Query(string userId)
        {
            IEnumerable <OrderViewModel> vModel = Enumerable.Empty <OrderViewModel>();

            using (DataTable dt = new DataTable())
            {
                string sqlTxt = @"select 
                                  *
                                  from paymenttbl where mid=@mid";

                string connStr = DBConnClass.GetDBConn();
                using (SqlConnection sqlConn = new SqlConnection(connStr))
                    using (SqlCommand cmd = new SqlCommand(sqlTxt, sqlConn)
                    {
                        CommandType = CommandType.Text
                    })
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            sqlConn.Open();
                            cmd.Parameters.Add("@mid", SqlDbType.NVarChar).Value = userId;
                            da.Fill(dt);
                        }

                if (dt.Rows.Count > 0)
                {
                    var query = from r in dt.AsEnumerable()
                                select new OrderViewModel
                    {
                        PayId      = Convert.ToInt32(r.Field <int>("payid")),
                        MName      = (r.Field <String>("name") ?? "").Trim(),
                        MPhone     = (r.Field <String>("phone") ?? "").Trim(),
                        MAddr      = (r.Field <String>("addr") ?? "").Trim(),
                        TotalPrice = Convert.ToInt32(r.Field <int>("pay")),
                        BuyDate    = Convert.ToDateTime(r.Field <DateTime>("paydate")),
                        Memo       = (r.Field <String>("memo") ?? "").Trim(),
                        //Status = Convert.ToInt32(r.Field<int>("status")),
                    };
                    vModel = query;
                }

                return(vModel);
            }
        }
コード例 #7
0
        public IEnumerable <OrderDetailViewModel> Query(int payId)
        {
            IEnumerable <OrderDetailViewModel> vModel = Enumerable.Empty <OrderDetailViewModel>();

            using (DataTable dt = new DataTable())
            {
                string sqlTxt =
                    @"select  p.name,p.kind,p.category,b.pqty,b.pprice 
                      from buyitemdetailtbl b,producttbl p 
                      where payid=@payid and b.pid=p.pid";

                string connStr = DBConnClass.GetDBConn();
                using (SqlConnection sqlConn = new SqlConnection(connStr))
                    using (SqlCommand cmd = new SqlCommand(sqlTxt, sqlConn)
                    {
                        CommandType = CommandType.Text
                    })
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            sqlConn.Open();
                            cmd.Parameters.Add("@payid", SqlDbType.Int).Value = payId;
                            da.Fill(dt);
                        }

                if (dt.Rows.Count > 0)
                {
                    var query = from r in dt.AsEnumerable()
                                select new OrderDetailViewModel
                    {
                        PName     = (r.Field <String>("name") ?? "").Trim(),
                        PKind     = Convert.ToInt32(r.Field <int>("kind")),
                        PCategory = Convert.ToInt32(r.Field <int>("category")),
                        BQty      = Convert.ToInt32(r.Field <int>("pqty")),
                        BPrice    = Convert.ToInt32(r.Field <int>("pprice")),
                    };
                    vModel = query;
                }

                return(vModel);
            }
        }
コード例 #8
0
ファイル: ProductEntity.cs プロジェクト: Dann1228/TakeFive
        public CartItem QueryByProductid(int?Pid)
        {
            using (DataTable dt = new DataTable())
            {
                string sqlTxt = @"select * from producttbl where pid=@pid";

                string connStr = DBConnClass.GetDBConn();
                using (SqlConnection sqlConn = new SqlConnection(connStr))
                    using (SqlCommand cmd = new SqlCommand(sqlTxt, sqlConn)
                    {
                        CommandType = CommandType.Text
                    })
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            sqlConn.Open();
                            cmd.Parameters.Add("@pid", SqlDbType.Int).Value = Pid;
                            da.Fill(dt);
                        }


                if (dt.Rows.Count == 1)
                {
                    CartItem item = new CartItem
                    {
                        Id       = dt.Rows[0].Field <int>("pid"),
                        Name     = (dt.Rows[0].Field <String>("Name") ?? "").Trim(),
                        Price    = dt.Rows[0].Field <int>("price"),
                        Quantity = 1,
                        PhotoUrl = (dt.Rows[0].Field <String>("vidiourl") ?? "").Trim(),
                    };

                    return(item);
                }
                else
                {
                    return(null);
                }
            }
        }
コード例 #9
0
ファイル: ProductEntity.cs プロジェクト: Dann1228/TakeFive
        //傳入Pid(產品編號),刪除資料
        public Boolean Delete(int Pid)
        {
            string        connStr       = DBConnClass.GetDBConn();
            SqlConnection sqlConnection = new SqlConnection(connStr);
            SqlCommand    cmd           = new SqlCommand();

            try
            {
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.CommandText =
                    @"delete from producttbl where pid=@pid";

                cmd.Connection = sqlConnection;
                cmd.Parameters.Add("@pid", SqlDbType.Int).Value = Pid;

                sqlConnection.Open();
                int effectrows;
                effectrows = cmd.ExecuteNonQuery();

                if (effectrows <= 0)
                {
                    return(false);
                }
                else
                {
                    return(true);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                sqlConnection.Close();
            }
        }
コード例 #10
0
        //新增一筆 ProductViewModels
        public Boolean Insert(List <CartItem> Items, string Mid, int payId)
        {
            using (SqlConnection cn = new SqlConnection()
            {
                ConnectionString = DBConnClass.GetDBConn()
            })
            {
                using (SqlCommand cmd = new SqlCommand()
                {
                    Connection = cn
                })
                {
                    /*
                     * Construct an INSERT statement with a SELECT which provides the new primary
                     * key on a successful insert.
                     *
                     * INSERT/SELECT was generated via part of a code sample I wrote
                     * https://code.msdn.microsoft.com/Working-with-SQL-Server-986fff9e
                     *
                     * where you select a database from a ComboBox, select a table from a ListBox
                     * then select columns from a CheckedListBox which in turn create a INSERT/SELECT
                     * statement.
                     */
                    cmd.CommandText = @"INSERT INTO buyitemdetailtbl 
                                        (pid,pqty,pprice,mid,btime,state,starttime,endtime,payid) 
                                        VALUES 
                                        (@pid,@pqty,@pprice,@mid,@btime,@state,@starttime,@endtime,@payid); ";


                    /*
                     * Setup parameters (one only inserting one row we would use cmd.Parameters.AddWithValue
                     * yet in a for-each we can't unless we clear the parameter collection and set the values
                     * on each iteration while doing the parameters as shown below is better.
                     *
                     * Usually what I see on forums is a new to data operations developer will not only
                     * re-create parameters in a for-each but also they will be opening/closing a connection,
                     * creating a new command, re-open a connection, run the query, not check for errors
                     * or get the new id which with not many records might be acceptable to them yet when dealing
                     * with many rows or many rows and many columns this most likely will slow things down and
                     * make it harder to maintain the code.
                     */
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@pid", DbType = DbType.Int32
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@pqty", DbType = DbType.Int32
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@pprice", DbType = DbType.Int32
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@mid", DbType = DbType.String
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@btime", DbType = DbType.DateTime
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@state", DbType = DbType.Int32
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@starttime", DbType = DbType.DateTime
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@endtime", DbType = DbType.DateTime
                    });
                    cmd.Parameters.Add(new SqlParameter()
                    {
                        ParameterName = "@payid", DbType = DbType.Int32
                    });

                    try
                    {
                        cn.Open();

                        foreach (CartItem player in Items)
                        {
                            /*
                             * Since we setup parameters once above we simply index to the proper
                             * parameter and set it's value
                             */
                            cmd.Parameters["@pid"].Value       = player.Id;
                            cmd.Parameters["@mid"].Value       = player.Id;
                            cmd.Parameters["@pqty"].Value      = player.Quantity;
                            cmd.Parameters["@pprice"].Value    = player.Price;
                            cmd.Parameters["@mid"].Value       = Mid;
                            cmd.Parameters["@btime"].Value     = DateTime.Now;
                            cmd.Parameters["@state"].Value     = SqlInt32.Null;
                            cmd.Parameters["@starttime"].Value = DateTime.Now;
                            cmd.Parameters["@endtime"].Value   = SqlDateTime.Null;
                            cmd.Parameters["@payid"].Value     = payId;

                            /*
                             * As we have an insert and select the ExecuteScalar returns
                             * the result of the select part of our CommandText. We get
                             * back the identity/primary key for the newly added record
                             * from ExecuteScalar cast from object to int.
                             */
                            cmd.ExecuteScalar();
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString());
                    }
                }
            }

            return(true);
        }