DbToXslx Windows Workflow Activity

Windows Workflow Activity DbToXslx. Activity , DataSet, DataSet Excel . , Actyvity Excel, Web Service WCF.
MS Visual Studio 2010 WCF + WorkFlow, Activity. WorkFlow, , Windows Form . , : , SQL , xlsx. Microsofr Excel.
1. Activity DbToXslx.
Activity InputArguments :
public class InputArguments
{
public string ConnectStrig{ get; set; }
public string SqlCommand{ get; set; }
public string FileName{ get; set; }
}
2. Activity DbToXslx.
Activity OutputResult : byteExcel - Xslx, dataSet Error - .
. :dataSet Error, byteExcel .
public class OutputResult
{
public string Error{ get; set; }
public DataSet dataSet{ get; set; }
public byte[] byteExcel{ get; set; }
}
3. Activity DbToXslx.
Activity DbToXlsx , :
  • BeginExecute;
  • EndExecute.
, SQL BeginExecute, Activity EndExecute.
/*11.12.2012. Activity DbToXlsx MSSQL , Excel,
xlsx .

1.Activity Orders MSSQL Northwind,
DataSet, , DataSet Exel.
Excel xlsx.

2. InputArguments :
public string ConnectStrig{ get; set; }
public string SqlCommand{ get; set; }
public string FileName{ get; set; }

3. OutputResult :
public string Error{ get; set; }
public DataSet dataSet{ get; set; }
public byte[] byteExcel{ get; set; }

4. InputArguments, Activity Excel
OutputResult.
byte[] , Microsoft Excel.
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Activities;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Markup;
using System.ComponentModel;
using System.Reflection;
using Microsoft.Office.Tools.Excel;
using EXL = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using ClassLibrary;

namespace ActivityLibrary
{
public sealed class DbToXlsx : AsyncCodeActivity<OutputResult>
{
// Input Object
[RequiredArgument]
[DefaultValue(null)]
public InArgument<InputArguments> inputArguments { get; set; }

// Temporary variable for input arguments
private InputArguments inputObject= null;
private string connectStr= null;
private string cmdSQL= null;
private string fileName= null;
private string activityDirectory= null;

//Output Object
private OutputResult OutRes= null;

// Temporary use variable
private DataSet dataSet= null;
private SqlConnection connect= null;
private SqlCommand command= null;
private SqlDataAdapter adapter= null;
private SqlDataReader reader= null;

// Temporary Exsel variaable
private EXL.Application xlsx= null;
private EXL.WorkbookworkBook= null;
private EXL.XmlMap ImportMap= null;
private EXL.Range rangel= null;

Object missing= (Object)Type.Missing;

protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
try
{
// Input Object
inputObject = inputArguments.Get(context);
// Connection string
connectStr= inputObject.ConnectStrig;
// Command string
cmdSQL= inputObject.SqlCommand;
// Name File for write docx
fileName= inputObject.FileName;

// If connect string is empty
if (String.IsNullOrEmpty(connectStr)) throw new ArgumentNullException("Value", "Connection String is Empty");

// If command string is empty
if (String.IsNullOrEmpty(cmdSQL)) throw new ArgumentNullException("Value", "Command String is Empty");

// If nameFile string is empty
if (String.IsNullOrEmpty(fileName)) throw new ArgumentNullException("Value", "File Name String is Empty");

connect = new SqlConnection(connectStr);
connect.Open();
command = new SqlCommand(cmdSQL, connect);
adapter = new SqlDataAdapter(command);
context.UserState = adapter;
return adapter.SelectCommand.BeginExecuteReader(callback, state);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}

protected override OutputResult EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
{
try
{
SqlDataAdapter adapter = (SqlDataAdapter)context.UserState;

//Create Output Object
OutRes = new OutputResult();

// End Execute Reader
reader = adapter.SelectCommand.EndExecuteReader(result);
reader.Close();

// Create DataSet
dataSet = new DataSet();
adapter.Fill(dataSet);

// Count Rows and Columns
int countRows = dataSet.Tables[0].Rows.Count;
int countField = dataSet.Tables[0].Columns.Count;

// Create xlsx Application
xlsx = new EXL.Application();

// Add workbook
workBook = xlsx.Workbooks.Add();

// Add a new XML map to the collection.
ImportMap = xlsx.ActiveWorkbook.XmlMaps.Add(dataSet.GetXmlSchema(), "NewDataSet");

// If error creating maps
if (ImportMap == null) { throw new System.InvalidOperationException("Create Map is Error"); }

// Definition range
rangel = xlsx.ActiveSheet.Range["A1",missing];

// Import xml data
xlsx.ActiveWorkbook.XmlImportXml(dataSet.GetXml(), out ImportMap,true,rangel);
xlsx.Visible = false;

// Disable tips and warnings
xlsx.DisplayAlerts = false;

// Path for write
activityDirectory = AppDomain.CurrentDomain.BaseDirectory;

// Write data
workBook.SaveAs(activityDirectory + fileName, EXL.XlFileFormat.xlWorkbookDefault,
missing, missing, missing, missing, EXL.XlSaveAsAccessMode.xlExclusive,
missing, missing, missing, missing, missing);

// Close and Ouit
workBook.Close(missing, missing, missing);
xlsx.Quit();

// Create output data
OutRes.byteExcel = File.ReadAllBytes(activityDirectory + fileName);
OutRes.dataSet = this.dataSet;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
this.OutRes.Error = ex.ToString();
}
finally
{
releaseObject(ImportMap);
releaseObject(rangel);
releaseObject(workBook);
releaseObject(xlsx);

GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
this.connect.Close();
}
return OutRes;
}

// Cleaning tempory Exsel data
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
Console.WriteLine(ex.Message);
this.OutRes.Error = ex.ToString();
}
finally
{
GC.Collect();
}
}
}
}
4. Activity DbToXlsx
, , Microsoft Excel.
/* 11.12.2012 Activity DbToXlsx */
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using EXL = Microsoft.Office.Interop.Excel;
using ClassLibrary;
using System.IO;

namespace ClientForDbToXlsx
{
public partial class Form1 : Form
{
private ServiceReference.ServiceClient client= null;
private ClassLibrary.InputArguments input= null;
private ClassLibrary.OutputResult output= null;

// Temporary Excel variable
private EXL.Application xlsx= null;
private EXL.Workbook workBook= null;
private string activityDirectory= null;

Object missing = (Object)Type.Missing;
public Form1()
{
InitializeComponent();
client = new ServiceReference.ServiceClient();
input = new InputArguments();
output = new OutputResult();
textBox2.Text = @"Data Source=PROGR\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True; Asynchronous Processing=true";
textBox3.Text = @"SELECT Orders.OrderID, Orders.CustomerID, Orders.ShipAddress FROM Orders";
textBox1.Text = "DbToXlsx.xlsx";
}

private void button1_Click(object sender, EventArgs e)
{
try
{
// Input string for connect
input.ConnectStrig = textBox2.Text;
// Input SQL command
input.SqlCommand = textBox3.Text;
// Input name File for write
input.FileName = textBox1.Text;

// Send and receive data
output = client.GetData(input);

// Write Excel File to Disk
File.WriteAllBytes(activityDirectory + input.FileName, output.byteExcel);

// Creating Excel document
xlsx = new EXL.Application();

// Add workbook
activityDirectory = AppDomain.CurrentDomain.BaseDirectory;
workBook = xlsx.Workbooks.Add(activityDirectory + input.FileName);
xlsx.Visible = true;
xlsx.DisplayAlerts = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}

Visual Studio 2010 : Activity, Hosting Activity Windows Form .
. C , .
15.12.2012 .

© Argument Ltd, 2017 ,  : 8-921-215-45-70,   e-mail