Well, right off the bat, only two things jump out at me:
1) Your table names are not the same. I think they need to be.
2) The actual Merge call. You've said that you've tried various combinations of parameters for it, but the simplest one should work. And that is simply:
ds.Merge(dt);
Did you try that one?
~~Bonnie
>Alright, you asked for it... ;)
>
>
> private void loadData()
> {
> // Get the data from the base table.
> DataSet dsAudit = this.getTableData();
>
> // Add the data from the cube.
> this.mergeCubeData(dsAudit);
>
> // Bind the data table to the grid.
> this.grdCompare.DataSource = dsAudit;
> this.grdCompare.DataMember = "tblBase";
> }
>
> private DataSet getTableData()
> {
> // Create a connection to the database.
> SqlConnection conn = new SqlConnection("Data Source=******;" +
> "Initial Catalog=*****;Integrated Security=True");
>
> // Open the Connection.
> conn.Open();
>
> // Create the select command on the open connection.
> SqlCommand command = new SqlCommand("declare @fdom smalldatetime, " +
> "@ldom smalldatetime\n" +
> "select @fdom = FirstDayOfMonth, @ldom = LastDayOfMonth " +
> "from RetailMonths where RetailMonth = 200511\n" +
> "select Category, sum(Sales_TotUnits) as BaseSalesUnits " +
> "from DayStoreCat_Sales where RetailDate between @fdom and @ldom " +
> "group by Category order by Category", conn);
>
> // Create a data adapter for the command.
> SqlDataAdapter da = new SqlDataAdapter(command);
>
> // Create a data table to fill and return.
> DataSet ds = new DataSet("dsSales");
>
> // Fill the data table.
> da.Fill(ds, "tblBase");
>
> // Close the connection.
> conn.Close();
>
> // Add a primary key constraint to the category column of the table.
> ds.Tables["tblBase"].Constraints.Add("Category",
> ds.Tables["tblBase"].Columns["Category"], true);
>
> // Return the data table.
> return ds;
> }
>
> private void mergeCubeData(DataSet ds)
> {
> DataTable dt = new DataTable("tblCube");
>
> //dt.Columns.Add("Description", Type.GetType("System.String"));
> dt.Columns.Add("Category", Type.GetType("System.Int16"));
> //dt.Columns.Add("BaseSalesUnits", Type.GetType("System.Int32"));
> dt.Columns.Add("CubeSalesUnits", Type.GetType("System.Int32"));
>
> // Add a primary key constraint to the category column of the table.
> dt.Constraints.Add("Category", dt.Columns["Category"], true);
>
> // Create a multi-dimensional connection.
> AdomdConnection ac = new AdomdConnection("Provider=MSOLAP;" +
> "Data Source=******;Initial Catalog=*****");
>
> // Open the connection.
> ac.Open();
>
> // Create a command with an MDX query on the open connection.
> AdomdCommand cmd = new AdomdCommand("SELECT {[Sales Tot Units]} ON COLUMNS, " +
> "[Merchandise].[Category].Members ON ROWS FROM DayStoreCat_Analysis " +
> "WHERE ([Time].[2005].[Fall].[Q4].[November])", ac);
>
> // Execute the command into a multi-dimensional data reader.
> AdomdDataReader reader = cmd.ExecuteReader();
>
> // Iterate the results.
> while (reader.Read())
> {
> // Skip any null rows.
> if (!reader.IsDBNull(5) && !reader.IsDBNull(6))
> {
> // Create a new row.
> DataRow row = dt.NewRow();
>
> // Fill in the description and sales units from the cube.
> //row["Description"] = reader.GetString(5).Substring(15);
> row["Category"] = int.Parse(reader.GetString(5).Substring(9, 4));
> row["CubeSalesUnits"] = reader.GetInt32(6);
>
> // Add the new row to the table.
> dt.Rows.Add(row);
> }
> }
>
> // Close the connection.
> ac.Close(true);
>
> // Add a calculated column to the data table.
> //dt.Columns.Add("Delta", Type.GetType("System.Int32"),
> // "BaseSalesUnits - CubeSalesUnits");
> dt.AcceptChanges();
> ds.Merge(dt, true, MissingSchemaAction.Add);
> }
>>I've gone back and forth between DataSets and DataTables and commented/uncommented much of the code. For this post, I deleted the code that has been commented out for brevity's sake. I've tried many combinations of parameters on the Merge method. Basically what this is doing is it pulls a query against a SQL table. Then, it pulls an MDX query that should get the same data from a Cube. I want to merge the two tables and then add the calculated column (I left that commented out for the time being) to see if any values differ between the two sources. This is simply an audit tool for me. My method (not shown) of just using tables, and inside the AdomdDataReader iteration looking up the matching row and populating it works. I just want to see if merge would work because it seems like it should.
>
>TIA,
>Chad
>
>
>>Chad,
>>
>>It should work just fine. Post your code. You'll need to be sure you have the PrimaryKey defined in both DataTables for it to work properly. I'm not sure what other "gotchas" there might be.
>>
>>~~Bonnie
