Insert a dataSet into a SQL table

Question

I have a dataset filled up with data from 3 different table. I want to store that dataset in an empty table in SQL, i have already created the table.

public void SaveDataBaseTailleALL(DataGridView dataGridViewRALSelectedTaille, DataSet oDSALL)
{
    PointageCls.totalPointage(dataGridViewRALSelectedTaille);
    SqlConnection cn = new SqlConnection();
    cn.ConnectionString = "Data Source=HOOSSEN-HP\\INFO2;Initial Catalog=SuiviOF;User ID=sa;Password= PROTECTED;"
    //string strSQL = ")";

    SqlDataAdapter adapt = new SqlDataAdapter("select * from tblTailleALL)", cn);
    SqlCommandBuilder builder = new SqlCommandBuilder(adapt);
    adapt.update(oDSALL.Tables[0]);
    oDSALL.Tables[0].AcceptChanges();
}

What should I do to achieve saving the dataset in an empty table ?

thanks

Answer 1

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[TableType] AS TABLE(
[UserId] int NOT NULL,
[UserName] [nvarchar](128) NULL,
[Password] [varchar](30)

)

and define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]
@myTableType TableType readonly
AS
BEGIN
insert into [dbo].Users select * from @myTableType 
END

and send your DataTable directly to sql server:

 SqlCommand command = new SqlCommand("InsertTable");
 command.CommandType = CommandType.StoredProcedure;
 var dt = new DataTable(); //create your own data table
 command.Parameters.Add(new SqlParameter("@myTableType", dt));
 command.ExecuteNonQuery();