How to add an autonumber column in a DataGridView control that is populated with a DataTable in simple steps ?

Figure.1

The first column in Figure.1 displays sequential row numbers. I have achieved this output by using the following function.

private DataTable AutoNumberedTable(DataTable SourceTable)

{

DataTable ResultTable = new DataTable();

DataColumn AutoNumberColumn = new DataColumn();

AutoNumberColumn.ColumnName="S.No.";

AutoNumberColumn.DataType = typeof(int);

AutoNumberColumn.AutoIncrement = true;

AutoNumberColumn.AutoIncrementSeed = 1;

AutoNumberColumn.AutoIncrementStep = 1;

ResultTable.Columns.Add(AutoNumberColumn);

ResultTable.Merge(SourceTable);

return ResultTable;

}

Refer http://msdn.microsoft.com/en-us/library/yctw654b.aspx on How to: Create an Autonumber DataColumn

Explanation

The function receives a DataTable as parameter. It create a Data Table, ResultTable. Then add a Data Column,AutoNumberColumn with auto incrementing behaviour. Then the SourceTable is merged with the ResultTable. The Merge method will add the rows of the source table to the destimation table one by one. Consequently, autonumber column values are generated as sequential row numbers

Implementation

In a few minutes, we will create a Windows Forms Application to test the case. Create a Windows Form and add a DataGridView. Write the following code in the Load event of the form.

private void Form1_Load(object sender, EventArgs e)

{

using(SqlConnection Connection=new SqlConnection(this.ConnectionString))

{

using (SqlCommand Command= Connection.CreateCommand())

{

Command.CommandText = "SELECT * FROM [myTable]";

SqlDataAdapter dataAdapter=new SqlDataAdapter(Command);

DataTable dataTable = new DataTable();

dataAdapter.Fill(dataTable);

this.dataGridView1.DataSource = AutoNumberedTable(dataTable);

}

}

}

Then add the ConnectionString property given below and AutoNumberedTable function given above in the body of the form. Run the program. See what happens!

private string ConnectionString

{

get

{

SqlConnectionStringBuilder ConnectionBuilder = new SqlConnectionStringBuilder();

ConnectionBuilder.DataSource = "myDataSource";

ConnectionBuilder.InitialCatalog = "myDatabase";

ConnectionBuilder.UserID = "myUserID";

ConnectionBuilder.Password = "myPassword";

return ConnectionBuilder.ToString();

}

}

SqlConnectionStringBuilder provides a simple way to create and manage the contents of connection strings.

Refer http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(VS.80).aspx on SqlConnectionStringBuilder Class

Details of Sample Application Seen in Figure.1

Database : Pubs

Query : "SELECT au_fname + ' ' + au_lname as [Name of Author],City FROM AUTHORS"

Add the following namespaces on top.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Windows.Forms;

Happy Coding! Happy Dotneting!