Creating table script with values in codebehind inC#

I want to build a console application with ado.net where I get a table from the database and convert it to a string output in a file. I want to do this with stringbuilder. But before I fetch the data and write it to the string builder I want to fill 2 fields of the table Person “name” and “last name” with “xxx”. End result must be a file with create and insert values statement in tsql.

class Program
    {
        static void Main(string[] args)
        {
            string ConString = @"Data Source=.;Initial Catalog=TEST;Integrated Security=True";

           

            SqlConnection con = new SqlConnection(ConString);
            string querystring = "Select * from person";

           

            con.Open();
            SqlCommand cmd = new SqlCommand(querystring, con);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString() + reader[3].ToString());
            }

            StringBuilder builder = new StringBuilder();
            builder.Append("");
            //CREATE TABLE Person
            //[name] [varchar](20) NOT NULL,
            //[surname] [varchar](20) NOT NULL,        
            //[Gender] [varchar](5) NOT NULL


            //INSERT VALUES (...,...) with modifyed name and surname data and put it in the stringbuilder


            string innerString = builder.ToString();

            using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"script.txt"))
            {
                file.WriteLine(innerString.ToString());
            }

         
               
            Console.Read();
        }

       
    }

Is there a smart way to achieve this? Thanks

Answer

I believe something like this is what you’re after:

static void Main(string[] args)
    {
        string ConString = @"Data Source=.;Initial Catalog=Clients;Integrated Security=True";

        SqlConnection con = new SqlConnection(ConString);
        string querystring = "Select * from person";

        con.Open();
        SqlCommand cmd = new SqlCommand(querystring, con);
        SqlDataReader reader = cmd.ExecuteReader();
        StringBuilder builder = new StringBuilder();

        builder.AppendLine("CREATE TABLE PERSON");
        builder.AppendLine("(");
        builder.AppendLine("[name] [varchar](20) NOT NULL,");
        builder.AppendLine("[surname] [varchar](20) NOT NULL,");
        builder.AppendLine("[gender] [varchar](5) NOT NULL");
        builder.AppendLine(")");

        while (reader.Read())
        {
            var name = reader[0].ToString();
            var surname = reader[1].ToString();
            var gender = reader[2].ToString();
            var value4 = reader[3].ToString();

            Console.WriteLine(name + " " + surname + " " + gender + value4);

            builder.AppendLine("INSERT INTO PERSON (name, surname, gender) VALUES ('" + name + "','" + surname + "','" + gender + "')");
        }

        System.IO.File.WriteAllText(@"script.txt", builder.ToString());

        Console.Read();
    }