ADO.Net中通过HashTable对SqlParameter赋值的三种方法和返回DataTable的两种方式

      一、得到数据库连接

        /// <summary>
        /// 得到数据连接
        /// </summary>
        /// <returns>返回连接字符串</returns>

        private string getConnectionString()
        {
            string strConn = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
            return strConn;
        }
      二、执行操作

       /// <summary>
        /// 执行sqlCommand
        /// </summary>
        /// <param name="sqlCommand">待执行的sql语句或存储过程名</param>
        /// <param name="htParameters">参数集</param>
        /// <returns>数据集</returns>

        public DataTable ExecuteSqlCommand(string sqlCommand,Hashtable htParameters)
        {
            string strConn = this.getConnectionString();

            SqlConnection sqlConn = new SqlConnection(strConn);

            SqlTransaction sqlTran = sqlConn.BeginTransaction();

            DataTable dtReturn = new DataTable();

            try
            {
                if (sqlConn.State == ConnectionState.Closed)
                {
                    sqlConn.Open();
                }

                SqlCommand sqlCom = sqlConn.CreateCommand();

                sqlCom.CommandType = CommandType.Text;

                sqlCom.CommandText = sqlCommand;

                /*
                //当sqlCommand为存储过程名时，应为如下所示

                sqlCom.CommandType = CommandType.StoredProcedure;

                sqlCom.CommandText = sqlCommand;
                */
 
                /*
                 //以下为通过Hashtable对sqlcommand赋参的三种方法
                */
                //

                //
                //方法一、通过IDictionaryEnumerator接口赋值
                //

                IDictionaryEnumerator iDictionary = htParameters.GetEnumerator();

                while (iDictionary.MoveNext())
                {
                   object objKey = iDictionary.Key;

                    sqlCom.Parameters.Add(objKey.ToString());

                   sqlCom.Parameters[objKey.ToString()].Value = iDictionary.Value;
                }

                //
                //方法二、通过OBJECT赋值
                //
                foreach (object objKeys in htParameters.Keys)
                {
                    sqlCom.Parameters.Add(objKeys);

                    sqlCom.Parameters[objKeys.ToString()].Value = htParameters[objKeys];
               }
                //
                //方法三、通过DictionaryEntry赋值
                //
                foreach (DictionaryEntry dEntry in htParameters)
                {
                    sqlCom.Parameters.Add(dEntry.Key);

                    sqlCom.Parameters[dEntry.Key.ToString()].Value = dEntry.Value;
                }

                /*
                 //以下为返回结果数据集以DataTable返回的两种方法
                 //
                 //DataReader数据不在内存中缓存，适合于检索大量数据
                 //
                */
                //
                //方法一、以SqlDataReader的形式返回DataTable数据集
                //

                SqlDataReader sqlReader = sqlCom.ExecuteReader();

                if (sqlReader.HasRows)
                {
                    dtReturn = sqlReader.GetSchemaTable();
                }

                //
                //方法二、以DataSet的形式返回DataTable数据集
                //

                SqlDataAdapter sqlData = new SqlDataAdapter(sqlCom);

                DataSet dtSet = new DataSet();

                sqlData.Fill(dtSet);

                dtReturn = dtSet.Tables[0];

            }
            catch (Exception ce)
            {
                sqlTran.Rollback();
                throw new ApplicationException(ce.Message.ToString());
            }
            finally
            {
                sqlTran.Commit();

                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }

            return dtReturn;
        }