form1.cs

SQL字段排序 检查SQL Server对字符集的支持。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Globalization;


namespace SQLSortList
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        // 摘要:
        //     表示 System.Char 的最大可能值。此字段为常数。
        //public const char MaxValue = (char)0xffff;
        //
        // 摘要:
        //     表示 System.Char 的最小可能值。此字段为常数。
        //public const char MinValue = '\0';
        //public const int MinValue = 0xFE32;
        //public const int MaxValue = 0xFFEE;

        #region UNICAODE 数值范围

        public const int MinValue0 = 0x0600; //ar 字符范围
        public const int MaxValue0 = 0x06ff;

        public const int MinValue1 = 0x0021; //ASCII 字符范围
        public const int MaxValue1 = 0x0261;

        public const int MinValue2 = 0xFF00; //全角 字符范围
        public const int MaxValue2 = 0xFFEE;

        public const int MinValue3 = 0x3041; //日语平假名
        public const int MaxValue3 = 0x309F;

        public const int MinValue4 = 0x30A0; //日语片假名
        public const int MaxValue4 = 0x30FF;

        public const int MinValue5 = 0x4E00; //cjk 汉字
        public const int MaxValue5 = 0x9FBF;

        public const int MinValue6 = 0xAC00; // 韩语  Hangul Syllables
        public const int MaxValue6 = 0xD7AF;

        public const int MinValue7 = 0x3130; // 韩语  Hangul Compatibility Jamo
        public const int MaxValue7 = 0x318F; // 

        public const int MinValue8 = 0x0590; // hebrew 
        public const int MaxValue8 = 0x05f4; // 

        public const int MinValue9 = 0xFB00; // Hebrew Presentation Forms
        public const int MaxValue9 = 0xFB4F; // 

        #endregion



        #region 三个按钮功能实现


        //退出程序
        private void btnEixt_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        //产生排序规则文件

        private void butSelectRule_Click(object sender, EventArgs e)
        {
            try
            {
                StreamWriter sw = File.CreateText("rule.txt"); //在当前目录创建 rule.txt

                string cnstring = this.tbDBcn.Text.Trim();
                SqlConnection cn = new SqlConnection(cnstring);
                string sqlstr = this.tbSQL.Text.Trim();

                SqlCommand cm = new SqlCommand(sqlstr, cn);

                cn.Open();
                SqlDataReader dr = cm.ExecuteReader(); //得到只读的数据集

                int counter = 0;

                while (dr.Read())   //遍历排序规则，并写入文件
                {
                    string str1 = dr.GetString(0);
                    sw.WriteLine(str1);
                    sw.Flush();   //从缓冲写入文件
                    counter++;
                }

                dr.Close();
                cn.Close();
                sw.Close();
                string strTip = "共有 " + counter.ToString() + " 条排序规则导入到 rule.txt 文件中！";
                MessageBox.Show(strTip);

            }

            catch (SqlException ee)
            {
                MessageBox.Show(ee.Message.ToString());
                return; 
            }

        }

        //按照不同排序规则文件，导出数据文件

        private void butSelectData_Click(object sender, EventArgs e)
        {
            try
            {

                DirectoryInfo dir = new DirectoryInfo("TestData"); //创建数据文件夹
                dir.Create();

                if (File.Exists("rule.txt"))
                {

                    StreamReader sr = new StreamReader("rule.txt");
                    string rulestr = sr.ReadLine();  // 取规则


                    string cnstring = this.tbDBcn.Text.Trim();
                    SqlConnection cn = new SqlConnection(cnstring);

                    int counter = 0;

                    try
                    {
                        while (rulestr != null)  //遍历规则文件
                        {
                            string filename = rulestr + ".txt";
                            filename = @"TestData\" + filename;
                            StreamWriter sw = File.CreateText(filename);
                            string sqlstr = @"Select " + this.tbColName.Text + " from " + this.tbTableName.Text + " order by " + this.tbColName.Text + " collate " + rulestr;
                            SqlCommand cm = new SqlCommand(sqlstr, cn);
                            cn.Open();

                            SqlDataReader dr = cm.ExecuteReader();

                            while (dr.Read())
                            {
                                string tmpstr = dr.GetString(0);
                                sw.WriteLine(tmpstr);
                                sw.Flush();
                            }
                            sw.Close();
                            dr.Close();
                            cn.Close();
                            rulestr = sr.ReadLine(); //循环从rule.txt取排序规则
                            counter++;
                        }
                    }
                    catch(SqlException ee)
                    {
                        MessageBox.Show(ee.Message.ToString());
                        return;
                    }

                    string strTip = "按照排序规则在TestData目录下，导出 " + counter.ToString() + " 个数据文件！";

                    MessageBox.Show(strTip);

                }
                else
                    MessageBox.Show("当前目录没有可用的规则文件 rule.txt！");
            }
            catch (IOException ee)
            {
                MessageBox.Show(ee.Message.ToString());
                return;
            }

        }

#endregion


#region 生成数据
        /// <summary>
        /// 检查生成的字符是否满足条件
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        private static bool isAcceptable(char c)
        {
            UnicodeCategory uc = char.GetUnicodeCategory(c);

            if (char.IsControl(c)
                //|| uc == UnicodeCategory.OtherLetter
                //|| uc == UnicodeCategory.OtherNotAssigned
                //|| uc == UnicodeCategory.OtherNumber
                //|| uc == UnicodeCategory.OtherPunctuation
                //|| uc == UnicodeCategory.OtherSymbol
                || uc == UnicodeCategory.Surrogate
                //|| uc == UnicodeCategory.PrivateUse)
            )
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        /// <summary>
        /// 在指定的Unicode 范围产生随机字符串
        /// </summary>
        /// <param name="r"></param>
        /// <returns></returns>
        private static string randomUnicodeString(Random r)
        {
            const int MAXLENGTH = 10;
            int length = r.Next(1, MAXLENGTH + 1);
            string result = "";
            Random sr = new Random();
            int s = 0; 
            char c = ' ';
            while (result.Length < length)
            {
                //s = sr.Next(0, 3); //ar ,chs,cht,chh
                s = sr.Next(0, 5); //jpn, kor  随机产生字符

                switch (s)
                {
                    case 0:
                        c = Convert.ToChar(r.Next(MinValue1, MaxValue1 + 1));
                        break;
                    case 1:
                        c = Convert.ToChar(r.Next(MinValue2, MaxValue2 + 1));
                        break;
                    case 2:
                        c = Convert.ToChar(r.Next(MinValue6, MaxValue6 + 1));
                        break;
                    case 3:
                        c = Convert.ToChar(r.Next(MinValue7, MaxValue7 + 1));
                        break;
                    case 4:
                        c = Convert.ToChar(r.Next(MinValue5, MaxValue5 + 1));
                      //  MessageBox.Show(c.ToString());
                        break;
 
                }

                if (isAcceptable(c))
                {
                    result += c;
                }
            }
            return result;
        }


        #endregion

        private void button1_Click(object sender, EventArgs e)
        {

            string str;
            StreamWriter sw = new StreamWriter("korokdata.txt");

            Random r = new Random();


            for (int i = 0; i < 10000; i++)  // 创建10000条测试数据，并写到文件中
            {

                str = randomUnicodeString(r);
                sw.WriteLine(randomUnicodeString(r));
                sw.Flush();
            }
            sw.Close();


            //StreamWriter sw2 = new StreamWriter("wwwww.txt");

            //char c = (char)0xfb4f;

            //MessageBox.Show(c.ToString());
            //string ttt = c.ToString();
            //sw2.WriteLine(ttt);
            //sw2.Flush();
            //sw2.Close();

            //for (int i2 = 0; i2 < 65535; i2++)
            //{
            //    c2 = (char)i2;
            //    if (isAcceptable(c2))
            //    {
            //        sw2.WriteLine("{0}\t{1}",i2,c2);
            //        sw2.Flush();
            //    }
            //}
            //sw2.Close();
        }



    }
}