ADO.NET 3.5 Cookbook, 2nd Edition

10.15. Reading and Writing Large-Value Data with SQL Server

Problem

You need to read and write large-value data type values from and to a SQL Server database.

Solution

The solution shows how to store, retrieve, and output nvarchar(max), varbinary(max), and varchar(max) lengths and values from both a DataTable and a DataReader.

The solution uses the table ReadWriteLargeData in the database AdoDotNet35Cookbook. Execute the following T-SQL to create this table:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE ReadWriteLargeData(
	    Id int NOT NULL PRIMARY KEY,
	    NVarCharMaxField nvarchar(max) NULL,
	    VarBinaryMaxField varbinary(max) NULL,
	    VarCharMaxField varchar(max) NULL )

The C# code in Program.cs in the project ReadWriteLargeData is shown in Example 10-25.

Example 10-25. File: Program.cs for ReadWriteLargeData solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Data.SqlTypes;

namespace ReadWriteLargeData
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlSelect = "SELECT Id, NVarCharMaxField, VarBinaryMaxField, " +
                "VarCharMaxField FROM ReadWriteLargeData";

            // fill data with schema and data SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); SqlCommandBuilder cb = new SqlCommandBuilder(da); DataTable dt = new DataTable(); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); // output schema info Console.WriteLine("---DataTable ...