This code show how to merge two DataSets. In this example, we are merging the results of a Stored Procedure with the results of a SELECT statement. This example uses Microsoft SQL Server.

Create the tables and populate data

Use this SQL code to create the tables and the SQL statement for use by the example below.

CREATE PROCEDURE sp_Names (@min_age int=0) AS
   SELECT * FROM Table1 WHERE age >= @min_age ORDER BY name
GO

DROP TABLE Table1
DROP TABLE Table2

CREATE TABLE Table1 (name varchar(255), age int)
INSERT INTO Table1 VALUES ('Dan', 27)
INSERT INTO Table1 VALUES ('Wanda', 26)
INSERT INTO Table1 VALUES ('Steve', 32)

CREATE TABLE Table2 (name varchar(255), age int)
INSERT INTO Table2 VALUES ('LaFawnda', 34)
INSERT INTO Table2 VALUES ('Kip', 32)

Merge and Sort Two DataSets

In the example below, we merge the results of the Stored Procedure sp_Names with the results of another SELECT statement, sort by the name field, and then display the results.

// SET UP DATABASE CONNECTION
SqlConnection conn = new SqlConnection("Persist Security Info=True;User ID=sa;" & _
   "Password=;Initial Catalog=myDB;Data Source=localhost");

// POPULATE ds1 WITH RESULTS OF sp_Names
DataSet ds1 = new DataSet();
SqlDataAdapter cmd = new SqlDataAdapter("sp_Names", conn);
cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
cmd.SelectCommand.Parameters.Add("@min_age", SqlDbType.VarChar, 11);
cmd.SelectCommand.Parameters["@min_age"].Value = "27";
cmd.Fill(ds1);

// POPULATE ds2 WITH RESULTS OF SELECT STATEMENT
DataSet ds2 = new DataSet();
SqlDataAdapter da = new SqlDataAdapter ("SELECT name, age FROM Table2", conn);
da.Fill(ds2);

// MERGE THE TWO DATASETS
ds1.Merge(ds2);

// SET SORTING BASED ON NAME
DataView dv = ds1.Tables[0].DefaultView;
dv.Sort = "name";

// LOOP THROUGH SORTED MERGED DATASET AND DISPLAY VALUES
for(int i=0; i < dv.Count; i++) {
   Response.Write("Name: " + dv[i]["name"] + " - Age: " + dv[i]["age"] + "");
}

When you execute this code given the data above, you should get the following results:

Name: Dan - Age: 27
Name: Kip - Age: 32
Name: LaFawnda - Age: 34
Name: Steve - Age: 32

Notice that anyone with an age below 27 is omitted because we set the @min_age parameter to 27.