Insert record to multiple Tables using ADO.Net in ASP.Net MVC

ramco191...

SOLVED CLOSED ACTIVE ANSWERED
User: ramco1917
Posted: on Jul 07, 2021 11:02 PM
Forum: ASP.Net MVC
Answer: 1
Views: 2018

I want to insert record using Ado.Net.

Below are the classes & the code i am trying

    public class InvoiceHeader
    {
        public InvoiceHeader()
        {
            //CreatedOn = DateTime.Now;
            //LastUpdatedOn = DateTime.Now;
        }

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

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

        [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}")]
        public DateTime PO_Date { get; set; }

        public List<InvoiceLines> _InvoiceLines { get; set; }
        public List<Items> _Items { get; set; }
    }
    -----------------------------------
    public class InvoiceLines
    {
        public string DocumentNo { get; set; }
        public Items Id { get; set; }
        public decimal Quantity { get; set; }
        public Nullable<decimal> price { get; set; }
        public Nullable<decimal> GrossAmount { get; set; }
        public Nullable<decimal> NetAmount { get; set; }
    }
    -----------------------------------
    public class Items
    {
        public Items()
        {
            //CreatedOn = DateTime.Now;
            //LastUpdatedOn = DateTime.Now;
        }
        [Key]
        [Required(ErrorMessage = "Please enter Id"), MaxLength(25)]
        [Display(Name = "Id")]
        public string Id { get; set; }
        [Required(ErrorMessage = "Please enter Description"), MaxLength(100)]
        [Display(Name = "Description")]
        public string Description { get; set; }
    }
    --------------------------------------------------------
    public int Add(InvoiceHeader objInvoice)
    {
            int i;
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("sp_Invoice", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@No", objInvoice.No);
                cmd.Parameters.AddWithValue("@No", objInvoice.DocumentDate);
                cmd.Parameters.AddWithValue("@No", objInvoice.Po_No);
                cmd.Parameters.AddWithValue("@No", objInvoice.PO_Date);
                cmd.Parameters.AddWithValue("@Action", "C");
                i = cmd.ExecuteNonQuery();
            }
            return i;
    }

Thanks

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Jul 08, 2021 04:32 AM
Report

Hi ramco1917,

Refer below code.

public void Add(InvoiceHeader objInvoice)
{
    string cs = "";
    // Insert to InvoiceHeader.
    using (SqlConnection con = new SqlConnection(cs))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("sp_Invoice", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@No", objInvoice.No);
        cmd.Parameters.AddWithValue("@DocumentDate", objInvoice.DocumentDate);
        cmd.Parameters.AddWithValue("@Po_No", objInvoice.Po_No);
        cmd.Parameters.AddWithValue("@PO_Date", objInvoice.PO_Date);
        cmd.Parameters.AddWithValue("@Action", "C");
        cmd.ExecuteNonQuery();
        con.Close();
    }

    // Insert to InvoiceLines.
    foreach (InvoiceLines line in objInvoice._InvoiceLines)
    {
        using (SqlConnection con = new SqlConnection(cs))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_InvoiceLines", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Id", line.Id);
            cmd.Parameters.AddWithValue("@DocumentNo", line.DocumentNo);
            cmd.Parameters.AddWithValue("@Quantity", line.Quantity);
            cmd.Parameters.AddWithValue("@price", line.price);
            cmd.Parameters.AddWithValue("@NetAmount", line.NetAmount);
            cmd.Parameters.AddWithValue("@GrossAmount", line.GrossAmount);
            cmd.Parameters.AddWithValue("@Action", "C");
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    // Insert to Items.
    foreach (Items item in objInvoice._Items)
    {
        using (SqlConnection con = new SqlConnection(cs))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_Items", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Id", item.Id);
            cmd.Parameters.AddWithValue("@Description", item.Description);
            cmd.Parameters.AddWithValue("@Action", "C");
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Insert record to multiple Tables using ADO.Net in ASP.Net MVC

Company

Explore

Follow us

Contact Us