[ASP.NET]Web API實作CRUD
Web API 是我個人目前最愛用的前端應用，這篇備忘利用Web API實作CRUD整個過程，
希望未來專案中可以完全取代Web Service(...可惜公司包袱很重，害我一直不敢使用)。
1.Routing 設定
App_Start新增WebApiConfig.cs
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
RouteTable.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{action}/{id}",
defaults: new { id = RouteParameter.Optional }
).RouteHandler = new SessionStateRouteHandler();
// 返回 JSON
var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(
t => t.MediaType == "application/xml");
config.Formatters.XmlFormatter.SupportedMediaTypes.Remove(appXmlType);
}
}
Global.asax.cs
void Application_Start(object sender, EventArgs e)
{
// 應用程式啟動時執行的程式碼
WebApiConfig.Register(GlobalConfiguration.Configuration);
BundleConfig.RegisterBundles(BundleTable.Bundles);
AuthConfig.RegisterOpenAuth();
GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();
}
2.新增Web API Controller
public class TestsController : ApiController
{
string connstr = ConfigurationManager.ConnectionStrings["mysql"].ToString();
public IEnumerable<TEST> GetAllTESTs()
{
string sqlstatement = "select * from TEST ";
using (SqlConnection connection = new SqlConnection(connstr))
{
var myquery = connection.Query<TEST>(sqlstatement, null);
if (myquery != null && myquery.Any())
{
if (HttpContext.Current.Session["test"] != null)
{
HttpContext.Current.Session["test"] = myquery;//這篇示範所以用session偷懶一下
}
}
return myquery as IEnumerable<TEST>;
}
}
public IEnumerable<TEST> GetTESTByc1(int c1)
{
if (HttpContext.Current.Session["test"] != null)
{
return (HttpContext.Current.Session["test"] as List<TEST>).Where((x) =>
int.Equals(x.c1, c1)).AsEnumerable<TEST>();
}
else
return null;
}
public IEnumerable<TEST> GetTESTBYc2(string c2)
{
if (HttpContext.Current.Session["test"] != null)
{
return (HttpContext.Current.Session["test"] as List<TEST>).Where((x) =>
string.Equals(x.c2, c2, StringComparison.OrdinalIgnoreCase)).AsEnumerable<TEST>();
}
else
return null;
}
public int Insert(TEST test)
{
try
{
using (SqlConnection connection = new SqlConnection(connstr))
{
string insert = "insert into test(c2,c3,c4) values(@c2,@c3,@c4)";
int rows = connection.Execute(insert, new { c2 = test.c2, c3 = test.c3, c4 = test.c4 });
return rows;
}
}
catch
{
throw;
}
}
public int Update(TEST test)
{
try
{
using (SqlConnection connection = new SqlConnection(connstr))
{
string strval = string.Empty;
string update = @"update test set str
where c1=@c1";
//TEST _TEST = new TEST();
PropertyInfo[] props = typeof(TEST).GetProperties();
foreach (PropertyInfo prop in props)
{
DbType dbtype = (DbType)Enum.Parse(typeof(DbType), prop.PropertyType.Name);
object value = prop.GetValue(test, null) as object;
if (value != null && prop.Name.ToLower() != "c1" && value.ToString() != "")
{
DateTime mytime;
if (dbtype == DbType.DateTime)
{
if (DateTime.TryParse(Convert.ToString(value), out mytime))
{
if (mytime != DateTime.MinValue)
{
strval += string.Format("{0}=@{0},", prop.Name);
//prop.SetValue(_TEST, value);
}
}
}
else
{
strval += string.Format("{0}=@{0},", prop.Name);
//prop.SetValue(_TEST, value);
}
}
}
int rows = connection.Execute(update.Replace("str", strval.TrimEnd(',')),
new { test.c1, test.c2, test.c3, test.c4 });
return rows;
}
}
catch
{
throw;
}
}
public int Delete(int C1)
{
try
{
using (SqlConnection connection = new SqlConnection(connstr))
{
string delete = "delete test where c1=@c1";
int rows = connection.Execute(delete, new { c1 = C1 });
return rows;
}
}
catch
{
throw;
}
}
}
CRUD.aspx
<form id="form1" runat="server">
<h1>
Tests List
</h1>
<table>
<thead>
<tr><th>c1</th><th>c2</th><th>c3</th><th>c4</th></tr>
</thead>
<tbody id="mytestdatas">
</tbody>
</table>
<input type="button" id="getall" value="查詢全部資料" onclick="getTests()" />
<input type="button" id="getbyc1" value="查詢C1=1" onclick="getTestsByc1('1')" />
<input type="button" id="getbyc2" value="查詢C2=ricoisme" onclick="getTestsByc2('ricoisme')" /><br/>
C1:<input type="text" id="c1val" />
C2:<input type="text" id="c2val" />c3:<input type="text" id="c3val" />c4:<input type="text" id="c4val" /><br/>
<input type="button" id="insert" value="新增" onclick="InsertTest()" />
<input type="button" id="update" value="更新" onclick="UpdateTest()" />
<input type="button" id="delete" value="刪除" onclick="DeleteTest()" />
</form>
<script type="text/javascript">
$(function () {
getTests();
});
function getTests() {
$.getJSON("api/Tests/GetAllTESTs",
function (Data) {
$('#mytestdatas').empty();
$.each(Data, function (key, val) {
var row = '<tr><td>' + val.c1 + '</td><td>' + val.c2 + '</td><td>' + val.c3 + '</td><td>' + val.c4 + '</td></tr>';
$('#mytestdatas').append(row);
});
});
}
function getTestsByc1(val) {
$.getJSON("api/Tests/GetTESTByc1?c1=" + val,
function (Data) {
$('#mytestdatas').empty();
$.each(Data, function (key, val) {
var row = '<tr><td>' + val.c1 + '</td><td>' + val.c2 + '</td><td>' + val.c3 + '</td><td>' + val.c4 + '</td></tr>';
$('#mytestdatas').append(row);
});
});
};
function getTestsByc2(val) {
$.getJSON("api/Tests/GetTESTBYc2?c2=" + val,
function (Data) {
$('#mytestdatas').empty();
$.each(Data, function (key, val) {
var row = '<tr><td>' + val.c1 + '</td><td>' + val.c2 + '</td><td>' + val.c3 + '</td><td>' + val.c4 + '</td></tr>';
$('#mytestdatas').append(row);
});
});
};
function InsertTest() {
var c2val = $("#c2val").val();
var c3val = $("#c3val").val();
var c4val = $("#c4val").val();
var newTest = {
c2: c2val,
c3: c3val,
c4: c4val
};
$.ajax({
url: "api/Tests/Insert",
data: JSON.stringify(newTest),
type: "POST",
contentType: "application/json;charset=utf-8",
success: function(msg) {
if (msg > 0) {
getTests();
alert("資料新增成功");
}
},
error: function () {
alert("新增失敗");
}
});
return false;
}
function UpdateTest() {
var c1val = $("#c1val").val();
var c2val = $("#c2val").val();
var c3val = $("#c3val").val();
var c4val = $("#c4val").val();
var newTest = {
c1: c1val,
c2: c2val,
c3: c3val,
c4: c4val
};
$.ajax({
url: "api/Tests/Update",
data: JSON.stringify(newTest),
type: "POST",
contentType: "application/json;charset=utf-8",
success: function (msg) {
if (msg > 0) {
getTests();
alert("資料更新成功");
}
},
error: function () {
alert("更新失敗");
}
});
return false;
}
function DeleteTest() {
var c1val = $("#c1val").val();
var newTest = {
c1: c1val
};
$.ajax({
url: "api/Tests/Delete?c1=" + c1val,
type: "DELETE",
contentType: "application/json;charset=utf-8",
success: function (msg) {
if (msg > 0) {
getTests();
alert("資料刪除成功");
}
},
error: function () {
alert("刪除失敗");
}
});
return false;
}
</script>
執行畫面
page_load()查詢該資料表全部資料
DB
新增
ps:c1 為 identity(PK)。
更新
只更新 c1=7 ,set c3=睡大覺。
刪除
刪除 c1=7
參考
Using ASP.NET Web API with ASP.NET Web Forms
CRUD operation using Web API in Asp.net Web form application