c# System.InvalidCastException when passing datatable to stored procedure
I'm defining a UDT in my MS SQL Server 2012 DB and using that in a stored procedure in the database. I'm trying to call my stored procedure from my c# program and I'm getting an error. Here's my code:
This is the definition of my UDT:
CREATE TYPE [dbo].[udt_850File] AS TABLE(
[strInbound850FromPharmacyFilename] [nvarchar](1000) NULL,
[strSenderID_ISA06] [nvarchar](15) NOT NULL,
[strReceiverID_ISA08] [nvarchar](15) NOT NULL,
[strInterchangeControlVersion_ISA12] [nvarchar](5) NOT NULL,
[strInterchangeControlNumber_ISA13] [nvarchar](9) NOT NULL,
[chElementDelimiter] [nchar](1) NOT NULL,
[chComponentElementDelimiter] [nchar](1) NOT NULL,
[chSegmentTerminator] [nchar](1) NOT NULL,
[intTransmissionDate_GS04] [int] NOT NULL,
[strVersionRelease_GS08] [nvarchar](12) NOT NULL,
[strTransSetControl_ST02] [nvarchar](9) NOT NULL,
[strPONumber_BEG03] [nvarchar](22) NOT NULL,
[intPODate_BEG05] [int] NOT NULL,
[strWholesalerAccountNum_N1BY04] [nvarchar](80) NOT NULL,
[dtFileCreatedDateTime] [datetime] NOT NULL
)
Here is my stored procedure definition:
CREATE PROCEDURE [dbo].[ppisp_sp_850Files_Insert]
@850FileData [dbo].[udt_850File] READONLY
AS
BEGIN
DECLARE @ReturnVal INT = 0
MERGE INTO [dbo].[850Files] AS target
USING @850FileData AS source
ON source.[strInbound850FromPharmacyFilename] = target.[strInbound850FromPharmacyFilename]
AND source.[strSenderID_ISA06] = target.[strSenderID_ISA06]
AND source.[strReceiverID_ISA08] = target.[strReceiverID_ISA08]
AND source.[strInterchangeControlVersion_ISA12] = target.[strInterchangeControlVersion_ISA12]
AND source.[strInterchangeControlNumber_ISA13] = target.[strInterchangeControlNumber_ISA13]
AND source.[chElementDelimiter] = target.[chElementDelimiter]
AND source.[chComponentElementDelimiter] = target.[chComponentElementDelimiter]
AND source.[chSegmentTerminator] = target.[chSegmentTerminator]
AND source.[intTransmissionDate_GS04] = target.[intTransmissionDate_GS04]
AND source.[strVersionRelease_GS08] = target.[strVersionRelease_GS08]
AND source.[strTransSetControl_ST02] = target.[strTransSetControl_ST02]
AND source.[strPONumber_BEG03] = target.[strPONumber_BEG03]
AND source.[intPODate_BEG05] = target.[intPODate_BEG05]
AND source.[strWholesalerAccountNum_N1BY04] = target.[strWholesalerAccountNum_N1BY04]
AND source.[dtFileCreatedDateTime] = target.[dtFileCreatedDateTime]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([strInbound850FromPharmacyFilename]
, [strSenderID_ISA06]
, [strReceiverID_ISA08]
, [strInterchangeControlVersion_ISA12]
, [strInterchangeControlNumber_ISA13]
, [chElementDelimiter]
, [chComponentElementDelimiter]
, [chSegmentTerminator]
, [intTransmissionDate_GS04]
, [strVersionRelease_GS08]
, [strTransSetControl_ST02]
, [strPONumber_BEG03]
, [intPODate_BEG05]
, [strWholesalerAccountNum_N1BY04]
, [dtFileCreatedDateTime])
VALUES (source.[strInbound850FromPharmacyFilename]
, source.[strSenderID_ISA06]
, source.[strReceiverID_ISA08]
, source.[strInterchangeControlVersion_ISA12]
, source.[strInterchangeControlNumber_ISA13]
, source.[chElementDelimiter]
, source.[chComponentElementDelimiter]
, source.[chSegmentTerminator]
, source.[intTransmissionDate_GS04]
, source.[strVersionRelease_GS08]
, source.[strTransSetControl_ST02]
, source.[strPONumber_BEG03]
, source.[intPODate_BEG05]
, source.[strWholesalerAccountNum_N1BY04]
, source.[dtFileCreatedDateTime]);
SET @ReturnVal = @@ROWCOUNT
END
Here is my c# class file where I define my datatable and populate it with data:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace PP_ISP_File
{
public class PP_ISP_File_Data
{
public DataTable dt_Files_Info { get; set; }
public PP_ISP_File_Data(string strFileDirectoryPath, int intFileType)
{
dt_Files_Info = new DataTable();
dt_Files_Info.Columns.Add("strSenderID_ISA06", typeof(string));
dt_Files_Info.Columns["strSenderID_ISA06"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strReceiverID_ISA08", typeof(string));
dt_Files_Info.Columns["strReceiverID_ISA08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlVersion_ISA12", typeof(string));
dt_Files_Info.Columns["strInterchangeControlVersion_ISA12"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlNumber_ISA13", typeof(string));
dt_Files_Info.Columns["strInterchangeControlNumber_ISA13"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("chElementDelimiter", typeof(char));
dt_Files_Info.Columns["chElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chComponentElementDelimiter", typeof(char));
dt_Files_Info.Columns["chComponentElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chSegmentTerminator", typeof(char));
dt_Files_Info.Columns["chSegmentTerminator"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("intTransmissionDate_GS04", typeof(int));
dt_Files_Info.Columns["intTransmissionDate_GS04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strVersionRelease_GS08", typeof(string));
dt_Files_Info.Columns["strVersionRelease_GS08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strTransSetControl_ST02", typeof(string));
dt_Files_Info.Columns["strTransSetControl_ST02"].DefaultValue = default(string);
if(intFileType == 850)
{
dt_Files_Info.Columns.Add("strInbound850FromPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strInbound850FromPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BEG03", typeof(string));
dt_Files_Info.Columns["strPONumber_BEG03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BEG05", typeof(int));
dt_Files_Info.Columns["intPODate_BEG05"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1BY04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1BY04"].DefaultValue = default(string);
}
else if(intFileType == 855)
{
dt_Files_Info.Columns.Add("strOutbound855ToPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strOutbound855ToPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BAK03", typeof(string));
dt_Files_Info.Columns["strPONumber_BAK03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BAK04", typeof(int));
dt_Files_Info.Columns["intPODate_BAK04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1ST04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1ST04"].DefaultValue = default(string);
}
dt_Files_Info.Columns.Add("dtFileCreatedDateTime", typeof(DateTime));
dt_Files_Info.Columns["dtFileCreatedDateTime"].DefaultValue = default(DateTime);
dt_Files_Info = GetFileDetails(strFileDirectoryPath, intFileType);
}
private DataTable GetFileDetails(string strFileDirectoryPath, int intFileType)
{
DataTable dtPP_ISP_Files_Return = dt_Files_Info.Clone();
DirectoryInfo diDirectoryInfo = new DirectoryInfo(strFileDirectoryPath);
if (diDirectoryInfo.Exists)
{
foreach (FileInfo fiFileInfo in diDirectoryInfo.GetFiles())
{
if (fiFileInfo.Exists)
{
using (StreamReader srFile = new StreamReader(fiFileInfo.FullName))
{
string strFileData = System.IO.File.ReadAllText(fiFileInfo.FullName);
if (strFileData.Length >= 104)
{
DataRow dr = dtPP_ISP_Files_Return.NewRow();
char chElementDelimiter = default(char);
char chComponentElementDelimiter = default(char);
char chSegmentTerminator = default(char);
chElementDelimiter = Convert.ToChar(strFileData.Substring(3, 1));
dr["chElementDelimiter"] = chElementDelimiter;
chComponentElementDelimiter = Convert.ToChar(strFileData.Substring(104, 1));
dr["chComponentElementDelimiter"] = chComponentElementDelimiter;
chSegmentTerminator = Convert.ToChar(strFileData.Substring(105, 1));
dr["chSegmentTerminator"] = chSegmentTerminator;
dr["dtFileCreatedDateTime"] = File.GetCreationTime(strFileDirectoryPath);
string arrFileLines = strFileData.Split(Convert.ToChar(chSegmentTerminator));
for (int i = 0; i <= arrFileLines.Length - 1; i++)
{
string arrLineData = arrFileLines[i].Split(Convert.ToChar(chElementDelimiter));
string strLineQualifier = arrLineData[0].Trim();
switch (strLineQualifier)
{
case "ISA":
dr["strSenderID_ISA06"] = arrLineData[6].Trim();
dr["strReceiverID_ISA08"] = arrLineData[8].Trim();
dr["strInterchangeControlVersion_ISA12"] = arrLineData[12].Trim();
dr["strInterchangeControlNumber_ISA13"] = arrLineData[13].Trim();
break;
case "GS":
dr["intTransmissionDate_GS04"] = Convert.ToInt32(arrLineData[4].Trim());
dr["strVersionRelease_GS08"] = arrLineData[8].Trim();
break;
case "ST":
dr["strTransSetControl_ST02"] = arrLineData[2].Trim();
break;
case "BEG":
dr["strInbound850FromPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BEG03"] = arrLineData[3].Trim();
dr["intPODate_BEG05"] = Convert.ToInt32(arrLineData[5].Trim());
break;
case "BAK":
dr["strOutbound855ToPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BAK03"] = arrLineData[3].Trim();
dr["intPODate_BAK04"] = Convert.ToInt32(arrLineData[4].Trim());
break;
case "N1":
if(arrLineData[1].Trim() == "BY")
{
dr["strWholesalerAccountNum_N1BY04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
else if (arrLineData[1].Trim() == "ST")
{
dr["strWholesalerAccountNum_N1ST04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
break;
default:
break;
}
}
}
}
}
}
}
return dtPP_ISP_Files_Return;
}
}
}
and lastly here is my code where I initialize my datatable and try to pass it to the stored procedure in my database:
using (SqlCommand cmd = new SqlCommand(strSPName, con))
{
PP_ISP_File_Data dtFileInfo = new PP_ISP_File_Data(strWatchPath, intFileType);
cmd.CommandText = strSPName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter spFilenames = new SqlParameter("@850FileData", SqlDbType.Structured);
spFilenames.TypeName = "dbo.udt_850File";
spFilenames.Value = dtFileInfo;
cmd.Parameters.Add(spFilenames);
SqlParameter returnParameter = new SqlParameter("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
con.Open();
cmd.ExecuteNonQuery();
int result = Convert.ToInt16(returnParameter.Value);
}
Running my code gives me this error:
System.InvalidCastException: 'Failed to convert parameter value from a
PP_ISP_File_Data to a IEnumerable`1.'
I have a suspicion that because I'm declaring my datatable in a class file it's not getting something that it needs to be able to enumerate over it when it gets passed to the stored procedure but I don't know how to correct that. Can someone please point me in the right direction to fix this? Thanks in advance.
c# sql-server datatable parameter-passing
add a comment |
I'm defining a UDT in my MS SQL Server 2012 DB and using that in a stored procedure in the database. I'm trying to call my stored procedure from my c# program and I'm getting an error. Here's my code:
This is the definition of my UDT:
CREATE TYPE [dbo].[udt_850File] AS TABLE(
[strInbound850FromPharmacyFilename] [nvarchar](1000) NULL,
[strSenderID_ISA06] [nvarchar](15) NOT NULL,
[strReceiverID_ISA08] [nvarchar](15) NOT NULL,
[strInterchangeControlVersion_ISA12] [nvarchar](5) NOT NULL,
[strInterchangeControlNumber_ISA13] [nvarchar](9) NOT NULL,
[chElementDelimiter] [nchar](1) NOT NULL,
[chComponentElementDelimiter] [nchar](1) NOT NULL,
[chSegmentTerminator] [nchar](1) NOT NULL,
[intTransmissionDate_GS04] [int] NOT NULL,
[strVersionRelease_GS08] [nvarchar](12) NOT NULL,
[strTransSetControl_ST02] [nvarchar](9) NOT NULL,
[strPONumber_BEG03] [nvarchar](22) NOT NULL,
[intPODate_BEG05] [int] NOT NULL,
[strWholesalerAccountNum_N1BY04] [nvarchar](80) NOT NULL,
[dtFileCreatedDateTime] [datetime] NOT NULL
)
Here is my stored procedure definition:
CREATE PROCEDURE [dbo].[ppisp_sp_850Files_Insert]
@850FileData [dbo].[udt_850File] READONLY
AS
BEGIN
DECLARE @ReturnVal INT = 0
MERGE INTO [dbo].[850Files] AS target
USING @850FileData AS source
ON source.[strInbound850FromPharmacyFilename] = target.[strInbound850FromPharmacyFilename]
AND source.[strSenderID_ISA06] = target.[strSenderID_ISA06]
AND source.[strReceiverID_ISA08] = target.[strReceiverID_ISA08]
AND source.[strInterchangeControlVersion_ISA12] = target.[strInterchangeControlVersion_ISA12]
AND source.[strInterchangeControlNumber_ISA13] = target.[strInterchangeControlNumber_ISA13]
AND source.[chElementDelimiter] = target.[chElementDelimiter]
AND source.[chComponentElementDelimiter] = target.[chComponentElementDelimiter]
AND source.[chSegmentTerminator] = target.[chSegmentTerminator]
AND source.[intTransmissionDate_GS04] = target.[intTransmissionDate_GS04]
AND source.[strVersionRelease_GS08] = target.[strVersionRelease_GS08]
AND source.[strTransSetControl_ST02] = target.[strTransSetControl_ST02]
AND source.[strPONumber_BEG03] = target.[strPONumber_BEG03]
AND source.[intPODate_BEG05] = target.[intPODate_BEG05]
AND source.[strWholesalerAccountNum_N1BY04] = target.[strWholesalerAccountNum_N1BY04]
AND source.[dtFileCreatedDateTime] = target.[dtFileCreatedDateTime]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([strInbound850FromPharmacyFilename]
, [strSenderID_ISA06]
, [strReceiverID_ISA08]
, [strInterchangeControlVersion_ISA12]
, [strInterchangeControlNumber_ISA13]
, [chElementDelimiter]
, [chComponentElementDelimiter]
, [chSegmentTerminator]
, [intTransmissionDate_GS04]
, [strVersionRelease_GS08]
, [strTransSetControl_ST02]
, [strPONumber_BEG03]
, [intPODate_BEG05]
, [strWholesalerAccountNum_N1BY04]
, [dtFileCreatedDateTime])
VALUES (source.[strInbound850FromPharmacyFilename]
, source.[strSenderID_ISA06]
, source.[strReceiverID_ISA08]
, source.[strInterchangeControlVersion_ISA12]
, source.[strInterchangeControlNumber_ISA13]
, source.[chElementDelimiter]
, source.[chComponentElementDelimiter]
, source.[chSegmentTerminator]
, source.[intTransmissionDate_GS04]
, source.[strVersionRelease_GS08]
, source.[strTransSetControl_ST02]
, source.[strPONumber_BEG03]
, source.[intPODate_BEG05]
, source.[strWholesalerAccountNum_N1BY04]
, source.[dtFileCreatedDateTime]);
SET @ReturnVal = @@ROWCOUNT
END
Here is my c# class file where I define my datatable and populate it with data:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace PP_ISP_File
{
public class PP_ISP_File_Data
{
public DataTable dt_Files_Info { get; set; }
public PP_ISP_File_Data(string strFileDirectoryPath, int intFileType)
{
dt_Files_Info = new DataTable();
dt_Files_Info.Columns.Add("strSenderID_ISA06", typeof(string));
dt_Files_Info.Columns["strSenderID_ISA06"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strReceiverID_ISA08", typeof(string));
dt_Files_Info.Columns["strReceiverID_ISA08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlVersion_ISA12", typeof(string));
dt_Files_Info.Columns["strInterchangeControlVersion_ISA12"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlNumber_ISA13", typeof(string));
dt_Files_Info.Columns["strInterchangeControlNumber_ISA13"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("chElementDelimiter", typeof(char));
dt_Files_Info.Columns["chElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chComponentElementDelimiter", typeof(char));
dt_Files_Info.Columns["chComponentElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chSegmentTerminator", typeof(char));
dt_Files_Info.Columns["chSegmentTerminator"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("intTransmissionDate_GS04", typeof(int));
dt_Files_Info.Columns["intTransmissionDate_GS04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strVersionRelease_GS08", typeof(string));
dt_Files_Info.Columns["strVersionRelease_GS08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strTransSetControl_ST02", typeof(string));
dt_Files_Info.Columns["strTransSetControl_ST02"].DefaultValue = default(string);
if(intFileType == 850)
{
dt_Files_Info.Columns.Add("strInbound850FromPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strInbound850FromPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BEG03", typeof(string));
dt_Files_Info.Columns["strPONumber_BEG03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BEG05", typeof(int));
dt_Files_Info.Columns["intPODate_BEG05"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1BY04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1BY04"].DefaultValue = default(string);
}
else if(intFileType == 855)
{
dt_Files_Info.Columns.Add("strOutbound855ToPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strOutbound855ToPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BAK03", typeof(string));
dt_Files_Info.Columns["strPONumber_BAK03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BAK04", typeof(int));
dt_Files_Info.Columns["intPODate_BAK04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1ST04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1ST04"].DefaultValue = default(string);
}
dt_Files_Info.Columns.Add("dtFileCreatedDateTime", typeof(DateTime));
dt_Files_Info.Columns["dtFileCreatedDateTime"].DefaultValue = default(DateTime);
dt_Files_Info = GetFileDetails(strFileDirectoryPath, intFileType);
}
private DataTable GetFileDetails(string strFileDirectoryPath, int intFileType)
{
DataTable dtPP_ISP_Files_Return = dt_Files_Info.Clone();
DirectoryInfo diDirectoryInfo = new DirectoryInfo(strFileDirectoryPath);
if (diDirectoryInfo.Exists)
{
foreach (FileInfo fiFileInfo in diDirectoryInfo.GetFiles())
{
if (fiFileInfo.Exists)
{
using (StreamReader srFile = new StreamReader(fiFileInfo.FullName))
{
string strFileData = System.IO.File.ReadAllText(fiFileInfo.FullName);
if (strFileData.Length >= 104)
{
DataRow dr = dtPP_ISP_Files_Return.NewRow();
char chElementDelimiter = default(char);
char chComponentElementDelimiter = default(char);
char chSegmentTerminator = default(char);
chElementDelimiter = Convert.ToChar(strFileData.Substring(3, 1));
dr["chElementDelimiter"] = chElementDelimiter;
chComponentElementDelimiter = Convert.ToChar(strFileData.Substring(104, 1));
dr["chComponentElementDelimiter"] = chComponentElementDelimiter;
chSegmentTerminator = Convert.ToChar(strFileData.Substring(105, 1));
dr["chSegmentTerminator"] = chSegmentTerminator;
dr["dtFileCreatedDateTime"] = File.GetCreationTime(strFileDirectoryPath);
string arrFileLines = strFileData.Split(Convert.ToChar(chSegmentTerminator));
for (int i = 0; i <= arrFileLines.Length - 1; i++)
{
string arrLineData = arrFileLines[i].Split(Convert.ToChar(chElementDelimiter));
string strLineQualifier = arrLineData[0].Trim();
switch (strLineQualifier)
{
case "ISA":
dr["strSenderID_ISA06"] = arrLineData[6].Trim();
dr["strReceiverID_ISA08"] = arrLineData[8].Trim();
dr["strInterchangeControlVersion_ISA12"] = arrLineData[12].Trim();
dr["strInterchangeControlNumber_ISA13"] = arrLineData[13].Trim();
break;
case "GS":
dr["intTransmissionDate_GS04"] = Convert.ToInt32(arrLineData[4].Trim());
dr["strVersionRelease_GS08"] = arrLineData[8].Trim();
break;
case "ST":
dr["strTransSetControl_ST02"] = arrLineData[2].Trim();
break;
case "BEG":
dr["strInbound850FromPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BEG03"] = arrLineData[3].Trim();
dr["intPODate_BEG05"] = Convert.ToInt32(arrLineData[5].Trim());
break;
case "BAK":
dr["strOutbound855ToPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BAK03"] = arrLineData[3].Trim();
dr["intPODate_BAK04"] = Convert.ToInt32(arrLineData[4].Trim());
break;
case "N1":
if(arrLineData[1].Trim() == "BY")
{
dr["strWholesalerAccountNum_N1BY04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
else if (arrLineData[1].Trim() == "ST")
{
dr["strWholesalerAccountNum_N1ST04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
break;
default:
break;
}
}
}
}
}
}
}
return dtPP_ISP_Files_Return;
}
}
}
and lastly here is my code where I initialize my datatable and try to pass it to the stored procedure in my database:
using (SqlCommand cmd = new SqlCommand(strSPName, con))
{
PP_ISP_File_Data dtFileInfo = new PP_ISP_File_Data(strWatchPath, intFileType);
cmd.CommandText = strSPName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter spFilenames = new SqlParameter("@850FileData", SqlDbType.Structured);
spFilenames.TypeName = "dbo.udt_850File";
spFilenames.Value = dtFileInfo;
cmd.Parameters.Add(spFilenames);
SqlParameter returnParameter = new SqlParameter("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
con.Open();
cmd.ExecuteNonQuery();
int result = Convert.ToInt16(returnParameter.Value);
}
Running my code gives me this error:
System.InvalidCastException: 'Failed to convert parameter value from a
PP_ISP_File_Data to a IEnumerable`1.'
I have a suspicion that because I'm declaring my datatable in a class file it's not getting something that it needs to be able to enumerate over it when it gets passed to the stored procedure but I don't know how to correct that. Can someone please point me in the right direction to fix this? Thanks in advance.
c# sql-server datatable parameter-passing
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
How aboutspFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object intoSqlDbType.Structured
, you need aDataTable
or collections which convertible withAsEnumerable()
.
– Tetsuya Yamamoto
Nov 21 '18 at 4:39
add a comment |
I'm defining a UDT in my MS SQL Server 2012 DB and using that in a stored procedure in the database. I'm trying to call my stored procedure from my c# program and I'm getting an error. Here's my code:
This is the definition of my UDT:
CREATE TYPE [dbo].[udt_850File] AS TABLE(
[strInbound850FromPharmacyFilename] [nvarchar](1000) NULL,
[strSenderID_ISA06] [nvarchar](15) NOT NULL,
[strReceiverID_ISA08] [nvarchar](15) NOT NULL,
[strInterchangeControlVersion_ISA12] [nvarchar](5) NOT NULL,
[strInterchangeControlNumber_ISA13] [nvarchar](9) NOT NULL,
[chElementDelimiter] [nchar](1) NOT NULL,
[chComponentElementDelimiter] [nchar](1) NOT NULL,
[chSegmentTerminator] [nchar](1) NOT NULL,
[intTransmissionDate_GS04] [int] NOT NULL,
[strVersionRelease_GS08] [nvarchar](12) NOT NULL,
[strTransSetControl_ST02] [nvarchar](9) NOT NULL,
[strPONumber_BEG03] [nvarchar](22) NOT NULL,
[intPODate_BEG05] [int] NOT NULL,
[strWholesalerAccountNum_N1BY04] [nvarchar](80) NOT NULL,
[dtFileCreatedDateTime] [datetime] NOT NULL
)
Here is my stored procedure definition:
CREATE PROCEDURE [dbo].[ppisp_sp_850Files_Insert]
@850FileData [dbo].[udt_850File] READONLY
AS
BEGIN
DECLARE @ReturnVal INT = 0
MERGE INTO [dbo].[850Files] AS target
USING @850FileData AS source
ON source.[strInbound850FromPharmacyFilename] = target.[strInbound850FromPharmacyFilename]
AND source.[strSenderID_ISA06] = target.[strSenderID_ISA06]
AND source.[strReceiverID_ISA08] = target.[strReceiverID_ISA08]
AND source.[strInterchangeControlVersion_ISA12] = target.[strInterchangeControlVersion_ISA12]
AND source.[strInterchangeControlNumber_ISA13] = target.[strInterchangeControlNumber_ISA13]
AND source.[chElementDelimiter] = target.[chElementDelimiter]
AND source.[chComponentElementDelimiter] = target.[chComponentElementDelimiter]
AND source.[chSegmentTerminator] = target.[chSegmentTerminator]
AND source.[intTransmissionDate_GS04] = target.[intTransmissionDate_GS04]
AND source.[strVersionRelease_GS08] = target.[strVersionRelease_GS08]
AND source.[strTransSetControl_ST02] = target.[strTransSetControl_ST02]
AND source.[strPONumber_BEG03] = target.[strPONumber_BEG03]
AND source.[intPODate_BEG05] = target.[intPODate_BEG05]
AND source.[strWholesalerAccountNum_N1BY04] = target.[strWholesalerAccountNum_N1BY04]
AND source.[dtFileCreatedDateTime] = target.[dtFileCreatedDateTime]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([strInbound850FromPharmacyFilename]
, [strSenderID_ISA06]
, [strReceiverID_ISA08]
, [strInterchangeControlVersion_ISA12]
, [strInterchangeControlNumber_ISA13]
, [chElementDelimiter]
, [chComponentElementDelimiter]
, [chSegmentTerminator]
, [intTransmissionDate_GS04]
, [strVersionRelease_GS08]
, [strTransSetControl_ST02]
, [strPONumber_BEG03]
, [intPODate_BEG05]
, [strWholesalerAccountNum_N1BY04]
, [dtFileCreatedDateTime])
VALUES (source.[strInbound850FromPharmacyFilename]
, source.[strSenderID_ISA06]
, source.[strReceiverID_ISA08]
, source.[strInterchangeControlVersion_ISA12]
, source.[strInterchangeControlNumber_ISA13]
, source.[chElementDelimiter]
, source.[chComponentElementDelimiter]
, source.[chSegmentTerminator]
, source.[intTransmissionDate_GS04]
, source.[strVersionRelease_GS08]
, source.[strTransSetControl_ST02]
, source.[strPONumber_BEG03]
, source.[intPODate_BEG05]
, source.[strWholesalerAccountNum_N1BY04]
, source.[dtFileCreatedDateTime]);
SET @ReturnVal = @@ROWCOUNT
END
Here is my c# class file where I define my datatable and populate it with data:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace PP_ISP_File
{
public class PP_ISP_File_Data
{
public DataTable dt_Files_Info { get; set; }
public PP_ISP_File_Data(string strFileDirectoryPath, int intFileType)
{
dt_Files_Info = new DataTable();
dt_Files_Info.Columns.Add("strSenderID_ISA06", typeof(string));
dt_Files_Info.Columns["strSenderID_ISA06"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strReceiverID_ISA08", typeof(string));
dt_Files_Info.Columns["strReceiverID_ISA08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlVersion_ISA12", typeof(string));
dt_Files_Info.Columns["strInterchangeControlVersion_ISA12"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlNumber_ISA13", typeof(string));
dt_Files_Info.Columns["strInterchangeControlNumber_ISA13"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("chElementDelimiter", typeof(char));
dt_Files_Info.Columns["chElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chComponentElementDelimiter", typeof(char));
dt_Files_Info.Columns["chComponentElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chSegmentTerminator", typeof(char));
dt_Files_Info.Columns["chSegmentTerminator"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("intTransmissionDate_GS04", typeof(int));
dt_Files_Info.Columns["intTransmissionDate_GS04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strVersionRelease_GS08", typeof(string));
dt_Files_Info.Columns["strVersionRelease_GS08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strTransSetControl_ST02", typeof(string));
dt_Files_Info.Columns["strTransSetControl_ST02"].DefaultValue = default(string);
if(intFileType == 850)
{
dt_Files_Info.Columns.Add("strInbound850FromPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strInbound850FromPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BEG03", typeof(string));
dt_Files_Info.Columns["strPONumber_BEG03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BEG05", typeof(int));
dt_Files_Info.Columns["intPODate_BEG05"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1BY04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1BY04"].DefaultValue = default(string);
}
else if(intFileType == 855)
{
dt_Files_Info.Columns.Add("strOutbound855ToPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strOutbound855ToPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BAK03", typeof(string));
dt_Files_Info.Columns["strPONumber_BAK03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BAK04", typeof(int));
dt_Files_Info.Columns["intPODate_BAK04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1ST04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1ST04"].DefaultValue = default(string);
}
dt_Files_Info.Columns.Add("dtFileCreatedDateTime", typeof(DateTime));
dt_Files_Info.Columns["dtFileCreatedDateTime"].DefaultValue = default(DateTime);
dt_Files_Info = GetFileDetails(strFileDirectoryPath, intFileType);
}
private DataTable GetFileDetails(string strFileDirectoryPath, int intFileType)
{
DataTable dtPP_ISP_Files_Return = dt_Files_Info.Clone();
DirectoryInfo diDirectoryInfo = new DirectoryInfo(strFileDirectoryPath);
if (diDirectoryInfo.Exists)
{
foreach (FileInfo fiFileInfo in diDirectoryInfo.GetFiles())
{
if (fiFileInfo.Exists)
{
using (StreamReader srFile = new StreamReader(fiFileInfo.FullName))
{
string strFileData = System.IO.File.ReadAllText(fiFileInfo.FullName);
if (strFileData.Length >= 104)
{
DataRow dr = dtPP_ISP_Files_Return.NewRow();
char chElementDelimiter = default(char);
char chComponentElementDelimiter = default(char);
char chSegmentTerminator = default(char);
chElementDelimiter = Convert.ToChar(strFileData.Substring(3, 1));
dr["chElementDelimiter"] = chElementDelimiter;
chComponentElementDelimiter = Convert.ToChar(strFileData.Substring(104, 1));
dr["chComponentElementDelimiter"] = chComponentElementDelimiter;
chSegmentTerminator = Convert.ToChar(strFileData.Substring(105, 1));
dr["chSegmentTerminator"] = chSegmentTerminator;
dr["dtFileCreatedDateTime"] = File.GetCreationTime(strFileDirectoryPath);
string arrFileLines = strFileData.Split(Convert.ToChar(chSegmentTerminator));
for (int i = 0; i <= arrFileLines.Length - 1; i++)
{
string arrLineData = arrFileLines[i].Split(Convert.ToChar(chElementDelimiter));
string strLineQualifier = arrLineData[0].Trim();
switch (strLineQualifier)
{
case "ISA":
dr["strSenderID_ISA06"] = arrLineData[6].Trim();
dr["strReceiverID_ISA08"] = arrLineData[8].Trim();
dr["strInterchangeControlVersion_ISA12"] = arrLineData[12].Trim();
dr["strInterchangeControlNumber_ISA13"] = arrLineData[13].Trim();
break;
case "GS":
dr["intTransmissionDate_GS04"] = Convert.ToInt32(arrLineData[4].Trim());
dr["strVersionRelease_GS08"] = arrLineData[8].Trim();
break;
case "ST":
dr["strTransSetControl_ST02"] = arrLineData[2].Trim();
break;
case "BEG":
dr["strInbound850FromPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BEG03"] = arrLineData[3].Trim();
dr["intPODate_BEG05"] = Convert.ToInt32(arrLineData[5].Trim());
break;
case "BAK":
dr["strOutbound855ToPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BAK03"] = arrLineData[3].Trim();
dr["intPODate_BAK04"] = Convert.ToInt32(arrLineData[4].Trim());
break;
case "N1":
if(arrLineData[1].Trim() == "BY")
{
dr["strWholesalerAccountNum_N1BY04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
else if (arrLineData[1].Trim() == "ST")
{
dr["strWholesalerAccountNum_N1ST04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
break;
default:
break;
}
}
}
}
}
}
}
return dtPP_ISP_Files_Return;
}
}
}
and lastly here is my code where I initialize my datatable and try to pass it to the stored procedure in my database:
using (SqlCommand cmd = new SqlCommand(strSPName, con))
{
PP_ISP_File_Data dtFileInfo = new PP_ISP_File_Data(strWatchPath, intFileType);
cmd.CommandText = strSPName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter spFilenames = new SqlParameter("@850FileData", SqlDbType.Structured);
spFilenames.TypeName = "dbo.udt_850File";
spFilenames.Value = dtFileInfo;
cmd.Parameters.Add(spFilenames);
SqlParameter returnParameter = new SqlParameter("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
con.Open();
cmd.ExecuteNonQuery();
int result = Convert.ToInt16(returnParameter.Value);
}
Running my code gives me this error:
System.InvalidCastException: 'Failed to convert parameter value from a
PP_ISP_File_Data to a IEnumerable`1.'
I have a suspicion that because I'm declaring my datatable in a class file it's not getting something that it needs to be able to enumerate over it when it gets passed to the stored procedure but I don't know how to correct that. Can someone please point me in the right direction to fix this? Thanks in advance.
c# sql-server datatable parameter-passing
I'm defining a UDT in my MS SQL Server 2012 DB and using that in a stored procedure in the database. I'm trying to call my stored procedure from my c# program and I'm getting an error. Here's my code:
This is the definition of my UDT:
CREATE TYPE [dbo].[udt_850File] AS TABLE(
[strInbound850FromPharmacyFilename] [nvarchar](1000) NULL,
[strSenderID_ISA06] [nvarchar](15) NOT NULL,
[strReceiverID_ISA08] [nvarchar](15) NOT NULL,
[strInterchangeControlVersion_ISA12] [nvarchar](5) NOT NULL,
[strInterchangeControlNumber_ISA13] [nvarchar](9) NOT NULL,
[chElementDelimiter] [nchar](1) NOT NULL,
[chComponentElementDelimiter] [nchar](1) NOT NULL,
[chSegmentTerminator] [nchar](1) NOT NULL,
[intTransmissionDate_GS04] [int] NOT NULL,
[strVersionRelease_GS08] [nvarchar](12) NOT NULL,
[strTransSetControl_ST02] [nvarchar](9) NOT NULL,
[strPONumber_BEG03] [nvarchar](22) NOT NULL,
[intPODate_BEG05] [int] NOT NULL,
[strWholesalerAccountNum_N1BY04] [nvarchar](80) NOT NULL,
[dtFileCreatedDateTime] [datetime] NOT NULL
)
Here is my stored procedure definition:
CREATE PROCEDURE [dbo].[ppisp_sp_850Files_Insert]
@850FileData [dbo].[udt_850File] READONLY
AS
BEGIN
DECLARE @ReturnVal INT = 0
MERGE INTO [dbo].[850Files] AS target
USING @850FileData AS source
ON source.[strInbound850FromPharmacyFilename] = target.[strInbound850FromPharmacyFilename]
AND source.[strSenderID_ISA06] = target.[strSenderID_ISA06]
AND source.[strReceiverID_ISA08] = target.[strReceiverID_ISA08]
AND source.[strInterchangeControlVersion_ISA12] = target.[strInterchangeControlVersion_ISA12]
AND source.[strInterchangeControlNumber_ISA13] = target.[strInterchangeControlNumber_ISA13]
AND source.[chElementDelimiter] = target.[chElementDelimiter]
AND source.[chComponentElementDelimiter] = target.[chComponentElementDelimiter]
AND source.[chSegmentTerminator] = target.[chSegmentTerminator]
AND source.[intTransmissionDate_GS04] = target.[intTransmissionDate_GS04]
AND source.[strVersionRelease_GS08] = target.[strVersionRelease_GS08]
AND source.[strTransSetControl_ST02] = target.[strTransSetControl_ST02]
AND source.[strPONumber_BEG03] = target.[strPONumber_BEG03]
AND source.[intPODate_BEG05] = target.[intPODate_BEG05]
AND source.[strWholesalerAccountNum_N1BY04] = target.[strWholesalerAccountNum_N1BY04]
AND source.[dtFileCreatedDateTime] = target.[dtFileCreatedDateTime]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([strInbound850FromPharmacyFilename]
, [strSenderID_ISA06]
, [strReceiverID_ISA08]
, [strInterchangeControlVersion_ISA12]
, [strInterchangeControlNumber_ISA13]
, [chElementDelimiter]
, [chComponentElementDelimiter]
, [chSegmentTerminator]
, [intTransmissionDate_GS04]
, [strVersionRelease_GS08]
, [strTransSetControl_ST02]
, [strPONumber_BEG03]
, [intPODate_BEG05]
, [strWholesalerAccountNum_N1BY04]
, [dtFileCreatedDateTime])
VALUES (source.[strInbound850FromPharmacyFilename]
, source.[strSenderID_ISA06]
, source.[strReceiverID_ISA08]
, source.[strInterchangeControlVersion_ISA12]
, source.[strInterchangeControlNumber_ISA13]
, source.[chElementDelimiter]
, source.[chComponentElementDelimiter]
, source.[chSegmentTerminator]
, source.[intTransmissionDate_GS04]
, source.[strVersionRelease_GS08]
, source.[strTransSetControl_ST02]
, source.[strPONumber_BEG03]
, source.[intPODate_BEG05]
, source.[strWholesalerAccountNum_N1BY04]
, source.[dtFileCreatedDateTime]);
SET @ReturnVal = @@ROWCOUNT
END
Here is my c# class file where I define my datatable and populate it with data:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace PP_ISP_File
{
public class PP_ISP_File_Data
{
public DataTable dt_Files_Info { get; set; }
public PP_ISP_File_Data(string strFileDirectoryPath, int intFileType)
{
dt_Files_Info = new DataTable();
dt_Files_Info.Columns.Add("strSenderID_ISA06", typeof(string));
dt_Files_Info.Columns["strSenderID_ISA06"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strReceiverID_ISA08", typeof(string));
dt_Files_Info.Columns["strReceiverID_ISA08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlVersion_ISA12", typeof(string));
dt_Files_Info.Columns["strInterchangeControlVersion_ISA12"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strInterchangeControlNumber_ISA13", typeof(string));
dt_Files_Info.Columns["strInterchangeControlNumber_ISA13"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("chElementDelimiter", typeof(char));
dt_Files_Info.Columns["chElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chComponentElementDelimiter", typeof(char));
dt_Files_Info.Columns["chComponentElementDelimiter"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("chSegmentTerminator", typeof(char));
dt_Files_Info.Columns["chSegmentTerminator"].DefaultValue = default(char);
dt_Files_Info.Columns.Add("intTransmissionDate_GS04", typeof(int));
dt_Files_Info.Columns["intTransmissionDate_GS04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strVersionRelease_GS08", typeof(string));
dt_Files_Info.Columns["strVersionRelease_GS08"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strTransSetControl_ST02", typeof(string));
dt_Files_Info.Columns["strTransSetControl_ST02"].DefaultValue = default(string);
if(intFileType == 850)
{
dt_Files_Info.Columns.Add("strInbound850FromPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strInbound850FromPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BEG03", typeof(string));
dt_Files_Info.Columns["strPONumber_BEG03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BEG05", typeof(int));
dt_Files_Info.Columns["intPODate_BEG05"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1BY04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1BY04"].DefaultValue = default(string);
}
else if(intFileType == 855)
{
dt_Files_Info.Columns.Add("strOutbound855ToPharmacyFilename", typeof(string));
dt_Files_Info.Columns["strOutbound855ToPharmacyFilename"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("strPONumber_BAK03", typeof(string));
dt_Files_Info.Columns["strPONumber_BAK03"].DefaultValue = default(string);
dt_Files_Info.Columns.Add("intPODate_BAK04", typeof(int));
dt_Files_Info.Columns["intPODate_BAK04"].DefaultValue = -1;
dt_Files_Info.Columns.Add("strWholesalerAccountNum_N1ST04", typeof(string));
dt_Files_Info.Columns["strWholesalerAccountNum_N1ST04"].DefaultValue = default(string);
}
dt_Files_Info.Columns.Add("dtFileCreatedDateTime", typeof(DateTime));
dt_Files_Info.Columns["dtFileCreatedDateTime"].DefaultValue = default(DateTime);
dt_Files_Info = GetFileDetails(strFileDirectoryPath, intFileType);
}
private DataTable GetFileDetails(string strFileDirectoryPath, int intFileType)
{
DataTable dtPP_ISP_Files_Return = dt_Files_Info.Clone();
DirectoryInfo diDirectoryInfo = new DirectoryInfo(strFileDirectoryPath);
if (diDirectoryInfo.Exists)
{
foreach (FileInfo fiFileInfo in diDirectoryInfo.GetFiles())
{
if (fiFileInfo.Exists)
{
using (StreamReader srFile = new StreamReader(fiFileInfo.FullName))
{
string strFileData = System.IO.File.ReadAllText(fiFileInfo.FullName);
if (strFileData.Length >= 104)
{
DataRow dr = dtPP_ISP_Files_Return.NewRow();
char chElementDelimiter = default(char);
char chComponentElementDelimiter = default(char);
char chSegmentTerminator = default(char);
chElementDelimiter = Convert.ToChar(strFileData.Substring(3, 1));
dr["chElementDelimiter"] = chElementDelimiter;
chComponentElementDelimiter = Convert.ToChar(strFileData.Substring(104, 1));
dr["chComponentElementDelimiter"] = chComponentElementDelimiter;
chSegmentTerminator = Convert.ToChar(strFileData.Substring(105, 1));
dr["chSegmentTerminator"] = chSegmentTerminator;
dr["dtFileCreatedDateTime"] = File.GetCreationTime(strFileDirectoryPath);
string arrFileLines = strFileData.Split(Convert.ToChar(chSegmentTerminator));
for (int i = 0; i <= arrFileLines.Length - 1; i++)
{
string arrLineData = arrFileLines[i].Split(Convert.ToChar(chElementDelimiter));
string strLineQualifier = arrLineData[0].Trim();
switch (strLineQualifier)
{
case "ISA":
dr["strSenderID_ISA06"] = arrLineData[6].Trim();
dr["strReceiverID_ISA08"] = arrLineData[8].Trim();
dr["strInterchangeControlVersion_ISA12"] = arrLineData[12].Trim();
dr["strInterchangeControlNumber_ISA13"] = arrLineData[13].Trim();
break;
case "GS":
dr["intTransmissionDate_GS04"] = Convert.ToInt32(arrLineData[4].Trim());
dr["strVersionRelease_GS08"] = arrLineData[8].Trim();
break;
case "ST":
dr["strTransSetControl_ST02"] = arrLineData[2].Trim();
break;
case "BEG":
dr["strInbound850FromPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BEG03"] = arrLineData[3].Trim();
dr["intPODate_BEG05"] = Convert.ToInt32(arrLineData[5].Trim());
break;
case "BAK":
dr["strOutbound855ToPharmacyFilename"] = fiFileInfo.Name;
dr["strPONumber_BAK03"] = arrLineData[3].Trim();
dr["intPODate_BAK04"] = Convert.ToInt32(arrLineData[4].Trim());
break;
case "N1":
if(arrLineData[1].Trim() == "BY")
{
dr["strWholesalerAccountNum_N1BY04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
else if (arrLineData[1].Trim() == "ST")
{
dr["strWholesalerAccountNum_N1ST04"] = arrLineData[4].Trim();
dtPP_ISP_Files_Return.Rows.Add(dr);
}
break;
default:
break;
}
}
}
}
}
}
}
return dtPP_ISP_Files_Return;
}
}
}
and lastly here is my code where I initialize my datatable and try to pass it to the stored procedure in my database:
using (SqlCommand cmd = new SqlCommand(strSPName, con))
{
PP_ISP_File_Data dtFileInfo = new PP_ISP_File_Data(strWatchPath, intFileType);
cmd.CommandText = strSPName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter spFilenames = new SqlParameter("@850FileData", SqlDbType.Structured);
spFilenames.TypeName = "dbo.udt_850File";
spFilenames.Value = dtFileInfo;
cmd.Parameters.Add(spFilenames);
SqlParameter returnParameter = new SqlParameter("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
con.Open();
cmd.ExecuteNonQuery();
int result = Convert.ToInt16(returnParameter.Value);
}
Running my code gives me this error:
System.InvalidCastException: 'Failed to convert parameter value from a
PP_ISP_File_Data to a IEnumerable`1.'
I have a suspicion that because I'm declaring my datatable in a class file it's not getting something that it needs to be able to enumerate over it when it gets passed to the stored procedure but I don't know how to correct that. Can someone please point me in the right direction to fix this? Thanks in advance.
c# sql-server datatable parameter-passing
c# sql-server datatable parameter-passing
edited Nov 21 '18 at 4:28
Gharbad The Weak
asked Nov 21 '18 at 4:24
Gharbad The WeakGharbad The Weak
143110
143110
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
How aboutspFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object intoSqlDbType.Structured
, you need aDataTable
or collections which convertible withAsEnumerable()
.
– Tetsuya Yamamoto
Nov 21 '18 at 4:39
add a comment |
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
How aboutspFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object intoSqlDbType.Structured
, you need aDataTable
or collections which convertible withAsEnumerable()
.
– Tetsuya Yamamoto
Nov 21 '18 at 4:39
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
How about
spFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object into SqlDbType.Structured
, you need a DataTable
or collections which convertible with AsEnumerable()
.– Tetsuya Yamamoto
Nov 21 '18 at 4:39
How about
spFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object into SqlDbType.Structured
, you need a DataTable
or collections which convertible with AsEnumerable()
.– Tetsuya Yamamoto
Nov 21 '18 at 4:39
add a comment |
1 Answer
1
active
oldest
votes
You actually need to pass datatable as parameter to the stored procedure for it to be used as UDT but right now you are passing whole object of your class which will not work.
So change the following line:
spFilenames.Value = dtFileInfo;
to:
spFilenames.Value = dtFileInfo.dt_Files_Info;
From MSDN:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable SqlDataRecord objects.
Also refer to Passing a Table-Valued Parameter to a Stored Procedure
You can take a look at the following posts for more:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx
Hope it helps.
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405238%2fc-sharp-system-invalidcastexception-when-passing-datatable-to-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You actually need to pass datatable as parameter to the stored procedure for it to be used as UDT but right now you are passing whole object of your class which will not work.
So change the following line:
spFilenames.Value = dtFileInfo;
to:
spFilenames.Value = dtFileInfo.dt_Files_Info;
From MSDN:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable SqlDataRecord objects.
Also refer to Passing a Table-Valued Parameter to a Stored Procedure
You can take a look at the following posts for more:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx
Hope it helps.
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
add a comment |
You actually need to pass datatable as parameter to the stored procedure for it to be used as UDT but right now you are passing whole object of your class which will not work.
So change the following line:
spFilenames.Value = dtFileInfo;
to:
spFilenames.Value = dtFileInfo.dt_Files_Info;
From MSDN:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable SqlDataRecord objects.
Also refer to Passing a Table-Valued Parameter to a Stored Procedure
You can take a look at the following posts for more:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx
Hope it helps.
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
add a comment |
You actually need to pass datatable as parameter to the stored procedure for it to be used as UDT but right now you are passing whole object of your class which will not work.
So change the following line:
spFilenames.Value = dtFileInfo;
to:
spFilenames.Value = dtFileInfo.dt_Files_Info;
From MSDN:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable SqlDataRecord objects.
Also refer to Passing a Table-Valued Parameter to a Stored Procedure
You can take a look at the following posts for more:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx
Hope it helps.
You actually need to pass datatable as parameter to the stored procedure for it to be used as UDT but right now you are passing whole object of your class which will not work.
So change the following line:
spFilenames.Value = dtFileInfo;
to:
spFilenames.Value = dtFileInfo.dt_Files_Info;
From MSDN:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable SqlDataRecord objects.
Also refer to Passing a Table-Valued Parameter to a Stored Procedure
You can take a look at the following posts for more:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters
https://visualstudiomagazine.com/articles/2015/06/01/table-valued-parameters.aspx
Hope it helps.
edited Nov 21 '18 at 4:44
answered Nov 21 '18 at 4:39
Ehsan SajjadEhsan Sajjad
51.1k1168125
51.1k1168125
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
add a comment |
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
works like a charm, I knew I was missing something simple. Accepted as answer and tried to up vote but my reputation is too low for it to show. Thanks again!
– Gharbad The Weak
Nov 21 '18 at 4:50
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
glad that it helped.
– Ehsan Sajjad
Nov 21 '18 at 5:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405238%2fc-sharp-system-invalidcastexception-when-passing-datatable-to-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
which line is showing this error?
– sujith karivelil
Nov 21 '18 at 4:28
@suijth karivelil the line that reads cmd.ExecuteNonQuery();
– Gharbad The Weak
Nov 21 '18 at 4:29
How about
spFilenames.Value = dtFileInfo.dt_Files_Info
? Certainly you cannot pass class object intoSqlDbType.Structured
, you need aDataTable
or collections which convertible withAsEnumerable()
.– Tetsuya Yamamoto
Nov 21 '18 at 4:39