Csharp/CSharp Tutorial/ADO.Net/Text File Load Save
Load Text file to Database
<source lang="csharp">/* 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.rumandText = @"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.rumandText = commandText; cmd.ExecuteNonQuery(); } static void PrepareInsertTextFile() { cmd.rumandText = @"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.rumandText); } 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; }
}</source>
Save text from database to text file
<source lang="csharp">/* 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; class RetrieveText {
static string textFile = null; static char[] textChars = null; static SqlConnection conn = null; static SqlCommand cmd = null; static SqlDataReader dr = null; public RetrieveText() { conn = new SqlConnection(@"data source = .\sqlexpress;integrated security = true;initial catalog = tempdb;"); // Create command cmd = new SqlCommand(@"select textfile,textdata from texttable", conn); // Open connection conn.Open(); // Create data reader dr = cmd.ExecuteReader(); } public static bool GetRow() { long textSize; int bufferSize = 100; long charsRead; textChars = new Char[bufferSize]; if (dr.Read()) { // Get file name textFile = dr.GetString(0); Console.WriteLine("------ start of file:"); Console.WriteLine(textFile); textSize = dr.GetChars(1, 0, null, 0, 0); Console.WriteLine("--- size of text: {0} characters -----", textSize); Console.WriteLine("--- first 100 characters in text -----"); charsRead = dr.GetChars(1, 0, textChars, 0, 100); Console.WriteLine(new String(textChars)); Console.WriteLine("--- last 100 characters in text -----"); charsRead = dr.GetChars(1, textSize - 100, textChars, 0, 100); Console.WriteLine(new String(textChars)); return true; } else { return false; } } public static void endRetrieval() { // Close the reader and the connection. dr.Close(); conn.Close(); } static void Main() { try { while (GetRow() == true) { Console.WriteLine("----- end of file:"); Console.WriteLine(textFile); } } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { endRetrieval(); } }
}</source>