ASP. net mvc interacts with SQL Server, inserts data, asp. netmvc

ASP. net mvc interacts with SQL Server, inserts data, asp. netmvc

 

In ASP. net mvc and SQL Server connection, it is connected to SQL Server. This practice inserts data into SQL Server.

 

Add the data insertion method to the Database Help class.

 

   public class SqlDB
    {
        protected SqlConnection conn;
// Open the connection
        public bool OpenConnection()
        {
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            try
            {
                bool result = true;
                if (conn.State.ToString() != "Open")
                {
                    conn.Open();
                }
                return result;
            }
            catch (SqlException ex)
            {
                return false;
            }
        }
// Close the connection
        public bool CloseConnection()
        {
            try
            {
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
// Insert data
        public int InsertData(string sql)
        {
            int lastId = 0;
            //string query = sql + ";SELECT @@Identity;";
            try
            {
                if(conn.State.ToString()=="Open")
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    //cmd.ExecuteNonQuery();
LastId = ToInt (cmd. ExecuteScalar (); // return the first column of the First row
                }
                return ToInt(lastId);
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
// Convert to an integer
        private int ToInt(object o)
        {
            try
            {
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
    }

 

Create a view model for the database Product.

 

    public class ProductVm
    {
[Required (ErrorMessage = "Required")]
        [StringLength(16)]
        public string Name { get; set; }
[Required (ErrorMessage = "Required")]
        [StringLength(16)]
        public string Quantity { get; set; }
[Required (ErrorMessage = "Required")]
        [StringLength(16)]
        public string Price { get; set; }
    }

 

Add two actions in TestController to process the added data.

 

    public class TestController : Controller
    {
        private SqlDB _db = new SqlDB();
        //
        // GET: /Test/
        public ActionResult Index()
        {
            bool r = _db.OpenConnection();
            if (r)
            {
Return Content ("connection successful ");
            }
            else
            {
Return Content ("connection failed ");
            }
        }
        public ActionResult AddProduct()
        {
            return View();
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult AddProduct(ProductVm productVm)
        {
            if(ModelState.IsValid)
            {
                _db.OpenConnection();
                int result = _db.InsertData("insert into Product(Name,quantity,Price) values('"+productVm.Name+"','"+productVm.Quantity+"','"+productVm.Price+"')");
                if(result > 0)
                {
ModelState. AddModelError ("success", "created successfully ");
                }
                else
                {
ModelState. AddModelError ("error", "failed to create ");
                }
                _db.CloseConnection();
                return View();
            }
            else
            {
                return View(productVm);
            }
        }
    }

 

In the Test/AddProduct View:

 

@model Portal.Models.ProductVm
@{
    ViewBag.Title = "AddProduct";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<H2> Create a product 
@using (Html.BeginForm("AddProduct", "Test", new { @id = "addForm" }, FormMethod.Post))
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        @Html.ValidationSummary(true)
        <div class="form-group">
            @Html.LabelFor(model => model.Name, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Name)
                @Html.ValidationMessageFor(model => model.Name)
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Quantity, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Quantity)
                @Html.ValidationMessageFor(model => model.Quantity)
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Price, new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Price)
                @Html.ValidationMessageFor(model => model.Price)
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
<Input type = "submit" value = "CREATE" class = "btn-default"/>
            </div>
        </div>
    </div>
}
<div>
    @Html.ActionLink("Back to List", "Index")
</div>