To retrieve data and update from in a single loop ?

Question

1.00/5 (2 votes)

See more:

C#

Perform data retieving and updating part in a single loop
public string single(string Variable, int qty, string Variablename)
{
    List<string> data = new List<string>();
    string date = DateTime.Now.ToString("yy:MM:dd:HH");
    string dt = date.Replace(":", "");
    string command1;
    //string HR = string.Join(";", data.ToArray());

    int items = 10;
    string[] newdate = new string[items];
    for (int i = 0; i < items; i++)
    {
        newdate[i] = dt;
    }
    string dates = string.Join(";", newdate.ToArray());
     string hr = string.Format("{0}person", Variablename);
    string Dt = string.Format("{0}score", Variablename);

    using (SqlConnection sqlcon = new SqlConnection(@""))
    {
        string query = string.Format("select top({0})SUBSTRING(Name,1,17),Grade from students Where  Grade= '{1}' and Progress IS NULL", qty, Variable);

        sqlcon.Open();
        using (SqlCommand sqlCommand = new SqlCommand(query, sqlcon))
        {
            // sqlcon.Open();
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            var dataReader = sqlDataReader;

            if (dataReader.Read())
            {
                data.Add(Convert.ToString(dataReader[0]));

            }
            dataReader.Close();
        }

        string HR = string.Join(";", data.ToArray());

        using (SqlCommand cmd = sqlcon.CreateCommand())
        {
            for (int i = 0; i < data.Count; i++)
            {
                string mo = data[i] + dt;
                // data[i] += dt;
                cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mo + "' where Name like '" + data[i] + "%' ";
                data[i] += dt;
                cmd.ExecuteNonQuery();
            }
            string mono = string.Join(";", data.ToArray());
            command1("Name of students.....");
    }
    return command1;
}

What I have tried:

I am trying to do everything in one loop .

C#

var dataReader = sqlDataReader;

if(dataReader.HasRows)
{
    data.Add(Convert.ToString(dataReader[0]));
    dataReader.Close();
    cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mo + "' where Name like '" + data[i] + "%' ";
    data[i] += dt;
    cmd.ExecuteNonQuery();

}

Posted 30-Jun-21 23:41pm

Member 15212425

Updated 1-Jul-21 0:58am

v3

Add a Solution

Comments

Richard MacCutchan 1-Jul-21 5:51am

What is the qustion?

Member 15212425 1-Jul-21 5:59am

data retrieving
while(dataReader.Read())
{
data.Add(Convert.ToString(dataReader[0]));

}
updating and storing that data to another array
for (int i = 0; i < data.Count; i++)
{
string mo = data[i] + dt;
// data[i] += dt;
cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mo + "' where Name like '" + data[i] + "%' ";
data[i] += dt;
cmd.ExecuteNonQuery();
}
string mono = string.Join(";", data.ToArray());

I want to perform both task in a single loop .

Richard MacCutchan 1-Jul-21 6:17am

"I want to perform both task in a single loop ."
So what is the problem in doing that?

Member 15212425 1-Jul-21 6:28am

if(dataReader.HasRows)
{
data.Add(Convert.ToString(dataReader[0]));
dataReader.Close();
cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mono + "' where Name like '" + data[i] + "%' ";
data[i] += dt;
cmd.ExecuteNonQuery();
} i tried to do this way . bt its not working

Member 15212425 1-Jul-21 6:47am

can u pls explain how to do that ?

Richard MacCutchan 1-Jul-21 6:54am

"i tried to do this way . bt its not working"
What is not working? Please explain, with proper technical details, exactly what is happening. We cannot guess what you see on your screen.

Member 15212425 1-Jul-21 7:30am

I am trying to do like this to optimize my code . i dont want to use 2 loops . sorry i am new to coding .
var dataReader = sqlDataReader;
if(dataReader.HasRows)
{
data.Add(Convert.ToString(dataReader[0]));
for (int i = 0; i < data.Count; i++)
{
string mo = data[i] + dt;
data[i] += dt;
cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mo + "' where name like '" + data[i] + "%' ";
data[i] += dt;
cmd.ExecuteNonQuery();
}
}
dataReader.Close();
string HR = string.Join(";", data.ToArray());

Richard Deeming 1-Jul-21 6:07am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Member 15212425 1-Jul-21 7:54am

thanks for the note . can u help me with ? here i am using 2 loops .. i want to do evrything in single loop . how can i do that ?
var dataReader = sqlDataReader;
if(dataReader.HasRows)
{
data.Add(Convert.ToString(dataReader[0]));
for (int i = 0; i < data.Count; i++)
{
string mo = data[i] + dt;
data[i] += dt;
cmd.CommandText = "update students SET Progress = 'Printed', Name= '" + mo + "' where name like '" + data[i] + "%' ";
data[i] += dt;
cmd.ExecuteNonQuery();
}
}
dataReader.Close();
string HR = string.Join(";", data.ToArray());

Richard Deeming 1-Jul-21 7:59am

Since you clearly haven't read either solution posted here, nobody can help you.

PIEBALDconsult 1-Jul-21 10:12am

Always think in layers, do not put all that code in one place.

Member 15212425 3-Jul-21 0:12am

i was doing in layers. but i am trying to optimize my code.

PIEBALDconsult 3-Jul-21 1:16am

Ummm, yeah, don't do that.

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)

OriginalGriff · Answer 1 · 2021-07-01T00:48:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Richard Deeming · Answer 2 · 2021-07-01T00:58:00

Issue a single command to update and return the rows. Wrap the command in a transaction, and commit the transaction when you have processed the returned rows.

C#

const string Query = @"
DECLARE @Data TABLE (ID int NOT NULL);
INSERT INTO @Data (ID) SELECT TOP (@RowCount) ID FROM students WHERE Grade = @Grade And Progress Is Null;

UPDATE
    S
SET
    Progress = 'Printed',
    Name = Name + @dt
FROM
    students As S
    INNER JOIN @Data As D ON D.ID = S.ID
;

SELECT
    Substring(Name, 1, 17) As Name
FROM
    students As S
    INNER JOIN @Data As D ON D.ID = S.ID
;";

using (var conn = new SqlConnection(...))
{
    conn.Open();
    
    using (var transaction = conn.BeginTransaction())
    using (var cmd = new SqlCommand(Query, conn) { Transaction = transaction })
    {
        cmd.Parameters.AddWithValue("@RowCount", qty);
        cmd.Parameters.AddWithValue("@Grade", Variable);
        cmd.Parameters.AddWithValue("@dt", dt);
        
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                string name = reader.GetString(0);
                ... PROCESS THE RECORD HERE ...
            }
        }
        
        transaction.Commit();
    }
}

To retrieve data and update from in a single loop ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0