如何使用C#在SQLServer中僅在第一行插入所有文本值-有解無憂

我使用以下代碼動態創建了文本框

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

有人可以幫我得到這個結果嗎？

uj5u.com熱心網友回復：

改變

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

uj5u.com熱心網友回復：

您需要在 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();

uj5u.com熱心網友回復：

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/486993.html

標籤：C＃ sql服务器

上一篇：SqlServer-動態復制表結構-如何管理浮點資料型別

下一篇：查找非連續日期范圍