CODEWITHSUNDEEP
c#sql-server-2008listview
Qwerty
Qwerty

Reputation: 55

How to update data faster in sql using c#

I want to update my database but I think my code takes a lot of time in doing it. It takes about 20secs or more in updating. Is it possible to make it faster? If so please help me.

This is my code:

for (int i = 0; i < listView1.Items.Count; i++)
        {
            if (listView1.Items[i].SubItems[13].Text.ToString() == ("ACTIVE") || listView1.Items[i].SubItems[13].Text.ToString() == ("Active"))
            {
                for (int x = 0; x < listView1.Items[i].SubItems.Count; x++)
                {
                string a = listView1.Items[i].SubItems[7].Text;
                TimeSpan time = Convert.ToDateTime(DateTime.Now.ToString("MMMM dd, yyyy")) - Convert.ToDateTime(a.ToString());
                int days = (int)time.TotalDays;
                listView1.Items[i].SubItems[11].Text = days.ToString() + " day(s)";
                Class1.ConnectToDB();
                Class1.sqlStatement = "Update tblhd set aging = '" + days.ToString() + " day(s)" + "'";
                Class1.dbcommand = new SqlCommand(Class1.sqlStatement, Class1.dbconnection);
                Class1.dbcommand.ExecuteReader();
                }
            }
            }

Upvotes: 2

Views: 154

Answers (4)

Kalpak Kitukale
Kalpak Kitukale

Reputation: 1

  1. do not open connection multiple time.
  2. use using statement for connection creation using (SqlConnection connection = Class1.ConnectToDB())
  3. and us sql with parameter or store procedures
  4. try to convert this day string into int so that you do not have to convert it every time
  5. use ExecuteNonQuery instead of ExecuteReader

Upvotes: 0

subi_speedrunner
subi_speedrunner

Reputation: 921

Each time your for loop is making a call to DB, which is not an efficient way to do this.

You can create a stored procedure which will make a single call to your DB.

Upvotes: 0

Yeldar Kurmangaliyev
Yeldar Kurmangaliyev

Reputation: 34234

Actually, it is hard to say what is your SQL request suggested to do.
- Why are you using database?
- What are you storing there?
- Why are you inserting 'day(s)' string into a database instead of days integer value?
- Why are you updating ALL rows every time?
- Why are you updating (and overwriting) the same rows every time?

Please, describe your model and scenario, so, we understand how you want it work like and help you.

For your information: now your algorithm sets all rows' aging value to the last ListView's row's days value. It overwrites previously stored and recently updated data and, thus, this for loop is absolutely useless.

Upvotes: 1

cyberj0g
cyberj0g

Reputation: 3797

It seems that you could do it with a single update statement:

UPDATE tblhd set aging=DATEDIFF(day, DateField, GETDATE())+" day(s)" WHERE ItemId=...

But it's generally not a good idea to store user-friendly labels like 'day(s)' in the database.

Upvotes: 1

Related Questions