How to pass table value parameter to user defined data table using C# and SQL

Question

Below is my stored procedure and my C# code. Currently, my rows count is 1000+ but I when I do tvp.Rows.Count I get 90 I believe my for loop is not really populating everything correctly. Here is my code snippet below. Mind you that the datatable is not populating my table in SQL Server.

Stored procedure:

ALTER PROCEDURE [dbo].[TableName] 
    @dt AS dbo.DataTableAsType READONLY   
AS
BEGIN
   INSERT INTO dbo.[DataTableAsType] ([Column names]) --There are 89 column names
       SELECT
           ([ColumnNames])
       FROM 
           @dt
END

Second stored procedure:

    @totalRecords INT OUTPUT
INSERT INTO dbo.tablename1 
FROM dbo.tablename2

SELECT @totalRecords = COUNT(*) 
FROM dbo.[tableName2]

C# code:

public void InsertDataTableAF2CSV(string ext)
{
    DataTable tvp = new DataTable();
tvp = ReadFile(filename, "", null);

using (StreamReader sr = new StreamReader(filename))
    {
        //this assume the first record is filled with the column names   
        //if (headerRowHasBeenSkipped)
        string headerValue = sr.ReadLine();

        string[] headers = sr.ReadLine().Split(',');
        foreach (string header in headers)
        {
            tvp.Columns.Add(header);
        }
        while (!sr.EndOfStream)
        {
            string[] rows = sr.ReadLine().Split(',');
            //Create a new row
            DataRow dr = tvp.NewRow();
            for (int i = 0; i < headers.Length; i++)
            {
                dr[i] = rows[i];
            }
            tvp.Rows.Add(dr);
            // Console.WriteLine(tvp);
        }          
    }  

  // Passing a table-valued parameter to a stored procedure
    using (SqlConnection con = new SqlConnection(connection name))
    {
        connection.Open();
        // Execute the cmd
        // Configure the command and parameter. 
        SqlCommand cmd = new SqlCommand("dbo.storedprocedure", connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 5000;

        // SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);

        // Create a DataTable with the modified rows.  
        DataTable addedCategories = tvp.GetChanges(DataRowState.Added);

        // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
        SqlParameter parameter = new SqlParameter("@dt", SqlDbType.Structured)
                {
                    //TypeName = "dbo.DataTableAsType",
                    TypeName = "dbo.importDataTable",
                    Value = tvp
                };                                   

        cmd.ExecuteNonQuery();
        con.Close();
    }
}

try this https://stackoverflow.com/questions/12320594/passing-datatable-to-a-stored-procedure — Daniel N, Dec 13 '18 at 17:14
This line looks suspicious: `tvp= ReadFile(filename, " ", null);`. Isn't that replacing the data table with the one you just loaded? — Dan Guzman, Dec 13 '18 at 17:16
Possible duplicate of [Passing datatable to a stored procedure](https://stackoverflow.com/questions/12320594/passing-datatable-to-a-stored-procedure) — Dour High Arch, Dec 13 '18 at 17:33
You are looping over an array defined for `[89]` and adding a data row on each iteration. If the array is meant to be a list of columns then that is a misuse of the array. — Crowcoder, Dec 13 '18 at 17:53
I was trying to build a for loop to add my 89 columns and their values to those columns. I have over 3000+ rows of data. How can I correct it? — JuniorDev, Dec 13 '18 at 18:42
@DanielN I have used the msdn table value parameter which is why my code looks similar to that. https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/bb675163(v=vs.90) — JuniorDev, Dec 13 '18 at 18:45
@DourHighArch-I used that before which is why my code is similar but I don't use the datagrid view — JuniorDev, Dec 13 '18 at 18:45
@DanGuzman If it is replacing the table I loaded should I take that out? — JuniorDev, Dec 13 '18 at 18:46
@JuniorDev, yes, at it doesn't look like you want to replace the `DataTable` at that point in your code. If you want to add more rows to the TVP from the file source, pass the `tvp` variable as a parameter to the function and return `void` instead of returning a new data table. — Dan Guzman, Dec 13 '18 at 18:55
@JuniorDev, I would expect `tvp = ReadFile(...` to result in a compile error if the function returns `void`. — Dan Guzman, Dec 13 '18 at 22:26
@DanGuzman I re-wrote the code if u can give me feedback. I am trying to add the values from my csv file then import to sql table — JuniorDev, Dec 14 '18 at 14:56
@JuniorDev, what is the purpose of `tvp = ReadFile(filename, "", null);`? Despite the comments, you don't need to set the TypeName property when calling a stored procedure with a TVP. Post your actual proc code as the problem might be there. Add `Console.WriteLine(tvp.Rows.Count);` as the last line of your `InsertDataTableAF2CSV` method for debugging. — Dan Guzman, Dec 14 '18 at 16:14
@DanGuzman ok, so my problem is conversion nvarchar to float. MY row count is 3993. — JuniorDev, Dec 14 '18 at 20:56

score 0 · Answer 1 · answered Dec 13 '18 at 17:53

0

In tvp you are adding 90 rows , so row count should come as 90 only. See below - you have iterated it as arr.length and array length is 90 only.

       for (int i = 0; i <= arr.Length; i++)

answered Dec 13 '18 at 17:53

Gaurav

623
5
11

right so I have 80+ columns but I have 3000+ rows of data which is why I think my for loop is incorrect? – JuniorDev Dec 13 '18 at 18:40
should I do for (int i = 0; i <= tvp.Length; i++) // which is my datatable variable? What am I doing wrong? – JuniorDev Dec 13 '18 at 18:41
Get the idea from here - https://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable – Gaurav Dec 13 '18 at 18:43
@Guarav looks similar to what I am doing, I am uploading a CSV file passing a parameter to the stored procedure but I am assuming my for loop is wrong? Is that what your saying? When I look in the object 'tvp' I can see the row count being 3000+ but the 'arr' is 90 row count. – JuniorDev Dec 13 '18 at 18:48

How to pass table value parameter to user defined data table using C# and SQL

1 Answers1