How to concatenate two or more columns and update those values in another column using C# ADO.NET

Question

I have SQL table like below.

I have the column SequenceId. Along with the sequenceid, i want to concatenate few parameters like YearId,Rec,Country.

So my final URL column should update like

https://US.prism.co.us/ViewFile?SequenceId=5683948823&YearId=2022&Rec=1&country=10

YearId,Rec,Country will be the static values always and SequenceId is the dynamic.

So, All the values of the URL Columns should concatenate and update every cells.

How to do this using ADO.NET C#..?

Answer 1

I'm short on time, but it looks to me, that you can form the URL this way:

UPDATE tbl
SET URL = concat('http://......./', SequenceID, '/....')

Add a suitable WHERE clause if you don't want to do it for the entire table.

Answer 2

@BeUnique , you could try the following code to concatenate columns and update it in other column.

 private void button1_Click(object sender, EventArgs e)  
        {  
            string connstr = "connstr";  
            SqlConnection connection=new SqlConnection(connstr);  
            connection.Open();  
            SqlCommand cmd = new SqlCommand("select * from Example",connection);  
            SqlDataReader reader = cmd.ExecuteReader();  
            string newsql = string.Format("Update Example set URL=@URL where SequenceId=@SequenceId");  
            SqlConnection connection1 = new SqlConnection(connstr);  
            while (reader.Read())  
            {  
                string SequenceId = reader["SequenceId"].ToString();  
                string url = string.Format("https://US.prism.co.us/ViewFile?SequenceId={0}&YearId=2022&Rec=1&country=10", SequenceId);  
                if(connection1.State== System.Data.ConnectionState.Closed)  
                {  
                    connection1.Open();   
                }  
                SqlCommand command = new SqlCommand(newsql,connection1);  
                command.Parameters.AddWithValue("@URL", url);  
                command.Parameters.AddWithValue("@SequenceId", SequenceId);  
                command.ExecuteNonQuery();  
  
            }  
            connection.Close();  
            MessageBox.Show("success");  
  
  
  
        }  

Result:

---

If the response is helpful, please click "Accept Answer" and upvote it.

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Rather than concatenate consider the following which has only works with .NET Core 5/6 and requires a reference to a ASP.NET Core DLLs which in size are less than 300k.

public class EmployeeData
{
    public long SequenceId { get; set; }
    public int YearId { get; set; }
    public int Rec { get; set; }
    public int Country { get; set; }
    public string BaseAddress { get; set; }

    public string FinalAddress()
    {

        var queryArguments = new Dictionary<string, string>()
        {
            {"SequenceId", $"{SequenceId}" },
            {"YearId", $"{YearId}" },
            {"Rec",$"{Rec}" },
            {"country",$"{Country}"}

        };

        return QueryHelpers.AddQueryString(BaseAddress, queryArguments);
    }
}

Called with

EmployeeData employeeData = new EmployeeData()
{
    BaseAddress = "https://US.prism.co.us/ViewFile", 
    SequenceId = 5683948823, 
    YearId = 2022, 
    Rec = 1, 
    Country = 10
};
Debug.WriteLine(employeeData.FinalAddress());

resulting string https://US.prism.co.us/ViewFile?SequenceId=5683948823&YearId=2022&Rec=1&country=10

The benefits are cleaner code flexible code e.g. let's assume YearId is the current year

EmployeeData employeeData = new EmployeeData()
{
    BaseAddress = "https://US.prism.co.us/ViewFile", 
    SequenceId = 5683948823, 
    YearId = DateTime.Now.Year, 
    Rec = 1, 
    Country = 10
};

Or perhaps the Country value may change etc.