Die gespeicherte Prozedur schlägt bei der Transportfehlermeldung fehl ⇐ C#

[Anonymous]

Ich könnte Hilfe verwenden, um herauszufinden, warum mein Code fehlschlägt. Ich versuche, eine gespeicherte Prozedur mit 130 Parametern auszuführen. Die gespeicherte Prozedur funktioniert, wenn ich zwei der Flag -Bit -Parameter kommentiere, aber fehlschlägt, wenn ich sie mit allen Parametern ausführe. Es spielt keine Rolle, welche 2 Parameter der 130, die ich ausschließe, es funktioniert nur. Gibt es eine Grenze für die Anzahl der Parameter, die ich an die gespeicherte Prozedur übergeben kann? Alle Parameterwerte werden ordnungsgemäß aus einem Klassen -Repository eingestellt. Gibt es eine Größengrenze, eine bestimmte Anzahl von Bytes, die als Parameter an eine gespeicherte Prozedur übergeben werden können? Wenn ja, ist es möglich, die Grenzwerte zu erhöhen? Ich stelle eine abgekürzte Version des folgenden Codes an: < /p>

Code: Select all

 public static DataTable GetTestActionInfo()
{
try
{
String datasource = string.Empty;
string initialcatalog = string.Empty;
switch (TestITMiddlewareCS.PropertiesCS.Database.ToString().ToLower())
{
case ("prod"):
{
datasource = ConstantsCS.c_DataSourceProd;
initialcatalog = ConstantsCS.c_InitialCatalogProd;
break;
}
case ("test"):
{
datasource = ConstantsCS.c_DataSourceTest;
initialcatalog = ConstantsCS.c_InitialCatalogTest;
break;
}
case ("dev"):
{
datasource = ConstantsCS.c_DataSourceDev;
initialcatalog = ConstantsCS.c_InitialCatalogDev;
break;
}
}
using (SqlConnection conn = new SqlConnection(new SqlConnectionStringBuilder()
{
DataSource = datasource,
InitialCatalog = initialcatalog,
//IntegratedSecurity = true     // for domain user comment out userid and userpassword
UserID = ConstantsCS.c_UserID,  //SQL user required for the client application.  The web service ends up using it too
Password = ConstantsCS.c_UserPassword
}.ConnectionString))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
using (da.SelectCommand = new SqlCommand("GetTestActionInfo", conn))
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.SelectCommand.Parameters.Add("@TestActionInfoID", SqlDbType.Int).Value = TestActionInfoCS.TestActionInfoRepository[0].TestActionInfoID;
da.SelectCommand.Parameters.Add("@TestInfoID", SqlDbType.Int).Value = TestActionInfoCS.TestActionInfoRepository[0].TestInfoID;
da.SelectCommand.Parameters.Add("@TestActionDescription", SqlDbType.VarChar, 150).Value = TestActionInfoCS.TestActionInfoRepository[0].TestActionDescription;
da.SelectCommand.Parameters.Add("@TestActionSequenceNumber", SqlDbType.Int).Value = TestActionInfoCS.TestActionInfoRepository[0].TestActionSequenceNumber;
da.SelectCommand.Parameters.Add("@IsScanInValueRequiredFlag", SqlDbType.Bit).Value = TestActionInfoCS.TestActionInfoRepository[0].IsScanInValueRequiredFlag;
da.SelectCommand.Parameters.Add("@UseScanInValueFlag", SqlDbType.Bit).Value = TestActionInfoCS.TestActionInfoRepository[0].UseScanInValueFlag;
da.SelectCommand.Parameters.Add("@ScanInValue", SqlDbType.NVarChar, 150).Value = TestActionInfoCS.TestActionInfoRepository[0].ScanInValue;
...
da.SelectCommand.Parameters.Add("@IsActiveFlag", SqlDbType.Bit).Value = TestActionInfoCS.TestActionInfoRepository[0].IsActiveFlag;

da.SelectCommand.Parameters.Add("@SelectTestActionInfoIDFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectTestActionInfoIDFlag;
da.SelectCommand.Parameters.Add("@SelectTestInfoIDFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectTestInfoIDFlag;
da.SelectCommand.Parameters.Add("@SelectTestActionDescriptionFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectTestActionDescriptionFlag;
da.SelectCommand.Parameters.Add("@SelectTestActionSequenceNumberFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectTestActionSequenceNumberFlag;
da.SelectCommand.Parameters.Add("@SelectIsScanInValueRequiredFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectIsScanInValueRequiredFlag;
da.SelectCommand.Parameters.Add("@SelectUseScanInValueFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectUseScanInValueFlag;
da.SelectCommand.Parameters.Add("@SelectScanInValueFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectScanInValueFlag;
...
da.SelectCommand.Parameters.Add("@SelectIsActiveFlag", SqlDbType.Bit).Value = TestActionInfoSelectParametersCS.TestActionInfoSelectParameters[0].SelectIsActiveFlag;

DataSet ds = new DataSet();
da.Fill(ds, "TestActionInfo");

return ds.Tables["TestActionInfo"];
}
}
};
}
catch (Exception ex)
{
MessageGLCS.LoadMessageGL("Error Message: Method GetTestActionInfo failed.");
MessageGLCS.LoadMessageGL(ex.Message);
return null;
}
}
< /code>
Hier ist die gespeicherte Prozedur:  < /p>
ALTER PROCEDURE [dbo].[GetTestActionInfo]
(

/*1   */ @TestActionInfoID Int = null,
/*2   */ @TestInfoID int = null,
/*3   */ @TestActionDescription VarChar(150) = null,
/*4   */ @TestActionSequenceNumber Int = null,
/*5   */ @IsScanInValueRequiredFlag Bit = false,
/*6   */ @UseScanInValueFlag Bit = false,
/*7   */ @ScanInValue NVarChar(150) = null,
/*8   */ @LeadingCharacterFlag Bit = false,
/*9   */ @LeadingCharacters nvarchar(10) = null,
/*10  */ @UnitInfoID int = null,
/*11  */ @DataTypeInfoID int = null,
/*12  */ @ParseScannedInValueFlag bit = false,
/*13  */ @ParseByStartPositionFlag bit = false,
/*14  */ @ParseStartPosition int = null,
/*15  */ @ParseLength int = null,
/*16  */ @ParseBySplitCharacterFlag bit = false,
/*17  */ @ParseSplitCharacter int = null,
/*18  */ @ParseCellSplitType int = null,
/*19  */ @ParseSplitUseCellNumber int = null,
/*20  */ @AlterScannedInValueFlag bit = false,
/*21  */ @AlterScannedInValueType NVarChar(50) = null,
/*22  */ @AlterScannedInValue_X int = null,
/*23  */ @AlterScannedInValue_Y int = null,
/*24  */ @AlterScannedInValue_Z int = null,
/*25  */ @DisplayAlterScannedInValueFlag bit = false,
/*26  */ @DisplayTestActionSequenceNumber int = null,
/*27  */ @DisplayUnitsFlag bit = false,
/*28  */ @ConvertScannedInValueFlag bit = false,
/*29  */ @ConversionTypeInfoID int = null,
/*30  */ @BuildOutputStringFromScanInValueFLag bit = false,
/*31  */ @JoinScanInValuesByTestActionSequenceNumbers varchar(50) = null,
/*32  */ @JoinScanInValueCharacterSeperator varchar(50) = null,
/*33  */ @UseScanInValueForPartLookupFlag bit = false,
/*34  */ @PartNumberLookupTestActionSequenceNumber int = null,
/*35  */ @MatchPartNumberDescriptionAttributeToGetValueFlag bit = false,
/*36  */ @UsePartNumberDescriptionAttributeParameterSequenceNumberFlag bit = false,
/*37  */ @PartNumberDescriptionAttributeParameterSequenceNumber int = null,
/*38  */ @AttributeRequiredFlag Bit = false,
/*39  */ @AttributeInfoID int = null,
/*40  */ @AttributeTypeInfoID int = null,
/*41  */ @UseMESInputParameterFlag bit = false,
/*42  */ @IsTesterScanRequiredFlag Bit = false,
/*43  */ @IsAuditorScanRequiredFlag Bit = false,
/*44  */ @IsStartTriggerFlag Bit = false,
/*45  */ @IsTestFlag Bit = false,
/*46  */ @IsBusinessRuleRequiredFlag Bit = false,
/*47  */ @CompoundConditionAllTrueFlag Bit = false,
/*48  */ @AutoStartGenerateSerialNumberFlag bit = false,
/*49  */ @StandaloneGenerateSerialNumberFlag bit = false,
/*50  */ @SerialNumberFlag bit = false,
/*51  */ @SerialNumberType varchar(50) = null,
/*52  */ @CheckIfSerialNumberExistFlag bit = false,
/*53  */ @IsSerialNumberKeyFlag bit = false,
/*54  */ @IfSerialNumberExistRetestFlag bit = false,
/*55  */ @IfSerialNumberExistRestartFlag bit = false,
/*56  */ @ProgramInfoID int = null,
/*57  */ @TesterProgramID int = null,
/*58  */ @TestCount int = null,
/*59  */ @GenerateActionOnTestCountFlag Bit = false,
/*60  */ @LabelFlag bit = false,
/*61  */ @LabelInfoID int = null,
/*62  */ @FlashFlag Bit = false,
/*63  */ @FlashInfoID int = null,
/*64  */ @IsTestCompleteTriggerFlag Bit = false,
/*65  */ @IsActiveFlag Bit = false,
/*66  */ @SelectTestActionInfoIDFlag Bit = false,
/*67  */ @SelectTestInfoIDFlag Bit = false,
/*68  */ @SelectTestActionDescriptionFlag Bit = false,
/*69  */ @SelectTestActionSequenceNumberFlag Bit = false,
/*70  */ @SelectIsScanInValueRequiredFlag Bit = false,
/*71  */ @SelectUseScanInValueFlag bit = false,
/*72  */ @SelectScanInValueFlag Bit = false,
/*73  */ @SelectLeadingCharacterFlag bit = false,
/*74  */ @SelectLeadingCharactersFlag bit = false,
/*75  */ @SelectUnitInfoIDFlag bit = false,
/*76  */ @SelectDataTypeInfoIDFlag bit = false,
/*77  */ @SelectParseScannedInValueFlag bit = false,
/*78  */ @SelectParseByStartPositionFlag bit = false,
/*79  */ @SelectParseStartPositionFlag bit = false,
/*80  */ @SelectParseLengthFlag bit = false,
/*81  */ @SelectParseBySplitCharacterFlag bit = false,
/*82  */ @SelectParseSplitCharacterFlag bit = false,
/*83  */ @SelectParseCellSplitTypeFlag bit = false,
/*84  */ @SelectParseSplitUseCellNumberFlag bit = false,
/*85  */ @SelectAlterScannedInValueFlag bit = false,
/*86  */ @SelectAlterScannedInValueTypeFlag bit = false,
/*87  */ @SelectAlterScannedInValue_XFlag bit = false,
/*88  */ @SelectAlterScannedInValue_YFlag bit = false,
/*89  */ @SelectAlterScannedInValue_ZFlag bit = false,
/*90  */ @SelectDisplayAlterScannedInValueFlag bit = false,
/*91  */ @SelectDisplayTestActionSequenceNumberFlag bit = false,
/*92  */ @SelectDisplayUnitsFlag bit = false,
/*93  */ @SelectConvertScannedInValueFlag bit = false,
/*94  */ @SelectConversionTypeInfoIDFlag bit = false,
/*95  */ @SelectBuildOutputStringFromScanInValueFLag bit = false,
/*96  */ @SelectJoinScanInValuesByTestActionSequenceNumbersFLag bit = false,
/*97  */ @SelectJoinScanInValueCharacterSeperatorFLag bit = false,
/*98  */ @SelectUseScanInValueForPartLookupFlag bit = false,
/*99  */ @SelectPartNumberLookupTestActionSequenceNumberFlag bit = false,
/*100 */ @SelectMatchPartNumberDescriptionAttributeToGetValueFlag bit = false,
/*101 */ @SelectUsePartNumberDescriptionAttributeParameterSequenceNumberFlag bit = false,
/*102 */ @SelectPartNumberDescriptionAttributeParameterSequenceNumberFlag bit = false,
/*103 */ @SelectAttributeRequiredFlag Bit = false,
/*104 */ @SelectAttributeInfoIDFlag Bit = false,
/*105 */ @SelectAttributeTypeInfoIDFlag Bit = false,
/*106 */ @SelectUseMESInputParameterFlag bit = false,
/*107 */ @SelectIsTesterScanRequiredFlag Bit = false,
/*108 */ @SelectIsAuditorScanRequiredFlag Bit = false,
/*109 */ @SelectIsStartTriggerFlag Bit = false,
/*110 */ @SelectIsTestFlag Bit = false,
/*111 */ @SelectIsBusinessRuleRequiredFlag Bit = false,
/*112 */ @SelectCompoundConditionAllTrueFlag bit = false,
/*113 */ @SelectAutoStartGenerateSerialNumberFlag bit = false,
/*114 */ @SelectStandaloneGenerateSerialNumberFlag bit = false,
/*115 */ @SelectSerialNumberFlag bit = false,
/*116 */ @SelectSerialNumberTypeFlag bit = false,
/*117 */ @SelectCheckIfSerialNumberExistFlag bit = false,
/*118 */ @SelectIsSerialNumberKeyFlag bit = false,
/*119 */ @SelectIfSerialNumberExistRetestFlag bit = false,
/*120 */ @SelectIfSerialNumberExistRestartFlag bit = false,
/*121 */ @SelectProgramInfoIDFlag Bit = false,
/*122 */ @SelectTesterProgramIDFlag Bit = false,
/*123 */ @SelectTestCountFlag bit = false,
/*124 */ @SelectGenerateActionOnTestCountFlag Bit = false,
/*125 */ @SelectLabelFlag bit = false,
/*126 */ @SelectLabelInfoIDFlag bit = false,
/*127 */ @SelectFlashFlag bit = false,
/*128 */ @SelectFlashInfoIDFlag bit = false,
/*129 */ @SelectIsTestCompleteTriggerFlag Bit = false,
/*130 */ @SelectIsActiveFlag bit = false
)
AS
SET NOCOUNT ON;
BEGIN
--* GetTestActionInfo by TestInfoID and IsActiveFlag
if (@TestInfoID is not null
and @SelectTestActionInfoIDFlag = 0
and @SelectTestInfoIDFlag = 1
and @SelectTestActionDescriptionFlag  = 0
and @SelectTestActionSequenceNumberFlag = 0
and @SelectIsScanInValueRequiredFlag  = 0
and @SelectUseScanInValueFlag = 0
and @SelectScanInValueFlag = 0
and @SelectLeadingCharacterFlag = 0
and @SelectLeadingCharactersFlag = 0
and @SelectUnitInfoIDFlag = 0
and @SelectDataTypeInfoIDFlag = 0
and @SelectParseScannedInValueFlag = 0
and @SelectParseByStartPositionFlag = 0
and @SelectParseStartPositionFlag = 0
and @SelectParseLengthFlag = 0
and @SelectParseBySplitCharacterFlag = 0
and @SelectParseSplitCharacterFlag = 0
and @SelectParseCellSplitTypeFlag = 0
and @SelectParseSplitUseCellNumberFlag = 0
and @SelectAlterScannedInValueFlag = 0
and @SelectAlterScannedInValueTypeFlag = 0
and @SelectAlterScannedInValue_XFlag = 0
and @SelectAlterScannedInValue_YFlag = 0
and @SelectAlterScannedInValue_ZFlag = 0
and @SelectDisplayAlterScannedInValueFlag = 0
and @SelectDisplayTestActionSequenceNumberFlag = 0
and @SelectDisplayUnitsFlag = 0
and @SelectConvertScannedInValueFlag = 0
and @SelectConversionTypeInfoIDFlag = 0
and @SelectBuildOutputStringFromScanInValueFLag = 0
and @SelectJoinScanInValuesByTestActionSequenceNumbersFLag = 0
and @SelectJoinScanInValueCharacterSeperatorFLag = 0
and @SelectUseScanInValueForPartLookupFlag = 0
and @SelectPartNumberLookupTestActionSequenceNumberFlag = 0
and @SelectMatchPartNumberDescriptionAttributeToGetValueFlag = 0
and @SelectUsePartNumberDescriptionAttributeParameterSequenceNumberFlag = 0
and @SelectPartNumberDescriptionAttributeParameterSequenceNumberFlag = 0
and @SelectAttributeRequiredFlag  = 0
and @SelectAttributeInfoIDFlag  = 0
and @SelectAttributeTypeInfoIDFlag  = 0
and @SelectUseMESInputParameterFlag = 0
and @SelectIsTesterScanRequiredFlag = 0
and @SelectIsAuditorScanRequiredFlag = 0
and @SelectIsStartTriggerFlag = 0
and @SelectIsTestFlag = 0
and @SelectIsBusinessRuleRequiredFlag = 0
and @SelectCompoundConditionAllTrueFlag = 0
and @SelectAutoStartGenerateSerialNumberFlag = 0
and @SelectStandaloneGenerateSerialNumberFlag = 0
and @SelectSerialNumberFlag = 0
and @SelectSerialNumberTypeFlag = 0
and @SelectCheckIfSerialNumberExistFlag = 0
and @SelectIsSerialNumberKeyFlag = 0
and @SelectIfSerialNumberExistRetestFlag = 0
and @SelectIfSerialNumberExistRestartFlag = 0
and @SelectProgramInfoIDFlag  = 0
and @SelectTesterProgramIDFlag  = 0
and @SelectTestCountFlag = 0
and @SelectGenerateActionOnTestCountFlag = 0
and @SelectLabelFlag = 0
and @SelectLabelInfoIDFlag = 0
and @SelectFlashFlag = 0
and @SelectFlashInfoIDFlag = 0
and @SelectIsTestCompleteTriggerFlag = 0
and @SelectIsActiveFlag = 1)
BEGIN
SELECT *
FROM TestActionInfoView
WHERE TestInfoID = @TestInfoID
AND IsActiveFlag = @IsActiveFlag
ORDER BY TestActionSequenceNumber;

RETURN(0);
END

RETURN(99);
END