1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
| //[Route("api/[controller]")] //mark起來，使用自己的Route
[ApiController]
public class ProductController : ControllerBase
{
private readonly IConfiguration _configuration; //IConfiguration用來讀取設定配置的介面
public ProductController(IConfiguration configuration)
{
_configuration = configuration;
}
[Route("GetAllProduct")] //使用自己的Route
[HttpGet]
public async Task<IActionResult> GetAllProduct()
{
List<ProductModel> list = new List<ProductModel>();
//sql:連接資料庫取得資料
SqlConnection conn = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")); //取得連線字串
if (conn.State != ConnectionState.Open) conn.Open(); //打開通道，建立連線
SqlCommand cmd = new SqlCommand("select * from Product", conn); //sql語句
SqlDataAdapter adapter = new SqlDataAdapter(cmd); //配接器
//取得的資料放入data table
DataTable dt = new DataTable();
adapter.Fill(dt);
//有資料
if (dt.Rows.Count > 0)
{
//資料放入list中
foreach (DataRow row in dt.Rows)
{
ProductModel m = new ProductModel();
m.Id = Convert.ToInt32(row["Id"]);
m.ProductName = row["ProductName"].ToString();
m.Price = Convert.ToInt32(row["Price"]);
m.Qty = Convert.ToInt32(row["Qty"]);
list.Add(m);
}
}
return Ok(list);
}
[Route("PostProduct")]
[HttpPost]
public async Task<IActionResult> PostProduct(ProductModel obj)
{
try
{
using (SqlConnection conn = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
//開啟db
if (conn.State != ConnectionState.Open) conn.Open();
//sql語句
string sql = "insert into product(productName,price,qty) values(@productName,@price,@qty)";
//定義參數及相關屬性和要傳入的值
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@productName", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@qty", SqlDbType.Int));
cmd.Parameters["@productName"].Value = obj.ProductName;
cmd.Parameters["@price"].Value = obj.Price;
cmd.Parameters["@qty"].Value = obj.Qty;
////執行sql語句
//cmd.ExecuteNonQuery();
//執行sql語句 ((如果用來新增修改刪除,成功它會返回受影響的列數,失敗回0.))
if (cmd.ExecuteNonQuery() == 0) return BadRequest("新增失敗");
}
return Ok(obj);
} catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
[Route("UpdateProduct")]
[HttpPut]
public async Task<IActionResult> UpdateProduct(ProductModel obj) {
using (SqlConnection conn = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
//開啟db
if (conn.State != ConnectionState.Open) conn.Open();
//sql語句
string sql = "update Product set ProductName=@ProductName, Price= @Price, Qty=@Qty where Id=@Id";
//定義參數及相關屬性和要傳入的值
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//command.Parameters.AddWithValue("@Value", "值");
cmd.Parameters.AddWithValue("@Id", obj.Id);
cmd.Parameters.AddWithValue("@ProductName", obj.ProductName);
cmd.Parameters.AddWithValue("@Price", obj.Price);
cmd.Parameters.AddWithValue("@Qty", obj.Qty);
//執行sql語句(如果用來新增修改刪除,成功它會返回受影響的列數,失敗回0.)
if (cmd.ExecuteNonQuery()==0) return BadRequest("更新失敗。");
}
}
return Ok(obj);
}
[Route("DeleteProduct")]
[HttpDelete]
public async Task<IActionResult> DeleteProduct(int id)
{
using (SqlConnection conn = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))
{
//開啟db
if (conn.State != ConnectionState.Open) conn.Open();
//sql語句
string sql = "delete Product where Id=@Id";
//定義參數及相關屬性和要傳入的值
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
//command.Parameters.AddWithValue("@Value", "值");
cmd.Parameters.AddWithValue("@Id", id);
//執行sql語句(如果用來新增修改刪除,成功它會返回受影響的列數,失敗回0.)
if (cmd.ExecuteNonQuery() == 0) return BadRequest("刪除失敗。");
}
}
return Ok("資料已刪除");
}
//使用SP
[Route("Product")]
[HttpPost]
public async Task<IActionResult> Product(ProductModel obj)
{
SqlConnection conn = new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
SqlCommand cmd;
SqlDataAdapter da;
DataTable dt = new DataTable();
try
{
//開啟db連線
if (conn.State != ConnectionState.Open) conn.Open();
//定義參數及相關屬性和要傳入的值
cmd = new SqlCommand("uspProduct", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", obj.Action);
cmd.Parameters.AddWithValue("@Id", obj.Id);
cmd.Parameters.AddWithValue("@ProductName", obj.ProductName);
cmd.Parameters.AddWithValue("@Price", obj.Price);
cmd.Parameters.AddWithValue("@Qty", obj.Qty);
da = new SqlDataAdapter(cmd);
da.Fill(dt);
//action=4為查詢所有資料
if (obj.Action == 4)
{
//沒資料
if (dt.Rows.Count <= 0) return NotFound("無資料可顯示");
//有資料資料放入list中
var list = new List<ProductModel>();
foreach (DataRow row in dt.Rows)
{
ProductModel m = new ProductModel();
m.Id = Convert.ToInt32(row["Id"]);
m.ProductName = row["ProductName"].ToString();
m.Price = Convert.ToInt32(row["Price"]);
m.Qty = Convert.ToInt32(row["Qty"]);
list.Add(m);
}
return Ok(list);
}
//執行sql語句(如果用來新增修改刪除,成功它會返回受影響的列數,失敗回0.)
if (cmd.ExecuteNonQuery() == 0) return BadRequest("操作失敗");
return Ok(obj);
} catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
}
|