I m developing a CLR application in c#. I encounter SqlChars type and SqlString. but i don't find the difference between them. When use SqlChars and when use SqlString?
thanks
0
Add a comment
|
3 Answers
I apologize for duplicating some of the data in the currently selected answer, but I wanted to try to provide a little clarification.
First, the short answer:
My recommendations for when to use SqlChars vs SqlString have to do with your intent. If you do not plan to modify the input string, use the SqlString. If you need to manipulate the input, use SqlChars.
And now some details about how I got there:
We can see from the documentation that SqlChars is a class and SqlString is a structure. Class vs structure is beyond the scope of this response, but suffice it to say structures are lighter weight. You can also see that SqlChars is mutable (you can change it without necessarily requiring a new memory allocation). I cannot find documentation referring to a size limitation to SqlString and I have not had any problems using data larger than VARCHAR(8000)/NVARCHAR(4000) with a SqlString. If you are having issues, please make sure your CLR code has MaxByteSize = -1.
The information below comes from the MSDN documentation of the SQL Types.
Classes
SqlChars is a mutable reference type that wraps a Char array or a SqlString instance.
Structures
SqlString - Represents a variable-length stream of characters to be stored in or retrieved from the database. SqlString has a different underlying data structure from its corresponding .NET Framework String data type.
5 Comments
Solomon Rutzky
thanks for adding this clarification. Just to add to this clarification: SqlString has the same 2 GB limit as SqlChars. There is no inherent mapping between NVARCHAR(1 - 4000) -> SqlString or NVARCHAR(MAX) -> SqlChars. It is all up to how the SQL wrapper object is defined. The default mapping s (what I just noted) and the ability to use SqlFacet MaxSize = -1 to get NVARCHAR(MAX) for SqlString is purely a construct of Visual Studio generating the SQL wrapper object. But you can create it manually or ALTER what Visual Studio creates to even do SqlChars for NVARCHAR(1 - 4000).
Solomon Rutzky
Also, regarding "intent", there is also this statement from the MSDN page for handling LOBs (technet.microsoft.com/en-us/library/ms131065.aspx) "These types allow streaming the LOB values from the database to the common language runtime (CLR) routine, instead of copying the entire value into managed space". So, assuming that I have 10k of text but want to do something like RegEx that requires a string, is it better to still use SqlChars only to immediately do ToString (since I then have a copy of it in managed space) or just use SqlString and remove a step?
Bennett Dill
I wasn't aware of the ability to stream and haven't used it. The advantage of streaming would be if you're moving/transforming data from one spot to another and don't need all of the contents at once. Since you need all the data to do a RegEx, I don't think streaming would help. If you were moving data from SQL to a file (from the MS sample), it would help reduce memory pressure in cases where you have a 1GB file, because you wouldn't need to have all 1GB in memory at once. I'd say for a RegEx, SqlString would be fine, but it wouldn't hurt to test both approaches.
Bennett Dill
Here's a link to the LOB example, it looks like a lot of work, but if you had large objects that were simply being pushed around, it would probably help reduce memory pressure. Otherwise, I'd probably avoid it. The comment in there cracks me up "Is getting chunks the best way to retrieve LOB from the database". msftengprodsamples.codeplex.com/SourceControl/…
Martin Smith
+1 for correcting the false info on SQLString and max length.-- SQL String is a sqltype in .net framework.It represents a variable-length stream of characters to be stored in or retrieved from the database.
-- SQLChar is a is a mutable reference type that wraps a Char array or a SqlString instance. SqlChar is a class. And what u found is right that sqlstring is limited to nvarchar(4000) and sqlchars is nvarchar(max). (But we can define the size, precision and scale of parameters and return types by using SqlFacet attribute.)
Comments
allow me to cite from mr. alastair: sqlchar is less demanding on system memory resources, because it doesn't require a block of contiguos nature, as sqlstring does.
1 Comment
Bennett Dill
Are you able to cite a reference for this? I would expect SqlString to be non-mutable, like most strings. While SqlChar is more like a StringBuilder, the data can be changed without a reallocation unless the reserved size has been overrun. This is different from the non-mutable string where any change requires a new allocation.