1. Входные данные Activity DbToJpg.
public class InputArguments
{
public string ConnectStrig { get; set; }
public string SqlCommand { get; set; }
public string FileName { get; set; }
}
2. Выходные данные Activity DbToJpg.
public class OutputResult
{
public string Error { get; set; }
public DataSet dataSet { get; set; }
public byte[] byteJpg { get; set; }
}
3. Исходный текст Activity DbToJpg.
/* 01.01.2013. Activity DbToJpg.
1. Activity DbToJpg делает выборку из базы данных MSSQL, переводит данные в формат Excel,
формирует графическую диаграмму,записывает ее на диск в формате jpg файла.
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[] byteJpg { get; set; }
4. Клиент передает для Activity минимально необходимый набор данных, оформленных в виде класса InputArguments,
принимает от Activity байтовый массив и DataSet в обьекте класса OutputResult.
Записывает массив bytуJpg на локальный диск, читает и отображает данные в компонент PictureBox.
*/
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 EXL = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using ClassLibrary;
namespace ActivityLibrary
{
public sealed class DbToJpg : 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 variables DataBase
private DataSet dataSet = null;
private SqlConnection connect = null;
private SqlCommand command = null;
private SqlDataAdapter adapter = null;
private SqlDataReader reader = null;
// Temporary variables Excel
private EXL.Application xlsx = null;
private EXL.Workbook workBook = null;
private EXL.Range rangel = null;
private EXL.ChartObject chart = null;
private EXL.ChartObjects charts = null;
private EXL.Chart chartPage = null;
private EXL.Worksheet sheet = 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(missing);
// x,y variables
string x,y = null;
// Definition sheet
sheet = (EXL.Worksheet)workBook.Worksheets.get_Item(1);
for (int i = 0; i < countRows; i++)
{
x = "A" + (i +1).ToString();
y = "B" + (i + 1).ToString();
sheet.Range[x].Value = dataSet.Tables[0].Rows[i][1].ToString();
sheet.Range[y].Value = dataSet.Tables[0].Rows[i][0].ToString();
}
// Create chart
charts = (EXL.ChartObjects) (sheet.ChartObjects(missing));
chart = (EXL.ChartObject) charts.Add(0, 0, 445, 310);
chartPage = chart.Chart;
rangel = sheet.UsedRange;
//DataSource
chartPage.SetSourceData(rangel, missing);
//Type Cart
chartPage.ChartType = EXL.XlChartType.xl3DColumnClustered;
// Disable type legend
chartPage.HasLegend = false;
// Еnable title
chartPage.HasTitle = true;
// Text title
chartPage.ChartTitle.Characters.Text = "Sample of Orders";
// X axis
chartPage.Axes(EXL.XlAxisType.xlValue).HasTitle = true;
chartPage.Axes(EXL.XlAxisType.xlValue).AxisTitle.Characters.Text = "Count";
// Y axis
chartPage.Axes(EXL.XlAxisType.xlCategory).HasTitle = true;
chartPage.Axes(EXL.XlAxisType.xlCategory).AxisTitle.Characters.Text = "ShipCountry";
// Path for write
activityDirectory = AppDomain.CurrentDomain.BaseDirectory;
// Disable tips and warnings
xlsx.DisplayAlerts = false;
// Export chart
chartPage.Export(activityDirectory + fileName);
// Close workBook
workBook.Close(missing, missing, missing);
xlsx.Quit();
// Create output data
OutRes.byteJpg = File.ReadAllBytes(activityDirectory + fileName);
OutRes.dataSet = this.dataSet;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
this.OutRes.Error = ex.ToString();
}
finally
{
releaseObject(sheet);
releaseObject(workBook);
releaseObject(xlsx);
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
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 DbToJpg
/* 01.01.2013 Клиент для проверки Activity DbToJpg :) */
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 ClassLibrary;
using System.IO;
namespace ClientForDbToJpg
{
public partial class Form1 : Form
{
private ServiceReference.ServiceClient client = null;
private ClassLibrary.InputArguments input = null;
private ClassLibrary.OutputResult output = null;
private string activityDirectory = null;
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 COUNT(*) AS Count, CustomerID FROM Orders WHERE (ShipCountry = 'UK') GROUP BY CustomerID";
textBox1.Text = "DbToJpg.jpg";
}
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 Jpg File to Disk
File.WriteAllBytes(activityDirectory + input.FileName, output.byteJpg);
// Load picture
pictureBox1.Load(activityDirectory + input.FileName);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
Visual Studio 2010 проект содержащий: Activity, Workflow, Windows Form клиент можно
Евгений Вересов.
01.01.2013 года.
