C# .NET - COMBINE TWO COLUMNS OF DATABASE

Asked By rani priya on 18-Apr-12 02:29 PM
HI

i have two columns in the table now i want to combine those values with " -" .

first column first name value=rani
second column last name value=priya

i want to combine these under name column as rani_priya.
Devil Scorpio replied to rani priya on 18-Apr-12 02:44 PM
Hi,

Try this sample code to concatenate first name and last name with symbol "-" in between

DataTable dtTest = new DataTable();
dtTest.Columns.Add("FirstName", System.Type.GetType("System.String"));
dtTest.Columns.Add("LastName", System.Type.GetType("System.String"));
DataColumn dcFullName = new DataColumn("FullName");
dcFullName.Expression = string.Format("{0}+'-'+{1}", "FirstName", "LastName");
dtTest.Columns.Add(dcFullName);


Venkat K replied to rani priya on 18-Apr-12 09:42 PM
Either directly you can concatenate the string using concatenate operator "+" or you can use the string builder to append the strings.

resultstr = dt.rows[0]["FirstName"] + "_" + dt.rows[0]["LastName"];
or

StringBuilder sb = new StringBuilder();
sb.Append(dt.rows[0]["FirstName"] );
sb.Append("_");
sb.Append(dt.rows[0]["LastName"]);


Suchit shah replied to rani priya on 18-Apr-12 11:34 PM
you can merge both the Column with something code like below

DataSet ds = new DataSet();    
ds.Tables.Add(new DataTable());    
ds.Tables[0].Columns.Add("id", typeof(int));    
ds.Tables[0].Columns.Add("firstname", typeof(string));    
ds.Tables[0].Columns.Add("lastname", typeof(string));      
ds.Tables[0].Rows.Add(1,"torvalds", "linus");    
ds.Tables[0].Rows.Add(2,"lennon", "john");            
ds.Tables[0].MergeColumns("name", "lastname", "firstname");
Somesh Yadav replied to rani priya on 18-Apr-12 11:50 PM

just chang your selection query:

Select Name, Code1 + ',' + Code2 + ',' + Code3 as Code FROM yourTable

Somesh Yadav replied to rani priya on 18-Apr-12 11:50 PM

You can merge cells in RowDataBound event of GridView. Try this (for your example):

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
      if (e.Row.RowType == DataControlRowType.Header)
      {
        e.Row.Cells[1].ColumnSpan = 3;
        e.Row.Cells[1].Text = "Code";
        e.Row.Cells.RemoveAt(3);
        e.Row.Cells.RemoveAt(2);
      }
      else if (e.Row.RowType == DataControlRowType.DataRow)
      {
        string cell1 = "," + ((Label)e.Row.FindControl("A2")).Text;
        string cell2 = "," + ((Label)e.Row.FindControl("A3")).Text;
        e.Row.Cells[1].ColumnSpan = 3;
        e.Row.Cells[1].Text = ((Label)e.Row.FindControl("A1")).Text + cell1 + cell2;
        e.Row.Cells.RemoveAt(3);
        e.Row.Cells.RemoveAt(2);
      }
    }
Web Star replied to rani priya on 19-Apr-12 12:18 AM
You can achieve your desired result in different way either in sql query or in C#.net code, But best and simple is just do it in sql query

Select FirstName + '_' +  LastName As FullName From tblname

above query gives you rani_priya if the Firstname is rani and last name is priya


Or you can also concatenate in c# code after getting the value in DataTable

and where you want to display that name just use concatenate both column as 

dt[row]["FirstName"].ToString() + "_" + dt[row]["LastName"].ToString()  

above code give you same result

hope this help you
dipa ahuja replied to rani priya on 19-Apr-12 06:23 AM

 
void getReuslt()
{
    string ConnString = "ConnectionString";
    SqlDataAdapter da = new SqlDataAdapter("SELECT gender, (fname+ ' ' + lname)  as name from people", ConnString);
    DataTable dt = new DataTable();
    da.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}