//傳入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);
}
