private static SqlDataRecord FillRecord(Int32 pk, SqlDataRecord record)
{
Int32 age = SlowRandom(16, 99);
string sourceString = "Age: " + age.ToString();
DateTime sourceDate = DateTime.UtcNow;
var data = /*salt + */sourceString;
string key = "Top Secret Key";
var encData = AES.EncryptBytes(data, key);
//var encDataBytes = Encoding.Unicode.GetBytes(encData);
var decData = AES.DecryptBytes(encData, key);
var sha = new SHA256Managed();
byte[] dataSHA256 = sha.ComputeHash(encData/*Bytes*/);
sha.Dispose();
// конвертирую хеш из byte[16] в строку шестнадцатиричного формата
// (вида «3C842B246BC74D28E59CCD92AF46F5DA»)
// это опциональный этап, если вам хеш нужен в строковом виде
// string sha512hex = BitConverter.ToString(dataSHA512).Replace("-", string.Empty);
record.SetInt32(0, pk);
record.SetDateTime(1, sourceDate);
record.SetString(2, sourceString);
record.SetString(3, Convert.ToBase64String(dataSHA256)); // sha256
record.SetString(4, Convert.ToBase64String(encData)); // Encrypted
record.SetString(5, decData); // Decrypted
return record;
}
public static void SetDateTimeRecord(SqlDataReader dr, string columnName, SqlDataRecord record, int ordinal)
{
if (dr[columnName] == DBNull.Value)
record.SetDBNull(ordinal);
else
record.SetDateTime(ordinal, Convert.ToDateTime(dr[columnName]));
}
public static void PushLeaseBlobResponse(Responses.LeaseBlobResponse lbr)
{
SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
new SqlMetaData("LeaseId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("LeaseBreakTimeSeconds", System.Data.SqlDbType.Int),
new SqlMetaData("RequestId", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 4000)
});
if (lbr.LeaseId.HasValue)
record.SetGuid(0, lbr.LeaseId.Value);
record.SetDateTime(1, lbr.Date);
if (lbr.LeaseTimeSeconds.HasValue)
record.SetInt32(2, lbr.LeaseTimeSeconds.Value);
record.SetGuid(3, lbr.RequestID);
record.SetString(4, lbr.Version);
SqlContext.Pipe.SendResultsStart(record);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
private static SqlDataRecord CreateDateTimeRecord(DateTime? value)
{
var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.DateTime2));
if (value.HasValue)
record.SetDateTime(0, value.Value);
else
record.SetDBNull(0);
return record;
}
public static void CopyBlob(
SqlString destinationAccount, SqlString destinationSharedKey, SqlBoolean useHTTPS,
SqlString sourceAccountName,
SqlString sourceContainerName, SqlString sourceBlobName,
SqlGuid sourceLeaseId, SqlGuid destinationLeaseId,
SqlString destinationContainerName, SqlString destinationBlobName,
SqlString xmsclientrequestId)
{
AzureBlobService absDest = new AzureBlobService(destinationAccount.Value, destinationSharedKey.Value, useHTTPS.Value);
Container contDest = absDest.GetContainer(destinationContainerName.Value);
ITPCfSQL.Azure.Blob bbDest = new Azure.Blob(contDest, destinationBlobName.Value);
AzureBlobService absSrc = new AzureBlobService(sourceAccountName.Value, "", useHTTPS.Value);
Container contSrc = absSrc.GetContainer(sourceContainerName.Value);
ITPCfSQL.Azure.Blob bbSrc = new Azure.Blob(contSrc, sourceBlobName.Value);
Responses.CopyBlobResponse resp = bbSrc.Copy(bbDest,
sourceLeaseID: sourceLeaseId.IsNull ? (Guid?)null : sourceLeaseId.Value,
destinationLeaseID: destinationLeaseId.IsNull ? (Guid?)null : destinationLeaseId.Value,
xmsclientrequestId: xmsclientrequestId.IsNull ? null : xmsclientrequestId.Value);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData[]
{
new SqlMetaData("BlobCopyStatus", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("CopyId", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
new SqlMetaData("ETag", System.Data.SqlDbType.NVarChar, 255),
new SqlMetaData("LastModified", System.Data.SqlDbType.DateTime),
new SqlMetaData("RequestID", System.Data.SqlDbType.UniqueIdentifier),
new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 255)
});
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, resp.BlobCopyStatus.ToString());
record.SetString(1, resp.CopyId);
record.SetDateTime(2, resp.Date);
record.SetString(3, resp.ETag);
record.SetDateTime(4, resp.LastModified);
record.SetGuid(5, resp.RequestID);
record.SetString(6, resp.Version);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void CreateNewRecordProc()
{
DateTime now = DateTime.UtcNow;
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PK", SqlDbType.Int),
new SqlMetaData("UTC_DateTime", SqlDbType.DateTime),
new SqlMetaData("Source", SqlDbType.NVarChar, 128),
new SqlMetaData("Encrypted_SHA256", SqlDbType.NVarChar, 32),
new SqlMetaData("Encrypted_AES", SqlDbType.NVarChar, 512),
new SqlMetaData("Decrypted", SqlDbType.NVarChar, 128));
SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < SlowRandom(1, 50); i++)
{
SqlContext.Pipe.SendResultsRow(FillRecord(i, record));
}
TimeSpan delta = DateTime.UtcNow - now;
record.SetInt32(0, 0);
record.SetDateTime(1, DateTime.UtcNow);
record.SetString(2, "Total ms:");
record.SetString(3, delta.Milliseconds.ToString());
record.SetString(4, "");
record.SetString(5, "");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
public static void IR_SM_Province_PerCustomer_PerDistributor(string productIds, DateTime startDate, DateTime endDate, string provinceIds, string distributorIds)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
try
{
List<string> prodIds = new List<string>();
string[] split2 = productIds.Split('-');
foreach (string s in split2)
if (s.ToLower().Trim() != "")
prodIds.Add(s.Trim().ToLower());
List<string> provIds = new List<string>();
string[] split = provinceIds.Split('-');
foreach (string s in split)
if (s.ToLower().Trim() != "")
provIds.Add(s.Trim().ToLower());
if (distributorIds != "")
{
List<string> distIdsList = new List<string>();
string[] splited_distIds = distributorIds.Split('-');
foreach (string distCode in splited_distIds)
if (distCode.ToLower().Trim() != "")
distIdsList.Add(distCode.Trim().ToLower());
foreach (string distId in distIdsList)
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid and [Global].[Distributors].[DistID] = @distid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
commands[commands.Count - 1].Parameters.AddWithValue("@distid", distId);
}
}
}
}
else
{
foreach (string provId in provIds)
{
foreach (string proId in prodIds)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID], [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID], [Global].[Distributors].[DistID], [Global].[Distributors].[DistNameEnglish] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] inner join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] inner join [Global].[Distributors] on [Global].[Distributors].[DistID] = [Sales].[DSSales].[DistID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid and [Global].[City].[ProvinceID] = @provinceid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", proId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
commands[commands.Count - 1].Parameters.AddWithValue("@provinceid", provId);
}
}
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("DistID", SqlDbType.Int),
new SqlMetaData("DistNameEnglish", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
int ProvinceID = Convert.ToInt32(reader["ProvinceID"]);
int DistID = Convert.ToInt32(reader["DistID"]);
string DistName = Convert.ToString(reader["DistNameEnglish"]);
record.SetInt32(0, DSID);
record.SetString(1, DSName);
record.SetInt32(2, ProductID);
record.SetInt32(3, SalesQty);
record.SetDateTime(4, CheckGDate);
record.SetInt32(5, ProvinceID);
record.SetInt32(6, DistID);
record.SetString(7, DistName);
pipe.SendResultsRow(record);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
throw ex;
}
}
}
public static void IR_SM_Total_PerCustomer_PerChannel(string productIds, DateTime startDate, DateTime endDate)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
SqlPipe pipe = SqlContext.Pipe;
List<SqlCommand> commands = new List<SqlCommand>();
List<string> productIdsList = new List<string>();
string[] splited_ids = productIds.Split('-');
foreach (string diCode in splited_ids)
{
if (diCode.ToLower().Trim() != "")
productIdsList.Add(diCode.Trim().ToLower());
}
foreach (string pId in productIdsList)
{
commands.Add(new SqlCommand("select [Sales].[DS].[DSID], [Sales].[DS].[DSName],[Global].[Products].[ProductID] , [Sales].[DSSales].[SalesQty], [Sales].[DSSales].[CheckGDate] ,[Global].[City].[ProvinceID] , [dbo].[IR_POS_Channel].[POS_Channel], [dbo].[IR_POS_Type].[POS_Type] from [Sales].[DS] inner join [Sales].[DSSales] on [Sales].[DS].[DSID] = [Sales].[DSSales].[DSID] inner join [Global].[Products] on [Global].[Products].[ProductID] = [Sales].[DSSales].[ProductID] left outer join [Global].[City] on [Global].[City].[CityID] = [Sales].[DS].[DSCityID] left outer join [dbo].[IR_POS_Classification] on [dbo].[IR_POS_Classification].[POS_ID] = [Sales].[DS].[DSID] left outer join [dbo].[IR_POS_Type] on [dbo].[IR_POS_Type].[Type_ID] = [dbo].[IR_POS_Classification].[POS_Type_ID] left outer join [dbo].[IR_POS_Channel] on [dbo].[IR_POS_Channel].[Channel_ID] = [dbo].[IR_POS_Classification].[POS_Channel_ID] where [Sales].[DSSales].[CheckGDate] between @startdate and @enddate and [Global].[Products].[ProductID] = @productid"));
commands[commands.Count - 1].Parameters.AddWithValue("@productid", pId);
commands[commands.Count - 1].Parameters.AddWithValue("@startdate", startDate);
commands[commands.Count - 1].Parameters.AddWithValue("@enddate", endDate);
}
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
new SqlMetaData("DSID", SqlDbType.Int),
new SqlMetaData("DSName", SqlDbType.NVarChar, 255, 1033, SqlCompareOptions.None),
new SqlMetaData("ProductID", SqlDbType.Int),
new SqlMetaData("SalesQty", SqlDbType.Int),
new SqlMetaData("CheckGDate", SqlDbType.DateTime),
new SqlMetaData("ProvinceID", SqlDbType.Int),
new SqlMetaData("ChannelType", SqlDbType.NVarChar, 255),
new SqlMetaData("Channel", SqlDbType.NVarChar, 255));
pipe.SendResultsStart(record);
foreach (SqlCommand cmd in commands)
{
try
{
cmd.Connection = con;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int IdCounter = 1;
while (reader.Read())
{
try
{
int DSID = Convert.ToInt32(reader["DSID"]);
string DSName = Convert.ToString(reader["DSName"]);
int ProductID = Convert.ToInt32(reader["ProductID"]);
int SalesQty = Convert.ToInt32(reader["SalesQty"]);
DateTime CheckGDate = Convert.ToDateTime(reader["CheckGDate"]);
record.SetInt32(0, IdCounter);
record.SetInt32(1, DSID);
record.SetString(2, DSName);
record.SetInt32(3, ProductID);
record.SetInt32(4, SalesQty);
record.SetDateTime(5, CheckGDate);
Utils.SetIntRecord(reader, "ProvinceID", record, 6);
Utils.SetStringRecord_NullEmpty(reader,"POS_Type",record,7);
Utils.SetStringRecord_NullEmpty(reader, "POS_Channel", record, 8);
pipe.SendResultsRow(record);
IdCounter++;
}
catch { }
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
con.Close();
}
}
pipe.SendResultsEnd();
}
}
public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
int ordinal)
{
if (!sqlDescription.HasDbType)
{
throw new InvalidDataException("SqlDbType can not be null");
}
if (value == null)
{
sqlDataRecord.SetDBNull(ordinal);
return;
}
switch (sqlDescription.SqlDbType)
{
case SqlDbType.BigInt:
var ll = value as long?;
if (!ll.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetInt64(ordinal, ll.Value);
break;
case SqlDbType.Binary:
var bb = value as byte?;
if (!bb.HasValue)
{
throw new Exception("Value is not BigInt");
}
sqlDataRecord.SetSqlByte(ordinal, bb.Value);
break;
case SqlDbType.Bit:
var bit = value as bool?;
if (!bit.HasValue)
{
throw new Exception("Value is not Bit");
}
sqlDataRecord.SetBoolean(ordinal, bit.Value);
break;
case SqlDbType.NChar:
case SqlDbType.Char:
var chr = value as char?;
if (!chr.HasValue)
{
throw new Exception("Value is not Char");
}
sqlDataRecord.SetChar(ordinal, chr.Value);
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
case SqlDbType.Date:
case SqlDbType.DateTime2:
var dt = value as DateTime?;
if (!dt.HasValue)
{
throw new Exception("Value is not DateTime");
}
sqlDataRecord.SetDateTime(ordinal, dt.Value);
break;
case SqlDbType.Decimal:
case SqlDbType.Money:
case SqlDbType.SmallMoney:
var dc = value as decimal?;
if (!dc.HasValue)
{
throw new Exception("Value is not Decimal");
}
sqlDataRecord.SetDecimal(ordinal, dc.Value);
break;
case SqlDbType.Float:
var d = value as double?;
if (!d.HasValue)
{
throw new Exception("Value is not Double");
}
sqlDataRecord.SetDouble(ordinal, d.Value);
break;
case SqlDbType.Image:
case SqlDbType.VarBinary:
var bytes = value as byte[];
if (bytes == null)
{
throw new Exception("Value is not byte array");
}
sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
break;
case SqlDbType.Int:
var integer = value as int?;
if (integer == null)
{
var ushortValue = (value as ushort?);
if (ushortValue == null)
{
throw new Exception("Value is not int or ushort");
}
integer = ushortValue.Value;
}
sqlDataRecord.SetInt32(ordinal, integer.Value);
break;
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Text:
case SqlDbType.Xml:
var str = value as string;
if (str == null)
{
var chars = value as char[];
if (chars == null)
{
throw new Exception("Value is not string or char array");
}
str = new string(chars);
}
sqlDataRecord.SetString(ordinal, str);
break;
case SqlDbType.Real:
var f = value as float?;
if (f == null)
{
throw new Exception("Value is not float");
}
sqlDataRecord.SetFloat(ordinal, f.Value);
break;
case SqlDbType.UniqueIdentifier:
var guid = value as Guid?;
if (guid == null)
{
throw new Exception("Value is not Guid");
}
sqlDataRecord.SetGuid(ordinal, guid.Value);
break;
case SqlDbType.SmallInt:
var sh = value as short?;
if (sh == null)
{
var uByte = value as sbyte?;
if (uByte == null)
{
throw new Exception("Value is not short or sbyte");
}
sh = uByte.Value;
}
sqlDataRecord.SetInt16(ordinal, sh.Value);
break;
case SqlDbType.TinyInt:
var b = value as byte?;
if (b == null)
{
throw new Exception("Value is not byte");
}
sqlDataRecord.SetByte(ordinal, b.Value);
break;
case SqlDbType.Time:
var timeSpan = value as TimeSpan?;
if (timeSpan == null)
{
throw new Exception("Value is not TimeSpan");
}
sqlDataRecord.SetTimeSpan(ordinal, timeSpan.Value);
break;
case SqlDbType.DateTimeOffset:
var dateTimeOffset = value as DateTimeOffset?;
if (dateTimeOffset == null)
{
throw new Exception("Value is not DateTimeOffset");
}
sqlDataRecord.SetDateTimeOffset(ordinal, dateTimeOffset.Value);
break;
case SqlDbType.Structured:
case SqlDbType.Udt:
case SqlDbType.Timestamp:
case SqlDbType.Variant:
throw new NotImplementedException();
default:
throw new ArgumentOutOfRangeException();
}
}