Sunday, January 11, 2009

file open ,Load Text file to Database tutorial example

/*
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;
}
}

0 comments:

Followers

Get our toolbar!

 
Design by Wordpress Theme | Bloggerized by Free Blogger Templates | JCPenney Coupons