Problem with conversion of values

[Shaggy Hiker]

While I can't say that I read every post thoroughly, it seems like the original code had a different issue that I didn't see addressed anywhere. You appeared to be using a datareader to get the first value from the first row of whatever was returned. If that's the case, then getting rid of the datareader will have better performance. ExecuteScalar gets the first value from the first row of whatever the query returns. It will be somewhat more compact than using a datareader.

[jmcilhinney]

If that's the case, then getting rid of the datareader will have better performance. ExecuteScalar gets the first value from the first row of whatever the query returns. It will be somewhat more compact than using a datareader.

For the record, ExecuteScalar uses a data reader internally. It can certainly make code more succinct and should be used where appropriate, but it won't affect performance. Here's the implementation of System.Data.SqlClient.SqlCommand.ExecuteScalar from .NET Framework:

        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);
            }
        }

[Shaggy Hiker]

I did not know that. I see a couple things in there that might still improve the performance, depending on what they imply, such as the ADP.ExecuteScalar and that CompleteExecuteScalar, but mostly I am struck by the utterly awful choice of naming the datareader object "ds".

[IliaPreston]

Windows Forms wasn't originally intended to make the trip to .NET Core. Microsoft were going to let it wither on the vine with .NET Framework and require Windows developers to use newer technologies but there was a bit of an outcry over that from many for whom WinForms did what they needed and they didn't want to learn something new. Of course, many of those people also complained about it not being able to do certain things they wanted to do but let's not worry about that. Suffice to say that there was enough pushback that Microsoft added support for WinForms to .NET Core - I think it was in version 3.0. Unfortunately though, it wasn't just a case of copy what they had into .NET Core. Things were a lot more complex than that so many aspects have been rewritten to mimic what was available before. One of those things is the designer. There are definitely bugs and other issues in the WinForms designer in .NET Core that did not exist in .NET Framework. It's possible that Microsoft will fix them but, given the low priority of WinForms these days, they would have to be affecting a significant number of people. Given that you are doing this to be able to use a using declaration instead of a using statement, I'd call it a waste of time from the outset. I suggest that you try again and see whether the same thing happens with a new project. If it does then search online to see whether anyone else has a solution or workaround. If they don't, just give it up and stop wasting time on something so unimportant.

Thanks for your advice.

Microsoft were going to let it [Windows Forms] wither on the vine with .NET Framework and require Windows developers to use newer technologies

What are those newer technologies?
Please note that i am talking about developing GUI applications (having forms with buttons, checkboxes, textboxes, grids, etc. on them) plus the ability to plug in sqLite.

I thought c# was the best and the most advanced tool to do that.
Are you saying that that s not true?

Thanks again for your help.

[techgnome]

This is where things get kind of muddled I think. The answer is "it depends" ... IF you want to build a classic WinForms that will only run on Windows, then you can use FW 4.8 and either VB.NET or C# will be sufficient. BUT, if you want something cross-platform, then you're looking at C# with .NET 8 and using one of the many seemingly half-baked technologies of Blazor, Avalona, or.... there's another but the name escapes me... But they are web-based, and don't have a GUI designer... so it's HTML/React/CSS style of design, which if you suck at... I'm great at the HTML, suck at the React part... which, kinda sucks.

-tg

[jmcilhinney]

I thought c# was the best and the most advanced tool to do that.

C# is a language, not a technology. You still have to choose what technology you're going to use to build the app. If you want a GUI app for Windows then WinForms is the oldest tech, then came WPF and you can also use UWP. Theye will all work but are not really being actively developed by Microsoft anymore. MAUI is an evolution of Xamarin and can be used to build GUI apps for Windows and other platforms.

[IliaPreston]

Stop creating new commands. You have one 'using' statement for the command so you should have one command object. Understand how a 'using' statement or declaration works. You create an object with that line of code and then that object gets disposed at the end of the scope. Create one command object, use it, then it gets disposed:

        private void btnInsStudents_Click(object sender, EventArgs e)
        {
            using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
            using SQLiteCommand cmd = new SQLiteCommand {Connection = conn};

            conn.Open();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('John', 'Davis', 1001)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('Michael', 'Colbert', 1002)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('Jennifer', 'Swanson', 1003)";
            cmd.ExecuteNonQuery();
        }

Of course, it's probably going to be even more correct to create the CommandText once too and add parameters, then set the Value of each parameter multiple times.

Thanks a lot for your help.

The above code shows how to declare the command object (using the using declaration) only once and then use it multiple times to run multiple queries.
Can you please also advise as to how to do the same thing for:

data reader (SQLiteDataReader)
data adapter (SQLiteDataAdapter)
and
datatable (DataTable)

as well

In other words how to declare a DataReader variable using the using declaration only once, and then use it multiple times.
Will it be like this:

                using SQLiteDataReader rdr = new SQLiteDataReader(cmd);

Or:

                using SQLiteDataReader rdr = new SQLiteDataReader(Command = cmd);

Or:
What?

Same question about data adapter and data table.

Also, after each use of the DataReader, SQLiteDataAdapter and DataTable should I close/clear them before using them for another query, or closing/clearing them is not needed?

    rdr.Close();
    adapter.Dispose();
    MyDataTable.???

Please help.
Thanks again.

[IliaPreston]

Stop creating new commands. You have one 'using' statement for the command so you should have one command object. Understand how a 'using' statement or declaration works. You create an object with that line of code and then that object gets disposed at the end of the scope. Create one command object, use it, then it gets disposed:

        private void btnInsStudents_Click(object sender, EventArgs e)
        {
            using SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBFilePath);
            using SQLiteCommand cmd = new SQLiteCommand {Connection = conn};

            conn.Open();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('John', 'Davis', 1001)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('Michael', 'Colbert', 1002)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "insert  into  STUDENTS (STD_FNAME, STD_LNAME, STD_NUM)  values  ('Jennifer', 'Swanson', 1003)";
            cmd.ExecuteNonQuery();
        }

Of course, it's probably going to be even more correct to create the CommandText once too and add parameters, then set the Value of each parameter multiple times.

Your response to post #47 (the post immediately before this) would be greatly appreciated.

I just need a bare minimum example of declaring a data reader using the using declaration only once and then using that data reader multiple times.
Same thing for data adapter and DataTable.

Thanks a lot for your help.
Ilia

[jmcilhinney]

I just need a bare minimum example of declaring a data reader using the using declaration only once and then using that data reader multiple times.

No you don't, because that's not a thing. You should already know that. You don't create the data reader. It is created when you call ExecuteReader on a command. You might have one command that you call ExecuteReader on multiple times but that doesn't mean that you have one data reader. You're still going to have one data reader for each call so you need one Using statement per call.

[Shaggy Hiker]

Is it even possible to use a datareader multiple times? My understanding is that it is forwards only, so once you've reached the end of the result set, there isn't anything else you can do with the datareader other than some trivial items like seeing whether or not it has (had) rows, and even that may not work (I've never tried, because at that point I'd already know the answer).

You might hold onto dataadapters, and you'd certainly hold onto datatables. In both cases, you just wouldn't...gah, how do you say that so that it doesn't look ridiculous? You wouldn't use Using...bleh!

Dataadapters you might hold onto for the purpose of later using them for CommandBuilders, though that's a pretty specialized use and therefore rare. Datatables...well, that's just an in-memory representation of the data. You can do whatever you want with them.