Question Calling a method in formload event in another method

[andrewmanuja]

Hi All,
I am developing an app to manage stocks.
I am loading drug orders related data into a window form (basically to a dataGridView) and need to calculate the sum of a column. For the said, purpose, I am using the below code within the FormLoad event;

int d = 0;
for(int n=0; n< dataGridViewOrder .Rows.Count; n++)
{
d += Convert.ToInt32(dataGridViewOrder.Rows[n].Cells[5].Value);
}

I want to perform a condition in a different method within the same Class based on the value of the variable "d" defined above.

However, I am not so sure how I can accomplish this task. Further, I could create the above calculating the value of "d" as a separate function within the same class and not so sure how to call it in another function passing the parameter values to evaluate the condition.

Could someone please help me with this issue?

Thank you in advance.

Kind regards,

Andrew

 

[andrewmanuja]

Hi All,

Please ignore my previous post. My actual requirement was something like below.

My requirement is, I got a column in the database table which is named as "issuequantity". My aim is to calculate the sum of the"issuequantity" for a given "orderID".
Database columns are, orderID, drugID, drugName and issueQuantity.
I create a textbox in the window form (named textbox1) and tried using the below code to calculate the sum of the issuequantity, but didn't work as expected.

SqlConnection con = new SqlConnection(@"Data Source=ABC\SQLEXPRESS;Initial Catalog=Order_DB;Integrated Security=True");
con.Open();
String sqlSelectQuery = "SELECT SUM(issuequantity) FROM tbl_drug WHERE orderID = 1";
SqlCommand cmd = new SqlCommand(sqlSelectQuery, con);
SqlDataReader dr = cmd.ExecuteReader();
textBox1.Text = (dr["SUM(issuequantity)"].ToString ());

con.Close();

Could someone please help me to resolve the above code or propose me a solution to calculate the sum of the "issuequantity" column may be using a dynamic array?

Thank you in advance.

Kind regards,

Andrew

 

[jmcilhinney]

The reason the code you posted is not working is that you never call Read on your data reader. ExecuteReader executes the query but you have to call Read to load the result set, once for each row.

That said, you shouldn't be using a data reader anyway. You are only retrieving a single value and the ExecuteScalar method exists for that specifically.

 

[NoUserHere]

I'd second using ExecuteScaler with using blocks - See link. If you're only retrieving one record, you don't need a reader. A reader would suffice if you were to lets say; read multiple fields from a selection of rows containing data, where you need to sift through each of them and maybe check a given condition. A reader is much heavier resource-wise compared to using scaler. You should also enclose your code with using blocks as they are self-disposing of any resources used and used within them. See the sample code on the link above and as follows ::

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

Hope you found this useful.

 

[jmcilhinney]

A reader is much heavier resource-wise compared to using scaler.

That's not actually true. Calling ExecuteScalar just makes for simpler code, but it actually uses a data reader internally, just as the Fill method of a data adapter does. Here's the implementation of OleDbCommand.ExecuteScalar:

public override object ExecuteScalar()
{
  OleDbConnection.ExecutePermission.Demand();
  IntPtr hScp;
  Bid.ScopeEnter(out hScp, "<oledb.OleDbCommand.ExecuteScalar|API> %d#\n", this.ObjectID);
  try
  {
    object obj = (object) null;
    this._executeQuery = true;
    using (OleDbDataReader oleDbDataReader = this.ExecuteReaderInternal(CommandBehavior.Default, nameof (ExecuteScalar)))
    {
      if (oleDbDataReader.Read())
      {
        if (0 < oleDbDataReader.FieldCount)
          obj = oleDbDataReader.GetValue(0);
      }
    }
    return obj;
  }
  finally
  {
    Bid.ScopeLeave(ref hScp);
  }
}

 

[NoUserHere]

That's interesting to know, but you are talking about the internal structure of how the ExecuteScalar method is constructed. That type of under-the-hood structuring doesn't make sense. Can you point me to a source from Microsoft's documentation that confirms what you wrote John? From my viewpoint; I've always considered it to be a poky way of returning the first result without using any implementation of recursive methods, including readers. After-all, that is why ExecuteScalar only returns one result.

Andrew, The best way I've seen ExecuteNonQuery, ExecuteScalar, and ExecuteReader explained was on this SO topic from 2012. For convenience I've brought the post into the forum here ::

Spoiler: How To Use

ExecuteNonQuery():

1. will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).
2. Returns the count of rows effected by the Query.
3. Return type is int
4. Return value is optional and can be assigned to an integer variable.

ExecuteReader():

1. will work with Action and Non-Action Queries (Select)
2. Returns the collection of rows selected by the Query.
3. Return type is DataReader.
4. Return value is compulsory and should be assigned to an another object DataReader.

ExecuteScalar():

1. will work with Non-Action Queries that contain aggregate functions.
2. Return the first row and first column value of the query result.
3. Return type is object.
4. Return value is compulsory and should be assigned to a variable of required type.

Further, if you want to stick with a reader, you can find the documentation on them here, and you can see what you're missing in the sample code here. - Search for // Call Read before accessing data.

 

[andrewmanuja]

Hi All,
Thank you very much for all your valuable thoughts.
I'll run my program as said and will post a feedback accordingly.
Actually, I'm retrieving multiple data values based on the orderID and one orderID can have more than a single "issuequantity" values.
However, I'll work on the give examples.
Thank you all again very much.
Kind regards,
Andrew

 

[jmcilhinney]

That's interesting to know, but you are talking about the internal structure of how the ExecuteScalar method is constructed.

I am, and I'm doing so because that has a direct effect on the resources required. You said that ExecuteScalar is less resource-intensive than ExecuteReader but that is not true because they both do the same thing under the hood.

That type of under-the-hood structuring doesn't make sense.

It makes perfect sense. A data reader reads data. Calling ExecuteScalar requires that data be read so why would it not use the functionality built into a data reader to do that? If it didn't do so, it would just have to reproduce that functionality, thus violating the DRY principal.

Can you point me to a source from Microsoft's documentation that confirms what you wrote John?

There's nothing in the documentation because it's an implementation detail that developers don't need to concern themselves with. I only brought it up because it demonstrates why what you said about relative resource usage is incorrect. What I posted is actual source code from the OleDbCommand class. I believe that VS alone can do it too but I have ReSharper configured to allow me to navigate to Framework implementations of members in the same way as you can navigate in your own code using Ctrl+Click.

From my viewpoint; I've always considered it to be a poky way of returning the first result without using any implementation of recursive methods, including readers. After-all, that is why ExecuteScalar only returns one result.

ExecuteScalar exists because retrieving a single value from a database is a common operation and thus it saves the effort of repeatedly writing the same code involving a data reader. It uses a data reader internally because a data reader already implements the functionality to read data from a database. Reading a single value is still reading data. There's no reason that the lower-level mechanism to read data should be different based on whether you want a single value or multiple. There's a reason that beginners tend to use a data reader to read a single value when they don't know that ExecuteScalar exists.

 

[NoUserHere]

I can't seem to find the article I read which explained that scalar and readers used different internal structuring and the same article also had different execution times between the two which made it a more believable read. I will try dig it out on the waybackmachine when I get home.

Before I ever read that article, I also believed that the two operated with different structuring anyway. So its nice to stand corrected. Our mistakes are what make us better and more knowledgeable. I guess you can't believe everything you read.

 

[Skydiver]

Actually the efficiency is dependent on the database provider. It's up to each provider to override ExecuteScalar() to make it as efficient (or inefficient) as they please. From what I can see, they both effectively call the reader.00

The OLEDB provider is meant to be a generic driver so it does things with the least about of surprise in terms of behavior. Code for it can be found here.

Spoiler: OleDbCommand.ExecuteScalar()

        override public object ExecuteScalar() {
            OleDbConnection.ExecutePermission.Demand();
 
            IntPtr hscp;
            Bid.ScopeEnter(out hscp, "<oledb.OleDbCommand.ExecuteScalar|API> %d#\n", ObjectID);
            try {
                object value = null;
                _executeQuery = true;
                using(OleDbDataReader reader = ExecuteReaderInternal(CommandBehavior.Default, ADP.ExecuteScalar)) {
                    if (reader.Read() && (0 < reader.FieldCount)) {
                        value = reader.GetValue(0);
                    }
                }
                return value;
            }
            finally {
                Bid.ScopeLeave(ref hscp);
            }
        }

On the other hand, the SQL implementation of can be found here.

Spoiler: OleDbCommand.ExecuteScalar()

override public object ExecuteScalar() {
            SqlConnection.ExecutePermission.Demand();
 
            // Reset _pendingCancel upon entry into any Execute - used to synchronize state
            // between entry into Execute* API and the thread obtaining the stateObject.
            _pendingCancel = false;
 
            SqlStatistics statistics = null;
            IntPtr hscp;
            Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteScalar|API> %d#", ObjectID);
            Bid.CorrelationTrace("<sc.SqlCommand.ExecuteScalar|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
 
            bool success = false;
            int? sqlExceptionNumber = null;
            try {
                statistics = SqlStatistics.StartTimer(Statistics);
                WriteBeginExecuteEvent();
                SqlDataReader ds;
                ds = RunExecuteReader(0, RunBehavior.ReturnImmediately, true, ADP.ExecuteScalar);
                object result = CompleteExecuteScalar(ds, false);
                success = true;
                return result;
            }
            catch (SqlException ex) {
                sqlExceptionNumber = ex.Number;
                throw;
            }
            finally {
                SqlStatistics.StopTimer(statistics);
                Bid.ScopeLeave(ref hscp);
                WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous: true);
            }
        }

private object CompleteExecuteScalar(SqlDataReader ds, bool returnSqlValue) {
            object retResult = null;
 
            try {
                if (ds.Read()) {
                    if (ds.FieldCount > 0) {
                        if (returnSqlValue) {
                            retResult = ds.GetSqlValue(0);
                        }
                        else {
                            retResult = ds.GetValue(0);
                        }
                    }
                }
            }
            finally {
                // clean off the wire
                ds.Close();
            }
 
            return retResult;
        }

 

[jmcilhinney]

@Skydiver, fair point. I was referring to the OleDb provider specifically, while Sheepings may well have been thinking about the SqlClient provider, which can be more efficient by virtue of having been written for one specific data source.

 

[andrewmanuja]

Hi All,

Thanks for your valuable comments. But still its bit hard for me to get my head around the issue I face.

I created a public method to retrieve the data from a database table in to a dataGridView and to evaluate a condition.

Please find the respective herewith;

SqlConnection con = new SqlConnection(@"Data Source=ABC\SQLEXPRESS;Initial Catalog=Order_DB;Integrated Security=True");

public void LoadSumIssueQty()
        {
            con.Open();
            SqlDataAdapter sda = new SqlDataAdapter(@"SELECT orderID, drugID, issuequantity FROM tbl_drug WHERE orderID=1 ", con);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridViewOrder.Rows.Clear();

            foreach (DataRow item in dt.Rows)
            {
                int n = dataGridViewOrder.Rows.Add();
                dataGridViewOrder.Rows[n].Cells[0].Value = item["orderID"].ToString();
                dataGridViewOrder.Rows[n].Cells[1].Value = item["drugID"].ToString();
                dataGridViewOrder.Rows[n].Cells[2].Value = item["issuequantity"].ToString();
            }

            int sumissuequantity = 0;
            for (int n = 0; n < dataGridViewOrder.Rows.Count; n++)
            {
                sumissuequantity += Convert.ToInt32(dataGridViewOrder.Rows[n].Cells[2].Value);
            }

            bool hasissue = false;
            if (sumissuequantity != 0)
            {
                hasissue = true;
            }
            else
            {
                hasissue = false;
            }           
            con.Close();
        }

Upon creating the above method, I am calling it inside the form load method and need to visible a new column on the same dataGridView based on the value of the "hasissue".

Please find the respective code below;

private void Form1_Load(object sender, EventArgs e)
{
    LoadSumIssueQty();
      
    if (hasissue==true)
    {
        dataGridViewOrder.Columns[3].Visible = true;
    }
    else
    {
        dataGridViewOrder.Columns[3].Visible = false;
    }
}

Note
Basically what I am doing here is, I am checking whether drugs have issued against a particular selected "orderID" and if so, open the new column named "Subsequent Issue" and if not, do not open the new column, "Subsequent Issue".
The method "LoadSumIssueQty" is working fine and generating the expected results. However, I am getting issues when running the if condition within the form load event.
The error message is , " The name 'hasissue' does not exist in the current context".

I may be doing a silly mistake. However, appreciate a lot your valuable feedback to rectify the said issue.

Thank you very much in advance.

Kind regards,

Andrew

 

[Skydiver]

That is because hasissue was declared as a local variable in your LoadSumIssueQty() method. Read up on scoping rules used by C#.

I suggest simply having your method return a Boolean flag if that is all that you really need from that method.

As an aside, you can configure the DataGridView to do the column lookups and type conversions for you so that all you need to do is set the DataTable as the grid's DataSource property value. You can get rid of you loop over the table rows to populate the grid rows.

As another aside, you can have your SQL query perform summing that column for you. It is better to get your data from your actual data source/model instead of interrogating your view. (You seem to be loading all your data into the data grid view, and then summing that column from the view.)

 

[andrewmanuja]

That is because hasissue was declared as a local variable in your LoadSumIssueQty() method. Read up on scoping rules used by C#.

I suggest simply having your method return a Boolean flag if that is all that you really need from that method.

As an aside, you can configure the DataGridView to do the column lookups and type conversions for you so that all you need to do is set the DataTable as the grid's DataSource property value. You can get rid of you loop over the table rows to populate the grid rows.

As another aside, you can have your SQL query perform summing that column for you. It is better to get your data from your actual data source/model instead of interrogating your view. (You seem to be loading all your data into the data grid view, and then summing that column from the view.)

Hi,

Thanks a lot.

As specified, I declared the "hasissue" as a public variable and was able to access it from the second method. Now I am able to continue with my work.

I'll try the dataGridView lookup function cos it also seems very handy.

I am actually using the loaded data for my subsequent processes.

Thank you very much.

Kind regards,

Andrew

 

[andrewmanuja]

Hi All,

Thank you very much for jmcilhinney and Sheepings too for their valuable insights.

Kind regards,

Andrew