C# with sql query result to variable

Question

I'm new in C#, so maybe i didn't wrotte it correctly.

In my C# form, i put order nr. into a form. Then it check this in sql.

need to execute this query:

Get OrderID from Order nr.
Need to check if Order ID is in [System_Opened_Orders] = order is opened
If order is opened, then need to run in C# Messagebox.Show("Order opened, try again later")
If is not opened, load data from order

SQL query:


    Declare @OrderID uniqueidentifier
    SET @OrderID = (SELECT m.ID FROM [Agenda].[dbo].[orders] m
    WHERE m.OrderNumber= @sqlordernr)

if EXISTS (SELECT 1 FROM [Agenda].[dbo].[System_Opened_Orders] 
        WHERE Record_ID=@OrderID )
            BEGIN
                  // Tell its opened, need try again later
            END

        ELSE
            BEGIN
                   // Order is not openend, can get custommer data
                   select ID, OrderNumber, CustommerName, CustommerCity
                   FROM [Agenda].[dbo].[orders] where OrderNumber = @sqlordernr
            END

C# code:

   SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Agenda;Persist Security Info=True;User ID=sa;Password=xxxxxxxx");
            conn.Open();

            SqlCommand command = new SqlCommand(/*sql query*/, conn);
            command.Parameters.AddWithValue("@sqlordernr", odernr);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                   // if order is opened then Messagebox.Show

                   // if is not opened 
                   // String OrderCustommerName = reader.GetString(3).TrimEnd();
                   // String OrderCustommerCity= reader.GetString(4).TrimEnd();
                   // lbOrderData.Text = OrderCustommerName + " " + OrderCustommerCity;
                }
            }
            conn.Close();

The answer is good but if you want you can try SQL output parameters as well. — Filip Cordas
– Filip Cordas, Commented Feb 27, 2017 at 0:34

Fırat Esmer · Accepted Answer · 2017-02-26 22:17:40Z

3

You don't need to select anything if "order is opened"

IF EXISTS (SELECT 1 FROM [Agenda].[dbo].[System_Opened_Orders] WHERE Record_ID=@OrderID )
BEGIN
    -- Order is not openend, get custommer data
    select ID, OrderNumber, CustommerName, CustommerCity
    FROM [Agenda].[dbo].[orders] where OrderNumber = @sqlordernr
END

And then at code side; if there is no row at datareader, while.Read() will return false

using (SqlDataReader reader = command.ExecuteReader())
{
    while(reader.Read())
    {
       // String OrderCustommerName = reader.GetString(3).TrimEnd();
       // String OrderCustommerCity= reader.GetString(4).TrimEnd();
       // lbOrderData.Text = OrderCustommerName + " " + OrderCustommerCity;
    }
}

answered Feb 26, 2017 at 22:17

Fırat Esmer

7781 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nordscan Over a year ago

Thank you, very simple, but in my case is better to use the select 1

Steve · Accepted Answer · 2017-02-26 22:05:35Z

You need to return something from your if statement inside the stored procedure. You can simply return a single value

if EXISTS (SELECT 1 FROM [Agenda].[dbo].[System_Opened_Orders] 
    WHERE Record_ID=@OrderID )
        BEGIN
            select 1 
        END
    ELSE
        BEGIN
           -- Order is not openend, get custommer data
           select ID, OrderNumber, CustommerName, CustommerCity
           FROM [Agenda].[dbo].[orders] where OrderNumber = @sqlordernr
        END

Now you can tell the difference between the two results looking at the property FieldCount of the DataReader

using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.FieldCount == 1)
        MessageBox.Show("Order open");
    else
    {
        while(reader.Read())
        {
           // String OrderCustommerName = reader.GetString(3).TrimEnd();
           // String OrderCustommerCity= reader.GetString(4).TrimEnd();
           // lbOrderData.Text = OrderCustommerName + " " + OrderCustommerCity;
        }
    }
}

Collectives™ on Stack Overflow

C# with sql query result to variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related