CODEWITHSUNDEEP
c#sql-serverdatagridview
sudhakarssd
sudhakarssd

Reputation: 451

Multiple insert to datagridview

I have a datagridview view that looks like this:

ProductId | Name | Price | Quantity

The datagridview starts with all cells empty.

The user will enter data in each of the columns. The number of products the user enters changes(is unknown). The user could enter only one product or five products.

So, once the user has entered all the products he has to enter, i want that when he clicks a button, all products will be saved to the database.

The table in the sql database looks like the datagrid:

ProductId | Name | Price | Quantity

As the number of products that the user will enter is unknown, how can I save the data to the database?

Upvotes: 0

Views: 2011

Answers (1)

praveen
praveen

Reputation: 12271

You can either use table value parameters or a stored proc which takes xml as an input for multiple insertion by just hitting the database once .

Table Value parameter:-

  CREATE TYPE dbo.ProductType AS TABLE
   ( ProductID int, Name nvarchar(250),Price decimal(10,2),Quantity int )

  CREATE PROCEDURE sp_InsertProducts 
  (@tvpProducts dbo.ProductType READONLY)
  AS
  BEGIN 
  INSERT INTO dbo.Products (ProductID , Name ,Price,Quantity  )
  SELECT a.ProductID ,a.Name ,a.Price, a.Quantity  FROM @tvpProducts AS a;
  END

In your C# application create a list of products and add the items whenever the user creates a new Product and at the end when user clicks on Sumbit button then convert List to datatable and execute your sql query

when the user clicks on new button to add the products then use collection to add the items

   List<Product> _product= new List<Product>
   _product.Add()
    {
     //Bind the gridview columns to the respective fields of the product class
    };

On clicking the submit button write the following code

   DataTable dtProducts = ConvertListToDataTable(_product);
   SqlCommand cmd = new SqlCommand("sp_InsertProducts", sqlConnection);
   SqlParameter p = cmd.Parameters.AddWithValue("@tvpNewDistricts", dtProducts );
   p.SqlDbType = SqlDbType.Structured;
   p.TypeName = "dbo.ProductType";
   cmd.ExecuteNonQuery();

If you want to use XML Stored proc then refer my other answer

Upvotes: 1

Related Questions