public void Insert_Inventory(pdi_inventory new_data) { //insert into pdi_inventory(ProductKey,NewProductNumber,InventoryDate) values('test10',1500,2021-01-15) var sql_insert_inventory = $@"insert into pdi_inventory(ProductKey,NewProductNumber,InventoryDate)" + "values(@ProductKey,@NewProductNumber,@InventoryDate)"; try { //執行SQL指令 SqlCommand cmd = new SqlCommand(sql_insert_inventory, pdi04_conn); //隱藏碼攻擊問題。 cmd.Parameters.AddWithValue("@ProductKey", new_data.ProductKey); cmd.Parameters.AddWithValue("@NewProductNumber", new_data.NewProductNumber); cmd.Parameters.AddWithValue("@InventoryDate", new_data.InventoryDate.ToString("yyyy-MM-dd HH:mm:ss")); //開啟資料庫 pdi04_conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { //丟出錯誤 throw new Exception(ex.Message.ToString()); } finally { //關閉資料庫 pdi04_conn.Close(); } }
//根據搜尋來取得成員陣列資料 public List <pdi_inventory> GetInv_Tables() { //宣告要回傳的搜尋資料為資料庫中的產品資料表 List <pdi_inventory> Inv_DataTable = new List <pdi_inventory>(); //sql語法 //SQL需要做inner join才可以 //SELECT pdi_table.ProductKey,pdi_table.ProductName,pdi_table.SafeAmount,pdi_inventory.Inv_number,pdi_inventory.NewProductNumber,pdi_inventory.InventoryDate from pdi_table INNER JOIN pdi_inventory ON pdi_table.ProductKey = pdi_inventory.ProductKey ORDER BY InventoryDate desc var sql_select_inventorytable = @"SELECT pdi_inventory.Inv_number,pdi_table.ProductKey,pdi_table.ProductName,pdi_table.SafeAmount,pdi_inventory.NewProductNumber,pdi_inventory.InventoryDate from pdi_table INNER JOIN pdi_inventory ON pdi_table.ProductKey = pdi_inventory.ProductKey ORDER BY InventoryDate desc"; //DataAdApter 資料配接器 會自動開啟或關閉連結 //順序依照sql語法由最新到最舊,要兩個資料表合併在寫入 //SqlDataAdapter sqlAd = new SqlDataAdapter(sql_select_inventorytable, pdi04_conn); //DataSet ds = new DataSet(); //sqlAd.Fill(ds, "inventory_tb"); //設定例外狀況 try { pdi04_conn.Open(); //執行SQL指令 SqlCommand sqlcmd_select_inventorytable = new SqlCommand(sql_select_inventorytable, pdi04_conn); //取得SQL資料 SqlDataReader sqldr_inventorytable = sqlcmd_select_inventorytable.ExecuteReader(); while (sqldr_inventorytable.Read()) { pdi_inventory InvData = new pdi_inventory(); InvData.Inv_number = Convert.ToInt32(sqldr_inventorytable["Inv_number"]); InvData.ProductKey = sqldr_inventorytable["ProductKey"].ToString(); InvData.NewProductNumber = Convert.ToInt32(sqldr_inventorytable["NewProductNumber"]); InvData.InventoryDate = Convert.ToDateTime(sqldr_inventorytable["InventoryDate"]); // InvData.ProductName = sqldr_inventorytable["ProductName"].ToString(); InvData.SafeAmount = Convert.ToInt32(sqldr_inventorytable["SafeAmount"]); Inv_DataTable.Add(InvData); } } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } finally { //關閉資料庫連線 pdi04_conn.Close(); } //回傳資料庫中的盤點資料表 return(Inv_DataTable); }
public ActionResult Create(string ProductKey, int NewProductNumber, DateTime InventoryDate) { pdi_inventory inventory_data = new pdi_inventory(); inventory_data.ProductKey = ProductKey; inventory_data.NewProductNumber = NewProductNumber; inventory_data.InventoryDate = InventoryDate; //使用Service來新增資料 invtable_service.Insert_Inventory(inventory_data); return(RedirectToAction("Index")); }