匆匆忙忙的看了两三周的 C# 基础，就被拉上阵开始写程序了。
昨天遇到一个问题，是关于带 output 参数的存储过程(SQLServer)。我看的那本书里貌似没有讲数据库操作，关于数据库方面的应用，我现在只有问同事，查GOOGLE。

问题是这样的，我需要一个存储过程，这个存储过程即有 output 参数，也有查询结果。

 

存储过程：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RetailerProductsChangeRate]
    @Threshold INT = 10,
    @CurrDay VARCHAR(10) OUTPUT,/////////////////////////
    @PREDAY VARCHAR(10) OUTPUT//////////////////////////
AS
BEGIN
    DECLARE @CreatedOn VARCHAR(10);
    --DECLARE @CURRDAY VARCHAR(10);
    --DECLARE @PREDAY VARCHAR(10);

    /**
    CreatedOn 当前日期,向表插入数据时，使用，请不要修改
    **/
    SET @CreatedOn = SUBSTRING(CONVERT(VARCHAR,GETDATE(),20),1,10);
    /**
    CurrDay 两个比较日期的较大者
    PreDay  较小者
    格式：2009-04-09
    这两个参数可以修改
    **/
    SET @CurrDay = @CreatedOn;
    SET @PreDay = SUBSTRING(CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),20),1,10);
    /**
    从数据库里查出最后一次执行的日期，并赋值给  PreDay,如果不存在，就使用上面的值。
    **/
    SELECT DISTINCT TOP 1 @PreDay = CreatedOn FROM CSK_Store_Retailer_Change_Rate WHERE CreatedOn < @CurrDay ORDER BY CreatedOn DESC;

 

    DELETE FROM CSK_Store_Retailer_Change_Rate WHERE CreatedOn = @CreatedOn;
   
    //SQLServer 2008 才支持 MERGE ,感觉太失败了。
    INSERT INTO CSK_Store_Retailer_Change_Rate (RetailerID,CreatedOn,ProductsCount)
    SELECT
        RP.RetailerID,
        @CreatedOn,
        COUNT(RP.RetailerID) AS NUM
    FROM
        CSK_Store_RetailerProduct RP
    WHERE
        RP.RetailerProductStatus = 1
    GROUP BY
        RP.RetailerID

    //这里要返回查询结果
    SELECT
        RC.RetailerCategoryName,
        A.RetailerID,
        R.RetailerName,
        A.ProductsCount AS CurrCount,
        B.ProductsCount AS PreCount,
        (A.ProductsCount - B.ProductsCount) / B.ProductsCount AS Rate
    FROM
        (SELECT * FROM CSK_Store_Retailer_Change_Rate WHERE CreatedOn = @CURRDAY) A LEFT JOIN
        (SELECT * FROM CSK_Store_Retailer_Change_Rate WHERE CreatedOn = @PreDay) B ON A.RetailerID = B.RetailerID LEFT JOIN
        CSK_Store_Retailer R ON R.RetailerID = A.RetailerID LEFT JOIN
        CSK_Store_RetailerCategory RC ON RC.RetailerCategoryID = R.RetailerCategory
    WHERE
        (A.ProductsCount - B.ProductsCount) / B.ProductsCount >= @Threshold
    ORDER BY
        RC.RetailerCategoryName,R.RetailerName   
END

 

 

 

C#代码：
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace RCR {
    class Program {
        static void Main(string[] args) {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["xxx"].ConnectionString);
            cmd.CommandText = "Store_RetailerProductsChangeRate";
           
            cmd.Parameters.AddWithValue("@Threshold",System.Configuration.ConfigurationManager.AppSettings.Get("Threshold"));
            
            SqlParameter paramCurrDay = cmd.Parameters.Add("@CurrDay",SqlDbType.VarChar,10);/////////////////////
            SqlParameter paramPreDay = cmd.Parameters.Add("@PreDay",SqlDbType.VarChar,10);////////////////////////
            paramCurrDay.Direction = ParameterDirection.Output;///////////////////
            paramPreDay.Direction = ParameterDirection.Output;///////////////////
            cmd.Connection.Open();
            string lastCategory = "";
            string result = "";
            IDataReader dr = cmd.ExecuteReader();
           
            while (dr.Read()) {
                if (lastCategory != dr.GetString(0)) {
                    lastCategory = dr.GetString(0);
                    result += "\r\n\r\n\r\n\r\n" + lastCategory + " " + paramCurrDay.Value + "\r\n\r\n";
                    result += "RetailerID,RetailerName,CurrCount,PreCount,ChangeRate\r\n\r\n";
                }
                result += dr.GetInt32(1) + "," + dr.GetString(2) + "," + dr.GetInt32(3) + "," + dr.GetInt32(4) + "," + dr.GetInt32(5) + "%\r\n";
            }
            dr.Close();
            cmd.Connection.Close();
            FileStream fs = new FileStream("aa.csv", FileMode.OpenOrCreate);
            byte[] data = new UTF8Encoding().GetBytes(result);
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
        }
    }
}

不知道为啥，paramCurrDay / cmd.Parameters["@CurrDay"] 的值一直是 null，有知道为啥的，请麻烦告诉我一声。谢谢。

 

另外说一下，Oracle和 MySQL 早就实现了这样的功能：如果不存在就插入，否则就修改，SQLServer 2005 居然没该功能（可能是我没有找对地方吧，有个 Merge 语法，但是是 SQLServer2008里的内容）。

用 SELECT 给变量赋值的时候，还不能查询其它内容，这一点真是太麻烦了。用了 DISTINCT 后，ORDER BY 里还需要包含 DISTINCT 的东东:
SELECT DISTINCT TOP 1 @PreDay = SUBSTRING(CONVERT(VARCHAR,CreatedOn,20),1,10) FROM CSK_Store_Retailer_Change_Rate WHERE CreatedOn < @CurrDay ORDER BY SUBSTRING(CONVERT(VARCHAR,CreatedOn,20),1,10) DESC

 

在说一下 用 smtp 发邮件，一开始 smtp服务里，啥都没改，结果是一大堆的问题，我就不罗列了，在 右键 默认 smtp 虚拟服务器-》属性-》访问-》中继-》添加127.0.0.1 就行了(http://www.cnblogs.com/hotsoho.net/archive/2008/12/23/1206561.html)。

        static void sendMail() {
            MailMessage msg = new MailMessage();
            msg.From = new MailAddress("no-reply@xxx.com");
            string[] emails = System.Configuration.ConfigurationManager.AppSettings.Get("Email").Split(',');
            foreach (string email in emails) {
                msg.To.Add(email);
            }
            msg.Subject = "Product Count Update " + DateTime.Today.ToLongDateString();
            msg.Body = "The following retailers have a change of more than " + System.Configuration.ConfigurationManager.AppSettings.Get("Threshold") + " %.";
            msg.Attachments.Add(new Attachment("report.csv"));
            msg.IsBodyHtml = false;

            SmtpClient smtp = new SmtpClient();
            smtp.Send(msg);
            msg.Dispose();
        }

一开始我写：SmtpClient smtp = new SmtpClient("localhost"); 也不行，后来同事告诉我在 app.config  里写：
  <system.net>
    <mailSettings>
      <smtp>
        <network host="127.0.0.1" password="" port="25" userName=""/>
      </smtp>
    </mailSettings>
  </system.net>
才可以。