c#:将dataTable传递到存储过程:列顺序问题。

时间:2022-09-24 10:08:38

I have a stored procedure which accepts a User defined table type called SeasonTable which looks like this:

我有一个存储过程,它接受一个用户定义的表类型,称为季节性表,它看起来是这样的:

SeasonTable

SeasonTable

    [SeasonId] [int] NOT NULL,
    [SeasonName] [varchar](50) NOT NULL,
    [Month] [int] NULL, 
    [IsDeleted] [bit] NOT NULL

Stored procedure is like as shown below

存储过程如下所示

CREATE PROCEDURE [dbo].[udpTest] 
(
    @STable dbo.SeasonTable READONLY
)   
AS
BEGIN
SELECT [SeasonName] as SeasonName,[SeasonId],[Month],[IsDeleted] 
    FROM @AdminBillingSeasonsTable
END

When I call this from C# application

当我从c#应用程序调用它时

private DataTable TestCreateDataTable()
{
            DataTable dt = new DataTable();

            dt.Columns.Add("SeasonName", typeof(string));
            dt.Columns.Add("SeasonId", typeof(int));                
            dt.Columns.Add("Month", typeof(int));
            dt.Columns.Add("IsDeleted", typeof(bool));
            dt.Rows.Add("season1",1, 4,  false);
            dt.Rows.Add("season2",2, 9, false);
            dt.Rows.Add("season3",3, 11,  false);
            return dt;
}

When I use the above table as param to SP from C# app it is throwing error:

当我使用上面的表格作为param从c#应用程序时,它会抛出错误:

unable to convert "season1" to int.

无法将“season1”转换为int。

Does this mean the column order in C# app should be same as the column order in SQL?

这是否意味着c# app中的列顺序应该与SQL中的列顺序相同?

Any help regarding this is highly appreciated.

对此有任何帮助,不胜感激。

Thanks in advance

谢谢提前

1 个解决方案

#1


5  

Try Switching your column sequence something like this

试试像这样切换列序列

 private DataTable TestCreateDataTable()
            {
                DataTable dt = new DataTable();

                dt.Columns.Add("SeasonId", typeof(int));     // seasonId and then
                dt.Columns.Add("SeasonName", typeof(string)); // seasonName
                dt.Columns.Add("Month", typeof(int));
                dt.Columns.Add("IsDeleted", typeof(bool));
                dt.Rows.Add("season1",1, 4,  false);
                dt.Rows.Add("season2",2, 9, false);
                dt.Rows.Add("season3",3, 11,  false);
                return dt;
            }

#1


5  

Try Switching your column sequence something like this

试试像这样切换列序列

 private DataTable TestCreateDataTable()
            {
                DataTable dt = new DataTable();

                dt.Columns.Add("SeasonId", typeof(int));     // seasonId and then
                dt.Columns.Add("SeasonName", typeof(string)); // seasonName
                dt.Columns.Add("Month", typeof(int));
                dt.Columns.Add("IsDeleted", typeof(bool));
                dt.Rows.Add("season1",1, 4,  false);
                dt.Rows.Add("season2",2, 9, false);
                dt.Rows.Add("season3",3, 11,  false);
                return dt;
            }