Visual Studio Community 2022 Data Storage Options

Question

question

VBCoder-7815 asked Jun 20, '22 VBCoder-7815 commented 5 days ago

Visual Studio Community 2022 Data Storage Options

Hi

I have recently installed Visual Studio Community 2022 and started creating a .NET Windows Form (WinForms) app.

The application is being used to replace an Excel Workbook which contained a series of VBA macros.

I need to store a table of approximately 20 columns and 50 rows in my application. A combobox will be used to display a list based on the first column and a series of textboxes will be populated based on the combobox selection. The table will not be updated by any user inputs or selections; it will only be used to populate the combobox and textboxes based on existing entries.

The table values were previously stored in an Excel Sheet which made it simple to load into a combobox or textbox.

I would like to receive advise on the most efficient way to store the table of data in Visual Studio for my Windows Form. I have read a lot of information relating to data tables, databases etc., although am not sure i understand the best method to store my data table.

Can anyone recommend the best method for storing a table of approximately 20 columns and 50 rows? Is there a method to import the table from a spreadsheet or text file into my application?

Appreciate any advise which can be offered.

Regards

windows-forms

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-06-20T09:05:46.367Z

Viorel-1 answered Jun 20, '22 VBCoder-7815 commented 5 days ago

Probably the most efficient method is to store such amount of read-only data directly into your program. In case of VB, the code will look like this:

 Class MyRow
     ' TODO: add columns and use convenient names '
     Public Column1 As Integer
     Public Column2 As String
     Public Column3 As Integer

     Public Sub New(column1 As Integer, column2 As String, column3 As Integer)
         Me.Column1 = column1
         Me.Column2 = column2
         Me.Column3 = column3
     End Sub
 End Class

In your form or class:

 Public ReadOnly MyData As New List(Of MyRow) From {
     New MyRow(1, "abc", 300),
     New MyRow(4, "text", 70),
     New MyRow(5, "def", 100),
     New MyRow(7, "qwerty", 90), ' etc. . . .
     }

But instead of typing these data manually, write a VBA macro in Excel that creates a string or file that includes this part:

     New MyRow(1, "abc", 300),
     New MyRow(4, "text", 70),
     New MyRow(5, "def", 100),
     New MyRow(7, "qwerty", 90), ' etc. . . .

Then paste the result of this macro to your VB code.

You can also consider other known methods, such as exporting from Excel to CSV, XML or JSON file, then using TextFieldParser, XDocument or JsonSerialiser classes.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VBCoder-7815 · 5 days ago

Hi Viorel-1

It took me a while to fully understand the above method, although i have the hang of it now after a couple of days trial/error and it is working great.

Thanks again for your help.

0 ·

Answer 2 · 2022-06-20T09:33:14.447Z

JackJJun-MSFT answered Jun 20, '22

@VBCoder-7815, Welcome to Microsoft Q&A, you could try to use OleDbConnection to convert your excel sheet to Datatable.

First, Please install the related component in your computer:

Microsoft Access Database Engine 2010 Redistributable

Second, Please try to change platform from AnyCpu to x64.

Third, you could try the following code to convert excel sheet to datatable and show it in datagirdview.

Note: Since I could not show the code in my answer in code format, I used picture to replace it.

Result:

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

image.png (63.2 KiB)

image.png (25.3 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-06-21T01:42:52.607Z

VBCoder-7815 answered Jun 21, '22 VBCoder-7815 commented 5 days ago

Viorel-1 and JackJJun-MFST thank you for the quick responses.

At this stage i have tested the method provided by JackJJun-MFST and managed to import my spreadsheet into a datatable for display in a data grid view...a column was then used to populate a combobox.

Would this method require the OleDbConnection to be used each time the application is restarted, or can the datatable be permanently stored in memory so the values can be read from different subroutines within the application?

I will also test the method proposed by Viorel-1 as i can see how this is directly storing the values.

Best Regards

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JackJJun-MSFT · Jun 21 at 09:44 AM

@VBCoder-7815, thanks for the feedback,

Would this method require the OleDbConnection to be used each time the application is restarted

Yes

or can the datatable be permanently stored in memory so the values can be read from different subroutines within the application?

It is hard to store the datatable in memory permanently. As usual, the memory will be released when you close the app.

0 ·

VBCoder-7815 · 5 days ago

Thanks for confirming an OleDdConnection would be required each time the application is restarted.

I have decided to use the method provided by Viorel-1 as this will allow me to permanently store the records in the application.

The datatable method gave me an introduction to another area of VB.net which i had not used, so thanks again for the instructions.

0 ·

question