C# 从文件中加载文本数据
// C# 从文件中加载文本数据
// LoadText.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
namespace Ch18
{
class LoadText
{
static string strFile = @".\LoadText.cs";
SqlConnection conn = null;
SqlCommand cmd = null;
static void Main()
{
LoadText loader = new LoadText();
try
{
loader.OpenConnection();
loader.CreateCommand();
loader.CreateTextTable();
loader.PrepareInsertTextFile();
loader.ExecuteInsertTextFile(strFile);
Console.WriteLine( "将文件{0}保存到数据库" , strFile);
}
catch ( Exception ex)
{
Console.WriteLine( "出错了:" + ex.ToString());
}
finally
{
loader.CloseConnection();
Console.ReadLine();
}
}
void OpenConnection()
{
string strConn = "server=.\\MSSQL2012;integrated security = true; database=tempdb";
conn = new SqlConnection(strConn);
conn.Open();
}
void CloseConnection()
{
conn.Close();
Console.WriteLine( "连接关闭" );
}
void CreateCommand()
{
cmd = new SqlCommand();
cmd.Connection = conn;
}
void ExecuteCommand( string cmdText)
{
int cmdResult;
cmd.CommandText = cmdText;
Console.WriteLine( "执行SQL:");
Console.WriteLine(cmd.CommandText);
cmdResult = cmd.ExecuteNonQuery();
Console.WriteLine( "ExecuteNonQuery 返回 {0}" , cmdResult);
}
void CreateTextTable()
{
// 如果texttable表存在，则删除表
ExecuteCommand( @"if exists (select * from information_schema.tables where table_name='texttable')
drop table texttable" );
// 再建表
ExecuteCommand( @"create table texttable (textfile varchar(255), textdate varchar(max))");
}
void PrepareInsertTextFile()
{
cmd.CommandText = @"insert into texttable values(@file,@data)";
cmd.Parameters.Add( "@file", SqlDbType.NVarChar, 30);
cmd.Parameters.Add( "@data", SqlDbType.Text, 1000000);
cmd.Prepare();
}
void ExecuteInsertTextFile( string strFile)
{
string textData = GetTextFile(strFile);
cmd.Parameters[ "@file"].Value = strFile;
cmd.Parameters[ "@data"].Value = textData;
ExecuteCommand(cmd.CommandText);
}
string GetTextFile( string textFile)
{
string textBytes = null;
Console.WriteLine( "加载文件:" + textFile);
FileStream fs = new FileStream(textFile, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader (fs);
textBytes = sr.ReadToEnd();
Console.WriteLine( "TextBytes has length {0} bytes." , textBytes.Length);
return textBytes;
}
}
}
---------------------
执行SQL:
if exists (select * from information_schema.tables where table_name='texttable')
drop table texttable
ExecuteNonQuery 返回 -1
执行SQL:
create table texttable (textfile varchar(255), textdate varchar(max))
ExecuteNonQuery 返回 -1
加载文件:.\LoadText.cs
TextBytes has length 3286 bytes.
执行SQL:
insert into texttable values(@file,@data)
ExecuteNonQuery 返回 1
将文件.\LoadText.cs保存到数据库
连接关闭
