如何在不使用多个sql查询的情况下从表中获取所有/特定记录

本文关键字：获取 记录 情况下 查询 sql | 更新日期: 2023-09-27 18:13:36

我有两个下拉列表和一个网格视图控件。当下拉列表更改时，我希望这些记录显示在我的网格中。如果在下拉列表中的任何一个或两者中都选择了"all"，那么我只想要这些记录。但是我想要的输出不需要对不同的条件使用不同的查询。

我的代码：

private void BindGrid()
        {
            DataTable dt = new DataTable();
            string query = "select * from customers";
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=TEST;User Id=sa;Password=");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            con.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();
            lblCountRows.Text = "Total Rows=" + dt.Rows.Count.ToString() + "";
        }
        private void BindDisposeId()
        {
            DataTable dt = new DataTable();
            string query = "select distinct DisposeID from customers";
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=TEST;User Id=sa;Password=");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            con.Close();
            ddlDisposeID.DataTextField = "DisposeID";
            ddlDisposeID.DataValueField = "DisposeID";
            ddlDisposeID.DataSource = dt;
            ddlDisposeID.DataBind();
            ddlDisposeID.Items.Insert(0, new ListItem("All", ""));
        }
        private void BindReceiveId()
        {
            DataTable dt = new DataTable();
            string query = "select distinct ReceiveID from customers";
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=TEST;User Id=sa;Password=");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            con.Close();
            ddlReceiveID.DataTextField = "ReceiveID";
            ddlReceiveID.DataValueField = "ReceiveID";
            ddlReceiveID.DataSource = dt;
            ddlReceiveID.DataBind();
            ddlReceiveID.Items.Insert(0, new ListItem("All", ""));
        }
        protected void DisposeIDchanged(object sender, EventArgs e)
        {
            ddlChanges();
        }
        protected void ReceiveIdchanged(object sender, EventArgs e)
        {
            ddlChanges();
        }
        private void ddlChanges()
        {
            string query = "";
            DataTable dt = new DataTable();
            query = "SELECT C1.DisposeDate,C1.DisposeID,C1.ReceiveDate,C1.ReceiveID FROM Customers C1 LEFT JOIN Customers C2 ON C1.DisposeID=C1.ReceiveID where C1.DisposeID='" + ddlDisposeID.SelectedValue + "' AND C1.ReceiveID='" + ddlReceiveID.SelectedValue + "'";
            SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=TEST;User Id=sa;Password=");
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            con.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();            
        }

如何在不使用多个sql查询的情况下从表中获取所有/特定记录

没有完全理解你想要的是什么。我将继续这个假设。

您有两个参数。CCD_ 1和CCD_ 2。如果同时显示了这两个值，则需要筛选查询。

但是，如果没有指定任何参数。您希望退回All客户。

就我个人而言，我会使用存储过程来避免使用动态SQL。类似这样的东西：

CREATE PROC up_Get_Customers
(
    @DisposeId INT,
    @ReceiveId INT
)
AS
BEGIN
    SELECT C1.DisposeDate,C1.DisposeID,C1.ReceiveDate,C1.ReceiveID
    FROM Customers C1 
    LEFT JOIN Customers C2 ON C1.DisposeID=C1.ReceiveID 
    WHERE (@DisposeId IS NULL OR C1.DisposeID= @DisposeId)
       AND (@ReceiveId IS NULL OR C1.ReceiveID= @ReceiveId)
END

在查询中，我是说WHEN @DisposeId没有被指定，THEN只是返回所有记录。但是，如果已指定，则仅返回匹配的DisposeId。ReceiveId也是如此。

因此，要返回所有记录，您可以调用这样的存储过程：

EXEC up_Get_Customers @DisposeId = NULL, @ReceiveId = NULL

如果您想在指定值时返回客户，它将是：

EXEC up_Get_Customers @DisposeId = 1, @ReceiveId = 5

您甚至可以指定一个特定的DisposeId，但不能指定DisposeId0。这将返回具有指定DisposeId但具有任何ReceiveId的所有客户，反之亦然。

--All DisposeIds for a specific ReceveId
EXEC up_Get_Customers @DisposeId = 1, @ReceiveId = NULL
--All ReceveIds for a specific DisposeId
EXEC up_Get_Customers @DisposeId = NULL, @ReceiveId = 5

下面是一个调用C#代码中存储过程的(非常基本的(示例：

private void ddlChanges()
        {
            var con = new SqlConnection("Data Source=.;Initial Catalog=TEST;;User Id=sa;Password=admin@123");
            //Get the selected items from the dropdown lists
           var disposeId =  ddlDiposeId.SelectedValue.ToString() == "All" ? null : (int?)Convert.ToIn32(ddlDiposeId.SelectedValue);
           var receiveId = ddlReceiveId.SelectedValue.ToString() == "All" ? null :  (int?)Convert.ToInt32(ddlReceiveId.SelectedValue);
            //Create the two parameters for the stored procedures
            var sqlParameters = new []
                {
                    new SqlParameter("DisposeId", disposeId ?? (object)DBNull.Value),
                    new SqlParameter("ReceiveId", receivedId ?? (object)DBNull.Value)
                };
            //Create the SqlCommand.
            var sqlCommand = new SqlCommand("up_Get_Customers", con);
            sqlCommand.Parameters.AddRange(sqlParameters);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            var adapter = new SqlDataAdapter(sqlCommand);
            var dataTable = new DataTable();
            try
            {
                con.Open();
                adapter.Fill(dataTable);
            }
            catch (SqlException sqlException)
            {
                //Handle errors
            }
            finally
            {
                con.Close();
            }
        }
相关文章：