如何使用C#和SQL将表值参数传递给用户定义的数据表

Question

下面是我的存储过程和C#代码。目前，我的行数是1000+，但当我做电视节目时。Rows.Count我得到90我相信我的for循环并没有真正正确地填充所有内容。下面是我的代码片段。请注意，数据表并没有填充SQL Server中的表。

存储过程：

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

第二个存储过程：

@totalRecords INT OUTPUT
INSERT INTO dbo.tablename1 
FROM dbo.tablename2
SELECT @totalRecords = COUNT(*) 
FROM dbo.[tableName2]

C#代码：

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();
}
}

Answer 1

在tvp中，您添加了90行，因此行数应仅为90。见下文-您已经将其迭代为arr.length，数组长度仅为90。

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