[ASP.NET]Web API實作CRUD

[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()查詢該資料表全部資料

image

 

DB

image

 

 

新增

image

ps:c1 為 identity(PK)。

image

 

 

更新

image

只更新 c1=7 ,set c3=睡大覺。

 

image

 

 

刪除

image

刪除 c1=7

 

image

 

 

 

 

參考

Using ASP.NET Web API with ASP.NET Web Forms

CRUD operation using Web API in Asp.net Web form application

ASP.NET Web API 參數繫結

Providing session state in ASP.NET WebAPI