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>
