commonclass.cs

一个通讯录源码,用C#写的.挺不错的,大家可以参考看一下.

                }
                catch (ArgumentException e)
                {
                    const string sConnectionError = "数据连接失败。";
                    ClientFuncion.ShowErrorMsg(sConnectionError + e.Message);
                }
            }
        }

        /// <summary>
        /// 取得数据库连接
        /// </summary>
        public DbConnection Connection
        {
            get
            {
                CheckConnection();
                return dbConn;
            }
        }

        /// <summary>
        /// 创建Command
        /// </summary>
        /// <param name="sqlString">SQL语法</param>
        /// <returns></returns>
        public DbCommand DBCommand(string sqlString)
        {
            DbCommand cmd = dbFactory.CreateCommand();
            cmd.CommandText = sqlString;
            cmd.Connection = Connection;
            return cmd;
        }

        public OleDbFactory DBFactory
        {
            get
            {
                return dbFactory;
            }
        }
        /// <summary>
        /// 创建DataAdapter
        /// </summary>
        /// <param name="sqlString">SQL语法</param>
        /// <returns></returns>
        public DbDataAdapter DBDataAdapter(string sqlString)
        {
            DbDataAdapter dr = dbFactory.CreateDataAdapter();
            dr.SelectCommand = DBCommand(sqlString);
            return dr;
        }

        /// <summary>
        /// 通过语法，取得数值，返回是值的数组
        /// </summary>
        /// <param name="sqlString">SQL语法</param>
        /// <returns></returns>
        public object[] DBSelectValue(string sqlString)
        {            
            DbCommand cmd = null;
            DbDataReader dr = null;
            object[] result = null;
            try
            {
                cmd = DBCommand(sqlString);
                dr = cmd.ExecuteReader();
                if (dr.HasRows && dr.Read())
                {
                    result = (object[])Array.CreateInstance(typeof(object), dr.FieldCount);
                    for (int I = 0; I < dr.FieldCount; I++)
                        result[I] = dr[I];
                    cmd.Cancel(); //DataReader Close之前调用Command的Cancel
                    dr.Close();
                }
            }
            catch (InvalidOperationException E)
            {
                ClientFuncion.ShowErrorMsg(E.ToString());
            }
            catch (DbException E)
            {
                ClientFuncion.ShowErrorMsg(E.Message.ToString());
            }
            return result;
        }

        /// <summary>
        /// 填充字符串
        /// </summary>
        /// <param name="value">原字符串</param>
        /// <param name="newChar">填充字符</param>
        /// <param name="length">总长度</param>
        /// <returns></returns>
        public string FillChar(string value, char newChar, int length)
        {
            string result = value;
            for (int I = 1; I <= length - value.Length; I++)
            {
                result = newChar + result;
            }
            return result;
        }

        /// <summary>
        /// 取得表中最大编号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="noFieldName">编号字段名</param>
        /// <param name="subStr">前缀字符</param>
        /// <returns></returns>
        public string GetMaxNo(string tableName, string noFieldName, string subStr)
        {
            const string sReadMaxNo = "SELECT MAX({0}) AS MaxNo FROM {1} WHERE {0} LIKE '{2}%' ";
            string result = "00001";
            object[] values = DBSelectValue(string.Format(sReadMaxNo, noFieldName, tableName, subStr));
            if (null != values && values[0].ToString().Length > 0)
            {
                string maxNO = values[0].ToString();
                int subLen = subStr.Length;
                result = Convert.ToString(Convert.ToInt32(maxNO.Substring(subLen + 1)) + 1);

                result = subStr + FillChar(result, '0', 5);
            }
            return result;
        }

        /// <summary>
        /// 取得表最大主键值，返回整型值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="keyFieldName">主键名</param>
        /// <param name="condition">附加条件</param>
        /// <returns></returns>
        public int GetMaxID(string tableName, string keyFieldName, string condition)
        {
            const string sReadMaxID = "SELECT MAX({0}) AS Maxid FROM {1}";
            string where = (condition.Length == 0)? string.Empty: "WHERE " + condition;
            int result = -1;            
            try
            {
                object[] values = DBSelectValue(string.Format(sReadMaxID, keyFieldName, tableName));
                if (null != values && Convert.ToInt32(values[0]) > 0)
                    result = Convert.ToInt32(values[0]) + 1;
                else
                    result = 1;
            }
            catch(InvalidOperationException)
            {
                return result;
            }
            return result;
        }

        /// <summary>
        /// 检测数据是否已经存在
        /// </summary>
        /// <param name="keyValue">主键ID值</param>
        /// <param name="tableName">表名</param>
        /// <param name="fieldName">检查值的字段</param>
        /// <param name="keyFieldName">主键字段</param>
        /// <param name="value">检查的值</param>
        /// <returns></returns>
        public bool ValueExists(int keyValue, string tableName, string fieldName, string keyFieldName, string value)
        {
            const string sReadExists = "SELECT 1 FROM {0} WHERE {1} <> {2} AND {3} = '{4}'";
            string[] format = new string[] { tableName, keyFieldName, keyValue.ToString(), fieldName, value.Replace("'", "\''") };
            object[] values = DBSelectValue(string.Format(sReadExists, format));
            return (null != values && values[0].ToString().Length > 0);
        }

    }
    #endregion

    #region 常用函数
    public class ClientFuncion
    {
        private ClientFuncion()
        {
        }

        /// <summary>
        /// 字符串加单引号
        /// </summary>
        /// <param name="value">字符串</param>
        /// <returns></returns>
        public static string QuoteStr(string value)
        {
            return '\'' + value.Replace("'", "''") + '\'';
        }

        public static void ShowErrorMsg(string message)
        {
            MessageBox.Show(message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        #region 程序集属性访问器

        public static string AssemblyTitle
        {
            get
            {
                // 获取此程序集上的所有 Title 属性
                object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyTitleAttribute), false);
                // 如果至少有一个 Title 属性
                if (attributes.Length > 0)
                {
                    // 请选择第一个属性
                    AssemblyTitleAttribute titleAttribute = (AssemblyTitleAttribute)attributes[0];
                    // 如果该属性为非空字符串，则将其返回
                    if (titleAttribute.Title != string.Empty)
                        return titleAttribute.Title;
                }
                // 如果没有 Title 属性，或者 Title 属性为一个空字符串，则返回 .exe 的名称
                return System.IO.Path.GetFileNameWithoutExtension(Assembly.GetExecutingAssembly().CodeBase);
            }
        }

        public static string AssemblyVersion
        {
            get
            {
                return Assembly.GetExecutingAssembly().GetName().Version.ToString();
            }
        }

        public static string AssemblyDescription
        {
            get
            {
                // 获取此程序集的所有 Description 属性
                object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyDescriptionAttribute), false);
                // 如果 Description 属性不存在，则返回一个空字符串
                if (attributes.Length == 0)
                    return string.Empty;
                // 如果有 Description 属性，则返回该属性的值
                return ((AssemblyDescriptionAttribute)attributes[0]).Description;
            }
        }

        public static string AssemblyProduct
        {
            get
            {
                // 获取此程序集上的所有 Product 属性
                object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyProductAttribute), false);
                // 如果 Product 属性不存在，则返回一个空字符串
                if (attributes.Length == 0)
                    return string.Empty;
                // 如果有 Product 属性，则返回该属性的值
                return ((AssemblyProductAttribute)attributes[0]).Product;
            }
        }

        public static string AssemblyCopyright
        {
            get
            {
                // 获取此程序集上的所有 Copyright 属性
                object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCopyrightAttribute), false);
                // 如果 Copyright 属性不存在，则返回一个空字符串
                if (attributes.Length == 0)
                    return string.Empty;
                // 如果有 Copyright 属性，则返回该属性的值
                return ((AssemblyCopyrightAttribute)attributes[0]).Copyright;
            }
        }

        public static string AssemblyCompany
        {
            get
            {
                // 获取此程序集上的所有 Company 属性
                object[] attributes = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCompanyAttribute), false);
                // 如果 Company 属性不存在，则返回一个空字符串
                if (attributes.Length == 0)
                    return string.Empty;
                // 如果有 Company 属性，则返回该属性的值
                return ((AssemblyCompanyAttribute)attributes[0]).Company;
            }
        }
        #endregion

        /// <SUMMARY>   
        /// 将Grid的数据导入到EXECL   
        /// </SUMMARY>   
        public static bool ExportExcel(DataGridView gridView, string excleFileName)
        {
            if (gridView.RowCount == 0) return false;

            Application.DoEvents();

            // 创建Excel对象 
            Excel.Application xlApp = new Excel.ApplicationClass();
            if (xlApp == null)
            {
                ClientFuncion.ShowErrorMsg("Excel无法启动");
                return false;
            }
            // 创建Excel工作薄
            Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
            Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];

            // 设置标题
            Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, gridView.ColumnCount]);
            range.MergeCells = true;
            xlApp.ActiveCell.FormulaR1C1 = string.Empty;
            xlApp.ActiveCell.Font.Size = 20;
            xlApp.ActiveCell.Font.Bold = true;
            xlApp.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;

            // 列索引，行索引，总列数，总行数
            int colIndex = 0;
            int RowIndex = 0;
            int colCount = gridView.ColumnCount;
            int RowCount = gridView.BindingContext[gridView.DataSource, gridView.DataMember].Count;

            // 创建缓存数据
            object[,] objData = new object[RowCount + 1, colCount];

            // 获取列标题
            foreach (DataGridViewColumn cs in gridView.Columns)
            {
                objData[RowIndex, colIndex++] = cs.HeaderText;
            }
            // 获取数据
            for (RowIndex = 0; RowIndex < RowCount; RowIndex++)
            {