Mega Code Archive
Load Text file to Database
<![CDATA[
/*
Quote from
Beginning C# 2005 Databases From Novice to Professional
# Paperback: 528 pages
# Publisher: Apress (December 18, 2006)
# Language: English
# ISBN-10: 159059777X
# ISBN-13: 978-1590597774
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
class LoadText
{
static string fileName =@"loadtext.cs";
static SqlConnection conn = null;
static SqlCommand cmd = null;
static void Main()
{
try
{
GetTextFile(fileName);
conn = new SqlConnection(@"data source = .\sqlexpress;integrated security = true;initial catalog = tempdb;");
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"create table texttable(textfile varchar(255),textdata varchar(max))";
cmd.ExecuteNonQuery();
PrepareInsertTextFile();
ExecuteInsertTextFile(fileName);
Console.WriteLine("Loaded {0} into texttable.", fileName);
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
static void ExecuteCommand(string commandText)
{
cmd.CommandText = commandText;
cmd.ExecuteNonQuery();
}
static void PrepareInsertTextFile()
{
cmd.CommandText = @"insert into texttable values (@textfile, @textdata)";
cmd.Parameters.Add("@textfile", SqlDbType.NVarChar, 30);
cmd.Parameters.Add("@textdata", SqlDbType.Text, 1000000);
}
static void ExecuteInsertTextFile(string textFile)
{
string textData = GetTextFile(textFile);
cmd.Parameters["@textfile"].Value = textFile;
cmd.Parameters["@textdata"].Value = textData;
ExecuteCommand(cmd.CommandText);
}
static string GetTextFile(string textFile)
{
string textBytes = null;
Console.WriteLine("Loading File: " + 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;
}
}
]]>