//傳入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); } } }
//傳入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(); } }
//新增一筆 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(); } }
//利用 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); } }
//傳入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(); } }
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); } }
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); } }
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); } } }
//傳入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(); } }
//新增一筆 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); }