如何在SQL Server中使用c#在第一行插入所有文本值

Question

我已经使用下面的代码动态创建了文本框

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);

当我插入值到数据库

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();
}
.......

我得到的输出是

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

我想要得到

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

谁能帮我弄到这个结果?

Answer 1

变化

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();
}

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();

Answer 2

您需要在foreach循环之外插入值。首先设置值，然后在foreach之后插入记录。

在foreach循环外写下面的代码:

//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();

Answer 3

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();
}
}