How to insert all rows data from a gridview to database

Convert Grid to dataTable and insert dataTable to your table like

public static void ConvertGridToTable(ref DataTable dt, ref GridView grd)

    {

        try

        {

            if (grd.Rows.Count <= 0) return;

 

            for (int i = 0; i <= grd.Columns.Count - 1; i++)

            {

                if (grd.Columns[i].GetType().Name.Equals("BoundField"))

                {

                    BoundField bf = (BoundField)grd.Columns[i];

                    dt.Columns.Add(bf.DataField.ToString());

                }

            }

 

            for (int i = 0; i <= grd.Rows.Count - 1; i++)

            {

                dt.Rows.Add();

                for (int j = 0; j <= grd.Columns.Count - 1; j++)

                {

                    if (grd.Columns[j].GetType().Name.Equals("BoundField"))

                    {

                        BoundField bf = (BoundField)grd.Columns[j];

                        for (int k = 0; k <= dt.Columns.Count - 1; k++)

                        {

                            if (dt.Columns[k].ColumnName.Trim().Equals(bf.DataField.ToString()))

                            {

                                string value = grd.Rows[i].Cells[j].Text.Trim().Contains(" ") ? grd.Rows[i].Cells[j].Text.Trim().Replace(" ", string.Empty) : grd.Rows[i].Cells[j].Text.Trim();

                                dt.Rows[i][bf.DataField.ToString()] = value;

                            }

                        }

                    }

                }

            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

    }

   Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(

    [Id] int NOT NULL,

    [Name] [nvarchar](128) NULL

)

and define a parameter in your Stored Ptocedure:

CREATE PROCEDURE [dbo].[InsertTable]

    @myTableType MyTableType readonly

AS

BEGIN

    insert into [dbo].Records select * from @myTableType

END

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})

{

    var dt = new DataTable(); //create your own data table

    command.Parameters.Add(new SqlParameter("@myTableType", dt));

    SqlHelper.Exec(command);

}