SQL XML with Access

The previous post discussed a SurfaceView object model concept for managed code add-ins. This post shares how to use SQL Server 2005, and above, XML features to implement stored procedure array parameters. The SQL XML code uses the sample Northwind SQL Server database as well as the SurfaceView model. The example scenario sends a <Products><ProductID> xml list that returns a non-contiguous list of products.

Note      The sample code, located at http://desktopweb.codeplex.com, contains a sql script that adds the SQL CML code to the Northwind sample database. The Northwind sample database is also available at http://desktopweb.codeplex.com.

SelectByIdList Stored Procedure

CREATE PROCEDURE SelectByIdList (       @productIds xml ) AS SET ARITHABORT ON -- @Products table for Products Non-contiguous XML query DECLARE @Products TABLE (ProductID int) INSERT INTO @Products (ProductID) SELECT ParamValues.ProductID.value('.','INT') FROM @productIds.nodes('/Products/ProductID') as ParamValues(ProductID) SELECT * FROM     Products INNER JOIN     @Products p ON    Products.ProductID = p.ProductID -- Test the procedure EXEC SelectByIdList @productIds= '<Products><ProductID>37</ProductID><ProductID>6</ProductID> <ProductID>15</ProductID><ProductID>3</ProductID></Products>'

SelectByIdList() Method

Once the SelectByIdList stored procedure is added to the Northwind database, you need to write some ADO.NET code to call SelectByIdList @productIds sending the stored procedure a XElement containing a non-contiguous list of products. Using SQL XML parameters provides a flexible means to query SQL Server.

ADO.NET Code

private DataTable sp_SelectByIdList(XElement idElements) {   using (StringWriter swStringWriter = new StringWriter())   {    using (SqlConnection dbConnection =   new SqlConnection   (MyDataAddin.Properties.Settings.Default.NorthwindConnectionString))      {        using (SqlCommand dbCommand = new SqlCommand("SelectByIdList", dbConnection))        {          dbCommand.CommandType = CommandType.StoredProcedure;          SqlParameter parameter = new SqlParameter();          parameter.ParameterName = "@productIds";         parameter.DbType = DbType.Xml;          parameter.Direction = ParameterDirection.Input; // Input Parameter           parameter.Value = idElements.ToString();          dbCommand.Parameters.Add(parameter);          dbConnection.Open();          SqlDataAdapter da = new SqlDataAdapter(dbCommand);          northwindDataSet.sp_SelectByIdList.Clear();          da.Fill(this.northwindDataSet.sp_SelectByIdList);          return this.northwindDataSet.sp_SelectByIdList;        }      }    }   }

Create Products XElement

private void selectButton_Click(object sender, EventArgs e) {   XElement xmlIdElements = new XElement("Products");   foreach (DataGridViewRow row in vw_ProductListDataGridView.Rows)   {   if (true == Convert.ToBoolean(row.Cells["SelectCheckbox"].Value))    { xmlIdElements.Add(new XElement("ProductID", Convert.ToInt32(row.Cells["ProductIDColumn"].Value)));    }   }   //Bind the DataGridView   sp_SelectByIdListDataGridView.DataSource = sp_SelectByIdList(xmlIdElements); }

To run the sample, download and setup the source from http://desktopweb.codeplex.com/SourceControl/changeset/changes/7973.  You might need to change the NorthwindConnectionString within Settings.Settings to point to your SQL Server instance.

To run the sample

1)      Open the Dashboard form by clicking the Dashboard ribbon button

2)      Click the SQL Xml Table ribbon button to view the data grid task pane

When you click a product row, the data in the form is bound to the DataGridRow. As you can see from the example, the form is now bound to Access data and SQL Server data. The sample code partially implements ADO.NET data binding. Hopefully, this might get you started bringing SQL Server data and ADO.NET into your Access solutions.

BindForm

public void BindForm(DataRow currentRow) {   access.TextBox textBox;    //Fill Form from BindingSource    //Set Form Textbox values based on ColumnName match    foreach (DataColumn c in currentRow.Table.Columns)    {      if (ThisDatabase.AllForms["Dashboard"].FindControl(c.ColumnName))        { textBox = ThisDatabase.AllForms["Dashboard"].Controls(c.ColumnName) as access.TextBox;          textBox.Value = currentRow[c.ColumnName].ToString();        }    }    textBox = null;    }   } }

Navigate a DataTable

int rowPosition = 0; void prevCommand_Click() {   if (this.SqlXmlDataGrid != null && this.SqlXmlDataGrid.Visible)   {    if (rowPosition > 0)    {      rowPosition--; this.SqlXmlDataGrid.BindForm (this.SqlXmlDataGrid.CurrentDataRow.Table.Rows[rowPosition]);    }   } } void nextCommand_Click() {   if (this.SqlXmlDataGrid != null && this.SqlXmlDataGrid.Visible)   {    if (rowPosition + 1 < this.SqlXmlDataGrid.CurrentDataRow.Table.Rows.Count)    {      rowPosition++;           this.SqlXmlDataGrid.BindForm      (this.SqlXmlDataGrid.CurrentDataRow.Table.Rows[rowPosition]);    }   } }