IRohitable | .NET, SQL Server, Ruby on Rails and everything related to Software

How to add auto-incremented column in DataTable at any position?

by Rohit 18. November 2014 22:53

In this post I will show you:

  1. How to add a new column at a certain position in a DataTable?
  2. How to make it an auto-incremented column?

 using ASP.NET/C# with SQL Server.

Almost always, we have a DataTable which gets populated by stored procedure results. At times we want to add an auto-incremented column from code-behind when it is not possible to alter stored procedure.

Let us create a sample table with the name: MonthData

CREATE TABLE [dbo].[MonthData](
	[Name] [varchar](6) NULL,
	[Year] [int] NULL,
	[Month] [int] NULL,
	[Value] [int] NULL,
	[Code] [int] NULL
) ON [PRIMARY]

 

Insert some rows in this table:

INSERT [dbo].[MonthData] ([Name], [Year], [Month], [Value], [Code]) VALUES (N'John', 2001, 5, 235, 9909)

INSERT [dbo].[MonthData] ([Name], [Year], [Month], [Value], [Code]) VALUES (N'Samson', 2003, 6, 1299, 989)

INSERT [dbo].[MonthData] ([Name], [Year], [Month], [Value], [Code]) VALUES (N'Alex', 2002, 3, 4322, 7392)

 

Now create a new ASP.NET Web Form with the following HTML code:

<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" Font-Names="Calibri" Font-Size="Medium"></asp:GridView>
    </div>
    </form>
</body>

 

In the Page Load event, add this simple code:

protected void Page_Load(object sender, EventArgs e)
        {
            string ConnectionString = "Data Source=.;Initial Catalog=Learn;Integrated Security=True";
            string Query = "SELECT [Name], [Year], [Code] FROM [MonthData]";

            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(Query, connection);
                connection.Open();
                using (SqlDataAdapter a = new SqlDataAdapter(command))
                {
                    a.Fill(dt);
                }
                connection.Close();
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

Executing the above code gives you results something like this:

 

Now let us add a Row Id column in the DataTable. Also, I want to show it at position 0 i.e, first column. To do this, modify the code as below:

protected void Page_Load(object sender, EventArgs e)
        {
            string ConnectionString = "Data Source=.;Initial Catalog=Learn;Integrated Security=True";
            string Query = "SELECT [Name], [Year], [Code] FROM [MonthData]";

            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(Query, connection);
                connection.Open();
                using (SqlDataAdapter a = new SqlDataAdapter(command))
                {
                    DataColumn Col = dt.Columns.Add("RowId", typeof(int));
                    Col.SetOrdinal(0);
                    dt.Columns["RowId"].AutoIncrement = true;
                    dt.Columns["RowId"].AutoIncrementSeed = 1;
                    dt.Columns["RowId"].AutoIncrementStep = 1;
                    a.Fill(dt);
                }
                connection.Close();
            }
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

On executing this code you get:

In SQL Query I have intentionally not used Row_Number function to get Row ID because I want to illustrate how this auto-increment number can be generated from code-behind.

Tags: ,

.NET | ASP.NET | C#

5 ways Agile changes the work of a tester

by Ulf.Eriksson 1. November 2014 12:09

[This is a Guest Post by Ulf Eriksson, founder:ReQtest]

For testers who are used to working in a traditional way, jumping on the Agile bandwagon takes a bit of adjusting to do.

The transition to working on an Agile project will challenge you to redefine the way you perceive your role in the team and widen the scope of your contribution to the project.

The increased responsibility on the tester’s shoulders might be intimidating initially, but, like any other skill, practice makes perfect.

In this article I’ll go over the main differences in the tester’s role between working within a traditional model of developing, and the Agile model. I’ll also point out the ways how you can leverage on your existing testing skills to adapt more quickly to working in an Agile environment.

The fundamental differences between the traditional and Agile methods can be easily grouped into five areas:

  1. Requirements management;
  2. Test documentation;
  3. Automated testing;
  4. The tester’s role; and
  5. Levels of testing.

More...

Tags: , , ,

Agile | Testing

2 Password Managers that can simplify your life

by Rohit 20. July 2014 20:45

I don't know how you manage your passwords but I am finding it difficult to keep track of all my passwords. From often changing bank passwords to credit card PINs, it is really difficult to keep these details secure and accessible.

I came across two tools which can really simplify life:

(1) F-Secure Key

It is a light-weight tool which you just install on your PC or Smartphone and set one master password. This master password is used to encrypt the data file using AES-256. The data file is stored on your PC. You will ask there are several tools which perform similar tasks and are freely available. Yes, but what makes F-Secure Key different is that it can synchronize your protected data across devices like smartphones. This feature is not FREE and available with a minimal subscription fee. Once you create F-Secure account and subscribe to sync feature, your passwords can be accessed on your smartphone. F-Secure Key mobile app. is available FREE on Google Play Store.

F-Secure Key can also auto-fill your passwords when you open websites.

(2) AVG Vault

It is a mobile app which you can install on your Android/iOS phone to store passwords. To use AVG Vault, a 4-6 digit PIN is required which acts as a master password. Once you have created this PIN, it is used to encrypt your data and sync the encrypted backup to either Dropbox or Google Drive.

One cool feature of AVG Vault is to automatically capture face from front camera if repetitive failed attempts are made to login. This image is mailed to the registered email ID.

 

Both F-Secure and AVG Vault are trusted names in the area of data security.

Tags: , ,

Security | Technology

About Rohit Prakash

Software Craftsman and Technology Enthusiast (not a Guru).

Technical Reviewer of a book on open-source programming IDE.

My day job keeps me engaged with Microsoft Technologies (ASP.NET, C# and SQL Server) hence most of the posts are related to these technologies. I, however, love to play with other open-source technologies. I also have interest in IT Security and therefore you will find posts on Malware Analysis, Cryptography and Anti-Virus programs.

Few people have contacted me for Guest Posts. You will find these posts as well.

 

You can reach me at:

rohit [at] irohitable.com

-----------------------------------------

LinkedIn:

in.linkedin.com/in/rohitpkhare

Twitter:

@BuzzRohit

-----------------------------------------
Google+

Search with this name: Rohit Prakash

Month List

Protected by Copyscape Web Plagiarism Software