Show all data table value into text box

Question

I want to show my data table column value into text box but its showing only one row value my Query returns only two columns and i want to show both column in text box similar to datatable

My data table has two column col1 and col2 i want to show this column value into text box I want to show my text vale similar to this col1 col2 @ID int, @additional varchar(max), @Mail_ID varchar(max)

private void Button5_Click(object sender, EventArgs e)
        {
            if (checkedListBox1.CheckedItems.Count > 0)
            {
                textBox5.Text = String.Empty;
                string strSQLConn = "Server =" + textBox1.Text + "; Initial Catalog =" + textBox2.Text + "; User ID =" + textBox3.Text + "; Password = " + textBox4.Text + ";";
                SqlConnection SQLConn = new SqlConnection(strSQLConn);
                SQLConn.Open();
                foreach (var item in checkedListBox1.CheckedItems)
                {
                    DataRowView row = item as DataRowView;
                    SqlCommand SQLcmd2 = new SqlCommand("WITH cte AS (SELECT '@' + COLUMN_NAME AS c1, CASE DATA_TYPE WHEN 'VARCHAR' THEN 'varchar(max)' WHEN 'NVARCHAR' THEN 'nvarchar(max)' WHEN 'INT' THEN 'int' WHEN 'DATETIME' THEN 'Datetime()' WHEN 'Float' THEN 'float' END AS c2, ROW_NUMBER() OVER(ORDER BY DATA_TYPE) AS rn, COUNT(*) OVER() AS cnt FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = '" + row["Table_Name"] + "') SELECT c1 As col1, c2 +CASE WHEN rn<cnt THEN ',' ELSE '' END AS col2 FROM cte;", SQLConn);

                    SQLCmd.CommandType = CommandType.Text;
                    SqlDataAdapter Da2 = new SqlDataAdapter(SQLcmd2);
                    DataTable dt2 = new DataTable();
                    Da2.Fill(dt2);                 
                    StringBuilder result = new StringBuilder();
                    foreach (DataRow dr in dt2.Rows)
                    {
                        foreach (DataColumn dc in dt2.Columns)
                        {
                            result.Append(dr[dc].ToString());
                        }
                    }

                    textBox5.Text = result.ToString();
                    textBox5.Text += "Create Proc SP_Insert_"+ row["Table_Name"] + Environment.NewLine +"Declare"+ " ( " + dt2.Rows[0]["col1"].ToString()
                }

Robin Bennett Robin Bennett · Accepted Answer · 2019-09-24T08:40:52

When you do this:

textBox5.Text = result.ToString();

It replaces any previous value, from the previous rows. Instead, do something like this:

if (textBox5.Text != string.empty) textBox5.Text += environment.NewLine;
textBox5.Text += result.ToString();

Also (just spotted this) you reset the result for every row. Move this line outside the loop:

StringBuilder result = new StringBuilder();

Show all data table value into text box

1 Answers