public static int ListsSubscribe(SqlString apikey, SqlString list_id, SqlString email, SqlString euid, SqlString leid, SqlString email_type, SqlBoolean double_optin, SqlBoolean update_existing, SqlBoolean send_welcome)
{
try
{
MailChimpManager mc = new MailChimpManager(apikey.ToString());
EmailParameter emailParam = new EmailParameter
{
Email = email.ToString(),
EUId = euid.ToString(),
LEId = leid.ToString()
};
EmailParameter result = mc.Subscribe(list_id.ToString(), emailParam, null, email_type.ToString(),
double_optin.IsTrue, update_existing.IsTrue, true, send_welcome.IsTrue);
SqlDataRecord record = new SqlDataRecord(EmailParameterResultsMetaData);
record.SetString(0, result.Email);
record.SetString(1, result.EUId);
record.SetString(2, result.LEId);
SqlContext.Pipe.Send(record);
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message);
return(1);
}
return(0);
}
private static IEnumerable <SqlDataRecord> BindDishRows(IEnumerable <DishEntity> dishesToUpdate)
{
foreach (var dish in dishesToUpdate)
{
SqlDataRecord record = new SqlDataRecord(typ_DishTable);
record.SetInt64(0, dish.DishId);
record.SetInt64(1, dish.ProviderId);
record.SetInt32(2, (int)dish.DishType);
record.SetString(3, dish.DishName);
record.SetString(4, dish.Description);
record.SetString(5, dish.Ingredients);
record.SetDecimal(6, dish.Price);
record.SetInt32(7, dish.WaitingTimeInMins);
if (!dish.ThumbNailPictureKey.HasValue)
{
record.SetDBNull(8);
}
else
{
record.SetGuid(8, dish.ThumbNailPictureKey.Value);
}
record.SetBoolean(9, dish.Available);
yield return(record);
}
}
public static void CallQuoteService(string ticker, string quoteType, DateTime?date)
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Ticker", SqlDbType.VarChar, 20),
new SqlMetaData("Date", SqlDbType.DateTime, -1),
new SqlMetaData("Open", SqlDbType.Decimal, -1),
new SqlMetaData("Close", SqlDbType.Decimal, -1),
new SqlMetaData("High", SqlDbType.Decimal, -1),
new SqlMetaData("Low", SqlDbType.Decimal, -1),
new SqlMetaData("Wap", SqlDbType.Decimal, -1),
new SqlMetaData("Volume", SqlDbType.BigInt, -1),
new SqlMetaData("ErrorMessage", SqlDbType.VarChar, -1)
);
try
{
SqlPipe sqlPip = SqlContext.Pipe;
var result = ClientUtilities.GetQuote(ticker, quoteType, date.Value);
record.SetString(0, result.Ticker);
record.SetDateTime(1, result.Date);
record.SetDecimal(2, (decimal)result.Open);
record.SetDecimal(3, (decimal)result.Close);
record.SetDecimal(4, (decimal)result.High);
record.SetDecimal(5, (decimal)result.Low);
record.SetDecimal(6, (decimal)result.Wap);
record.SetInt64(7, result.Volume);
record.SetString(8, result.ErrorMessage);
SqlContext.Pipe.Send(record);
}
catch (Exception ex)
{
record.SetString(8, ex.Message);
SqlContext.Pipe.Send(record);
}
}
/// <summary>
/// Set property value to sql record.
/// </summary>
/// <param name="dataRecord">
/// Instance of sql data record.
/// </param>
/// <param name="databaseType">
/// Type of database column.
/// </param>
/// <param name="propValue">
/// Value to be inserted.
/// </param>
/// <param name="ordinal">
/// The zero based ordinal of the data record column.
/// </param>
private void SetDataRecordValue(SqlDataRecord dataRecord, DbType databaseType, object propValue, int ordinal)
{
switch (databaseType)
{
case DbType.String:
dataRecord.SetString(ordinal, propValue.ToString());
break;
case DbType.Int32:
dataRecord.SetInt32(ordinal, Convert.ToInt32(propValue));
break;
case DbType.DateTime:
dataRecord.SetDateTime(ordinal, Convert.ToDateTime(propValue));
break;
case DbType.Boolean:
dataRecord.SetBoolean(ordinal, Convert.ToBoolean(propValue));
break;
default:
dataRecord.SetString(ordinal, propValue.ToString());
break;
}
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <Observation> observations)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("Province", SqlDbType.NVarChar, 100),
new SqlMetaData("Country", SqlDbType.NVarChar, 100),
new SqlMetaData("Timestamp", SqlDbType.DateTime2),
new SqlMetaData("Confirmed", SqlDbType.Int),
new SqlMetaData("Deaths", SqlDbType.Int),
new SqlMetaData("Recovered", SqlDbType.Int),
new SqlMetaData("Lat", SqlDbType.Real),
new SqlMetaData("Lon", SqlDbType.Real)
);
// Now yield the Measurements in the Data Record:
foreach (var observation in observations)
{
sdr.SetString(0, observation.Province);
sdr.SetString(1, observation.Country);
sdr.SetDateTime(2, observation.Timestamp);
sdr.SetInt32(3, observation.Confirmed);
sdr.SetInt32(4, observation.Deaths);
sdr.SetInt32(5, observation.Recovered);
sdr.SetFloat(6, (float)observation.Lat);
sdr.SetFloat(7, (float)observation.Lon);
yield return(sdr);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("ExportId", SqlDbType.Int),
new SqlMetaData("OrderColumn", SqlDbType.Int),
new SqlMetaData("ColumnName", SqlDbType.VarChar, 500),
new SqlMetaData("ColumnType", SqlDbType.VarChar, 500),
new SqlMetaData("ColumnValue", SqlDbType.VarChar, 500),
new SqlMetaData("TableName", SqlDbType.VarChar, 500),
new SqlMetaData("STATUS", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt)
);
foreach (tBaseExportColumns data in this)
{
ret.SetInt32(0, data.Id);
ret.SetInt32(1, data.ExportId);
ret.SetInt32(2, data.OrderColumn);
ret.SetString(3, String.IsNullOrEmpty(data.ColumnName) ? "" : data.ColumnName);
ret.SetString(4, String.IsNullOrEmpty(data.ColumnType) ? "" : data.ColumnType);
ret.SetString(5, String.IsNullOrEmpty(data.ColumnValue) ? "" : data.ColumnValue);
ret.SetString(6, String.IsNullOrEmpty(data.TableName) ? "" : data.TableName);
ret.SetInt16(7, data.Status);
ret.SetInt16(8, data.Action);
yield return(ret);
}
}
/// <summary>
/// Create a [Brimborium].[TVP_KeyValue] parameter from a list
/// </summary>
/// <param name="name">the name of the parameter.</param>
/// <param name="e">the list</param>
/// <returns>the parameter</returns>
public static SqlParameter CreateTVP_KeyValueParameter(string name, IEnumerable <KeyValuePair <string, string> > e)
{
var lst = new List <SqlDataRecord>();
var sdrKey = new SqlMetaData("Key", SqlDbType.NVarChar, 255);
var sdrValue = new SqlMetaData("Value", SqlDbType.NVarChar, SqlMetaData.Max);
foreach (var kv in e)
{
if (kv.Value != null)
{
var sdr = new SqlDataRecord(sdrKey, sdrValue);
sdr.SetString(0, kv.Key);
sdr.SetString(1, kv.Value);
lst.Add(sdr);
}
}
var parameter = new SqlParameter(name, SqlDbType.Structured)
{
Direction = ParameterDirection.Input,
TypeName = "[Brimborium].[TVP_KeyValue]",
Value = ((lst.Count == 0) ? null : lst)
};
return(parameter);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
//new SqlMetaData("DISTRIBUTORID", SqlDbType.Int),
new SqlMetaData("ACCOUNTTYPE", SqlDbType.Int),
new SqlMetaData("BANKNAME", SqlDbType.VarChar, 50),
new SqlMetaData("ROUTINGNUMBER", SqlDbType.VarChar, 50),
new SqlMetaData("ACCOUNTNUMBER", SqlDbType.VarChar, 50),
new SqlMetaData("SETASDEFAULT", SqlDbType.SmallInt),
new SqlMetaData("ACTION", SqlDbType.SmallInt),
new SqlMetaData("POSITION", SqlDbType.Int)
);
foreach (tBaseBank data in this)
{
ret.SetInt32(0, data.ID);
ret.SetInt32(1, data.AccountType);
ret.SetString(2, data.BankName);
ret.SetString(3, data.RoutingNumber);
ret.SetString(4, data.AccountNumber);
ret.SetInt16(5, data.Setasdefault);
ret.SetInt16(6, data.Action);
ret.SetInt32(7, data.Position);
yield return(ret);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("PROPERTY", SqlDbType.VarChar, 50),
new SqlMetaData("DATATYPEID", SqlDbType.Int),
new SqlMetaData("NOTES", SqlDbType.VarChar, 500),
new SqlMetaData("STATUS", SqlDbType.Int),
new SqlMetaData("METHODID", SqlDbType.Int),
new SqlMetaData("CREATEDBY", SqlDbType.Int),
new SqlMetaData("UPDATEDBY", SqlDbType.Int)
);
foreach (tBaseOutput data in this)
{
ret.SetInt32(0, data.Id);
ret.SetString(1, data.Property);
ret.SetInt32(2, data.DataTypeId);
ret.SetString(3, data.Notes);
ret.SetInt32(4, data.Status);
ret.SetInt32(5, data.MethodId);
ret.SetInt32(6, data.CreatedBy);
ret.SetInt32(7, data.UpdatedBy);
yield return(ret);
}
}
public static void remove_file(String filePath)
{
// https://msdn.microsoft.com/en-us/library/ms143368(v=vs.110).aspx
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// This text is added only once to the file.
if (File.Exists(filePath))
{
// Attempt to remove the file
try{
File.Delete(filePath);
record.SetString(0, "The file was removed.");
}catch {
record.SetString(0, "The file could not be removed.");
}
}
else
{
record.SetString(0, "The file does not exist.");
}
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
public void ShouldBulkInsert()
{
using var writer = Store.BeginWriteTransaction();
var idMetaData = new SqlMetaData("Id", SqlDbType.NVarChar, 50);
var nameMetadata = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
var referencesMetadata = new SqlMetaData("References", SqlDbType.NVarChar, 50);
var records = new List <SqlDataRecord>();
for (var i = 0; i < 100000; i++)
{
var record = new SqlDataRecord(idMetaData, nameMetadata, referencesMetadata);
record.SetString(0, "MyId-" + i);
record.SetString(1, "Name for " + i);
record.SetString(2, "Some-Other-Doc-" + i);
records.Add(record);
}
var parameters = new CommandParameterValues();
parameters.AddTable("bulkInsertData", new TableValuedParameter("TestSchema.SomeTableInsertData", records));
writer.ExecuteNonQuery("insert into TestSchema.SomeTable ([Id], [Name], [References]) select [Id], [Name], [References] from @bulkInsertData", parameters);
var count = writer.ExecuteScalar <int>("select count(*) from TestSchema.SomeTable");
count.Should().Be(100000);
writer.Commit();
}
public static void write_file(SqlString filePath, SqlString fileContent)
{
// Write provided file content to provided file path
System.IO.File.AppendAllText(filePath.Value, fileContent.Value);
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// This text is added only once to the file.
if (File.Exists(filePath.Value))
{
// Set values for each column in the row
record.SetString(0, "Conent was written.");
}
else
{
// Set values for each column in the row
record.SetString(0, "Conent was written.");
}
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
public static void read_file(String filePath)
{
// https://msdn.microsoft.com/en-us/library/ms143368(v=vs.110).aspx
// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("output", SqlDbType.NVarChar, 4000));
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
// This text is added only once to the file.
if (File.Exists(filePath))
{
// Open the file to read from.
string readText = File.ReadAllText(filePath);
// Write output
record.SetString(0, readText.ToString());
}
else
{
record.SetString(0, "The file does not exist.");
}
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("TestimonialCommentId", SqlDbType.BigInt),
new SqlMetaData("SortOrder", SqlDbType.Int),
new SqlMetaData("Comment", SqlDbType.NVarChar, -1),
new SqlMetaData("Author", SqlDbType.NVarChar, 100),
new SqlMetaData("AuthorTitle", SqlDbType.NVarChar, 100),
new SqlMetaData("CommentDate", SqlDbType.NVarChar, 30)
);
foreach (TestimonialComment comment in this)
{
if (comment.TestimonialCommentId != 0)
{
sdr.SetInt64(0, comment.TestimonialCommentId);
}
else
{
sdr.SetDBNull(0);
}
sdr.SetInt32(1, comment.SortOrder);
sdr.SetString(2, comment.Comment);
sdr.SetString(3, comment.Author);
sdr.SetString(4, comment.AuthorTitle);
sdr.SetString(5, comment.CommentDate);
yield return(sdr);
}
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new[]
{
new SqlMetaData("ID", SqlDbType.VarChar, 36),
new SqlMetaData("ACTIVITYID", SqlDbType.VarChar, 36),
new SqlMetaData("ACTIVITYSTATE", SqlDbType.Int),
new SqlMetaData("LATITUDE", SqlDbType.Float),
new SqlMetaData("LONGITUDE", SqlDbType.Float),
new SqlMetaData("TRACEDATE", SqlDbType.DateTime),
}
);
foreach (var trace in this)
{
sdr.SetString(0, trace.Id);
sdr.SetString(1, trace.ActivityId);
sdr.SetInt32(2, (int)trace.ActivityState);
sdr.SetDouble(3, trace.Latitude);
sdr.SetDouble(4, trace.Longitude);
sdr.SetDateTime(5, trace.TraceDate);
yield return(sdr);
}
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
var items = new List <SqlDataRecord>();
foreach (var param in _transfers)
{
var rec = new SqlDataRecord(_transferIdMetaData, _senderAccountIdMetaData, _receiverAccountIdMetaData, _requiredPaymentId, _commitmentId, _amount, _type, _collectionPeriodName);
rec.SetInt64(0, param.TransferId);
rec.SetInt64(1, param.SenderAccountId);
rec.SetInt64(2, param.ReceiverAccountId);
rec.SetGuid(3, param.RequiredPaymentId);
rec.SetInt64(4, param.CommitmentId);
rec.SetDecimal(5, param.Amount);
rec.SetString(6, param.Type.ToString());
rec.SetString(7, param.CollectionPeriodName);
items.Add(rec);
}
var p = sqlCommand.Parameters.Add("@transfers", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "[Data_Load].[TransferEntity]";
p.Value = items;
}
private IEnumerable <SqlDataRecord> ToSqlDataRecords(IEnumerable <Station> stations)
{
// Construct the Data Record with the MetaData:
SqlDataRecord sdr = new SqlDataRecord(
new SqlMetaData("Identifier", SqlDbType.NVarChar, 5),
new SqlMetaData("Name", SqlDbType.NVarChar, 255),
new SqlMetaData("StartDate", SqlDbType.DateTime2),
new SqlMetaData("EndDate", SqlDbType.DateTime2),
new SqlMetaData("StationHeight", SqlDbType.SmallInt),
new SqlMetaData("State", SqlDbType.NVarChar, 255),
new SqlMetaData("Latitude", SqlDbType.Real),
new SqlMetaData("Longitude", SqlDbType.Real)
);
// Now yield the Measurements in the Data Record:
foreach (var station in stations)
{
sdr.SetString(0, station.Identifier);
sdr.SetString(1, station.Name);
sdr.SetDateTime(2, station.StartDate);
sdr.SetNullableDateTime(3, station.EndDate);
sdr.SetInt16(4, station.StationHeight);
sdr.SetString(5, station.State);
sdr.SetFloat(6, station.Latitude);
sdr.SetFloat(7, station.Longitude);
yield return(sdr);
}
}
public List <SqlDataRecord> BuildBaselines(List <ArtBaselineModel> baselines, int siteId)
{
var records = new List <SqlDataRecord>();
var errors = new List <ArtBaselineModel>();
SqlMetaData[] sqlMetaData = new SqlMetaData[8];
sqlMetaData[0] = new SqlMetaData("PatientIdentifier", SqlDbType.NVarChar, 450);
sqlMetaData[1] = new SqlMetaData("IdNo", SqlDbType.Int);
sqlMetaData[2] = new SqlMetaData("HivConfirmationDate", SqlDbType.DateTime2);
sqlMetaData[3] = new SqlMetaData("EnrolmentDate", SqlDbType.DateTime2);
sqlMetaData[4] = new SqlMetaData("ArtDate", SqlDbType.DateTime2);
sqlMetaData[5] = new SqlMetaData("DispositionDate", SqlDbType.NVarChar, 450);
sqlMetaData[6] = new SqlMetaData("DispositionCode", SqlDbType.NVarChar, 50);
sqlMetaData[7] = new SqlMetaData("FacilityId", SqlDbType.Int);
var duplicateBaselines = baselines.GroupBy(x => x.PatientIdentifier).Where(x => x.Count() > 1).Select(x => x.Key).ToList();
baselines.ForEach(p =>
{
var row = new SqlDataRecord(sqlMetaData);
row.SetString(0, p.PatientIdentifier);
row.SetInt32(1, p.IdNo);
row.SetDateTime(2, p.HivConfirmationDate != null ? p.HivConfirmationDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetDateTime(3, p.EnrolmentDate != null ? p.EnrolmentDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetDateTime(4, p.ArtDate != null ? p.ArtDate.Value : (DateTime)SqlDateTime.MinValue);
row.SetString(5, !string.IsNullOrEmpty(p.DispositionDate)? p.DispositionDate : string.Empty);
row.SetString(6, !string.IsNullOrEmpty(p.DispositionCode) ? p.DispositionCode : string.Empty);
row.SetInt32(7, siteId);
records.Add(row);
});
return(records);
}
internal static SqlDataRecord MapToTvp(this LogStackLookup stackLookup)
{
var record = new SqlDataRecord(Constants.StackLookupMetadata);
record.SetString(0, stackLookup.StackHash);
record.SetString(1, stackLookup.StackTraceText);
return(record);
}
internal static SqlDataRecord MapToTvp(this LogMessageLookup message)
{
var record = new SqlDataRecord(Constants.MessageLookupMetaData);
record.SetString(0, message.LogMessageHash);
record.SetString(1, message.MessageText);
return(record);
}
internal static SqlDataRecord MapToTvp(this LogAdditionalDataKVP addlData)
{
var record = new SqlDataRecord(Constants.AddlDataMetaData);
record.SetGuid(0, addlData.LogUUID);
record.SetString(1, addlData.Key);
record.SetString(2, addlData.Value);
return(record);
}
internal static SqlDataRecord MapToTvp(this LogCallingMethodParameter message)
{
var recrod = new SqlDataRecord(Constants.CallingMethodIOData);
recrod.SetGuid(0, message.OwnerLog.LogUUID);
recrod.SetBoolean(1, message.IsInput);
recrod.SetString(2, message.ParameterName);
recrod.SetString(3, message.Value);
return(recrod);
}
private static void BindFile(FileInfo _file, SqlDataRecord record)
{
record.SetSqlDateTime(0, _file.Exists ? _file.CreationTime : SqlDateTime.Null);
record.SetSqlDateTime(1, _file.Exists ? _file.LastAccessTime : SqlDateTime.Null);
record.SetSqlDateTime(2, _file.Exists ? _file.LastWriteTime : SqlDateTime.Null);
record.SetSqlBoolean(3, _file.Exists);
record.SetString(4, _file.Name);
record.SetString(5, _file.DirectoryName);
record.SetString(6, _file.Extension);
record.SetSqlInt64(7, _file.Exists ? _file.Length : SqlInt64.Null);
}
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var row = new SqlDataRecord(new SqlMetaData("value1", SqlDbType.NVarChar, 1000), new SqlMetaData("value2", SqlDbType.NVarChar, 1000));
foreach (Pair <string, string> kv in this)
{
row.SetString(0, kv.Item1);
row.SetString(1, kv.Item2);
yield return(row);
}
}
///<summary>RSS</summary>
public static void RSS()
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
// Retrieve the RSS feed
//XPathDocument doc = new PathDocument("http://msdn.microsoft.com/sql/rss.xml");
//XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
XPathDocument doc = new XPathDocument("http://msdn.microsoft.com/rss.xml");
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select("//item");
// create metadata for four columns
// three of them are string types and one of them is a datetime
SqlMetaData[] rss_results = new SqlMetaData[4];
rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
rss_results[1] = new SqlMetaData("Publication Date",
SqlDbType.DateTime);
rss_results[2] = new SqlMetaData("Description",
SqlDbType.NVarChar, 2000);
rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(rss_results);
// cache a SqlPipe instance to avoid repeated calls to
// SqlContext.GetPipe()
SqlPipe sqlpipe = SqlContext.Pipe;
// send the metadata, do not send the values in the data record
sqlpipe.SendResultsStart(record);
// for each xml node returned, extract four pieces
// of information and send back each item as a row
while (i.MoveNext())
{
record.SetString(0, (string)
i.Current.Evaluate("string(title[1]/text())"));
record.SetDateTime(1, DateTime.Parse((string)
i.Current.Evaluate("string(pubDate[1]/text())")));
record.SetString(2, (string)
i.Current.Evaluate("string(description[1]/text())"));
record.SetString(3, (string)
i.Current.Evaluate("string(link[1]/text())"));
sqlpipe.SendResultsRow(record);
}
// signal end of results
sqlpipe.SendResultsEnd();
}
}
/// <summary>
/// Retrieves master page zone element records.
/// </summary>
/// <returns>Enumerable SQL data records.</returns>
IEnumerator <SqlDataRecord> IEnumerable <SqlDataRecord> .GetEnumerator()
{
var sdr = new SqlDataRecord(
new SqlMetaData("MasterPageZoneId", SqlDbType.BigInt),
new SqlMetaData("MasterPageZoneElementId", SqlDbType.BigInt),
new SqlMetaData("MasterPageZoneSortOrder", SqlDbType.Int),
new SqlMetaData("SortOrder", SqlDbType.Int),
new SqlMetaData("ElementId", SqlDbType.BigInt),
new SqlMetaData("BeginRender", SqlDbType.NVarChar, -1),
new SqlMetaData("EndRender", SqlDbType.NVarChar, -1)
);
foreach (MasterPageZoneElement masterPageZoneElement in this)
{
if (masterPageZoneElement.MasterPageZoneId != 0)
{
sdr.SetInt64(0, masterPageZoneElement.MasterPageZoneId);
}
else
{
sdr.SetDBNull(0);
}
if (masterPageZoneElement.MasterPageZoneElementId != 0)
{
sdr.SetInt64(1, masterPageZoneElement.MasterPageZoneElementId);
}
else
{
sdr.SetDBNull(1);
}
sdr.SetInt32(2, masterPageZoneElement.MasterPageZoneSortOrder);
sdr.SetInt32(3, masterPageZoneElement.SortOrder);
sdr.SetInt64(4, masterPageZoneElement.ElementId);
if (masterPageZoneElement.BeginRender != null)
{
sdr.SetString(5, masterPageZoneElement.BeginRender);
}
else
{
sdr.SetDBNull(5);
}
if (masterPageZoneElement.EndRender != null)
{
sdr.SetString(6, masterPageZoneElement.EndRender);
}
else
{
sdr.SetDBNull(6);
}
yield return(sdr);
}
}
private static SqlDataRecord CreateVideoDataRecord(YoutubeVideo video)
{
int index = 0;
var dataRecord = new SqlDataRecord(VideoMetaData);
dataRecord.SetString(index++, video.ChannelId);
dataRecord.SetString(index++, video.Id);
dataRecord.SetString(index++, video.Title);
dataRecord.SetInt64(index++, video.Duration.Ticks);
dataRecord.SetDateTimeOffset(index++, video.PublishedAt);
dataRecord.SetString(index++, video.Thumbnail);
return(dataRecord);
}
private static IEnumerable <SqlDataRecord> ConvertToSqlDataRecord(IEnumerable <Movie> movies)
{
var sqlDataRecord = new SqlDataRecord(s_sqlMetaDataCreateMovies);
foreach (var movie in movies)
{
sqlDataRecord.SetString(0, movie.Title);
sqlDataRecord.SetString(1, GenreParser.ToString(movie.Genre));
sqlDataRecord.SetInt32(2, movie.Year);
sqlDataRecord.SetString(3, movie.ImageUrl);
yield return(sqlDataRecord);
}
}
public static void _2(SqlDateTime dateTime, SqlInt32 minAge)
{
var rec = new SqlDataRecord(
new SqlMetaData("LastName", SqlDbType.NVarChar, 50),
new SqlMetaData("FirstName", SqlDbType.NVarChar, 50),
new SqlMetaData("EmailAddress", SqlDbType.NVarChar, 50),
new SqlMetaData("Age", SqlDbType.Int)
);
using (var mConnection = new SqlConnection("context connection=true"))
{
var mCmd = new SqlCommand(
"SELECT LastName, FirstName, EmailAddress, DATEDIFF(year, BirthDate, @DATE) Age FROM HumanResources.Employee e " +
"JOIN Person.Person p ON p.BusinessEntityId = e.BusinessEntityId " +
"JOIN Person.EmailAddress ea ON ea.BusinessEntityId = p.BusinessEntityId " +
"WHERE DATEDIFF(year, BirthDate, @DATE) > @MINAGE",
mConnection
);
mCmd.Parameters.Add("@DATE", SqlDbType.DateTime).Value = dateTime;
mCmd.Parameters.Add("@MINAGE", SqlDbType.Int).Value = minAge;
try
{
mConnection.Open();
var rdr = mCmd.ExecuteReader();
if (rdr.HasRows)
{
SqlContext.Pipe.SendResultsStart(rec);
while (rdr.Read())
{
rec.SetString(0, (string)rdr["LastName"]);
rec.SetString(1, (string)rdr["FirstName"]);
rec.SetString(2, (string)rdr["EmailAddress"]);
rec.SetInt32(3, (int)rdr["Age"]);
SqlContext.Pipe.SendResultsRow(rec);
}
}
SqlContext.Pipe.SendResultsEnd();
}
catch (SqlException e)
{
SqlContext.Pipe.Send(e.Message.ToString());
}
finally
{
mConnection.Close();
}
}
}
private SqlDataRecord[] CreateSqlDataRecords(NewStreamEvent[] events)
{
var sqlDataRecords = events.Select(@event =>
{
var record = new SqlDataRecord(_appendToStreamSqlMetadata);
record.SetGuid(1, @event.EventId);
record.SetString(3, @event.Type);
record.SetString(4, @event.JsonData);
record.SetString(5, @event.JsonMetadata);
return(record);
}).ToArray();
return(sqlDataRecords);
}
