public static void GetConnectedPublnSets()
{
using (var connection = new SqlConnection("context connection=true"))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("cluster", SqlDbType.Int),
new SqlMetaData("new_id", SqlDbType.Int)
});
SqlContext.Pipe.SendResultsStart(record);
List<int> publnUT = new List<int>();
List<int> pubMinPairIndex = new List<int>();
List<int> pubMaxPairIndex = new List<int>();
List<int> pairPub2Index = new List<int>();
command.CommandText = string.Format("select new_id, min_pair_index, max_pair_index from tmp_publns4 order by pub_index");
using (SqlDataReader reader = command.ExecuteReader())
while (reader.Read())
{
publnUT.Add(reader.GetInt32(0));
pubMinPairIndex.Add(reader.GetInt32(1));
pubMaxPairIndex.Add(reader.GetInt32(2));
}
command.CommandText = string.Format("select publn2_index from tmp_publn_pairs3 order by pair_index");
using (SqlDataReader reader = command.ExecuteReader())
while (reader.Read())
pairPub2Index.Add(reader.GetInt32(0));
ConnectedPubSetSearcher connectedPubSetSearcher = new ConnectedPubSetSearcher(pubMinPairIndex, pubMaxPairIndex, pairPub2Index);
List<List<int>> connectedPubSets = connectedPubSetSearcher.getConnectedPubSets();
for (int i = 0; i < connectedPubSets.Count; i++)
{
record.SetValue(0, i);
for (int j = 0; j < connectedPubSets[i].Count; j++)
{
record.SetValue(1, publnUT[connectedPubSets[i][j]]);
SqlContext.Pipe.SendResultsRow(record);
}
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
private void AddToWindow(object[] newTuple, string[] operators, ArrayList resultCollection, ArrayList resultstringCollection, SqlDataRecord record, SqlBoolean isFrameworkMode, int level, DataTable dtResult)
{
//Erste Spalte ist die ID
long?[] recordInt = new long?[operators.GetUpperBound(0) + 1];
string[] recordstring = new string[operators.GetUpperBound(0) + 1];
DataRow row = dtResult.NewRow();
for (int iCol = 0; iCol <= newTuple.GetUpperBound(0); iCol++)
{
//Only the real columns (skyline columns are not output fields)
if (iCol <= operators.GetUpperBound(0))
{
//LOW und HIGH Spalte in record abfüllen
if (operators[iCol].Equals("LOW"))
{
if (newTuple[iCol] == DBNull.Value)
recordInt[iCol] = null;
else
recordInt[iCol] = (long)newTuple[iCol];
//Check if long value is incomparable
if (iCol + 1 <= recordInt.GetUpperBound(0) && operators[iCol + 1].Equals("INCOMPARABLE"))
{
//Incomparable field is always the next one
recordstring[iCol] = (string)newTuple[iCol + 1];
}
}
}
else
{
row[iCol - (operators.GetUpperBound(0) + 1)] = newTuple[iCol];
record.SetValue(iCol - (operators.GetUpperBound(0) + 1), newTuple[iCol]);
}
}
row[record.FieldCount - 1] = level;
record.SetValue(record.FieldCount - 1, level);
if (isFrameworkMode == true)
{
dtResult.Rows.Add(row);
}
else
{
SqlContext.Pipe.SendResultsRow(record);
}
resultCollection.Add(recordInt);
resultstringCollection.Add(recordstring);
}
public static void SendDataTable(DataTable dt)
{
bool[] coerceToString; // Do we need to coerce this column to string?
SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);
SqlDataRecord record = new SqlDataRecord(metaData);
SqlPipe pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
try
{
foreach (DataRow row in dt.Rows)
{
for (int index = 0; index < record.FieldCount; index++)
{
object value = row[index];
if (null != value && coerceToString[index])
value = value.ToString();
record.SetValue(index, value);
}
pipe.SendResultsRow(record);
}
}
finally
{
pipe.SendResultsEnd();
}
}
public void Update(IList<TreeViewNode> treeViewNodes)
{
var sqlConnection = new SqlConnection(ConnectionString);
var sqlCommand = new SqlCommand("dbo.UpdateTreeViewData", sqlConnection)
{
CommandType = CommandType.StoredProcedure,
};
sqlCommand.Parameters.Add("@p_TreeViewData", SqlDbType.Structured);
var list = new List<SqlDataRecord>();
foreach (var treeViewNode in treeViewNodes)
{
var sqlDataRecord = new SqlDataRecord(
new SqlMetaData("Id", SqlDbType.Int),
new SqlMetaData("ParentId", SqlDbType.Int),
new SqlMetaData("NodeName", SqlDbType.NVarChar, 50),
new SqlMetaData("IsSelected", SqlDbType.Bit));
sqlDataRecord.SetValue(0, treeViewNode.Id);
sqlDataRecord.SetValue(1, treeViewNode.ParentId);
sqlDataRecord.SetString(2, treeViewNode.NodeName);
sqlDataRecord.SetValue(3, treeViewNode.IsSelected);
list.Add(sqlDataRecord);
}
sqlCommand.Parameters["@p_TreeViewData"].Value = list;
try
{
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
}
finally
{
sqlConnection.Close();
}
}
/// <summary>Send a DataTable to a SqlContext.</summary>
/// <exception cref="Exception">Throw an exception if the DataTable is null.</exception>
/// <param name="dt">The DataTable to send to the SqlContext.</param>
internal static void SendDataTable(DataTable dt)
{
if (dt == null)
{
throw new Exception(ExceptionMessage.Unexpected_NullResultSet);
}
bool[] useToString;
var metaData = ExtractDataTableColumnMetaData(dt, out useToString);
var record = new SqlDataRecord(metaData);
var pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
try
{
foreach (DataRow row in dt.Rows)
{
for (var index = 0; index < record.FieldCount; index++)
{
var value = row[index];
if (value != null && useToString[index])
{
value = value.ToString();
}
record.SetValue(index, value);
}
pipe.SendResultsRow(record);
}
}
finally
{
pipe.SendResultsEnd();
}
}
/// <summary>
/// Gets the orders by order ids.
/// </summary>
/// <param name="ids">The ids.</param>
/// <param name="fcn">SQL connection.</param>
/// <param name="ftrns">SQL transaction.</param>
/// <returns>The matching orders.</returns>
public static List<Order> GetOrdersByOrderIds(int[] ids, SqlConnection fcn, SqlTransaction ftrns)
{
List<Order> orders = new List<Order>();
if(ids.Length == 0) { return orders; };
List<SqlDataRecord> rowData = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s = new StringBuilder();
foreach(int id in ids) {
SqlDataRecord rec = new SqlDataRecord(hashTable);
rec.SetValue(0, "orderId");
rec.SetValue(1, id);
rec.SetBoolean(2, false);
rec.SetString(3, "int");
rec.SetValue(4, 8);
rowData.Add(rec);
}
SqlConnection cn;
if(fcn != null) {
cn = fcn;
} else {
cn = Site.SqlConnection;
}
using(SqlCommand cmd = cn.CreateCommand()) {
if(fcn != null) {
cmd.Transaction = ftrns;
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.getOrders";
cmd.Parameters.Add("@orderIds", SqlDbType.Structured);
cmd.Parameters["@orderIds"].Direction = ParameterDirection.Input;
cmd.Parameters["@orderIds"].Value = rowData;
using(SqlDataReader u = cmd.ExecuteReader()) {
int orderId = -1;
DateTime orderDate = DateTime.MinValue;
decimal grandTotal = 0;
decimal taxTotal = 0;
decimal subTotal = 0;
decimal shippingTotal = 0;
decimal service1 = 0;
decimal service2 = 0;
string manifest = "";
string purchaseOrder = "";
decimal discount = 0;
string comment = "";
decimal paid = 0;
Guid billToAddressId = Guid.Empty;
bool closed = false;
bool canceled = false;
Guid paymentMethodId = Guid.Empty;
int termId = -1;
int userId = -1;
string orderNumber = "";
bool creditMemo = false;
string scanned_order_image = "";
DateTime readyForExport = DateTime.MinValue;
DateTime recalculatedOn = DateTime.MinValue;
Guid sessionId = Guid.Empty;
int soldBy = -1;
int requisitionedBy = -1;
int approvedBy = -1;
DateTime deliverBy = DateTime.MinValue;
string vendor_accountNo = "";
string FOB = "";
int parentOrderId = -1;
int order_status = -1;
List<Line> lines = new List<Line>();
while(u.Read()) {
/* #44 is orderId */
if(u.GetInt32(44) != orderId && orderId != -1) {
/*the orderId has changed, add the previous order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
lines = new List<Line>();/* create a new list of lines for the next order */
}
orderId = u.GetInt32(44);
orderDate = u.GetDateTime(132);
grandTotal = u.GetDecimal(133);
taxTotal = u.GetDecimal(134);
subTotal = u.GetDecimal(135);
shippingTotal = u.GetDecimal(136);
service1 = u.GetDecimal(137);
service2 = u.GetDecimal(138);
manifest = u.GetString(139);
purchaseOrder = u.GetString(140);
discount = u.GetDecimal(164);
comment = u.GetString(141);
paid = u.GetDecimal(190);
billToAddressId = u.GetGuid(103);
closed = u.GetBoolean(191);
canceled = u.GetBoolean(191);
termId = u.GetInt32(84);
userId = u.GetInt32(55);
orderNumber = u.GetString(46);
creditMemo = u.GetBoolean(193);
scanned_order_image = u.GetString(53);
readyForExport = u.GetDateTime(7);
recalculatedOn = u.GetDateTime(194);
sessionId = u.GetGuid(38);
soldBy = u.GetInt32(195);
requisitionedBy = u.GetInt32(196);
approvedBy = u.GetInt32(197);
deliverBy = u.GetDateTime(198);
vendor_accountNo = u.GetString(199);
FOB = u.GetString(200);
parentOrderId = u.GetInt32(201);
order_status = u.GetInt32(5);
/* always add every line */
lines.Add(new Line(
u.GetGuid(37)/*cartId*/,
u.GetGuid(38)/*sessionId*/,
u.GetInt32(39)/*qty*/,
u.GetString(0)/*itemNumber*/,
u.GetDecimal(41)/*price*/,
u.GetDateTime(42)/*add time*/,
u.GetInt32(44)/*orderId*/,
u.GetInt32(45)/*serialId*/,
u.GetString(46)/*orderNumber*/,
u.GetString(47)/*serialNumber*/,
u.GetGuid(48)/*addressId*/,
u.GetInt32(49)/*shipmentId*/,
u.GetString(50)/*shipmentNumber*/,
u.GetInt32(51)/*lineNumber*/,
u.GetString(52)/*epsmmcsoutput*/,
u.GetString(54)/*epsmmcsfilename*/,
u.GetDecimal(170)/*valueCostTotal*/,
u.GetDecimal(171)/*noTaxValueCostTotal*/,
u.GetDateTime(203)/*fullfillmentDate*/,
u.GetDateTime(204)/*estimatedFulfillmentDate*/,
u.GetGuid(202)/*parentCartId*/,
u.GetInt32(12)/*backorderedqty*/,
u.GetInt32(13)/*canceledQty*/,
u.GetString(174)/*customLineNumber*/,
u.GetInt32(205)/*kitAllocationId*/,
u.GetInt32(206)/*kitQty*/,
u.GetBoolean(207)/*showAsSeperateLineOnInvoice*/,
u.GetGuid(208)/*vendorItemKitAssignmentId*/,
u.GetGuid(209)/*kitAllocationCartId*/,
u.GetInt32(1)/*line_status*/
));
}
/* add the last order */
orders.Add(new Order(
orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
));
/* now all the shipments that belong to the orders */
u.NextResult();
while(u.Read()) {
int shipmentOrderId = u.GetInt32(0);
/* find the order that goes to this shipment */
Commerce.Order sOrd = orders.Find(delegate(Commerce.Order ord) {
return ord.OrderId == shipmentOrderId;
});
if(sOrd == null) { continue; }
/*
cart.orderId,addressUpdateId,cart.shipmentNumber,tracking,
dateShipped,actualWeight,actualService,actualCost,
actualBilledWeight,packageLength,packageWidth,
packageHeight,thirdPartyAccount,voidStatus,
emailSent,addDate
*/
Shipment shp = new Shipment(sOrd.ShipToAddress, sOrd,
u.GetGuid(1), u.GetString(2), u.GetString(3),
u.GetString(4), u.GetString(5),
u.GetString(6), u.GetString(7),
u.GetString(8), u.GetString(9),
u.GetString(10), u.GetString(11),
u.GetString(12), u.GetString(13),
u.GetDateTime(14), u.GetDateTime(15));
sOrd.Shipments.Add(shp);
}
/* next batch... line detail
cartDetailId, cartDetail.cartId,
inputName, value, cartDetail.sessionId */
u.NextResult();
while(u.Read()) {
LineDetail lineDetail = new LineDetail(
u.GetGuid(0),
u.GetGuid(1),
u.GetGuid(4),
u.GetString(2),
u.GetString(3)
);
/* find the line to attach this line detail to */
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == lineDetail.CartId;
});
if(line != null) {
line.LineDetail.Add(lineDetail);
}
}
/* next batch... form source
* order_line_forms.cartId, sourceCode, formName */
u.NextResult();
while(u.Read()) {
Guid id = u.GetGuid(0);
Line line = lines.Find(delegate(Commerce.Line l) {
return l.CartId == id;
});
if(line != null) {
if(u.IsDBNull(1)) {
line.SourceCode = "";
line.FormName = "NO FORM";
} else {
line.SourceCode = u.GetString(1);
line.FormName = u.GetString(2);
}
}
}
}
}
return orders;
}
private static IEnumerable<SqlDataRecord> BuildTableValueParameter(IEnumerable<string> list, SqlMetaData[] tvpDefinition)
{
var includeList = new List<SqlDataRecord>();
foreach (var item in list)
{
var rec = new SqlDataRecord(tvpDefinition);
rec.SetValue(0, item);
includeList.Add(rec);
}
return includeList;
}
public static void GetConnectedPubSetsTest()
{
List<int> blocks_to_process = new List<int>();
using (var connection = new SqlConnection("context connection=true"))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "select block_id from block_processing order by block_id";
using (SqlDataReader reader = command.ExecuteReader())
while (reader.Read())
//blocks_to_process.Add(Convert.ToInt32(reader.GetInt32(0)));
blocks_to_process.Add(reader.GetInt32(0));
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("block_id", SqlDbType.Int),
new SqlMetaData("cluster", SqlDbType.Int),
new SqlMetaData("ut", SqlDbType.Char, 15),
new SqlMetaData("au_count", SqlDbType.Int)
});
SqlContext.Pipe.SendResultsStart(record);
for (int k = 0; k < blocks_to_process.Count; k++)
{
List<string> pubUT = new List<string>();
List<int> pubAuCount = new List<int>();
List<int> pubMinPairIndex = new List<int>();
//var pubMinPairIndex = new List<Int64>();
List<int> pubMaxPairIndex = new List<int>();
List<int> pairPub2Index = new List<int>();
//command.CommandText = string.Format("select ut, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = {0} order by pub_index", blocks_to_process[k]);
command.CommandText = string.Format("select ut, au_count, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = {0} order by pub_index", blocks_to_process[k]);
//command.CommandText = "select ut, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = " + blocks_to_process[k] + " order by pub_index";
using (SqlDataReader reader = command.ExecuteReader())
while (reader.Read())
{
pubUT.Add(reader.GetString(0));
//pubMinPairIndex.Add(Convert.ToInt32(reader.GetInt64(1)));
pubAuCount.Add(reader.GetInt32(1));
pubMinPairIndex.Add(reader.GetInt32(2));
//pubMaxPairIndex.Add(Convert.ToInt32(reader.GetInt64(2)));
pubMaxPairIndex.Add(reader.GetInt32(3));
}
//command.CommandText = "select pub2_index from tmp_blocks_pub_pairs3 where block_id = " + blocks_to_process[k] + " order by pair_index";
command.CommandText = string.Format("select pub2_index from tmp_blocks_pub_pairs3 where block_id = {0} order by pair_index", blocks_to_process[k]);
using (SqlDataReader reader = command.ExecuteReader())
while (reader.Read())
// pairPub2Index.Add(Convert.ToInt32(reader.GetInt64(0)));
pairPub2Index.Add(reader.GetInt32(0));
ConnectedPubSetSearcher connectedPubSetSearcher = new ConnectedPubSetSearcher(pubMinPairIndex, pubMaxPairIndex, pairPub2Index);
List<List<int>> connectedPubSets = connectedPubSetSearcher.getConnectedPubSets();
//SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
//new SqlMetaData("block_id", SqlDbType.Int),
//new SqlMetaData("cluster", SqlDbType.Int),
//new SqlMetaData("ut", SqlDbType.Char, 15)
// });
//SqlContext.Pipe.SendResultsStart(record);
//foreach (var connectedPubSet in connectedPubSets)
//{
// record.SetValue(1, connectedPubSet);
//}
for (int i = 0; i < connectedPubSets.Count; i++)
{
record.SetValue(0, blocks_to_process[k]);
record.SetValue(1, i);
for (int j = 0; j < connectedPubSets[i].Count; j++)
{
record.SetValue(2, pubUT[connectedPubSets[i][j]]);
record.SetValue(3, pubAuCount[connectedPubSets[i][j]]);
SqlContext.Pipe.SendResultsRow(record);
}
}
//SqlContext.Pipe.SendResultsEnd();
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
public static JsonResponse JsonReadOrDelete(string objectName, int rowFrom, int rowTo, SqlWhere whereClause, Guid accountId, SqlWhere searchClause,
IDictionary<string, string> aggregates, ICollection<int> selectedRows, bool includeSchemaData, Int64 checksum, bool deleteSelection,
string orderBy, OrderDirection orderByDirection, SqlConnection connection)
{
var s = new JsonResponse();
var aggs = new StringBuilder();
var sRows = new StringBuilder();
s.MethodName = "JsonReadOrDelete";
var rows = new List<object>();
s.Add("rows", rows);
// convert aggregate column dictionary to a string
aggregates = aggregates ?? new Dictionary<string, string>();
if(aggregates.Count>0){
foreach(var k in aggregates){
aggs.AppendFormat("{0}|{1},", k.Key, k.Value);
}
// remove trailing comma
aggs.Remove(aggs.Length - 1, 1);
}
selectedRows = selectedRows ?? new Collection<int>();
foreach(var i in selectedRows){
sRows.AppendFormat("{0},",i);
// remove trailing comma
sRows.Remove(aggs.Length - 1, 1);
}
using (var cn = connection ?? CreateConnection()) {
if (cn.State != ConnectionState.Open) { cn.Open(); }
using (var cmd = cn.CreateCommand()) {
cmd.CommandText = _jsonReadOrDeleteQuery;
cmd.CommandType = CommandType.StoredProcedure;
SqlMetaData[] prameterList = {
new SqlMetaData("Name",SqlDbType.VarChar,100),
new SqlMetaData("Type",SqlDbType.VarChar,100),
new SqlMetaData("Length",SqlDbType.VarChar,10),
new SqlMetaData("Value",SqlDbType.Variant)
};
var whereParameterList = new List<SqlDataRecord>();
var searchParameterList = new List<SqlDataRecord>();
whereClause = whereClause ?? new SqlWhere();
searchClause = searchClause ?? new SqlWhere();
foreach(var p in whereClause.Parmeters){
var whereParameter = new SqlDataRecord(prameterList);
whereParameter.SetString(0, p.Name);
whereParameter.SetString(1, p.SqlDbType.ToString());
whereParameter.SetValue(2, p.Length);
whereParameter.SetValue(3, p.Value);
whereParameterList.Add(whereParameter);
}
foreach (var p in searchClause.Parmeters) {
var searchParameter = new SqlDataRecord(prameterList);
searchParameter.SetString(0, p.Name);
searchParameter.SetString(1, p.SqlDbType.ToString());
searchParameter.SetValue(2, p.Length);
searchParameter.SetValue(3, p.Value);
searchParameterList.Add(searchParameter);
}
cmd.Parameters.Add("@objName", SqlDbType.VarChar).Value = objectName;
cmd.Parameters.Add("@record_from", SqlDbType.Int).Value = rowFrom;
cmd.Parameters.Add("@record_to", SqlDbType.Int).Value = rowTo;
cmd.Parameters.Add("@suffix", SqlDbType.VarChar).Value = whereClause.WhereClause;
cmd.Parameters.Add("@accountId", SqlDbType.UniqueIdentifier).Value = accountId;
cmd.Parameters.Add("@searchSuffix", SqlDbType.VarChar).Value = searchClause.WhereClause;
cmd.Parameters.Add("@aggregateColumns", SqlDbType.VarChar).Value = aggs.ToString();
cmd.Parameters.Add("@selectedRowsCSV", SqlDbType.VarChar).Value = sRows.ToString();
cmd.Parameters.Add("@includeSchema", SqlDbType.Bit).Value = includeSchemaData;
cmd.Parameters.Add("@checksum", SqlDbType.BigInt).Value = checksum;
cmd.Parameters.Add("@delete", SqlDbType.Bit).Value = deleteSelection;
cmd.Parameters.Add("@orderBy_override", SqlDbType.VarChar).Value = orderBy;
cmd.Parameters.Add("@orderDirection_override", SqlDbType.VarChar).Value = orderByDirection == OrderDirection.Ascending ? "asc" : "desc";
cmd.Parameters.Add("@whereParameterList", SqlDbType.Structured);
cmd.Parameters["@whereParameterList"].Direction = ParameterDirection.Input;
cmd.Parameters["@whereParameterList"].Value = (whereParameterList.Count == 0 ? null : whereParameterList);
cmd.Parameters.Add("@searchParameterList", SqlDbType.Structured);
cmd.Parameters["@searchParameterList"].Direction = ParameterDirection.Input;
cmd.Parameters["@searchParameterList"].Value = (searchParameterList.Count == 0 ? null : searchParameterList);
using (var r = cmd.ExecuteReader()) {
if(searchClause.WhereClause.Length == 0){
// add range data
var range = new Dictionary<string, object> { { "from", rowFrom }, { "to", rowTo } };
s.Add("range", range);
// first row contains error data
r.Read();
var header = JsonConvert.DeserializeObject<Dictionary<string, object>>(r.GetString(0));
s.Add("header", header);
// pull error and description info from SP result
s.Error = Convert.ToInt32((Int64)header["error"]);
s.Message = (string)header["description"];
// second row contains schema data
r.Read();
var schema = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
s.Add("columns", schema);
// the rest are row data
while (r.Read()) {
var row = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
rows.Add(row);
}
}else {
if(r.HasRows){
s["rows"] = JsonConvert.DeserializeObject<List<int>>(r.GetString(0));
}else {
s["rows"] = new List<int>();
}
}
}
}
}
return s;
}
/// <summary>
/// Pays with existing payment.
/// </summary>
/// <param name="paymentMethodIds">The list of paymentMethodIds.</param>
/// <param name="amount">The amount.</param>
/// <param name="userId">The userId.</param>
/// <param name="postingDate">The posting date.</param>
/// <param name="orderIds">The order ids.</param>
/// <param name="cn">The sql connection (or null).</param>
/// <param name="trans">The sql transaction (or null).</param>
/// <returns>{error:0,desc:"error description"}.</returns>
public static Dictionary<string, object> PayWithExistingPaymentMethods( List<object> paymentMethodIds,
decimal amount, int userId, DateTime postingDate, List<object> orderIds,
SqlConnection cn, SqlTransaction trans )
{
int errorId = 0;
string desc = "";
List<int> intIds = orderIds.ConvertAll( delegate( object i ) {
return Convert.ToInt32( i );
} );
Dictionary<string, object> j = new Dictionary<string, object>();
/* before updating - check to ensure that there really is enouch left over on this paymentMethodId(s)
* to attach the desiered amount to the selected order
*/
using( SqlCommand cmd = new SqlCommand() ) {
List<SqlDataRecord> rec_paymentMethodIds = new List<SqlDataRecord>();
List<SqlDataRecord> rec_orderIds = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
foreach( string id in paymentMethodIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "paymentMethodId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "uniqueidentifier" );
rec.SetValue( 4, 32 );
rec_paymentMethodIds.Add( rec );
}
foreach( int id in intIds ) {
SqlDataRecord rec = new SqlDataRecord( hashTable );
rec.SetValue( 0, "orderId" );
rec.SetValue( 1, id );
rec.SetBoolean( 2, false );
rec.SetString( 3, "int" );
rec.SetValue( 4, 8 );
rec_orderIds.Add( rec );
}
cmd.Connection = cn;
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
/* this SP will return a single row with error, desc saying if the procedure was successfull.
* the SP sums the remaning total value left on the selected paymentMethods and compares
* it to the amount trying to be paid. If the remaining amount is >= the amount trying to
* be paid the payments will be attached, if the remaining amount is < the amoun trying to
* be paid an error will be returned saying as much.
*/
cmd.CommandText = "dbo.attachPaymentMethods";
cmd.Parameters.Add( "@amountTryingToBePaid", SqlDbType.Money ).Value = amount;
cmd.Parameters.Add( "@paymentMethodIds", SqlDbType.Structured );
cmd.Parameters[ "@paymentMethodIds" ].Direction = ParameterDirection.Input;
if( rec_paymentMethodIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@paymentMethodIds" ].Value = rec_paymentMethodIds;
}
cmd.Parameters.Add( "@orderIds", SqlDbType.Structured );
cmd.Parameters[ "@orderIds" ].Direction = ParameterDirection.Input;
if( rec_orderIds.Count == 0 ) {
string message = "You must select at least one payment method.";
message.Debug( 7 );
Exception ex = new Exception( message );
throw ex;
} else {
cmd.Parameters[ "@orderIds" ].Value = rec_orderIds;
}
using( SqlDataReader r = cmd.ExecuteReader() ) {
/* batch 1 is the status */
r.Read();
Dictionary<string, object> s = new Dictionary<string, object>();
errorId = r.GetInt32( 0 );
desc = r.GetString( 1 );
/* NOTE: Addtional callback information for attaching payments to orders
* I don't really care about this stuff so I'm not going to write anything to capture it
* but there is is for anyone who does want to capture it.
*/
/* batch 2 is the actual payment detail inserts (paymentMethodDetailId,paymentMethodId,refId,amount)*/
/* batch 3 is the actual upated orders (orderId, paid) */
}
}
if( errorId != 0 ) {
j.Add( "error", errorId );
j.Add( "description", desc );
} else {
j.Add( "error", 0 );
j.Add( "description", "" );
}
return j;
}
public static void Insert( string paymentType, Guid paymentMethodId, Guid addressId, int userId, Guid sessionId,
int termId, string reference, decimal amount, DateTime postingDate, List<int> orderIds,
string cardName, string cardType, string cardNumber, string expMonth, string expYear,
string secNumber, string routingNumber, string checkNumber, string bankAccountNumber,
string payPalEmailAddress, string swift, string bankName, string routingTransitNumber,
bool cash, string notes, bool _promiseToPay, SqlConnection cn, SqlTransaction trans )
{
String.Format( "Place Order > insertPaymentMethod for userId: {0}, type: {1}", userId, paymentType ).Debug( 7 );
try {
using(SqlCommand cmd=new SqlCommand()) {
List<SqlDataRecord> rowData=new List<SqlDataRecord>();
SqlMetaData[] hashTable= {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s=new StringBuilder();
foreach(int id in orderIds) {
SqlDataRecord rec=new SqlDataRecord(hashTable);
rec.SetValue(0,"orderId");
rec.SetValue(1,id);
rec.SetBoolean(2,false);
rec.SetString(3,"int");
rec.SetValue(4,8);
rowData.Add(rec);
}
cmd.Connection=cn;
cmd.Transaction=trans;
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="dbo.insertPaymentMethod";
cmd.Parameters.Add("@paymentMethodId",SqlDbType.UniqueIdentifier).Value=paymentMethodId;
cmd.Parameters.Add("@paymentType",SqlDbType.VarChar).Value=paymentType;
cmd.Parameters.Add("@cardName",SqlDbType.VarChar).Value=cardName;
cmd.Parameters.Add("@cardType",SqlDbType.VarChar).Value=cardType.MaxLength(25,false);
cmd.Parameters.Add("@cardNumber",SqlDbType.VarChar).Value=cardNumber;
cmd.Parameters.Add("@expMonth",SqlDbType.VarChar).Value=expMonth;
cmd.Parameters.Add("@expYear",SqlDbType.VarChar).Value=expYear;
cmd.Parameters.Add("@secNumber",SqlDbType.VarChar).Value=secNumber;
cmd.Parameters.Add("@userId",SqlDbType.Int).Value=userId;
cmd.Parameters.Add("@sessionId",SqlDbType.UniqueIdentifier).Value=sessionId;
cmd.Parameters.Add("@addressId",SqlDbType.UniqueIdentifier).Value=addressId;
cmd.Parameters.Add("@routingNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@checkNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@bankAccountNumber",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@payPalEmailAddress",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@swift",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@bankName",SqlDbType.VarChar).Value="";
cmd.Parameters.Add("@routingTransitNumber",SqlDbType.VarChar).Value=routingTransitNumber;
cmd.Parameters.Add("@cash",SqlDbType.Bit).Value=cash;
cmd.Parameters.Add("@notes",SqlDbType.VarChar).Value=notes;
cmd.Parameters.Add("@termId",SqlDbType.Int).Value=termId;
cmd.Parameters.Add("@reference",SqlDbType.VarChar).Value=reference;
cmd.Parameters.Add("@amount",SqlDbType.Money).Value=amount;
cmd.Parameters.Add("@promiseToPay",SqlDbType.Bit).Value=_promiseToPay;
cmd.Parameters.Add("@orderIds",SqlDbType.Structured);
cmd.Parameters["@orderIds"].Direction=ParameterDirection.Input;
if(rowData.Count==0) {
cmd.Parameters["@orderIds"].Value=null;
} else {
cmd.Parameters["@orderIds"].Value=rowData;
}
cmd.ExecuteNonQuery();
cmd.Dispose();
}
} catch(Exception ex) {
String.Format("Place Order > insertPaymentMethod exception:{0}",ex.Message).Debug(0);
}
}
private SqlDataRecord SetDbRecord(SqlDataRecord record, object value, int index)
{
if (value.IsPrimitive())
{
var cValue = Convert.ChangeType(value, record.GetFieldType(index));
record.SetValue(index, cValue);
}
else
{
record.SetValue(index, value.ToString());
}
return record;
}
/// <summary>
/// Posts payments to general ledger.
/// </summary>
/// <param name="ids">The payment ids.</param>
/// <param name="postingDate">The posting date.</param>
/// <param name="postingNotes">The posting notes.</param>
/// <param name="preview">if set to <c>true</c> [preview].</param>
/// <returns>{error:0,desc:"error description",preview:false,
/// generalLedgerEntries:{
/// drDate,
/// drDetails,
/// drReference,
/// drAmount,
/// crDate,
/// crDetails,
/// crReference,
/// crAmount
/// },
/// rawGL:{
/// generalLedgerId,
/// creditRecord,
/// debitRecord,
/// amount,
/// userId,
/// termId,
/// addDate,
/// reference,
/// orderId,
/// generalLedgerId
/// },
/// rawGLDetail:{
/// generalLedgerDetailId,
/// generalLedgerId,
/// refId,
/// refType
/// }
/// }.</returns>
public static Dictionary<string, object> PostPaymentsToGeneralLedger(List<object> ids, string postingDate, string postingNotes, bool preview)
{
Dictionary<string, object> j = new Dictionary<string, object>();
List<object> accountantReadable = new List<object>();
List<object> rawGL = new List<object>();
List<object> rawGLDetail = new List<object>();
using(SqlConnection cn = Site.CreateConnection(true, true)) {
cn.Open();
using(SqlTransaction trans = cn.BeginTransaction("paymentPosting")) {
List<Order> orders = new List<Order>();
int error = 0;
string desc = "";
decimal crTotal = 0;
decimal drTotal = 0;
DateTime _postingDate;
if(!DateTime.TryParse(postingDate, out _postingDate)) {
j.Add("preview", preview);
j.Add("error", -2);
j.Add("description", "Posting date is not in the correct format.");
}
if(ids.Count == 0) {
j.Add("preview", preview);
j.Add("error", -1);
j.Add("description", "No orders selected.");
};
List<SqlDataRecord> rowData = new List<SqlDataRecord>();
SqlMetaData[] hashTable = {
new SqlMetaData("keyName",SqlDbType.VarChar,100),
new SqlMetaData("keyValue",SqlDbType.Variant),
new SqlMetaData("primary_key",SqlDbType.Bit),
new SqlMetaData("dataType",SqlDbType.VarChar,50),
new SqlMetaData("dataLength",SqlDbType.Int),
new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
};
StringBuilder s = new StringBuilder();
foreach(object id in ids) {
SqlDataRecord rec = new SqlDataRecord(hashTable);
rec.SetValue(0, "paymentId");
rec.SetValue(1, (new Guid((string)id)).ToString());
rec.SetBoolean(2, false);
rec.SetString(3, "uniqueidentifier");
rec.SetValue(4, 0);
rowData.Add(rec);
}
using(SqlCommand cmd = cn.CreateCommand()) {
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.postPaymentsToGeneralLedger";
cmd.Parameters.Add("@paymentMethodIds", SqlDbType.Structured);
cmd.Parameters["@paymentMethodIds"].Direction = ParameterDirection.Input;
cmd.Parameters["@paymentMethodIds"].Value = rowData;
cmd.Parameters.Add("@unique_site_id", SqlDbType.UniqueIdentifier);
cmd.Parameters["@unique_site_id"].Direction = ParameterDirection.Input;
cmd.Parameters["@unique_site_id"].Value = new Guid(Site.Id.ToString());
cmd.Parameters.Add("@postingDate", SqlDbType.DateTime);
cmd.Parameters["@postingDate"].Direction = ParameterDirection.Input;
cmd.Parameters["@postingDate"].Value = _postingDate;
cmd.Parameters.Add("@referenceNotes", SqlDbType.VarChar);
cmd.Parameters["@referenceNotes"].Direction = ParameterDirection.Input;
cmd.Parameters["@referenceNotes"].Value = postingNotes;
using(SqlDataReader u = cmd.ExecuteReader()) {
u.Read();
error = u.GetInt32(0);
desc = u.GetString(1);
drTotal = u.GetDecimal(2);
crTotal = u.GetDecimal(3);
if(error != 0) {
j.Add("error", error);
j.Add("description", desc);
} else {
j.Add("error", 0);
j.Add("description", "");
}
j.Add("drTotal", drTotal);
j.Add("crTotal", crTotal);
u.NextResult();
/* second batch is an accountant readable GL table - the result of the table vars in the SP
* but not the literal values in the GL, the literal values are next
*/
while(u.Read()) {
/* looks like: userId, handle, addDate, debit, credit, notes */
Dictionary<string, object> row = new Dictionary<string, object>();
row.Add("userId", u.GetInt32(0));
row.Add("handle", u.GetString(1));
row.Add("addDate", u.GetDateTime(2));
row.Add("debit", u.GetDecimal(3));
row.Add("credit", u.GetDecimal(4));
row.Add("notes", u.GetString(5));
accountantReadable.Add(row);
}
/* third batch is the GL entries in the format of the generalLedger table */
u.NextResult();
while(u.Read()) {
Dictionary<string, object> row = new Dictionary<string, object>();
row.Add("generalLedgerId", u.GetGuid(0));
row.Add("refId", u.GetString(1));
row.Add("refType", u.GetInt32(2));
row.Add("creditRecord", u.GetBoolean(3));
row.Add("debitRecord", u.GetBoolean(4));
row.Add("amount", u.GetDecimal(5));
row.Add("userId", u.GetInt32(6));
row.Add("addDate", u.GetDateTime(7));
row.Add("reference", u.GetString(8));
row.Add("generalLedgerInsertId", u.GetGuid(9));
rawGL.Add(row);
}
j.Add("generalLedgerEntries", accountantReadable);
j.Add("rawGL", rawGL);
j.Add("preview", preview);
}
}
if(preview) {
trans.Rollback("paymentPosting");
} else {
trans.Commit();
}
}
}
return j;
}
public static void SendDataTableOverPipe(DataTable tbl)
{
// Build our record schema
List<SqlMetaData> OutputColumns = new List<SqlMetaData>(tbl.Columns.Count);
foreach (DataColumn col in tbl.Columns)
{
SqlMetaData outputColumn;
if (col.DataType == typeof(Int32) || col.DataType == typeof(Int64) || col.DataType == typeof(DateTime))
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType));
}
else if (col.DataType == typeof(Decimal))
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), 12, 10);
}
else
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
}
OutputColumns.Add(outputColumn);
}
// Build our SqlDataRecord and start the results
SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
SqlContext.Pipe.SendResultsStart(record);
// Now send all the rows
foreach (DataRow row in tbl.Rows)
{
for (int col = 0; col < tbl.Columns.Count; col++)
{
record.SetValue(col, row.ItemArray[col]);
}
SqlContext.Pipe.SendResultsRow(record);
}
// And complete the results
SqlContext.Pipe.SendResultsEnd();
}
internal static SqlDataRecord ToSqlDataRecord(this EventRecord record)
{
var sqlDataRecord = new SqlDataRecord(SqlMetaData);
sqlDataRecord.SetValue(0, record.InstanceName ?? string.Empty);
sqlDataRecord.SetValue(1, record.ProviderId);
sqlDataRecord.SetValue(2, record.ProviderName ?? string.Empty);
sqlDataRecord.SetValue(3, record.EventId);
sqlDataRecord.SetValue(4, record.EventKeywords);
sqlDataRecord.SetValue(5, record.Level);
sqlDataRecord.SetValue(6, record.Opcode);
sqlDataRecord.SetValue(7, record.Task);
sqlDataRecord.SetValue(8, record.Timestamp);
sqlDataRecord.SetValue(9, record.Version);
sqlDataRecord.SetValue(10, (object)record.FormattedMessage ?? DBNull.Value);
sqlDataRecord.SetValue(11, (object)record.Payload ?? DBNull.Value);
return sqlDataRecord;
}
internal static SqlDataRecord ToSqlDataRecord(this EventEntry record, string instanceName)
{
var sqlDataRecord = new SqlDataRecord(SqlMetaData);
sqlDataRecord.SetValue(0, instanceName ?? string.Empty);
sqlDataRecord.SetValue(1, record.ProviderId);
sqlDataRecord.SetValue(2, record.Schema.ProviderName ?? string.Empty);
sqlDataRecord.SetValue(3, record.EventId);
sqlDataRecord.SetValue(4, (long)record.Schema.Keywords);
sqlDataRecord.SetValue(5, (int)record.Schema.Level);
sqlDataRecord.SetValue(6, (int)record.Schema.Opcode);
sqlDataRecord.SetValue(7, (int)record.Schema.Task);
sqlDataRecord.SetValue(8, record.Timestamp);
sqlDataRecord.SetValue(9, record.Schema.Version);
sqlDataRecord.SetValue(10, (object)record.FormattedMessage ?? DBNull.Value);
sqlDataRecord.SetValue(11, (object)EventEntryUtil.JsonSerializePayload(record) ?? DBNull.Value);
sqlDataRecord.SetValue(12, record.ActivityId);
sqlDataRecord.SetValue(13, record.RelatedActivityId);
sqlDataRecord.SetValue(14, record.ProcessId);
sqlDataRecord.SetValue(15, record.ThreadId);
return sqlDataRecord;
}
private static void SendTable(SqlDataReader reader, TRowSetMap Map)
{
//SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
DataTable LDataTable = reader.GetSchemaTable();
SqlDataRecord WriteRecord;
List<TFieldAlias> Fields = new List<TFieldAlias>();
TFieldAlias Field;
string FieldName;
int FieldCount = reader.FieldCount, WriteFieldCount = 0;
int i;
SqlMetaData[] WriteFields;
if(Map.Fields.Length > 0)
{
WriteFields = new SqlMetaData[0];
foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
Field.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
Field.Name = FieldMap;
FieldName = FieldMap;
}
for(i = 0; i < FieldCount; i++)
{
if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
Field.FieldIndex = i;
Fields.Add(Field);
Array.Resize(ref WriteFields, ++WriteFieldCount);
//WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
}
}
else
{
WriteFields = new SqlMetaData[FieldCount];
for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
}
WriteRecord = new SqlDataRecord(WriteFields);
try
{
SqlContext.Pipe.SendResultsStart(WriteRecord);
Object[] values = new Object[FieldCount];
while (reader.Read())
{
reader.GetValues(values);
if(Map.Fields.Length > 0)
{
for(i = 0; i < WriteFieldCount; i++)
WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
}
else
{
WriteRecord.SetValues(values);
}
SqlContext.Pipe.SendResultsRow(WriteRecord);
}
}
finally
{
SqlContext.Pipe.SendResultsEnd();
}
}
public static void Query(SqlString profileids, SqlString username, SqlString password, SqlDateTime dateFrom,
SqlDateTime dateTo, SqlString dimensions, SqlString metrics, SqlString sort,
SqlString segments, SqlString filters)
{
// Google Analytics service endpoint
const string dataFeedUrl = "https://www.google.com/analytics/feeds/data";
// Create a GData.net service object to contact the endpoint with our authentication details
AnalyticsService service = new AnalyticsService("Skyscanner Analytics");
service.setUserCredentials(username.ToString(), password.ToString());
// Construct and populate an analytics query object
DataQuery query = new DataQuery(dataFeedUrl);
query.Ids = profileids.ToString();
query.Metrics = metrics.ToString();
query.Dimensions = dimensions.ToString();
query.GAStartDate = dateFrom.Value.ToString("yyyy-MM-dd");
query.GAEndDate = dateTo.Value.ToString("yyyy-MM-dd");
query.Sort = sort.ToString();
query.Segment = segments.ToString();
query.Filters = filters.ToString();
query.NumberToRetrieve = 10000;
// Count the number of metrics and dimensions to be returned
int metricCount = query.Metrics.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Length;
int dimensionCount = query.Dimensions.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries).Length;
// Not possible to query the API without at least one metric, so return immediately if none were specified
if (metricCount == 0) return;
// Gather the results from the Google Analytics API
DataFeed dataFeed = service.Query(query);
// Prepare a set of columns for our SQL result set
SqlMetaData[] columns = new SqlMetaData[metricCount + dimensionCount];
// Iterate through each of the dimensions, and begin populating the column array
DataEntry header = (DataEntry)dataFeed.Entries[0];
for (int i = 0; i < dimensionCount; i++)
{
SqlParameter col = new SqlParameter(header.Dimensions[i].Name, header.Dimensions[i].Value);
columns[i] = new SqlMetaData
(
col.ParameterName,
col.SqlDbType,
SqlMetaData.Max
);
}
// Continue populating the column array with each of the metrics
for (int i = 0; i < metricCount; i++)
{
SqlParameter col = new SqlParameter(header.Metrics[i].Name, header.Metrics[i].Value);
columns[dimensionCount + i] = new SqlMetaData
(
col.ParameterName,
col.SqlDbType,
SqlMetaData.Max
);
}
// Create a placeholder record based on the column metadata
SqlDataRecord record = new SqlDataRecord(columns);
// Set up a pipe to return results to the stored procedure callee
SqlPipe pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
// Iterate through the data feed results
foreach (DataEntry entry in dataFeed.Entries)
{
// Populate each dimension entry in the row
for (int i = 0; i < dimensionCount; i++)
{
record.SetValue(i, entry.Dimensions[i].Value);
}
// Populate each metric entry in the row
for (int i = 0; i < metricCount; i++)
{
record.SetValue(dimensionCount + i, entry.Metrics[i].Value);
}
// Send the result back to the callee
pipe.SendResultsRow(record);
}
// Indicate that the result set is finished
pipe.SendResultsEnd();
}
private SqlDataRecord GetSqlDataRecord(object item)
{
SqlDataRecord record = new SqlDataRecord(_columnList.ToArray());
for (int index = 0; index < _columnList.Count(); index += 1)
{
var valueOfMappedProperty = GetValueOfMappedProperty(item, index);
record.SetValue(index, valueOfMappedProperty);
}
return record;
}
public static Dictionary<string, object> CreateUpdateOrDelete( string objectName, List<object> data, bool overwrite, Int64 commandType )
{
var j = new Dictionary<string, object>();
var rowData = new List<SqlDataRecord>();
SqlMetaData[] rowUpdateTable = {
new SqlMetaData("KeyName",SqlDbType.VarChar,100),
new SqlMetaData("KeyValue",SqlDbType.Variant),
new SqlMetaData("Primary_key",SqlDbType.Bit),
new SqlMetaData("DataType",SqlDbType.VarChar,50),
new SqlMetaData("DataLength",SqlDbType.Int),
new SqlMetaData("VarCharMaxValue",SqlDbType.VarChar,-1)
};
foreach( var field in data ) {
var inner = ( Dictionary<string, object> )field;
var rec = new SqlDataRecord(rowUpdateTable);
var varCharMaxValue = "";
foreach(var innerField in inner ) {
switch (innerField.Key)
{
case "dataType":
rec.SetString( 3, innerField.Value.ToString() );
break;
case "primaryKey":
rec.SetBoolean( 2, Convert.ToBoolean( innerField.Value ) );
break;
case "name":
rec.SetString( 0, innerField.Value.ToString() );
break;
case "value":
varCharMaxValue = innerField.Value.ToString();
rec.SetValue( 1, innerField.Value );
break;
case "dataLength":
rec.SetValue( 4, Convert.ToInt32( innerField.Value ) );
break;
}
}
rec.SetValue( 5, Convert.ToString( varCharMaxValue ) );
rowData.Add( rec );
}
using(var cn = CreateConnection()) {
cn.Open();
using(var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _createOrUpdateQuery;
cmd.Parameters.Add("@objectName", SqlDbType.VarChar, 50);
cmd.Parameters["@objectName"].Direction = ParameterDirection.Input;
cmd.Parameters["@objectName"].Value = objectName;
cmd.Parameters.Add("@row", SqlDbType.Structured);
cmd.Parameters["@row"].Direction = ParameterDirection.Input;
cmd.Parameters["@row"].Value = rowData;
cmd.Parameters.Add("@overwrite", SqlDbType.Bit);
cmd.Parameters["@overwrite"].Direction = ParameterDirection.Input;
cmd.Parameters["@overwrite"].Value = overwrite;
cmd.Parameters.Add("@commandType", SqlDbType.Int);
cmd.Parameters["@commandType"].Direction = ParameterDirection.Input;
cmd.Parameters["@commandType"].Value = Convert.ToInt32(commandType);/* 0 = Update, 1 = insert, 2 = delete*/
using(var u = cmd.ExecuteReader()) {
u.Read();
j.Add("error", u.GetInt32(0));
j.Add("description", u.GetString(1));
j.Add("primaryKey", u.GetValue(2).ToString());
if(commandType == 0) {
if(u.GetInt32(0) == 0) {
j.Add("RowVersion", u.GetValue(3).ToString());
}
} else {
j.Add("RowVersion", -1);
}
j.Add("commandType", commandType.ToString(CultureInfo.InvariantCulture));
}
}
}
return j;
}
