How do you load a 150 kb (kilobyte) text files into sql server 2008?

Question

How do you load 150 kb 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["SQL_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();
    }
}

Answer 1

Normally I would use the BULK INSERT T/SQL command or the BCP command line utility to import the data. This allow you to load all the data in a single transaction (or a large batch of records) instead of row by row as row by row is going to be very slow.

You can do this in C# via the bulk insert object that is part of the SQL objects (I don't have any code samples handy, but hopefully someone can post one for you).