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




Re: Visual C# General Create a text file based on following

Figo Fei - MSFT

Hi,

Try this:

Code Snippet

using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Person", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
using (StreamWriter writer = File.CreateText(FilePath))
{
while (reader.Read())
{
writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader.GetString(0) + "', " +
reader.GetInt32(1) + ", " + reader.GetInt32(2) + ");");
//Or as follows
writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader["Name"] + "', " +
reader["Age"] + ", " + reader["RollNo"] + ");");
}
}
}
}

However, if you want to backup your data in the database, you'd rather use the database engine to do this work than write into a text file.

Thanks






Re: Visual C# General Create a text file based on following

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 first from Table1 and then from Table2.





Re: Visual C# General Create a text file based on following

Figo Fei - MSFT

Well,

Just add another SqlCommand based on the previous one like:

Code Snippet

using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Person", conn);
SqlCommand cmd2 = new SqlCommand("SELECT * FROM Emp", conn);
SqlDataReader reader = cmd.ExecuteReader();
using (StreamWriter writer = File.CreateText(FilePath))
{
while (reader.Read())
{
writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader.GetString(0) + "', " +
reader.GetInt32(1) + ", " + reader.GetInt32(2) + ");");
//Or as follows
writer.WriteLine("INSERT INTO Person (Name, Age, RollNo) VALUES('" + reader["Name"] + "', " +
reader["Age"] + ", " + reader["RollNo"] + ");");
}
reader.Close();
reader = cmd2.ExecuteReader();
while (reader.Read())
{
writer.WriteLine("INSERT INTO Person (EmpName, EmpAge, EmpRollNo) VALUES('" + reader.GetString(0) + "', " +
reader.GetInt32(1) + ", " + reader.GetInt32(2) + ");");
//Or as follows
writer.WriteLine("INSERT INTO Person (EmpName, EmpAge, EmpRollNo) VALUES('" + reader["EmpName"] + "', " +
reader["EmpAge"] + ", " + reader["EmpRollNo"] + ");");
}
reader.Close();
}
}

Thanks