Click here to Skip to main content
15,613,470 members
Please Sign up or sign in to vote.
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
Updated 1-Jul-21 0:58am
v3
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 ?

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?
 
Share this answer
 
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();
    }
}
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900