C# CommandType类代码示例

        private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter, CommandType cmdType)
        {
            IDbDataParameter clone = new OracleParameter() { IsNullable = dbParameter.IsNullable };

            // Remove leading ':' character for stored procedures.
            if (cmdType == CommandType.StoredProcedure)
            {
                string name = parameterRenderer.RenderParameterName(dbParameter);
                if (name.StartsWith(":", StringComparison.Ordinal))
                    name = name.Substring(1, name.Length - 1);

                clone.ParameterName = name;
            }
            else
            {
                clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
            }

            if (dbParameter.PassMode == SpArgumentPassMode.DataTableFilledByAdapter)
                ((OracleParameter)clone).OracleDbType = OracleDbType.RefCursor;
            else
                clone.DbType = dbParameter.DbType;

            clone.Direction = dbParameter.Direction;
            clone.Precision = dbParameter.Precision;
            clone.Scale = dbParameter.Scale;
            clone.Size = dbParameter.Size;
            clone.SourceColumn = dbParameter.SourceColumn;
            clone.SourceVersion = dbParameter.SourceVersion;
            clone.Value = dbParameter.Value;

            return clone;
        }

        public static void ExecuteNonQuery( CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = cmdType;
                cmd.CommandText = strProcedureName;

                AttachParameters(cmd, objParameters);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

            }
            finally
            {

                con.Close();
            }
        }

        // This function will be used to execute R(CRUD) operation of parameterized commands
        internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
        {
            DataTable table = new DataTable();

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(param);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return table;
        }

        public static DataSet ExecuteDataSet(CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
        {
            DataSet dset = new DataSet();
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = cmdType;
                cmd.CommandText = strProcedureName;

                AttachParameters(cmd, objParameters);

                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dset);

                return dset;

            }
            catch (Exception ex)
            {
                return dset;
            }
            finally
            {
                con.Close();
            }
        }

        // This function will be used to execute CUD(CRUD) operation of parameterized commands
        internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
        {
            int result = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
            return (result > 0);
        }

        public static SqlCommand GenerateCommand(SqlConnection Connection, MethodInfo Method, object[] Values, CommandType SQLCommandType, string SQLCommandText)
        {
            if (Method == null)
            {
                Method = (MethodInfo)new StackTrace().GetFrame(1).GetMethod();
            }

            SqlCommand command = new SqlCommand();
            command.Connection = Connection;
            command.CommandType = SQLCommandType;

            if (SQLCommandText.Length == 0)
            {
                command.CommandText = Method.Name;
            }
            else
            {
                command.CommandText = SQLCommandText;
            }

            if (command.CommandType == CommandType.StoredProcedure)
            {
                GenerateCommandParameters(command, Method, Values);

                command.Parameters.Add(ReturnValueParameterName, SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            }

            return command;
        }

        public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
        {
            SqlCommand cmd = null;
            int res = 0;

            cmd = con.CreateCommand();

            cmd.CommandType = cmdType;
            cmd.CommandText = CommandName;
            cmd.Parameters.AddRange(pars);

            try
            {
                con.Open();

                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                con.Close();
            }

            if (res >= 1)
            {
                return true;
            }
            return false;
        }

 /// <summary>
 /// 查询操作方法
 /// </summary>
 /// <param name="sql">执行的sql语句</param>
 /// <param name="cmdType">sql语句类型</param>
 /// <param name="paras">传入的参数</param>
 /// <returns>返回执行结果的首行首列</returns>
 public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] paras)
 {
     using (SqlConnection conn = new SqlConnection(serverPath))
     {
         using (SqlCommand cmd = new SqlCommand(sql, conn))
         {
             if (paras != null)
             {
                 cmd.Parameters.AddRange(paras);
             }
             cmd.CommandType = cmdType;
             try
             {
                 conn.Open();
                 return cmd.ExecuteScalar();
             }
             catch (Exception ex)
             {
                 conn.Close();
                 conn.Dispose();
                 throw ex;
             }
         }
     }
 }

 public static DataTable ExecuteData(CommandType cmdCommandType, string cmdCommandString, params SqlParameter[] cmdParameters)
 {
     SqlCommand cmdCommand = new SqlCommand();
     SqlConnection connect = new SqlConnection(ConnectionString.Text);
     try
     {
         DataTable dattTopic = new DataTable();
         SqlDataAdapter dataTopic = new SqlDataAdapter(cmdCommand);
         PrepareCommand(cmdCommand, connect, null, cmdCommandType, cmdCommandString, cmdParameters);
         cmdCommand.ExecuteNonQuery();
         dataTopic.Fill(dattTopic);
         cmdCommand.Parameters.Clear();
         if (connect.State == ConnectionState.Open) connect.Close();
         return dattTopic;
     }
     catch (SqlException ex)
     {
         if (connect.State == ConnectionState.Open)
         {
             connect.Close();
             SqlConnection.ClearPool(connect);
         }
         throw ex;
     }
 }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteAdapter(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A Dataset containing the results</returns>
        public static DataSet ExecuteAdapter(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    conn.Close();
                    //added by lk in 2010-05-21 begin
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (dc.DataType == Type.GetType("System.String"))
                                {
                                    // NULL -> ""; + TRIM()
                                    dr[dc] = dr[dc].ToString().Trim();
                                }
                                else if (dc.DataType == Type.GetType("System.DateTime"))
                                {
                                    if (Convert.IsDBNull(dr[dc]) || dr[dc].ToString().Equals(""))
                                    {
                                        dr[dc] = DateTime.MinValue;
                                    }
                                }
                                else if (dc.DataType == Type.GetType("System.Decimal") || dc.DataType == Type.GetType("System.Int32") || dc.DataType == Type.GetType("System.Int64"))
                                {
                                    if (Convert.IsDBNull(dr[dc]))
                                    {
                                        dr[dc] = 0;
                                    }
                                }

                            }
                        }
                    }
                    //added by lk in 2010-05-21 end
                    return ds;
                }
            }
            catch
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                throw;
            }
        }

 /// <summary>
 /// 1.带参数查询，返回bool，直接SQL语句
 /// </summary>
 /// <param name="sqlTypeString"></param>
 /// <param name="cmdType"></param>
 /// <param name="parameters"></param>
 /// <returns></returns>
 public static bool CheckIsExist(string sqlTypeString, CommandType cmdType, params SqlParameter[] parameters)
 {
     bool flag = false;
     SqlDataReader reader = null;
     try
     {
         using (SqlCommand cmd = new SqlCommand(sqlTypeString, Connection))
         {
             cmd.CommandType = cmdType;
             cmd.Parameters.AddRange(parameters);
             reader = cmd.ExecuteReader();
             if (reader.HasRows && reader.Read())
             {
                 flag = true;
             }
         }
     }
     catch (SqlException ex)
     {
         flag = false;
         throw ex;
     }
     finally
     {
         if (reader != null) reader.Close();
     }
     return flag;
 }

		private static void ExecuteCommand(CommandType command, DirectoryInfo baseDirectory, Lazy<Uri> url, string userName, string password) 
		{
			var engine = Engine.CreateStandard(baseDirectory);
			switch (command)
			{
				case CommandType.Help:
					break;
				case CommandType.Generate:
					var generatedDocuments = engine.Generate();
					foreach (var generatedDocument in generatedDocuments)
					{
						System.Console.WriteLine(generatedDocument);
						System.Console.WriteLine();
					}
					break;
				case CommandType.Check:
					var haveChanged =
						engine.CheckIfChanged(url.Value, userName, password);
					System.Console.WriteLine(haveChanged? "Changed": "Have not changed");
					break;
				case CommandType.Push:
					engine.PushIfChanged(url.Value, userName, password);
					break;
				case CommandType.Purge:
					engine.PurgeDatabase(url.Value, userName, password);
					break;
				default:
					throw new ArgumentOutOfRangeException();
			}
		}

        /// <summary>
        /// Gets a data reader.
        /// </summary>
        /// <param name="query">The query.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static IDataReader GetDataReader( string query, CommandType commandType, Dictionary<string, object> parameters )
        {
            string connectionString = GetConnectionString();
            if ( !string.IsNullOrWhiteSpace( connectionString ) )
            {
                SqlConnection con = new SqlConnection( connectionString );
                con.Open();

                SqlCommand sqlCommand = new SqlCommand( query, con );
                sqlCommand.CommandType = commandType;

                if ( parameters != null )
                {
                    foreach ( var parameter in parameters )
                    {
                        SqlParameter sqlParam = new SqlParameter();
                        sqlParam.ParameterName = parameter.Key.StartsWith( "@" ) ? parameter.Key : "@" + parameter.Key;
                        sqlParam.Value = parameter.Value;
                        sqlCommand.Parameters.Add( sqlParam );
                    }
                }

                return sqlCommand.ExecuteReader( CommandBehavior.CloseConnection );
            }

            return null;
        }

        /// <summary>
        ///     Constructs a SqlCommand with the given parameters. This method is normally called
        ///     from the other methods and not called directly. But here it is if you need access
        ///     to it.
        /// </summary>
        /// <param name="qry">SQL query or stored procedure name</param>
        /// <param name="type">Type of SQL command</param>
        /// <param name="args">
        ///     Query arguments. Arguments should be in pairs where one is the
        ///     name of the parameter and the second is the value. The very last argument can
        ///     optionally be a SqlParameter object for specifying a custom argument type
        /// </param>
        /// <returns></returns>
        public SqlCommand CreateCommand(string qry, CommandType type, params object[] args)
        {
            var cmd = new SqlCommand(qry, Conn);

            // Associate with current transaction, if any
            if (Trans != null)
                cmd.Transaction = Trans;

            // Set command type
            cmd.CommandType = type;

            // Construct SQL parameters
            for (var i = 0; i < args.Length; i++)
            {
                if (args[i] is string && i < (args.Length - 1))
                {
                    var parm = new SqlParameter();
                    parm.ParameterName = (string) args[i];
                    parm.Value = args[++i];
                    cmd.Parameters.Add(parm);
                }
                else if (args[i] is SqlParameter)
                {
                    cmd.Parameters.Add((SqlParameter) args[i]);
                }
                else throw new ArgumentException("Invalid number or type of arguments supplied");
            }
            return cmd;
        }

        public static DataTable Execute_DataTable(SqlParameter[] prm, string Query, string TableName, CommandType CmnType,SqlConnection con)
        {
            dt = new DataTable(TableName);
            cmn = new SqlCommand();
            cmn.CommandType = CmnType;
            cmn.CommandText = Query.Replace("@DbName", SCMDBNAME).Replace("@FrmNo", FRMNO).Replace("@DnmNo", DNMNO);
            cmn.Connection = con;
            foreach (SqlParameter item in prm)
            {
                cmn.Parameters.AddWithValue(item.ParameterName, item.Value);
            }
            try
            {

                adp = new SqlDataAdapter(cmn);
                adp.Fill(dt);
                cmn.Dispose();
                adp.Dispose();
            }
            catch (Exception ex)
            {

                cmn.Dispose();
                adp.Dispose();

            }
            return dt;
        }