public static IEnumerable FindPHkeysAndSlots(SqlInt16 partsnap, SqlInt16 fofsnap, SqlString temp_partid_table_name, SqlInt32 numparts)
{
//string temp_partid_table = "#temp_partid_list";
List<PhkeySlot> keysAndSlots = new List<PhkeySlot>();
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
string getFoFGroupIDsCommandString = "select partid from " + temp_partid_table_name.ToString();
SqlCommand getFoFGroupIDsListCommand = new SqlCommand(getFoFGroupIDsCommandString, connection);
SqlDataReader getFOFGroupIDsReader = getFoFGroupIDsListCommand.ExecuteReader();
Int64[] partids = new Int64[(int) numparts];
int numlines = 0;
while (getFOFGroupIDsReader.Read())
{
partids[numlines] = Int64.Parse(getFOFGroupIDsReader["partid"].ToString());
++numlines;
}
getFOFGroupIDsReader.Close();
// Find the index blocks we need to look in
HashSet<Int64> blocks = new HashSet<Int64>();
foreach (Int64 id in partids)
{
blocks.Add((id - 1) / 1024 / 1024);
}
//means we want all snaps
string rawCommand = "";
if (partsnap < 0)
{
rawCommand = "select a.snap, a.phkey, a.slot from particleDB.dbo.index{0} a, " + temp_partid_table_name.ToString() + " b "
+ " where a.partid = b.partid";
}
else
{
rawCommand = "select a.snap, a.phkey, a.slot from particleDB.dbo.index{0} a, " + temp_partid_table_name.ToString() + " b "
+ " where a.partid = b.partid and a.snap = @partsnap";
}
foreach (Int64 blockID in blocks)
{
string joinFoFGroupIndexCommandString = string.Format(rawCommand, blockID);
SqlCommand joinFofGroupIndexCommand = new SqlCommand(joinFoFGroupIndexCommandString, connection);
SqlParameter partsnapParam = new SqlParameter("@partsnap", System.Data.SqlDbType.SmallInt);
partsnapParam.Value = partsnap;
joinFofGroupIndexCommand.Parameters.Add(partsnapParam);
SqlDataReader joinReader = joinFofGroupIndexCommand.ExecuteReader();
while (joinReader.Read())
{
int snap = Int32.Parse(joinReader["snap"].ToString());
int phkey = Int32.Parse(joinReader["phkey"].ToString());
short slot = Int16.Parse(joinReader["slot"].ToString());
PhkeySlot current = new PhkeySlot(snap, phkey, slot);
keysAndSlots.Add(current);
}
joinReader.Close();
}
}
return keysAndSlots;
}
private static void throwExceptionIfSchemaIsEmpty(SqlString command, DataTable schema)
{
if (schema == null)
{
throw new InvalidResultSetException("The command [" + command.ToString() + "] did not return a result set");
}
}
public static SqlChars GetAlterStatementWithoutSchemaBinding(SqlString createStatement)
{
var startPosition = GetStartPosition(createStatement);
var regex = new Regex(@"CREATE\s+VIEW(\s*.*?\s*)WITH\s+SCHEMABINDING\s+AS");
var output = regex.Replace(createStatement.ToString(), @"ALTER VIEW$1AS", 1, startPosition);
return new SqlChars(output);
}
public static SqlString GetPinYin(SqlString word)
{
// 汉字 to 拼音首字母.
string result = GetChineseSpell(word.ToString());
// 返回.
return new SqlString(result);
}
public void Execute(SqlString command)
{
SqlConnection conn = null;
try
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
{
String connectionString = CreateConnectionStringToContextDatabase();
conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = command.ToString();
cmd.ExecuteNonQuery();
}
}
catch (SecurityException se)
{
throw new CommandExecutorException("Error connecting to database. You may need to create tSQLt assembly with EXTERNAL_ACCESS.", se);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public static SqlDouble DetermineCompressionRate(SqlBinary originalEncodedValue, SqlString decodedValue)
{
int decodedValueLength = decodedValue.ToString().Length;
int encodedValueLength = originalEncodedValue.Value.Length;
double rate = (double)encodedValueLength / (double)decodedValueLength;
return new SqlDouble(rate);
}
public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords, SqlInt32 sortType, SqlInt32 upToLevel)
{
SPMultipleSkylineBNLLevel skyline = new SPMultipleSkylineBNLLevel();
string[] additionalParameters = new string[5];
additionalParameters[4] = upToLevel.ToString();
skyline.GetSkylineTable(strQuery.ToString(), strOperators.ToString(), numberOfRecords.Value, false, Helper.CnnStringSqlclr, Helper.ProviderClr, additionalParameters, sortType.Value, true);
}
public CompStrings(SqlString from, SqlString to, SqlString replace, SqlString tolerance, SqlString comp) {
this.from = from.ToString();
this.to = to.ToString();
this.replace = replace.ToString();
this.tolerance = tolerance.ToString();
this.comp = comp.ToString();
}
public static int GetParam(SqlString s)
{
// 在此处放置代码
Regex _break_comp = new Regex(@"\{\d\}", RegexOptions.Compiled);
return _break_comp.Matches(s.ToString() ).Count;
//return new SqlString("Hello");
}
// 全ての件数を取得
public int[] countUsbLog()
{
int[] arrCount = new int[2];
DateTime dtToday = DateTime.Now;
string today = dtToday.ToString("yyyy-MM-dd");
try {
db.conn.Open();
// 最初のループで今日のレコード件数について取得
for (int i = 0; i < 2; i++) {
MySqlCommand commCountUsbLog = new MySqlCommand("SELECT COUNT(log_id) FROM t_log", db.conn);
// 最初の実行で今日のログを取得するSQLを実行
if (i == 0) {
SqlString condition = new SqlString(" WHERE log_time LIKE '" + today + "%'");
commCountUsbLog.CommandText += condition.ToString();
}
db.debugSql = commCountUsbLog.CommandText;
arrCount[i] = Convert.ToInt32(commCountUsbLog.ExecuteScalar());
}
db.conn.Close();
} catch (MySqlException e) {
db.exceptionMsg = "エラーコード：" + e.ErrorCode + "<br>エラー内容：" + e.Message + "<br>エラー原因：" + e.Source;
Debug.Listeners.Add(new TextWriterTraceListener(Console.Out));
Debug.WriteLine(db.exceptionMsg);
Debug.WriteLine(db.debugSql);
}
return arrCount;
}
public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords,
SqlInt32 sortType, SqlInt32 count, SqlInt32 dimension, SqlString algorithm, SqlBoolean hasIncomparable)
{
try
{
Type strategyType = Type.GetType("prefSQL.SQLSkyline." + algorithm.ToString());
if (!typeof (SkylineStrategy).IsAssignableFrom(strategyType))
{
throw new Exception("passed algorithm is not of type SkylineStrategy.");
}
var strategy = (SkylineStrategy) Activator.CreateInstance(strategyType);
strategy.Provider = Helper.ProviderClr;
strategy.ConnectionString = Helper.CnnStringSqlclr;
strategy.RecordAmountLimit = numberOfRecords.Value;
strategy.HasIncomparablePreferences = hasIncomparable.Value;
strategy.SortType = sortType.Value;
var skylineSample = new SkylineSampling
{
SubsetCount = count.Value,
SubsetDimension = dimension.Value,
SelectedStrategy = strategy
};
DataTable dataTableReturn = skylineSample.GetSkylineTable(strQuery.ToString(), strOperators.ToString());
SqlDataRecord dataRecordTemplate = skylineSample.DataRecordTemplateForStoredProcedure;
//throw new Exception(dataTableReturn.Rows[0].Table.Columns.Count.ToString());
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.SendResultsStart(dataRecordTemplate);
foreach (DataRow recSkyline in dataTableReturn.Rows)
{
for (var 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
var strError = "Error in prefSQL_SkylineSampling: ";
strError += ex.Message;
if (SqlContext.Pipe != null)
{
SqlContext.Pipe.Send(strError);
}
}
}
public void Accumulate(SqlString Value, SqlString delimiter)
{
_delimiter = delimiter.Value;
if (Value.IsNull == false)
{
_elements.Add(Value.ToString());
}
}
public static void ResultSetCapture
(SqlString Command,
SqlString rsTable1, SqlString rsColumnList1,
SqlString rsTable2, SqlString rsColumnList2
)
{
// Storage for the parameters:
CommandCall cmd = new CommandCall();
// the command to execute
cmd.Command = Command.ToString();
// the tables to hold the result sets produced
cmd.rsTable = new List<ResultTables>();
// rsTableN where N = result set sequence number
// rsColumnList is the list of columns to capture * = All
if ( rsTable1.ToString() != "")
{
cmd.rsTable.Add(new ResultTables { resultSetSeq = 1, tableName = rsTable1.ToString(), columnList = rsColumnList1.ToString().ToLower() });
}
if ( rsTable2.ToString() != "" )
{
cmd.rsTable.Add(new ResultTables { resultSetSeq = 2, tableName = rsTable2.ToString(), columnList = rsColumnList2.ToString().ToLower() });
}
//*******************************************************************
// Execute & Save results to a dataSet
DataSet CommnandResults = CommandCallUtilities.ExecCommand(cmd);
DataSet dsTargetTablesOut = CommandCallUtilities.getTargetTableMetaData(cmd);
// Build a dataset with the output tables
DataSet dsResultSetSchema = CommandCallUtilities.getResultSetMetaData(cmd);
// Use the columnList choose the columns and meta data from the result set
// and then add any columns that are missing to the target table
// Map the columns of the result set to the columns of the output table;
bool result = CommandCallUtilities.mapResultsToOutputTables(CommnandResults, dsTargetTablesOut, dsResultSetSchema, cmd);
return;
}
public static void GetRanking(SqlString strQuery, SqlString strSelectExtremas, SqlInt32 numberOfRecords, SqlString strRankingWeights, SqlString strRankingExpressions, SqlBoolean showInternalAttr, SqlString strColumnNames)
{
SPRanking ranking = new SPRanking();
ranking.Provider = Helper.ProviderClr;
ranking.ConnectionString = Helper.CnnStringSqlclr;
ranking.RecordAmountLimit = numberOfRecords.Value;
ranking.ShowInternalAttributes = showInternalAttr.Value;
ranking.GetRankingTable(strQuery.ToString(), false, strSelectExtremas.ToString(), strRankingWeights.ToString(), strRankingExpressions.ToString(), strColumnNames.ToString());
}
public void Accumulate(SqlString value, SqlString separator)
{
if (value.IsNull || separator.IsNull) return;
if (intermediateResult.Length == 0)
intermediateResult.Append(value.Value);
else
intermediateResult.Append(value.ToString().Insert(0, separator.Value));
separatorResult = separator.Value;
}
public static SqlString strToBase64CLR(SqlString toEncode)
{
SqlString returnValue = null;
if (!toEncode.IsNull) {
byte[] toEncodeAsBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode.ToString());
returnValue = System.Convert.ToBase64String(toEncodeAsBytes);
}
return returnValue;
}
public static SqlString strFromBase64CLR(SqlString toDecode)
{
SqlString returnValue = null;
if (!toDecode.IsNull) {
byte[] DecodedBytes = System.Convert.FromBase64String(toDecode.ToString());
returnValue = System.Text.ASCIIEncoding.ASCII.GetString(DecodedBytes);
}
return returnValue;
}
public static SqlBinary binFromBase64CLR(SqlString toDecode)
{
SqlBinary result = null;
if (!toDecode.IsNull) {
byte[] DecodedBytes = System.Convert.FromBase64String(toDecode.ToString());
result = new SqlBinary(DecodedBytes);
}
return result;
}
public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords, SqlInt32 sortType)
{
SPSkylineHexagonLevel skyline = new SPSkylineHexagonLevel();
string[] additionalParameters = new string[1];
//additionalParameters[0] = strFirstSQLHexagon;
//additionalParameters[1] = strOperatorsHexagon;
//additionalParameters[2] = "";
//additionalParameters[3] = "0";
skyline.GetSkylineTable(strQuery.ToString(), strOperators.ToString(), numberOfRecords.Value, false, Helper.CnnStringSqlclr, Helper.ProviderClr, additionalParameters, sortType.Value);
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.result.Append(value.ToString());
this.result.Append(",");
}
private static DataTable attemptToGetSchemaTable(SqlString command, SqlDataReader reader)
{
try
{
return reader.GetSchemaTable();
}
catch (Exception e)
{
throw new InvalidResultSetException("The command [" + command.ToString() + "] did not return a valid result set", e);
}
}
public static SqlString DownloadURI(SqlString URI)
{
System.Net.WebRequest req = System.Net.HttpWebRequest.Create(URI.ToString());
req.Method = "GET";
System.Net.WebResponse resp = req.GetResponse();
using (System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream()))
{
return sr.ReadToEnd();
}
}
public static SqlInt32 IsTableExists(SqlString tableName, SqlString queryInfo)
{
SqlConnection cnn = new SqlConnection("context connection=true");
cnn.Open();
// 查询结果.
Object result = null;
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
// 拼SQL.
string sql = "SELECT Top 1 1 FROM " + tableName.ToString();
if( !String.IsNullOrEmpty(queryInfo.ToString())) {
sql = sql + " WHERE " + queryInfo.ToString();
}
// 准备用于检索数据的 动态SQL.
cmd.CommandText = sql;
// 执行查询.
result = cmd.ExecuteScalar();
} catch {
// 发生异常的情况下，认为不存在.
result = null;
}
finally {
// 必须关闭连接.
cnn.Close();
}
if (result == null)
{
return new SqlInt32(0);
}
// 在此处放置代码
return new SqlInt32(1);
}
public SqlDataReader executeCommand(SqlString Command)
{
infoMessage = SqlString.Null;
connection.InfoMessage += OnInfoMessage;
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = Command.ToString();
SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
return dataReader;
}
public static SqlString SF_RestPost(SqlString uri, SqlString postData)
{
// String returnVal=AuthenticateSfdcRestUser();
//string[] tokens = returnVal.Split(',');
// oauthToken = tokens[1];
// serviceUrl = tokens[0];
AuthenticateSfdcRestUser();
string result = null;
try
{
byte[] postBytes = Encoding.UTF8.GetBytes(Convert.ToString(postData));
System.Net.ServicePointManager.ServerCertificateValidationCallback += delegate(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate, System.Security.Cryptography.X509Certificates.X509Chain chain, System.Net.Security.SslPolicyErrors sslPolicyErrors)
{
return true; // **** Always accept
};
Debug.WriteLine(System.Net.ServicePointManager.CheckCertificateRevocationList);
HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(serviceUrl + uri.ToString());
webRequest.Method = "POST";
webRequest.ContentType = "application/xml";
webRequest.ContentLength = postBytes.Length;
String auth = "Authorization:" + "Bearer " + oauthToken;
webRequest.Headers.Add(auth);
Stream postStream = webRequest.GetRequestStream();
postStream.Write(postBytes, 0, postBytes.Length);
postStream.Close();
HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse();
Console.WriteLine(webResponse.StatusCode);
Console.WriteLine(webResponse.Server);
Stream responseStream = webResponse.GetResponseStream();
StreamReader responseStreamReader = new StreamReader(responseStream);
result = responseStreamReader.ReadToEnd();
return result;
}
catch (WebException e)
{
Console.WriteLine("Web exception");
throw e;
}
}
public static void GetSkyline(SqlString strQuery, SqlString strOperators, SqlInt32 numberOfRecords, SqlInt32 sortType, SqlString strSelectIncomparable, int weightHexagonIncomparable)
{
SPSkylineHexagon skyline = new SPSkylineHexagon();
string[] additionalParameters = new string[6];
additionalParameters[4] = strSelectIncomparable.ToString();
additionalParameters[5] = weightHexagonIncomparable.ToString();
string preferenceOperators = strOperators.Value;
string querySQL = strQuery.Value;
//Change operators array and SQL query (replace INCOMPARABLES values)
skyline.CalculateOperators(ref preferenceOperators, additionalParameters, Helper.CnnStringSqlclr, Helper.ProviderClr, ref querySQL);
skyline.GetSkylineTable(querySQL, preferenceOperators, numberOfRecords.Value, false, Helper.CnnStringSqlclr, Helper.ProviderClr, additionalParameters, sortType.Value);
}
public static void SOAPRequest(SqlString uri, ref SqlXml header, ref SqlXml body)
{
SqlString soapRequest = string.Empty;
SqlString soapResponse;
soapRequest += "<soap:Envelope ";
soapRequest += "xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' ";
soapRequest += "xmlns:xsd='http://www.w3.org/2001/XMLSchema' ";
soapRequest += "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' ";
soapRequest += "xmlns:encodingStyle='http://schemas.xmlsoap.org/soap/encoding' >";
// Добавляем залоловок, если он передан
if (!header.IsNull && header.Value != string.Empty)
soapRequest += "<soap:Header>" + header.Value.ToString() + "</soap:Header>";
soapRequest += "<soap:Body>";
soapRequest += body.Value.ToString();
soapRequest += "</soap:Body></soap:Envelope>";
var req = WebRequest.Create(uri.ToString());
req.Headers.Add("SOAPAction", "\"\"");
req.ContentType = "text/xml;charset=\"utf-8\"";
req.Method = "POST";
// Отправляем запрос
using (var stm = req.GetRequestStream())
using (var stmw = new StreamWriter(stm))
stmw.Write(soapRequest);
// Получаем ответ
using (var webResponse = req.GetResponse())
using (var responseStream = new StreamReader(webResponse.GetResponseStream(), Encoding.UTF8))
soapResponse = responseStream.ReadToEnd();
// Удаляем неописанные namespace из ответа (ответы WEB сервисов не всегда правильны :-( )
soapResponse = Regex.Replace(soapResponse.Value, "SOAP-ENV:", "", RegexOptions.IgnoreCase);
soapResponse = Regex.Replace(soapResponse.Value, "SOAP:", "", RegexOptions.IgnoreCase);
// Возвращаем данные из <Header>
using (var r = XmlReader.Create(new StringReader(soapResponse.Value)))
header = r.ReadToFollowing("Header") ? new SqlXml(XmlReader.Create(new StringReader(r.ReadInnerXml()))) : SqlXml.Null;
// Возвращаем данные из <Body>
using (var r = XmlReader.Create(new StringReader(soapResponse.Value)))
body = r.ReadToFollowing("Body") ? new SqlXml(XmlReader.Create(new StringReader(r.ReadInnerXml()))) : SqlXml.Null;
}
public static SqlString Slugify(SqlString name)
{
if (name.IsNull)
{
return SqlString.Null;
}
string str = RemoveAccent(name.ToString()).ToLower();
str = Regex.Replace(str, @"[^a-z0-9\s-]", string.Empty); // invalid chars
str = Regex.Replace(str, @"\s+", " ").Trim(); // convert multiple spaces into one space
str = Regex.Replace(str, @"-+", "-"); // convert multiple hyphens into one hyphen
str = str.Substring(0, str.Length <= MAX_SLUG_LENGTH ? str.Length : MAX_SLUG_LENGTH).Trim(); // cut and trim it
str = Regex.Replace(str, @"\s", "-"); // hyphens
str = str.TrimStart(new char[] { '-' }); // remove any leading hyphens
str = str.TrimEnd(new char[] { '-' }); // remove any trailing hyphens
return new SqlString(str);
}
public static IEnumerable SplitDelimitedString(SqlString value, SqlString delimiter)
{
if (value.IsNull)
{
// Return an empty collection
return new string[] { };
}
// Assume this is what the user wants
if (delimiter.IsNull)
{
delimiter = new SqlString(",");
}
string clrString = value.ToString();
if (String.IsNullOrEmpty(clrString))
{
// Return an empty collection
return new string[] { };
}
char[] clrDelimiter = delimiter.ToString().ToCharArray();
return clrString.Split(clrDelimiter);
}
public static string EncryptTZ(SqlString message)
{
byte[] Results;
System.Text.UTF8Encoding UTF8 = new System.Text.UTF8Encoding();
MD5CryptoServiceProvider HashProvider = new MD5CryptoServiceProvider();
byte[] TDESKey = HashProvider.ComputeHash(UTF8.GetBytes(passphrase));
TripleDESCryptoServiceProvider TDESAlgorithm = new TripleDESCryptoServiceProvider();
TDESAlgorithm.Key = TDESKey;
TDESAlgorithm.Mode = CipherMode.ECB;
TDESAlgorithm.Padding = PaddingMode.PKCS7;
byte[] DataToEncrypt = UTF8.GetBytes(message.ToString());
try
{
ICryptoTransform Encryptor = TDESAlgorithm.CreateEncryptor();
Results = Encryptor.TransformFinalBlock(DataToEncrypt, 0, DataToEncrypt.Length);
}
finally
{
TDESAlgorithm.Clear();
HashProvider.Clear();
}
return Convert.ToBase64String(Results);
}
