mysql生成随机码

【核心代码】

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace MySQL随机码
{
    class Program
    {
        //连接数据库的连接字符串
        private static string _connectionString =
            string.Format("server={0};Port={1};User Id={2};database={3};password={4};Charset=utf8",
                           "127.0.0.1", 3300, "root", "Test0507", "123456");
        private static string tableName = "t_RandomCode";//表的名称
        private static StringBuilder strTableChar = new StringBuilder("0123456789ABCDEFGHJKLMNPQRSTUVWXYZ");//防伪码的字符组成  
        private static Random r = new Random();//随机数基因
        private static List<string> list = new List<string>();//防伪码列表
        private static List<string> dbList = new List<string>();//保存从数据库读取的数据
        private static int length = 32;//防伪码的长度
        private static Dictionary<char, int> dict = new Dictionary<char, int>();//字符个数统计
        private static string QueryString = "select fRandomCode from t_randomcode";//查询所有防伪码的语句
        static void Main(string[] args)
        {           
            int number = 0;
            Console.WriteLine("正在加载数据库......");         
            GetLarge(QueryString);
            Console.WriteLine("数据库加载完毕");
            while (true)
            {
                Console.Write("请输入随机数生成个数：");
                try
                {
                    number = Convert.ToInt32(Console.ReadLine());
                    break;
                }
                catch
                {
                    Console.WriteLine("请输入一个正整数!!!");
                }
            }
            int i;
            StringBuilder temp = new StringBuilder();
            for (i = 0; i < number; i  )
            {
                temp.Clear();
                int j;
                for (j = 0; j < length; j  )
                {
                    temp.Append(strTableChar[r.Next(34)]);
                }
                //如果已存在数据库，则重新产生一个防伪码
                if(dbList.Contains(temp.ToString()))
                {
                    i--;
                    continue;
                }
                list.Add(temp.ToString());
                if (list.Count >= 1000)
                {
                    if (ExecuteSqlTran(list) == list.Count)
                    {
                        //成功写入数据库
                        list.Clear();
                        dbList.Clear();
                        GetLarge(QueryString);
                    }
                    else
                    {
                        Console.WriteLine("写入数据库失败");
                    }
                }
                else if (i == number - 1)
                {
                    if (ExecuteSqlTran(list) == list.Count)
                    {
                        //成功写入数据库
                        list.Clear();                      
                        dbList.Clear();
                        GetLarge(QueryString);
                    }
                    else
                    {
                        Console.WriteLine("写入数据库失败");
                    }
                }
            }
            string SQLString = string.Format("select count(*) from {0}", tableName);
            int count = Convert.ToInt32(GetSingle(SQLString));
            Console.WriteLine("已经写入数据库，目前数据库共有{0}条记录。", count);
            CharCount(count);
            Console.ReadLine();
        }

        /// <summary>
        /// 执行插入多条数据，实现数据库事务
        /// </summary>
        private static int ExecuteSqlTran(List<string> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    int count = 0;
                    foreach (String item in SQLStringList)
                    {
                        DateTime now = DateTime.Now;
                        //记得在values的内容加‘’，否则报错
                        string query = string.Format("insert into {0}(fRandomCode,fInputDate )values ('{1}','{2}')",
                            tableName, item, now);
                        cmd.CommandText = query;
                        count  = cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return count;
                }
                catch
                {
                    tran.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 从数据库中读取所有防伪码，并统计字母出现概率
        /// </summary>
        /// <param name="number"></param>
        private static void CharCount(int number)
        {
            int i;           
            foreach(string item in dbList)
            {
                for(i = 0; i < item.Length; i  )
                {
                    char c = item[i];
                    if(!dict.ContainsKey(c))
                    {
                        dict.Add(c, 0);
                    }
                    else
                    {
                        dict[c]  ;
                    }
                }
            }
            Console.WriteLine("在数据库中各字符的统计如下：");
            for(i = 0; i < strTableChar.Length; i  )
            {
                if(dict.ContainsKey(strTableChar[i]))
                {
                    double percent = Convert.ToDouble(dict[strTableChar[i]]) / Convert.ToDouble(number) / 32f;
                    Console.WriteLine("{0}:{1}%", strTableChar[i],percent);
                }
                else
                {
                    Console.WriteLine("{0}:0%", strTableChar[i]);
                }
            }
        }

    /// <summary>
    /// 执行一条SQL语句，返回单个查询结果
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>返回结果：obj</returns>
    public static object GetSingle(string SQLString)
        {
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, conn))
                {
                    try
                    {
                        conn.Open();
                        Object obj = cmd.ExecuteScalar();
                        if (Equals(obj, null) || Equals(obj, DBNull.Value))
                        {
                            return null;
                        }
                        return obj;
                    }
                    catch (MySqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条SQL语句，返回一个数据列表
        /// </summary>
        /// <param name="SQLString"></param>
        private static void GetLarge(string SQLString)
        {
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(SQLString, conn))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    if (ds == null || ds.Tables.Count <= 0)
                    {
                        return;
                    }
                    DataTable dt = ds.Tables[0];
                    foreach (DataRow r in dt.Rows)
                    {
                        string str = r.ItemArray[0].ToString();
                        dbList.Add(str);
                    }
                }
            }
        }
    }
}