Saturday, May 21, 2011

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


Monday, May 2, 2011

Managing Access Add-ins with a SurfaceView

Extending Microsoft Access solutions with managed code has traditionally been a challenge. Visual Studio Shared Add-ins have been available for some time; however, managing the details of releasing Access.exe after closing an Access application has been a challenge. This article presents a SurfaceView object model that manages the release of Access form and report controls. After discussing the SurfaceView, the article suggests effective ways to extend Access applications using SurfaceView, Ribbon controls, custom task panes, a custom Access form and Windows Workflow 4.0. The code snippets used within the article are contained within the source code located at http://desktopweb.codeplex.com/SourceControl/changeset/changes/7526.

Topics

Using the SurfaceView object model to manage an Access .NET add-in enables extensibility opportunities beyond traditional VBA. This article presents several how to topics to get you started extending Access with managed code.
  • Setup the sample including troubleshooting Add-in setup
  • Use the sample
  • Use a SurfaceView to manage forms and reports
  • Use a ribbon button
  • Create custom task panes
  • Create a custom Access popup form
  • Use Windows Workflow 4.0 to automate Access
Please note that the SurfaceView model is a sample of a potential object model to manage Access objects. As with any sample, the concepts and source code have not been thoroughly designed or tested and are not intended for production use.
The remainder of this blog is in pdf format …

PDF Article