Retrieving VARCHAR(MAX)

Question

How can I retrieve a column of datatype Varchar(MAX) from a SQL Server database in C#?

I don't think SqlDataReader helps retrieve it.

Any clue?

Thanks

I tried retrieving it using SqlDataReader reader = command.ExecuteReader(); But I read it that it needs to be retrieved using Streams/Pointers/Something and the traditional approach doesn't work — Jayesh, Jul 21 '11 at 07:14
@Joy - how *big* is the data; if it is enormous, there are some things you might need to do differently... — Marc Gravell, Jul 21 '11 at 07:21
off topic, In case its xml data you can check out [`ExecuteXmlReader`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executexmlreader.aspx) — V4Vendetta, Jul 21 '11 at 07:29
Check here. http://msdn.microsoft.com/en-us/library/bb399384.aspx In my experience with C# and stored procedures I have to specify the size as -1 for the parameter. Other than that, pretty much works like a regular nvarchar or varchar parameter. — Jon Raynor, Jul 22 '11 at 03:42

score 5 · Accepted Answer · answered Jul 21 '11 at 07:16

5

It's just a string field .... just grab it like any other string.....

// define your query
string query = "SELECT YourField FROM dbo.YourTable WHERE ID = 1";

using(SqlConnection conn = new SqlConnection("......"))
using(SqlCommand cmd = new SqlCommand(query, conn))
{
   conn.Open();

   using(SqlDataReader rdr = cmd.ExecuteReader())
   {
       if(rdr.Read())
       {
          string fieldValue = rdr.GetString(0);
       }
   }

   conn.Close();
}

There's really nothing special about the VARCHAR(MAX) type - it's just a text field, and it can hold up to 2 GB of text - just like a .NET string. You don't need to do anything special, like streaming or anything - just read the column into a string!

answered Jul 21 '11 at 07:16

marc_s

732,580
175
1,330
1,459

Although, if it turns out to be 500MB you might *choose* to read it via the streaming API – Marc Gravell Jul 21 '11 at 07:22
@Marc Gravell: yes, sure - but how many VARCHAR(MAX) columns do you really have that hold 500 MB of data? – marc_s Jul 21 '11 at 07:26
1

I think there's a practical limit on how big a .NET string can be that is lower than [2 GB](http://stackoverflow.com/questions/140468/what-is-the-maximum-possible-length-of-a-net-string). – Damien_The_Unbeliever Jul 21 '11 at 07:27
@marc_s Bro it works. Thank you. But I am just curious that will it work using SqlDataAdapter ? – Chintan Aug 03 '17 at 13:03
I would like to undo my downvote, but stackoverflow won't allow it. I initially thought that this approach will truncate (without warning) the retrieved string to a length of 2033 - but that applies when the query returns an `[xml]` column type - not `nvarchar(max)`. – Nathan Dec 11 '17 at 22:52

score 0 · Answer 2 · answered Jul 21 '11 at 07:18

Working with Large-Value Types in ADO.NET

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
    SqlChars buffer = reader.GetSqlChars(0);
}

Or just convert it to CLR type:

while (reader.Read())
{
     string str = reader[0].ToString();
     Console.WriteLine(str);
}

Retrieving VARCHAR(MAX)

2 Answers2