Click here to Skip to main content
15,847,748 members
Please Sign up or sign in to vote.
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
Updated 28-Sep-19 15:04pm
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

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[^]
 
Share this answer
 
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

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