Create a text file based on following

[nwcomer]

Hi,

I'm new to c#/win forms and Please help me to solve my problem.

Say I have a DataBase Table (Peron) with three fields in it.
Name nvarchar(20), Age int, RollNo int

Table would be something like this

Name Age RollNo

Andy 10 1

Scott 30 2

Smith 25 3

I fire a query (select * from Person) to this table to get back all the rows.

Now when I get the result back I want to create a text file which would hold information like that. As we see there are three records in the table and I would like to create those three records in the text file(if there are hundred records in the table then I would like to create hundred lines in the text file) like this..



// Text file should look like this

Insert into Person(Name, Age, RollNo) Values('Andy',10,1)
Insert into Person(Name, Age, RollNo) Values('Scott',30,2)
Insert into Person(Name, Age, RollNo) Values('Smith',25,3)

// end of text file

Idea is basically to create Insert statement for all the Records .

Please advise..

 

[ninjadeathmonkey]

Like this:

[blue]string[/blue] fileLocation = @"c:\mytext.txt";
SqlConnection conn = [blue]new[/blue] SqlConnection(connString);
conn.Open();
SqlCommand cmd = [blue]new[/blue] SqlCommand("SELECT * FROM Person", conn);
SqlDataReader reader = cmd.ExecuteReader();
TextWriter writer = File.CreateText(fileLocation);
[blue]while[/blue](reader.Read())
{
    writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader["Name"] + "', " + 
        reader["Age"] + ", " + reader["RollNo"] + ");");
}

reader.Close();
conn.Close();
writer.Close();

Ron Wheeler
Tekdev Open Source Development

 

[nwcomer]

Thanks a lot. I really really appreciate that.

I have one more question please.
Instead of one query I have to fire two queries now and add all records from table1 and then from table2.

Table person:

Name Age RollNo

Andy 10 1

Scott 30 2

Smith 25 3

Table Emp:

EmpName EmpAge EmpRollNo

Andy 10 1

Scott 30 2

Smith 25 3

Records in the file would go like this
// Text file should look like this
// All the records from Person Table

Insert into Person(Name, Age, RollNo) Values('Andy',10,1)
Insert into Person(Name, Age, RollNo) Values('Scott',30,2)
Insert into Person(Name, Age, RollNo) Values('Smith',25,3)

// All the records from Emp Table

Insert into Person(EmpName, EmpAge, EmpRollNo) Values('Andy',10,1)
Insert into Person(EmpName, EmpAge, EmpRollNo) Values('Scott',30,2)
Insert into Person(EmpName, EmpAge, EmpRollNo) Values('Smith',25,3)


// end of text file

I don't know how to fire two queries and add there records in the text file from first Table1 and then from Table2.

 

[ninjadeathmonkey]

Essentially, it's the same, just do another query and while loop.

string fileLocation = @"c:\mytext.txt";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Person", conn);
SqlDataReader reader = cmd.ExecuteReader();
TextWriter writer = File.CreateText(fileLocation);
while(reader.Read())
{
    writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader["Name"] + "', " +
        reader["Age"] + ", " + reader["RollNo"] + ");");
}

cmd = new SqlCommand("SELECT * FROM EmpPerson", conn);
reader = cmd.ExecuteReader();
while(reader.Read())
{
    writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader["EmpName"] + "', " +
        reader["EmpAge"] + ", " + reader["EmpRollNo"] + ");");
}

reader.Close();
conn.Close();
writer.Close();

I'm a little confused on your second set of queries though, is it inserting into the same table, or into a separate Employees table? (My example inserts into the same table)

Ron Wheeler
Tekdev Open Source Development

 

[nwcomer]

You are such a star person. Thanks a lot mate. Much appreciated.
My second set of queries, it inserting into a separate Employees table , my mistake.I think I need to change the text Emp instead of Person in second insert.

I really thanks a lot.
Regards,

 

[nwcomer]

correct me if I'm wrong..I've not tested it yet but I'll do it tomorrow & let you know.

Thanks,