选择c#.net中带有for循环的SQL server db中的所有行，并设置为datagridview

本文关键字：datagridview 设置 db SQL net for 选择 循环 server | 更新日期: 2023-09-27 18:06:13

我有两个名称为Sale和Sale return的表单
sale形式工作良好，但我有sale return形式的问题。

我想从Sale Database和for loop中获得datagridview中的所有行，但是当我运行下面给定的选择查询时，只有一行有选择。

所以请帮我解决这个问题。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 namespace DataGridViewtoTextbox
 {
public partial class Form2 : Form
{
    public Form2()
    {
        InitializeComponent();
    }
    private void Form2_Load(object sender, EventArgs e)
    {
        Random rand = new Random();
        DataGridView dgv = this.dataGridView1;
        //DATAGRIDVIEW SETTING
        dgv.AllowUserToAddRows = false;
        dgv.RowHeadersVisible = false;
        dgv.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
        //ADD COLUMN HEADERS
        dgv.Columns.Add("Invoice", "SInvoice");
        dgv.Columns.Add("Code", "ItemCode");
        dgv.Columns.Add("Amount", "Price");
        dgv.Columns.Add("Quantity", "Quantity");
        //ADD 10 ROWS

        SqlConnection con = new SqlConnection("Data Source=.; Initial Catalog=mateenwin; User ID=sa; Password=123");
        con.Open();
        SqlCommand cmd = new SqlCommand("Select Sale_Invoice_No,Item_Code,Item_Payable_Amount from sale where Sale_Invoice_No = '" + textBox1.Text + "'", con);
        SqlDataReader sdr;
        sdr = cmd.ExecuteReader();
        if (sdr.Read())
        {
            int itm = (Int32)sdr["Sale_Invoice_No"];
            int inovice = (Int32)sdr["Item_Code"];
            double ptype = (double)sdr["Item_Payable_Amount"];
            int qnty = (Int32)0;
            ////NOW, POPULATE THE DATA INTO THE CELLS
            int n = dgv.Rows.Add(sdr);

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                dataGridView1.Rows[i].Cells[0].Value = itm;
                dataGridView1.Rows[i].Cells[1].Value = itm;
                dataGridView1.Rows[i].Cells[2].Value = ptype;
                dataGridView1.Rows[i].Cells[3].Value = qnty;
                con.Close();
             }
        }

        //int inv =Convert.ToInt32(cmd.Parameters.AddWithValue("@Sale_Invoice", "Sale_Invoice"));
        //int cod=Convert.ToInt32( cmd.Parameters.AddWithValue("@Item_Code", "Item_Code"));
        //double amount=Convert.ToDouble( cmd.Parameters.AddWithValue("@Item_Payable_Amount", "Item_Payable_Amount"));
        //cmd.ExecuteScalar();
        ////NOW, POPULATE THE DATA INTO THE CELLS
        //for (int i = 0; i < 10; i++)
        //{
        //    double price = rand.Next(1, 30) * rand.NextDouble();
        //    dgv.Rows[i].Cells[0].Value = inv;
        //    dgv.Rows[i].Cells[1].Value = cod;
        //    dgv.Rows[i].Cells[2].Value = amount;
        //    dgv.Rows[i].Cells[3].Value = 0;
        //}
        //CLEARS THE DEFAULT SELECTION WHICH IS THE FIRST ROW
        dgv.ClearSelection();
    }
    private void textBox1_TextChanged(object sender, EventArgs e)
    {
    }
    private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        dataGridView1.BeginEdit(true);
        int cod = Convert.ToInt32(dataGridView1.CurrentRow.Cells[1].Value);
        double price = Convert.ToDouble(dataGridView1.CurrentRow.Cells[2].Value);
        dataGridView1.CurrentRow.Cells[3].Value =Convert.ToDouble( (cod) * (price));
    }
}
}

选择c#.net中带有for循环的SQL server db中的所有行，并设置为datagridview

试试下面的代码:

SqlConnection con = new SqlConnection("Data Source=.; Initial Catalog=mateenwin; User ID=sa; Password=123");
con.Open();
SqlCommand cmd = new SqlCommand(string.Format("Select Sale_Invoice_No as Invoice, Item_Code as Code, Item_Payable_Amount as Amount, 0.00 as Quantity from sale where Sale_Invoice_No = '{0}'", textBox1.Text), con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet dsInvoices = new DataSet();
sda.Fill(dsInvoices);
dgv.DataSource = null;
dgv.DataSource = dsInvoices.Tables.Count > 0 ? dsInvoices.Tables[0] : null;

如果不需要使用for循环插入数据，您可以将数据获取到Dataset并将其与网格绑定，如下所示。

private void Form2_Load(object sender, EventArgs e)
{
    string txt=textBox1.Text;
    DataSet ds = new DataSet();
    ds=db.getDataQuery(txt);
    if (ds.Tables[0].Rows.Count > 0)
    {
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }
}

查询获取数据

public DataSet getDataQuery(string txt)
{
   try
   {
        String strQuery = @"Select Sale_Invoice_No, Item_Code, Item_Payable_Amount from sale where Sale_Invoice_No = '" + txt + "'";
            return SqlHelper.ExecuteDataset(your_connection_string_name, CommandType.Text, strQuery);
   }
  catch (Exception ex)
  {
     throw ex;
  }

}

请根据你的答案编辑。你必须创建一个连接字符串。尝试创建全局连接。这是个好习惯。

似乎你正在关闭连接数据库内for循环。因此，预计在处理第一行连接后将关闭。此外，阅读器需要推进下一行，直到它有行。此外，你需要在使用阅读器之前进行适当的检查。

请检查我的代码并使用它。

do
{
        if (sdr.HasRows)
        {

        int itm = (Int32)sdr["Sale_Invoice_No"];
        int inovice = (Int32)sdr["Item_Code"];
        double ptype = (double)sdr["Item_Payable_Amount"];
        int qnty = (Int32)0;
        ////NOW, POPULATE THE DATA INTO THE CELLS
        int n = dgv.Rows.Add(sdr);

        for (int i = 0; i < dgv.Rows.Count; i++)
        {
            dataGridView1.Rows[i].Cells[0].Value = itm;
            dataGridView1.Rows[i].Cells[1].Value = itm;
            dataGridView1.Rows[i].Cells[2].Value = ptype;
            dataGridView1.Rows[i].Cells[3].Value = qnty;

         }
        }
} while (sdr.NextResult());
con.Close();
相关文章：