public override void Set(SqlDataRecord record, int ordinal, short value)
{
record.SetInt16(ordinal, value);
}
void SetValue(SqlDataRecord record, int position, Type type, object value)
{
switch (type.Name)
{
case "Int16":
record.SetInt16(position, (short)value);
break;
case "Int32":
record.SetInt32(position, (int)value);
break;
case "Int64":
record.SetInt64(position, (long)value);
break;
case "Boolean":
record.SetBoolean(position, (bool)value);
break;
case "Byte":
record.SetByte(position, (byte)value);
break;
case "Bytes[]":
record.SetBytes(position, 0, (byte[])value, 0, ((byte[])value).Length);
break;
case "Char":
record.SetChar(position, (char)value);
break;
case "Char[]":
record.SetChars(position, 0, (char[])value, 0, ((char[])value).Length);
break;
case "DateTime":
record.SetDateTime(position, (DateTime)value);
break;
case "Decimal":
record.SetDecimal(position, (decimal)value);
break;
case "Double":
record.SetDouble(position, (double)value);
break;
case "Guid":
record.SetGuid(position, (Guid)value);
break;
case "String":
record.SetSqlString(position, (string)value);
break;
default:
record.SetValue(position, value);
break;
}
}
/// <summary>
/// Returns an enumerator that iterates through the collection.
/// </summary>
/// <returns>A <see cref="T:System.Collections.Generic.IEnumerator`1" /> that can be used to iterate through the collection.</returns>
public IEnumerator <SqlDataRecord> GetEnumerator()
{
if (_data == null || !_data.Any())
{
yield break;
}
PropertyInfo[] properties = _importType.GetProperties();
StringComparer comparer = StringComparer.InvariantCultureIgnoreCase;
this._validator = this._validator ?? new ObjectValidator();
bool?isDynamicType = null;
int errorColumnOrdinal = -1;
var sqlMetaArray = _sqlMetadata.ToArray();
if (_sqlMetadata.Any(x => comparer.Equals(x.Name, _errorColumn)))
{
SqlDataRecord tempRecord = new SqlDataRecord(sqlMetaArray);
errorColumnOrdinal = tempRecord.GetOrdinal(_errorColumn); //will cause an exception if it does not exist, hence the any check
tempRecord = null;
}
foreach (dynamic row in _data)
{
_rowIndex++;
SqlDataRecord record = new SqlDataRecord(sqlMetaArray);
List <string> errors = new List <string>();
//check the first object to see if it is a dynamic type as we dont need to run it throught the object mapper in that case
if (!isDynamicType.HasValue)
{
isDynamicType = FileIOHelpers.IsDynamicType(row);
}
T rowObj = default(T);
if (isDynamicType.Value)
{
try
{
rowObj = FileIOUtilities.MapObject <T>(row, _rowIndex, _validator, _fileValuesMapper, ref errors);
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
}
else
{
rowObj = row;
}
try
{
//built in data annotation validation
this._validator.TryValidate(rowObj, ref errors);
//custom validation
if (_customValidator != null)
{
_customValidator.Invoke(rowObj, ref errors);
}
}
catch (Exception ex)
{
errors.Add(ex.ToString());
}
ISqlRecordMapper mapperObj = null;
//if they provide a custom mapper use that one over the interface.
if (this._customSqlMapper != null)
{
this._customSqlMapper.Invoke(rowObj, record, _rowIndex, errors);
}
else if ((mapperObj = rowObj as ISqlRecordMapper) != null)
{
mapperObj.MapSqlRecord(record, _rowIndex, errors);
}
else //last ditch effort, hopefully they don't rely on this
{
object val;
//try to set the rows from the metadata, and the properties
foreach (SqlMetaData metaData in _sqlMetadata)
{
string name = metaData.Name;
val = null;
if (!comparer.Equals(name, _errorColumn))
{
var prop = properties.FirstOrDefault(x => comparer.Equals(x.Name, name));
if (prop != null && (val = prop.GetValue(rowObj, null)) != null)
{
record.SetValue(record.GetOrdinal(name), val);
}
}
}
//if an error column is defined, set the import errors
if (errorColumnOrdinal != -1 && errors.Count != 0)
{
string errorMessage = FileIOHelpers.ErrorsToXml(errors, _rowIndex);
record.SetString(errorColumnOrdinal, errorMessage);
}
}
yield return(record);
}
}
static IEnumerable <SqlDataRecord> ToTaskMessageParameter(TaskMessage msg)
{
var record = new SqlDataRecord(TaskEventSchema);
yield return(PopulateTaskMessageRecord(msg, record, eventPayloadMap: null));
}
public static void GetTable(string connectionString, string tableName)
{
var metaCount = 0;
var fieldNames = new List <string>();
//--use: "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;OLE DB Services=-4;" for SQL Compact 3.1
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;OLE DB Services=-4;" for SQL Compact 3.5 SP2
//--use: "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;OLE DB Services=-4;" for SQL Compact 4.0
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
// determine the number of SqlMetadata parameters needed
using (var cmd = new OleDbCommand())
{
cmd.CommandText = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @p1 ORDER BY ORDINAL_POSITION";
cmd.Parameters.Add(new OleDbParameter("@p1", OleDbType.VarWChar, 128));
cmd.Parameters[0].Value = tableName;
cmd.Connection = conn;
using (var rdr = cmd.ExecuteReader())
{
while (rdr != null && rdr.Read())
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send(rdr[1].ToString());
}
if (rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
{
continue;
}
metaCount++;
fieldNames.Add("[" + rdr[0] + "]");
}
}
}
if (metaCount == 0)
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send("No data found, or table does not exist");
}
return;
}
//Get the meta data for the fields
var metadata = GetMetaData(metaCount, tableName, conn);
var record = new SqlDataRecord(metadata);
var fields = new System.Text.StringBuilder();
foreach (var field in fieldNames)
{
fields.Append(field);
fields.Append(", ");
}
fields.Remove(fields.Length - 2, 2);
using (var cmd = new OleDbCommand("SELECT " + fields + " FROM [" + tableName + "]", conn))
{
using (var rdr = cmd.ExecuteReader())
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send(cmd.CommandText);
SqlContext.Pipe.SendResultsStart(record);
while (rdr != null && rdr.Read())
{
for (var i = 0; i < rdr.FieldCount; i++)
{
if (rdr.IsDBNull(i))
{
record.SetDBNull(i);
}
else
{
if (metadata[i].SqlDbType == SqlDbType.Bit)
{
record.SetBoolean(i, rdr.GetBoolean(i));
}
if (metadata[i].SqlDbType == SqlDbType.TinyInt)
{
record.SetByte(i, rdr.GetByte(i));
}
if (metadata[i].SqlDbType == SqlDbType.SmallInt)
{
record.SetInt16(i, rdr.GetInt16(i));
}
if (metadata[i].SqlDbType == SqlDbType.Int)
{
record.SetInt32(i, rdr.GetInt32(i));
}
if (metadata[i].SqlDbType == SqlDbType.BigInt)
{
record.SetInt64(i, rdr.GetInt64(i));
}
if (metadata[i].SqlDbType == SqlDbType.NVarChar || metadata[i].SqlDbType == SqlDbType.NChar)
{
record.SetString(i, rdr.GetString(i));
}
if (metadata[i].SqlDbType == SqlDbType.UniqueIdentifier)
{
record.SetGuid(i, rdr.GetGuid(i));
}
if (metadata[i].SqlDbType == SqlDbType.Timestamp || metadata[i].SqlDbType == SqlDbType.Binary || metadata[i].SqlDbType == SqlDbType.VarBinary)
{
var tsbuffer = (byte[])rdr[i];
record.SetBytes(i, 0, tsbuffer, 0, tsbuffer.Length);
}
if (metadata[i].SqlDbType == SqlDbType.DateTime)
{
record.SetDateTime(i, rdr.GetDateTime(i));
}
if (metadata[i].SqlDbType == SqlDbType.Money || metadata[i].SqlDbType == SqlDbType.Decimal)
{
record.SetDecimal(i, rdr.GetDecimal(i));
}
if (metadata[i].SqlDbType == SqlDbType.Float)
{
record.SetDouble(i, rdr.GetDouble(i));
}
if (metadata[i].SqlDbType == SqlDbType.Real)
{
record.SetSqlSingle(i, Convert.ToSingle(rdr.GetValue(i)));
}
}
}
//Send the completed record..
SqlContext.Pipe.SendResultsRow(record);
}
if (rdr != null)
{
rdr.Close();
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
conn.Close();
}
}
public async Task AddTransactions(IEnumerable <ByteString> transactions)
{
using (await m_lock.LockAsync())
{
using (SqlTransaction context = Connection.BeginTransaction(IsolationLevel.Snapshot))
{
try
{
foreach (ByteString rawTransaction in transactions)
{
byte[] rawTransactionBuffer = rawTransaction.ToByteArray();
Transaction transaction = MessageSerializer.DeserializeTransaction(rawTransaction);
byte[] transactionHash = MessageSerializer.ComputeHash(rawTransactionBuffer);
byte[] mutationHash = MessageSerializer.ComputeHash(transaction.Mutation.ToByteArray());
Mutation mutation = MessageSerializer.DeserializeMutation(transaction.Mutation);
IReadOnlyList <Record> conflicts = await ExecuteQuery <Record>(
"EXEC [Openchain].[AddTransaction] @instance, @transactionHash, @mutationHash, @rawData, @records;",
reader => mutation.Records.First(record => record.Key.Equals(new ByteString((byte[])reader[0]))),
new Dictionary <string, object>()
{
["instance"] = this.instanceId,
["transactionHash"] = transactionHash,
["mutationHash"] = mutationHash,
["rawData"] = rawTransactionBuffer,
["type:records"] = "Openchain.RecordMutationTable",
["records"] = mutation.Records.Select(record =>
{
SqlDataRecord result = new SqlDataRecord(recordMutationMetadata);
RecordKey key = ParseRecordKey(record.Key);
result.SetBytes(0, 0, record.Key.ToByteArray(), 0, record.Key.Value.Count);
if (record.Value == null)
{
result.SetDBNull(1);
}
else
{
result.SetBytes(1, 0, record.Value.ToByteArray(), 0, record.Value.Value.Count);
}
result.SetBytes(2, 0, record.Version.ToByteArray(), 0, record.Version.Value.Count);
result.SetString(3, key.Name);
result.SetByte(4, (byte)key.RecordType);
return(result);
}).ToList()
},
context);
if (conflicts.Count > 0)
{
throw new ConcurrentMutationException(conflicts[0]);
}
}
context.Commit();
}
catch (Exception ex)
{
if (!(ex is ConcurrentMutationException))
{
var excep = ex;
}
throw;
}
}
}
}
private static List <object[]> GetObjectArrayFromSQL(string strQuery, string strConnection, string strProvider, DataTable dt, string[] operators, out SqlDataRecord record, bool addLevel)
{
record = null;
List <object[]> listObjects = new List <object[]>();
DbProviderFactory factory = DbProviderFactories.GetFactory(strProvider);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object (i.e. SqlConnection ...)
if (connection != null)
{
connection.ConnectionString = strConnection;
try
{
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
selectCommand.CommandText = strQuery;
if (dap != null)
{
DbDataReader reader = selectCommand.ExecuteReader();
List <SqlMetaData> outputColumns = new List <SqlMetaData>();
object[] recordObjectStart = new object[reader.FieldCount];
//only if data is available
if (reader.Read())
{
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObjectStart[iCol] = (object)reader[iCol];
if (iCol >= operators.Length)
{
DataColumn col = new DataColumn(reader.GetName(iCol), reader.GetFieldType(iCol));
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
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
}
outputColumns.Add(outputColumn);
//Check if column name already exists
if (!dt.Columns.Contains(col.ColumnName))
{
dt.Columns.Add(col);
}
else
{
throw new Exception("Column name '" + col.ColumnName + "' already exists. Use an alias instead.");
}
}
}
}
listObjects.Add(recordObjectStart);
//add level column for multiple skyline algorithms
if (addLevel)
{
SqlMetaData outputColumnLevel = new SqlMetaData("level", TypeConverter.ToSqlDbType(typeof(Int32)));
outputColumns.Add(outputColumnLevel);
}
record = new SqlDataRecord(outputColumns.ToArray());
//Now save all records to array (Profiling: faster than working with the reader in the algorithms)
while (reader.Read())
{
object[] recordObject = new object[reader.FieldCount];
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObject[iCol] = (object)reader[iCol];
}
listObjects.Add(recordObject);
}
reader.Close();
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(listObjects);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("Caterer_ID", SqlDbType.Int),
new SqlMetaData("Caterer_Name", SqlDbType.VarChar, -1),
new SqlMetaData("Username", SqlDbType.VarChar, -1),
new SqlMetaData("Password", SqlDbType.VarChar, -1),
new SqlMetaData("Email", SqlDbType.VarChar, -1),
new SqlMetaData("Country", SqlDbType.VarChar, -1),
new SqlMetaData("City", SqlDbType.VarChar, -1),
new SqlMetaData("Buffet_PH", SqlDbType.VarChar, -1),
new SqlMetaData("Speciality", SqlDbType.VarChar, -1),
new SqlMetaData("Office_Contact", SqlDbType.VarChar, -1),
new SqlMetaData("Office_Hours_From", SqlDbType.VarChar, -1),
new SqlMetaData("Office_Hours_To", SqlDbType.VarChar, -1),
new SqlMetaData("Website_Url", SqlDbType.VarChar, -1),
new SqlMetaData("Facebook_Page", SqlDbType.VarChar, -1),
new SqlMetaData("Longitude", SqlDbType.VarChar, -1),
new SqlMetaData("Latitude", SqlDbType.VarChar, -1),
new SqlMetaData("Img1", SqlDbType.VarChar, -1),
new SqlMetaData("Img2", SqlDbType.VarChar, -1),
new SqlMetaData("Img3", SqlDbType.VarChar, -1),
new SqlMetaData("Img4", SqlDbType.VarChar, -1),
new SqlMetaData("Img5", SqlDbType.VarChar, -1),
new SqlMetaData("Isactive", SqlDbType.Bit),
new SqlMetaData("IsAdminAproved", SqlDbType.Bit)
);
foreach (Caterer data in this)
{
if (CheckNullOrEmpty(data.Caterer_ID))
{
ret.SetDBNull(0);
}
else
{
ret.SetInt32(0, data.Caterer_ID);
}
if (CheckNullOrEmpty(data.Caterer_Name))
{
ret.SetDBNull(1);
}
else
{
ret.SetString(1, data.Caterer_Name);
}
if (CheckNullOrEmpty(data.Username))
{
ret.SetDBNull(2);
}
else
{
ret.SetString(2, data.Username);
}
if (CheckNullOrEmpty(data.Password))
{
ret.SetDBNull(3);
}
else
{
ret.SetString(3, data.Password);
}
if (CheckNullOrEmpty(data.Email))
{
ret.SetDBNull(4);
}
else
{
ret.SetString(4, data.Email);
}
if (CheckNullOrEmpty(data.Country))
{
ret.SetDBNull(5);
}
else
{
ret.SetString(5, data.Country);
}
if (CheckNullOrEmpty(data.City))
{
ret.SetDBNull(6);
}
else
{
ret.SetString(6, data.City);
}
if (CheckNullOrEmpty(data.Buffet_PH))
{
ret.SetDBNull(7);
}
else
{
ret.SetString(7, data.Buffet_PH);
}
if (CheckNullOrEmpty(data.Speciality))
{
ret.SetDBNull(8);
}
else
{
ret.SetString(8, data.Speciality);
}
if (CheckNullOrEmpty(data.Office_Contact))
{
ret.SetDBNull(9);
}
else
{
ret.SetString(9, data.Office_Contact);
}
if (CheckNullOrEmpty(data.Office_Hours_From))
{
ret.SetDBNull(10);
}
else
{
ret.SetString(10, data.Office_Hours_From);
}
if (CheckNullOrEmpty(data.Office_Hours_To))
{
ret.SetDBNull(11);
}
else
{
ret.SetString(11, data.Office_Hours_To);
}
if (CheckNullOrEmpty(data.Website_Url))
{
ret.SetDBNull(12);
}
else
{
ret.SetString(12, data.Website_Url);
}
if (CheckNullOrEmpty(data.Facebook_Page))
{
ret.SetDBNull(13);
}
else
{
ret.SetString(13, data.Facebook_Page);
}
if (CheckNullOrEmpty(data.Longitude))
{
ret.SetDBNull(14);
}
else
{
ret.SetString(14, data.Longitude);
}
if (CheckNullOrEmpty(data.Latitude))
{
ret.SetDBNull(15);
}
else
{
ret.SetString(15, data.Latitude);
}
if (CheckNullOrEmpty(data.Img1))
{
ret.SetDBNull(16);
}
else
{
ret.SetString(16, data.Img1);
}
if (CheckNullOrEmpty(data.Img2))
{
ret.SetDBNull(17);
}
else
{
ret.SetString(17, data.Img2);
}
if (CheckNullOrEmpty(data.Img3))
{
ret.SetDBNull(18);
}
else
{
ret.SetString(18, data.Img3);
}
if (CheckNullOrEmpty(data.Img4))
{
ret.SetDBNull(19);
}
else
{
ret.SetString(19, data.Img4);
}
if (CheckNullOrEmpty(data.Img5))
{
ret.SetDBNull(20);
}
else
{
ret.SetString(20, data.Img5);
}
if (CheckNullOrEmpty(data.Isactive))
{
ret.SetDBNull(21);
}
else
{
ret.SetBoolean(21, (Boolean)data.Isactive);
}
if (CheckNullOrEmpty(data.IsAdminAproved))
{
ret.SetDBNull(22);
}
else
{
ret.SetBoolean(22, (Boolean)data.IsAdminAproved);
}
yield return(ret);
}
}
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);
SqlContext.Pipe.Send(withMdxBuilder.ToString());
SqlContext.Pipe.Send(selectMdxBuilder.ToString());
SqlContext.Pipe.Send(fromMdxBuilder.ToString());
#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) && ag.Priority >= 0)
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
);
}
public static void GetSumReport(int type, int userID, int level, int debug, DateTime dts, DateTime dte)
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
if (1 == debug)
{
SqlContext.Pipe.Send(string.Format("dts：{0}-dte：{1}", dts, dte));
SqlContext.Pipe.Send(string.Format("dts：{0}-dte：{1}", dts, dte));
}
connection.Open();
var command = connection.CreateCommand();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
string runSQL = string.Empty;
switch (type)
{
case 0:
runSQL = "SELECT u001 FROM wgs049(NOLOCK) WHERE u012=@UserID";
break;
case 1:
runSQL = "SELECT u002 FROM wgs048(NOLOCK) AS ur WHERE ur.u001=@UserID AND ur.u002<>@UserID AND ur.u002 NOT IN (SELECT u001 FROM wgs049(NOLOCK) AS u WHERE u.u012=@UserID);";
break;
case 2:
runSQL = "SELECT u002 FROM wgs048(NOLOCK) AS ur WHERE ur.u001=@UserID AND ur.u002<>@UserID;";
break;
case 3:
runSQL = "SELECT u002 FROM wgs048(NOLOCK) AS ur WHERE ur.u001=@UserID AND ur.u002<>@UserID AND ur.u002 NOT IN (SELECT u001 FROM wgs049(NOLOCK) AS u WHERE u.u012=@UserID);";
break;
}
if (0 == runSQL.Length)
{
if (1 == debug)
{
pipe.Send("句子不能为空");
}
}
else
{
command.CommandText = runSQL;
command.Parameters.AddWithValue("@UserID", userID);
da.SelectCommand = command;
da.Fill(ds, "UserIDs");
StringBuilder ids = new StringBuilder();
string IDs = string.Empty;
List <int> userIDList = new List <int>();
if (0 < ds.Tables["UserIDs"].Rows.Count)
{
foreach (DataRow item in ds.Tables["UserIDs"].Rows)
{
ids.Append(item[0].ToString() + ",");
}
IDs = ids.ToString();
IDs = IDs.Substring(0, IDs.Length - 1);
if (0 < ids.ToString().Length)
{
if (1 == debug)
{
pipe.Send(string.Format("IDS：{0}", IDs));
}
}
}
else
{
if (1 == debug)
{
pipe.Send("找不到用户");
}
}
if (0 < IDs.Length)
{
command.Parameters.Clear();
command.CommandText = string.Format("SELECT COUNT(1) AS u001, '' AS u002, SUM(dr004) AS dr004,SUM(dr005) AS dr005,SUM(dr006) AS dr006,SUM(dr007) AS dr007,SUM(dr008) AS dr008, SUM(dr009) AS dr009,SUM(dr010) AS dr010,SUM(dr011) AS dr011,SUM(dr012) AS dr012,SUM(dr013) AS dr013,SUM(dr014) AS dr014,SUM(dr015) AS dr015,SUM(dr016) AS dr016, SUM(dr017) AS dr017,SUM(dr018) AS dr018 FROM wgs042(NOLOCK) WHERE u001 IN({0}) AND dr002 >='{1}' AND dr002 <= '{2}';", IDs, dts, dte);
if (1 == debug)
{
pipe.Send(command.CommandText);
}
SqlDataReader dr = command.ExecuteReader();
pipe.Send(dr);
}
else
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("u001", SqlDbType.Int),
new SqlMetaData("u002", SqlDbType.Char, 32),
new SqlMetaData("dr004", SqlDbType.Decimal),
new SqlMetaData("dr005", SqlDbType.Decimal),
new SqlMetaData("dr006", SqlDbType.Decimal),
new SqlMetaData("dr007", SqlDbType.Decimal),
new SqlMetaData("dr008", SqlDbType.Decimal),
new SqlMetaData("dr009", SqlDbType.Decimal),
new SqlMetaData("dr010", SqlDbType.Decimal),
new SqlMetaData("dr011", SqlDbType.Decimal),
new SqlMetaData("dr012", SqlDbType.Decimal),
new SqlMetaData("dr013", SqlDbType.Decimal),
new SqlMetaData("dr014", SqlDbType.Decimal),
new SqlMetaData("dr015", SqlDbType.Decimal),
new SqlMetaData("dr016", SqlDbType.Decimal),
new SqlMetaData("dr017", SqlDbType.Decimal),
new SqlMetaData("dr018", SqlDbType.Decimal)
);
record.SetValues(new object[] { 0, string.Empty, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m, 0.0000m });
pipe.Send(record);
}
}
}
}
public static SqlGeometry GeometryAlphaShape(SqlGeometry MultiPoint, SqlDouble alpha)
{
// Retrieve the SRID
int srid = (int)MultiPoint.STSrid;
// Check valid input
if (!(MultiPoint.STGeometryType() == "MULTIPOINT" && MultiPoint.STNumPoints() > 3))
{
throw new ArgumentException("Input must be a MultiPoint containing at least three points");
}
// Initialise a list of vertices
List <SimplePoint> Vertices = new List <SimplePoint>();
// Add all the original supplied points
for (int i = 1; i <= MultiPoint.STNumPoints(); i++)
{
SimplePoint Point = new SimplePoint((double)MultiPoint.STPointN(i).STX, (double)MultiPoint.STPointN(i).STY);
// MultiPoints can contain the same point twice, but this messes up Delauney
if (!Vertices.Contains(Point))
{
Vertices.Add(Point);
}
}
// Important - count the number of points in the array, NOT using STNumPoints of the supplied geometry, as some duplicate points
// may have been removed
int numPoints = Vertices.Count;
// Important! Sort the list so that points sweep from left - right
Vertices.Sort();
// Calculate the "supertriangle" that encompasses the pointset
SqlGeometry Envelope = MultiPoint.STEnvelope();
// Width
double dx = (double)(Envelope.STPointN(2).STX - Envelope.STPointN(1).STX);
// Height
double dy = (double)(Envelope.STPointN(4).STY - Envelope.STPointN(1).STY);
// Maximum dimension
double dmax = (dx > dy) ? dx : dy;
// Centre
double avgx = (double)Envelope.STCentroid().STX;
double avgy = (double)Envelope.STCentroid().STY;
// Create the points at corners of the supertriangle
SimplePoint a = new SimplePoint(avgx - 2 * dmax, avgy - dmax);
SimplePoint b = new SimplePoint(avgx + 2 * dmax, avgy - dmax);
SimplePoint c = new SimplePoint(avgx, avgy + 2 * dmax);
// Add the supertriangle vertices to the end of the vertex array
Vertices.Add(a);
Vertices.Add(b);
Vertices.Add(c);
double radius;
SimplePoint circumcentre;
CalculateCircumcircle(a, b, c, out circumcentre, out radius);
// Create a triangle from the vertices
SimpleTriangle SuperTriangle = new SimpleTriangle(numPoints, numPoints + 1, numPoints + 2, circumcentre, radius);
// Add the supertriangle to the list of triangles
List <SimpleTriangle> Triangles = new List <SimpleTriangle>();
Triangles.Add(SuperTriangle);
List <SimpleTriangle> CompletedTriangles = new List <SimpleTriangle>();
// Loop through each point
for (int i = 0; i < numPoints; i++)
{
// Initialise the edge buffer
List <int[]> Edges = new List <int[]>();
// Loop through each triangle
for (int j = Triangles.Count - 1; j >= 0; j--)
{
// If the point lies within the circumcircle of this triangle
if (Distance(Triangles[j].circumcentre, Vertices[i]) < Triangles[j].radius)
{
// Add the triangle edges to the edge buffer
Edges.Add(new int[] { Triangles[j].a, Triangles[j].b });
Edges.Add(new int[] { Triangles[j].b, Triangles[j].c });
Edges.Add(new int[] { Triangles[j].c, Triangles[j].a });
// Remove this triangle from the list
Triangles.RemoveAt(j);
}
// If this triangle is complete
else if (Vertices[i].x > Triangles[j].circumcentre.x + Triangles[j].radius)
{
{
CompletedTriangles.Add(Triangles[j]);
}
Triangles.RemoveAt(j);
}
}
// Remove duplicate edges
for (int j = Edges.Count - 1; j > 0; j--)
{
for (int k = j - 1; k >= 0; k--)
{
// Compare if this edge match in either direction
if (Edges[j][0].Equals(Edges[k][1]) && Edges[j][1].Equals(Edges[k][0]))
{
// Remove both duplicates
Edges.RemoveAt(j);
Edges.RemoveAt(k);
// We've removed an item from lower down the list than where j is now, so update j
j--;
break;
}
}
}
// Create new triangles for the current point
for (int j = 0; j < Edges.Count; j++)
{
CalculateCircumcircle(Vertices[Edges[j][0]], Vertices[Edges[j][1]], Vertices[i], out circumcentre, out radius);
SimpleTriangle T = new SimpleTriangle(Edges[j][0], Edges[j][1], i, circumcentre, radius);
Triangles.Add(T);
}
}
// We've finished triangulation. Move any remaining triangles onto the completed list
CompletedTriangles.AddRange(Triangles);
// Define the metadata of the results column
SqlMetaData metadata = new SqlMetaData("Triangle", SqlDbType.Udt, typeof(SqlGeometry));
// Create a record based on this metadata
SqlDataRecord record = new SqlDataRecord(metadata);
// Send the results back to the client
SqlGeometry result = new SqlGeometry();
result.STSrid = srid;
foreach (SimpleTriangle Tri in CompletedTriangles)
{
// Only include triangles whose radius is less than supplied alpha
if (Tri.radius < alpha)
{
SqlGeometry triangle = TriangleFromPoints(Vertices[Tri.a], Vertices[Tri.b], Vertices[Tri.c], srid);
// Create union of all matching triangles
result = result.STUnion(triangle);
}
}
return(result);
}
/// <summary>
/// Searches Active Directory according provided parameters
/// </summary>
/// <param name="userName">UserName to be used to authenticate AD</param>
/// <param name="password">Password to be used to authenticate to AD</param>
/// <param name="authType">Authentication type to be used to authenticate to AD</param>
/// <param name="adRoot">AD Root for querying AD</param>
/// <param name="filter">Filter to be used for querying</param>
/// <param name="searchScope">Scope to be used for queryingg</param>
/// <param name="propertiesToLoad">List of properties to return</param>
/// <param name="pageSize">Represents a PageSise for the paged search of AD</param>
/// <param name="rowsLimit">Rrepresent limit for numbers of rows returned. NULL or value less than 1 represents unlimited</param>
private static void SearchAD(string userName, string password, string authType, string adRoot, string filter, string searchScope, string propertiesToLoad, int pageSize, SqlInt32 rowsLimit)
{
string[] properties = propertiesToLoad.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
ADPropertyInfo[] adProperties = new ADPropertyInfo[properties.Length];
SqlMetaData[] recordMetaData = new SqlMetaData[properties.Length];
SearchScope scope;
Type et = typeof(Encoding); //Encoding Type
int limit = rowsLimit.IsNull ? 0 : rowsLimit.Value;
int rowsCount = 0;
if (rowsLimit > 0 && pageSize > limit)
{
pageSize = limit;
}
if (!TryParseEnum <SearchScope>(searchScope, true, out scope))
{
throw new System.InvalidCastException(string.Format("searchScope must be one of '{0}'", GetEnumNames <SearchScope>()));
}
//Trim properties and prepare result set metadata, also process specified lengths
for (int i = 0; i < properties.Length; i++)
{
string[] propDetails = properties[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); //Properties detals - Split to Name, Length, Decoder and Encoder
string propName = propDetails[0].Trim();
int len = 4000;
int tmpLen;
bool isBinary = false;
Encoding decoder = Encoding.Unicode;
Encoding encoder = Encoding.Unicode;
bool encodingBiinary = false;
#region Field Length Retrieval
if (propDetails.Length > 1)
{
//if length is "max" then set len = -1 whihc equals to MAX
if (propDetails[1].ToLower() == "max")
{
len = -1;
}
else if (int.TryParse(propDetails[1], out tmpLen) && tmpLen >= 1 && tmpLen <= 4000)
{
len = tmpLen;
}
else
{
throw new System.InvalidCastException(string.Format("[{0}] - Length of field has to be numeric value in range 1 - 4000 or max", properties[i]));
}
}
#endregion
#region Get Decoder and Encoder
//find Encoding
if (propDetails.Length >= 3)
{
decoder = null;
Exception exc = null;
//If Decoder or Encoder is BINARY then we will use a binary storage
if (propDetails[2] == "BINARY" || (propDetails.Length >= 4 && propDetails[3] == "BINARY"))
{
isBinary = true;
}
if (propDetails.Length >= 4 && propDetails[3] == "BINARY")
{
encodingBiinary = true;
}
//Get Decoder
if (propDetails[2] != "BINARY")
{
int codePage;
try
{
if (int.TryParse(propDetails[2].Trim(), out codePage))
{
decoder = Encoding.GetEncoding(codePage);
}
else
{
//Try to get static property of the Encoding Class
System.Reflection.PropertyInfo pi = et.GetProperty(propDetails[2].Trim(), et);
//if the static property does not exists, treats the name as Code Page name
if (pi == null)
{
decoder = Encoding.GetEncoding(propDetails[2].Trim());
}
else if (pi.CanRead)
{
decoder = pi.GetValue(null, null) as Encoding;
}
}
}
catch (Exception e)
{
exc = e;
}
if (decoder == null)
{
//Get List of available static properties of the EncodingClass
StringBuilder sb = new StringBuilder();
sb.Append("BINARY");
foreach (PropertyInfo p in et.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Static))
{
if (p.PropertyType == et)
{
sb.Append(",");
sb.Append(p.Name);
}
}
throw new System.NotSupportedException(string.Format("[{0}] - Decoder has to be one of {1} or a CodePage Numer or CodePage name. For Code Pages see http://msdn.microsoft.com/en-us/library/vstudio/system.text.encoding(v=vs.100).aspx", properties[i], sb.ToString()), exc);
}
}
//Get Encoder
if (propDetails.Length >= 4 && propDetails[3] != "BINARY")
{
encoder = null;
int codePage;
try
{
//In case of CodePage number, try to get code page
if (int.TryParse(propDetails[3].Trim(), out codePage))
{
encoder = Encoding.GetEncoding(codePage);
}
else
{
//Try to get static property of the Encoding Class
System.Reflection.PropertyInfo pi = et.GetProperty(propDetails[2].Trim(), et);
//if the static property does not exists, treats the name as Code Page name
if (pi == null)
{
encoder = Encoding.GetEncoding(propDetails[2].Trim());
}
else if (pi.CanRead)
{
encoder = pi.GetValue(null, null) as Encoding;
}
}
}
catch (Exception e)
{
exc = e;
}
if (encoder == null)
{
//Get List of available static properties of the EncodingClass
StringBuilder sb = new StringBuilder();
sb.Append("BINARY");
foreach (PropertyInfo p in et.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Static))
{
if (p.PropertyType == et)
{
sb.Append(",");
sb.Append(p.Name);
}
}
throw new System.NotSupportedException(string.Format("[{0}] - Encoder has to be one of {1} or a CodePage Numer or CodePage name. For Code Pages see http://msdn.microsoft.com/en-us/library/vstudio/system.text.encoding(v=vs.100).aspx", properties[i], sb.ToString()), exc);
}
}
}
#endregion
//IF Binary, use VarBinary data type otherwise NVarChar
if (isBinary)
{
recordMetaData[i] = new SqlMetaData(propName, System.Data.SqlDbType.VarBinary, len);
}
else
{
recordMetaData[i] = new SqlMetaData(propName, System.Data.SqlDbType.NVarChar, len);
}
//Set ADProperties for current property
adProperties[i] = new ADPropertyInfo(propName, len, decoder, encoder, isBinary, encodingBiinary);
properties[i] = propName;
}
//Get Root Directory Entry
using (DirectoryEntry rootEntry = GetRootEntry(adRoot, userName, password, authType))
{
//Create a directory searcher with aproperiate filter, properties and search scope
using (DirectorySearcher ds = new DirectorySearcher(rootEntry, filter, properties, scope))
{
if (pageSize > 0)
{
ds.PageSize = pageSize; //Set Page Size - without this we will not do a paged search and we will be limiited to 1000 results
}
//find all object from the rood, according the filter and search scope
using (SearchResultCollection results = ds.FindAll())
{
SqlDataRecord record = new SqlDataRecord(recordMetaData);
//Start pushing of records to client
SqlContext.Pipe.SendResultsStart(record);
Regex dnr = null;
foreach (SearchResult result in results)
{
record = new SqlDataRecord(recordMetaData);
for (int i = 0; i < properties.Length; i++)
{
ADPropertyInfo adProperty = adProperties[i];
ResultPropertyValueCollection props = result.Properties[adProperty.PropertyName];
if (props.Count == 1) //if property collection contains single vallue, set the record field to that value
{
if (props[0] is byte[])
{
byte[] buffer = props[0] as byte[];
if (adProperty.IsBinary) //If Binary output, write binary buffer
{
record.SetBytes(i, 0, buffer, 0, adProperty.Length != -1 && adProperty.Length < buffer.Length ? adProperty.Length : buffer.Length);
}
else //Otherwise use decoder to decode the buffer to sctring
{
record.SetSqlString(i, adProperty.Decoder.GetString(buffer));
}
}
else
{
if (adProperty.IsBinary) //In case of binary output, Encode binary to bytes array
{
var buffer = adProperty.Encoder.GetBytes(props[0].ToString());
record.SetBytes(i, 0, buffer, 0, adProperty.Length != -1 && adProperty.Length < buffer.Length ? adProperty.Length : buffer.Length);
}
else
{
record.SetSqlString(i, props[0].ToString());
}
}
}
else if (props.Count == 0) //if property collection doesn't contain any value, set record field to NULL
{
//In case Distinguished Name was requested and such attribute is not in the LDAP, parse the result.Path
if (adProperty.PropertyName.ToLower() == "dn" || adProperty.PropertyName.ToLower() == "distinguishedname")
{
if (dnr == null)
{
dnr = new Regex(@"(?:.+?)//(?:(?:\w|\.)+?)/(?<dn>.+)", RegexOptions.Compiled); // LDAP://server_name_or_ip/DN
}
var match = dnr.Match(result.Path);
if (match != null && match.Groups.Count > 1)
{
if (adProperty.IsBinary)
{
var buffer = adProperty.Encoder.GetBytes(match.Groups["dn"].Value);
record.SetBytes(i, 0, buffer, 0, adProperty.Length != -1 && adProperty.Length < buffer.Length ? adProperty.Length : buffer.Length);
}
else
{
record.SetSqlString(i, match.Groups["dn"].Value);
}
}
}
else
{
if (adProperty.IsBinary)
{
record.SetSqlBinary(i, SqlBinary.Null);
}
else
{
record.SetSqlString(i, SqlString.Null);
}
}
}
else //In case of multiple value, separate the values by commas
{
if (adProperty.IsBinary) //In case of Binary output, create a MemoryStream to which we write multiple binary values and the store the data in the output field
{
using (MemoryStream ms = new MemoryStream()) //MemoryStream to store the binary data
{
using (BinaryWriter bw = new BinaryWriter(ms)) //Binary write for writin the data into the memory stream
{
foreach (object prop in props)
{
if (prop is byte[]) //If property is byte array, write that byte array into the memory stream
{
bw.Write((byte[])prop);
}
else
{
if (adProperty.EncodeBinary) //If BinaryEncoded, user the BinaryWrite.Write(string)
{
bw.Write(prop.ToString());
}
else //Otherwise use Encoder to encode the string into byte array and write it iinto the memory stream
{
bw.Write(adProperty.Encoder.GetBytes(prop.ToString()));
}
}
}
}
//Get the MemoryStream buffer and write it into the output field
var buffer = ms.GetBuffer();
record.SetBytes(i, 0, buffer, 0, adProperty.Length != -1 && adProperty.Length < buffer.Length ? adProperty.Length : buffer.Length);
}
}
else //character output
{
StringBuilder sb = new StringBuilder();
bool firstItem = true;
foreach (object prop in props)
{
if (!firstItem)
{
sb.Append(',');
}
else
{
firstItem = false;
}
if (prop is byte[]) //In case of binary data, decode them to a string using decoder
{
sb.Append(adProperty.Decoder.GetString((byte[])prop));
}
else
{
sb.Append(prop.ToString());
}
}
var c = sb.ToString();
record.SetSqlString(i, sb.ToString());
}
}
}
//send record to client
SqlContext.Pipe.SendResultsRow(record);
//if rowsLimit was reached, break the loop
if (++rowsCount == rowsLimit)
{
break;
}
}
//stop sending records to client
SqlContext.Pipe.SendResultsEnd();
}
}
}
}
public override void Set(SqlDataRecord record, int ordinal, byte[] value)
{
record.SetBytes(ordinal, fieldOffset: 0, value, bufferOffset: 0, value.Length);
}
public override void Set(SqlDataRecord record, int ordinal, byte value)
{
record.SetByte(ordinal, value);
}
/// <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 = CodeFirstStoredProcHelpers.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 = CodeFirstStoredProcHelpers.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 <StoredProcAttributes.Name>();
String name = (null == attr) ? p.Name : attr.Value;
mapping.Add(name, p.Name);
// get column type
var ct = p.GetAttribute <StoredProcAttributes.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 <StoredProcAttributes.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 <StoredProcAttributes.Precision>();
Byte precision = (null == pa) ? (byte)10 : pa.Value;
var sca = p.GetAttribute <StoredProcAttributes.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 InitMethod(SqlString table, SqlString column, SqlString pattern, SqlString includeColumns)
{
string[] extraColumns;
if (includeColumns.IsNull)
{
// if the includeColumns parameter has been omitted
extraColumns = new string[0];
}
else
{
// allow columns to be delimited in a few different ways
string[] delimiters = new string[3] {
" ", ",", ";"
};
extraColumns = includeColumns.ToString().Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
}
// compile the regex, since it will be used on a lot of rows
// this trades a slower instantiation for faster execution
Regex split = new Regex(pattern.ToString(), RegexOptions.Singleline | RegexOptions.Compiled);
// find the group names in the provided regex (or numbers for not named capturing groups)
string[] groupNamesAndEntireMatch = split.GetGroupNames();
// allocate storage for the actual group names (-1 for removing the entire match)
string[] groupNames = new string[groupNamesAndEntireMatch.Length - 1];
Array.Copy(groupNamesAndEntireMatch, 1, groupNames, 0, groupNames.Length);
// open a connection in the same context as we are executing
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// set up the query to fetch data
string query =
" SELECT " +
(extraColumns.Length == 0 ? "" : string.Join(",", extraColumns) + ",") +
column.ToString() +
" FROM " +
table.ToString();
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
// set up the columns expected to be returned
SqlMetaData[] md = new SqlMetaData[extraColumns.Length + groupNames.Length];
SqlDbType providerType;
int columnSize;
byte numericPrecision, numericScale;
int i;
// first add the extra columns
for (i = 0; i < extraColumns.Length; i++)
{
extraColumns[i] = extraColumns[i].Replace("[", "").Replace("]", "");
providerType = (SqlDbType)(int)reader.GetSchemaTable().Rows[i]["ProviderType"];
switch (providerType)
{
case SqlDbType.Bit:
case SqlDbType.BigInt:
case SqlDbType.DateTime:
case SqlDbType.Float:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.SmallDateTime:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.Timestamp:
case SqlDbType.TinyInt:
case SqlDbType.UniqueIdentifier:
case SqlDbType.Xml:
md[i] = new SqlMetaData(extraColumns[i], providerType);
break;
case SqlDbType.Binary:
case SqlDbType.Char:
case SqlDbType.Image:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.VarBinary:
case SqlDbType.VarChar:
columnSize = (int)reader.GetSchemaTable().Rows[i]["ColumnSize"];
columnSize = columnSize == 2147483647 ? -1 : columnSize;
md[i] = new SqlMetaData(extraColumns[i], providerType, columnSize);
break;
case SqlDbType.Decimal:
numericPrecision = (byte)reader.GetSchemaTable().Rows[i]["NumericPrecision"];
numericScale = (byte)reader.GetSchemaTable().Rows[i]["NumericScale"];
md[i] = new SqlMetaData(extraColumns[i], providerType, numericPrecision, numericScale);
break;
default:
md[i] = new SqlMetaData(extraColumns[i], providerType);
break;
}
}
// then the expected columms created by the split operation
for (i = 0; i < groupNames.Length; i++)
{
md[i + extraColumns.Length] = new SqlMetaData(groupNames[i], SqlDbType.NVarChar, -1);
}
SqlDataRecord writer = new SqlDataRecord(md);
SqlString columnValue; // holds a value from the column to be matched against
GroupCollection groups; // holds the results of the match
using (reader)
{
SqlContext.Pipe.SendResultsStart(writer);
// iterate through the result set
while (reader.Read())
{
for (i = 0; i < extraColumns.Length; i++)
{
// pass through the extra columns
writer.SetValue(i, reader.GetValue(i));
}
// read column to matched against
columnValue = reader.GetSqlString(extraColumns.Length);
// split column according to pattern
groups = split.Match(columnValue.ToString()).Groups;
for (i = 0; i < groupNames.Length; i++)
{
// write content of capturing group
if (groups[groupNames[i]].Value == String.Empty)
{
// an empty string is no match, and should be a null value
writer.SetSqlString(i + extraColumns.Length, SqlString.Null);
}
else
{
// if the string is non-empty, write the actual value
writer.SetSqlString(i + extraColumns.Length, groups[groupNames[i]].Value);
}
}
// send the row to the result set
SqlContext.Pipe.SendResultsRow(writer);
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
internal override DataTable GetSkylineTable(IEnumerable <object[]> database, DataTable dataTableTemplate, SqlDataRecord dataRecordTemplate, string preferenceOperators)
{
throw new NotImplementedException();
}
/// <summary>
/// Executing a batch command
/// </summary>
/// <param name="cmd">the DbCommand already prepared</param>
/// <param name="applyTable">the table rows to apply</param>
/// <param name="failedRows">the failed rows dmTable to store failed rows</param>
/// <param name="scope">the current scope</param>
public override void ExecuteBatchCommand(DbCommand cmd, DmTable applyTable, DmTable failedRows, ScopeInfo scope)
{
if (applyTable.Rows.Count <= 0)
{
return;
}
var lstMutableColumns = applyTable.Columns.Where(c => !c.ReadOnly).ToList();
List <SqlDataRecord> records = new List <SqlDataRecord>(applyTable.Rows.Count);
SqlMetaData[] metadatas = new SqlMetaData[lstMutableColumns.Count];
for (int i = 0; i < lstMutableColumns.Count; i++)
{
var column = lstMutableColumns[i];
SqlMetaData metadata = GetSqlMetadaFromType(column);
metadatas[i] = metadata;
}
try
{
foreach (var dmRow in applyTable.Rows)
{
SqlDataRecord record = new SqlDataRecord(metadatas);
int sqlMetadataIndex = 0;
for (int i = 0; i < dmRow.ItemArray.Length; i++)
{
// check if it's readonly
if (applyTable.Columns[i].ReadOnly)
{
continue;
}
// Get the default value
// Since we have the readonly values in ItemArray, get the value from original column
dynamic defaultValue = applyTable.Columns[i].DefaultValue;
dynamic rowValue = dmRow[i];
var columnType = applyTable.Columns[i].DataType;
// metadatas don't have readonly values, so get from sqlMetadataIndex
var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType;
if (rowValue != null)
{
switch (sqlMetadataType)
{
case SqlDbType.BigInt:
if (columnType != typeof(long))
{
if (Int64.TryParse(rowValue.ToString(), out Int64 v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Int64", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Bit:
if (columnType != typeof(bool))
{
if (Boolean.TryParse(rowValue.ToString(), out Boolean v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Boolean", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
if (columnType != typeof(DateTime))
{
if (DateTime.TryParse(rowValue.ToString(), out DateTime v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to DateTime", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.DateTimeOffset:
if (columnType != typeof(DateTimeOffset))
{
if (DateTimeOffset.TryParse(rowValue.ToString(), out DateTimeOffset dt))
{
rowValue = dt;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to DateTimeOffset", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Decimal:
if (columnType != typeof(Decimal))
{
if (Decimal.TryParse(rowValue.ToString(), out decimal v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Decimal", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Float:
if (columnType != typeof(Double))
{
if (Double.TryParse(rowValue.ToString(), out Double v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Double", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Real:
if (columnType != typeof(float))
{
if (float.TryParse(rowValue.ToString(), out float v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Double", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Image:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
if (columnType != typeof(Byte[]))
{
rowValue = BitConverter.GetBytes(rowValue);
}
break;
case SqlDbType.Variant:
break;
case SqlDbType.Int:
if (columnType != typeof(Int32))
{
if (Int32.TryParse(rowValue.ToString(), out int v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Int32", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (columnType != typeof(Decimal))
{
if (Decimal.TryParse(rowValue.ToString(), out Decimal v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Decimal", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Char:
if (columnType != typeof(string))
{
rowValue = rowValue.ToString();
}
break;
case SqlDbType.SmallInt:
if (columnType != typeof(Int16))
{
if (Int16.TryParse(rowValue.ToString(), out Int16 v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Int16", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Time:
if (columnType != typeof(TimeSpan))
{
if (TimeSpan.TryParse(rowValue.ToString(), out TimeSpan v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to TimeSpan", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Timestamp:
break;
case SqlDbType.TinyInt:
if (columnType != typeof(Byte))
{
if (Byte.TryParse(rowValue.ToString(), out byte v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Byte", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
case SqlDbType.Udt:
throw new SyncException($"Can't use UDT as SQL Type", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
case SqlDbType.UniqueIdentifier:
if (columnType != typeof(Guid))
{
if (Guid.TryParse(rowValue.ToString(), out Guid v))
{
rowValue = v;
}
else
{
throw new SyncException($"Can't convert value {rowValue} to Guid", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
}
break;
}
}
if (applyTable.Columns[i].AllowDBNull && rowValue == null)
{
rowValue = DBNull.Value;
}
else if (rowValue == null)
{
rowValue = defaultValue;
}
record.SetValue(sqlMetadataIndex, rowValue);
sqlMetadataIndex++;
}
records.Add(record);
}
}
catch (Exception ex)
{
throw new SyncException($"Can't create a SqlRecord based on the rows we have: {ex.Message}", Enumerations.SyncStage.ApplyingChanges, SyncExceptionType.NotSupported);
}
((SqlParameterCollection)cmd.Parameters)["@changeTable"].TypeName = string.Empty;
((SqlParameterCollection)cmd.Parameters)["@changeTable"].Value = records;
((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = scope.Id;
((SqlParameterCollection)cmd.Parameters)["@sync_min_timestamp"].Value = scope.LastTimestamp;
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
cmd.Transaction = this.transaction;
}
using (DbDataReader dataReader = cmd.ExecuteReader())
{
failedRows.Fill(dataReader);
}
}
catch (DbException ex)
{
//DbException dbException = dbException1;
//Error = CheckZombieTransaction(tvpCommandNameForApplyType, Adapter.TableName, dbException);
//this.AddFailedRowsAfterRIFailure(applyTable, failedRows);
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
public static List <object[]> GetObjectArrayFromSQL(string strQuery, string strConnection, string strProvider, DataTable dt, string[] operators, out SqlDataRecord record)
{
return(GetObjectArrayFromSQL(strQuery, strConnection, strProvider, dt, operators, out record, false));
}
public SqlUncommittedEventDataRecord()
{
this._record = new SqlDataRecord(Schema);
}
public void SqlRecordFillTest()
{
SqlMetaData[] metaData = new SqlMetaData[]
{
new SqlMetaData("col1", SqlDbType.Bit),
new SqlMetaData("col2", SqlDbType.TinyInt),
new SqlMetaData("col3", SqlDbType.VarBinary, 1000),
new SqlMetaData("col4", SqlDbType.NVarChar, 1000),
new SqlMetaData("col5", SqlDbType.DateTime),
new SqlMetaData("col6", SqlDbType.Float),
new SqlMetaData("col7", SqlDbType.UniqueIdentifier),
new SqlMetaData("col8", SqlDbType.SmallInt),
new SqlMetaData("col9", SqlDbType.Int),
new SqlMetaData("col10", SqlDbType.BigInt),
new SqlMetaData("col11", SqlDbType.Real),
new SqlMetaData("col12", SqlDbType.Decimal),
new SqlMetaData("col13", SqlDbType.Money),
new SqlMetaData("col14", SqlDbType.Variant)
};
SqlDataRecord record = new SqlDataRecord(metaData);
for (int i = 0; i < record.FieldCount; i++)
{
Assert.Equal($"col{i + 1}", record.GetName(i));
}
record.SetBoolean(0, true);
Assert.True(record.GetBoolean(0));
record.SetByte(1, 1);
Assert.Equal(1, record.GetByte(1));
byte[] bb1 = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
byte[] bb2 = new byte[5];
record.SetSqlBinary(2, new SqlBinary(new byte[0]));
record.SetBytes(2, 0, bb1, 0, 3);
record.SetBytes(2, 2, bb1, 6, 3);
// Verify the length of the byte array
Assert.Equal(5, record.GetBytes(2, 0, bb2, 0, 5));
Assert.Equal(5, record.GetBytes(2, 0, null, 0, 0));
byte[] expected = new byte[] { 1, 2, 7, 8, 9 };
Assert.Equal <byte>(expected, bb2);
char[] cb1 = new char[] { 'a', 'b', 'c', 'd', 'e', 'f', 'g' };
char[] cb2 = new char[5];
record.SetChars(3, 0, cb1, 0, 3);
record.SetChars(3, 2, cb1, 4, 3);
char[] expectedValue = new char[] { 'a', 'b', 'e', 'f', 'g' };
Assert.Equal(expectedValue.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal <char>(expectedValue, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetString(3, "");
string xyz = "xyz";
record.SetString(3, "xyz");
Assert.Equal(xyz, record.GetString(3));
Assert.Equal(xyz.Length, record.GetChars(3, 0, cb2, 0, 5));
Assert.Equal(xyz, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
record.SetChars(3, 2, cb1, 4, 3);
Assert.Equal(5, record.GetChars(3, 0, cb2, 0, 5));
string interleavedResult = "xyefg";
Assert.Equal(interleavedResult, new string(cb2, 0, (int)record.GetChars(3, 0, null, 0, 0)));
Assert.Equal(interleavedResult, record.GetString(3));
record.SetSqlDateTime(4, SqlDateTime.MaxValue);
Assert.Equal(SqlDateTime.MaxValue, record.GetSqlDateTime(4));
record.SetSqlDouble(5, SqlDouble.MaxValue);
Assert.Equal(SqlDouble.MaxValue, record.GetSqlDouble(5));
SqlGuid guid = new SqlGuid("F9168C5E-CEB2-4faa-B6BF-329BF39FA1E4");
record.SetSqlGuid(6, guid);
Assert.Equal(guid, record.GetSqlGuid(6));
record.SetSqlInt16(7, SqlInt16.MaxValue);
Assert.Equal(SqlInt16.MaxValue, record.GetSqlInt16(7));
record.SetSqlInt32(8, SqlInt32.MaxValue);
Assert.Equal(SqlInt32.MaxValue, record.GetSqlInt32(8));
record.SetSqlInt64(9, SqlInt64.MaxValue);
Assert.Equal(SqlInt64.MaxValue, record.GetSqlInt64(9));
record.SetSqlSingle(10, SqlSingle.MinValue);
Assert.Equal(SqlSingle.MinValue, record.GetSqlSingle(10));
record.SetSqlDecimal(11, SqlDecimal.Null);
record.SetSqlDecimal(11, SqlDecimal.MaxValue);
Assert.Equal(SqlDecimal.MaxValue, record.GetSqlDecimal(11));
record.SetSqlMoney(12, SqlMoney.MaxValue);
Assert.Equal(SqlMoney.MaxValue, record.GetSqlMoney(12));
// Try adding different values to SqlVariant type
for (int i = 0; i < record.FieldCount - 1; ++i)
{
object valueToSet = record.GetSqlValue(i);
record.SetValue(record.FieldCount - 1, valueToSet);
object o = record.GetSqlValue(record.FieldCount - 1);
if (o is SqlBinary)
{
Assert.Equal <byte>(((SqlBinary)valueToSet).Value, ((SqlBinary)o).Value);
}
else
{
Assert.Equal(valueToSet, o);
}
record.SetDBNull(record.FieldCount - 1);
Assert.Equal(DBNull.Value, record.GetSqlValue(record.FieldCount - 1));
record.SetDBNull(i);
Assert.Equal(DBNull.Value, record.GetValue(i));
}
}
/// <summary>
/// Round trip sql_variant value using TVP.
/// </summary>
private static void SendVariantTvp(object paramValue, string expectedTypeName, string expectedBaseTypeName)
{
string tvpTypeName = DataTestUtility.GetUniqueNameForSqlServer("tvpVariant");
using (SqlConnection connTvp = new SqlConnection(s_connStr))
{
connTvp.Open();
// Drop and re-create tvp type.
xsql(connTvp, "if exists(select 1 from sys.types where name='{0}') begin drop type {0} end", tvpTypeName);
xsql(connTvp, "create type {0} as table (f1 sql_variant)", tvpTypeName);
// Send TVP using SqlMetaData.
SqlMetaData[] metadata = new SqlMetaData[1];
metadata[0] = new SqlMetaData("f1", SqlDbType.Variant);
SqlDataRecord[] record = new SqlDataRecord[1];
record[0] = new SqlDataRecord(metadata);
record[0].SetValue(0, paramValue);
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", record);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlMetaData]", dr, expectedTypeName, expectedBaseTypeName);
}
}
// Send TVP using DataSet.
// DEVNOTE: TVPs do not support sql_variant inside DataTable, by design according to tvp spec:
// 4.3 DbDataReader
// The only difference or special case for DataTable is that we will not support:
// 1) UDT?s
// 2) Variant
/*
* DataTable t = new DataTable();
* t.Columns.Add("f1", typeof(object));
* t.Rows.Add(new object[] { paramValue });
* t.AcceptChanges();
*
* using (SqlCommand cmd = connTvp.CreateCommand())
* {
* cmd.CommandText = "select f1, sql_variant_property(@p1,'BaseType') as BaseType from @tvpParam";
* SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", t);
* p.SqlDbType = SqlDbType.Structured;
* p.TypeName = @"dbo.tvpVariant";
* using (SqlDataReader dr = cmd.ExecuteReader())
* {
* VerifyReader("SendVariantTvp[DataTable]", dr, expectedTypeName, expectedBaseTypeName);
* }
* }
*/
// Send TVP using SqlDataReader.
using (SqlDataReader dr = GetReaderForVariant(paramValue, false))
{
using (SqlCommand cmd = connTvp.CreateCommand())
{
cmd.CommandText = "select f1, sql_variant_property(f1,'BaseType') as BaseType from @tvpParam";
SqlParameter p = cmd.Parameters.AddWithValue("@tvpParam", dr);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = string.Format("dbo.{0}", tvpTypeName);
using (SqlDataReader dr2 = cmd.ExecuteReader())
{
VerifyReader("SendVariantTvp[SqlDataReader]", dr2, expectedTypeName, expectedBaseTypeName);
}
}
}
// Cleanup tvp type.
xsql(connTvp, "if exists(select 1 from sys.types where name='{0}') begin drop type {0} end", tvpTypeName);
}
}
public static void run_query2(SqlString execTsql)
{
// user connection string builder here, accept query, server, current, user, password - execute as system by default, accept windows creds, sql creds
// Connection string
using (SqlConnection connection = new SqlConnection(@"Data Source=MSSQLSRV04\SQLSERVER2014;Initial Catalog=master;Integrated Security=True"))
{
connection.Open();
SqlCommand command = new SqlCommand(execTsql.ToString(), connection);
command.CommandTimeout = 240;
SqlDataReader reader = command.ExecuteReader();
// Create List for Columns
List <SqlMetaData> OutputColumns = new List <SqlMetaData>(reader.FieldCount);
// Get schema
DataTable schemaTable = reader.GetSchemaTable();
// Get column names, types, and sizes from reader
for (int i = 0; i < reader.FieldCount; i++)
{
// Check if char and string types
if (typeof(char).Equals(reader.GetFieldType(i)) || typeof(string).Equals(reader.GetFieldType(i)))
{
SqlMetaData OutputColumn = new SqlMetaData(reader.GetName(i), SqlHelper.GetDbType(reader.GetFieldType(i)), 4000);
OutputColumns.Add(OutputColumn);
}
else
{
// Anything other type
SqlMetaData OutputColumn = new SqlMetaData(reader.GetName(i), SqlHelper.GetDbType(reader.GetFieldType(i)));
OutputColumns.Add(OutputColumn);
}
}
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// Check for rows
if (reader.HasRows)
{
while (reader.Read())
{
// Iterate through column count, set value for each column in row
for (int i = 0; i < reader.FieldCount; i++)
{
// Add value to the current row/column
record.SetValue(i, reader[i]);
}
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
}
}
else
{
// Set values for each column in the row
record.SetString(0, "No rows found.");
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
}
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
connection.Close();
}
}
/// <summary> /// TODO: comment /// </summary> /// <param name="database"></param> /// <param name="dataTableTemplate"></param> /// <param name="dataRecordTemplate"></param> /// <param name="preferenceOperators"></param> /// <returns></returns> internal abstract DataTable GetSkylineTable(IEnumerable <object[]> database, DataTable dataTableTemplate, SqlDataRecord dataRecordTemplate, string preferenceOperators);
public static SqlDataRecord Convert(ExcelSignleRow row, DataSourceNames name)
{
try
{
if (row == null) throw new ArgumentNullException("data can't be null in convert");
if (row.PrimaryKeyRequired(name) == false)
{
throw new NullReferenceException($"Primary Key can't be null{name}");
}
var record = new SqlDataRecord(Constants.Structures[name]);
for (var index = 0; index < Constants.Structures[name].Length; index++)
{
var fieldName = Constants.Structures[name][index].Name;
var property = row.Properties.TryFirst(o => o.Descriptor.FiledName.Equals(fieldName, StringComparison.OrdinalIgnoreCase));
if (property == null)
record.SetDBNull(index);
else
{
if (property.Descriptor.Type == System.Data.SqlDbType.DateTime)
{
if (property.Value.TryToUnixStampDateTime(out long? timestamp))
{
record.SetInt64(index, timestamp ?? 0);
}
else
{
record.SetDBNull(index);
}
}
else if (property.Descriptor.Type == System.Data.SqlDbType.Int)
{
if (int.TryParse(property.Value?.ToString(), out int val))
{
record.SetInt32(index, val);
}
else
{
record.SetDBNull(index);
}
}
else if (property.Descriptor.Type == System.Data.SqlDbType.Money)
{
if (decimal.TryParse(property.Value?.ToString(), out decimal money))
{
record.SetSqlMoney(index, money);
}
else
{
record.SetDBNull(index);
}
}
else
{
record.SetStringIfNullOrEmpty(index, property.Value?.ToString());
}
}
}
return record;
}
catch (Exception ex)
{
return null;
}
}
public bool UpdateDatasource(DTO.DatasourceDto dsDto)
{
SqlConnection sqlConn = new SqlConnection(this.ConnectionString);
sqlConn.Open();
object Formobj = null;
if (dsDto.IsEpiInfoForm)
{
Formobj = ReadEWEDatasourceFormId(new DTO.EWEDatasourceDto()
{
DatabaseName = dsDto.Connection.DatabaseName
});
}
SqlCommand addDSCommand = sqlConn.CreateCommand();
SqlCommand addUserDSCommand = sqlConn.CreateCommand();
addDSCommand.CommandType = System.Data.CommandType.StoredProcedure;
addDSCommand.CommandText = "usp_update_datasource";
Cryptography cy = new Cryptography();
addDSCommand.Parameters.Add("EIWSSurveyId", (Formobj != null) ? Formobj : DBNull.Value);
addDSCommand.Parameters.Add("DatasourceName", dsDto.DatasourceName);
addDSCommand.Parameters.Add("DatabaseType", dsDto.Connection.DatabaseType.ToString());
addDSCommand.Parameters.Add("PersistSecurityInfo", dsDto.Connection.PersistSecurityInfo.ToString());
addDSCommand.Parameters.Add("InitialCatalog", cy.Encrypt(dsDto.Connection.DatabaseName));
addDSCommand.Parameters.Add("DatasourceServerName", cy.Encrypt(dsDto.Connection.ServerName));
addDSCommand.Parameters.Add("DatabaseUserID", cy.Encrypt(dsDto.Connection.UserId));
addDSCommand.Parameters.Add("Password", cy.Encrypt(dsDto.Connection.Password));
addDSCommand.Parameters.Add("DatabaseObject", cy.Encrypt(dsDto.Connection.DatabaseObject));
addDSCommand.Parameters.Add("DatasourceID", dsDto.DatasourceId);
addDSCommand.Parameters.Add("active", dsDto.IsActive);
addDSCommand.Parameters.Add("pnumber", cy.Encrypt(dsDto.Connection.PortNumber));
addDSCommand.Parameters.Add("@DatasourceUser", SqlDbType.Structured);
addDSCommand.Parameters["@DatasourceUser"].Direction = ParameterDirection.Input;
addDSCommand.Parameters["@DatasourceUser"].TypeName = "DatasourceUserTableType";
List <SqlDataRecord> sqlDrList = new List <SqlDataRecord>();
SqlDataRecord sqdr;
try
{
foreach (Ewav.DTO.UserDTO item in dsDto.AssociatedUsers)
{
sqdr = new SqlDataRecord(new SqlMetaData[]
{ new SqlMetaData("DatasourceID", SqlDbType.Int),
new SqlMetaData("UserID", SqlDbType.Int) });
// Set the record fields.
sqdr.SetInt32(0, dsDto.DatasourceId);
sqdr.SetInt32(1, item.UserID);
sqlDrList.Add(sqdr);
}
//// Also add the creator
//sqdr = new SqlDataRecord(new SqlMetaData[]
// { new SqlMetaData("DatasourceID", SqlDbType.Int ),
// new SqlMetaData("UserID", SqlDbType.Int)
// });
//sqdr.SetInt32(0, dsDto.DatasourceId);
//sqdr.SetInt32(1, dsDto.CreatorID);
//sqlDrList.Add(sqdr);
if (dsDto.AssociatedUsers.Count == 0)
{
addDSCommand.Parameters["@DatasourceUser"].Value = null;
}
else
{
addDSCommand.Parameters["@DatasourceUser"].Value = sqlDrList;
}
}
catch (Exception e)
{
}
try
{
addDSCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//throw ex;
return(false);
}
return(true);
}
/// <summary>
/// Executing a batch command
/// </summary>
public override async Task ExecuteBatchCommandAsync(DbCommand cmd, Guid senderScopeId, IEnumerable <SyncRow> applyRows, SyncTable schemaChangesTable,
SyncTable failedRows, long?lastTimestamp, DbConnection connection, DbTransaction transaction = null)
{
var applyRowsCount = applyRows.Count();
if (applyRowsCount <= 0)
{
return;
}
var dataRowState = DataRowState.Unchanged;
var records = new List <SqlDataRecord>(applyRowsCount);
SqlMetaData[] metadatas = new SqlMetaData[schemaChangesTable.Columns.Count];
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
metadatas[i] = GetSqlMetadaFromType(schemaChangesTable.Columns[i]);
}
try
{
foreach (var row in applyRows)
{
dataRowState = row.RowState;
var record = new SqlDataRecord(metadatas);
int sqlMetadataIndex = 0;
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
var schemaColumn = schemaChangesTable.Columns[i];
// Get the default value
//var columnType = schemaColumn.GetDataType();
dynamic defaultValue = schemaColumn.GetDefaultValue();
dynamic rowValue = row[i];
// metadatas don't have readonly values, so get from sqlMetadataIndex
var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType;
if (rowValue != null)
{
var columnType = rowValue.GetType();
switch (sqlMetadataType)
{
case SqlDbType.BigInt:
if (columnType != typeof(long))
{
rowValue = SyncTypeConverter.TryConvertTo <long>(rowValue);
}
break;
case SqlDbType.Bit:
if (columnType != typeof(bool))
{
rowValue = SyncTypeConverter.TryConvertTo <bool>(rowValue);
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
if (columnType != typeof(DateTime))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTime>(rowValue);
}
break;
case SqlDbType.DateTimeOffset:
if (columnType != typeof(DateTimeOffset))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTimeOffset>(rowValue);
}
break;
case SqlDbType.Decimal:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
break;
case SqlDbType.Float:
if (columnType != typeof(double))
{
rowValue = SyncTypeConverter.TryConvertTo <double>(rowValue);
}
break;
case SqlDbType.Real:
if (columnType != typeof(float))
{
rowValue = SyncTypeConverter.TryConvertTo <float>(rowValue);
}
break;
case SqlDbType.Image:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
if (columnType != typeof(byte[]))
{
rowValue = SyncTypeConverter.TryConvertTo <byte[]>(rowValue);
}
break;
case SqlDbType.Variant:
break;
case SqlDbType.Int:
if (columnType != typeof(int))
{
rowValue = SyncTypeConverter.TryConvertTo <int>(rowValue);
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
break;
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Char:
if (columnType != typeof(string))
{
rowValue = SyncTypeConverter.TryConvertTo <string>(rowValue);
}
break;
case SqlDbType.SmallInt:
if (columnType != typeof(short))
{
rowValue = SyncTypeConverter.TryConvertTo <short>(rowValue);
}
break;
case SqlDbType.Time:
if (columnType != typeof(TimeSpan))
{
rowValue = SyncTypeConverter.TryConvertTo <TimeSpan>(rowValue);
}
break;
case SqlDbType.Timestamp:
break;
case SqlDbType.TinyInt:
if (columnType != typeof(byte))
{
rowValue = SyncTypeConverter.TryConvertTo <byte>(rowValue);
}
break;
case SqlDbType.Udt:
throw new ArgumentException($"Can't use UDT as SQL Type");
case SqlDbType.UniqueIdentifier:
if (columnType != typeof(Guid))
{
rowValue = SyncTypeConverter.TryConvertTo <Guid>(rowValue);
}
break;
}
}
if (rowValue == null)
{
rowValue = DBNull.Value;
}
record.SetValue(sqlMetadataIndex, rowValue);
sqlMetadataIndex++;
}
records.Add(record);
}
}
catch (Exception ex)
{
throw new InvalidOperationException($"Can't create a SqlRecord based on the rows we have: {ex.Message}");
}
((SqlParameterCollection)cmd.Parameters)["@changeTable"].TypeName = string.Empty;
((SqlParameterCollection)cmd.Parameters)["@changeTable"].Value = records;
((SqlParameterCollection)cmd.Parameters)["@sync_min_timestamp"].Value = lastTimestamp.HasValue ? (object)lastTimestamp.Value : DBNull.Value;
((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = senderScopeId;
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
cmd.Transaction = transaction;
using var dataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
while (dataReader.Read())
{
//var itemArray = new object[dataReader.FieldCount];
var itemArray = new object[failedRows.Columns.Count];
for (var i = 0; i < dataReader.FieldCount; i++)
{
var columnValueObject = dataReader.GetValue(i);
var columnName = dataReader.GetName(i);
var columnValue = columnValueObject == DBNull.Value ? null : columnValueObject;
var failedColumn = failedRows.Columns[columnName];
var failedIndexColumn = failedRows.Columns.IndexOf(failedColumn);
itemArray[failedIndexColumn] = columnValue;
}
// don't care about row state
// Since it will be requested by next request from GetConflict()
failedRows.Rows.Add(itemArray, dataRowState);
}
dataReader.Close();
}
catch (DbException ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
records.Clear();
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// Template function for each algorithm
/// Performance measurements, CLR, sorting, and so on are handled here
/// </summary>
/// <param name="database"></param>
/// <param name="dataTableTemplate"></param>
/// <param name="dataRecordTemplate"></param>
/// <param name="operators"></param>
/// <param name="numberOfRecords"></param>
/// <param name="isIndependent"></param>
/// <param name="sortType"></param>
/// <param name="additionalParameters"></param>
/// <returns></returns>
internal DataTable GetSkylineTable(IEnumerable <object[]> database, DataTable dataTableTemplate, SqlDataRecord dataRecordTemplate, string operators, int numberOfRecords, bool isIndependent, int sortType, string[] additionalParameters)
{
string[] operatorsArray = operators.Split(';');
DataTable dataTableReturn = new DataTable();
Stopwatch sw = new Stopwatch();
try
{
//Time the algorithm needs (afer query to the database)
sw.Start();
//Run the specific algorithm
dataTableReturn = GetSkylineFromAlgorithm(database, dataTableTemplate, operatorsArray, additionalParameters);
//Sort ByRank
if (sortType == 1)
{
dataTableReturn = Helper.SortBySum(dataTableReturn, SkylineValues);
}
else if (sortType == 2)
{
dataTableReturn = Helper.SortByRank(dataTableReturn, SkylineValues);
}
//Remove certain amount of rows if query contains TOP Keyword
Helper.GetAmountOfTuples(dataTableReturn, numberOfRecords);
//Send results if working with the CLR
if (isIndependent == false)
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.SendResultsStart(dataRecordTemplate);
foreach (DataRow recSkyline in dataTableReturn.Rows)
{
for (int i = 0; i < recSkyline.Table.Columns.Count; i++)
{
dataRecordTemplate.SetValue(i, recSkyline[i]);
}
SqlContext.Pipe.SendResultsRow(dataRecordTemplate);
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
catch (Exception ex)
{
//Pack Errormessage in a SQL and return the result
string strError = "Unexpected Error. Msg:";
strError += ex.Message;
strError += "\n StackTrace: " + ex.StackTrace;
if (isIndependent)
{
Debug.WriteLine(strError);
}
else
{
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send(strError);
}
}
}
//Report time the execution time of the algorithm
sw.Stop();
TimeInMs = sw.ElapsedMilliseconds;
return(dataTableReturn);
}
public static List <SqlDataRecord> GetSearchResultsList(string searchTerm, LoginUser loginUser)
{
SqlMetaData recordIDColumn = new SqlMetaData("recordID", SqlDbType.Int);
SqlMetaData relevanceColumn = new SqlMetaData("relevance", SqlDbType.Int);
SqlMetaData[] columns = new SqlMetaData[] { recordIDColumn, relevanceColumn };
List <SqlDataRecord> result = new List <SqlDataRecord>();
using (SqlCommand command = new SqlCommand())
{
if (!string.IsNullOrWhiteSpace(searchTerm))
{
command.CommandText = @"
SELECT
p.ProductID,
match.RANK
FROM
dbo.Products p
INNER JOIN CONTAINSTABLE(Products, (Name, Description), @searchTerm) AS match
ON p.ProductID = match.[KEY]
WHERE
p.OrganizationID = @organizationID";
}
else
{
command.CommandText = @"
SELECT
p.ProductID,
1 AS RANK
FROM
Products p
WHERE
p.OrganizationID = @organizationID";
}
command.CommandText += GetProductFamiliesRightsClause(loginUser);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@organizationID", loginUser.OrganizationID);
command.Parameters.AddWithValue("@userID", loginUser.UserID);
command.Parameters.AddWithValue("@searchTerm", string.Format("\"*{0}*\"", searchTerm));
using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted);
command.Connection = connection;
command.Transaction = transaction;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
SqlDataRecord record = new SqlDataRecord(columns);
record.SetInt32(0, (int)reader["ProductID"]);
record.SetInt32(1, (int)(reader["RANK"]));
result.Add(record);
}
}
}
return(result);
}
public override void Set(SqlDataRecord record, int ordinal, double value)
{
record.SetDouble(ordinal, value);
}
