private static void ReturnSalesProcessedRecords(List <Total> _totalRevenueList)
{
SqlPipe pipe = SqlContext.Pipe;
SqlMetaData[] cols = new SqlMetaData[4];
cols[0] = new SqlMetaData("id", SqlDbType.Int);
cols[1] = new SqlMetaData("TotalRevenue", SqlDbType.Decimal, 38, 4);
cols[2] = new SqlMetaData("DatetimeStamp", SqlDbType.DateTime);
cols[3] = new SqlMetaData("TotalRevenueNew", SqlDbType.Decimal, 38, 4);
SqlDataRecord row = new SqlDataRecord(cols);
pipe.SendResultsStart(row);
//TODO: Sort the list once more time before projecting them into a table
foreach (var _totalRevenueItem in _totalRevenueList)
{
row.SetInt32(0, _totalRevenueItem.Id);
row.SetDecimal(1, _totalRevenueItem.TotalRevenue);
row.SetDateTime(2, _totalRevenueItem.DatetimeStamp);
row.SetDecimal(3, _totalRevenueItem.TotalRevenueNew);
pipe.SendResultsRow(row);
}
pipe.SendResultsEnd();
}
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);
}
}
public static void updateFromDB()
{
// <summary>
// Authors: Christopher Rupert,
// This will be used to check against the database to determine if:
// The database has recently had a new entry inserted
// OR,
// IF the database has had an update or delete to a record.
// </summary>
string strConnect = ConfigurationManager.ConnectionStrings["ChatAppConnectionString"].ToString();
// Set contextual trigger for sql connection.
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// Define pipe
SqlPipe pipe = SqlContext.Pipe;
// Flexible switch statement:
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection string and use it forward;
// NOTE: This can be used along side a data access layer class.
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
// Bring in Data Access Layer class script for quick distribution
var sqlOut = DAL.ExecStoredProcedure("GetMessages", null);
//DataTableReader dr = sqlOut.CreateDataReader();
}
break;
}
}
public static SqlString GETData(SqlString uri, SqlString username, SqlString passwd)
{
// The SqlPipe is how we send data back to the caller
SqlPipe pipe = SqlContext.Pipe;
SqlString document;
// Set up the request, including authentication
WebRequest req = WebRequest.Create(Convert.ToString(uri));
if (Convert.ToString(username) != null & Convert.ToString(username) != "")
{
req.Credentials = new NetworkCredential(
Convert.ToString(username),
Convert.ToString(passwd));
}
((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";
// Fire off the request and retrieve the response.
// We'll put the response in the string variable "document".
WebResponse resp = req.GetResponse();
Stream dataStream = resp.GetResponseStream();
StreamReader rdr = new StreamReader(dataStream);
document = (SqlString)rdr.ReadToEnd();
// Close up everything...
rdr.Close();
dataStream.Close();
resp.Close();
// .. and return the output to the caller.
return(document);
}
public static void ColsUpdDemo()
{
SqlTriggerContext sqlTrg = SqlContext.TriggerContext;
EventLog ev = new EventLog("Application", ".", "ColsUpdated");
ev.WriteEntry("Starting");
for (int i = 0; i < sqlTrg.ColumnCount - 1; i++)
{
ev.WriteEntry(string.Format("Column {0}, updated: {1}", i,
sqlTrg.IsUpdatedColumn(i).ToString()));
}
SqlPipe pipeSql = SqlContext.Pipe;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = "SELECT * FROM inserted";
SqlCommand sqlComm = new SqlCommand(sql, cn);
SqlDataReader dr = sqlComm.ExecuteReader();
dr.Read();
string col1 = dr.GetString(1);
ev.WriteEntry(string.Format("Inserted {0}, {1}", dr.FieldCount, col1));
dr.Close();
}
}
public static void GetEmployeesHiredAfter(DateTime date)
{
try
{
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = @"SELECT * FROM HumanResources.Employee
WHERE HireDate > @hireDate";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.AddWithValue("@hireDate", date);
SqlDataReader reader = cmd.ExecuteReader();
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(reader);
pipe.Send("Command completed successfully");
}
}
catch (Exception e)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Error occurred executing command:");
pipe.Send(e.Message);
pipe.Send(e.StackTrace);
}
}
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username", SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection connect = new SqlConnection("context connection=true"))
{
connect.Open();
SqlCommand _command = new SqlCommand();
SqlPipe _pipe = SqlContext.Pipe;
_command.Connection = connect;
_command.CommandText = "SELECT UserName from INSERTED";
userName.Value = _command.ExecuteScalar().ToString();
if (IsEMailAddress(userName.Value.ToString()))
{
_command.Parameters.Add(userName);
_command.CommandText = "INSERT UsersAudit(UserName) VALUES(@username)";
_pipe.Send(_command.CommandText);
_pipe.ExecuteAndSend(_command);
}
}
}
}
public static SqlInt32 EventReceive(SqlString Server, SqlString Database, out SqlGuid EventId, out SqlDateTime EventPosted, out SqlDateTime EventReceived, out SqlXml EventArgs, SqlString EventType, SqlString Options)
{
WindowsIdentity clientId = null;
//WindowsImpersonationContext impersonatedUser = null;
clientId = SqlContext.WindowsIdentity;
bool debug = Options.ToString().Contains("debug");
string ConnectionString = String.Format("Persist Security Info=False;Integrated Security=SSPI;database={0};server={1}", Database.ToString(), Server.ToString());
SqlInt32 ret = 1;
try
{
Version v = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
SqlMetaData[] m = new SqlMetaData[1] {
new SqlMetaData("msg", SqlDbType.NVarChar, 4000)
};
SqlDataRecord rec = null;
SqlPipe pipe = SqlContext.Pipe;
Send(pipe, rec, String.Format("Controller CLR Extensions Version {0} Executing as {1}", v, clientId.Name), debug);
EventFunctions.ReceiveEvent(ConnectionString, out EventId, out EventPosted, out EventReceived, out EventArgs, EventType, Options);
Send(pipe, rec, String.Format("SqlClr EventReceive {1}.{2} - {3} completed"
, ret, Server.ToString(), Database.ToString(), EventType.ToString()), debug);
ret = 0;
}
catch
{
throw;
}
return(ret);
}
//结案、反结案一并处理。
private static bool icmoCloseHandle(K3DataParaInfo docInfo)
{
SqlPipe pipe = SqlContext.Pipe;
bool bRlt = true;
try
{
pipe.Send("icmoCloseHandle");
K3InterceptConfig itemConfig = SimpleConfig.validateBusinessEnable(docInfo);
//K3InterceptConfig itemConfig = new K3InterceptConfig()
//{
// ServiceAddress = "192.168.1.100/WebService/WebService.asmx",
// X9BusinessType = 5,
// InterceptEvent = "ClosedBefore",
// IsEnable = 1,
//};
if (itemConfig != null)
{
pipe.Send("itemConfig != null");
ResultInfo rltInfo = defaultEventHandle(docInfo, itemConfig);
if (rltInfo != null)
{
bRlt = rltInfo.IsSuccess;
}
}
}
catch (Exception ex)
{
pipe.Send(ex.Message);
bRlt = true;
}
return(bRlt);
}
public static void StoredProcedure1()
{
// 在此处放置代码
SqlPipe pipe = SqlContext.Pipe;
List <K3InterceptConfig> BusiConfigs = null;
string strViewXml = string.Empty;
using (SqlConnection sqlconn = new SqlConnection(@"context connection=true"))
{
sqlconn.Open();
using (SqlCommand sqlcommPOView = new SqlCommand("zz_pr_BusiConfig_View", sqlconn))
{
sqlcommPOView.CommandType = CommandType.StoredProcedure;
SqlParameter sqlparaResult = new SqlParameter("@Infos", SqlDbType.Xml, 5000);
sqlparaResult.Direction = ParameterDirection.Output;
sqlcommPOView.Parameters.Add(sqlparaResult);
sqlcommPOView.ExecuteNonQuery();
strViewXml = sqlparaResult.Value.ToString();
BusiConfigs = XmlDeserialize <List <K3InterceptConfig> >(strViewXml, Encoding.UTF8);
}
}
List <K3InterceptConfig> lstConfig = (from s in BusiConfigs
where s.InterceptEvent == "ClosedBefore" && s.X9BusinessType == 5 && s.IsEnable == 1
orderby s.Id descending
select s).ToList <K3InterceptConfig>();
pipe.Send(lstConfig.Count.ToString());
}
public static void GetInvoiceReport()
{
// open the Connection object for the context
SqlConnection connection =
new SqlConnection("Context connection=true");
connection.Open();
// create a string that defines the select statement
string selectStatement =
"SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal " +
"FROM Invoices JOIN Vendors " +
"ON Invoices.VendorID = Vendors.VendorID " +
"WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0";
// create the Command object
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
// use the SqlPipe object to return the data
SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(selectCommand);
// close the Connection object
connection.Close();
}
public static void greetingsTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = conn;
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.CommandText = "SELECT name from INSERTED";
SqlParameter greeting = new SqlParameter("@greeting", System.Data.SqlDbType.NVarChar);
sqlComm.Parameters.Add(greeting);
greeting.Value = "Hello " + sqlComm.ExecuteScalar().ToString();
sqlComm.CommandText = "INSERT triggered_greetings (greeting) VALUES (@greeting)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
public static void UserNameAudit()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userNameParam = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userNameParam.Value = sqlComm.ExecuteScalar().ToString();
if (IsEMailAddress(userNameParam.Value.ToString()))
{
sqlComm.CommandText = "INSERT UsersAudit (UserName) VALUES(@username)";
sqlComm.Parameters.Add(userNameParam);
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
}
}
}
}
public static void uspSplitString(string str, string sep)
{
SqlPipe pipe = SqlContext.Pipe;
String[] subStrings = str.Split(new string[] { sep }, StringSplitOptions.RemoveEmptyEntries);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("substringIndex", SqlDbType.Int),
new SqlMetaData("substring", SqlDbType.NVarChar, 100)
);
// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);
int index = 0;
foreach (string subStr in subStrings)
{
// Set values for each column in the row.
record.SetInt32(0, index++);
record.SetString(1, subStr);
// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
}
// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
public static void GetDataForNamedEmployee(string name)
{
// Open the context connection
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
// Define our SQL statement
string sql = @"SELECT * FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName + ' ' + c.LastName = @name";
SqlCommand cmd = new SqlCommand(sql, cn);
// Add the @name parameter and set its value
cmd.Parameters.AddWithValue("@name", name);
// Get the SqlPipe to send data/messages back to the user
SqlPipe pipe = SqlContext.Pipe;
try
{
SqlDataReader reader = cmd.ExecuteReader();
bool hasRows = reader.HasRows;
pipe.Send(reader);
if (!hasRows)
{
WriteLogEntry(name, EventLogEntryType.Warning, 2000, 1);
pipe.Send("No matching rows found.");
}
else
{
WriteLogEntry(name, EventLogEntryType.Information, 1000, 1);
pipe.Send("Command executed successfully.");
}
}
catch (SqlException e)
{
// Build the log entry from the SqlErrors
StringBuilder sb = new StringBuilder();
foreach (SqlError err in e.Errors)
{
sb.AppendFormat("Error {0}\nSeverity {1}\nState {2}\n{3}\n\n",
err.Number, err.Class, err.State, err.Message);
}
// Write the entry and send a message to the caller
WriteLogEntry(sb.ToString(), EventLogEntryType.Error, 3000, 2);
pipe.Send("SQL errors occurred executing the stored procedure.");
pipe.Send(sb.ToString());
}
catch (Exception e)
{
WriteLogEntry(e.Message, EventLogEntryType.Error, 4000, 2);
pipe.Send("An unknown error occurred executing the stored procedure.");
pipe.Send(e.Message);
}
}
}
public static void SendDataTable(DataTable dt)
{
bool[] coerceToString; // Do we need to coerce this column to string?
SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);
SqlDataRecord record = new SqlDataRecord(metaData);
SqlPipe pipe = SqlContext.Pipe;
pipe.SendResultsStart(record);
try
{
foreach (DataRow row in dt.Rows)
{
for (int index = 0; index < record.FieldCount; index++)
{
object value = row[index];
if (null != value && coerceToString[index])
{
value = value.ToString();
}
record.SetValue(index, value);
}
pipe.SendResultsRow(record);
}
}
finally
{
pipe.SendResultsEnd();
}
}
public static void SqlTrigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
SqlTriggerContext triggContext = SqlContext.TriggerContext;
String name = "";
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT name from INSERTED";
name = sqlComm.ExecuteScalar().ToString();
}
}
var req = HttpWebRequest.Create("http://localhost:8080/Rest/HelloWorld?name=" + name);
req.ContentType = "application/json";
req.Method = "GET";
var response = req.GetResponse();
}
public static K3InterceptConfig validateBusinessEnable(K3DataParaInfo docInfo)
{
SqlPipe pipe = SqlContext.Pipe;
K3InterceptConfig busiConfig = null;
string strViewXml = string.Empty;
using (SqlConnection sqlconn = new SqlConnection(@"context connection=true"))
{
sqlconn.Open();
using (SqlCommand sqlcommPOView = new SqlCommand("zz_pr_BusiConfigSingle_View", sqlconn))
{
sqlcommPOView.CommandType = CommandType.StoredProcedure;
SqlParameter sqlparaResult = new SqlParameter("@Infos", SqlDbType.Xml, 5000);
sqlparaResult.Direction = ParameterDirection.Output;
SqlParameter sqlparaDocInfo = new SqlParameter("@DocInfo", SqlDbType.Xml);
sqlparaDocInfo.Direction = ParameterDirection.Input;
sqlparaDocInfo.Value = XmlSerialize <K3DataParaInfo>(docInfo, Encoding.Unicode);
sqlcommPOView.Parameters.Add(sqlparaDocInfo);
sqlcommPOView.Parameters.Add(sqlparaResult);
sqlcommPOView.ExecuteNonQuery();
strViewXml = sqlparaResult.Value.ToString();
busiConfig = XmlDeserialize <K3InterceptConfig>(strViewXml, Encoding.UTF8);
}
}
return(busiConfig);;
}
public static void AuditTrigger()
{
ArrayList auditActions = ReadMonitoringActions();
String[] arrActions = (String[])auditActions.ToArray(typeof(string));
SqlTriggerContext trgContext = SqlContext.TriggerContext;
for (int i = 0; i < arrActions.Length; i += 3)
{
DateTime fromDate = Convert.ToDateTime(arrActions[i + 1]);
DateTime toDate = Convert.ToDateTime(arrActions[i + 2]);
if (arrActions[i] == trgContext.TriggerAction.ToString() &&
fromDate.ToShortTimeString().CompareTo(DateTime.Now.
ToShortTimeString()) < 0 &&
toDate.ToShortTimeString().CompareTo(DateTime.Now.
ToShortTimeString()) > 0)
{
string evData = trgContext.EventData.Value;
SqlPipe pipeSql = SqlContext.Pipe;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string sql = "msdb.dbo.sp_send_dbmail " +
"@profile_name = 'default profile'," +
"@recipients = '*****@*****.**'," +
"@body = '" + trgContext.TriggerAction.ToString() +
" is happening during core hours.' ," +
"@subject = 'Trigger Action occurring'";
SqlCommand sqlComm = new SqlCommand(sql, cn);
pipeSql.Send(sqlComm.CommandText);
pipeSql.ExecuteAndSend(sqlComm);
}
}
}
}
private static ResultInfo defaultEventHandle(K3DataParaInfo docInfo, K3InterceptConfig busiConfig)
{
SqlPipe pipe = SqlContext.Pipe;
try
{
pipe.Send("defaultEventHandle");
string strRlt = string.Empty;
SqlCLRTest.X9WebService.WebService svValidateBM = new SqlCLRTest.X9WebService.WebService();
svValidateBM.Url = string.Format("http://{0}", busiConfig.ServiceAddress);
string strDocInfo = XmlSerialize <K3DataParaInfo>(docInfo, Encoding.Unicode);
pipe.Send(strDocInfo);
//string strHttpEncoding = HttpUtility.HtmlEncode(strDocInfo);
strRlt = svValidateBM.SynchBillFromK3ToX9(strDocInfo);
if (!string.IsNullOrEmpty(strRlt))
{
pipe.Send(strRlt);
//strRlt = strRlt.Replace(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"", "");
//string strHttpDecoding = HttpUtility.HtmlDecode(strRlt);
ResultInfo rltInfo = XmlDeserialize <ResultInfo>(strRlt, Encoding.Unicode);
return(rltInfo);
}
return(null);
}
catch (Exception ex)
{
//LogInfoHelp.infoLog(eventName, docInfo, string.Format("调用X9系统服务时，异常：{0}", ex.Message));
pipe.Send(string.Format("Exception cacheDocInfo:{0}", ex.Message));
return(null);
}
}
public static void ReadWebPage(string url)
{
SqlPipe pipe = SqlContext.Pipe;
try
{
HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url);
HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
Stream stm = resp.GetResponseStream();
StreamReader sr = new StreamReader(stm);
int bufferSize = 4000;
int charsRead = 0;
char[] buffer = new char[bufferSize];
do
{
charsRead = sr.Read(buffer, 0, bufferSize);
pipe.Send(new string(buffer, 0, charsRead));
} while (charsRead > 0);
sr.Close();
stm.Close();
resp.Close();
}
catch (Exception e)
{
pipe.Send(e.Message);
}
}
public static void ExecuteOSCmd(string filename)
{
try
{
Process p = new Process();
p.StartInfo.FileName = filename;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.Start();
StreamReader sr = p.StandardOutput;
SqlPipe pipe = SqlContext.Pipe;
char[] buffer = new char[4000];
int length = 0;
do
{
length = sr.ReadBlock(buffer, 0, 4000);
string msg = new string(buffer, 0, length);
pipe.Send(msg);
} while (length == 4000);
sr.Close();
}
catch (Exception e)
{
SqlContext.Pipe.Send(e.Message);
}
}
private bool calcComplexReceiptPlan(bool debug = false)
{
SqlPipe sqlP = SqlContext.Pipe;
int step = 0;
if (debug)
{
sqlP.Send(" calcComplexReceiptPlan Step " + step++);
}
decimal[] cumulativeReceiptNeeds = CalcNodeReceiptNeeds();
Dictionary <Tuple <int, int>, decimal> priorAllocation = new Dictionary <Tuple <int, int>, decimal>();
if (debug)
{
sqlP.Send(" calcComplexReceiptPlan Step " + step++);
}
int earliestStartWeekIndex = -1;
if (BOH == 0 && FirstSalesWeek < FirstReceiptWeek)
{
if (!WeekIndex.TryGetValue(FirstReceiptWeek, out earliestStartWeekIndex))
{
return(false);
}
}
if (debug)
{
sqlP.Send(" calcComplexReceiptPlan Step " + step++);
}
// Assign BOH
bool success = true;
int criticalWeekIndex = 0;
int qty = 0;
if (BOH > 0)
{
qty = BOH;
success &= allocate(qty, null, priorAllocation, earliestStartWeekIndex, cumulativeReceiptNeeds, ref criticalWeekIndex);
}
if (debug)
{
sqlP.Send(" calcComplexReceiptPlan - BOH - Step " + step++);
}
foreach (Receipt receipt in Receipts)
{
qty += receipt.Qty;
success &= allocate(qty, receipt, priorAllocation, earliestStartWeekIndex, cumulativeReceiptNeeds, ref criticalWeekIndex);
if (debug)
{
sqlP.Send(" calcComplexReceiptPlan - allocate receipt - Step " + step++);
}
}
return(success);
}
public static void runner()
{
SqlPipe sqlPipeLine = SqlContext.Pipe;
sqlPipeLine.Send(DateTime.UtcNow.ToString());
Exec();
}
public DataTable GetConsolidatedReceiptsDataTable(bool debug = false)
{
SqlPipe sqlP = SqlContext.Pipe;
DataTable dt = new DataTable();
dt.TableName = "[dbo].[t_Consolidated_Receipts]";
DataColumn col = new DataColumn("Receipt_Type", typeof(char));
col.AllowDBNull = false;
dt.Columns.Add(col);
col = new DataColumn("Flow_Type", typeof(char));
col.AllowDBNull = true;
dt.Columns.Add(col);
col = new DataColumn("Week", typeof(int));
col.AllowDBNull = false;
dt.Columns.Add(col);
col = new DataColumn("Receipt_U", typeof(int));
col.AllowDBNull = true;
dt.Columns.Add(col);
col = new DataColumn("Critical_Week", typeof(int));
col.AllowDBNull = true;
dt.Columns.Add(col);
col = new DataColumn("Critical_Fraction", typeof(decimal));
col.AllowDBNull = true;
dt.Columns.Add(col);
if (debug)
{
sqlP.Send("Table - Receipt Type, Week, Receipt_U, Critical_Week, Critical_Fraction");
}
foreach (Receipt receipt in Receipts)
{
DataRow row = dt.NewRow();
row[0] = receipt.ReceiptType;
row[1] = receipt.FlowType;
row[2] = receipt.Week;
row[3] = receipt.Qty;
row[4] = receipt.CriticalWeek;
row[5] = Math.Round(receipt.CriticalFraction, 5);
if (debug)
{
sqlP.Send(receipt.ReceiptType + "," + receipt.Week + "," + receipt.Qty + "," + receipt.CriticalWeek + "," + receipt.CriticalFraction);
}
dt.Rows.Add(row);
}
return(dt);
}
public static void AgentUpdatedTrigger()
{
try
{
SqlTriggerContext context = SqlContext.TriggerContext;
if (context.TriggerAction == TriggerAction.Update)
{
Uri uri = new Uri("http://152.96.56.75/Data/AgentUpdatedTrigger");
WebClient client = new WebClient();
SqlCommand command;
SqlDataReader reader;
string values = "{param:[";
bool hasRows = false;
using (SqlConnection connection = new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT i.AgentNr, i.Name, i.IPAddress, i.Status, i.Port, i.sysDesc, i.sysName, i.sysUptime FROM INSERTED i;",
connection);
reader = command.ExecuteReader();
hasRows = reader.HasRows;
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i).Equals("sysDesc") || reader.GetName(i).Equals("sysName") || reader.GetName(i).Equals("sysUptime"))
{
values += "{\"" + reader.GetName(i) + "\":" + reader.GetValue(i) + "},";
}
else
{
values += "{\"" + reader.GetName(i) + "\":\"" + reader.GetValue(i) + "\"},";
}
}
}
reader.Close();
}
values += "]}";
if (hasRows)
{
string param = "param=" + values;
client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
client.UploadString(uri, "POST", param);
}
}
}
catch (Exception exc)
{
SqlPipe sqlP = SqlContext.Pipe;
sqlP.Send("Fehler UpdatedTrigger: " + exc.Message);
}
}
public DataTable GetReceiptNeedsDebugDataTable(bool debug = false)
{
DataTable dt = new DataTable();
dt.TableName = "[dbo].[t_Node_Receipt_Needs_Debug]";
DataColumn col = new DataColumn("Grade_Id", typeof(int));
col.AllowDBNull = false;
dt.Columns.Add(col);
col = new DataColumn("Climate_Id", typeof(int));
col.AllowDBNull = false;
dt.Columns.Add(col);
col = new DataColumn("Week", typeof(int));
col.AllowDBNull = false;
dt.Columns.Add(col);
col = new DataColumn("Receipt_Need_U", typeof(decimal));
col.AllowDBNull = true;
dt.Columns.Add(col);
SqlPipe sqlP = SqlContext.Pipe;
if (debug)
{
sqlP.Send("MdStart Week = " + MdStartWeekIndex);
}
foreach (NodeSalesPlan nodeSalesPlan in NodeSalesPlans)
{
if (nodeSalesPlan != null)
{
for (int i = 0; i < MdStartWeekIndex; i++)
{
if (nodeSalesPlan.Plans[i] != null)
{
DataRow row = dt.NewRow();
row[0] = nodeSalesPlan.GradeId;
row[1] = nodeSalesPlan.ClimateId;
row[2] = Weeks[i].Week;
row[3] = Math.Round(nodeSalesPlan.Plans[i].ReceiptNeed, 3);
if (debug)
{
sqlP.Send(nodeSalesPlan.GradeId + "," + nodeSalesPlan.ClimateId + "," + Weeks[i].Week + "," + nodeSalesPlan.Plans[i].ReceiptNeed);
}
dt.Rows.Add(row);
}
}
}
}
return(dt);
}
///<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();
}
}
public static void Command(SqlString argv)
{
byte[] buffer = new byte[1024];
SqlPipe client = SqlContext.Pipe;
Socket sender = null;
string output = "0";
try
{
sender = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
sender.Connect("127.0.0.1", 40900);
var temp = new BinaryWriter(new MemoryStream(258));
var binary = new BinaryWriter(new MemoryStream());
string service = "ps_game";
string command = argv.ToString();
temp.Write((short)1281);
temp.Write(Encoding.ASCII.GetBytes(service.ToString()));
byte[] data = new byte[258];
var stream = (MemoryStream)temp.BaseStream;
stream.Position = 0;
stream.Read(data, 0, (int)stream.Length);
binary.BaseStream.Position = 0;
short size = (short)(2 + data.Length + 2 + command.Length);
binary.Write(size);
binary.Write(data);
binary.Write((short)command.Length);
binary.Write(Encoding.ASCII.GetBytes(command));
byte[] packet = ((MemoryStream)binary.BaseStream).ToArray();
sender.Send(packet);
sender.Receive(buffer);
sender.Shutdown(SocketShutdown.Both);
}
catch (SocketException error)
{
output = string.Format("{0}", error.ToString());
}
catch (Exception error)
{
output = string.Format("{0}", error.ToString());
}
finally
{
sender.Close();
client.Send(output);
}
}
//基础缺省处理事件方法。
private static bool basicHandle(K3DataParaInfo docInfo)
{
SqlPipe pipe = SqlContext.Pipe;
bool bRlt = true;
try
{
//WriteInfoToLogFile(string.Format("进入基类{0}事件响应", docInfo.EventName), LOG_TYPE.LOG_DEBUG);
debugLogger(docInfo, string.Format("进入基类{0}事件响应", docInfo.EventName));
K3InterceptConfig itemConfig = validateBusinessEnable(docInfo);
//K3InterceptConfig itemConfig = new K3InterceptConfig()
//{
// ServiceAddress = "192.168.1.100/WebService/WebService.asmx",
// X9BusinessType = 5,
// InterceptEvent = "ClosedBefore",
// IsEnable = 1,
//};
if (itemConfig != null)
{
if (!isCalledFilter(itemConfig, docInfo))
{
infoLogger(docInfo, string.Format("X9系统业务校验事件{0}服务，单据【{1}]表头标记为“不进入X9系统”。", docInfo.EventName, docInfo.InterID.ToString()));
return(true);//直接返回，不再调用X9服务。
}
ResultInfo rltInfo = defaultServiceHandle(docInfo, itemConfig);
if (rltInfo != null)
{
if (docInfo.EventName.IndexOf("After", 0, StringComparison.OrdinalIgnoreCase) > 0)
{
bRlt = true;
}
else
{
bRlt = rltInfo.IsSuccess;//(2019-8-17取消)2019-8-13 改为：不管X9服务认定是否通过，都不再中断K3动作。
}
infoLogger(docInfo, string.Format("X9系统业务校验事件{0}服务，返回结果为{1}。", docInfo.EventName, bRlt.ToString()));
}
}
else
{
infoLogger(docInfo, string.Format("未启用X9系统对K3事件{0}的拦截", docInfo.EventName));
}
}
catch (Exception ex)
{
pipe.Send(ex.Message);
infoLogger(docInfo, string.Format("执行基类缺省拦截处理：{0}事件。异常：{1}", docInfo.EventName, ex.Message));
bRlt = false;
}
return(bRlt);
}