Passing DataSet to Stored Procedure

Question

I have the following Stored Procedure that receives a DataSet as parameter and Inserts into table Excel.

CREATE PROCEDURE spInsertInvoice
      @tblInvoice InvoiceType READONLY
AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO Excel
      SELECT Template, Cust_Name, Invoice_No,InvoiceDate FROM @tblInvoice
END

In my code file I am trying to read the Excel Sheet and filling the dataset. But problem is I am a bit confused as to how should I send the DataSet as Parameter to the stored Procedure. This is what I have tried so far, but it doesn't seem to work

if (FileUpload1.HasFile)
        {
            string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
            FileUpload1.PostedFile.SaveAs(path);
            OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0;");
            OleDbCommand Olecmd = new OleDbCommand("select * from [Sheet1$]", oleCon);
            OleDbDataAdapter dtap = new OleDbDataAdapter(Olecmd);
            DataSet ds = new DataSet();
            dtap.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            if (ds.Tables[0].Rows.Count > 0)
            {
                string consString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlCommand cmd = new SqlCommand("spInsertInvoice"))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@tblInvoice", ds);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }

When I execute it, it throws ArgumentException on cmd.ExecuteNonQuery()

No mapping exists from object type System.Data.DataSet to a known managed provider native type.

Solution 1

You cannot pass dataset to stored procedure but you can pass datatable to stored procedure. Follow below algorithm to execute it:

1) Create Table type in sql server for the DataTable which you want to pass.
2) Declare input variable for given table type as readonly in stored procedure.
3) Pass that data table to procedure.

This only restricts your table type parameter sequence and datatable column sequence should be same.

You can refer this link Sending a DataTable to a Stored Procedure

Or Table-Valued Parameters

Solution 2

You can't parameterize your table name, basically.

Parameterized SQL is just for values - not table names, column names, or any other database objects. This is one place where you do probably want to build the SQL dynamically - but with a white-listed set of options or strong validation before you put this table name in your sql query.

It that line;

cmd.Parameters.AddWithValue("@tblInvoice", ds);

You try to pass your DataSet to your table name which does not make sense.

Comments

[Martin]

What happens when you execute this?

[Nuke]

I have Updated The post.

[Panagiotis Kanavos]

Table valued parameters work with DataTables, not DataSets. Pass the dataset's table as a parameter value

[Panagiotis Kanavos]

@Nuke TVPs work with datatables, not datasets. Pass the datatable you want as a parameter value