Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL (varchar(max) vs CLR (string, SqlString, SqlChars)?

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.

like image 895
Chaki_Black Avatar asked Sep 28 '11 08:09

Chaki_Black


People also ask

What is VARCHAR Max in SQL?

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).

What is the difference between VARCHAR 50 and VARCHAR Max?

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.

What is the difference between VARCHAR and string?

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.


2 Answers

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 }
like image 147
Kolya Evdokimov Avatar answered Oct 16 '22 18:10

Kolya Evdokimov


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.

like image 37
Chaki_Black Avatar answered Oct 16 '22 17:10

Chaki_Black