I have a problem, that I can't solve. I'm using SQL Server 2005, C# CLR for using outer dll. The problem is at length of parameter. I need to use as function parameter type varchar(max)
. If at C# code I use string
, SqlSring
, I can't use T-SQL type varchar(max)
, just varchar(4000)
of nvarchar(4000)
. I need to say, that can be situations, when I need to use more then 4000 symbols, so I need know, what C# type I need to use for varchar(max)
.
I have read a lot of articles, and several of them say, that for this I can use SqlChars
. But! I have manipulations with strings. How can I have actions with string
or SqlString
and then convert to SqlChars
? (It is possible SqlChars.ToString()
or SqlChars.ToSqlString()
).
I didn't find any C# code for this.
varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
VARCHAR(MAX) is different from VARCHAR because it supports character strings up to 2 GB (2,147,483,647 bytes) in length. You should consider using VARCHAR(MAX) only when each string stored in this data type varies considerably in length, and a value might exceed 8000 bytes in size.
SQL varchar stores variable string length whereas SQL char stores fixed string length. This means SQL Server varchar holds only the characters we assign to it and char holds the maximum column space regardless of the string it holds.
What you need to do is add SqlFacet(MaxSize = -1) attribute to parameter:
[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString YourSqlFunction([SqlFacet(MaxSize = -1)]SqlString sourceSS)
{ return new }
I found the answer. Not just only me asked about this question. I have to read posts more attentively...
Can read here http://social.msdn.microsoft.com/Forums/is/sqlnetfx/thread/391ebb98-e1b5-47fc-a72d-9f1e39829e3a The problem of CLR compatibility solves not very difficult.
All time I wanted use varchar(max)
and tried to use C# types string
, SqlString
, SqlChars
. Need to use T-SQL nvarchar(max)
, and you can use any of C# types string
, SqlString
, SqlChars
!
Of course nvarchar
taking up more space than varchar
at two times.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With