How do I update sets of duplicate values , based on a containing character

Question

0.00/5 (No votes)

See more:

I have a program to update sets of DISTINCT duplicates values, for given result set. The values in a set will be updated based on
1.the highest available int value in a mixed int/varchar column, for a related table.
2.whether they contain the character C at index 10

When i run the function that updates the values , they are not getting updated.

If my result set returns 2 sets of duplicates as follows:

ALID-HEA-CO-001
ALID-HEA-CO-001
ALID-HEA-CO-001
EMIL-MTR-CO-012
EMIL-MTR-CO-012

and in the related table my SP query returns the highest available int as 23 (from AG00023) then i want to update EACH DISTINCT duplicates set above as :

AG00024
AG00024
AG00024
AG00025
AG00025

As a test i can get the duplicates sets using the following console app :

C#

static void Main(string[] args) {
 	List<Broker> BrokerList = GetData();
	foreach(Broker b in BrokerList) {
		Console.WriteLine(b.Code);
	}
}
public static List<Broker> GetData()
        {          
            List<Broker> details = new List<Broker>();
            using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
            {
                conn.Open();
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand("getCOAgents", conn);
                cmd.CommandType = CommandType.StoredProcedure;               
                cmd.ExecuteNonQuery();
           
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    Broker broker = new Broker();
                    broker.Code = Convert.ToString(dr["agent_shortname"]);
                    details.Add(broker);
                }
                conn.Close();
            }
           return  details;
        }

What im stuck at is then looping through the result set, such that WHENEVER program flow encounters a DISTINCT set of duplicates it updates THAT set as illustrated above, then the next DISTINCT set, then the next one , until the whole result set is updated.

What I have tried:

Function to update the sets, and i am able to get the highest available int using SP spGetHighestAGCode. Any assistance on how i can make the while loop is not check for a change in duplicates sets properly, or something im misssing in the structure of the ActionResult's algorithm:

C#

public ActionResult NewAGAgentMapping()
           {
            Models.FileUpload.Mapping mapping = new Models.FileUpload.Mapping();
            try
            {
                
                List<Broker> gnbc = GetData();

                using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
                {
                   Models.FileUpload.Mapping mappinggg = new Models.FileUpload.Mapping();
                   SqlCommand cmd = new SqlCommand("spGetHighestAGCode", conn);
                   cmd.CommandType = CommandType.StoredProcedure;
                   SqlParameter param = new SqlParameter("@returnValue", SqlDbType.Int);
                   cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;
                   cmd.ExecuteNonQuery();

                   int Counter = Convert.ToInt32(cmd.Parameters["@returnValue"].Value);

                   int newCode = Counter + 1;

                    conn.Close();

                    mappinggg.Counter = "AG" + newCode.ToString("000000");

                  conn.Open();
                  SqlDataReader dr = cmd.ExecuteReader();            
                  conn.Close();
                  
                    //Update the Duplicates
                    var duplicateBrokers = gnbc.FindDuplicates(p => p.Code);
                    int n = duplicateBrokers.Count;

                    for (int i = 0; i < n; i++)
                    {
                        //bool isDuplicate = false;
                        for (int j = 0; j < i; j++)
                        {
                            while (duplicateBrokers[i] == duplicateBrokers[j])
                            {
                                foreach (var dupBroker in duplicateBrokers)
                                {
                                    using (SqlConnection conn1 = new SqlConnection(Helpers.DatabaseConnect))
                                    {
                                        Models.FileUpload.Mapping mappingg = new Models.FileUpload.Mapping();
                                        string query = "UPDATE BrokerTest SET  agent_shortname ='" + Convert.ToString(mappinggg.Counter) + "' WHERE agent_shortname='" + dupBroker + "'";
                                        SqlCommand command = new SqlCommand(query, conn1);
                                        conn1.Open();
                                        command.ExecuteNonQuery();
                                        conn1.Close();
                                    }
                                }
                            }
                        }
                    }
                }                
            }
            catch (Exception ex)
            {
                ViewBag.ErrorMessage = Helpers.Messages.GENERAL_ERROR;
                return View("AccountAnalysisResponse");
            }
          return RedirectToAction("AccountAnalysisResponse");
        }

Posted 28-Sep-19 1:41am

Tshumore

Updated 28-Sep-19 14:04pm

Add a Solution

Comments

Aarti Meswania 30-Sep-19 5:32am

please put your code for sps you have create and It seems you can solve the problems using sp only. kindly give sp details and the sample data and required output, that will make things easier to understand and you will able to get solution faster.
.net code is not required here, as it will just be used to trigger sp.

1 solution

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)

Patrice T · Answer 1 · 2019-09-28T14:04:00

Solution 1

C#

query = "UPDATE BrokerTest SET  agent_shortname ='" + Convert.ToString(mappinggg.Counter) + "' WHERE agent_shortname='" + dupBroker + "'";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 28-Sep-19 14:04pm

Patrice T

Comments

Tshumore 30-Sep-19 5:43am

I am able to get all the DISTINCT duplicates sets using :

var duplicateBrokers = gnbc.FindDuplicates(p => p.Code);

var result = duplicateBrokers.Distinct(StringComparer.OrdinalIgnoreCase);

Output
ALID-HEA-CO-001
EMIL-MTR-CO-012

Using this representative result set i have been able to update all values in the first DISTINCT duplicates set using the following snippet:
static void Main(string[] args)
{
NewAGAgentMapping();

}
public static void NewAGAgentMapping()
{

List<broker> gnbc = GetData();
using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
{
conn.Open();
Models.Broker mappinggg = new Models.Broker();
SqlCommand cmd = new SqlCommand("GetHighestAGCode", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@returnValue", SqlDbType.Int);
cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();

int Counter = Convert.ToInt32(cmd.Parameters["@returnValue"].Value);
int newCode = Counter + 1;
mappinggg.Counter = "AG" + newCode.ToString("000000");

conn.Close();

//Update the Duplicates
var duplicateBrokers = gnbc.FindDuplicates(p => p.Code);

var result = duplicateBrokers.Distinct(StringComparer.OrdinalIgnoreCase);
var first = result.ElementAt(0);

foreach (var dupBroker in duplicateBrokers)
{

using (SqlConnection conn1 = new SqlConnection(Helpers.DatabaseConnect))
{
conn1.Open();
Models.Broker mappingg = new Models.Broker();
var query = "UPDATE BrokerTest SET agent_shortname =@newanalysiscode WHERE agent_shortname=@firstdistinctset";
SqlCommand command = new SqlCommand(query, conn1);
command.Parameters.AddWithValue("@newanalysiscode", Convert.ToString(mappinggg.Counter));
command.Parameters.AddWithValue("@firstdistinctset", first);
SqlCommand command = new SqlCommand(query, conn1);

command.ExecuteNonQuery();
conn1.Close();
}
}
}

i.e
ALID-HEA-CO-001 -- AG00024
ALID-HEA-CO-001 -- AG00024
ALID-HEA-CO-001 -- AG00024

My question , how do i then update all values in the 2nd distinct set, 3rd distinct set etc .With code above 2nd set is successfully updating to :
EMIL-MTR-CO-012 -- AG00025
EMIL-MTR-CO-012 -- AG00025

I feel there is some LINQ method that i can apply to the "result" variable. Ideally a dynamic way of referencing the values in "result" rather than using ElementAt()... it should then be easier to update all values in duplicateBrokers that are similar to the value at index 0, 1 , 2 etc of "result".

Tshumore 30-Sep-19 5:46am

Sorry typo. I meant to say with code above 2nd set SHOULD update to .. For now i can only update the 1st set as illustarted. Thanks in advance to all who can offer additional insight