如何将大于 64 kb 缓冲区大小限制的文本文件加载到数据库中？
我正在尝试将文本文件（.aspx、.cs、html 等）加载到 sql server 2008 数据库中。到目前为止，我能够加载所有小于 64 kb 的文件。我有两个问题；如何绕过 64 kb 限制？我使用的方法是实现此目的的最佳方法吗？
感谢您的帮助。
数据库：
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();
}
}
}
请注意：这是我之前提出的问题的副本，当我清除 cookie 时，我失去了控制。 如何加载大于 64 kb 缓冲区大小限制的文本文件？
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. How do I load text files greater than the 64 kb buffersize limit?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
SQL Server 中没有 64kb 限制。对于行内数据类型（char、varchar、nchar、nvarchar、binary 和 varbinary），SQL 字符串的限制为 8000 字节；对于 LOB 类型（varchar(max)、nvarchar(max) 和 varbinary(max)，SQL 字符串的限制为 2 GB ）。您看到的 64 kb 限制一定来自其他原因，很可能来自 IIS 上传或 ASP 或 CLR 处理限制。
但是您将无法像这样处理任意长度的文件。 .Net 不会使用 Stream.ReadToEnd() 将大流加载到字符串中，因为内存分配不会成功。您将使用 LOB 特定的
UPDATE table SET column.WRITE...语法分块加载文件并将每个块附加到数据库中。PS 一些回复建议使用旧的 LOB 类型，例如
text、ntext和image。不要在 SQL Server 2008 中使用这些类型，它们已被弃用和劝阻。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,ntextandimage. Don't use those types with SQL Server 2008, they are deprecated and discouraged.使用 TEXT 列而不是 VARCHAR/CHAR 列。如果您需要比 TEXT 更大的内容，或者要加载到二进制文件中，请查看 BINARY/VARBINARY/IMAGE 等。
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.
MSDN 提供了有关所有可用数据类型的文档。对于文本文件，您可能需要使用 TEXT 类型，对于二进制文件，请使用二进制字符串类型之一。
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.
除了 phoebus 的响应之外，如果您的工作缓冲区太小，甚至小于 64k，您可以读取第一个段，用它更新文本字段，读取另一个缓冲区并用文本+新缓冲区更新文本，然后重复直到已加载所有数据。
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.
数据库不是用来存储大文件的。将文件存储在硬盘上，并将文件名存储到数据库中。
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.