2015年10月 的存档
2015十月31

SQLSERVER2014内存数据表

sql server 评论关闭

内存数据表有两大索引,一是hash,一是非聚集,对于hash索引对=起作用,若是范围查询,则会执行表扫描。可以建立hash索引后再建立个非聚集索引解决单个与范围查询的问题,经测试,内存数据表平均比SSD还要快上10倍以上。故可将热表放入内存中,如业绩统计表,利用它插入更新迅速的特点,写触发器更新,利用其查询速度快的优点,提升查询速度。

USE [master]
--创建数据库
CREATE DATABASE [TestDB]
ON  PRIMARY
( NAME = N'TestDB', FILENAME = N'D:SQL2104SQLDataTestDB.mdf' ,
SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB )
LOG ON
( NAME = N'TestDB_log', FILENAME = N'D:SQL2104SQLDataTestDB_log.ldf' ,
SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )
GO
--创建内存表使用的文件组
ALTER DATABASE [TestDB] ADD FILEGROUP [TestDB_MFG1] CONTAINS MEMORY_OPTIMIZED_DATA
GO
--创建内存表使用的文件夹
ALTER DATABASE [TestDB]
ADD FILE ( NAME = N'TestDB_MDir1', FILENAME = N'D:SQL2104SQLDataTestDB_MDir1')
TO FILEGROUP [TestDB_MFG1]
GO

CREATE TABLE [dbo].[TB1_IM]
(
    [c1] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY
    NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c2] [nchar](200)  COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL
    INDEX ix_c2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c3] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    [c4] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    INDEX ix_c3_c4 NONCLUSTERED HASH(c3,c4) WITH(BUCKET_COUNT=1000000),
    INDEX ix_c2_c3  NONCLUSTERED (c2,c3)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

说明:
对于原有表,可以右键内存优化表来进行建立

1,内存索引表不支持ALTER TABLE 和 ALTER INDEX操作,如果需要修改表或者修改索引
2,对于内存优化表,有两种持续性可以选择:SCHEMA_ONLY(非持久表)和SCHEMA_AND_DATA(持久表),SCHEMA_ONLY 选项会导致数据在实例重启后丢失;而对于SCHEMA_AND_DATA(持久表),又可以设置完全持久行还是延迟持续性,延迟持续性选项允许在事务提交时可以不立即将日志写入磁盘,从而提升性能,当然代价就是发生故障时可能丢失数据。
2015十月29

mysql时间戳timestamp在sqlserver中的实现

SQL,sql server 评论关闭

CREATE FUNCTION [dbo].[UNIX_TIMESTAMP]
(
    @DATE DATETIME=''
)

RETURNS DECIMAL
AS
BEGIN
  --注意时区,北京时间统一减8变成格林尼治时间
   IF @DATE='' SET @DATE=GETDATE()
  SET @DATE=DATEADD(HOUR,-8,@DATE)
   RETURN DATEDIFF(SECOND,'1970-01-01',@DATE)

END

CREATE FUNCTION [dbo].[FROM_UNIXTIME]
(
    @i DECIMAL
)

RETURNS VARCHAR(20)
AS
BEGIN
 RETURN CONVERT(VARCHAR(20),DATEADD(SECOND,@i+28800,'1970-01-01'),120) --北京时间要加回8*3600=28800秒
END

引自:MSSQL(SQL SERVER)中获取UNIX时间戳

2015十月21

读取xml报16进制字符错误

未分类 评论关闭

读取xml报16进制字符错误,因为xml文件中包括特殊字符,如u0018,造成解析错误
解析方法:剔除xml特殊字符再读取

  private string RemoveInvalidXmlChars(string text)
        {
            var validXmlChars = text.Where(ch => XmlConvert.IsXmlChar(ch)).ToArray();
            return new string(validXmlChars);
        }

private void OnRecvMessage(string Message) //接收到插件发来的消息触发该事件
        {
            try
            {
                RtxMessage rm = new RtxMessage();

                StringReader sr1 = new StringReader(RemoveInvalidXmlChars(Message.Content));//避免0/这种异常字符
                XmlReader xr1 = XmlTextReader.Create(sr1); // xr1's type is XmlTextReaderImpl

                while (xr1.Read())
                {
                    if (xr1.Name == "Item")
                    {

                        switch (xr1["Key"])
                        {
                            case "Mode": { if (xr1.Read()) { rm.Mode = xr1.Value; }; break; }
                            case "Content": { if (xr1.Read()) { rm.Content = GetContent(xr1.Value); }; break; }
                            case "Initiator": { if (xr1.Read()) { rm.Initiator = xr1.Value; }; break; }
                            case "Key": { if (xr1.Read()) { rm.Key = xr1.Value; }; break; }
                            case "Title": { if (xr1.Read()) { rm.Title = xr1.Value; }; break; }
                            case "im_message_id": { if (xr1.Read()) { rm.im_message_id = xr1.Value; }; break; }
                            default: { break; }
                        }
                    }
                }

            }
            catch (COMException xe)
            {

            }

        }
2015十月17

quarz+topself实现调度

未分类 评论关闭

quarz:用来灵活的调度程序的执行
topself:用来将应用提升为服务

一,建立控制台应用程序,引用右键添加:quarz.net,topself,topself.log4Net
二,main中添加代码,用topself承接程序的管理

using System
using Quartz;
using Topshelf;
using System.IO;

namespace Quarz
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                log4net.Config.XmlConfigurator.ConfigureAndWatch(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "log4net.config"));
                HostFactory.Run(x =>
                {
                    x.UseLog4Net();//记录topself自己产生的日志

                    x.Service<ServiceRunner>(); //ServiceRunner是quarz的调度程序,以此来调用

                    x.SetDescription("自己的WindowsService调度中心");
                    x.SetDisplayName("CedarQuarz");
                    x.SetServiceName("CedarQuarz");

                    x.EnablePauseAndContinue();
                });

            }
            catch (SchedulerException se)
            {
                Console.WriteLine(se);
            }
        }
    }
}

//ServiceRunner.cs如下
using Quartz;
using Quartz.Impl;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Topshelf;

namespace Quarz
{
    public sealed class ServiceRunner : ServiceControl, ServiceSuspend
    {
        private readonly IScheduler scheduler;

        public ServiceRunner()
        {
            scheduler = StdSchedulerFactory.GetDefaultScheduler();//调用调度计划,在quarz_job.xml配置要执行的job
        }

        public bool Start(HostControl hostControl)
        {
            scheduler.Start();
            return true;
        }

        public bool Stop(HostControl hostControl)
        {
            scheduler.Shutdown(false);
            return true;
        }

        public bool Continue(HostControl hostControl)
        {
            scheduler.ResumeAll();
            return true;
        }

        public bool Pause(HostControl hostControl)
        {
            scheduler.PauseAll();
            return true;
        }

    }
}
//自己写的job实现ijob即可
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Quartz;
using System.Threading.Tasks;
using RTXSAPILib;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Quarz
{
    class RtxSendMsg : IJob
    {
        private static RTXSAPILib.IRTXSAPIRootObj _RootObj;
        public static RTXSAPILib.IRTXSAPIRootObj RootObj {
            get {
                if (_RootObj == null) {
                    _RootObj = new RTXSAPIRootObj();
                }
                return _RootObj;
            }
        }

        public static string ServerIP= ConfigurationSettings.AppSettings["RtxServerIP"];
        public static short ServerPort = short.Parse(ConfigurationSettings.AppSettings["RtxServerPort"]);
        public static string ConnString = ConfigurationSettings.AppSettings["constring"];
        public static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public void Execute(IJobExecutionContext context)
        {
            log.Info("开始");
            //初始化服务器属性
            try
            {
                RootObj.ServerIP =   ServerIP;
                RootObj.ServerPort = ServerPort; //设置服务器端口
                DataTable dt = GetWaitNotifyInfo();
                if (dt == null || dt.Rows.Count == 0) return;
                foreach (DataRow dr in dt.Rows)
                {
                    SendNotify(StringHelper.FormatObjToString(dr["receiverAccount"]),
                        StringHelper.FormatObjToString(dr["CustomerID"]),
                        StringHelper.FormatObjToString(dr["CustomerName"]),
                        StringHelper.FormatObjToString(dr["D_StartTime"]),
                        StringHelper.FormatObjToString(dr["title"]), StringHelper.FormatObjToInt(dr["holdtime"]), StringHelper.FormatObjToString(dr["content"]));
                }
            }
            catch (Exception e) {
                log.Error(e.StackTrace.ToString());
            }
            log.Info("结束");
            //SendNotify("zhengxuesong", "hello", "", "【未接来电】客户65871于2015-10-16 14:10打来电话,请及时跟进");
        }

        public DataTable GetWaitNotifyInfo() {
            SqlParameter[] storedParams =
             {
                new SqlParameter("@Code",""),
                new SqlParameter("@UserID","")
            };
            IDataReader idr = Share.Data.SqlHelper.ExecuteReader(ConnString,
                  CommandType.StoredProcedure,
                  "GetWaitNotifyInfo",
                  storedParams);
            DataTable dt = new DataTable();
            dt.Load(idr);
            return dt;
        }

        public static void SendNotify(string receiverAccount, string CustomerID, string CustomerName, string D_StartTime,  string title, int holdtime, string content)
        {

            try
            {
                if (!RootObj.UserManager.IsUserExist(receiverAccount)) {
                    log.Warn(string.Format("{0}在腾讯通里不存在!", receiverAccount));
                    return ;
                };
                RootObj.SendNotify(receiverAccount, title, holdtime, content); //获取版本信息
                log.Info(string.Format("{0},{1},{2},{3},{4},{5},{6}", receiverAccount, CustomerID, CustomerName, D_StartTime, title, holdtime, content));
            }
            catch (Exception xe)
            {
                log.Error(xe.StackTrace.ToString());

            }

        }

    }
}

 

三,在app.config中指定job配置的xml文件,触发器的规格见文档

  <quartz>
    <add key="quartz.scheduler.instanceName" value="ExampleDefaultQuartzScheduler"/>
    <add key="quartz.threadPool.type" value="Quartz.Simpl.SimpleThreadPool, Quartz"/>
    <add key="quartz.threadPool.threadCount" value="10"/>
    <add key="quartz.threadPool.threadPriority" value="2"/>
    <add key="quartz.jobStore.misfireThreshold" value="60000"/>
    <add key="quartz.jobStore.type" value="Quartz.Simpl.RAMJobStore, Quartz"/>
    <!--******************************Plugin配置********************************************* -->
    <add key="quartz.plugin.xml.type" value="Quartz.Plugin.Xml.XMLSchedulingDataProcessorPlugin, Quartz" />
    <add key="quartz.plugin.xml.fileNames" value="~/quartz_jobs.xml"/>
  </quartz>

四,quartz_jobs.xml如下

<?xml version="1.0" encoding="UTF-8"?>

<!-- This file contains job definitions in schema version 2.0 format -->

<job-scheduling-data xmlns="http://quartznet.sourceforge.net/JobSchedulingData" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0">

  <processing-directives>
    <overwrite-existing-data>true</overwrite-existing-data>
  </processing-directives>

  <schedule>

    <!--TestJob测试 任务配置-->
    <job>
      <name>RtxSendMsg</name>
      <group>Rtx</group>
      <description>向rtx发送提醒</description>
      <job-type>Quarz.RtxSendMsg,Quarz</job-type>
      <durable>true</durable>
      <recover>false</recover>
    </job>
    <trigger>
      <cron>
        <name>向rtx发送提醒每10分钟</name>
        <group>Rtx</group>
        <job-name>RtxSendMsg</job-name>
        <job-group>Rtx</job-group>
        <start-time>2015-01-22T00:00:00+08:00</start-time>
        <cron-expression>0 0/10 * * * ?</cron-expression>
      </cron>
    </trigger>

  </schedule>
</job-scheduling-data>

五,注册服务在bin下执行

 myapp.exe install
 myapp.exe start
 myapp.exe stop
 myapp.exe uninstall
2015十月17

log4j.net写入日志到sqlserver

未分类 评论关闭

log4j.net用了好久,没有总结过,这次做个记录。
作用:可以缓存多少条再写入库,哪些写入哪些不写入可以配置

一,log4j.net诊断
在web.config或者app.config中增加:

 
  <system.diagnostics>
    <trace autoflush="true">
      <listeners>
        <add
          name="textWriterTraceListener"
          type="System.Diagnostics.TextWriterTraceListener"
          initializeData="C:testlog4net.txt" />
      </listeners>
    </trace>
  </system.diagnostics>

  <appSettings>
    <add key="log4net.Internal.Debug" value="true"/>
    </appSettings >

二,写入sqlserver
1,启动时加载

log4net.Config.XmlConfigurator.ConfigureAndWatch(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "log4net.config"));

2,页面中使用:

public static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
log.Error(e.StackTrace.ToString());

3,数据库里建表


USE [K8]
GO
 
/****** Object:  Table [dbo].[Log4j]    Script Date: 2015/10/17 18:37:59 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Log4j](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Thread] [VARCHAR](255) NOT NULL,
[Logger] [VARCHAR](255) NOT NULL,
[Date] [DATETIME] NOT NULL,
[Level] [VARCHAR](20) NOT NULL,
[Message] [VARCHAR](4000) NOT NULL,
 CONSTRAINT [PK_Log4j] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
 

4,log4net.config配置如下

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>

  <log4net>
    <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      <connectionString value="Data Source=127.0.0.1;Initial Catalog=mydb;User ID=sa;Password=helloworld;Connect Timeout=200;Max Pool Size=100;pooling=true;"/>
      <commandText value="INSERT INTO Log4j ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)"/>
      <bufferSize value="5" />     <!--缓存多少条再写入库里-->
      <threshold value="All"/> <!--记录哪些,如info,debug,error-->
      <parameter>
        <parameterName value="@log_date"/>
        <dbType value="DateTime"/>
        <layout type="log4net.Layout.PatternLayout" value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}"/>
      </parameter>
      <parameter>
        <parameterName value="@thread"/>
        <dbType value="String"/>
        <size value="255"/>
        <layout type="log4net.Layout.PatternLayout" value="%thread"/>
      </parameter>
      <parameter>
        <parameterName value="@log_level"/>
        <dbType value="String"/>
        <size value="50"/>
        <layout type="log4net.Layout.PatternLayout" value="%level"/>
      </parameter>
      <parameter>
        <parameterName value="@logger"/>
        <dbType value="String"/>
        <size value="255"/>
        <layout type="log4net.Layout.PatternLayout" value="%logger"/>
      </parameter>
      <parameter>
        <parameterName value="@message"/>
        <dbType value="String"/>
        <size value="4000"/>
        <layout type="log4net.Layout.PatternLayout" value="%message"/>
      </parameter>
    </appender>

    <!-- 控制台前台显示日志   -->
    <appender name="ColoredConsoleAppender" type="log4net.Appender.ColoredConsoleAppender">
      <mapping>
        <level value="ERROR" />
        <foreColor value="Red, HighIntensity" />
      </mapping>
      <mapping>
        <level value="Info" />
        <foreColor value="Green" />
      </mapping>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%n%date{HH:mm:ss,fff} [%-5level] %m" />
      </layout>

      <filter type="log4net.Filter.LevelRangeFilter">
        <param name="LevelMin" value="Info" />
        <param name="LevelMax" value="Fatal" />
      </filter>
    </appender>

    <root>
      <!--(高) OFF > FATAL > ERROR > WARN > INFO > DEBUG > ALL (低) -->
      <level value="all" />
      <appender-ref ref="AdoNetAppender_SqlServer"/>
      <!--
      <appender-ref ref="ColoredConsoleAppender"/>
 <appender-ref ref="RollingLogFileAppender"/>
       -->

    </root>
  </log4net>
</configuration>