Stored Procedure

Question

Hi

How to insert records using single stored procedure with Ado.Net in MVC c#

public class Invoice
{
public Invoice()
{
}

    [Key]
    public string No { get; set; }

    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
    public string DocDate { get; set; }

    public virtual ICollection InvoiceDetails { get; set; }
    public string Status { get; set; }

}

public partial class InvoiceDetail
{
    public string DocNo { get; set; }
    public Product Id { get; set; }
    public decimal Quantity { get; set; }
    public Nullable price { get; set; }
    public Nullable GrossAmount { get; set; }
    public Nullable NetAmount { get; set; }
}

Thanks

Answer 1

You would use a table value parameter in the stored proc to pass a virtual table of rows to insert

https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15

And use ado.nets support of a table value parameters

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

Answer 2

Hi @Jagjit Saini ,
Using a single stored procedure means selecting, updating, inserting and deleting all SQL queries; we can put in one stored procedure and perform this CRUD operation by calling only this single stored procedure.
The steps is like this:

1. Create MVC Application
2. Create a model class file
3. Create a table to add stored procedure to perform CRUD operation in MVC.
4. Create a stored procedure to perform CRUD operation in MVC.
5. Create a class file called DataAccessLayer.cs inside manually created folder named DataAccess. To add connection string, add name as well as stored procedure name to perform CRUD role.
   Just like this:

       con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());    
       SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete_Customer", con);    
       cmd.CommandType = CommandType.StoredProcedure;    
       //cmd.Parameters.AddWithValue("@CustomerID", 0);    
       cmd.Parameters.AddWithValue("@Name", objcust.Name);    
       cmd.Parameters.AddWithValue("@Address", objcust.Address);    
       cmd.Parameters.AddWithValue("@Mobileno", objcust.Mobileno);    
       cmd.Parameters.AddWithValue("@Birthdate", objcust.Birthdate);    
       cmd.Parameters.AddWithValue("@EmailID", objcust.EmailID);    
       cmd.Parameters.AddWithValue("@Query", 1);    
       con.Open();    
       result = cmd.ExecuteScalar().ToString();    
       return result;    
   

6. Create a controller class file
   Just like this:

   public ActionResult InsertCustomer(Customer objCustomer)    
       {       
       objCustomer.Birthdate = Convert.ToDateTime(objCustomer.Birthdate);    
       if (ModelState.IsValid)     
       {    
           DataAccessLayer objDB = new DataAccessLayer();    
           string result = objDB.InsertData(objCustomer);    
           //ViewData["result"] = result;    
           TempData["result1"] = result;    
           ModelState.Clear(); //return View();    
           return RedirectToAction("ShowAllCustomerDetails");    
       }    
   
       else    
       {    
           ModelState.AddModelError("", "Error in saving data");    
           return View();    
       }               
   }   
   

7. Create view cshtml file.
8. Add connection string in Web.config file.

If you want to know how to create the stored procedure,you could refer to below article:
https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

Best regards,
Yijing Sun

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.