How to alter DataTable to add new Column -C#, asp.net

In this tutorial i will show how to manipulate the DataTable to append with new column.
In this example i will retrieve data from database using SqlDataAdapter, you can refer this tutorial to know how to using SqlDataAdapter.
After fill DataTable with data, the program will append the column of the DataTable.

Lets check on the example below.

The Database Table "Users"

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
    [Userid] [varchar](50) NOT NULL,
    [UserEmail] [varchar](50) NOT NULL,
    [DateTimeCreated] [datetime] NOT NULL,
    [CreatedBy] [varchar](50) NOT NULL,
    [DateTimeModified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Users] ([Userid], [UserEmail], [DateTimeCreated], [CreatedBy], [DateTimeModified], [ModifiedBy]) VALUES (N'developers', N'developersnote@gmail.com', CAST(0x0000A284014950B0 AS DateTime), N'SYS', NULL, NULL)
INSERT [dbo].[Users] ([Userid], [UserEmail], [DateTimeCreated], [CreatedBy], [DateTimeModified], [ModifiedBy]) VALUES (N'noted', N'developersnote@gmail.com', CAST(0x0000A284014950B0 AS DateTime), N'SYS', NULL, NULL)


The ASPX Page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataTableExample.aspx.cs" Inherits="BlogExample.DataTableExample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
     <div>
        <h1>
            Data From Database without modified
        </h1>
        <asp:GridView ID="GridView1" runat="server" BackColor="White"
            BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3"
            ForeColor="Black" GridLines="Vertical">
            <AlternatingRowStyle BackColor="#CCCCCC" />
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#808080" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#383838" />
        </asp:GridView>
    </div>
    <div>
        <h1>
            Data From Database append with new column id
        </h1>
        <asp:GridView ID="GridView2" runat="server" BackColor="White"
            BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
            GridLines="Vertical">
            <AlternatingRowStyle BackColor="#DCDCDC" />
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#0000A9" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#000065" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>


The Code Behind

protected void Page_Load(object sender, EventArgs e)
        {
            BindGridView1();
            BindGridView2();
        }

        private void BindGridView1()
        {
            DataTable tableUsers = getDataTableUsers();
            GridView1.DataSource = tableUsers;
            GridView1.DataBind();
        }

        private void BindGridView2()
        {
            DataTable tableUsers = getDataTableUsers();
            //create new datatable
            DataTable newTableUsers = new DataTable();
            //create column id
            newTableUsers.Columns.Add("ID",typeof(string));

            //copy table get from database to new table with append id column
            newTableUsers = copyTable(tableUsers,newTableUsers);

            GridView2.DataSource = newTableUsers;
            GridView2.DataBind();

        }

        public DataTable copyTable(DataTable TableToCopy, DataTable tableAfterCopy)
        {
            //copy table column first
            foreach(DataColumn col in TableToCopy.Columns)
            {
                tableAfterCopy.Columns.Add(col.ColumnName,col.DataType);
            }

            //copy table data
            int index =1;
            for(int i = 0; i < TableToCopy.Rows.Count;i++)
            {
                DataRow row = tableAfterCopy.NewRow();
                foreach(DataColumn col in TableToCopy.Columns)
                {
                    row[col.ColumnName] = TableToCopy.Rows[i][col.ColumnName];
                }
                //add value to the column ID
                row["ID"] = index.ToString();

                //add row into new datatable
                tableAfterCopy.Rows.Add(row);

                //increase value index
                index++;
            }
            return tableAfterCopy;
        }

        public DataTable getDataTableUsers()
        {
            DataTable usersTable = new DataTable();
            using (SqlConnection dbConn = new SqlConnection("Data Source=<Data source location>;Initial Catalog=<Catalog name>;Integrated Security=True"))
            {
                using (SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM Users",dbConn))
                {
                    dbConn.Open();
                    dbAdapter.Fill(usersTable);
                }
            }

            return usersTable;
        }

The Output




By
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Popular posts from this blog

Example to disable save as certain file type in SSRS Report Viewer

Phone book application example - Java

Modal Popup Message Box ASP.NEt C# Example