How do I load text files into database that are greater than the 64 kb buffersize limit?

Question

I'm trying to load text files (.aspx, .cs, html, etc) into a sql server 2008 database. I'm able to load all files that are less than 64 kb so far. I have two questions; How do I get around the 64 kb limit, and is the method I'm using the best way to do this?

Thanks for the help.

Database:

    file_length int,
    file_path varchar(250),
    file_string varchar(MAX)


private static void Load_Files()
{
    string source = HttpContext.Current.Server.MapPath("~/website/");

    DirectoryInfo di = new DirectoryInfo(source);
    FileInfo[] files = di.GetFiles();

    foreach (FileInfo f in files)
    {
        string sourceFile = f.FullName;

        FileStream fs_reader = new FileStream(sourceFile, FileMode.Open, FileAccess.Read);
        StreamReader reader = new StreamReader(fs_reader);
        string content = reader.ReadToEnd();

        Int32 file_length = content.Length;

        string CS = ConfigurationManager.ConnectionStrings["MCP_CS"].ConnectionString;
        SqlConnection SQL_Conn_01 = new SqlConnection(CS);

        string SQL_01 = "INSERT INTO Page_File_Store (file_length, file_path, file_string) VALUES (@file_length, @file_path, @file_string)";
        SqlCommand SQL_File_Load = new SqlCommand(SQL_01, SQL_Conn_01);
        SQL_File_Load.Parameters.Add(new SqlParameter("@file_length", file_length));
        SQL_File_Load.Parameters.Add(new SqlParameter("@file_path", sourceFile));

        //SQL_File_Load.Parameters.Add(new SqlParameter("@file_string", content));

        SqlParameter contentParameter = new SqlParameter("@file_string", SqlDbType.VarChar, -1);
        contentParameter.Value = content;
        SQL_File_Load.Parameters.Add(contentParameter);

        SQL_Conn_01.Open();
        SQL_File_Load.ExecuteNonQuery();
        SQL_Conn_01.Close();

        reader.Close();
    }
}

}

Please Note: this is a copy of a question I asked earlier and lost control of when I cleared my cookies. http://stackoverflow.com/questions/1474107

Answer 1

Use a TEXT column instead of a VARCHAR/CHAR column. If you need something even bigger than TEXT, or will be loading in binary files, look into BINARY/VARBINARY/IMAGE etc.

Answer 2

MSDN provides documentation on all of the available data types. For text files you'll probably want to use the TEXT type, for binary files use one of the Binary String types.

Answer 3

In addition to phoebus's response, if your working buffer is too small, or even smaller than the 64k, you can read in the first segment, update the text field with that, read another buffer and update text with text + new buffer and repeat until all data loaded.

Answer 4

• Databases are not made to store big files in it. Store the files on the harddisk instead and store the filenames into the database.

• If you still want to store them into the database anyway you can use a compression library like #ziplib to decrease file sizes (source code compresses very well) and use binary column types like phoebus proposes.

Answer 5

There is no 64kb limit in SQL Server. The limits for SQL strings are either at 8000 bytes for in-row data types (char, varchar, nchar, nvarchar, binary and varbinary) or 2 GB for LOB types (varchar(max), nvarchar(max) and varbinary(max)). The 64 kb limitation you see must come from something else, most likely from IIS upload or ASP or CLR processing limitations.

But you're not going to be able to process arbitrary length files like this. .Net will not load a large stream into a string using Stream.ReadToEnd() because the memory allocation won't succeed. You are going to load the file in chunks and append each chunk into the database, using the LOB specific UPDATE table SET column.WRITE... syntax.

P.S. Some responses recommend use of the old LOB types like text, ntext and image. Don't use those types with SQL Server 2008, they are deprecated and discouraged.