DaniWeb Forum Index > Software Development > C#

C# RSS

import txt file sql database plz help!

Please support our C# advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Feb 2006

Posts: 399

Reputation: chris5126 is an unknown quantity at this point

Solved Threads: 14

chris5126

Offline

Posting Whiz

import txt file sql database plz help!

Feb 28th, 2009

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
            }
 
        }
    }
}
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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,
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!!

If my post helped add to my rep!

•

Join Date: Aug 2008

Posts: 1,735

Reputation: LizR has a spectacular aura about

Solved Threads: 186

LizR

Offline

Posting Virtuoso

Re: import txt file sql database plz help!

Feb 28th, 2009

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

Did I just hear "You gotta help us, Doc. We've tried nothin' and we're all out of ideas" ? Is this you? Dont let this be you! I will put in as much effort as you seem to.

•

Join Date: Feb 2006

Posts: 399

Reputation: chris5126 is an unknown quantity at this point

Solved Threads: 14

chris5126

Offline

Posting Whiz

Re: import txt file sql database plz help!

Feb 28th, 2009

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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)
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!

If my post helped add to my rep!

•

Join Date: Aug 2008

Posts: 1,735

Reputation: LizR has a spectacular aura about

Solved Threads: 186

LizR

Offline

Posting Virtuoso

Re: import txt file sql database plz help!

Mar 1st, 2009

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.

Did I just hear "You gotta help us, Doc. We've tried nothin' and we're all out of ideas" ? Is this you? Dont let this be you! I will put in as much effort as you seem to.

•

Join Date: Feb 2006

Posts: 399

Reputation: chris5126 is an unknown quantity at this point

Solved Threads: 14

chris5126

Offline

Posting Whiz

Re: import txt file sql database plz help!

Mar 1st, 2009

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

If my post helped add to my rep!

•

Join Date: Feb 2006

Posts: 399

Reputation: chris5126 is an unknown quantity at this point

Solved Threads: 14

chris5126

Offline

Posting Whiz

Re: import txt file sql database plz help!

Mar 1st, 2009

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
            }
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();
            }

Last edited by chris5126; Mar 1st, 2009 at 7:29 am.

If my post helped add to my rep!

•

Join Date: Aug 2008

Posts: 1,735

Reputation: LizR has a spectacular aura about

Solved Threads: 186

LizR

Offline

Posting Virtuoso

Re: import txt file sql database plz help!

Mar 1st, 2009

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.

Did I just hear "You gotta help us, Doc. We've tried nothin' and we're all out of ideas" ? Is this you? Dont let this be you! I will put in as much effort as you seem to.

•

Join Date: Feb 2006

Posts: 399

Reputation: chris5126 is an unknown quantity at this point

Solved Threads: 14

chris5126

Offline

Posting Whiz

Re: import txt file sql database plz help!

Mar 1st, 2009

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 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();
            }
 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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
            }
            }
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

If my post helped add to my rep!

•

Join Date: Aug 2008

Posts: 1,735

Reputation: LizR has a spectacular aura about

Solved Threads: 186

LizR

Offline

Posting Virtuoso

Re: import txt file sql database plz help!

Mar 1st, 2009

OK do you know what "Scope" is?

Did I just hear "You gotta help us, Doc. We've tried nothin' and we're all out of ideas" ? Is this you? Dont let this be you! I will put in as much effort as you seem to.