Sunday, 2 December 2012

Datatable and LINQ,CRUD Operations in DATATABLE using LINQ VB.NET

Datatable and LINQ,CRUD Operations in DATATABLE using LINQ C#

DataTable and LINQ C#
Querying Datatable using LINQ
CRUD Operations on Datatable using LINQ

Step 2)  Add Datatable Programatically
Private Sub AddDataTableColumns()
    Dim ID As New DataColumn("SerialNo", GetType(System.Int32))
    ID.AutoIncrement = True
    ID.AutoIncrementSeed = 1000
    ID.AutoIncrementStep = 16

    Dim BrandName As New DataColumn("StudentName", GetType(System.String))
    BrandName.MaxLength = 100

    Dim BrandImage As New DataColumn("Course")
    BrandImage.DataType = GetType([String])
    'typeof(System.Web.UI.WebControls.Image);
    brandsTable.Columns.Add(ID)
    brandsTable.Columns.Add(BrandName)
    brandsTable.Columns.Add(BrandImage)

End Sub
Private Sub AddDataTableRows()
    Dim row1 As DataRow = brandsTable.NewRow()
    row1(1) = "Bata"
    row1(2) = "VB.NET"
    brandsTable.Rows.Add(row1)
    row1 = brandsTable.NewRow()
    row1(1) = "'Brands"
    row1(2) = "ASP.NET"
    brandsTable.Rows.Add(row1)
    row1 = brandsTable.NewRow()
    row1(1) = "MODULES"
    row1(2) = "JAVA"
    brandsTable.Rows.Add(row1)

End Sub
Step 3) Querying Datatable using LINQ


        Public Function GetStudentTable() As List<StudentInfo>

            var studentquery = from st Function brandsTable.AsEnumerable() As in
                                   SerialNo = st.Field<System.Int32>(0),
                                   StudentName = st.Field<String>(1),
                                   Course = st.Field<String>(2)
            End Function

            Return studentquery.ToList()
        End Function

Step 4) Insert Rows into datatable using LINQ

        Public Function AddRows(ByVal sInfo As StudentInfo) As Boolean
             Dim row As DataRow = brandsTable.NewRow()
            row(1) = sInfo.StudentName
            row(2) = sInfo.Course
            brandsTable.Rows.Add(row)
                        Return True
        End Function
 
Step 5) Update Rows  in datatable using LINQ

 
       Public Function UpdateRows(ByVal sInfo As StudentInfo) As Boolean
            Dim bRet As Boolean =  False
                      Dim row As DataRow =  brandsTable.AsEnumerable().SingleOrDefault(s  = > s.Field<Int32>(0)  =  sInfo.SerialNo)
            If Not row Is Nothing Then
                row(1) = sInfo.StudentName
                row(2) = sInfo.Course
                bRet = True
            End If
            Return bRet
       End Function
Step 6) Delete Records in Datatable using LINQ
        public bool DeleteRows(StudentInfo sInfo)
        {
            bool bRet = false;
                        DataRow row = brandsTable.AsEnumerable().SingleOrDefault(s => s.Field<Int32>(0) == sInfo.SerialNo);
            if (row != null)
            {
                brandsTable.Rows.Remove(row);
                            bRet = true;
            }
            return bRet;
        }
The above example explains how to add Datatable Programatically, exploit datatable using LINQ technology.  Because DataRows,DataColumns in are not implemented IEnumerable interface. So needs to convert every row,column into AsEnumerable first, before querying Datatable using LINQ.