import txt file sql database plz help!

[chris5126]

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
            {
               //read from textfile
                FileStream readfile = new FileStream("c:\\testbench1\\info\\20090225174432", FileMode.Open);
                StreamReader streamreader = new StreamReader(readfile);

                string line = "";

                int lineNo = 0;
                do
                {
                    line = streamreader.ReadLine();
                    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");
                string ServerId = Console.ReadLine();
            }

            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);
                string customerId = Console.ReadLine();
            }
            try
            {
                //read from sql database
                Console.Write("please enter a serverID\n");
                string ServerId = Console.ReadLine();
                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 + "'";
                Console.WriteLine("about to execute: {0}\n\n", dataCommand.CommandText);
                SqlDataReader datareader = dataCommand.ExecuteReader();
                
                while (datareader.Read())
                {
                    //code to display current row
                    //int serverID = datareader.GetInt32(0);
                    String HostName = datareader.GetString(0);
                    String MachineType = datareader.GetString(1);
                    String Kernel = datareader.GetString(2);
                    byte CPUS = datareader.GetByte(3);
                    String Speed = datareader.GetString(4);
                    int Memory = datareader.GetInt32(5);
                    String Arch = datareader.GetString(6);
                    String SerialNO = datareader.GetString(7);
                    String PrimaryIP = datareader.GetString(9);
                    String DNS = datareader.GetString(10);
                    String NIS = datareader.GetString(11);
                    DateTime CTS = datareader.GetDateTime(12);
                    DateTime UTS = datareader.GetDateTime(13);


                    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);

                }
                datareader.Close();
                //add code
                string test = Console.ReadLine();
                //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);
                string customerId = Console.ReadLine();
            }
            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!!

[LizR]

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

[chris5126]

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!

[LizR]

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.

[chris5126]

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??

[chris5126]

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
            {
                StreamReader objReader = new StreamReader("c:\\testbench1\\info\\20090225174432");
                string sLine = "";
                ArrayList arrText = new ArrayList();

                while (sLine != null)
                {
                    sLine = objReader.ReadLine();
                    if (sLine != null)
                        arrText.Add(sLine);
                }
                objReader.Close();
                Console.WriteLine(arrText[0]);
                Console.ReadLine();
                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] + "')";
                Console.WriteLine("about to execute: {0}\n\n", dataCommand.CommandText);
                Console.ReadLine();
                SqlDataReader datareader = dataCommand.ExecuteReader();
                datareader.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in ShowFile: {0}", e);
                Console.Write("Pause\n");
                Console.ReadLine();
            }

[LizR]

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.

[chris5126]

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);
                string customerId = Console.ReadLine();
            }

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);
                string customerId = Console.ReadLine();
            }
            }

I get a load of errors have been looking for ages

[LizR]

OK do you know what "Scope" is?

[chris5126]

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

[chris5126]

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);
                string customerId = Console.ReadLine();
                return null;
            }
        }
         static void Main(string[] args)
        {
            System.Data.SqlClient.SqlConnection dataConnection = openConnection();

[LizR]

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.

If i had

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 :)

[chris5126]

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!!

[chris5126]

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);
                string customerId = Console.ReadLine();
                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";
                SqlDataReader datareader = dataCommand.ExecuteReader();
                while (datareader.Read())
                {
                    serverList.Add(datareader[0]);
                }
                datareader.Close();
                return serverList;
            }
            catch (Exception e)
            {
                Console.WriteLine("can't get list of servers: " + e.Message);
                string customerId = Console.ReadLine();
                return null;
            }
        }
        public static ArrayList readFile(string fname)
        {
            StreamReader objReader = new StreamReader(fname);
            string sLine = "";
            ArrayList arrText = new ArrayList();

            while (sLine != null)
            {
                sLine = objReader.ReadLine();
                if (sLine != null)
                    arrText.Add(sLine);
            }
            //for the file we have just found put the contents into an array
            objReader.Close();
            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);
                    Console.ReadLine();
                    //then for each file found check what info they contain then
                    //add the information to the database

                    ArrayList arrText = readFile(fname.FullName);
                    foreach (string sOutput in arrText)
                        Console.WriteLine(sOutput);
                    Console.ReadLine();
                    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] + "'";
                    Console.WriteLine("about to execute: {0}\n\n", dataCommand.CommandText);
                    Console.ReadLine();
                    SqlDataReader datareader = dataCommand.ExecuteReader();
                    datareader.Close();
                    File.Delete(fname.FullName);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in writing data to database: {0}", e);
                Console.Write("Pause\n");
                Console.ReadLine();
            }
         

            try
            {
                //read from sql database
                Console.Write("please enter a serverID\n");
                string ServerId = Console.ReadLine();
                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 + "'";
                Console.WriteLine("about to execute: {0}\n\n", dataCommand.CommandText);
                SqlDataReader datareader = dataCommand.ExecuteReader();

                while (datareader.Read())
                {
                    //code to display current row
                    //int serverID = datareader.GetInt32(0);
                    String HostName = datareader.GetString(0);
                    String MachineType = datareader.GetString(1);
                    String Kernel = datareader.GetString(2);
                    byte CPUS = datareader.GetByte(3);
                    String Speed = datareader.GetString(4);
                    int Memory = datareader.GetInt32(5);
                    String Arch = datareader.GetString(6);
                    String SerialNO = datareader.GetString(7);
                    String PrimaryIP = datareader.GetString(9);
                    String DNS = datareader.GetString(10);
                    String NIS = datareader.GetString(11);
                    DateTime CTS = datareader.GetDateTime(12);
                    DateTime UTS = datareader.GetDateTime(13);


                    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);

                }
                datareader.Close();
                //add code
                string test = Console.ReadLine();
                //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);
                string customerId = Console.ReadLine();
            }

            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!

[LizR]

Um, just like I showed :P