How to Insert foreign key values into database table

SQL FOREIGN KEY Constraint ?

How do I insert a row which contains a foreign key?

How to defined product under category?

In our last lecture we insert a data into category table with he following parameter (Name, Description, Thumbnail) Now here in this lecture we have to defined product tables value along with category id as a foreign key

Step 1: Defined model with the following parameter

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace Pick_n_Buy.Models
{
public class MdlProduct
{
[Key]
public int ID { get; set; }    //Product table key

public int Category { get; set; }  //category primary key as a foreign key in product table

[Required]
public string Name { get; set; }

[Required]
public double UnitPrice { get; set; }

public string Thumbnail { get; set; }

[Required]
public string Description { get; set; }

public List<MdlCategory> CategoryList { get; set; }   //category model list that we have to populate on product page load 

}
}

Step 2: Defined controller method with the following code based on this method we have to generate a view page by selecting the create option and the above model.

public ActionResult AddProduct()
{

try
{
BLL_Category obj = new BLL_Category();
MdlProduct mdl=new MdlProduct();
mdl.CategoryList = obj.BLL_ReadCategory();
return View(mdl);
}
catch (Exception)
{

throw;
}
}

This method will return the view that’s  being generated on the base of this but before return view statement we have to make get call from the database to list down all the category in product definition page, this get call is same we have discuss in previous lecture while reading all category table data (Display category page).

Step 3: What you have to change in your view page generated for product definition

@model Pick_n_Buy.Models.MdlProduct

@{
ViewBag.Title = “AddProduct”;
Layout = “~/Views/Shared/_LayoutPage1.cshtml”;
}

<h2 class=”text-secondary text-center”>AddProduct</h2>

@using (Html.BeginForm(“AddProduct”, “Admin”, FormMethod.Post, new { enctype = “multipart/form-data” }))  //Add method, controller and enctype to this tag to insert file and product table table 
{
@Html.AntiForgeryToken()

<div class=”form-horizontal p-4″>
@Html.ValidationSummary(true, “”, new { @class = “text-danger” })
<div class=”form-group”>
@Html.LabelFor(model => model.Category, htmlAttributes: new { @class = “control-label col-md-2” })
<div class=”col-md-10″>
@Html.DropDownListFor(md => md.Category, new SelectList(Model.CategoryList, “ID”, “Name”), “–Select Product–“, new { @class = “form-control text-center”, @id = “DrpDwn”, @style = “border-radius:17px;” }) // Replace the html with this dropdown html code
@Html.ValidationMessageFor(model => model.Category, “”, new { @class = “text-danger” })
</div>
</div>

<div class=”form-group”>
@Html.LabelFor(model => model.Name, htmlAttributes: new { @class = “control-label col-md-2” })
<div class=”col-md-10″>
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = “form-control” } })
@Html.ValidationMessageFor(model => model.Name, “”, new { @class = “text-danger” })
</div>
</div>

<div class=”form-group”>
@Html.LabelFor(model => model.UnitPrice, htmlAttributes: new { @class = “control-label col-md-2” })
<div class=”col-md-10″>
@Html.EditorFor(model => model.UnitPrice, new { htmlAttributes = new { @class = “form-control” } })
@Html.ValidationMessageFor(model => model.UnitPrice, “”, new { @class = “text-danger” })
</div>
</div>

<div class=”form-group”>
@Html.LabelFor(model => model.Thumbnail, htmlAttributes: new { @class = “control-label col-md-2” })
<div class=”col-md-10″>
<input type=”file” name=”file” /> // replace html code here with this file type to upload image
@Html.ValidationMessageFor(model => model.Thumbnail, “”, new { @class = “text-danger” })
</div>
</div>

<div class=”form-group”>
@Html.LabelFor(model => model.Description, htmlAttributes: new { @class = “control-label col-md-2” })
<div class=”col-md-10″>
@Html.TextAreaFor(model => model.Description, new { @cols = 15, @rows = 8, @class = “form-control” })
@Html.ValidationMessageFor(model => model.Description, “”, new { @class = “text-danger” })
</div>
</div>

<div class=”form-group”>
<div class=”col-md-offset-2 col-md-10″>
<input type=”submit” value=”Create” class=”btn btn-secondary” />
</div>
</div>
</div>
}

Step 4: Here you have to define method in controller to insert data received from the product view page, in this method all the work is same as we do while saving the category table values into database.

[HttpPost]
public ActionResult AddProduct(MdlProduct mdl, HttpPostedFileBase file)
{

try
{
var allowedextention = new[] { “.jpg”, “.Jpg”, “.jpeg”, “.png” };
if (file != null)
{
var ext = Path.GetExtension(file.FileName);
if (allowedextention.Contains(ext))
{
var filename = Path.GetFileName(file.FileName);
var path = “~/Images/Product_Image/” + filename+ext;
file.SaveAs(Server.MapPath(path));
mdl.Thumbnail = path.Replace(“~/”, “/../”);
}
}
if (ModelState.IsValid)
{
BLL_Product obj = new BLL_Product();
obj.Bll_Add_Product(mdl);
return RedirectToAction(“Product”, “Admin”);
}
return View();
}
catch (Exception)
{

throw;
}

}

Step 5: Defined the following method in BLL class

public void Bll_Add_Product(MdlProduct mdl)
{
DAL_Product obj = new DAL_Product();
obj.Dal_Add_Product(mdl);
}

Step 6: Defined the following method in DAL class, where you have to map all modal parameter data into your stored procedure variable

public void Dal_Add_Product(MdlProduct mdl)
{
try
{

SqlParameter[] parm = new SqlParameter[5];
parm[0] = new SqlParameter(“@name”, SqlDbType.NVarChar)
{
Value = mdl.Name
};
parm[1] = new SqlParameter(“@Description”, SqlDbType.NVarChar)
{
Value = mdl.Description
};
parm[2] = new SqlParameter(“@Thumbnail”, SqlDbType.NVarChar)
{
Value = mdl.Thumbnail
};
parm[3] = new SqlParameter(“@UnitPrice”, SqlDbType.Float)
{
Value = mdl.UnitPrice
};
parm[4] = new SqlParameter(“@Category”, SqlDbType.Int)
{
Value = mdl.Category
};
SqlHelper.SaveData(this._ConnString, CommandType.StoredProcedure, “SPW_AddProduct”, parm);

}
catch (Exception)
{

throw;
}
}

Step 7: Defined the following method in Sql helper class

public static int SaveData(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandparameters)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = commandType;
cmd.CommandText = commandText;

for (int i = 0; i < commandparameters.Length; i++)
{
cmd.Parameters.Add(commandparameters[i]);
}
cmd.Connection = con;
return cmd.ExecuteNonQuery();

}

}

Step 8: Stored Procedure for the table product having parameter name, thumbnail, description, unitprice and category(category id)

Create procedure [dbo].[SPW_AddProduct]
(
@name nvarchar(50),
@Thumbnail nvarchar(50),
@Description nvarchar(300),
@UnitPrice float,
@Category Int
)
As
Begin
INSERT INTO Tbl_Product(Name,Thumbnail,Description,UnitPrice,Category)
VALUES(@name,@Thumbnail,@Description,@UnitPrice,@Category)
End