Hi guys,

Im trying to create a console app that will read from a text file and import the file into a sql database. I have completed the following already:

Read from a text file and put out to console
Read from sql database and put out to console
Now i need to read from the text file into the database and im stuck:

Ill give you the code I have got already:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;

namespace ConsoleApplication1
{
class reports
{
static void Main(string[] args)
{
try
{
FileStream readfile = new FileStream("c:\\testbench1\\info\\20090225174432", FileMode.Open);

string line = "";

int lineNo = 0;
do
{
if (line != null)
{
Console.WriteLine("{0}: {1}", lineNo, line);
lineNo++;
}
} while (line != null);

}
catch (Exception e)
{
Console.WriteLine("Exception in ShowFile: {0}", e);
Console.Write("Pause\n");
}

SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Data Source=chris-laptop\\project;Initial Catalog=sitehealth;Integrated Security=True";
dataConnection.Open();
Console.WriteLine("connected to database");
}

catch (Exception e)
{
Console.WriteLine("error opening the database: " + e.Message);
}
try
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT HostName, MachineType, Kernel, CPUS, CPUSPeed, MemoryMB, Architecture, SerialNO, PrimaryIP, PrimaryIP, DNS, NIS, CTS, UTS ";
dataCommand.CommandText += "FROM T_Servers WHERE ServerID='" +
ServerId + "'";

{
//code to display current row

Console.WriteLine("hostname= {0}\nMqcType= {1}\nKernel={2}\n" +
"CPU's={3}\nCPUSpeed={4}\nMemory={5}\nArch={6}\n" +
"Serial={7}\nPrimary IP={8}\nDNS={9}\nNIS={10}\nCTS={11}\nUTS={12}", HostName,
MachineType, Kernel, CPUS, Speed, Memory, Arch, SerialNO, PrimaryIP,
DNS, NIS, CTS, UTS);

}
//now we have read from the database lets try to write to
//the database
}
catch (Exception e)
{
Console.WriteLine("error reading from the database: " + e.Message);
}
finally
{
dataConnection.Close();
}

}
}
}

Also here is an example text file:

testbench2
i86pc
Generic_127128-11
2
2790
1024
i386
VMware-56_4d_b2_6e_3a_8b_c2_8a-02_0b_49_06_ed_a1_56_3c
192.168.1.100
DNS
noNIS

And here is the table it will be writing too:

T_Servers
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[HostName] [varchar](50) NOT NULL,
[MachineType] [varchar](50) NOT NULL,
[Kernel] [varchar](50) NOT NULL,
[CPUS] [tinyint] NOT NULL,
[CPUSPeed] [varchar](15) NOT NULL,
[MemoryMB] [int] NOT NULL,
[Architecture] [varchar](50) NOT NULL,
[SerialNo] [varchar](100) NOT NULL,
[PrimaryIP] [varchar](20) NOT NULL,
[DNS] [varchar](10) NOT NULL,
[NIS] [varchar](10) NOT NULL,
[CTS] [datetime] NOT NULL,
[UTS] [datetime] NOT NULL,

I know its a massive ask for help but i really need to be able to read the text file straight to the database, also CTS (current time stamp) and UTS (update time stamp) should be auto generated as will the server ID number, i have looked at various guides but they dont really make sense help!!

2
Contributors
14
Replies
15
Views
8 Years
Discussion Span
Last Post by LizR
Featured Replies
• 1

Well done. And whats better is is you almost certainly understand why you ended up with thec ode you did, rather than have someone hand you it.

Have you worked out the SQL you would need to run to put the data into your table?

kinda and kinda not!
This is what i do know!
I know i need to read the text file line by line and insert it into the database but obviously there declared as different types so don’t know whether i cld do it as one command or whether i wld need to read each line into an array or a variable, once the data was in memory i wld need to write it to the database i know the sql statement looks like: im assuming im reading the text file into variables named the same at the columns

INSERT INTO T_Servers(HostName, MachineType, Kernel, CPUS, CPUSPeed, MemoryMB, Architecture, SerialNo, PrimaryIP, DNS,
NIS)
VALUES (HostName, MachineType, Kernel, CPUS, CPUSPeed, MemoryMB, Architecture, SerialNo, PrimaryIP, DNS,
NIS)

The serverID column UTS and CTS should auto update, and I’m pretty sure that SQL command is right but this is the first time i have done any SQL ever!

So. Each line of your file has a field, hopefully you can work out how to read the file into variables, then now you have an template for how to do the SQL, you need to work out how to add those values. As the values as they are in your post wont take the values from the variables.

But you're getting there.

Yer the SQL stuff and reading the file isnt too bad its the method of actually writing to the database Im stuck on any help??

hello again,

Right i have now done what i needed to do, i can read from a text file into a sql database!! (code below). Have lots more things to do but for now this is it cheers!!

try
{
string sLine = "";
ArrayList arrText = new ArrayList();

while (sLine != null)
{
if (sLine != null)
}
Console.WriteLine(arrText[0]);
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "INSERT INTO T_Servers(HostName, MachineType, Kernel, CPUS, CPUSPeed, MemoryMB, Architecture, SerialNo, PrimaryIP, DNS,NIS)";
dataCommand.CommandText += "VALUES ('" + arrText[0] + "','" + arrText[1] + "','" + arrText[2] + "'," + arrText[3] + ",'" + arrText[4] + "',";
dataCommand.CommandText += "" + arrText[5] + ",'" + arrText[6] + "','" + arrText[7] + "','" + arrText[8] + "','" + arrText[9] + "','" + arrText[10] + "')";
}
catch (Exception e)
{
Console.WriteLine("Exception in ShowFile: {0}", e);
Console.Write("Pause\n");
}

Well done. And whats better is is you almost certainly understand why you ended up with thec ode you did, rather than have someone hand you it.

This is going very well here...

hi liz,

thank you for you usefull advice i need one more bit of help if thats ok:

My code is below but i need to put certain aspects into functions. e.g having the following in a function would be very usefull as it will be called multiple times:

SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Data Source=chris-laptop\\project;Initial Catalog=sitehealth;Integrated Security=True";
dataConnection.Open();
Console.WriteLine("connected to database");
}
//if it doesn't work show an error
catch (Exception e)
{
Console.WriteLine("error opening the database: " + e.Message);
}

First question where does this fuction go? is it at the start of main or in the class or namespace and what what i need to pass in and pass out. As when i tried to make it into a function:

static void openConnection ()
{
//open connection to database
SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Data Source=chris-laptop\\project;Initial Catalog=sitehealth;Integrated Security=True";
dataConnection.Open();
Console.WriteLine("connected to database");
}
//if it doesn't work show an error
catch (Exception e)
{
Console.WriteLine("error opening the database: " + e.Message);
}
}

I get a load of errors have been looking for ages

OK do you know what "Scope" is?

hmm have heard of it but dont really know too much about it!

hi liz,

this works but is it correct and the best way to do it:

public static System.Data.SqlClient.SqlConnection openConnection()
{
//open connection to database
SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Data Source=chris-laptop\\project;Initial Catalog=sitehealth;Integrated Security=True";
dataConnection.Open();
Console.WriteLine("connected to database");
return dataConnection;
}
//if it doesn't work show an error
catch (Exception e)
{
Console.WriteLine("error opening the database: " + e.Message);
return null;
}
}
static void Main(string[] args)
{
System.Data.SqlClient.SqlConnection dataConnection = openConnection();

OK Ive seen worse, although you should check that your openConnection doesnt return null in your Main.

Socpe.. Scope is a little like a line of sight.

private void  Something()
{
int i;
i=5;
}

private void other()
{
if (i==5)
{
Console.WriteLine("I is 5");
}
}

It would fail because i is only visible to the first function.

Most likely you would only open a connection to your database once. You wouldnt repeat it, so while having a separate method is a good idea, for this is has no major reason.

However, your inserting of data section would have a point :)

oh how do i create a method any useful tips or tricks, i really should have learnt c# before deciding to do a project in it!!

i know this is really cheeky but can you look at the code and let me know if im doing it right: I have started putting stuff into functions that i will need to use more than once. I would like to put the database connection into a method so I only have to call it once and im trying to read up on it now.

using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;

namespace ConsoleApplication1
{

class reports
{
public static System.Data.SqlClient.SqlConnection openConnection()
{
//open connection to database
SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Data Source=chris-laptop\\project;Initial Catalog=sitehealth;Integrated Security=True";
dataConnection.Open();
Console.WriteLine("connected to database");
return dataConnection;
}
//if it doesn't work show an error
catch (Exception e)
{
Console.WriteLine("error opening the database: " + e.Message);
return null;
}
}
public static ArrayList getServerList()
{
try
{
//get a list of the servers to be monitored and put them
//into an array and return the array
ArrayList serverList = new ArrayList();
SqlCommand dataCommand = new SqlCommand();
System.Data.SqlClient.SqlConnection dataConnection = openConnection();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT HostName FROM T_Servers";
{
}
return serverList;
}
catch (Exception e)
{
Console.WriteLine("can't get list of servers: " + e.Message);
return null;
}
}
{
string sLine = "";
ArrayList arrText = new ArrayList();

while (sLine != null)
{
if (sLine != null)
}
//for the file we have just found put the contents into an array
return arrText;
}
static void Main(string[] args)
{
System.Data.SqlClient.SqlConnection dataConnection = openConnection();
ArrayList serverList = getServerList();
//for each server check for new files in the info directory
try
{
DirectoryInfo dirinfo = new DirectoryInfo("C:\\testbench1\\info\\");
foreach (FileInfo fname in dirinfo.GetFiles("*"))
{
//if there are new files display them on the screen
Console.WriteLine("fname=: " + fname);
//then for each file found check what info they contain then
//add the information to the database

foreach (string sOutput in arrText)
Console.WriteLine(sOutput);
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "UPDATE T_Servers SET MachineType='" + arrText[1] + " ', Kernel='" + arrText[2] + "', ";
dataCommand.CommandText += "CPUS=" + arrText[3] + ", CPUSPeed='" + arrText[4] + "', MemoryMB=" + arrText[5] + ", ";
dataCommand.CommandText += "Architecture='" + arrText[6] + "', SerialNo='" + arrText[7] + "', PrimaryIP='" + arrText[8] + "', ";
dataCommand.CommandText += "DNS='" + arrText[9] + "', NIS='" + arrText[4] + "' ";
dataCommand.CommandText += "WHERE HostName ='" + arrText[0] + "'";
File.Delete(fname.FullName);
}
}
catch (Exception e)
{
Console.WriteLine("Exception in writing data to database: {0}", e);
Console.Write("Pause\n");
}

try
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT HostName, MachineType, Kernel, CPUS, CPUSPeed, MemoryMB, Architecture, SerialNO, PrimaryIP, PrimaryIP, DNS, NIS, CTS, UTS ";
dataCommand.CommandText += "FROM T_Servers WHERE ServerID='" +
ServerId + "'";

{
//code to display current row

Console.WriteLine("hostname= {0}\nMqcType= {1}\nKernel={2}\n" +
"CPU's={3}\nCPUSpeed={4}\nMemory={5}\nArch={6}\n" +
"Serial={7}\nPrimary IP={8}\nDNS={9}\nNIS={10}\nCTS={11}\nUTS={12}", HostName,
MachineType, Kernel, CPUS, Speed, Memory, Arch, SerialNO, PrimaryIP,
DNS, NIS, CTS, UTS);

}
//now we have read from the database lets try to write to
//the database
}
catch (Exception e)
{
Console.WriteLine("error reading from the database: " + e.Message);
}

finally
{
dataConnection.Close();
}

}
}
}

by the way the program does work and does what i want it to do but i will need to expand it and would like it set up correclty i do have a book and am reading through guides but they seem to all say different things so i think im doing it right but im not sure!

Um, just like I showed :P