question

JagjitSaini-0360 avatar image
1 Vote"
JagjitSaini-0360 asked YijingSun-MSFT answered

Stored Procedure

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

dotnet-aspnet-mvc
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BruceBarker-8516 avatar image
0 Votes"
BruceBarker-8516 answered

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

https://docs.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://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered

Hi @JagjitSaini-0360 ,
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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.