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.
By Mohd Zulkamal
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 =)
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 Mohd Zulkamal
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 =)
