Load Text file to Database : Text File Load Save « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. Text File Load Save

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