sql server clr 应用(二)

在网络发达的今天，短信似乎已经不重要了。但在某些特定的场合还是很有用的，尤其是提醒类的短信。发送短信基本上用的是运营商的mas机，很坑的是：对一般企业来说，三大运营商的mas机不能互发，巨坑的是只能发省内的手机，而且还要加入白名单。限制实在太多。对民营企业来说，mas机可以发送大部份的短信，受限制的部份只能用短信猫来发送。

厂家配送的短信猫驱动大多是dll的动态链接库，单条短信发送是没问题，长时间连续多条发送时，常会出现一些莫名的问题。无奈之下只得考虑自己写驱动。这种触发类的驱动，用数据库的触发器来触发是最理想的，每插入一条记录就发送一条短信。 查阅相关资料后，终于搞懂短信发送的编码和解码，以下是我用Ｃ#写，用于发送短信的数据库确发器。

C#代码：

                
        using System;
        using System.Data.SqlTypes;
        using System.Data.SqlClient;
        using Microsoft.SqlServer.Server;
        using System.Transactions;
        using System.IO.Ports;
        using System.Text;
        namespace gym
        {
            public class gymtest
            {
                const int sms_lr = 69; //短信内容字数
                const string sms_pr = "00";//非中文补字符

                /*      
                /// <summary>
                /// 短信发送
                /// </summary>
                /// <param name="sms_cont">发送内容</param>
                /// <param name="pph">手机号</param>
                /// <param name="port">串口号</param>
                /// <returns></returns>
                  */  
                public static int sendmao(string sms_cont, string pph, SerialPort port)
                {
                    String sim = String.Empty;
                    /*
                    //获取收到的短信条数，当收到的短信浅满时，就无法再发送，所以发送前就要先清空收到的短信
                    //"AT+CPMS=\"SM\""表示获短信总条数
                    */
                    SendCommand("AT+CPMS=\"SM\"" + Environment.NewLine, ref sim, 15000, port);
                    int j = int.Parse(sim.Substring(sim.IndexOf(":")
                    + 1, sim.IndexOf(",")
                    - (sim.IndexOf(":") + 1)));
                    for (int i = j; i > 0; i--)
                    {
                        //"AT+CMGD="表示删除短信
                        SendCommand("AT+CMGD=" + i.ToString() + Environment.NewLine, ref sim, 5000, port);
                    }
                    string cstr = getucs2(sms_cont); //内容编码后再能发送
                    string ph = getphone(pph.Trim() + "F"); //对手机号编码
                    SendCommand("AT+CMGF=0" + Environment.NewLine, ref sim, 15000, port); //定义格式
                    string mycst = string.Format("AT+CMGS={0}\n0031000D9168{1}000800{2:X2}{3}{4}"
                                    , (15 + (int)(cstr.Length / 2)).ToString()
                                    , ph
                                    , (int)(cstr.Length / 2)
                                    , cstr.ToUpper()
                                    , Encoding.ASCII.GetString(new byte[] { (byte)(26) }));
                    SendCommand(mycst, ref sim, 5000, port); //发送短信内容
                    return (sim.IndexOf("ERROR") > 0 ? undefined : 1);

                }
                    /*
                /// <summary>
                /// 往手机串口发送数据
                ///
                ///</summary>
                /// <param name="Sendcmd">发送的数据（命令）</param>
                /// <param name="ReceiveData">接收到的数据</param>
                /// <param name="Overtime">超时</param>
                /// <param name="port">串口号</param>
                    */
                public static void SendCommand(string Sendcmd, ref string ReceiveData, int Overtime, SerialPort port)
                {
                    if (port.IsOpen)
                    {
                        try
                        {
                            port.DiscardInBuffer(); //清空接收缓冲区
                            port.Write(Sendcmd); //写入数据（命令 ）
                            int num = 0,
                            ret = 0;
                            while (num++ < Overtime)
                            {
                                System.Threading.Thread.Sleep(1000);
                                if (port.BytesToRead > 0) //存在需要读取的数据
                                {
                                    ret = port.BytesToRead;
                                    break;
                                }
                                else
                                {
                                    System.Threading.Thread.Sleep(100); //延时，等待数据发送到串口上
                                }
                            }
                            if (ret > 0) //如果要读取的数据存在
                            {
                                ReceiveData = port.ReadExisting();      //接收发送命令后返回的数据
                            }
                        }
                        catch { } //出错不处理
                    }

                }
                    /*
                /// <summary>
                /// 对手机号编码
                /// </summary>
                /// <param name="ph">手机号</param>
                /// <returns></returns>
                    */
                public static string getphone(string ph)
                {
                    if (ph.Length != 12) return "";
                    char[] qq = new char[12];
                    for (int i = 5; i >= 0; i--)
                    {
                        qq[i * 2 + 1] = ph[i * 2];
                        qq[i * 2] = ph[i * 2 + 1];
                    }
                    string cstr = "";
                    foreach (char rt in qq)
                    {
                        cstr += rt.ToString();
                    }
                    //return string.Concat(qq); clr下不能用string.Concat
                    return cstr;
                }
                    /*
                //读取短信时的手机号解码
                //public static  string fgetphone(string ph)
                //{

                //    char[] qq = new char[ph.Length];
                //    for (int i = (int)(ph.Length / 2) - 1; i >= 0; i--)
                //    {
                //        qq[i * 2 + 1] = ph[i * 2];
                //        qq[i * 2] = ph[i * 2 + 1];
                //    }
                //    return string.Concat(qq);
                //}
                /// <summary>
                /// 
                /// </summary>
                /// <param name="sms_cont"></param>
                /// <returns></returns>
                    */
                public static string getucs2(string sms_cont)  //短信内容编码
                {
                    string cstr = "";
                    string lostr = "";
                    int l = (sms_cont.Length > sms_lr ? sms_lr : sms_cont.Length);
                    for (int i = 0; i < l; i++)
                    {
                        lostr = ((int)sms_cont[i]).ToString("x");
                        cstr += (lostr.Length < 4 ? sms_pr + lostr : lostr);
                    }
                    return cstr;
                }
                    /*
                /// <summary>
                /// 数据库的确发器
                ///</summary>
                    */
                [SqlTrigger(Name = @"send_sms", Target = "[dbo].[sms]", Event = "FOR INSERT")]
                unsafe public static void send_sms()
                {
                    string constr = ""; //短信内容
                    string ph = "";  //手机号
                    int cid = 0; //短信类型
                    int bzid = 0; //是否重发
                    int st = undefined;  //发送状态
                    SqlCommand command;
                    SqlTriggerContext triggContext = SqlContext.TriggerContext;
                    SqlDataReader reader;

                    switch (triggContext.TriggerAction)
                    {
                        case TriggerAction.Insert:
                            //打开手机所在的串口
                            SerialPort port = new SerialPort("com3", 9600, Parity.None, 8, StopBits.One);
                            port.ReadTimeout = 15000;
                            port.WriteTimeout = 15000;
                            port.RtsEnable = true;

                            //连接数据库
                            using (SqlConnection connection = new SqlConnection(@"context connection=true"))
                            {
                                try
                                {
                                    connection.Open();
                                    //获取插入内容
                                    command = new SqlCommand(@"SELECT ph,nei,id,bzid FROM INSERTED;", connection);
                                    reader = command.ExecuteReader();
                                    reader.Read();
                                    constr = (string)reader[1];
                                    ph = (string)reader[0];
                                    cid = (int)reader[2];
                                    bzid = (int)reader[3];
                                    reader.Close();
                                    bool lok = true;
                                    try { port.Open(); }
                                    catch { lok = false; }
                                    st = lok ? sendmao(constr.Trim(), ph.Trim(), port) : undefined;
                                }
                                catch { } //出错不处理
                                finally
                                {
                                    if (bzid > 0)//重发
                                    {
                                        if (st == 1)  //成功后删除zlc中的失败记录
                                        {
                                            command = new SqlCommand(string.Format("delete from zlc where sid={0}", bzid), connection);
                                            command.ExecuteNonQuery();
                                            command = new SqlCommand(string.Format("insert zl(nei,ph,bz,sid) values ('{0}','{1}',{2},{3})", 
                                                    constr, ph, st, bzid), connection);
                                            command.ExecuteNonQuery();

                                        }
                                        else //不成功，记录发送次数记录
                                        {
                                            command = new SqlCommand(string.Format("update zlc set ssid=ssid+1 where sid={0}", bzid), connection);
                                            command.ExecuteNonQuery();
                                        }
                                    }
                                    else  //正常发送
                                    {
                                        if (st == 1) //成功
                                        {
                                            command = new SqlCommand(string.Format("insert zl(nei,ph,bz,sid) values ('{0}','{1}',{2},{3})", 
                                                                            constr.Replace("'", "''"), ph, st, cid), connection);
                                            command.ExecuteNonQuery();
                                        }
                                        else //不成功，记录失败记录，待时重发
                                        {
                                            command = new SqlCommand(string.Format("insert zlc(nei,ph,bz,sid) values ('{0}','{1}',{2},{3})", 
                                                            constr.Replace("'", "''"), ph, st, cid), connection);
                                            command.ExecuteNonQuery();
                                        }
                                    }
                                    port.Close();
                                    port.Dispose();
                                    port = null;
                                    System.Threading.Thread.Sleep(2600);//等待dispose完成

                                }
                            }

                            break;
                        //st：1成功，-1发送错误 -2打开端口错误
                    }
                }
            }
        }