ADO.NET：(2) 执行命令

是ADO.NET有以下几种方法来执行一个命令：
(1) ExecuteNonQuery() -- 无结果返回。通常用于 UPDATE, INSERT 和 DELETE命令，可以返回有几行受到影响
(2) ExecuteReader() -- 返回结果 IDataReader。返回一个数据集，比如 SELECT * FROM ACCOUNT
(3) ExecuteScalar() -- 返回第一行第一列的数据。通常用于返回单一数据，比如 SELECT COUNT(*) FROM ACCOUNT
(4) ExecuteXmlReader() -- 返回XmlReader对象。只适用于SQL数据库。T-SQL支持对SELECT进行FOR XML扩展，其中包括三种扩展(FOR XML AUTO, FOR XML RAW, FOR XML EXPLICIT)，具体含义可以参看其他参考资料。

下面来分别看一下以上几个方法的使用例子：

            string select = "INSERT INTO [TestDatabase].[dbo].[Account]" +
                            "([accountid]" +
                            ",[name])" +
                            "VALUES" +
                            "(newid()" +
                            ",'microsoft')";
            SqlCommand cmd = new SqlCommand(select, conn);
            int rowsReturned = cmd.ExecuteNonQuery();

            string select = "SELECT [accountid]" +
                            ",[name]" +
                            "FROM [TestDatabase].[dbo].[Account]";
            SqlCommand cmd = new SqlCommand(select, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[1]);
            }

            string select = "SELECT COUNT(*) FROM [TestDatabase].[dbo].[Account]";
            SqlCommand cmd = new SqlCommand(select, conn);
            object o = cmd.ExecuteScalar();
            Console.WriteLine(o);

            string select = "SELECT [accountid]" +
                                ",[name]" +
                                "FROM [TestDatabase].[dbo].[Account] FOR XML AUTO";
            SqlCommand cmd = new SqlCommand(select, conn);
            XmlReader xr = cmd.ExecuteXmlReader();
            xr.Read();
            string data;
            do
            {
                data = xr.ReadOuterXml();
                if (!string.IsNullOrEmpty(data))
                    Console.WriteLine(data);
            } while (!string.IsNullOrEmpty(data));

下面再看几个对存储过程调用的例子。下面的两个例子中，两个存储过程分别是一个简单的UPDATE和DELETE操作，所以我们可以使用ExecuteNonQuery：

CREATE PROCEDURE [dbo].[UpdateAccountWebsite] (@AccountName NCHAR(50), @Website NCHAR(50))
AS
BEGIN
	SET NOCOUNT ON;
    UPDATE Account Set website = @Website WHERE name = @AccountName
END

            SqlCommand cmd = new SqlCommand("UpdateAccountWebsite", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@AccountName", "apple");
            cmd.Parameters.AddWithValue("@Website", @"www.apple.com");
            cmd.ExecuteNonQuery();

CREATE PROCEDURE [dbo].[DeleteAccountByName](@AccountName NCHAR(50))
AS
BEGIN
	SET NOCOUNT ON;
    DELETE Account WHERE name = @AccountName
END

            SqlCommand cmd = new SqlCommand("DeleteAccountByName", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@AccountName", "apple");
            cmd.ExecuteNonQuery();

如果存储过程带有返回参数的话：

CREATE PROCEDURE [dbo].[InsertNewAccount](@AccountName NCHAR(50), @Website NCHAR(50), @AccountId Uniqueidentifier = null OUTPUT	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @NEWID Uniqueidentifier
	SET @NEWID = NEWID()
	SELECT @AccountId = @NEWID
	INSERT INTO [TestDatabase].[dbo].[Account]
           ([accountid]
           ,[name]
           ,[website])
     VALUES
           (@NEWID
           ,@AccountName
           ,@Website) 
END

            SqlCommand cmd = new SqlCommand("InsertNewAccount", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@AccountName", SqlDbType.NChar, 50));
            cmd.Parameters.Add(new SqlParameter("@Website", SqlDbType.NChar, 50));
            cmd.Parameters["@AccountName"].Value = "google";
            cmd.Parameters["@Website"].Value = "www.google.com";
            cmd.Parameters.Add(new SqlParameter("@AccountId", SqlDbType.UniqueIdentifier, 0, 
                ParameterDirection.Output, false, 0, 0, "RegionID", DataRowVersion.Default, null));

            
            cmd.ExecuteNonQuery();
            Guid newaccountid = (Guid)cmd.Parameters["@AccountId"].Value;

假如存储过程还要返回一组数据的话：

CREATE PROCEDURE [dbo].[InsertNewAccount2](@AccountName NCHAR(50), @Website NCHAR(50))	
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE @NEWID Uniqueidentifier
	SET @NEWID = NEWID()
	INSERT INTO [TestDatabase].[dbo].[Account]
           ([accountid]
           ,[name]
           ,[website])
     VALUES
           (@NEWID
           ,@AccountName
           ,@Website) 
    SELECT * FROM [TestDatabase].[dbo].[Account] WHERE accountid = @NEWID
END

            SqlCommand cmd = new SqlCommand("InsertNewAccount2", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@AccountName", SqlDbType.NChar, 50));
            cmd.Parameters.Add(new SqlParameter("@Website", SqlDbType.NChar, 50));
            cmd.Parameters["@AccountName"].Value = "google";
            cmd.Parameters["@Website"].Value = "www.google.com";

            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                reader.GetGuid(0);
                Console.WriteLine(reader[0]);
            }