How to insert a datatable in a table using stored procedure in SQL Server?

Question

Need to post more than 10 data using wcf services.I am getting a datatable from c# code.How to insert a datatable in a table using stored procedure ?

    public static DataTable Get_JobTypeList() { 
       return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "JMPS_GET_Jobs_Type").Tables[0];
 }

public static DataTable Get_JobTypeList() { return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "JMPS_GET_Jobs_Type").Tables[0]; } — Bikash Panigrahi, Feb 26 '16 at 04:05
This post could help you .. it has issue as you want http://stackoverflow.com/questions/10409576/pass-table-valued-parameter-using-ado-net — Moumit, Feb 26 '16 at 05:23

score 0 · Accepted Answer · answered Feb 26 '16 at 04:06

The following stored procedure is created which will accept the DataTable as parameter and then will insert all records into the table.

CREATE PROCEDURE [dbo].[Insert_Customers]
          @tblCustomers CustomerType READONLY
    AS
    BEGIN
          SET NOCOUNT ON;

          INSERT INTO Customers(CustomerId, Name, Country)
          SELECT Id, Name, Country FROM @tblCustomers
    END

score 0 · Answer 2 · answered Feb 26 '16 at 04:15

Write a stored procedure in database side.
List the parameters to be passed in List<SqlParameter> and call the following function:

public static int ExecSqlNonQuery(string storedProcName, List<SqlParameter> listSqlParams)
    {
        SqlConnection Conn = new SqlConnection("YourConnectionString");
        SqlCommand cmd = new SqlCommand();
        try
        {
            getSqlPara(listSqlParams, cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Conn;
            Conn.Open();
            cmd.CommandText = storedProcName;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn.State != ConnectionState.Closed)
            {
                Conn.Close();
                Conn.Dispose();
            }
            cmd.Dispose();
        }
        //return 0;
    }

How to insert a datatable in a table using stored procedure in SQL Server?

2 Answers2