How to return IEnumerable datatable in VB.NET

Question

I am trying to convert C# code to VB.Net but unable to find solution yield return equivalent of C# in Vb.NET

I am iterating over the datatable. One approach which I have found is to use list to iterate over the Datatable rows. But again it requires to convert the list to datatable. Hence what approach is required to yield return the datatable in VB.NET?

Here is VB.NET my Code:

Public Function GetFileData(ByVal sourceFileFullName As String, ByVal dt1 As System.Data.DataTable, ByVal RowCount As Integer) As IEnumerable(Of System.Data.DataTable)

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand


    con.ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings("con1").ConnectionString)

    Dim chunkRowCount As Integer = 0
    Dim Row As String

    Using sr As StreamReader = New StreamReader(sourceFileFullName)

        While Not (Row = sr.ReadLine()) = ""

            If Not RowCount = 0 Then
                chunkRowCount = chunkRowCount + 1
                    //var chunkDataTable = ; //Code for filling datatable or whatever  
                dt1.Rows.Add()

                Dim i As Integer = 0
                Dim Cell As String
                    For Each Cell  in Row.Split(',')

                    If (String.IsNullOrEmpty(Cell)) Then

                        dt1.Rows(dt1.Rows.Count - 1)(i) = DBNull.Value
                        i = i + 1

                    ElseIf Cell = "00.00.0000" Then

                        dt1.Rows(dt1.Rows.Count - 1)(i) = DBNull.Value
                        i = i + 1

                    Else

                        dt1.Rows(dt1.Rows.Count - 1)(i) = Cell
                        i = i + 1
                    End If
                Next
            End If

            RowCount = RowCount + 1

            If chunkRowCount = 10000 Then

                chunkRowCount = 0
                Yield return dt1
                dt1.Clear()
            End If

        End While

    End Using

    If dt1.Rows.Count > 0 Then
        yield return dt1

    End If
End Function

C# Code:

  public static IEnumerable<System.Data.DataTable> GetFileData(string sourceFileFullName, System.Data.DataTable dt1, int RowCount)
    {
        var con = ConfigurationManager.ConnectionStrings["con1"].ConnectionString.ToString();
        var connection = new SqlConnection(con);

        int chunkRowCount = 0;

        string Row;

        using (var sr = new StreamReader(sourceFileFullName))
        {

            //Read and display lines from the file until the end of the file is reached.                
            while ((Row = sr.ReadLine()) != null)
            {
                if (RowCount != 0)
                {
                    chunkRowCount++;
                    //var chunkDataTable = ; //Code for filling datatable or whatever  
                    dt1.Rows.Add();

                    int i = 0;

                    foreach (string Cell in Row.Split(','))
                    {
                        if (String.IsNullOrEmpty(Cell))
                        {
                            dt1.Rows[dt1.Rows.Count - 1][i] = DBNull.Value;
                            i = i + 1;
                        }
                        else if (Cell == "00.00.0000")
                        {
                            dt1.Rows[dt1.Rows.Count - 1][i] = DBNull.Value;
                            i = i + 1;
                        }
                        else
                        {
                            dt1.Rows[dt1.Rows.Count - 1][i] = Cell;
                            i = i + 1;
                        }
                    }

                }
                RowCount = RowCount + 1;

                if (chunkRowCount == 10000)
                {
                    chunkRowCount = 0;
                    yield return dt1;
                    dt1.Clear(); // = null;
                }

            } //end while


        }

        //return last set of data which less then chunk size
        if (dt1.Rows.Count > 0)
            yield return dt1;
    }

Possible duplicate of [What is the equivalent syntax in VB.NET for "yield return"?](http://stackoverflow.com/questions/2912851/what-is-the-equivalent-syntax-in-vb-net-for-yield-return) — sujith karivelil, Jan 04 '17 at 06:16
What are you trying to do? The code you posted tries to return the same table multiple times. A table that is *already* available to the caller, since it's the caller that provided it as a parameter. Why return anything in this case? If you have to return something, just return the table as is — Panagiotis Kanavos, Jan 04 '17 at 08:49
From Matteo Maqrciano: You can use the [Iterator](https://msdn.microsoft.com/en-us/library/hh156569.aspx) modifier in the function declaration to be able to use Yield in VB.NET — Panagiotis Kanavos, Jan 04 '17 at 09:59
@PanagiotisKanavos, I was just trying to read the data from CSV in chunk to avoid MemoryException Issue...For every Iteration of Datatable, it fetches the recordsets in set of Chunk and returns the datatable — rahul16590, Jan 04 '17 at 13:03
That's not what this code does. It returns the same table over and over. Also, using ADO.NET to read a CSV is the most expensive way possible. — Panagiotis Kanavos, Jan 04 '17 at 13:07
@PanagiotisKanavos...This function gets called in for each loop of another function which contains bulk insertion of the datatable for every call...now its working fine at my end...I can Insert 1 millions rows in approx. 45 seconds — rahul16590, Jan 04 '17 at 13:12

score 1 · Accepted Answer · answered Jan 04 '17 at 08:45

1

You can use the Iterator modifier in the function declaration to be able to use Yield in VB.NET

answered Jan 04 '17 at 08:45

Matteo Marciano - MSCP

419
2
5

The method is already an iterator but it just doesn't make sense. The method returns the same table it received as a parameter, over and over. Anyway, this should be a comment, not an answer – Panagiotis Kanavos Jan 04 '17 at 08:51
The VB version **IS NOT** an iterator function in the same way as the C# is. Plus, I don't have enough reputation to add comments to anything but my answers. Cheers – Matteo Marciano - MSCP Jan 04 '17 at 09:46
If you don't have the rep, wait until you do. The rule is there for a reason. New users should get acquainted with the site first. SO is a Q&A site, not a discussion forum. If something can't be a good A, it shouldn't be an A at all. In fact, this answers *should* be deleted, it's even one of the options when flagging an answer for deletion. Otherwise, they'll soon get downvoted – Panagiotis Kanavos Jan 04 '17 at 09:49
Moreover, as you noticed in the Question's comments, what the OP asks is a duplicate. It's better to *close* duplicate questions than answer them, because this pollutes the site and makes finding a good answer *harder*. In this case, there are multiple good answers already, but which do you chose as a duplicate? – Panagiotis Kanavos Jan 04 '17 at 09:52
First paragraph of the Q was asking about the VB equivalent of C#'s yield operator. If the original C# code works, using VB-equivalent iterator will also work. So my answer **is** an answer. Therefore, mine was not a comment, I give you a point on waiting to comment until I get enough reputation. Didn't notice the comment when I first answered. Thanks for the precious piece of advice. – Matteo Marciano - MSCP Jan 04 '17 at 09:58
@MatteoMarciano-MSCP...correct I just need to add Iterator keyword to make this function as Iterator...and to remove return keyword as Yield Suffices the requirement...thanks – rahul16590 Jan 04 '17 at 12:58

How to return IEnumerable datatable in VB.NET

1 Answers1