Windows Workflow 4.0 activity designers are ideal for integrating SQL Azure cloud storage into desktop applications. The latest desktopWeb.codeplex.com source code shows how to create two SQL Azure compatible activities:

  • ConnectionBuilderActivity
  • SelectDataTableActivity

To support SelectDataTableActivity, two new ExpressionItemsProviders were added to get SQL Server on premise or SQL Azure view schema.

ConnectionBuilderActivity

ConnectionBuilderActivity returns a SqlConnectionStringBuilder tailored to an on premise or SQL Azure instance. Credentials are passed to ConnectionBuilderActivity from the LoginActivity which is not shown in this summary.

image

SelectDataTableActivity

SelectDataTableActivity uses the SqlConnectionStringBuilder from ConnectionBuilderActivity to connect to any SQL instance including SQL Azure.

image

Code Activity

  [Designer(typeof(MyData.Activities.Designers.SelectDataTableDesigner))]
  public sealed class SelectDataTableActivity : CodeActivity
  {
    [RequiredArgument]
    public InArgument<SqlConnectionStringBuilder> SqlConnectionBuilder { get; set; }
    [RequiredArgument]
    public InArgument<string> SqlViewName { get; set; }
    [RequiredArgument]
    public InArgument<List<string>> ColumnNames { get; set; }

    //The InitialCatalog comes from the SqlConnectionBuilder connection 
    [RequiredArgument]
    public InArgument<string> InitialCatalog { get; set; }

    public OutArgument<DataTable> DataTable { get; set; }

    protected override void Execute(CodeActivityContext context)
    {
      string viewName = SqlViewName.Get(context);
      List<string> columnNameList = ColumnNames.Get(context);

      using (SqlConnection connection = new SqlConnection(SqlConnectionBuilder.Get(context).ToString()))
      {
        using (SqlCommand command = connection.CreateCommand())
        {
          connection.Open();

          string columnNames = string.Empty;
          foreach (string columnName in columnNameList)
          {
            columnNames += columnName + " ,";
          }

          if (columnNames.Length > 0)
            columnNames = columnNames.Substring(0, columnNames.Length - 1);

          command.CommandText = String.Format("SELECT {0} FROM {1}", columnNames, viewName);

          DataTable dt = new DataTable();
          SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
          dataAdapter.Fill(dt);

          DataTable.Set(context, dt);
        }
      }
    }
  }

Select Expression

SqlViewNamesItemsProvider is used to provide view name menu options from any SQL Server instance including SQL Azure to create the Select Expression.

image

Code Snippet

    public static List<ListItem> ViewNames(SqlConnectionStringBuilder connStringBuilder)
    {
      using (SqlConnection connection = new SqlConnection(connStringBuilder.ToString()))
      {
        connection.Open();

        return  (from r in connection.GetSchema("TABLES").AsEnumerable()
                 where r.Field<string>("TABLE_TYPE") == "VIEW"
                 select
                 new ListItem
                 {
                    Name = r.Field<string>("TABLE_NAME"),
                    Value = String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME"))
                 }).ToList<ListItem>();
      }
    }

Columns Expression

SqlViewSchemaItemsProvider is used to provide view schema list items from any SQL Server instance including SQL Azure to create select columns.

image

Code Snippet

    public static List<ListItem> ViewSchema(string viewName, SqlConnectionStringBuilder connStringBuilder)
    {
      using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
      {
        conn.Open();

        return (from r in conn.GetSchema("Columns").AsEnumerable()
                where r.Field<string>("TABLE_NAME") == viewName
                orderby r.Field<int>("ORDINAL_POSITION")
                select new ListItem
                {
                  Name = r.Field<string>("COLUMN_NAME"),
                  Value = String.Format("{0} ({1})", r.Field<string>("DATA_TYPE"), r.Field<int?>("CHARACTER_MAXIMUM_LENGTH").ToString())
                }
                ).ToList<ListItem>();
      }
    }

Workflow Designer

The workflow designer supports the ConnectionBuilderActivity and SelectDataTableActivity.

image

Example – Run Office Workflow to get SQL Azure DataTable

  1. Get Credentials with LoginFormActivity
  2. Connect to SQL Azure with ConnectionBuilderActivity
  3. Fill DataTable with SelectDataTableActivity

image

sampleParticipant_ActivityStateTracked

      //Sample Word Event Filter
      if (e.ActivityStateRecord.Activity.Name == "SelectDataTableActivity")
      {
        System.Data.DataTable dt = (System.Data.DataTable)e.ActivityStateRecord.Arguments["DataTable"];
       
        List<string> q = (from r in dt.AsEnumerable() 
                          select r.Field<string>("Name")).ToList<string>();

        foreach (var value in q)
        {
          Globals.ThisAddIn.Application.Selection.TypeText(String.Format("{0} ,", value));
        }
      }
      //Sample Excel Event Filter      
      if (e.ActivityStateRecord.Activity.Name == "SelectDataTableActivity")
      {
        System.Data.DataTable dt = (System.Data.DataTable)e.ActivityStateRecord.Arguments["DataTable"];

        List<string> q = (from r in dt.AsEnumerable()
                          select r.Field<string>("Name")).ToList<string>();

        for (int i = 1; i <= q.Count; i++)
        {
          Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
          sheet.Cells[i, 1] = q[i - 1]; 
        }
      }

Last edited Nov 24, 2011 at 8:41 PM by dvana, version 12

Comments

No comments yet.