CODEWITHSUNDEEP
c#mergedataset
mezoid
mezoid

Reputation: 28720

How do I merge two columns in a DataSet?

Previously I've asked about inserting a column into a dataset. I now have a similar question...namely merging two or more columns into a single column.

Lets say I have the following data set:

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));

I am needing to combine the "firstname" and "lastname" columns into a single column called "name".

Would it be best for me to create a method that merges two columns together or a more general one that can be used to merge multiple columns together?

My idea is to create a generalized method sort of like the following:

MergeColumns(string format, string mergedColumn, DataTable dt, params string[] columnsToMerge)

The user supplies a format as follows: "{0} {1}"

mergedColumn is the name of the new column...but it must be able to be the same as one of the columns that will be merged cause in my real world case I'm merging "name" and "given_names" into "name"...but I still want to be able to use it if I ever needed to merge "street", "city", "state" "postcode" etc into a column called "address".

The way I am thinking this would be used is as follows:

MergeColumns("{0} {1}", "name", dataTable, "firstname", "lastname");

given the above dataset, I would expect the resultant data set to look as follows:

DataSet ds = new DataSet();
ds.Tables.Add(new DataTable());
ds.Tables[0].Columns.Add("id", typeof(int));
ds.Tables[0].Columns.Add("name", typeof(string));

Does this seem like a reasonable approach? Does a method like this already exist? I don't want to reinvent the wheel. Also, am I creating a method that does more than I actually need right now?

Upvotes: 2

Views: 25440

Answers (8)

Sameera R.
Sameera R.

Reputation: 4592

Extension Method With selectable seperator and position,

public static class Helper
{
    public static void MergeColumns(this DataTable t, string newColumn, params string[] columnsToMerge)
    {
        t.Columns.Add(newColumn, typeof(string));

        var sb = new StringBuilder();

        sb.Append("{0}, ");
        for (int i = 1; i < columnsToMerge.Length; ++i)
            sb.Append("{" + i.ToString() + "}");

        string format = sb.ToString();

        foreach(DataRow r in t.Rows)
            r[newColumn] = string.Format(format, columnsToMerge.Select(col => r[col]).ToArray() );
    }
}

Usage

//DataTable dt
dt.MergeColumns("name", 0, " ", "lastname", "firstname");

Upvotes: 0

Himanshu Shukla
Himanshu Shukla

Reputation: 145

string result = string.Join(",", dtTotalQuery.AsEnumerable().Select(row => dtTotalQuery.Rows[0].ItemArray[0] + " " + dtTotalQuery.Rows[0].ItemArray[1]));

Upvotes: 0

Adi
Adi

Reputation: 61

This Exactly was my requirement and after a lot of searching I ended up in deciding that its better to create your own datatable and use that as your datasource to the ListBox or DropDownList whichever is required.

DataTable dt = new DataTable();
dt.Columns.Add("Test",typeof(String));
foreach (DataRow r in ds.Tables[0].Rows)
{
  DataRow dr = dt.NewRow();
  dr["Test"] = r["LastName"] + " ," + r["firstName"];
  dt.Rows.Add(dr);
}

Here ds is the dataset containing the main return table of the procedure.

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

alternatively, you can do this:

    public Form1()
    {
        InitializeComponent();


        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].Columns.Add("name", typeof(string), "lastname + ', ' + firstname"); 


        foreach (DataRow dr in ds.Tables[0].Rows)
            MessageBox.Show(dr["name"].ToString());


    }

Upvotes: 1

Michael Buen
Michael Buen

Reputation: 39393

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();


        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");

        foreach (DataRow dr in ds.Tables[0].Rows)
            MessageBox.Show(dr["name"].ToString());


    }


}

public static class Helper
{
    public static void MergeColumns(this DataTable t, string newColumn, params string[] columnsToMerge)
    {
        t.Columns.Add(newColumn, typeof(string));

        var sb = new StringBuilder();

        sb.Append("{0}, ");
        for (int i = 1; i < columnsToMerge.Length; ++i)
            sb.Append("{" + i.ToString() + "}");

        string format = sb.ToString();

        foreach(DataRow r in t.Rows)
            r[newColumn] = string.Format(format, columnsToMerge.Select(col => r[col]).ToArray() );
    }


}

Upvotes: 5

Michael Buen
Michael Buen

Reputation: 39393

        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].Columns.Add("name", typeof(string));
        foreach (DataRow dr in ds.Tables[0].Rows) dr["name"] = dr["lastname"] + ", " + dr["firstname"];


        foreach (DataRow dr in ds.Tables[0].Rows)
            MessageBox.Show(dr["name"]);

Upvotes: 0

Jason
Jason

Reputation: 15931

it seems to me that this should happen in the data layer and not the logic layer. presumably the dataset consumer knows what data it needs and could request it from the datastore explicitly, without having to manipulate datasets.

in a database scenario, i'm essentially proponing a multiple stored procedure approach and the dataset consumer would retrieve the data from the appropriate one.

Upvotes: 1

Soviut
Soviut

Reputation: 91555

If the dataset is specific enough, a MergeName() method is fine. This is especially true if you don't think you'll be using it in more than once place. A generic method will require considerably more work, but could be worth it if you're doing a lot of merging.

Some things I can think of off the top of my head that you'll have to deal with:

  • One issue you may run into is type checking; what happens if a user wants to merge a date field or a numeric field.
  • What happens to the old fields? Will they remain or be deleted? If you're using this as a direct data source, you may want to remove left over columns, so some kind of RemoveOldColumns flag may be a good idea.

Upvotes: 1

Related Questions