如何使用DataGridView在SQL Server中存储多条记录

本文关键字:存储 记录 Server 何使用 DataGridView SQL | 更新日期: 2023-09-27 17:49:27

我正在尝试使用c#从DataGridView插入5条记录到SQL Server数据库表。

从我的代码,它需要几个记录的输入,但在数据库中只插入第一个记录。有人可以帮助我保存5记录在数据库中,只需点击保存按钮?

下面是我的代码:
    DataSet ds = new DataSet();
    SqlConnection cs = new SqlConnection(@"Data Source=DELL-PC;Initial Catalog=Image_DB;Integrated Security=True");
    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand();
    BindingSource Input = new BindingSource();
    DataView dview = new DataView();
    private void Form1_Load(object sender, EventArgs e)
    {
        //create a DataGridView Image Column
        DataGridViewImageColumn dgvImage = new DataGridViewImageColumn();
        //set a header test to DataGridView Image Column
        dgvImage.HeaderText = "Images";
        dgvImage.ImageLayout = DataGridViewImageCellLayout.Stretch;
        DataGridViewTextBoxColumn dgvId = new DataGridViewTextBoxColumn();
        dgvId.HeaderText = "ID";
        DataGridViewTextBoxColumn dgvName = new DataGridViewTextBoxColumn();
        dgvName.HeaderText = "Name";
        dataGridView1.Columns.Add(dgvId);
        dataGridView1.Columns.Add(dgvName);
        dataGridView1.Columns.Add(dgvImage);
        dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        dataGridView1.RowTemplate.Height = 120;
        dataGridView1.AllowUserToAddRows = false;
    }
    // button add data to dataGridView
    // insert image from pictureBox to dataGridView 
    private void btn_Add_Click(object sender, EventArgs e)
    {
        MemoryStream ms = new MemoryStream();
        pictureBox1.Image.Save(ms, pictureBox1.Image.RawFormat);
        byte[] img = ms.ToArray();
        dataGridView1.Rows.Add(txt_UserID.Text, txt_Name.Text, img);
    }
    // browse image in pictureBox1 Click
    private void pictureBox1_Click(object sender, EventArgs e)
    {
        OpenFileDialog opf = new OpenFileDialog();
        opf.Filter = "Choose Image(*.jpg; *.png; *.gif)|*.jpg; *.png; *.gif";
        if (opf.ShowDialog() == DialogResult.OK)
        {
            pictureBox1.Image = Image.FromFile(opf.FileName);
        }
    }
    private void btn_Save_Click(object sender, EventArgs e)
    {
        for (int i = 5; i < dataGridView1.Rows.Count; i++)
        {
            string col1 = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col2 = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string col3 = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            string insert_sql = "INSERT INTO Input(UserID, UserName, PassImage) VALUES ('" + col1 + "','" + col2 + "','" + col3 + "')";
            this.getcom(insert_sql);
        }
        MessageBox.Show("Record Added");
    }
    public SqlConnection GetSqlConnection() //connection function
    {
        string str_sqlcon = "Data Source=DELL-PC;Initial Catalog=Image_DB;Integrated Security=True";
        SqlConnection mycon = new SqlConnection(str_sqlcon);
        mycon.Open();
        return mycon;
    }
    public void getcom(string sqlstr) //function for adding rows
    {
        SqlConnection sqlcon = this.GetSqlConnection(); // Watch out same string type as GetSQLConnection function
        SqlCommand sqlcom = new SqlCommand(sqlstr, sqlcon);
        sqlcom.ExecuteNonQuery();
        sqlcom.Dispose();
        sqlcon.Close();
        sqlcon.Dispose();
    }

如何使用DataGridView在SQL Server中存储多条记录

问题是在这个" for "循环中,您没有在for循环中使用i

最好试试这个。

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    string col1 = dataGridView1.Rows[i].Cells[0].Value.ToString();
    string col2 = dataGridView1.Rows[i].Cells[1].Value.ToString();
    string col3 = dataGridView1.Rows[i].Cells[2].Value.ToString();
    string insert_sql = "INSERT INTO Input(UserID, UserName, PassImage) VALUES ('" + col1 + "','" + col2 + "','" + col3 + "')";
    this.getcom(insert_sql);
}
                                             

修改代码逻辑,如果需要的话

for (int i = 5; i < dataGridView1.Rows.Count; i++)

将其更改为for (int i = 0; i < dataGridView1.Rows.Count; i++)

您将5分配给i,因此在一次插入之后,它将退出循环,因为i将变成6

在指定datagridview行时也包含'i' .否则它将始终指向一行并插入5行,但值相同