Sunday, 2 December 2012

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
        void AddDataTableColumns()
        {
            DataColumn ID = new DataColumn("SerialNo", typeof(System.Int32));
            ID.AutoIncrement = true;
            ID.AutoIncrementSeed = 1000;
            ID.AutoIncrementStep = 16;

            DataColumn BrandName = new DataColumn("StudentName", typeof(System.String));
            BrandName.MaxLength = 100;

            DataColumn BrandImage = new DataColumn("Course");
            BrandImage.DataType = typeof(String);
            //typeof(System.Web.UI.WebControls.Image);
            brandsTable.Columns.Add(ID); brandsTable.Columns.Add(BrandName); brandsTable.Columns.Add(BrandImage);
           
        }
        void AddDataTableRows()
        {
            DataRow row1 = brandsTable.NewRow();
            row1[1] = "Bata";
            row1[2] = "C#"; 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);

        }

Step 3) Querying Datatable using LINQ

        public List<StudentInfo> GetStudentTable()
        {
            var studentquery = from st in brandsTable.AsEnumerable()
                               select new StudentInfo
                               {
                                   SerialNo = st.Field<System.Int32>(0),
                                   StudentName = st.Field<String>(1),
                                   Course = st.Field<String>(2)
                               };
            return studentquery.ToList();
        }
Step 4) Insert Rows into datatable using LINQ
        public bool AddRows(StudentInfo sInfo)
        {
           
          

            //brandsTable = (DataTable)HttpContext.Current.Session["tbl"];
            DataRow row =brandsTable.NewRow();
            row[1] = sInfo.StudentName;
            row[2] = sInfo.Course;
            brandsTable.Rows.Add(row);
                        return true;
        }

Step 5) Update Rows  in datatable using LINQ

        public bool UpdateRows(StudentInfo sInfo)
        {
            bool bRet = false;
                      DataRow row = brandsTable.AsEnumerable().SingleOrDefault(s => s.Field<Int32>(0) == sInfo.SerialNo);
            if (row != null)
            {
                row[1] = sInfo.StudentName;
                row[2] = sInfo.Course;
                bRet = true;
                      }
            return bRet;
        }
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.