how to find number of rows inserted

Question

4.00/5 (1 vote)

See more:

how to find total number of rows inserted in result set

HTML

    protected void Button1_Click(object sender, EventArgs e)
    {
       if(TextBox.Text !=String.Empty)
        {
            foreach (GridViewRow row in GridView1.Rows)
            {
                string textBoxText = ((TextBox)row.FindControl("TextBox1")).Text;
                cmd = new SqlCommand("Insert into Text(number)values(@number)", con);
                cmd.Parameters.Add(new SqlParameter("@number", textBoxText));
                con.Open();
                int a = cmd.ExecuteNonQuery();
                con.Close();
                ShowAlertMessage("inserted");
                TextBox.Text = "";


            }

        }
else
        {
             ShowAlertMessage("Enter the value");

        }
    }

Posted 12-Dec-14 20:45pm

Rajalakshmi Ravi

Add a Solution

Comments

King Fisher 13-Dec-14 2:53am

Use Increment Operator within that loop

Maciej Los 13-Dec-14 4:23am

What's database?

Rajalakshmi Ravi 14-Dec-14 23:39pm

sry for delay of response i am using sql server 2008

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-12T22:39:00

I see several issues, like SQLserver stop responding or application may hang-up.

Rather then inserting value one-by-one, use stored procedure (SP)[^], like:

SQL

CREATE PROCEDURE usp_AddValue
    @val INT
AS
BEGIN
   INSERT INTO TableName (FieldName)
   VALUES(@val)
   --return number of rows affected
   RETURN @@ROWCOUNT
END

Why to use SP? Becasue of SQL Injection[^].
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
SQL Security: New SQL Truncation Attacks And How To Avoid Them[^]
SQL Injection and how to avoid it[^]
@@ROWCOUNT (T-SQL)[^]
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to: Execute a Stored Procedure that Returns Rows[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]

I have no idea why do you loop through the collection of rows. Sounds, like you wanted to update data instead of adding them. In that case, use SP:

SQL

CREATE PROCEDURE usp_UpdateAll
    @val INT
AS
BEGIN
   UPDATE TableName SET FieldName = @val
   --return number of rows affected
   RETURN @@ROWCOUNT
END

If you would like to update single record:

SQL

CREATE PROCEDURE usp_UpdateSingle
    @id INT,
    @val INT
AS
BEGIN
   UPDATE TableName SET FieldName = @val
   WHERE Key = @id
   --return number of rows affected
   RETURN @@ROWCOUNT
END

Good luck!

DamithSL · Accepted Answer · 2014-12-12T20:50:00

Solution 1

C#

int insertCount =0;
con.Open();
foreach (GridViewRow row in GridView1.Rows)
{
    string textBoxText = ((TextBox)row.FindControl("TextBox1")).Text;
    cmd = new SqlCommand("Insert into Text(number)values(@number)", con);
    cmd.Parameters.AddWithValue("@number", textBoxText);
    int a = cmd.ExecuteNonQuery();
    if(a>0)
       insertCount++;
    TextBox.Text = "";

}
con.Close();
ShowAlertMessage(string.Format("{0} records inserted", insertCount));

Posted 12-Dec-14 20:50pm

DamithSL

Updated 12-Dec-14 20:53pm

v3

Comments

Maciej Los 13-Dec-14 4:41am

My vote of 3. Although your code is helpful, but i see several reasons to not use it.
Please, see my answer.

DamithSL 13-Dec-14 4:51am

OP using Parameters, it is safe.
I have used the same connection for all the inserts and it will prevent multiple connections.
I don't see considerable improvement in your code other than additional things like update which OP newer ask the question.
even for using your SP, OP have to insert one by one, you are not sending array or list of items at ones.

Maciej Los 13-Dec-14 5:25am

OK, you can test it on your database. Try to add via Web (in loop) over 10K records. Let me know about application stability... Does it hanged up or not?
That's what i'm talking about.

DamithSL 13-Dec-14 5:30am

Issue is not with inserting but loading 10k records to list view and displaying all of them!

Maciej Los 13-Dec-14 5:35am

With inserting and loading...
If you don't believe me, try... Even if the sql server is a local server, application may hang up.
Respectfully,
Maciej

DamithSL 13-Dec-14 5:47am

Macej, we don't know what exactly the background of the OPs requirements, it may be 1k records. normally we won't load many records to list view. for example OP may be adding new records to list and saving them ones.
I agreed that the SP give you some performance gain but for inserting record by record using SP will not give you considerable performance. May be for the same requirement there may be better alternative but we don't know exactly the context, only provided piece of code we have.
Thanks for your vote and the thoughts I really appreciate you.

Maciej Los 13-Dec-14 6:02am

Finally, i decided to re-vote (4). You're right, OP did not provide enough information and we don't know how many records He wants to insert (or update). For several dozen of records, your solution is perfect with my comments about possible issues in case of hudreds of records.
Thank you for your cooperation, Damith ;)
Cheers,
Maciej