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

Gani_tpt 1,466 Reputation points
2022-01-18T02:27:02.273+00:00

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#..?

165922-image.png

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,177 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,545 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-18T23:16:56.67+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Jack J Jun 24,276 Reputation points Microsoft Vendor
    2022-01-18T06:37:56.697+00:00

    @Gani_tpt , 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:

    165917-image.png


    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.


  2. Karen Payne MVP 35,026 Reputation points
    2022-01-18T11:24:31.633+00:00

    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.