Geek Answers Handbook

What to use for the Size argument in Database.AddOutParameter when returning a row?

I get the string as an output parameter and should know what to set for the Size argument in the AddOutParameter call.

I know that I can just use a huge amount, like int.MaxValue, but I want to know the best practices.

In SQL Server, a column is actually a uniqueidentifier type. The executed T-SQL statement inserts the record, and then sets some output variables to the identifier and GUID of the newly inserted records. This is the actual code that I am using, but with the changed names.

database.AddOutParameter(cmd, "@someInt", DbType.Int32, 0); database.AddOutParameter(cmd, "@someString", DbType.String, 0); database.ExecuteNonQuery(cmd); someInt = (int)database.GetParameterValue(cmd, "@someInt"); someString = database.GetParameterValue(cmd, "@someString").ToString();

When executed, I get the following error ...

System.InvalidOperationException: String [2]: The Size property has an invalid size of 0.

So, for me it is obvious that you cannot just use size 0 with the line output option. You can do this with the output parameter of Int32, but I assume that the string needs a valid size. So what is the best practice for sizing? Could this be a huge size without affecting performance at all? Can I just set it to int.MaxValue or something else? Is there a constant that can be used here; (didn't see any String.MaxValue - you can probably say I'm new to C # with a Java background).

Should I find out what is the maximum size of a uniqueidentifier column and set its size? What if I do the same for a VARCHAR or NVARCHAR column?

I would like the structure to just do it for me; I do not want to specify a size for each line that I get as output. Anyone have suggestions for best practice?

I read the posts below, as well as the MSDN documentation, but there really is no better answer to this question that I have found yet.

AddOutParameter - non-magic number for finding the length of DBType.Int32

Read VARBINARY (MAX) from SQL Server in C #

+8
c # database sql-server enterprise-library
source share
1 answer

As we found out, you used the wrong type for UniqueIdentifer. You should use DbType.Guid instead of the string, but you raised other questions in the comments that I couldn't answer in the comments, and I wasn't sure about that, so I needed to test.

They are

  • What should you set the size for various string output options?
  • Does it matter if it is Nvarchar or varchar?
  • What happens if you make it too big or too small?

I started by using SqlCommandBuilder.DeriveParameters to find out what ADO.NET and SQL Server think it should be, and then performed a stored procedure to see what our return values ​​are.

 Sql Type | DbType | Size | Returned string.Length() ---------------------------------------------------------------- Varchar(10) | AnsiString | 10 | 9 Char(10) | AnsiStringFixedLength | 10 | 10 Nvarchar(10 | String | 10 | 9 Varchar(max) | AnsiString | -1 | 20,480 NVarchar(max)| String | -1 | 20,480

As expected, the derived sizes correspond to the length field for all character types except the maximum and return values, where the expected length is. However, looking at maximum types and DbTypes, we had some new issues that could be discussed with our first three.

  • What is the type of AnsiString, and if we set it to DbType.String, it will affect the result if we keep the same size? Answer: No, this is not so, probably because the .NET strings are unicode

  • Does the value of Paramater.Size any value without the value of max? Answer: Yes, but only char (10). It increases the size of the output by adding empty spaces.

  • Does Paramater.Size any value without max value? Yes, it truncates return values

  • Is the size of the magic -1? Answer: Yes, if you set the size to -1, it will return values, as if you set them correctly

SQL Server 2008 .NET 4.0 Test Code

SQL code

 CREATE PROCEDURE SomeOutput( @tenVC varchar(10) output, @tenC char(10) output, @tenNVC nvarchar(10) output, @maxVC varchar(max) output, @maxNVC nvarchar(max) output, @Indentifier uniqueidentifier output) AS SELECT @tenC = '123456789', @tenVC = '123456789', @tenNVC = '123456789', @Indentifier = NEWID(), @maxVC = '', @maxNVC = '' SELECT @maxVC = @maxVC + '1234567890', @maxNVC = @maxNVC + '1234567890' FROM master..spt_values WHERE type= 'P'

C # code

 static void Main(string[] args) { using (SqlConnection cnn = new SqlConnection("Server=.;Database=Test;Trusted_Connection=True;")) { SqlCommand cmd = new SqlCommand("SomeOutput", cnn); cmd.CommandType = CommandType.StoredProcedure; cnn.Open(); SqlCommandBuilder.DeriveParameters(cmd); Printparams(cmd.Parameters, "Derived"); foreach (SqlParameter param in cmd.Parameters) { //By default output parameters are InputOutput //This will cause problems if the value is both null if (param.Direction == ParameterDirection.InputOutput ) param.Direction = ParameterDirection.Output; } cmd.ExecuteNonQuery(); Printparams(cmd.Parameters ,"Executed"); cmd.Parameters["@tenVC"].DbType = DbType.String; cmd.Parameters["@tenNVC"].DbType = DbType.AnsiString; cmd.ExecuteNonQuery(); Printparams(cmd.Parameters, "DbType change"); foreach (SqlParameter param in cmd.Parameters) { if (param.DbType != DbType.Int32 && param.DbType != DbType.Guid && param.Size != -1) { param.Size = param.Size * 2; } } cmd.ExecuteNonQuery(); Printparams(cmd.Parameters, "Mangeled sizes up"); foreach (SqlParameter param in cmd.Parameters) { if (param.DbType != DbType.Int32 && param.DbType != DbType.Guid && param.Size != -1) { param.Size = param.Size / 4; } } cmd.ExecuteNonQuery(); Printparams(cmd.Parameters, "Mangeled sizes down"); cmd.Parameters["@maxVC"].Size = Int32.MaxValue; cmd.Parameters["@maxNVC"].Size = Int32.MaxValue; cmd.ExecuteNonQuery(); Printparams(cmd.Parameters, "Fixed max sizes"); foreach (SqlParameter param in cmd.Parameters) { if (param.DbType != DbType.Int32 && param.DbType != DbType.Guid) { param.Size = -1; } } cmd.ExecuteNonQuery(); Printparams(cmd.Parameters, "is negative one magic"); } }

results

 Derived @RETURN_VALUE : Int32 : 0 : ReturnValue : 0 : @tenVC : AnsiString : 10 : InputOutput : 0 : @tenC : AnsiStringFixedLength : 10 : InputOutput : 0 : @tenNVC : String : 10 : InputOutput : 0 : @maxVC : AnsiString : -1 : InputOutput : 0 : @maxNVC : String : -1 : InputOutput : 0 : @Indentifier : Guid : 0 : InputOutput : 0 : Executed @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : AnsiString : 10 : Output : 9 : 123456789 @tenC : AnsiStringFixedLength : 10 : Output : 10 : 123456789 @tenNVC : String : 10 : Output : 9 : 123456789 @maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : eccc3632-4d38-44e8-9edf-031 DbType change @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : String : 10 : Output : 9 : 123456789 @tenC : AnsiStringFixedLength : 10 : Output : 10 : 123456789 @tenNVC : AnsiString : 10 : Output : 9 : 123456789 @maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : 94cb0039-8587-4357-88fb-25c Mangeled sizes up @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : String : 20 : Output : 9 : 123456789 @tenC : AnsiStringFixedLength : 20 : Output : 20 : 123456789 @tenNVC : AnsiString : 20 : Output : 9 : 123456789 @maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : 4de88f14-9963-4a78-b09b-bb6 Mangeled sizes down @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : String : 5 : Output : 5 : 12345 @tenC : AnsiStringFixedLength : 5 : Output : 5 : 12345 @tenNVC : AnsiString : 5 : Output : 5 : 12345 @maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : 5e973e72-14e5-4b75-9cff-e88 Fixed max sizes @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : String : 5 : Output : 5 : 12345 @tenC : AnsiStringFixedLength : 5 : Output : 5 : 12345 @tenNVC : AnsiString : 5 : Output : 5 : 12345 @maxVC : AnsiString : 2147483647 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : 2147483647 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : 6cab2b41-d4ba-42d2-a93a-e59 is negative one magic @RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0 @tenVC : String : -1 : Output : 9 : 123456789 @tenC : AnsiString : -1 : Output : 10 : 123456789 @tenNVC : AnsiString : -1 : Output : 9 : 123456789 @maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567 @maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567 @Indentifier : Guid : 0 : Output : 36 : 0d69ed57-fab7-49c8-b03a-d75
+16
source share

More articles:


All Articles