Get all the row data with select statement in codebehind C# using ADO.NET

Question

Maybe someone can help me with the following problem. what i want to achieve is to get all rows of a table and put them in a string with comma between each value. The result should be a scriptfile to get all of the rowdata in the table.

For so far:

static void Main(string[] args) {
    StringBuilder builder = new StringBuilder();
    string ConString = @"Data Source=.;Initial Catalog=USR;Integrated Security=True";
    foreach (string itemtablename in TableNames())
    {
        Schema schema = new Schema();
        schema.TableName = itemtablename; //the name of the table

        //int i = 0;
        string queryvalues = "select * from " + itemtablename;
        SqlConnection conValues = new SqlConnection(ConString);
        using (conValues)
        {
            conValues.Open();
            SqlCommand cmdSchemaValues = new SqlCommand(queryvalues, conValues);
            SqlDataReader readerSchemaValues = cmdSchemaValues.ExecuteReader();
            DataTable dataTable = readerSchemaValues.GetSchemaTable();

            //builder.AppendLine("INSERT INTO " + itemtablename);
            //builder.AppendLine(" VALUES (");

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                string rowValues= dataTable.Rows[i].ToString() + ",";
               
                builder.Append(rowValues);
            }
        }
    }
    System.IO.File.WriteAllText(@"C:\script.txt", builder.ToString());
}

Warning, statements like `string queryvalues = "select * from " + itemtablename;` are **wide** open to injection. — Thom A, Sep 16 '20 at 12:49
What is this trying to do? Generate a script or actually insert data? Why not export the data to a CSV file and import it as needed? A single `'` in one of those fields could result in an invalid script or worse, allow script injection attacks — Panagiotis Kanavos, Sep 16 '20 at 12:51
Just need to create only the insert value script with data form the database. What is the best way to achieve this? — mahiruzun, Sep 16 '20 at 12:56
what is the actual _problem_ here? You've shown some code. You've described a goal. What's missing is any discussion of your precise difficulty. Where does your code fall short of that goal currently? Please describe the current behaviour of the code, and give an example of exactly what you wanted instead. — ADyson, Sep 16 '20 at 12:57

score 0 · Answer 1 · answered Sep 16 '20 at 13:03

0

If "generating the insert script for data present in the table" is the objective then you can try using the SQL Server Management Studio to do that. It has been discussed in other Stack Overflow questions as well.

answered Sep 16 '20 at 13:03

AKV

46
4

Maybe the OP wants to be able to automate that, or do it at scale for multiple tables? – ADyson Sep 16 '20 at 13:07
I want to create the insert values part from codebehind. – mahiruzun Sep 16 '20 at 13:29

TJacken · Accepted Answer · 2020-09-16T13:44:22.550

0

Try this logic:

foreach(DataRow row in datatable.Rows)
{
    builder.AppendLine("INSERT INTO " + itemtablename + "(" + string.Join(", ", datatable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray()).Trim().TrimEnd(","));
    builder.AppendLine("VALUES ('" + string.Join("', '", row.ItemArray).Trim().TrimEnd('\'').Trim().TrimEnd(',') + "')")
}

string script = builder.ToString();

edited Sep 16 '20 at 13:44

answered Sep 16 '20 at 13:35

TJacken

354
3
12

Get all the row data with select statement in codebehind C# using ADO.NET

2 Answers2