Web API GET,PUT POST connecto database without Using EF?

Question

HI All, I would like have sample web API For GET ,POST ,PUT to be connected with mysql database, without using the Entity framework .i want to consue it in asp.net webform. web API Creattion To be consumed by Client Application : Asp.net Application Thanks in Advance , Karthik K

Rajanikant Hawaldar · Answer

Hi Karthik, check this Post/Put data from Web API without entity Framework https://forums.asp.net/t/2125139.aspx?Post+Put+data+from+Web+API+without+entity+Framework Thanks

Piyush Pansuriya · Answer

Hi Kartik, In my first URL there is a GET method as below: [HttpGet] [ActionName( "GetEmployeeByID" )] public Employee Get( int id) { //return listEmp.First(e => e.ID == id); SqlDataReader reader = null; SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = @ "Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;" ; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "Select * from tblEmployee where EmployeeId=" + id + "" ; sqlCmd.Connection = myConnection; myConnection.Open(); reader = sqlCmd.ExecuteReader(); Employee emp = null; while (reader.Read()) { emp = new Employee(); emp.EmployeeId = Convert.ToInt32(reader.GetValue(0)); emp.Name = reader.GetValue(1).ToString(); emp.ManagerId = Convert.ToInt32(reader.GetValue(2)); } return emp; myConnection.Close(); } Using that you can read the data.

Piyush Pansuriya · Answer

Hi, Please go through the below link, https://www.c-sharpcorner.com/UploadFile/97fc7a/webapi-restful-operations-in-webapi-using-ado-net-objects-a/ https://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-Asp-Net-form-data-into-database-using-web-api/

Rajanikant Hawaldar · Answer

Hi Karthik, Refer this too https://www.youtube.com/watch?v=1IFS33sPDhE Thanks

Karthik K · Answer

Hi RajaniKant , As Far as POST,PUT Method is ok. I really require that GET Method , I Need to Read the data from database using the Particular id value. how can i Proceed. Thanks Karthik K

Karthik K · Answer

@Piyush , I have used the your mentioned Url for POST Method , https://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-Asp-Net-form-data-into-database-using- web-api/ My Requirement is to Read the Data from database , using GET Method of API . Guide me if you know this . Thanking You , Karthik K

Rajanikant Hawaldar · Answer

Code in Model: public class Product { public int Id { get ; set ; } public string Name { get ; set ; } public string Category { get ; set ; } public decimal Price { get ; set ; } } Code in Api Controller: public class ProductsController : ApiController { public SqlConnection conn = new SqlConnection(); //POST: /api/products public HttpResponseMessage PostProduct([FromBody]Product item) { conn.ConnectionString = @ "database connection string" ; string sqlText = "Insert into products values(@Id,@name,@category,@price)" ; SqlCommand sqlCmd = new SqlCommand(sqlText, conn); sqlCmd.Parameters.AddWithValue( "@Id" , item.Id); sqlCmd.Parameters.AddWithValue( "@name" , item.Name); sqlCmd.Parameters.AddWithValue( "@category" , item.Category); sqlCmd.Parameters.AddWithValue( "@price" , item.Price); conn.Open(); int i = sqlCmd.ExecuteNonQuery(); conn.Close(); var response = Request.CreateResponse (HttpStatusCode.Created, item); string uri = Url.Link( "DefaultApi" , new { id = item.Id }); response.Headers.Location = new Uri(uri); return response; } //PUT: /api/products/id public void PutProduct(Product product) { conn.ConnectionString = @ "SQL database connection" ; string sqlText = "update products set Id=@Id,Name=@name,Category=@category,Price=@price" ; SqlCommand sqlCmd = new SqlCommand(sqlText, conn); sqlCmd.Parameters.AddWithValue( "@Id" , product.Id); sqlCmd.Parameters.AddWithValue( "@name" , product.Name); sqlCmd.Parameters.AddWithValue( "@category" , product.Category); sqlCmd.Parameters.AddWithValue( "@price" , product.Price); conn.Open(); int i = sqlCmd.ExecuteNonQuery(); conn.Close(); } } } Code in View: h1 > My WebAPI h1 > div id = "body" > section > h2 > Add Record h2 > Id: input id = "Id" type = "text" /> Name: input id = "name" type = "text" /> br /> Category: input id = "category" type = "text" /> Price: input id = "price" type = "text" /> br /> input id = "addItem" type = "button" value = "AddRecord" /> section > section > br /> br /> h2 > Update Record h2 > Id: input id = "id2" type = "text" /> br /> Name: input id = "name2" type = "text" /> br /> Category: input id = "category2" type = "text" /> Price: input id = "price2" type = "text" /> br /> input id = "editItem" type = "button" value = "UpdateRecord" /> section > div > "~/Scripts/jquery-1.10.2.min.js" > //create a model to save input data var Product = { create: function () { Id: "" ; Name: "" ; Category: "" ; Price: "" ; return Product; } } //Add a record. method:POST url: /api/Products //Request PostProduct(Product item) method in ProductsController $( "#addItem" ).click( function () { var newProduct = Product.create(); newProduct.Id = $( "#Id" ).val(); newProduct.Name = $( "#name" ).val(); newProduct.Category = $( "#category" ).val(); newProduct.Price = $( "#price" ).val(); $.ajax({ url: "/api/Products" , type: "POST" , contentType: "application/json; charset=utf-8" , data: JSON.stringify(newProduct), success: function () { alert( "Success!" ); }, error: function (XMLHttpRequest, textStatus, errorThrown) { alert( "Request failed,info:" + textStatus + " " + errorThrown); } }); }); //update a record. method:PUT url: /api/Products/Id //Request PutProduct(int id, Product product) method in ProductsController $( "#editItem" ).click( function () { var newProduct = Product.create(); newProduct.Id = $( "#id2" ).val(); newProduct.Name = $( "#name2" ).val(); newProduct.Category = $( "#category2" ).val(); newProduct.Price = $( "#price2" ).val(); $.ajax({ url: "/api/Products/" + $( "#id2" ).val(), type: "PUT" , data: JSON.stringify(newProduct), contentType: "application/json; charset=urf-8" , success: function () { alert( "Success! " ); }, error: function (XMLHttpRequest, textStatus, errorThrown) { alert( "Request failed,info:" + textStatus + " " + errorThrown); } }); });