How to add a column when using SqlDataAdapter to fill a datagridview

Question

I am using SQL to fill my datagridview. I am doing that this way:

string cn = ConfigurationManager.ConnectionStrings["Scratchpad"].ConnectionString;
SqlConnection myConnection = new SqlConnection(cn);

string sql = "some text here";
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, myConnection);
DataSet ds = new DataSet();
myConnection.Open();
dataadapter.Fill(ds, "Authors_table");
myConnection.Close();
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Authors_table";

Now it deletes the old datagridview and paste the selection. But I want just to add the data to the column right of the existing data.

Thanks in advance

Answer 1

This requirement sounds like it could be met with a SQL Inner Join in the query you're using in your SqlDataAdapter. If you were able to join your two data sources together on matching keys, you could simply pull the data down once. But I'm going to assume there's some reason why that won't work for you.

You can in fact do what you want with a DataSet, but you'll need to take a few more steps:

string sql = "The first query"
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, myConnection);
DataSet ds = new DataSet();
myConnection.Open();
dataadapter.Fill(ds, "Authors_table");
myConnection.Close();

// I don't know what your primary key is, but you need to have one.
// I am assuming it's called "author_id".

DataColumn authorIdColumn = ds.Tables["Authors_table"].Columns["author_id"];
ds.Tables["Authors_table"].PrimaryKey = new[] { authorIdColumn };

// Get your second set of data
sql = "My second query, which also has the same primary key, but has more columns"
dataadapter = new SqlDataAdapter(sql, myConnection);
dataadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet ds2 = ds.Clone();

myConnection.Open();
dataadapter.Fill(ds2, "Authors_table");
myConnection.Close();

// Now we use the DataSet.Merge method, which is very powerful. 
ds.Merge(ds2, false, MissingSchemaAction.AddWithKey);

dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Authors_table";

You may have to experiment with that a lot: this is just the outline. Without knowing your data and what you do up to this point, it's hard to know what settings or methods you might also need to call.

Answer 2

Try this. I ran multiple queries in one go

SqlDataAdapter adapter = new SqlDataAdapter(
  "SELECT * FROM Customers; SELECT * FROM Orders", connection);
adapter.TableMappings.Add("Table", "Customer");
adapter.TableMappings.Add("Table1", "Order");

adapter.Fill(ds);