/// <include file='..\..\..\..\..\..\..\..\doc\snippets\Microsoft.Data.SqlClient.Server\SqlDataRecord.xml' path='docs/members[@name="SqlDataRecord"]/SetValue/*' />
public virtual void SetValue(int ordinal, object value)
{
EnsureSubclassOverride();
SqlMetaData metaData = GetSqlMetaData(ordinal);
ExtendedClrTypeCode typeCode = MetaDataUtilsSmi.DetermineExtendedTypeCodeForUseWithSqlDbType(
metaData.SqlDbType, false /* isMultiValued */, value, metaData.Type, SmiVersion);
if (ExtendedClrTypeCode.Invalid == typeCode)
{
throw ADP.InvalidCast();
}
if (SmiVersion >= SmiContextFactory.KatmaiVersion)
{
ValueUtilsSmi.SetCompatibleValueV200(_eventSink, _recordBuffer, ordinal, GetSmiMetaData(ordinal), value, typeCode, 0, 0, null);
}
else
{
ValueUtilsSmi.SetCompatibleValue(_eventSink, _recordBuffer, ordinal, GetSmiMetaData(ordinal), value, typeCode, 0);
}
}
static string MapToSqlDataType(string name, object value)
{
var sqlMetaData = SqlMetaData.InferFromValue(value, name);
var dbType = sqlMetaData.SqlDbType;
switch (dbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
return($"{dbType}({sqlMetaData.MaxLength})");
case SqlDbType.Decimal:
return($"{dbType}({sqlMetaData.Precision},{sqlMetaData.Scale})");
default:
return(dbType.ToString());
}
}
public static void MyHelloProcedure(SqlString name, SqlInt32 number)
{
var pipe = SqlContext.Pipe;
var columns = new SqlMetaData[2];
columns[0] = new SqlMetaData("НомерСтроки", SqlDbType.NVarChar, 100);
columns[1] = new SqlMetaData("Приветствие", SqlDbType.NVarChar, 500);
var row = new SqlDataRecord(columns);
pipe.SendResultsStart(row);
for (int i = 0; i < number; i++)
{
row.SetSqlString(0, string.Format("Строка №{0} ", i));
row.SetSqlString(1, string.Format("Добрый день, уважаемый {0}", name));
pipe.SendResultsRow(row);
}
pipe.SendResultsEnd();
}
private IReadOnlyCollection <SqlDataRecord> MapCategoriesToValues(IReadOnlyCollection <Category> categories)
{
if (categories == null || categories?.Count < 1)
{
return(null);
}
var records = new List <SqlDataRecord>();
var metadata = new SqlMetaData[]
{
new SqlMetaData("CategoryId", SqlDbType.Int)
};
foreach (var item in categories)
{
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetValue(0, item.CategoryId);
records.Add(record);
}
return(records);
}
/// <summary>
/// Converts SqlParameterCollection to be passed to db as SpParametersType.
/// </summary>
/// <param name="comandParameters"> SqlParameterCollection to be converted. </param>
/// <returns> DataTable containing all neccesary comandParameters information. </returns>
private static List <SqlDataRecord> SqlParameterCollectionToDataTable(SqlParameterCollection comandParameters)
{
List <SqlDataRecord> procedureParameters = new List <SqlDataRecord>();
SqlMetaData pName = new SqlMetaData("PName", SqlDbType.NVarChar, 100);
SqlMetaData pType = new SqlMetaData("PType", SqlDbType.NVarChar, 20);
SqlMetaData pValue = new SqlMetaData("PValue", SqlDbType.NVarChar, 4000);
foreach (SqlParameter sqlParam in comandParameters)
{
string paramName = sqlParam.ParameterName;
string paramType = sqlParam.SqlDbType.GetName();
string paramValue = GetSqlParameterStringValue(sqlParam.Value, sqlParam.SqlDbType);
SqlDataRecord sqlDataRecord = new SqlDataRecord(new[] { pName, pType, pValue });
sqlDataRecord.SetString(0, paramName);
sqlDataRecord.SetString(1, paramType);
sqlDataRecord.SetString(2, paramValue);
procedureParameters.Add(sqlDataRecord);
}
return(procedureParameters);
}
public void GetValues_IfValuesBiggerThanColumnCount_LastArrayItemKeptEmpty()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.NVarChar, 50),
new SqlMetaData("col2", SqlDbType.Int)
};
SqlDataRecord record = new SqlDataRecord(metaData);
record.SetString(0, "test");
record.SetSqlInt32(1, 2);
object[] values = new object[5];
int columnCount = record.GetValues(values);
for (int i = 2; i < 5; i++)
{
Assert.Null(values[i]);
}
Assert.Equal(2, columnCount);
}
public override IEnumerator <SqlDataRecord> GetEnumerator()
{
var metaData = new SqlMetaData[1]
{
new SqlMetaData(base._parameterName, base._typeSqlDbTypeMap.GetSqlDbType(base._type))
};
foreach (object item in _tableValuedList)
{
var sqlDataRecord = new SqlDataRecord(metaData);
try
{
sqlDataRecord.SetValues(item);
}
catch (Exception exception)
{
throw new ArgumentException("An error occured while setting SqlDbValues.", exception);
}
yield return(sqlDataRecord);
}
}
public void InsertListTestSqlDataRecord(List <CurveData> data)
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlMetaData[] meta = new SqlMetaData[] {
new SqlMetaData("CurveId", SqlDbType.Int),
new SqlMetaData("TimeStamp", SqlDbType.DateTime),
new SqlMetaData("Value", SqlDbType.Decimal),
};
using (SqlTransaction tran = connection.BeginTransaction())
{
var insertCommand = connection.CreateCommand();
insertCommand.Transaction = tran;
insertCommand.CommandText = "dbo.InsertCurveDataList";
insertCommand.CommandType = System.Data.CommandType.StoredProcedure;
List <SqlDataRecord> records = new List <SqlDataRecord>();
foreach (var item in data)
{
SqlDataRecord r = new SqlDataRecord(meta);
r.SetValues(item.CurveId, item.TimeStamp, item.Value);
records.Add(r);
}
Console.WriteLine("InsertListTestSqlDataRecord: {0} items data converted in {1} ms.", data.Count, watch.ElapsedMilliseconds);
SqlParameter tvparam = insertCommand.Parameters.AddWithValue("@List", records);
tvparam.SqlDbType = System.Data.SqlDbType.Structured;
insertCommand.ExecuteNonQuery();
tran.Commit();
}
}
watch.Stop();
Console.WriteLine("InsertListTestSqlDataRecord: {0} items saved in {1} ms.", data.Count, watch.ElapsedMilliseconds);
}
private IReadOnlyCollection <SqlDataRecord> GetMailRecords(IReadOnlyCollection <Mail> mails)
{
if (mails == null || mails?.Count < 1)
{
return(null);
}
var records = new List <SqlDataRecord>();
var metadata = new SqlMetaData[]
{
new SqlMetaData("MailId", SqlDbType.Int),
new SqlMetaData("Email", SqlDbType.NVarChar, -1)
};
foreach (var item in mails)
{
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetValue(0, item.MailId);
record.SetValue(1, item.Email);
records.Add(record);
}
return(records);
}
private IReadOnlyCollection <SqlDataRecord> GetPhoneRecords(IReadOnlyCollection <Phone> phones)
{
if (phones == null || phones?.Count < 1)
{
return(null);
}
var records = new List <SqlDataRecord>();
var metadata = new SqlMetaData[]
{
new SqlMetaData("PhoneId", SqlDbType.Int),
new SqlMetaData("Number", SqlDbType.NVarChar, 50)
};
foreach (var item in phones)
{
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetValue(0, item.PhoneId);
record.SetValue(1, item.Number);
records.Add(record);
}
return(records);
}
public static void MakeFib(SqlInt32 first, SqlInt32 second, SqlInt32 length)
{
var resultMetadata = new SqlMetaData[2];
resultMetadata[0] = new SqlMetaData("position", SqlDbType.Int);
resultMetadata[1] = new SqlMetaData("value", SqlDbType.Int);
var dataRecord = new SqlDataRecord(resultMetadata);
var pipe = SqlContext.Pipe;
pipe.SendResultsStart(dataRecord);
var position = 1;
dataRecord.SetSqlInt32(0, position);
dataRecord.SetSqlInt32(1, first);
pipe.SendResultsRow(dataRecord);
position += 1;
dataRecord.SetSqlInt32(0, position);
dataRecord.SetSqlInt32(1, second);
pipe.SendResultsRow(dataRecord);
var preceding2 = first;
while (position < length)
{
position += 1;
dataRecord.SetSqlInt32(0, position);
var preceding1 = dataRecord.GetSqlInt32(1);
dataRecord.SetSqlInt32(1, preceding2 + preceding1);
pipe.SendResultsRow(dataRecord);
preceding2 = preceding1;
}
pipe.SendResultsEnd();
}
/// <include file='../../../../../../../../doc/snippets/Microsoft.Data.SqlClient.Server/SqlDataRecord.xml' path='docs/members[@name="SqlDataRecord"]/SetValues/*' />
// ISqlUpdateableRecord Implementation
public virtual int SetValues(params object[] values)
{
EnsureSubclassOverride();
if (values == null)
{
throw ADP.ArgumentNull(nameof(values));
}
// Allow values array longer than FieldCount, just ignore the extra cells.
int copyLength = (values.Length > FieldCount) ? FieldCount : values.Length;
ExtendedClrTypeCode[] typeCodes = new ExtendedClrTypeCode[copyLength];
// Verify all data values as acceptable before changing current state.
for (int i = 0; i < copyLength; i++)
{
SqlMetaData metaData = GetSqlMetaData(i);
typeCodes[i] = MetaDataUtilsSmi.DetermineExtendedTypeCodeForUseWithSqlDbType(
metaData.SqlDbType,
isMultiValued: false,
values[i],
metaData.Type
);
if (typeCodes[i] == ExtendedClrTypeCode.Invalid)
{
throw ADP.InvalidCast();
}
}
// Now move the data (it'll only throw if someone plays with the values array between
// the validation loop and here, or if an invalid UDT was sent).
for (int i = 0; i < copyLength; i++)
{
ValueUtilsSmi.SetCompatibleValueV200(_eventSink, _recordBuffer, i, GetSmiMetaData(i), values[i], typeCodes[i], offset: 0, length: 0, peekAhead: null);
}
return(copyLength);
}
public IEnumerator <SqlDataRecord> GetEnumerator()
{
SqlMetaData[] columnStructure = new SqlMetaData[9];
columnStructure[0] = new SqlMetaData("idOrden", SqlDbType.UniqueIdentifier);
columnStructure[1] = new SqlMetaData("idOrdenExamen", SqlDbType.UniqueIdentifier);
columnStructure[2] = new SqlMetaData("idOrdenMuestra", SqlDbType.UniqueIdentifier);
columnStructure[3] = new SqlMetaData("codigoMuestra", SqlDbType.VarChar, 20);
columnStructure[4] = new SqlMetaData("conforme", SqlDbType.Bit);
columnStructure[5] = new SqlMetaData("laboratorioRecepcion", SqlDbType.Int);
columnStructure[6] = new SqlMetaData("fechaColeccion", SqlDbType.DateTime);
columnStructure[7] = new SqlMetaData("horaColeccion", SqlDbType.DateTime);
columnStructure[8] = new SqlMetaData("idProyecto", SqlDbType.Int);
try
{
List <SqlDataRecord> result = new List <SqlDataRecord>();
foreach (var x in lista)
{
SqlDataRecord dataRecord = new SqlDataRecord(columnStructure);
dataRecord.SetGuid(0, x.OrdenId);
dataRecord.SetGuid(1, x.OrdenExamenId);
dataRecord.SetGuid(2, x.OrdenMuestraId);
dataRecord.SetString(3, x.CodigoMuestra);
dataRecord.SetBoolean(4, x.Conforme);
dataRecord.SetInt32(5, x.EstablecimientoDestinoId);
dataRecord.SetDateTime(6, x.FechaObtencionDT);
dataRecord.SetDateTime(7, x.HoraObtencionDT);
dataRecord.SetInt32(8, x.ProyectoId);
yield return(dataRecord);
}
//return result;
}
// no catch block allowed due to the "yield" command
finally
{
}
}
private SqlMetaData[] ConstructSchemeFromObject(object example)
{
var type = example.GetType();
// Если класс не отнаследован, то просто выбираем все его свойства
_properties = GetPropertiesInRightOrder(type);
var scheme = new List <SqlMetaData>();
foreach (var property in _properties)
{
SqlMetaData data = null;
if (property.PropertyType != typeof(TimeSpan) && property.PropertyType != typeof(DateTimeOffset))
{
var propertyType = property.PropertyType;
propertyType = Nullable.GetUnderlyingType(propertyType) ?? propertyType;
data = InferFromType(property.GetValue(example), propertyType, property.Name);
}
if (property.GetValue(example) != null)
{
data = data ?? SqlMetaData.InferFromValue(property.GetValue(example), property.Name);
}
if (data == null)
{
throw new ArgumentException(
"В TableValuedParemeter был передан некорректный объект для добавления.");
}
scheme.Add(data);
}
return(scheme.ToArray());
}
public static void cp_Lease_Calc()
{
SqlMetaData[] fields = new SqlMetaData[5];
fields[0] = new SqlMetaData("LeaseId", SqlDbType.Int);
fields[1] = new SqlMetaData("LeaseVendor", SqlDbType.NVarChar, 50);
fields[2] = new SqlMetaData("LeaseNumber", SqlDbType.NVarChar, 50);
fields[3] = new SqlMetaData("ContactDate", SqlDbType.DateTime);
fields[4] = new SqlMetaData("TotalAmount", SqlDbType.Money);
// calculate values
// set record
SqlDataRecord record = new SqlDataRecord(fields);
//set data
record.SetInt32(0, 1001);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7890");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 2000);
// send record (set)
SqlContext.Pipe.Send(record);
//set data
record.SetInt32(0, 1002);
record.SetString(1, "LeaseLeaseLease Inc.");
record.SetString(2, "123-456-7891");
record.SetDateTime(3, DateTime.Now);
record.SetSqlMoney(4, 4000);
// send record (set)
SqlContext.Pipe.Send(record);
return;
}
public IEnumerable <SqlDataRecord> ConvertToEMBDataRecord(IEnumerable <EBMModel> parameters)
{
var sqlMetaData = new SqlMetaData[] {
new SqlMetaData("RID", SqlDbType.VarChar, 50),
new SqlMetaData("ChartNo", SqlDbType.VarChar, 50),
new SqlMetaData("STATUS", SqlDbType.VarChar, 5),
};
var mappingDictionarys = sqlMetaData.ToMappingDictionary();
var records = new List <SqlDataRecord>();
if (parameters != null)
{
foreach (var para in parameters)
{
var record = new SqlDataRecord(sqlMetaData);
record.SetStringOrNull(mappingDictionarys["RID"], para.RID);
record.SetStringOrNull(mappingDictionarys["ChartNo"], para.ChartNo);
record.SetStringOrNull(mappingDictionarys["STATUS"], para.Status);
records.Add(record);
}
}
return(records);
}
private SqlParameter CreateOrderedBooksParameter(string parametrName, List <int> bookIds)
{
var rows = new List <SqlDataRecord>();
var sqlMetaData = new SqlMetaData("Id", SqlDbType.Int);
foreach (var id in bookIds)
{
var row = new SqlDataRecord(sqlMetaData);
row.SetValues(id);
rows.Add(row);
}
var orderedBooks = new SqlParameter
{
ParameterName = "@OrderedBooks",
SqlDbType = SqlDbType.Structured,
TypeName = "[dbo].[IdentifierList]",
Direction = ParameterDirection.Input,
Value = rows
};
return(orderedBooks);
}
/// <summary>
/// converts a sql type to a .net type
/// </summary>
/// <param name="sqlType">sql type</param>
/// <returns>.net type</returns>
public static Type SqlToCSharp(SqlMetaData sqlType)
{
sqlType = Arguments.EnsureNotNull(sqlType, nameof(sqlType));
// Based off the following document
// https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
return(sqlType.SqlDbType switch
{
SqlDbType.BigInt => typeof(long),
SqlDbType.DateTimeOffset => typeof(DateTimeOffset),
SqlDbType.DateTime or SqlDbType.Date => typeof(DateTime),
SqlDbType.Decimal or SqlDbType.Money or SqlDbType.SmallMoney => typeof(decimal),
SqlDbType.Int => typeof(int),
SqlDbType.TinyInt => typeof(byte),
SqlDbType.SmallInt => typeof(short),
SqlDbType.Float => typeof(double),
SqlDbType.VarChar or SqlDbType.NVarChar or SqlDbType.NChar or SqlDbType.Char or SqlDbType.Xml => typeof(string),
SqlDbType.UniqueIdentifier => typeof(Guid),
SqlDbType.Bit => typeof(bool),
SqlDbType.Binary or SqlDbType.Image or SqlDbType.VarBinary => typeof(byte[]),
SqlDbType.Timestamp => typeof(byte[]),
_ => throw new InvalidOperationException(FormattableString.Invariant($"unexpected type:{sqlType.SqlDbType}")),
});
public void AddAsParameter(SqlCommand cmd)
{
SqlParameter sqlParam = cmd.CreateParameter();
sqlParam.ParameterName = Name;
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = "dbo.NvarcharTable";
if (_list.Length == 0)
{
sqlParam.Value = _emptyNvarcharTableParam;
}
else
{
sqlParam.Value = _list.Select(t =>
{
SqlMetaData meta = new SqlMetaData("value", SqlDbType.NVarChar, 1000);
SqlDataRecord record = new SqlDataRecord(meta);
record.SetValue(0, t);
return(record);
});
}
cmd.Parameters.Add(sqlParam);
}
private static SqlMetaData SqlMetaDataFromColumn(DataColumn column)
{
SqlMetaData smd = null;
Type clrType = column.DataType;
string name = column.ColumnName;
switch (Type.GetTypeCode(clrType))
{
case TypeCode.String:
smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
break;
case TypeCode.Boolean: smd = new SqlMetaData(name, SqlDbType.Bit); break;
case TypeCode.Byte: smd = new SqlMetaData(name, SqlDbType.TinyInt); break;
case TypeCode.Char: smd = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;
case TypeCode.DateTime: smd = new SqlMetaData(name, SqlDbType.DateTime); break;
case TypeCode.Decimal: smd = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;
case TypeCode.Double: smd = new SqlMetaData(name, SqlDbType.Float); break;
case TypeCode.Int16: smd = new SqlMetaData(name, SqlDbType.SmallInt); break;
case TypeCode.Int32: smd = new SqlMetaData(name, SqlDbType.Int); break;
case TypeCode.Int64: smd = new SqlMetaData(name, SqlDbType.BigInt); break;
case TypeCode.Single: smd = new SqlMetaData(name, SqlDbType.Real); break;
default: throw UnknownDataType(clrType);
}
return(smd);
}
/// <summary>
/// Initializes a new instance of <see cref="MsSqlStreamStoreV3"/>
/// </summary>
/// <param name="settings">A settings class to configure this instance.</param>
public MsSqlStreamStoreV3(MsSqlStreamStoreV3Settings settings)
: base(settings.GetUtcNow, settings.LogName)
{
Ensure.That(settings, nameof(settings)).IsNotNull();
_settings = settings;
_createConnection = () => settings.ConnectionFactory(settings.ConnectionString);
_streamStoreNotifier = new Lazy <IStreamStoreNotifier>(() =>
{
if (settings.CreateStreamStoreNotifier == null)
{
throw new InvalidOperationException(
"Cannot create notifier because supplied createStreamStoreNotifier was null");
}
return(settings.CreateStreamStoreNotifier.Invoke(this));
});
_scripts = new Scripts(settings.Schema);
var sqlMetaData = new List <SqlMetaData>
{
new SqlMetaData("StreamVersion", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),
new SqlMetaData("Id", SqlDbType.UniqueIdentifier),
new SqlMetaData("Created", SqlDbType.DateTime, true, false, SortOrder.Unspecified, -1),
new SqlMetaData("Type", SqlDbType.NVarChar, 128),
new SqlMetaData("JsonData", SqlDbType.NVarChar, SqlMetaData.Max),
new SqlMetaData("JsonMetadata", SqlDbType.NVarChar, SqlMetaData.Max)
};
if (settings.GetUtcNow != null)
{
// Created column value will be client supplied so should prevent using of the column default function
sqlMetaData[2] = new SqlMetaData("Created", SqlDbType.DateTime);
}
_appendToStreamSqlMetadata = sqlMetaData.ToArray();
_commandTimeout = settings.CommandTimeout;
}
private IReadOnlyCollection <SqlDataRecord> GetImageRecords(IReadOnlyCollection <Image> images)
{
if (images == null || images?.Count < 1)
{
return(null);
}
var records = new List <SqlDataRecord>();
var metadata = new SqlMetaData[]
{
new SqlMetaData("ImageId", SqlDbType.Int),
new SqlMetaData("Data", SqlDbType.VarBinary, -1),
new SqlMetaData("Extension", SqlDbType.NVarChar, 50),
};
foreach (var item in images)
{
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetValue(0, item.ImageId);
record.SetValue(1, item.Data);
record.SetString(2, item.Extension);
records.Add(record);
}
return(records);
}
private SqlMetaData GetSqlMetadaFromType(DmColumn column)
{
SqlMetaData smd = null;
var sqlDbType = column.GetSqlDbType();
var dbType = column.DbType;
var precision = column.GetSqlTypePrecision();
int maxLength = column.MaxLength;
if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar)
{
maxLength = column.MaxLength <= 0 ? ((sqlDbType == SqlDbType.NVarChar) ? 4000 : 8000) : column.MaxLength;
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (column.DataType == typeof(char))
{
return(new SqlMetaData(column.ColumnName, sqlDbType, 1));
}
smd = new SqlMetaData(column.ColumnName, sqlDbType);
return(smd);
}
public static void AddParameters(this SqlCommand command, IDictionary <string, object> parameterDictionary)
{
if (parameterDictionary != null)
{
foreach (var param in parameterDictionary)
{
if (param.Value is string[])
{
string[] stringArray = (string[])param.Value;
SqlParameter sqlParam = command.CreateParameter();
sqlParam.ParameterName = param.Key;
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = "dbo.NvarcharTable";
if (stringArray.Length == 0)
{
sqlParam.Value = _emptyNvarcharTableParam;
}
else
{
sqlParam.Value = stringArray.Select(str =>
{
SqlMetaData meta = new SqlMetaData("value", SqlDbType.NVarChar, 1000);
SqlDataRecord record = new SqlDataRecord(meta);
record.SetValue(0, str);
return(record);
});
}
command.Parameters.Add(sqlParam);
}
else
{
command.Parameters.AddWithValue(param.Key, param.Value);
}
}
}
}
//</Snippet1>
//
public static void CreateNewRecord1()
{
//<Snippet2>
// Variables.
SqlMetaData column1Info;
SqlMetaData column2Info;
SqlDataRecord record;
// Create the column metadata.
column1Info = new SqlMetaData("Column1", SqlDbType.NVarChar, 12);
column2Info = new SqlMetaData("Column2", SqlDbType.Int);
// Create a new record with the column metadata.
record = new SqlDataRecord(new SqlMetaData[] { column1Info,
column2Info });
//</Snippet2>
// Set the record fields.
record.SetString(0, "Hello World!");
record.SetInt32(1, 42);
// Send the record to the calling program.
SqlContext.Pipe.Send(record);
}
public static void CLR_Alerts_ConversionAnalysis_Adgroup(Int32 AccountID, Int32 Period, DateTime ToDay, string ChannelID, float CPR_threshold, float CPA_threshold, string excludeIds, string cubeName, string acq1FieldName, string acq2FieldName, string cpaFieldName, string cprFieldName, out SqlString returnMsg, string extraFields)
{
returnMsg = string.Empty;
double totalCost = 0;
double totalAcq1 = 0;
double totalAcq2 = 0;
double avgCPR = 0;
double avgCPA = 0;
#region Exclude
StringBuilder excludeBuilder = new StringBuilder();
//SqlContext.Pipe.Send(excludeIds);
string excludeSyntax = "[Getways Dim].[Gateways].[Campaign].&[{0}].children";
if (!string.IsNullOrEmpty(excludeIds))
{
foreach (string id in excludeIds.Split(','))
{
excludeBuilder.Append(string.Format(excludeSyntax, id));
excludeBuilder.Append(",");
}
}
if (excludeBuilder.Length > 0)
{
excludeBuilder.Remove(excludeBuilder.Length - 1, 1);
}
#endregion
string fromDate = ToDay.AddDays((Double)(-1 * (Period - 1))).ToString("yyyyMMdd");
string toDate = ToDay.ToString("yyyyMMdd");
try
{
StringBuilder withMdxBuilder;
StringBuilder selectMdxBuilder;
StringBuilder fromMdxBuilder;
GetAdgroupMDXQueryParams(AccountID, ChannelID, cubeName, acq1FieldName, acq2FieldName, cpaFieldName, cprFieldName, extraFields, excludeBuilder, fromDate, toDate, out withMdxBuilder, out selectMdxBuilder, out fromMdxBuilder);
#region Creating Command
SqlCommand command = new SqlCommand("dbo.SP_ExecuteMDX");
command.CommandType = CommandType.StoredProcedure;
SqlParameter withMDX = new SqlParameter("WithMDX", withMdxBuilder.ToString());
command.Parameters.Add(withMDX);
SqlParameter selectMDX = new SqlParameter("SelectMDX", selectMdxBuilder.ToString());
command.Parameters.Add(selectMDX);
SqlParameter fromMDX = new SqlParameter("FromMDX", fromMdxBuilder.ToString());
command.Parameters.Add(fromMDX);
#endregion
Dictionary <string, AlertedCampaignAdgroups> campaigns = new Dictionary <string, AlertedCampaignAdgroups>();
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
command.Connection = conn;
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
string campName = Convert.ToString(reader["[Getways Dim].[Gateways].[Campaign].[MEMBER_CAPTION]"]);
AlertedCampaignAdgroups camp = new AlertedCampaignAdgroups();
if (campaigns.TryGetValue(campName, out camp))
{ // if campaign exists than add adgroup to this campaign
camp.AddAdgroup(reader, extraFields, acq1FieldName, acq2FieldName, cpaFieldName, cprFieldName);
}
else // if campaign doesnt exists than create campaign and add adgroup
{
campaigns.Add(campName, new AlertedCampaignAdgroups(reader, extraFields, acq1FieldName, acq2FieldName, cpaFieldName, cprFieldName));
}
}
}
List <AlertedAdgroup> alertedAdgroups = new List <AlertedAdgroup>();
StringBuilder commandBuilder = new StringBuilder();
if (campaigns.Count > 0)
{
CalcTotalsAndAvg(campaigns, out totalCost, out totalAcq1, out totalAcq2, out avgCPA, out avgCPR);
}
SetAdgroupValuePriority(campaigns, avgCPA, avgCPR);
foreach (var camp in campaigns)
{
var alertedAdgroupsPerCampaign = (from ag in camp.Value.AdGroups
where (ag.GetCalculatedCPA() >= CPA_threshold * avgCPA) || (ag.GetCalculatedCPR() >= CPR_threshold * avgCPR)
select ag).OrderBy(val => val.Priority);
alertedAdgroups.AddRange(alertedAdgroupsPerCampaign);
}
alertedAdgroups = alertedAdgroups.OrderByDescending(val => val.Priority).ToList();
//commandBuilder.Append(string.Format("select [Campaign],[Ad Group],[Cost],[{0}],[CPA({1})],[{2}],[CPR({3})] from (", acq2FieldName, cpaFieldName, acq1FieldName, cprFieldName));
SqlMetaData[] cols = new SqlMetaData[]
{
new SqlMetaData("Campaign", SqlDbType.NVarChar, 1024),
new SqlMetaData("AdGroup", SqlDbType.NVarChar, 1024),
new SqlMetaData("Cost", SqlDbType.NVarChar, 1024),
new SqlMetaData(acq2FieldName, SqlDbType.NVarChar, 1024),
new SqlMetaData(string.Format("CPA({0})", cpaFieldName), SqlDbType.NVarChar, 1024),
new SqlMetaData(acq1FieldName, SqlDbType.NVarChar, 1024),
new SqlMetaData(string.Format("CPR({0})", cprFieldName), SqlDbType.NVarChar, 1024),
//new SqlMetaData("P", SqlDbType.NVarChar, 1024)
};
SqlDataRecord rec = new SqlDataRecord(cols);
if (alertedAdgroups.Count == 0)
{
SqlContext.Pipe.Send("Error");
}
SqlContext.Pipe.SendResultsStart(rec);
foreach (AlertedAdgroup adgroup in alertedAdgroups)
{
//if (adgroup.Priority > 2)
// continue;
rec.SetSqlString(0, adgroup.CampaignName);
rec.SetSqlString(1, adgroup.Name);
//cost
rec.SetSqlString(2, string.IsNullOrEmpty((Math.Round(adgroup.Cost, 0)).ToString("#,#", CultureInfo.InvariantCulture)) == true ? "0" : '$' + ((Math.Round(adgroup.Cost, 0)).ToString("#,#", CultureInfo.InvariantCulture)));
//actives
rec.SetSqlString(3, Math.Round(adgroup.Acq2, 0).ToString());
//CPA
rec.SetSqlString(4, string.IsNullOrEmpty((Math.Round(adgroup.CPA, 0)).ToString("#,#", CultureInfo.InvariantCulture)) == true ? "0" : '$' + ((Math.Round(adgroup.CPA, 0)).ToString("#,#", CultureInfo.InvariantCulture)));
//Regs
rec.SetSqlString(5, Math.Round(adgroup.Acq1, 0).ToString());
//CPR
rec.SetSqlString(6, string.IsNullOrEmpty((Math.Round(adgroup.CPR, 0)).ToString("#,#", CultureInfo.InvariantCulture)) == true ? "0" : '$' + ((Math.Round(adgroup.CPR, 0)).ToString("#,#", CultureInfo.InvariantCulture)));
//Priority
//rec.SetSqlString(7, adgroup.Priority.ToString());
SqlContext.Pipe.SendResultsRow(rec);
}
SqlContext.Pipe.SendResultsEnd();
}
}
catch (Exception e)
{
throw new Exception(".Net Exception : " + e.ToString(), e);
}
returnMsg = string.Format("<br><br>Execution Time: {0:dd/MM/yy H:mm} GMT <br><br>Time Period:"
+ "{1} - {2} ({3} Days) <br><strong> AVG CPA: ${4} </strong><br><strong> AVG CPR: ${5} </strong><br>"
+ " Defined CPA Threshold: {6}% <br> Defined CPR Threshold: {7}% <br>",
DateTime.Now,
ToDay.AddDays(-1 * (Period - 1)).ToString("dd/MM/yy"),
ToDay.ToString("dd/MM/yy"),
Period,
Math.Round(avgCPA, 0),
Math.Round(avgCPR, 0),
CPA_threshold * 100,
CPR_threshold * 100
);
}
protected Column(string name, SqlDbType type, bool nullable, byte precision, byte scale)
: this(nullable)
{
Metadata = new SqlMetaData(name, type, precision, scale);
}
protected Column(string name, SqlDbType type, bool nullable, long length)
: this(nullable)
{
Metadata = new SqlMetaData(name, type, length);
}
public static void GeographyAStar(SqlInt32 StartID, SqlInt32 GoalID)
{
/**
* INITIALISATION
*/
// The "Open List" contains the nodes that have yet to be assessed
List <AStarNode> OpenList = new List <AStarNode>();
// The "Closed List" contains the nodes that have already been assessed
// Implemented as a Dictionary<> to enable quick lookup of nodes
Dictionary <int, AStarNode> ClosedList = new Dictionary <int, AStarNode>();
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
conn.Open();
// Retrieve the location of the StartID
SqlCommand cmdGetStartNode = new SqlCommand("SELECT geog4326 FROM Nodes WHERE NodeID = @id", conn);
SqlParameter param = new SqlParameter("@id", SqlDbType.Int);
param.Value = StartID;
cmdGetStartNode.Parameters.Add(param);
object startNode = cmdGetStartNode.ExecuteScalar();
SqlGeography startGeom;
if (startNode != null)
{
startGeom = (SqlGeography)(startNode);
}
else
{
throw new Exception("Couldn't find start node with ID " + StartID.ToString());
}
cmdGetStartNode.Dispose();
// Retrieve the location of the GoalID;
SqlCommand cmdGetEndNode = new SqlCommand("SELECT geog4326 FROM Nodes WHERE NodeID = @id", conn);
SqlParameter endparam = new SqlParameter("@id", SqlDbType.Int);
endparam.Value = GoalID;
cmdGetEndNode.Parameters.Add(endparam);
object endNode = cmdGetEndNode.ExecuteScalar();
SqlGeography endGeom;
if (endNode != null)
{
endGeom = (SqlGeography)(endNode);
}
else
{
throw new Exception("Couldn't find end node with ID " + GoalID.ToString());
}
cmdGetEndNode.Dispose();
conn.Close();
// To start with, the only point we know about is the start node
AStarNode StartNode = new AStarNode(
(int)StartID, // ID of this node
-1, // Start node has no parent
0, // g - the distance travelled so far to get to this node
(double)startGeom.STDistance(endGeom) // h - the estimated remaining distance to the goal
);
// Add the start node to the open list
OpenList.Add(StartNode);
/**
* TRAVERSAL THROUGH THE NETWORK
*/
// So long as there are open nodes to assess
while (OpenList.Count > 0)
{
// Sort the list of open nodes by ascending f score
OpenList.Sort(delegate(AStarNode p1, AStarNode p2)
{ return(p1.f.CompareTo(p2.f)); });
// Consider the open node with lowest f score
AStarNode NodeCurrent = OpenList[0];
/**
* GOAL FOUND
*/
if (NodeCurrent.NodeID == GoalID)
{
// Reconstruct the route to get here
List <SqlGeography> route = new List <SqlGeography>();
int parentID = NodeCurrent.ParentID;
// Keep looking back through nodes until we get to the start (parent -1)
while (parentID != -1)
{
conn.Open();
SqlCommand cmdSelectEdge = new SqlCommand("GetEdgeBetweenNodes", conn);
cmdSelectEdge.CommandType = CommandType.StoredProcedure;
SqlParameter fromOSODRparam = new SqlParameter("@NodeID1", SqlDbType.Int);
SqlParameter toOSODRparam = new SqlParameter("@NodeID2", SqlDbType.Int);
fromOSODRparam.Value = NodeCurrent.ParentID;
toOSODRparam.Value = NodeCurrent.NodeID;
cmdSelectEdge.Parameters.Add(fromOSODRparam);
cmdSelectEdge.Parameters.Add(toOSODRparam);
object edge = cmdSelectEdge.ExecuteScalar();
SqlGeography edgeGeom;
if (edge != null)
{
edgeGeom = (SqlGeography)(edge);
route.Add(edgeGeom);
}
conn.Close();
NodeCurrent = ClosedList[parentID];
parentID = NodeCurrent.ParentID;
}
// Send the results back to the client
SqlMetaData ResultMetaData = new SqlMetaData("Route", SqlDbType.Udt, typeof(SqlGeography));
SqlDataRecord Record = new SqlDataRecord(ResultMetaData);
SqlContext.Pipe.SendResultsStart(Record);
// Loop through route segments in reverse order
for (int k = route.Count - 1; k >= 0; k--)
{
Record.SetValue(0, route[k]);
SqlContext.Pipe.SendResultsRow(Record);
}
SqlContext.Pipe.SendResultsEnd();
return;
} // End if (NodeCurrent.NodeID == GoalID)
/**
* GOAL NOT YET FOUND - IDENTIFY ALL NODES ACCESSIBLE FROM CURRENT NODE
*/
List <AStarNode> Successors = new List <AStarNode>();
conn.Open();
SqlCommand cmdSelectSuccessors = new SqlCommand("GetNodesAccessibleFromNode", conn);
cmdSelectSuccessors.CommandType = CommandType.StoredProcedure;
SqlParameter CurrentNodeOSODRparam = new SqlParameter("@NodeID", SqlDbType.Int);
CurrentNodeOSODRparam.Value = NodeCurrent.NodeID;
cmdSelectSuccessors.Parameters.Add(CurrentNodeOSODRparam);
using (SqlDataReader dr = cmdSelectSuccessors.ExecuteReader())
{
while (dr.Read())
{
// Create a node for this potential successor
AStarNode SuccessorNode = new AStarNode(
dr.GetInt32(0), // NodeID
NodeCurrent.NodeID, // Successor node is a child of the current node
NodeCurrent.g + dr.GetDouble(1), // Additional distance from current node to successor
(double)(((SqlGeography)dr.GetValue(2)).STDistance(endGeom))
);
// Add the end of the list of successors
Successors.Add(SuccessorNode);
}
}
cmdSelectSuccessors.Dispose();
conn.Close();
/**
* Examine list of possible nodes to go next
*/
SqlContext.Pipe.Send("Possible nodes to visit from " + NodeCurrent.NodeID.ToString());
foreach (AStarNode NodeSuccessor in Successors)
{
// Keep track of whether we have already found this node
bool found = false;
// If this node is already on the closed list, it doesn't need to be examined further
if (ClosedList.ContainsKey(NodeSuccessor.NodeID))
{
found = true;
SqlContext.Pipe.Send(NodeSuccessor.NodeID.ToString() + "(" + NodeSuccessor.f.ToString() + ") (already visited)");
}
// If we didn't find the node on the closed list, look for it on the open list
if (!found)
{
for (int j = 0; j < OpenList.Count; j++)
{
if (OpenList[j].NodeID == NodeSuccessor.NodeID)
{
found = true;
SqlContext.Pipe.Send(NodeSuccessor.NodeID.ToString() + "(" + NodeSuccessor.f.ToString() + ") (already on list to consider)");
// If this is a cheaper way to get there
if (OpenList[j].h > NodeSuccessor.h)
{
// Update the route on the open list
OpenList[j] = NodeSuccessor;
}
break;
}
}
}
// If not on either list, add to the open list
if (!found)
{
OpenList.Add(NodeSuccessor);
SqlContext.Pipe.Send(NodeSuccessor.NodeID.ToString() + "(" + NodeSuccessor.f.ToString() + ") (new)");
}
}
// SqlContext.Pipe.Send("---");
// Once all successors have been examined, we've finished with the current node
// so move it to the closed list
OpenList.Remove(NodeCurrent);
ClosedList.Add(NodeCurrent.NodeID, NodeCurrent);
} // end while (OpenList.Count > 0)
SqlContext.Pipe.Send("No route could be found!");
return;
}
}
/// <summary>
/// Do the work of converting a source data object to SqlDataRecords
/// using the parameter attributes to create the table valued parameter definition
/// </summary>
/// <returns></returns>
internal static IEnumerable <SqlDataRecord> TableValuedParameter(IList table)
{
// get the object type underlying our table
Type t = StoredProcedureHelpers.GetUnderlyingType(table.GetType());
// list of converted values to be returned to the caller
List <SqlDataRecord> recordlist = new List <SqlDataRecord>();
// get all mapped properties
PropertyInfo[] props = StoredProcedureHelpers.GetMappedProperties(t);
// get the column definitions, into an array
List <SqlMetaData> columnlist = new List <SqlMetaData>();
// get the propery column name to property name mapping
// and generate the SqlMetaData for each property/column
Dictionary <String, String> mapping = new Dictionary <string, string>();
foreach (PropertyInfo p in props)
{
// default name is property name, override of parameter name by attribute
var attr = p.GetAttribute <StoredProcedureAttributes.Name>();
String name = (null == attr) ? p.Name : attr.Value;
mapping.Add(name, p.Name);
// get column type
var ct = p.GetAttribute <StoredProcedureAttributes.ParameterType>();
SqlDbType coltype = (null == ct) ? SqlDbType.Int : ct.Value;
// create metadata column definition
SqlMetaData column;
switch (coltype)
{
case SqlDbType.Binary:
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.Image:
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.VarBinary:
// get column size
var sa = p.GetAttribute <StoredProcedureAttributes.Size>();
int size = (null == sa) ? 50 : sa.Value;
column = new SqlMetaData(name, coltype, size);
break;
case SqlDbType.Decimal:
// get column precision and scale
var pa = p.GetAttribute <StoredProcedureAttributes.Precision>();
Byte precision = (null == pa) ? (byte)10 : pa.Value;
var sca = p.GetAttribute <StoredProcedureAttributes.Scale>();
Byte scale = (null == sca) ? (byte)2 : sca.Value;
column = new SqlMetaData(name, coltype, precision, scale);
break;
default:
column = new SqlMetaData(name, coltype);
break;
}
// Add metadata to column list
columnlist.Add(column);
}
// load each object in the input data table into sql data records
foreach (object s in table)
{
// create the sql data record using the column definition
SqlDataRecord record = new SqlDataRecord(columnlist.ToArray());
for (int i = 0; i < columnlist.Count(); i++)
{
// locate the value of the matching property
var value = props.Where(p => p.Name == mapping[columnlist[i].Name])
.First()
.GetValue(s, null);
// set the value
record.SetValue(i, value);
}
// add the sql data record to our output list
recordlist.Add(record);
}
// return our list of data records
return(recordlist);
}
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();
}
// compare SmiMetaData to SqlMetaData and determine if they are compatible.
static internal bool IsCompatible(SmiMetaData firstMd, SqlMetaData secondMd) {
return firstMd.SqlDbType == secondMd.SqlDbType &&
firstMd.MaxLength == secondMd.MaxLength &&
firstMd.Precision == secondMd.Precision &&
firstMd.Scale == secondMd.Scale &&
firstMd.CompareOptions == secondMd.CompareOptions &&
firstMd.LocaleId == secondMd.LocaleId &&
firstMd.Type == secondMd.Type &&
firstMd.SqlDbType != SqlDbType.Structured && // SqlMetaData doesn't support Structured types
!firstMd.IsMultiValued; // SqlMetaData doesn't have a "multivalued" option
}
// Convert SqlMetaData instance to an SmiExtendedMetaData instance.
internal static SmiExtendedMetaData SqlMetaDataToSmiExtendedMetaData( SqlMetaData source ) {
// now map everything across to the extended metadata object
string typeSpecificNamePart1 = null;
string typeSpecificNamePart2 = null;
string typeSpecificNamePart3 = null;
if (SqlDbType.Xml == source.SqlDbType) {
typeSpecificNamePart1 = source.XmlSchemaCollectionDatabase;
typeSpecificNamePart2 = source.XmlSchemaCollectionOwningSchema;
typeSpecificNamePart3 = source.XmlSchemaCollectionName;
}
else if (SqlDbType.Udt == source.SqlDbType) {
// Split the input name. UdtTypeName is specified as single 3 part name.
// NOTE: ParseUdtTypeName throws if format is incorrect
string typeName = source.ServerTypeName;
if (null != typeName) {
String[] names = SqlParameter.ParseTypeName(typeName, true /* is for UdtTypeName */);
if (1 == names.Length) {
typeSpecificNamePart3 = names[0];
}
else if (2 == names.Length) {
typeSpecificNamePart2 = names[0];
typeSpecificNamePart3 = names[1];
}
else if (3 == names.Length) {
typeSpecificNamePart1 = names[0];
typeSpecificNamePart2 = names[1];
typeSpecificNamePart3 = names[2];
}
else {
throw ADP.ArgumentOutOfRange("typeName");
}
if ((!ADP.IsEmpty(typeSpecificNamePart1) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart1.Length)
|| (!ADP.IsEmpty(typeSpecificNamePart2) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart2.Length)
|| (!ADP.IsEmpty(typeSpecificNamePart3) && TdsEnums.MAX_SERVERNAME < typeSpecificNamePart3.Length)) {
throw ADP.ArgumentOutOfRange("typeName");
}
}
}
return new SmiExtendedMetaData( source.SqlDbType,
source.MaxLength,
source.Precision,
source.Scale,
source.LocaleId,
source.CompareOptions,
source.Type,
source.Name,
typeSpecificNamePart1,
typeSpecificNamePart2,
typeSpecificNamePart3 );
}
