How to insert all text values at first row only in SQL Server using C#-CodePudding

I have created textboxes dynamically using the below code

TextBox txt = new TextBox();
txt.Name = ("txt"   (i   1)).ToString();
txt.Location = new Point(1160, 148   (850 * i));

Size txtsize = new Size(260, 50);
txt.Size = txtsize;
txt.BorderStyle = BorderStyle.Fixed3D;
txt.Multiline = true;
txt.Font = new Font("Times New Roman", 14, FontStyle.Bold);

this.Controls.Add(txt);

When I inserted values into database

if (txt.Name == "txt1")
{
    string str = "insert into sample1 (txt1) values ('"   txt.Text "')";

    if (conn.State != ConnectionState.Open)
         conn.Open();

    SqlCommand cmd = new SqlCommand(str, conn);
    cmd.Parameters.Add(new SqlParameter("@txt1", txt.Text.Trim()));
    cmd.ExecuteNonQuery();

    conn.Close();
}
.......

I am getting the output like

txt1   txt2   txt3
-------------------
10     NULL   NULL
NULL   20     NULL
NULL   NULL   30

I would like to get

txt1   txt2   txt3
------------------
  10    20     30

Can someone help me get this result?

CodePudding user response：

Change

if (txt.Name == "txt1")
{
    string str = "insert into sample1 (txt1) values ('"   txt.Text "')";

    if (conn.State != ConnectionState.Open)
         conn.Open();

    SqlCommand cmd = new SqlCommand(str, conn);
    cmd.Parameters.Add(new SqlParameter("@txt1", txt.Text.Trim()));
    cmd.ExecuteNonQuery();

    conn.Close();
}

to this

var textBoxNames = Enumerable
    .Range(1, 3)
    .Select(n => "txt"   n);

var textBoxes = Controls
    .OfType<TextBox>()
    .Where(tb => textBoxNames.Contains(tb.Name));

var fields = textBoxNames
    .Aggregate((a, b) => $"{a}, {b}");

var pars = textBoxNames
    .Select(s => "@"   s)
    .Aggregate((a, b) => $"{a}, {b}");

string str = $"insert into sample1 ({fields}) values ({pars})";

if (conn.State != ConnectionState.Open)
    conn.Open();

SqlCommand cmd = new SqlCommand(str, conn);
var parList = textBoxes
    .Select(tb => new SqlParameter("@"   tb.Name, tb.Text))
    .ToArray();
cmd.Parameters.AddRange(parList);
cmd.ExecuteNonQuery();

conn.Close();

CodePudding user response：

You need to insert values outside of your foreach loop. First set values, then after foreach insert your records.

Outside foreach loop write the code below:


//This cant be in foreach loop

string str = "insert into sample1 (txt1, txt2, txt3) values (@txt1, @txt2, @txt3)";
if (conn.State != ConnectionState.Open)
   conn.Open();
SqlCommand cmd = new SqlCommand(str, conn);
cmd.Parameters.Add(new SqlParameter("@txt1", txt.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("@txt2", txt2.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("@txt3", txt3.Text.Trim()));
cmd.ExecuteNonQuery();
conn.Close();

CodePudding user response：

var textBoxes = this.Controls.OfType<TextBox>().ToList();

var columns = string.Join(", ", textBoxes.Select(tb => tb.Name));
var parameters = string.Join(", ", textBoxes.Select((_, i) => "@p"   i));

string sql = $"insert into sample1 ({columns}) values ({parameters})";

using (var con = new SqlConnection(_connectionString))
{
    con.Open();
    using (var cmd = new SqlCommand(sql, con))
    {
        for (int i = 0; i < textBoxes.Count; i  )
        {
            cmd.Parameters.Add("@p"   i, SqlDbType.NVarChar).Value = textBoxes[i].Text;
        }
        cmd.ExecuteNonQuery();
    }
}