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); }