Has C# a cursor data type

BenTam 1,761

Dear All,

I am converting a Visual FoxPro (VFP) program to a C# program. In VFP there is a data type "Cursor" (with columns and rows) to store data retrieved from SQL Server. Has C# a similar data type?

For example, the following screen capture is a "Date Schedule form" from my old VFP program. Data enclosed by the red line is a single select from the SQL server to a VFP cursor. Then I extract the morning, afternoon, and evening courses from the cursor and put them in the slots. That will minimize the traffic between the client and the SQL server.

VFP DateSehedule

Jiale Xue - MSFT 49,051 Reputation points Microsoft Vendor

2023-07-28T02:28:40.42+00:00

Is there any updated?
BenTam 1,761 Reputation points

2023-07-30T03:37:00.5+00:00

Hi @Jiale Xue - MSFT

Do you mean the question I asked before? Please look at the page where you left comments.

4 answers

Fabio Caruso 90 Reputation points

2023-07-31T14:25:47+00:00
Hi,

if You want a graphical interface like Cursor of VFP, in C# the best choice is DataGrid if You are using WPF (Windows Presentation Foundation), or DataGridView if You are using Windows Forms.

Developing your C# code, the steps to join a DataGrid graphical object to your SQL Database are as follows:

In Visual Studio, create a new WPF Project, and drag a new Datagrid graphical object from Visual Studio Toolbox;

Open a Sql connection to your database;

Create an istance of SqlDataAdapter class to issue your SELECT Sql command and retrieve your data from the database;

Create a new istance of DataTable class, and issue a fill command to fill the datatable with data retrieved from the DataAdapter;

Set the DataSource property of your DataGrid to that Datatable, so all your data will be show in your new DataGrid.

Enjoy.

Also make the consideration that the communication between the DataGrid and your Sql Database can be made in Two-Way mode...
Please sign in to rate this answer.

1 person found this answer helpful.
BenTam 1,761 Reputation points

2023-08-01T02:51:21.63+00:00

Hi @Fabio Caruso

Thanks for your reply. Is there a simple way to divide the courses into 3 slots (morning, afternoon, and evening) as shown in the VFP screen capture?

BenTam 1,761 Reputation points

2023-08-01T03:00:52.0733333+00:00

.......

Fabio Caruso 90 Reputation points

2023-08-01T18:33:52.9033333+00:00

Surely, using a WPF project, You can easily create and organise your page like You want. Using a <StackPanel> control with an horizontal orientation, You can place three DataGrids in horizontal, next to each other.

See that: https://learn.microsoft.com/en-us/dotnet/api/system.windows.controls.stackpanel.orientation?view=windowsdesktop-7.0

In the XAML window, You will have:

<Grid> <StackPanel Orientation="Horizontal"> <DataGrid x:Name="DG_morning"></DataGrid> <DataGrid x:Name="DG_afternoon"></DataGrid> <DataGrid x:Name="DG_evening"></DataGrid> </StackPanel> </Grid>

and in your code, You can have three Datatables, in each of wich You can put the results of the three SQL "SELECT" commands (to choose morning, afternoon and evening results), filling every datatable with SqlDataAdapter.

At the end, You write:

DG_morning.ItemSource = datatable1.AsDataView(); DG_afternoon.ItemSource = datatable2.AsDataView(); DG_evening.ItemSource = datatable3.AsDataView();

BenTam 1,761 Reputation points

2023-08-03T07:35:03.0133333+00:00

Hi @Fabio Caruso

Do you mean that I have to use 3 SQL commands to fill the controls? If yes, the traffic is large.

Fabio Caruso 90 Reputation points

2023-08-03T18:14:07.5466667+00:00

Yes, with your SqlDataAdapter, performing the first SELECT operation, You can fill the first datatable with:

dataAdapter.Fill(datatable1);

and so on, also for the second and third SELECT commands.

Don't care about network traffic, because with one SELECT, You are downloading, in one shot, the data to be displayed in the datagrid only. Search and select operation is performed on the Sql Server: the results travel on the net...

The three filled tables are now in your RAM: nothing more is required from (or sent to) the server.

In your code:

string connectionString = "Server=...; Database=...."; SqlConnection connection = new SqlConnection(connectionString); SqlDataAdapter da = new SqlDataAdapter(); DataTable datatable1 = new DataTable(); DataTable datatable2 = new DataTable(); DataTable datatable3 = new DataTable(); da.SelectCommand = new SqlCommand("SELECT FIELD1, FIELD2... FROM ....;", connection); da.Fill(datatable1); da.SelectCommand = new SqlCommand("SELECT FIELD3, FIELD4... FROM ....;", connection); da.Fill(datatable2); da.SelectCommand = new SqlCommand("SELECT FIELDS... FROM ...;", connection); da.Fill(datatable3); DG_morning.ItemsSource = datatable1.AsDataView(); DG_afternoon.ItemsSource = datatable2.AsDataView(); DG_evening.ItemsSource = datatable3.AsDataView();

Fabio Caruso 90 Reputation points

2023-08-03T19:14:16.0533333+00:00

And at last, if You want the three datagrids occupy the same space, in your XAML code, You can replace the StackPanel, with a (smarter) Grid:

<Grid> <Grid.ColumnDefinitions> <ColumnDefinition Width="*" /> <ColumnDefinition Width="*" /> <ColumnDefinition Width="*" /> </Grid.ColumnDefinitions> <DataGrid x:Name="DG_morning" Grid.Row="1" Grid.Column="0"></DataGrid> <DataGrid x:Name="DG_afternoon" Grid.Row="1" Grid.Column="1"></DataGrid> <DataGrid x:Name="DG_evening" Grid.Row="1" Grid.Column="2"></DataGrid> </Grid>

BenTam 1,761 Reputation points

2023-08-04T03:08:26.91+00:00

Hi @Fabio Caruso

In VFP I can select one time, from the SQL server, and put the result in a cursor. Then I can perform three selects, in the local machine, to populate the data into the morning, afternoon, and evening slots respectively. Your method performs three selects from the SQL server. It doesn't meet my requirement.

Fabio Caruso 90 Reputation points

2023-08-04T17:20:33.2833333+00:00

After selecting (for one time only) the records from SQL Server, if You want to simulate the three SELECT operations on the local machine, one solution could be to use SQLite to create a local in-memory database, and to copy the results of the first Sql SELECT to this new in-memory database, so You can operate with the next SELECTs on this one local in-memory SqLite database. Finally, You can pass your data to the three DataGrids.

Or another solution could be to create a class (a record) with your fields, for use it in a List<yourClass>. Then, after SELECT command, using a SqlDataReader You can add all the results in the List<yourClass>, on the which You can operate with LINQ queries (similar to sql) and You can select the records like You want.

BenTam 1,761 Reputation points

2023-08-05T04:08:01.82+00:00

Hi @Fabio Caruso

Thanks for your reply and suggestions.

Alessandro Bitetto 0 Reputation points

2025-02-26T09:20:23.21+00:00

@Fabio Caruso Buongiorno Fabio, grazie mille delle indicazioni intanto. Sto facendo delle prove con il tuo approccio e mi chiedevo: hai mai avuto problemi di performance quando i SELECT restituiscono molti record? Mi chiedevo se, in un'applicazione reale, potrebbe essere utile paginare i dati o usare un DataReader invece dei DataTable. Hai avuto esperienze dirette con scenari di grandi volumi di dati?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Sander van de Velde | MVP 35,246 Reputation points MVP

2023-07-23T12:24:39.9633333+00:00

Hello @BenTam-3003 ,

The cursors you used are a SQL Server database concept.

There is not much Sql Server documentation available about using them in programming languages.

This post about using cursors in Entity Framework seems helpful though.

MS Learn offers a number of free training modules regarding Entity Framework Core.

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.
Please sign in to rate this answer.
BenTam 1,761 Reputation points

2023-07-24T04:54:05.0933333+00:00

Hi @Sander van de Velde Thanks for your reply! I will study the link you provide.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Olaf Helper 46,196 Reputation points

2023-07-24T07:27:27.5566667+00:00

The closest to a cursor are DataSets.

https://learn.microsoft.com/en-us/dotnet/api/system.data.dataset?view=net-7.0
Please sign in to rate this answer.
BenTam 1,761 Reputation points

2023-07-27T04:59:52.7433333+00:00

.....

BenTam 1,761 Reputation points

2023-07-27T15:04:59.6666667+00:00

Hi @Olaf Helper

Thanks for your reply.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Bruce (SqlWork.com) 71,696 Reputation points

2023-08-01T21:31:05.9933333+00:00

VFP was designed as a database tool. It has data aware controls which support paging. C# does not have any intrinsic database or forms support.

C# currently has 3 popular form libraries, Winforms, WPF and Maui. These libraries are general purpose. They have lists/grids that bind to data collections, which can come from a database, but you code the database access separately. you will also need implement paging and supply a paging ui if you need paging as in your sample. the same is true for filtering and sorting.

you also can bind control to a model, but it is not database aware, you will create the crud operations in code.

you will probably use Entity Framework for database access. This is a completely different model than VFP, and will take some leaning. You other option is the ado library which is lower level.

There are 3rd party controls for your chosen form library which are more data aware. You will probably want a 3rd party grid. for example see:

https://www.telerik.com/products/wpf/overview.aspx
Please sign in to rate this answer.
BenTam 1,761 Reputation points

2023-08-03T07:05:18.1533333+00:00

Hi @Bruce (SqlWork.com)

Thanks for your answer!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Has C# a cursor data type

4 answers

Your answer