SQL工具类

public class SqlDBHelper    
{
        public static string connectionString = ConnectConfig.DBConnectionString;  
        #region 初始化init()
        /// <summary>
        /// 根据实体类生成数据访问
        /// 王利民
        /// </summary>
        /// <param name="dtStru"></param>
        /// <param name="adapter"></param>
        /// <param name="conn"></param>
        private static void init(DataTable dtStru, System.Data.SqlClient.SqlDataAdapter adapter, SqlConnection connection)
        {
            string strTableName = dtStru.TableName;
            DataColumn[] colPkName = dtStru.PrimaryKey;
            string strInsertField = "";
            string strInsertValue = "";
            string strUpdate = "";
            //插入语句
            string strInsertSql = "";
            //修改语句
            string strUpdateSql = "";
            //删除语句
            string strDeleteSql = "";
            string strWhereSql = "";
            foreach (DataColumn dc in colPkName)
            {
                if (strWhereSql == "")
                    strWhereSql = " where " + dc.ColumnName + @" = @Original_" + dc.ColumnName;
                else
                    strWhereSql += " and " + dc.ColumnName + @" = @Original_" + dc.ColumnName;
            }
            System.Data.SqlClient.SqlCommand sqlInsertComm = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand sqlUpdateComm = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand sqlDeleteComm = new System.Data.SqlClient.SqlCommand();

            //动态生成映射列
            System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName);

            foreach (DataColumn dcField in dtStru.Columns)
            {
                dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName);
                //如果不是自动增长的字段

                if (!dcField.AutoIncrement)
                {

                    if (strInsertField == "")
                    {
                        strInsertField = dcField.ColumnName;
                        strInsertValue = "@" + dcField.ColumnName;

                    }
                    else
                    {
                        strInsertField += "," + dcField.ColumnName;
                        strInsertValue += ",@" + dcField.ColumnName;

                    }
                    if (!dcField.Unique)
                    {
                        if (strUpdate == "")
                        {
                            strUpdate = dcField.ColumnName + "=@" + dcField.ColumnName;
                        }
                        else
                        {
                            strUpdate += "," + dcField.ColumnName + "=@" + dcField.ColumnName;
                        }
                    }
                }
            }
            strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")";
            strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql;
            strDeleteSql = @"delete " + strTableName + strWhereSql;
            adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap });
            // 
            // Adapter
            // 

            adapter.DeleteCommand = sqlDeleteComm;
            adapter.InsertCommand = sqlInsertComm;
            adapter.UpdateCommand = sqlUpdateComm;
            // 
            // sqlInsertComm
            // 
            //动态生成插入语句

            sqlInsertComm.CommandText = strInsertSql;
            sqlInsertComm.Connection = connection;
            BuildPara(sqlInsertComm, dtStru);
            // 
            // sqlUpdateComm
            // 
            sqlUpdateComm.CommandText = strUpdateSql;
            sqlUpdateComm.Connection = connection;
            BuildPara(sqlUpdateComm, dtStru);
            BuildWherePara(sqlUpdateComm, dtStru);
            // 
            // sqlDeleteComm
            // 
            sqlDeleteComm.CommandText = strDeleteSql;
            sqlDeleteComm.Connection = connection;
            BuildWherePara(sqlDeleteComm, dtStru);
        }

        /// <summary>
        /// 根据表名返回所有列名称
        /// </summary>
        /// <param name="strTablename">表名称</param>
        /// <returns></returns>
        private static ArrayList getcolumn(string strTablename, SqlTransaction myTrans)
        {
            try
            {
                SqlParameter[] paramss = 
                {
                    new SqlParameter{ParameterName="@table_name",Direction = ParameterDirection.Input,  Value = strTablename}
                };
                // 执行存储过程，返回指定的结果集行数，同时返回总行数
                DataSet ds = RunProcedureNoConn("sp_columns", paramss, myTrans);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ArrayList arlCol = new ArrayList();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        arlCol.Add(dr["COLUMN_NAME"].ToString());
                    }
                    return arlCol;
                };
                return null;

            }
            catch (System.Exception e)
            {
                throw e;
            }

        }


        private static DataSet RunProcedureNoConn(string storedProcName, IDataParameter[] parameters, SqlTransaction myTrans)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet data = new DataSet();
                try
                {
                    adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    adapter.SelectCommand.Transaction = myTrans;
                    adapter.Fill(data, storedProcName);
                }
                catch (System.Exception e)
                {
                    throw e;
                }
                return data;
            }
        }


        /// <summary>
        /// 验证表结构
        /// </summary>
        /// <param name="dtStru">DataTable</param>
        /// <param name="adapter"></param>
        /// <param name="arlCol">在SQlServer表中的列名称</param>
        private static void init(DataTable dtStru,SqlConnection connection, System.Data.SqlClient.SqlDataAdapter adapter, ArrayList arlCol)
        {
            string strTableName = dtStru.TableName;
            DataColumn[] colPkName = dtStru.PrimaryKey;
            string strInsertField = "";
            string strInsertValue = "";
            string strUpdate = "";
            //插入语句
            string strInsertSql = "";
            //修改语句
            string strUpdateSql = "";
            //删除语句
            string strDeleteSql = "";
            string strWhereSql = "";
            foreach (DataColumn dc in colPkName)
            {
                if (strWhereSql == "")
                    strWhereSql = " where " + dc.ColumnName + @" = @Original_" + dc.ColumnName;
                else
                    strWhereSql += " and " + dc.ColumnName + @" = @Original_" + dc.ColumnName;
            }
            System.Data.SqlClient.SqlCommand sqlInsertComm = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand sqlUpdateComm = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand sqlDeleteComm = new System.Data.SqlClient.SqlCommand();

            //动态生成映射列
            System.Data.Common.DataTableMapping dTableMap = new System.Data.Common.DataTableMapping("Table", strTableName);

            foreach (DataColumn dcField in dtStru.Columns)
            {
                bool bnlIsExistCol = false;
                dTableMap.ColumnMappings.Add(dcField.ColumnName, dcField.ColumnName);
                //检查在表结构中是否存在
                foreach (string aStr in arlCol)
                {
                    if (aStr.Equals(dcField.ColumnName))
                    {
                        bnlIsExistCol = true;
                    }
                }
                //如果不是自动增长的字段

                if (!dcField.AutoIncrement && bnlIsExistCol)
                {

                    if (strInsertField == "")
                    {
                        strInsertField = dcField.ColumnName;
                        strInsertValue = "@" + dcField.ColumnName;
                        strUpdate = dcField.ColumnName + "=@" + dcField.ColumnName;
                    }
                    else
                    {
                        strInsertField += "," + dcField.ColumnName;
                        strInsertValue += ",@" + dcField.ColumnName;
                        strUpdate += "," + dcField.ColumnName + "=@" + dcField.ColumnName;
                    }
                }
            }
            strInsertSql = @"INSERT INTO " + strTableName + " (" + strInsertField + ") values(" + strInsertValue + ")";
            strUpdateSql = @"update " + strTableName + " set " + strUpdate + strWhereSql;
            strDeleteSql = @"delete from " + strTableName + strWhereSql;
            adapter.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { dTableMap });
            // 
            // Adapter
            // 

            adapter.DeleteCommand = sqlDeleteComm;
            adapter.InsertCommand = sqlInsertComm;
            adapter.UpdateCommand = sqlUpdateComm;
            // 
            // sqlInsertComm
            // 
            //动态生成插入语句

            sqlInsertComm.CommandText = strInsertSql;
            sqlInsertComm.Connection = connection;
            BuildPara(sqlInsertComm, dtStru);
            // 
            // sqlUpdateComm
            // 
            sqlUpdateComm.CommandText = strUpdateSql;
            sqlUpdateComm.Connection = connection;
            BuildPara(sqlUpdateComm, dtStru);
            BuildWherePara(sqlUpdateComm, dtStru);
            // 
            // sqlDeleteComm
            // 
            sqlDeleteComm.CommandText = strDeleteSql;
            sqlDeleteComm.Connection = connection;
            BuildWherePara(sqlDeleteComm, dtStru);


        }

        private static void BuildPara(System.Data.SqlClient.SqlCommand mSqlommand, DataTable dt)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                //如果不是自动增长的字段， 创建参数
                if (!dc.AutoIncrement)
                {

                    switch (dc.DataType.Name.ToString())
                    {
                        case "String":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.VarChar, 0, dc.ColumnName));
                            break;
                        case "Int16":
                        case "Int32":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.Int, 0, dc.ColumnName));
                            break;
                        case "Int64":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.BigInt, 0, dc.ColumnName));
                            break;
                        case "Decimal":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.Decimal, 0, dc.ColumnName));
                            break;
                        case "Boolean":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.Bit, 0, dc.ColumnName));
                            break;

                        case "DateTime":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.DateTime, 0, dc.ColumnName));
                            break;
                        case "VarBinary":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.VarBinary, 2147483647, dc.ColumnName));
                            break;
                        case "Byte[]":
                            mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@" + dc.ColumnName, System.Data.SqlDbType.VarBinary, 2147483647, dc.ColumnName));
                            break;

                    }
                }
            }
        }

        private static void BuildWherePara(System.Data.SqlClient.SqlCommand mSqlommand, DataTable dt)
        {
            foreach (DataColumn dc in dt.PrimaryKey)
            {
                switch (dc.DataType.Name.ToString())
                {
                    case "String":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.VarChar, 0, dc.ColumnName));
                        break;
                    case "Int16":
                    case "Int32":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.Int, 0, dc.ColumnName));
                        break;
                    case "Int64":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.BigInt, 0, dc.ColumnName));
                        break;
                    case "Decimal":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.Decimal, 0, dc.ColumnName));
                        break;
                    case "Boolean":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.Bit, 0, dc.ColumnName));
                        break;
                    case "DateTime":
                        mSqlommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_" + dc.ColumnName, System.Data.SqlDbType.DateTime, 0, dc.ColumnName));
                        break;
                }
            }

        }



        #endregion

        /// <summary>
        /// 根据记录集更新数据        
        /// </summary>
        /// <param name="data">用户进行增加删除修改操作的记录集</param>
        /// <returns></returns>
        public static string UpdateData(DataSet data)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string blnResult = "";
                int intTableNum = data.Tables.Count;
                System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];

                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i], adapter[i],connection);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {
                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added));
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));

                    }

                    myTrans.Commit();

                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult = e.Message;
                    throw e;
                }
                return blnResult;
            }
        }

        /// <summary>
        /// 根据记录集更新数据
        /// 参数记录集data， 与SQlServer表结构不一致时使用（--待更新表的字段关联了一些其他表的字段）
        /// 王利民
        /// </summary>
        /// <param name="data">用户进行增加删除修改操作的记录集</param>
        /// <returns></returns>
        public static string UpdateDataCheckField(DataSet data)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string blnResult = "";
                int intTableNum = data.Tables.Count;
                System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        ArrayList arlCol = getcolumn(data.Tables[i].TableName, myTrans);
                        System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i],connection, adapter[i], arlCol);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {
                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added));
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));

                    }

                    myTrans.Commit();

                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult = e.Message;
                    throw e;
                }
                return blnResult;
            }
        }

        /// <summary>
        /// 根据多记录集更新数据
        /// 参数记录集data， 与SQlServer表结构不一致时使用（--待更新表的字段关联了一些其他表的字段）
        /// 王兴华
        /// </summary>
        /// <param name="data">用户进行增加删除修改操作的记录集 List<DataSet> </param>
        /// <returns></returns>
        public static string UpdateDataCheckField(List<DataSet> list)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string blnResult = "";
                if (list == null) return blnResult;
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int j = 0; j < list.Count; j++)
                    {
                        DataSet data = list[j];
                        int intTableNum = data.Tables.Count;
                        System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];
                        for (int i = 0; i < intTableNum; i++)
                        {
                            ArrayList arlCol = getcolumn(data.Tables[i].TableName, myTrans);
                            System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                            adapter[i] = adapterTmp;
                            init(data.Tables[i],connection, adapter[i], arlCol);
                            adapter[i].UpdateCommand.Transaction = myTrans;
                            adapter[i].DeleteCommand.Transaction = myTrans;
                            adapter[i].InsertCommand.Transaction = myTrans;
                        }
                        for (int i = 0; i < intTableNum; i++)
                        {
                            if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                            if (data.GetChanges(DataRowState.Added) != null) adapter[i].Update(data.GetChanges(DataRowState.Added));
                            if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));
                        }
                    }
                    myTrans.Commit();
                }
                catch (Exception e)
                {                    
                    myTrans.Rollback();
                    blnResult = e.Message;
                }
                return blnResult;
            }
        }

        public string SaveLargeData(DataTable SaveDT)
        {
            try
            {
                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectConfig.DBConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                sqlBulkCopy.DestinationTableName = SaveDT.TableName;
                sqlBulkCopy.WriteToServer(SaveDT);
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 更新海量数据,先删除，再插入.
        /// </summary>
        /// <returns></returns>
        public static void UpdateLargeData(string deleteSql, DataTable SaveDT)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    SqlTransaction myTrans = connection.BeginTransaction();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = deleteSql;
                    cmd.Transaction = myTrans;
                    cmd.ExecuteNonQuery();
                    SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, myTrans);
                    sqlBulkCopy.DestinationTableName = SaveDT.TableName;
                    sqlBulkCopy.WriteToServer(SaveDT);
                    myTrans.Commit();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 更改数据集，同时更新basOrder
        /// </summary>
        /// <param name="data">待修改的记录集</param>
        /// <param name="strFieldKey">记录主键的分类标识</param>
        /// <returns></returns>
        public static string UpdateData(DataSet data, string strFieldKey)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string blnResult = "";
                int intTableNum = data.Tables.Count;
                DataSet dsTemp = data.GetChanges(DataRowState.Added);
                System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i], adapter[i],connection);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {

                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null)
                        {
                            if (i == 0 && data.Tables[0].GetChanges(DataRowState.Added) != null)
                            {

                                //获取主表主键
                                DataColumn[] colPkName = data.Tables[0].PrimaryKey;
                                if (!colPkName[0].AutoIncrement)
                                {
                                    //获取、处理记录主键的表
                                    System.Data.SqlClient.SqlDataAdapter adapterSelect = new SqlDataAdapter();


                                    SqlCommand command = new SqlCommand("SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    adapterSelect.SelectCommand = command;
                                    adapterSelect.SelectCommand.Transaction = myTrans;
                                    DataSet dsOrder = new DataSet();
                                    adapterSelect.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                                    string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                                    if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                                    {
                                        strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                                    }
                                    else
                                    {
                                        if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                                        {
                                            int intNum = int.Parse(strKeyNum.Substring(10, 6));
                                            intNum = 1000001 + intNum;

                                            strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);

                                        }
                                        else
                                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";

                                    }
                                    //更新主键值
                                    command = command = new SqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    command.Transaction = myTrans;
                                    command.ExecuteNonQuery();


                                    //更改记录集的主键
                                    for (int iTable = 0; iTable < data.Tables.Count; iTable++)
                                    {
                                        foreach (DataRow dr in dsTemp.Tables[iTable].Rows)
                                        {
                                            dr[colPkName[0].ToString()] = strKeyNum;
                                        }
                                    }
                                }
                            }
                            adapter[i].Update(dsTemp);

                        }
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));

                    }

                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    if (((System.Data.SqlClient.SqlException)(e)).Number.ToString() == "547") blnResult = "数据已经被使用，无法删除！";
                    else blnResult = e.Message;
                }
                return blnResult;
            }
        }

        /// <summary>
        /// 更改数据集，同时更新basOrder
        /// </summary>
        /// <param name="data">待修改的记录集</param>
        /// <param name="strFieldKey">记录主键的分类标识</param>
        /// <returns>ID</returns>
        public static string[] UpdateDataReturnID(DataSet data, string strFieldKey)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string[] blnResult = new string[2];
                blnResult[0] = "";
                blnResult[1] = "";
                int intTableNum = data.Tables.Count;
                DataSet dsTemp = data.GetChanges(DataRowState.Added);
                System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];                
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i], adapter[i],connection);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {

                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null)
                        {
                            if (i == 0 && data.Tables[0].GetChanges(DataRowState.Added) != null)
                            {

                                //获取主表主键
                                DataColumn[] colPkName = data.Tables[0].PrimaryKey;
                                if (!colPkName[0].AutoIncrement)
                                {
                                    //获取、处理记录主键的表
                                    System.Data.SqlClient.SqlDataAdapter adapterSelect = new SqlDataAdapter();


                                    SqlCommand command = new SqlCommand("SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    adapterSelect.SelectCommand = command;
                                    adapterSelect.SelectCommand.Transaction = myTrans;
                                    DataSet dsOrder = new DataSet();
                                    adapterSelect.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                                    string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                                    if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                                    {
                                        strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                                    }
                                    else
                                    {
                                        if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                                        {
                                            int intNum = int.Parse(strKeyNum.Substring(10, 6));
                                            intNum = 1000001 + intNum;

                                            strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);

                                        }
                                        else
                                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";

                                    }
                                    //更新主键值
                                    command = command = new SqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    command.Transaction = myTrans;
                                    command.ExecuteNonQuery();


                                    //更改记录集的主键
                                    for (int iTable = 0; iTable < data.Tables.Count; iTable++)
                                    {
                                        foreach (DataRow dr in dsTemp.Tables[iTable].Rows)
                                        {
                                            dr[colPkName[0].ToString()] = strKeyNum;
                                        }
                                    }
                                    blnResult[1] = strKeyNum;
                                }
                            }
                            adapter[i].Update(dsTemp);

                        }
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));

                    }

                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult[0] = e.Message;
                    throw e;
                }
                return blnResult;
            }
        }

        /// <summary>
        /// 更改数据集，同时更新basOrder---验证字段
        /// </summary>
        /// <param name="data">待修改的记录集</param>
        /// <param name="strFieldKey">记录主键的分类标识</param>
        /// <returns>ID</returns>
        public static string[] UpdateDataReturnIDCheckField(DataSet data, string strFieldKey)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string[] blnResult = new string[2];
                blnResult[0] = "";
                blnResult[1] = "";
                int intTableNum = data.Tables.Count;
                DataSet dsTemp = data.GetChanges(DataRowState.Added);
                System.Data.SqlClient.SqlDataAdapter[] adapter = new System.Data.SqlClient.SqlDataAdapter[intTableNum];
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    for (int i = 0; i < intTableNum; i++)
                    {
                        ArrayList arlCol = getcolumn(data.Tables[i].TableName, myTrans);
                        System.Data.SqlClient.SqlDataAdapter adapterTmp = new SqlDataAdapter();
                        adapter[i] = adapterTmp;
                        init(data.Tables[i],connection, adapter[i], arlCol);
                        adapter[i].UpdateCommand.Transaction = myTrans;
                        adapter[i].DeleteCommand.Transaction = myTrans;
                        adapter[i].InsertCommand.Transaction = myTrans;
                    }
                    for (int i = 0; i < intTableNum; i++)
                    {

                        if (data.GetChanges(DataRowState.Deleted) != null) adapter[intTableNum - i - 1].Update(data.GetChanges(DataRowState.Deleted));
                        if (data.GetChanges(DataRowState.Added) != null)
                        {
                            if (i == 0 && data.Tables[0].GetChanges(DataRowState.Added) != null)
                            {

                                //获取主表主键
                                DataColumn[] colPkName = data.Tables[0].PrimaryKey;
                                if (!colPkName[0].AutoIncrement)
                                {
                                    //获取、处理记录主键的表
                                    System.Data.SqlClient.SqlDataAdapter adapterSelect = new SqlDataAdapter();


                                    SqlCommand command = new SqlCommand("SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    adapterSelect.SelectCommand = command;
                                    adapterSelect.SelectCommand.Transaction = myTrans;
                                    DataSet dsOrder = new DataSet();
                                    adapterSelect.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                                    string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                                    if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                                    {
                                        strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                                    }
                                    else
                                    {
                                        if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                                        {
                                            int intNum = int.Parse(strKeyNum.Substring(10, 6));
                                            intNum = 1000001 + intNum;

                                            strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);

                                        }
                                        else
                                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";

                                    }
                                    //更新主键值
                                    command = command = new SqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                                    command.Transaction = myTrans;
                                    command.ExecuteNonQuery();


                                    //更改记录集的主键
                                    for (int iTable = 0; iTable < data.Tables.Count; iTable++)
                                    {
                                        foreach (DataRow dr in dsTemp.Tables[iTable].Rows)
                                        {
                                            dr[colPkName[0].ToString()] = strKeyNum;
                                        }
                                    }
                                    blnResult[1] = strKeyNum;
                                }
                            }
                            adapter[i].Update(dsTemp);

                        }
                        if (data.GetChanges(DataRowState.Modified) != null) adapter[i].Update(data.GetChanges(DataRowState.Modified));

                    }

                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult[0] = ((System.Data.SqlClient.SqlException)(e)).Number.ToString()
    ;
                    //throw e;
                }
                return blnResult;
            }
        }


        /// <summary>
        /// 根据SQLString 获取记录集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="strTabname"></param>
        /// <returns></returns>
        public static DataSet getTableData(string commandText, string strTabname)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                DataSet data = new DataSet();
                System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
                try
                {
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    SqlCommand command = new SqlCommand(commandText, connection);
                    adapter.SelectCommand = command;
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    if (strTabname != "")
                        adapter.Fill(data, strTabname);
                    else
                        adapter.Fill(data);
                }
                catch (System.Exception e)
                {
                    throw e;
                }

                return data;
            }
        }

        /// <summary>
        /// 根据SQLString 获取记录集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="strTabname"></param>
        /// <returns></returns>
        public static DataSet getTableDataWithOutKey(string commandText, string strTabname)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                DataSet data = new DataSet();
                System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
                try
                {

                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    SqlCommand command = new SqlCommand(commandText, connection);
                    adapter.SelectCommand = command;
                    adapter.MissingSchemaAction = MissingSchemaAction.Add;
                    if (strTabname != "")
                        adapter.Fill(data, strTabname);
                    else
                        adapter.Fill(data);
                }
                catch (System.Exception e)
                {
                    throw e;
                }
                return data;
            }
        }

        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决多表问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public DataSet getTableData(Hashtable hstSqlTable)
        {
            ArrayList arList = new ArrayList();
            arList.Add(hstSqlTable);
            return getTableData(arList);
        }

        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决排序问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public static DataSet getTableData(ArrayList hstSQlTabel)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                DataSet data = new DataSet();
                System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
                SqlTransaction myTrans = connection.BeginTransaction();
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                try
                {
                    for (int i = 0; i < hstSQlTabel.Count; i++)
                    {
                        Hashtable hst = (Hashtable)hstSQlTabel[i];
                        foreach (DictionaryEntry hstSql in hst)
                        {
                            DataSet ds = new DataSet();
                            SqlCommand command = new SqlCommand(hstSql.Value.ToString(), connection);
                            adapter.SelectCommand = command;
                            adapter.SelectCommand.Transaction = myTrans;

                            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                            if (hstSql.Key.ToString() != "")
                                adapter.Fill(ds, hstSql.Key.ToString());
                            else
                                adapter.Fill(ds);
                            data.Merge(ds);
                        }
                    }
                    myTrans.Commit();
                }
                catch (System.Exception e)
                {
                    myTrans.Rollback();
                    throw e;
                }
                return data;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string strTabname)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet data = new DataSet();
                try
                {
                    adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    if (strTabname != "")
                        adapter.Fill(data, strTabname);
                    else
                        adapter.Fill(data);
                }
                catch (System.Exception e)
                {
                    throw e;
                }
                return data;
            }

        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        public static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

        /// <summary>
        /// 执行单条记录的插入、修改
        /// 王利民 
        /// 2011-04-01
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="hSQlFieldList">字段列表</param>
        /// <param name="hSQLwhereList">条件字段列表</param>
        /// <param name="strOpt"></param>
        public static string ExecuteSqlString(string strTableName, Hashtable hSQlFieldList, Hashtable hSQLwhereList, string strOpt)
        {
            string strReturn = "";
            string strSql = "";
            string strFields = "";
            string strValue = "";
            if (strOpt == "INSERT")
            {
                foreach (DictionaryEntry myDE in hSQlFieldList)
                {
                    if (strFields == "")
                    {
                        strFields = " (" + myDE.Key.ToString();
                        strValue = " ('" + myDE.Value.ToString() + "'";

                    }
                    else
                    {
                        strFields += " ," + myDE.Key.ToString();
                        strValue += " ,'" + myDE.Value.ToString() + "'";

                    }
                }

                strSql = "insert into " + strTableName + strFields + strValue + ")";
            }
            else
            {
                foreach (DictionaryEntry myDE in hSQlFieldList)
                {
                    if (strFields == "")
                    {
                        strFields = myDE.Key.ToString() + "='" + myDE.Value.ToString() + "'";

                    }
                    else
                    {
                        strFields += "," + myDE.Key.ToString() + "='" + myDE.Value.ToString() + "'";

                    }
                }

                foreach (DictionaryEntry myDE in hSQLwhereList)
                {
                    if (strFields == "")
                    {
                        strValue = myDE.Key.ToString() + "='" + myDE.Value.ToString() + "'";

                    }
                    else
                    {
                        strValue += " and " + myDE.Key.ToString() + "='" + myDE.Value.ToString() + "'";

                    }
                }

                strSql = "update " + strTableName + " Set " + strFields + strValue + ")";

            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {                
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand();
                    command.CommandText = strSql;
                    command.Connection = connection;
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    strReturn = e.Message;
                    throw e;
                }
                return strReturn;
            }

        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数

        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }

        }
        /// <summary>
        /// 执行一条或多条SQL语句
        /// </summary>
        /// <param name="arlSQl"></param>
        /// <returns></returns>
        public static string ExecutesqlTrans(ArrayList arlSQl)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string strReturn = "";
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    //循环
                    foreach (string aStr in arlSQl)
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandText = aStr;
                        command.Connection = connection;
                        command.Transaction = myTrans;
                        command.ExecuteNonQuery();
                    }
                    myTrans.Commit();
                }

                catch (Exception ex)
                {
                    myTrans.Rollback();
                    strReturn = ex.Message;
                    throw ex;
                }
                return strReturn;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。

        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, connection, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch(Exception ex)
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }


        public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 更新当前主键编号，获取主键编号
        /// </summary>
        /// <param name="strFieldKey">主键标识--两位</param>
        /// <returns>Return[0]：成功与否；Return[1]：返回主键</returns>
        public static string[] getOrderNO(string strFieldKey)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string[] blnResult = new string[2];
                blnResult[0] = "";
                blnResult[1] = "";
                System.Data.SqlClient.SqlDataAdapter adapterSelect = new SqlDataAdapter();
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'", connection);
                    adapterSelect.SelectCommand = command;
                    adapterSelect.SelectCommand.Transaction = myTrans;
                    DataSet dsOrder = new DataSet();
                    adapterSelect.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                    string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                    if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                    {
                        strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                    }
                    else
                    {
                        if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                        {
                            int intNum = int.Parse(strKeyNum.Substring(10, 6));
                            intNum = 1000001 + intNum;

                            strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);

                        }
                        else
                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";

                    }
                    //更新主键值
                    command = command = new SqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                    command.Transaction = myTrans;
                    command.ExecuteNonQuery();
                    blnResult[1] = strKeyNum;
                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult[0] = e.Message;
                    throw e;
                }
                return blnResult;
            }

        }

        public static string[] getOrderNO(string strFieldKey, int OrderNum)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string[] blnResult = new string[2];
                blnResult[0] = "";
                blnResult[1] = "";
                System.Data.SqlClient.SqlDataAdapter adapterSelect = new SqlDataAdapter();
                SqlTransaction myTrans = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("SELECT KeyGroup,KeyNumber FROM PriOrders where  KeyGroup  = '" + strFieldKey + "'", connection);
                    adapterSelect.SelectCommand = command;
                    adapterSelect.SelectCommand.Transaction = myTrans;
                    DataSet dsOrder = new DataSet();
                    adapterSelect.Fill(dsOrder, basOrderData.BASORDER_TABLE);
                    string strKeyNum = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                    string strKeyNumB = dsOrder.Tables[basOrderData.BASORDER_TABLE].Rows[0][basOrderData.START_NUMBER_FIELD].ToString();
                    if (strKeyNum == DBNull.Value.ToString() || strKeyNum == "")
                    {
                        int intNum = 1000001 + OrderNum;
                        strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6);
                        strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                    }
                    else
                    {
                        if (DateTime.Today.ToString("yyyyMMdd") == strKeyNum.Substring(2, 8))
                        {
                            int intNum = int.Parse(strKeyNum.Substring(10, 6));
                            intNum = 1000001 + intNum + OrderNum;
                            int intNumB = 1000001 + intNum;
                            strKeyNum = strKeyNum.Substring(0, 10) + intNum.ToString().Substring(1, 6);
                            strKeyNumB = strKeyNum.Substring(0, 10) + intNumB.ToString().Substring(1, 6);
                        }
                        else
                        {
                            int intNum = 1000001 + OrderNum;
                            strKeyNum = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + intNum.ToString().Substring(1, 6);
                            strKeyNumB = strFieldKey + DateTime.Today.ToString("yyyyMMdd") + "000001";
                        }
                    }
                    //更新主键值
                    command = command = new SqlCommand("update PriOrders set KeyNumber = '" + strKeyNum + "' where  KeyGroup  = '" + strFieldKey + "'", connection);
                    command.Transaction = myTrans;
                    command.ExecuteNonQuery();
                    blnResult[1] = strKeyNumB;
                    myTrans.Commit();
                }
                catch (Exception e)
                {
                    myTrans.Rollback();
                    blnResult[0] = e.Message;
                    throw e;
                }
                return blnResult;
            }
        }


        public static string GeyPrimaryKey(string sTableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string cmdText = "SELECT a.name FROM sys.syscolumns AS a INNER JOIN sys.sysobjects AS b ON a.id = b.parent_obj INNER JOIN sys.sysindexes AS c ON a.id = c.id AND b.name = c.name INNER JOIN sys.sysindexkeys AS d ON a.id = d.id AND c.indid = d.indid AND a.colid = d.colid WHERE (a.id = OBJECT_ID('" + sTableName + "')) AND (b.xtype = 'PK')";
                connection.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, cmdText, null);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return "";
                        }
                        else
                        {
                            return (string)obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }

                }
            }
        }

        /// <summary>
        /// 海量数据插入，主键为自增
        /// </summary>
        /// <param name="SaveDT">数据表，表名称必须与数据库表名称相同</param>
        /// <returns></returns>
        public static string SaveHugeData(DataTable SaveDT)
        {
            try
            {
                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectConfig.DBConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                sqlBulkCopy.DestinationTableName = SaveDT.TableName;
                sqlBulkCopy.WriteToServer(SaveDT);
                return "";
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        #region 数据库访问
        /// <summary>
        /// 根据SQLString 获取记录集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="strTabname"></param>
        /// <returns></returns>
        public static DataSet getTableData(string commandText, string strTabname, string connStr)
        {
            DataSet data = new DataSet();
            System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection Conn = new SqlConnection();
            try
            {
                Conn.ConnectionString = connStr;
                Conn.Open();
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                SqlCommand command = new SqlCommand(commandText, Conn);
                adapter.SelectCommand = command;
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                if (strTabname != "")
                    adapter.Fill(data, strTabname);
                else
                    adapter.Fill(data);
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                adapter.Dispose();
                adapter = null;
                Conn.Close();
                Conn.Dispose();
                Conn = null;
            }
            return data;
        }       

        public static int SqlExec(string SqlStr)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    System.Data.SqlClient.SqlCommand SqlCmd = connection.CreateCommand();
                    SqlCmd.CommandText = SqlStr;
                    return SqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        #endregion


        #region wd add
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        public static DataSet Query(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        /// <summary>
        /// 根据SQLString 获取记录集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="strTabname"></param>
        /// <returns></returns>
        public static DataSet Query(string commandText, string strTabname)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, commandText, null);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        if (strTabname != "")
                            da.Fill(ds, strTabname);
                        else
                            da.Fill(ds);
                    }
                    catch (System.Exception e)
                    {
                        throw e;
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决排序问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public static DataSet Query(ArrayList hstSQlTabel)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    DataSet data = new DataSet();
                    SqlTransaction myTrans = connection.BeginTransaction();
                    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    try
                    {
                        for (int i = 0; i < hstSQlTabel.Count; i++)
                        {
                            Hashtable hst = (Hashtable)hstSQlTabel[i];
                            foreach (DictionaryEntry hstSql in hst)
                            {
                                DataSet ds = new DataSet();
                                SqlCommand cmd = new SqlCommand();
                                PrepareCommand(cmd, connection, myTrans, hstSql.Value.ToString(), null);
                                da.SelectCommand = cmd;
                                if (hstSql.Key.ToString() != "")
                                    da.Fill(ds, hstSql.Key.ToString());
                                else
                                    da.Fill(ds);
                                data.Merge(ds);
                            }
                        }
                        myTrans.Commit();
                    }
                    catch (System.Exception e)
                    {
                        myTrans.Rollback();
                        throw e;
                    }
                    return data;
                }
            }
        }
        /// <summary>
        /// 执行多条SQL
        /// 2011-04-11 解决多表问题
        /// </summary>
        /// <param name="hstSQlTabel">Key： TableName  Value： SQLs</param>
        /// <returns></returns>
        public static DataSet Query(Hashtable hstSqlTable)
        {
            ArrayList arList = new ArrayList();
            arList.Add(hstSqlTable);
            return Query(arList);
        }
        #endregion
    }