public static System.Data.DataTable GetInputDatatable(Activity dnActivity,
IEnumerable <LinkedService> linkedServices,
IEnumerable <Dataset> datasets)
{
//SQL or Azure Blob CSV only
var inLS = LinkedServiceHelper.GetInputLinkedService(dnActivity, linkedServices, datasets);
System.Data.DataTable dsRtn = GetInputDatatableShell(dnActivity, linkedServices, datasets);
//Figure out which Type
switch (inLS.Properties.Type)
{
case "AzureStorage":
CloudStorageAccount inputStorageAccount = CloudStorageAccount.Parse(((AzureStorageLinkedService)inLS.Properties.TypeProperties).ConnectionString);
CloudBlobClient inputClient = inputStorageAccount.CreateCloudBlobClient();
AzureBlobDataset abdInput = datasets.Single(d => d.Name == dnActivity.Inputs.First().Name).Properties.TypeProperties as AzureBlobDataset;
CloudBlockBlob cbbInputFile = new CloudBlockBlob(new Uri(inputStorageAccount.BlobEndpoint.AbsoluteUri + abdInput.FolderPath + "/" + abdInput.FileName));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
cbbInputFile.DownloadToStream(ms);
ms.Position = 0;
using (Microsoft.VisualBasic.FileIO.TextFieldParser tfp = new Microsoft.VisualBasic.FileIO.TextFieldParser(ms))
{
tfp.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
tfp.SetDelimiters(",");
while (!tfp.EndOfData)
{
string[] fields = tfp.ReadFields();
dsRtn.LoadDataRow(fields, true);
}
}
break;
case "AzureSqlDatabase":
AzureSqlTableDataset astInput = datasets.Single(d => d.Name == dnActivity.Inputs.First().Name).Properties.TypeProperties as AzureSqlTableDataset;
System.Data.SqlClient.SqlConnection scInput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)inLS.Properties.TypeProperties).ConnectionString);
System.Data.SqlClient.SqlCommand commInput = new System.Data.SqlClient.SqlCommand();
commInput.Connection = scInput;
commInput.CommandType = System.Data.CommandType.Text;
commInput.CommandText = string.Format("SELECT * FROM [{0}]", astInput.TableName);
System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commInput);
sdaInput.Fill(dsRtn);
break;
default:
throw new NotImplementedException();
}
return(dsRtn);
}
public override Dataset ToSdkObject()
{
var dataset = new AzureSqlTableDataset(this.LinkedServiceName);
dataset.TableName = this.TableName;
dataset.SchemaTypePropertiesSchema = this.AzureSqlTableDatasetSchema;
dataset.Table = this.Table;
SetProperties(dataset);
return(dataset);
}
public static System.Data.DataSet GetInputDatasetShell(Activity dnActivity,
IEnumerable <LinkedService> linkedServices,
IEnumerable <Dataset> datasets)
{
//SQL or Azure Blob CSV only
var inLS = LinkedServiceHelper.GetInputLinkedService(dnActivity, linkedServices, datasets);
System.Data.DataSet dsRtn = new System.Data.DataSet();
//Figure out which Type
switch (inLS.Properties.Type)
{
case "AzureStorage":
foreach (var ds in dnActivity.Inputs)
{
var curTbl = dsRtn.Tables.Add(ds.Name);
foreach (var d in datasets.First(ds1 => ds1.Name == ds.Name).Properties.Structure)
{
curTbl.Columns.Add(d.Name);
}
}
break;
case "AzureSqlDatabase":
foreach (var ds in datasets)
{
var curTbl = dsRtn.Tables.Add(ds.Name);
AzureSqlTableDataset astInput = ds.Properties.TypeProperties as AzureSqlTableDataset;
System.Data.SqlClient.SqlConnection scInput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)inLS.Properties.TypeProperties).ConnectionString);
System.Data.SqlClient.SqlCommand commInput = new System.Data.SqlClient.SqlCommand();
commInput.Connection = scInput;
commInput.CommandType = System.Data.CommandType.Text;
commInput.CommandText = string.Format("SELECT * FROM [{0}]", astInput.TableName);
System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commInput);
sdaInput.FillSchema(curTbl, System.Data.SchemaType.Source);
}
break;
default:
throw new NotImplementedException();
}
return(dsRtn);
}
public static System.Data.DataSet GetOutputDatasetShell(Activity dnActivity,
IEnumerable <LinkedService> linkedServices,
IEnumerable <Dataset> datasets)
{
//SQL or Azure Blob CSV only
var outLS = LinkedServiceHelper.GetOutputLinkedService(dnActivity, linkedServices, datasets);
System.Data.DataSet dsRtn = new System.Data.DataSet();
//Figure out which Type
switch (outLS.Properties.Type)
{
case "AzureStorage":
foreach (var ds in dnActivity.Outputs)
{
System.Data.DataTable dtNew = new System.Data.DataTable(ds.Name);
foreach (var d in datasets.Single(d => d.Name == ds.Name).Properties.Structure)
{
Type tCol = typeof(string);
switch (d.Type)
{
case "Boolean":
tCol = typeof(bool);
break;
case "Decimal":
tCol = typeof(decimal);
break;
case "Enum":
tCol = typeof(Enum);
break;
case "Guid":
tCol = typeof(Guid);
break;
case "Int16":
tCol = typeof(Int16);
break;
case "Int64":
tCol = typeof(Int64);
break;
case "Int32":
tCol = typeof(Int32);
break;
case "Datetime":
tCol = typeof(DateTime);
break;
case "DateTimeOffset":
tCol = typeof(DateTimeOffset);
break;
case "Double":
tCol = typeof(double);
break;
case "Single":
tCol = typeof(Single);
break;
case "Timespan":
tCol = typeof(TimeSpan);
break;
}
dtNew.Columns.Add(d.Name, tCol);
}
dsRtn.Tables.Add(dtNew);
}
break;
case "AzureSqlDatabase":
System.Data.SqlClient.SqlConnection scOutput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)outLS.Properties.TypeProperties).ConnectionString);
System.Data.SqlClient.SqlCommand commOutput = new System.Data.SqlClient.SqlCommand();
foreach (var t in dnActivity.Outputs)
{
AzureSqlTableDataset astOutput = datasets.Single(d => d.Name == t.Name).Properties.TypeProperties as AzureSqlTableDataset;
commOutput.Connection = scOutput;
commOutput.CommandType = System.Data.CommandType.TableDirect;
commOutput.CommandText = astOutput.TableName;
System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commOutput);
sdaInput.FillSchema(dsRtn, System.Data.SchemaType.Source);
}
break;
default:
throw new NotImplementedException();
}
return(dsRtn);
}
public static void WriteOutputDataset(Activity dnActivity,
IEnumerable <LinkedService> linkedServices,
IEnumerable <Dataset> datasets, System.Data.DataSet dsOutput)
{
//SQL or Azure Blob CSV only
var outLS = LinkedServiceHelper.GetOutputLinkedService(dnActivity, linkedServices, datasets);
//Figure out which Type
switch (outLS.Properties.Type)
{
case "AzureStorage":
CloudStorageAccount outputStorageAccount = CloudStorageAccount.Parse(((AzureStorageLinkedService)outLS.Properties.TypeProperties).ConnectionString);
CloudBlobClient outputClient = outputStorageAccount.CreateCloudBlobClient();
foreach (var t in dnActivity.Outputs)
{
AzureBlobDataset abdOutput = datasets.Single(d => d.Name == t.Name).Properties.TypeProperties as AzureBlobDataset;
CloudBlobContainer container = outputClient.GetContainerReference(abdOutput.FolderPath.Split('/').First());
string sFolder = abdOutput.FolderPath.Replace(abdOutput.FolderPath.Split('/').First(), "");
CloudBlockBlob cbbOutputFile = container.GetBlockBlobReference(sFolder.Length > 0 ? sFolder + "/" + abdOutput.FileName : abdOutput.FileName);
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
using (var swOutput = new System.IO.StreamWriter(ms))
{
System.Data.DataTable dt = dsOutput.Tables[t.Name];
foreach (System.Data.DataRow r in dt.Rows)
{
foreach (System.Data.DataColumn c in dt.Columns)
{
if (dt.Columns.IndexOf(c) == 0)
{
swOutput.Write("\"");
}
swOutput.Write(r[c]);
if (dt.Columns.IndexOf(c) != dt.Columns.Count - 1)
{
swOutput.Write("\",\"");
}
else
{
swOutput.WriteLine("\"");
}
}
}
swOutput.Flush();
ms.Position = 0;
cbbOutputFile.UploadFromStream(ms, null, null, null);
}
}
break;
case "AzureSqlDatabase":
foreach (var t in dnActivity.Outputs)
{
AzureSqlTableDataset astOutput = datasets.Single(d => d.Name == t.Name).Properties.TypeProperties as AzureSqlTableDataset;
System.Data.SqlClient.SqlConnection scOutput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)outLS.Properties.TypeProperties).ConnectionString);
System.Data.SqlClient.SqlCommand commOutput = new System.Data.SqlClient.SqlCommand();
commOutput.Connection = scOutput;
commOutput.CommandType = System.Data.CommandType.TableDirect;
commOutput.CommandText = astOutput.TableName;
System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commOutput);
sdaInput.Update(dsOutput.Tables[t.Name]);
}
break;
default:
throw new NotImplementedException();
}
}
