RF / weld · Files

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Text.RegularExpressions;
namespace Weld
{
    class LinkSqlDB
    {
        private string strSQL;
        //与数据库的连接
        //private SqlConnection myConnection;

        private SqlCommandBuilder sqlCmdBld;
        private DataSet ds = new DataSet();
        private SqlDataAdapter da;

        #region  //判断数据项目是否存在
        /// <summary>
      /// 判断数据库中数据是否已经存在
      /// </summary>
      /// <param name="tbName">表名</param>
      /// <param name="strColumn">字段名</param>
      /// <param name="strKeyID">字段值</param>
      /// <returns></returns>
        public int Exists(string tbName,string strColumn,string strKeyID)
        {
            int intValue;
            string strSql = "Select count(1) from " + tbName + " where " + strColumn+" ='"+strKeyID+"'";
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                intValue=int.Parse( dbCmd.ExecuteScalar().ToString());
                dbTrans.Commit();
            }
            catch (Exception t)
            {
                MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                return -1;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return intValue;
        }

        /// <summary>
        /// 判断数据库中数据是否已经存在
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="strColumn">字段名</param>
        /// <param name="strKeyID">字段值</param>
        /// <returns></returns>
        public int Exists(string tbName, string strColumn1, string strColumn2, string strValue1,string strValue2)
        {
            int intValue;
            string strSql = "Select count(0) from " + tbName + " where " + strColumn1 + " ='" + strValue1 + "' and  '"+ strColumn2+ " ='" +strValue2 +"'";
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                intValue = int.Parse(dbCmd.ExecuteScalar().ToString());
                dbTrans.Commit();
            }
            catch (Exception t)
            {
                MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                return -1;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return intValue;
        }

        /// <summary>
        /// 判断数据是否已经存在，主要用于修改数据保存用
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="strColumn">字段名 </param>
        /// <param name="strKeyID">字段值 </param>
        /// <returns></returns>
        public int Exists2(string tbName, string strColumn, string strKeyID)
        {
            int intValue;
            string strSql = "Select count(1) from " + tbName + " where " + strColumn + " ='" + strKeyID + "'";
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                intValue = int.Parse(dbCmd.ExecuteScalar().ToString());
                dbTrans.Commit();
                if (intValue <= 1)
                { intValue = 0; }
            }
            catch (Exception t)
            {
                MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                return -1;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return intValue;
        }
        #endregion

        #region ///////////  操作脱机数据库(创建了该类的实例时直接用)  ////////////////////////////////////////
        /// <summary>
        /// //根据输入的SQL语句检索数据库数据
        /// </summary>
        /// <param name="tempStrSQL">查询SQL字符串</param>
        /// <param name="tempTableName">填充表格的名称</param>
        /// <returns>DataSet对象</returns>
        public DataSet SelectDataBase(string tempStrSQL, string tempTableName)
        {
            try
            {
                this.strSQL = tempStrSQL;
                SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
                this.da = new SqlDataAdapter(this.strSQL, myConnection);
                this.ds.Clear();
                this.da.Fill(ds, tempTableName);
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return ds;//返回填充了数据的DataSet，其中数据表以tempTableName给出的字符串命名
        }

        /// <summary>
        /// //数据库数据更新(传DataSet和DataTable的对象)
        /// </summary>
        /// <param name="changedDataSet">数据发生变化的DataSet对象</param>
        /// <param name="tableName">数据表名</param>
        /// <returns>DataSet对象</returns>
        public DataSet UpdateDataBase(DataSet changedDataSet, string tableName)
        {
            try
            {
                SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
                this.da = new SqlDataAdapter(this.strSQL, myConnection);
                this.sqlCmdBld = new SqlCommandBuilder(da);
                this.da.Update(changedDataSet, tableName);
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return changedDataSet;//返回更新了的数据库表
        }

        

        #endregion

        #region////////////////  直接操作数据库(未创建该类的实例时直接用)  /////////////////////////////////////////////////////

        
        /// <summary>
        /// //检索数据库数据(传字符串,直接操作数据库)
        /// </summary>
        /// <param name="tempStrSQL">SQL查询语句</param>
        /// <returns>Datatable对象</returns>
        public DataTable SelectDataBase(string tempStrSQL)
        {
            DataSet tempDataSet = new DataSet();
            try
            {
                SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
                
                this.da = new SqlDataAdapter(tempStrSQL, myConnection);
                this.da.Fill(tempDataSet);
            }
           
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
                
            }
                        
            return tempDataSet.Tables[0];
        }
        ///// <summary>
        ///// //检索数据库数据(传字符串,直接操作数据库)
        ///// </summary>
        ///// <param name="tempStrSQL">SQL查询语句</param>
        ///// <returns>Datatable对象</returns>
        //public string SelectDataColumn(string tempStrSQL)
        //{
        //    DataSet tempDataSet = new DataSet();
        //    try
        //    {
        //        SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);

        //        this.da = new SqlDataAdapter(tempStrSQL, myConnection);
        //        this.da.Fill(tempDataSet);
        //    }

        //    catch (Exception ex)
        //    {
        //        LogExecute.WriteDBExceptionLog(ex);

        //    }

        //    return tempDataSet.Tables[0].Rows[0][0].ToString();
        //}
        /// <summary>
        /// //数据库数据更新(传字符串，直接操作数据库)
        /// </summary>
        /// <param name="tempStrSQL">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int UpdateDataBase(string tempStrSQL)
        {
            int intNumber = 0;
            try
            {
                SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
                //使用Command之前一定要先打开连接,后关闭连接,而DataAdapter则会自动打开关闭连接
                myConnection.Open();
                SqlCommand tempSqlCommand = new SqlCommand(tempStrSQL, myConnection);
                intNumber = tempSqlCommand.ExecuteNonQuery();//返回数据库中影响的行数
                myConnection.Close();
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return intNumber;
        }
        #endregion

        #region 条件查询操作  表名+Sql语句
        /// <summary>
        /// 条件查询操作
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strSql">Sql语句</param>
        /// <returns>条件查询结果</returns>
        public DataSet SelectX(string strTableName, string strSql)
        {
            DataSet ds = new DataSet();

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();

            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            SqlDataAdapter dbAdapt = new SqlDataAdapter(strSql, myConnection);
            dbAdapt.SelectCommand = dbCmd;

            try
            {
                dbAdapt.Fill(ds, strTableName);
            }
            catch(Exception ex)
            {
                ds.Clear();
                LogExecute.WriteDBExceptionLog(ex);
            }
            finally
            {
                myConnection.Close();
            }
            return ds;
            
        }
        #endregion
        #region 单条记录的插入(包括相应记录的时间）  表名+列名组+值组
        /// <summary>
        /// 单条记录的插入操作+插入时间
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strColumn">列名组</param>
        /// <param name="strValue">值组</param>
        /// <param name="flagTime"></param>
        public bool InsertT(string strTableName, string[] strColumn, object[] strValue)
        {
            #region

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            string strSql = "INSERT INTO " + strTableName + " (";
            for (int i = 0; i < strColumn.Length ; i++)
            {
                strSql += (strColumn[i] + ", ");
            }
            strSql += ("InputTime) VALUES ('");
            for (int i = 0; i < strValue.Length-1 ; i++)
            {
                strSql += (strValue[i] + "', '");
            }
            strSql += (strValue[strValue.Length-1]) + "',";
            strSql += ( "sysdatetime())");
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #endregion
        #region 单条记录的插入(包括相应记录的时间）  表名+列名组+值组
        /// <summary>
        /// 单条记录的插入操作
        /// </summary>
        public bool Insert(string strInsertSql)
        {
            #region

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strInsertSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {

                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #endregion

        #region 单条记录的插入  表名+列名组+值组
        /// <summary>
        /// 单条记录的插入操作
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strColumn">列名组</param>
        /// <param name="strValue">值组</param>
        public bool Insert(string strTableName, string[] strColumn, object[] strValue)
        {
            #region

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            string strSql = "INSERT INTO " + strTableName + " (";
            for (int i = 0; i < strColumn.Length - 1; i++)
            {
                strSql += (strColumn[i] + ", ");
            }
            strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('");
            for (int i = 0; i < strValue.Length - 1; i++)
            {
                strSql += (strValue[i] + "', '");
            }
            strSql += (strValue[strValue.Length - 1] + "')");
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
                dbTrans.Rollback();
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #endregion
   
        #region 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
        /// <summary>
        /// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
        /// </summary>
        /// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
        public bool InsertSet(ref DataSet ds)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();


            try
            {
                foreach (DataTable dt in ds.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string strSql = "INSERT INTO " + dt.TableName + " (";
                        for (int i = 0; i < dt.Columns.Count - 1; i++)
                        {
                            strSql += (dt.Columns[i].Caption + ", ");
                        }
                        strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('");
                        for (int i = 0; i < dt.Columns.Count - 1; i++)
                        {
                            strSql += (dr[i] + "', '");
                        }
                        strSql += (dr[dt.Columns.Count - 1] + "')");

                        SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
                        dbCmd.Transaction = dbTrans;
                        dbCmd.ExecuteNonQuery();
                    }
                }
                dbTrans.Commit();
            }

            catch
            {
                dbTrans.Rollback();
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #endregion
        /// <summary>
        /// 更新函数 
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strColumn"></param>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public bool Update(string strTableName, string[] strColumn, object[] strValue)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();

            string strSql = "UPDATE " + strTableName + " SET ";
            for (int i = 0; i < strColumn.Length - 1; i++)
            {
                strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
            }
            strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " );

            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        /// <summary>
        /// 单条更新数据+更新时间记录
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strColumn"></param>
        /// <param name="strValue"></param>
        /// <param name="strID"></param>
        /// <returns></returns>
        public bool UpdateT(string strTableName, string[] strColumn, object[] strValue,string strID)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();

            string strSql = "UPDATE " + strTableName + " SET ";
            for (int i = 0; i < strColumn.Length  ; i++)
            {
                strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
            }
            strSql +=  "InputTime = sysdatetime() Where C_ID="+strID ;

            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        /// <summary>
        /// 单条更新数据+更新时间记录
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strColumn"></param>
        /// <param name="strValue"></param>
        /// <returns></returns>
        public bool UpdateT(string strTableName, string[] strColumn, object[] strValue)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();

            string strSql = "UPDATE " + strTableName + " SET ";
            for (int i = 0; i < strColumn.Length; i++)
            {
                strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
            }
            strSql += "InputTime = sysdatetime() " ;

            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        /// <summary>
        /// 单条更新数据
        /// </summary>
        /// <returns></returns>
        public bool Update(string strSql)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
                // MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #region 条件更新操作  表名+列名组+值组+条件
        /// <summary>
        /// 条件更新操作
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strColumn">列名组</param>
        /// <param name="strValue">值组</param>
        /// <param name="strCondition">条件</param>
        public bool Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
        {
            #region
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            myConnection.Open();

            string strSql = "UPDATE " + strTableName + " SET ";
            for (int i = 0; i < strColumn.Length - 1; i++)
            {
                strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
            }
            strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " WHERE " + strCondition);

            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.CommandTimeout = 300;
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch (Exception ex)
            {
               // MessageBox.Show(t.ToString());
                dbTrans.Rollback();
                LogExecute.WriteDBExceptionLog(ex);
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                dbCmd.Dispose();
                myConnection.Close();
            }
            return true;
            #endregion
        }
        #endregion
        #region 无条件删除操作,即删除表中所有记录
        /// <summary>
        /// 无条件删除操作,即删除表中所有记录
        /// </summary>
        /// <param name="strTableName">表名</param>
        public bool DeleteAll(string strTableName)
        {
            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            string strSql = "DELETE FROM " + strTableName;
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch
            {
                dbTrans.Rollback();
                return false;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
        }
        #endregion
        #region 条件删除操作
        /// <summary>
        /// 条件删除操作
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strCondition">条件</param>
        public bool Delete(string strTableName, string strCondition)
        {

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
            string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch
            {
                dbTrans.Rollback();
                return false;
                //			throw;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
        }
        #endregion
        #region 条件删除操作
        /// <summary>
        /// 条件删除操作
        /// </summary>
        public bool Delete(string strSql)
        {

            SqlConnection myConnection = new SqlConnection(DBConnection.DBConnstring);
           // string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
            myConnection.Open();
            SqlTransaction dbTrans = myConnection.BeginTransaction();
            SqlCommand dbCmd = new SqlCommand(strSql, myConnection);
            dbCmd.Transaction = dbTrans;

            try
            {
                dbCmd.ExecuteNonQuery();
                dbTrans.Commit();
            }
            catch
            {
                dbTrans.Rollback();
                return false;
                //			throw;
            }
            finally
            {
                dbTrans.Dispose();
                myConnection.Close();
            }
            return true;
        }
        #endregion
        #region 执行SQL语句返回SqlDataReader
        ///<summary>
        ///执行SQL语句返回SqlDataReader
        ///</summary>
        ///<param  name="sql">要执行的SQL语句</param>
        ///<returns>返回SqlDataReader,需要手动关闭连接！</returns>
        ///
        public SqlDataReader datareader(string sql)
        {
            //SqlConnection myCn = Login.Cn();
            SqlConnection myCn = DBConnection.DBConnectionOpen();
            SqlCommand cmd = new SqlCommand(sql, myCn);
            SqlDataReader dr = null;

            try
            {
                dr = cmd.ExecuteReader();
            }
            catch (SqlException oe)
            {
                if (dr != null) dr.Close();
                myCn.Dispose();
                cmd.Dispose();
                throw new Exception(oe.ToString());
            }
            finally
            {
                //			myCn.Close();
            }
            return dr;
        }
        #endregion

        /// <summary>
      /// 获取表格所有数据
      /// </summary>
      /// <param name="tbName"></param>
      /// <returns></returns>
        public DataTable GetALL(string tbName)
        {
            try
            {
                string SQL_Str = " select * from  "+tbName;
                DataSet ds = SqlHelper.ExecuteDataset(DBConnection.DBConnstring, CommandType.Text, SQL_Str);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return null;
        }

        /// <summary>
        /// 获取表格所有数据
        /// </summary>
        /// <param name="tbName"></param>
        /// <returns></returns>
        public DataTable GetALL2(string strSql)
        {
            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(DBConnection.DBConnstring, CommandType.Text, strSql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return null;
        }

        /// <summary>
        /// 获取表格所有数据,并分页显示
        /// </summary>
        /// <param name="tbName"></param>
        /// <returns></returns>
        public DataTable GetALLDataPageDisplay(string TableName,string TIndex,string TColumn,string strWhere , int pageIndex, int pageSize,string strOrderby,out int RecordCount )
        {
             RecordCount = 0;
            using (SqlConnection connection = new SqlConnection(DBConnection.DBConnstring))
            {
                SqlCommand command = new SqlCommand("procPageDisplay", connection);
                command.CommandType = CommandType.StoredProcedure;//采用存储过程
                //存储过程参数
                command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = TableName;
                command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = TIndex;
                command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = TColumn;
                command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = strWhere;
                command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();
                command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();
                command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = strOrderby;
                //打开连接
                if (connection.State != ConnectionState.Open) { connection.Open(); }
                try
                {
                    //填充数据
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    //获取总记录数
                    RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                    //返回数据集
                    return ds.Tables[0];

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                    return null; ;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="tbName"></param>
        /// <returns></returns>
        public int execProcedureEquiment(string ProcName, string stationCode, string EquipmentCode, string EquipmentName, string EquipmentType, string EquipmentCycleTime, string EquipmentMaintainTime)
        {
           
            using (SqlConnection connection = new SqlConnection(DBConnection.DBConnstring))
            {
                SqlCommand command = new SqlCommand(ProcName, connection);
                command.CommandType = CommandType.StoredProcedure;//采用存储过程
                //存储过程参数
                command.Parameters.Add("@stationCode", SqlDbType.NVarChar, 50).Value = stationCode;
                command.Parameters.Add("@equipmentCode", SqlDbType.NVarChar, 50).Value = EquipmentCode;
                command.Parameters.Add("@equipmentName", SqlDbType.NVarChar, 50).Value = EquipmentName;
                command.Parameters.Add("@equipmentType", SqlDbType.NVarChar, 50).Value = EquipmentType;
                command.Parameters.Add("@equipmentCycleTime", SqlDbType.NVarChar, 50).Value = EquipmentCycleTime;
                command.Parameters.Add("@equipmentMaintainTime", SqlDbType.NVarChar, 50).Value = EquipmentMaintainTime;
             
                //打开连接
                if (connection.State != ConnectionState.Open) { connection.Open(); }
                try
                {
                    //填充数据
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    
                    //获取总记录数
                    //返回数据集
                    return da.Fill(ds); ;

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                    return -1 ;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="tbName"></param>
        /// <returns></returns>
        public int execProcedureParameter(string ProcName, int equipmentID, string parameterCode, string parameterName)
        {

            using (SqlConnection connection = new SqlConnection(DBConnection.DBConnstring))
            {
                SqlCommand command = new SqlCommand(ProcName, connection);
                command.CommandType = CommandType.StoredProcedure;//采用存储过程
                //存储过程参数
                command.Parameters.Add("@equipmentInfoID", SqlDbType.Int).Value = equipmentID;
                command.Parameters.Add("@parameterCode", SqlDbType.NVarChar, 50).Value = parameterCode;
                command.Parameters.Add("@parameterName", SqlDbType.NVarChar, 50).Value = parameterName;
                //打开连接
                if (connection.State != ConnectionState.Open) { connection.Open(); }
                try
                {
                    //填充数据
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();

                    //获取总记录数
                    //返回数据集
                    return da.Fill(ds); ;

                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                    return -1;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 获取一行数据
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public DataRow GetOne(string tbName,string C_ID)
        {
            try
            {
                string SQL_Str = " select * from "+tbName+" where C_ID="+C_ID;
                DataSet ds = SqlHelper.ExecuteDataset(DBConnection.DBConnstring, CommandType.Text, SQL_Str);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0];
                }
            }
            catch (Exception ex)
            {
                LogExecute.WriteDBExceptionLog(ex);
            }
            return null;
        }
        #region 驗證輸入的字符串
        /// <summary>
        /// 判斷輸入的字符類型　
        /// </summary>
        /// <param name="_value">輸入的字串</param>
        /// <param name="_kind">要驗證的類型</param>
        /// 1: 由26個英文字母組成的字串 
        /// 2: 正整數 
        /// 3: 非負整數（正整數 + 0)
        /// 4: 非正整數（負整數 + 0）
        /// 5: 負整數 
        /// 6: 整數
        /// 7: 非負浮點數（正浮點數 + 0）
        /// 8: 正浮點數
        /// 9: 非正浮點數（負浮點數 + 0)
        /// 10: 負浮點數 
        /// 11: 浮點數
        /// 12: 由26個英文字母的大寫組成的字串
        /// 13: 由26個英文字母的小寫組成的字串
        /// 14: 由數位和26個英文字母組成的字串
        /// 15: 由數位、26個英文字母或者下劃線組成的字串
        /// 16: Email
        /// 17: URL
        /// 18: 只能輸入入中文
        /// 19: 
        /// <returns>true是驗証通過,false是驗証錯誤</returns>

        public  bool ValidateUserInput(String _value, int _kind)
        {
            string RegularExpressions = null;

            switch (_kind)
            {
                case 1:
                    //由26個英文字母組成的字串
                    RegularExpressions = "^[A-Za-z]+$";
                    break;
                case 2:
                    //正整數 
                    RegularExpressions = "^[0-9]*[1-9][0-9]*$";
                    break;
                case 3:
                    //非負整數（正整數 + 0)
                    RegularExpressions = "^\\d+$";
                    break;
                case 4:
                    //非正整數（負整數 + 0）
                    RegularExpressions = "^((-\\d+)|(0+))$";
                    break;
                case 5:
                    //負整數 
                    RegularExpressions = "^-[0-9]*[1-9][0-9]*$";
                    break;
                case 6:
                    //整數
                    RegularExpressions = "^-?\\d+$";
                    break;
                case 7:
                    //非負浮點數（正浮點數 + 0）
                    RegularExpressions = "^\\d+(\\.\\d+)?$";
                    break;
                case 8:
                    //正浮點數
                    RegularExpressions = "^(([0-9]+\\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\\.[0-9]+)|([0-9]*[1-9][0-9]*))$";
                    break;
                case 9:
                    //非正浮點數（負浮點數 + 0）
                    RegularExpressions = "^((-\\d+(\\.\\d+)?)|(0+(\\.0+)?))$";
                    break;
                case 10:
                    //負浮點數
                    RegularExpressions = "^(-(([0-9]+\\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\\.[0-9]+)|([0-9]*[1-9][0-9]*)))$";
                    break;
                case 11:
                    //浮點數
                    RegularExpressions = "^(-?\\d+)(\\.\\d+)?$";
                    break;
                case 12:
                    //由26個英文字母的大寫組成的字串
                    RegularExpressions = "^[A-Z]+$";
                    break;
                case 13:
                    //由26個英文字母的小寫組成的字串
                    RegularExpressions = "^[a-z]+$";
                    break;
                case 14:
                    //由數位和26個英文字母組成的字串
                    RegularExpressions = "^[A-Za-z0-9]+$";
                    break;
                case 15:
                    //由數位、26個英文字母或者下劃線組成的字串 
                    RegularExpressions = "^[0-9a-zA-Z_]+$";
                    break;
                case 16:
                    //email地址
                    RegularExpressions = "\\w+([-+.']\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*";
                    break;
                case 17:
                    //url
                    RegularExpressions = "^[a-zA-z]+://(\\w+(-\\w+)*)(\\.(\\w+(-\\w+)*))*(\\?\\S*)?$";
                    break;
                case 18:
                    //只能輸入中文
                    RegularExpressions = "^[^\u4E00-\u9FA5]";
                    break;
                case 19:
                    //只能输入0和非0打头的数字
                    RegularExpressions = "^(0|[1-9][0-9]*)$";
                    break;
                case 20:
                    //只能输入数字
                    RegularExpressions = "^[0-9]*$";
                    break;
                case 21:
                    //只能輸入數字加2位小數
                    RegularExpressions = "^[0-9]+(.[0-9]{1,2})?$";
                    break;
                case 22:
                    //只能输入0和非0打头的数字加2位小數
                    RegularExpressions = "^(0|[1-9]+)(.[0-9]{1,2})?$";
                    break;
                case 23:
                    //只能输入0和非0打头的数字加2位小數   但不匹配0.00
                    RegularExpressions = "^(0(.(0[1-9]|[1-9][0-9]))?|[1-9]+(.[0-9]{1,2})?)$";
                    break;
                default:
                    break;
            }

            Match m = Regex.Match(_value, RegularExpressions);

            if (m.Success)
                return true;
            else
                return false;

        }
        #endregion 


    }
}