"scrivere il codice in c# di un controller per gestire la tabella orders del database northwind"
è andato pure a cercarsi il db :
Ecco il codice C# per il controller che gestisce la tabella orders del database Northwind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Data.SqlClient;
using System.Configuration;
namespace Northwind.Controllers
{
public class OrdersController : ApiController
{
// Get all orders
public IHttpActionResult Get()
{
List<Orders> ordersList = new List<Orders>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("select * from orders", conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Orders orders = new Orders();
orders.OrderID = reader["OrderID"] != DBNull.Value ? Convert.ToInt32(reader["OrderID"]) : 0;
orders.CustomerID = reader["CustomerID"] != DBNull.Value ? Convert.ToString(reader["CustomerID"]) : null;
orders.EmployeeID = reader["EmployeeID"] != DBNull.Value ? Convert.ToInt32(reader["EmployeeID"]) : 0;
orders.OrderDate = reader["OrderDate"] != DBNull.Value ? Convert.ToDateTime(reader["OrderDate"]) : DateTime.MinValue;
orders.RequiredDate = reader["RequiredDate"] != DBNull.Value ? Convert.ToDateTime(reader["RequiredDate"]) : DateTime.MinValue;
orders.ShippedDate = reader["ShippedDate"] != DBNull.Value ? Convert.ToDateTime(reader["ShippedDate"]) : DateTime.MinValue;
orders.ShipVia = reader["ShipVia"] != DBNull.Value ? Convert.ToInt32(reader["ShipVia"]) : 0;
orders.Freight = reader["Freight"] != DBNull.Value ? Convert.ToDecimal(reader["Freight"]) : 0;
orders.ShipName = reader["ShipName"] != DBNull.Value ? Convert.ToString(reader["ShipName"]) : null;
orders.ShipAddress = reader["ShipAddress"] != DBNull.Value ? Convert.ToString(reader["ShipAddress"]) : null;
orders.ShipCity = reader["ShipCity"] != DBNull.Value ? Convert.ToString(reader["ShipCity"]) : null;
orders.ShipRegion = reader["ShipRegion"] != DBNull.Value ? Convert.ToString(reader["ShipRegion"]) : null;
orders.ShipPostalCode = reader["ShipPostalCode"] != DBNull.Value ? Convert.ToString(reader["ShipPostalCode"]) : null;
orders.ShipCountry = reader["ShipCountry"] != DBNull.Value ? Convert.ToString(reader["ShipCountry"]) : null;
ordersList.Add(orders);
}
conn.Close();
}
return Ok(ordersList);
}
// Get order by id
public IHttpActionResult Get(int id)
{
Orders orders = new Orders();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("select * from orders where OrderID=" + id, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
orders.OrderID = reader["OrderID"] != DBNull.Value ? Convert.ToInt32(reader["OrderID"]) : 0;
orders.CustomerID = reader["CustomerID"] != DBNull.Value ? Convert.ToString(reader["CustomerID"]) : null;
orders.EmployeeID = reader["EmployeeID"] != DBNull.Value ? Convert.ToInt32(reader["EmployeeID"]) : 0;
orders.OrderDate = reader["OrderDate"] != DBNull.Value ? Convert.ToDateTime(reader["OrderDate"]) : DateTime.MinValue;
orders.RequiredDate = reader["RequiredDate"] != DBNull.Value ? Convert.ToDateTime(reader["RequiredDate"]) : DateTime.MinValue;
orders.ShippedDate = reader["ShippedDate"] != DBNull.Value ? Convert.ToDateTime(reader["ShippedDate"]) : DateTime.MinValue;
orders.ShipVia = reader["ShipVia"] != DBNull.Value ? Convert.ToInt32(reader["ShipVia"]) : 0;
orders.Freight = reader["Freight"] != DBNull.Value ? Convert.ToDecimal(reader["Freight"]) : 0;
orders.ShipName = reader["ShipName"] != DBNull.Value ? Convert.ToString(reader["ShipName"]) : null;
orders.ShipAddress = reader["ShipAddress"] != DBNull.Value ? Convert.ToString(reader["ShipAddress"]) : null;
orders.ShipCity = reader["ShipCity"] != DBNull.Value ? Convert.ToString(reader["ShipCity"]) : null;
orders.ShipRegion = reader["ShipRegion"] != DBNull.Value ? Convert.ToString(reader["ShipRegion"]) : null;
orders.ShipPostalCode = reader["ShipPostalCode"] != DBNull.Value ? Convert.ToString(reader["ShipPostalCode"]) : null;
orders.ShipCountry = reader["ShipCountry"] != DBNull.Value ? Convert.ToString(reader["ShipCountry"]) : null;
}
conn.Close();
}
if (orders.OrderID == 0)
{
return NotFound();
}
return Ok(orders);
}
// Create new order
public IHttpActionResult Post([FromBody]Orders orders)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("insert into orders(CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) values(@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry); select SCOPE_IDENTITY()", conn);
cmd.Parameters.AddWithValue("@CustomerID", orders.CustomerID);
cmd.Parameters.AddWithValue("@EmployeeID", orders.EmployeeID);
cmd.Parameters.AddWithValue("@OrderDate", orders.OrderDate);
cmd.Parameters.AddWithValue("@RequiredDate", orders.RequiredDate);
cmd.Parameters.AddWithValue("@ShippedDate", orders.ShippedDate);
cmd.Parameters.AddWithValue("@ShipVia", orders.ShipVia);
cmd.Parameters.AddWithValue("@Freight", orders.Freight);
cmd.Parameters.AddWithValue("@ShipName", orders.ShipName);
cmd.Parameters.AddWithValue("@ShipAddress", orders.ShipAddress);
cmd.Parameters.AddWithValue("@ShipCity", orders.ShipCity);
cmd.Parameters.AddWithValue("@ShipRegion", orders.ShipRegion);
cmd.Parameters.AddWithValue("@ShipPostalCode", orders.ShipPostalCode);
cmd.Parameters.AddWithValue("@ShipCountry", orders.ShipCountry);
conn.Open();
orders.OrderID = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return Created(Request.RequestUri + orders.OrderID.ToString(), orders);
}
// Update existing order
public IHttpActionResult Put(int id, [FromBody]Orders orders)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("update orders set CustomerID=@CustomerID, EmployeeID=@EmployeeID, OrderDate=@OrderDate, RequiredDate=@RequiredDate, ShippedDate=@ShippedDate, ShipVia=@ShipVia, Freight=@Freight, ShipName=@ShipName, ShipAddress=@ShipAddress, ShipCity=@ShipCity, ShipRegion=@ShipRegion, ShipPostalCode=@ShipPostalCode, ShipCountry=@ShipCountry where OrderID=" + id, conn);
cmd.Parameters.AddWithValue("@CustomerID", orders.CustomerID);
cmd.Parameters.AddWithValue("@EmployeeID", orders.EmployeeID);
cmd.Parameters.AddWithValue("@OrderDate", orders.OrderDate);
cmd.Parameters.AddWithValue("@RequiredDate", orders.RequiredDate);
cmd.Parameters.AddWithValue("@ShippedDate", orders.ShippedDate);
cmd.Parameters.AddWithValue("@ShipVia", orders.ShipVia);
cmd.Parameters.AddWithValue("@Freight", orders.Freight);
cmd.Parameters.AddWithValue("@ShipName", orders.ShipName);
cmd.Parameters.AddWithValue("@ShipAddress", orders.ShipAddress);
cmd.Parameters.AddWithValue("@ShipCity", orders.ShipCity);
cmd.Parameters.AddWithValue("@ShipRegion", orders.ShipRegion);
cmd.Parameters.AddWithValue("@ShipPostalCode", orders.ShipPostalCode);
cmd.Parameters.AddWithValue("@ShipCountry", orders.ShipCountry);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result == 0)
{
return NotFound();
}
}
return Ok(orders);
}
// Delete order
public IHttpActionResult Delete(int id)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("delete from orders where OrderID=" + id, conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result == 0)
{
return NotFound();
}
}
return Ok();
}
}
// Orders model class
public class Orders
{
public int OrderID { get; set; }
public string CustomerID { get; set; }
public int EmployeeID { get; set; }
public DateTime OrderDate { get; set; }
public DateTime RequiredDate { get; set; }
public DateTime ShippedDate { get; set; }
public int ShipVia { get; set; }
public decimal Freight { get; set; }
public string ShipName { get; set; }
public string ShipAddress { get; set; }
public string ShipCity { get; set; }
public string ShipRegion { get; set; }
public string ShipPostalCode { get; set; }
public string ShipCountry { get; set; }
}
}