C# SqlDataRecord.SetDouble方法代码示例

 public static void SetDoubleRecord(SqlDataReader dr, string columnName, SqlDataRecord record, int ordinal)
 {
     if (dr[columnName] == DBNull.Value)
         record.SetDBNull(ordinal);
     else
         record.SetDouble(ordinal, Convert.ToDouble(dr[columnName]));
 }

 public static void IR_SM_PriceList_PerType()
 {
     using (SqlConnection con = new SqlConnection("context connection=true"))
     {
         SqlPipe pipe = SqlContext.Pipe;
         List<SqlCommand> commands = new List<SqlCommand>();
         try
         {
             commands.Add(new SqlCommand("SELECT DISTINCT IR_PriceList.PRICE, IR_PriceList.YEAR AS YearT, IR_PriceList.MONTH AS MonthT, IR_PriceList.SAPRODUCTCODE, IR_PriceList.PRICETYPE FROM IR_PriceList"));
             SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PRICE", SqlDbType.Float),
             new SqlMetaData("MonthT", SqlDbType.Int),
             new SqlMetaData("YearT", SqlDbType.Int),
             new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
             new SqlMetaData("PRICETYPE", SqlDbType.NVarChar, 20));
             pipe.SendResultsStart(record);
             foreach (SqlCommand cmd in commands)
             {
                 try
                 {
                     cmd.Connection = con;
                     con.Open();
                     SqlDataReader dr = cmd.ExecuteReader();
                     while (dr.Read())
                     {
                         double price = Convert.ToDouble(dr["PRICE"]);
                         int month = Convert.ToInt32(dr["montht"]);
                         int year = Convert.ToInt32(dr["yeart"]);
                         string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
                         string thePriceType = Convert.ToString(dr["PRICETYPE"]);
                         record.SetDouble(0, price);
                         record.SetInt32(1, month);
                         record.SetInt32(2, year);
                         record.SetString(3, saproductcode);
                         record.SetString(4, thePriceType);
                         pipe.SendResultsRow(record);
                     }
                 }
                 catch (Exception ex)
                 {
                     throw ex;
                 }
                 finally
                 {
                     if (con != null)
                         con.Close();
                 }
             }
             pipe.SendResultsEnd();
         }
         catch (Exception ex)
         {
             throw ex;
         }
     }
 }

        private static SqlDataRecord CreateFractalRecord(double? value)
        {
            var record = new SqlDataRecord(new SqlMetaData("Value", SqlDbType.Float));

            if (value.HasValue)
                record.SetDouble(0, value.Value);
            else
                record.SetDBNull(0);

            return record;
        }

    public static void IR_SM_Pos_Objective(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string dsIds)
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();
            try
            {
                SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
                SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;

                List<string> brdcods = new List<string>();
                string[] split = brandCodes.Split('-');
                foreach (string s in split)
                    if (s.ToLower().Trim() != "")
                        brdcods.Add(s.Trim().ToLower());

                List<string> dsids = new List<string>();
                string[] split2 = dsIds.Split('-');
                foreach (string s in split2)
                    if (s.ToLower().Trim() != "")
                        dsids.Add(s.Trim().ToLower());

                for (int year = (int)smallyear; year <= bigyear; year++)
                {
                    foreach (string brandCode in brdcods)
                    {
                        foreach (string dsid in dsids)
                        {
                            commands.Add(new SqlCommand("SELECT [Counter], [Year], [Month], [DSID], [BudgetValue], [Brandcode] FROM [IR_POS_OBJ] where DSID = @dsid AND (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth)"));
                            commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
                            commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
                            commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
                            commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
                            commands[commands.Count - 1].Parameters.AddWithValue("@dsid", dsid);
                        }
                    }
                }

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
                                       new SqlMetaData("DSID", SqlDbType.Int),
                                       new SqlMetaData("MonthT", SqlDbType.Int),
                                       new SqlMetaData("YearT", SqlDbType.Int),
                                       new SqlMetaData("VAL", SqlDbType.Float),
                                       new SqlMetaData("BrandCode", SqlDbType.NVarChar, 255));

                pipe.SendResultsStart(record);
                int idCounter = 1;
                foreach (SqlCommand cmd in commands)
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();

                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            
                            int dsid = Convert.ToInt32(dr["DSID"]);
                            int month = Convert.ToInt32(dr["month"]);
                            int year = Convert.ToInt32(dr["year"]);
                            double val = Convert.ToDouble(((dr["BudgetValue"] == DBNull.Value) ? 0 : dr["BudgetValue"]));
                            string brandcode = Convert.ToString(dr["Brandcode"]);
                            record.SetInt32(0, idCounter);
                            record.SetInt32(1, dsid);
                            record.SetInt32(2, month);
                            record.SetInt32(3, year);
                            record.SetDouble(4, val);
                            record.SetString(5, brandcode);
                            pipe.SendResultsRow(record);
                            idCounter++;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (con != null)
                            con.Close();
                    }
                }
                pipe.SendResultsEnd();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    public static void IR_SM_TotalSalesIn_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear)
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();
            try
            {
                SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
                SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;

                List<string> brdcods = new List<string>();
                string[] split = brandCodes.Split('-');
                foreach (string s in split)
                {
                    if (s.ToLower().Trim() != "")
                        brdcods.Add(s.Trim().ToLower());
                }


                for (int year = (int)smallyear; year <= bigyear; year++)
                {
                    foreach (string brandCode in brdcods)
                    {
                        commands.Add(new SqlCommand("SELECT [MONTH] AS MonthT, [YEAR] AS YearT, [SalesIN_ACT] AS VAL FROM [IR_SalesIN_Brand] WHERE (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth) "));
                        commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
                        commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
                        commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
                        commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
                    }
                }

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData("MonthT", SqlDbType.Int),
                new SqlMetaData("YearT", SqlDbType.Int),
                new SqlMetaData("VAL", SqlDbType.Float));

                pipe.SendResultsStart(record);

                foreach (SqlCommand cmd in commands)
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();

                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            int month = Convert.ToInt32(dr["montht"]);
                            int year = Convert.ToInt32(dr["yeart"]);
                            double val = Convert.ToDouble(((dr["val"] == DBNull.Value) ? 0 : dr["val"]));

                            record.SetInt32(0, month);
                            record.SetInt32(1, year);
                            record.SetDouble(2, val);
                            pipe.SendResultsRow(record);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (con != null)
                            con.Close();
                    }
                }
                pipe.SendResultsEnd();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }

 public void SetValue(ref SqlDataRecord sqlDataRecord, SqlDescriptionAttribute sqlDescription, object value,
                      int ordinal)
 {
     if (!sqlDescription.HasDbType)
     {
         throw new InvalidDataException("SqlDbType can not be null");
     }
     if (value == null)
     {
         sqlDataRecord.SetDBNull(ordinal);
         return;
     }
     switch (sqlDescription.SqlDbType)
     {
         case SqlDbType.BigInt:
             var ll = value as long?;
             if (!ll.HasValue)
             {
                 throw new Exception("Value is not BigInt");
             }
             sqlDataRecord.SetInt64(ordinal, ll.Value);
             break;
         case SqlDbType.Binary:
             var bb = value as byte?;
             if (!bb.HasValue)
             {
                 throw new Exception("Value is not BigInt");
             }
             sqlDataRecord.SetSqlByte(ordinal, bb.Value);
             break;
         case SqlDbType.Bit:
             var bit = value as bool?;
             if (!bit.HasValue)
             {
                 throw new Exception("Value is not Bit");
             }
             sqlDataRecord.SetBoolean(ordinal, bit.Value);
             break;
         case SqlDbType.NChar:
         case SqlDbType.Char:
             var chr = value as char?;
             if (!chr.HasValue)
             {
                 throw new Exception("Value is not Char");
             }
             sqlDataRecord.SetChar(ordinal, chr.Value);
             break;
         case SqlDbType.DateTime:
         case SqlDbType.SmallDateTime:
         case SqlDbType.Date:
         case SqlDbType.DateTime2:
             var dt = value as DateTime?;
             if (!dt.HasValue)
             {
                 throw new Exception("Value is not DateTime");
             }
             sqlDataRecord.SetDateTime(ordinal, dt.Value);
             break;
         case SqlDbType.Decimal:
         case SqlDbType.Money:
         case SqlDbType.SmallMoney:
             var dc = value as decimal?;
             if (!dc.HasValue)
             {
                 throw new Exception("Value is not Decimal");
             }
             sqlDataRecord.SetDecimal(ordinal, dc.Value);
             break;
         case SqlDbType.Float:
             var d = value as double?;
             if (!d.HasValue)
             {
                 throw new Exception("Value is not Double");
             }
             sqlDataRecord.SetDouble(ordinal, d.Value);
             break;
         case SqlDbType.Image:
         case SqlDbType.VarBinary:
             var bytes = value as byte[];
             if (bytes == null)
             {
                 throw new Exception("Value is not byte array");
             }
             sqlDataRecord.SetBytes(ordinal, 0, bytes, 0, bytes.Length);
             break;
         case SqlDbType.Int:
             var integer = value as int?;
             if (integer == null)
             {
                 var ushortValue = (value as ushort?);
                 if (ushortValue == null)
                 {
                     throw new Exception("Value is not int or ushort");
                 }
                 integer = ushortValue.Value;
             }
             sqlDataRecord.SetInt32(ordinal, integer.Value);
             break;
         case SqlDbType.NText:
         case SqlDbType.NVarChar:
//.........这里部分代码省略.........

    public static void IR_SM_TotalObjective_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string saCodes)
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();

            SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
            SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;

            List<string> brdcods = new List<string>();
            string[] split = brandCodes.Split('-');
            foreach (string s in split)
                if (s.ToLower().Trim() != "")
                    brdcods.Add(s.Trim().ToLower());

            List<string> sacods = new List<string>();
            string[] split2 = saCodes.Split('-');
            foreach (string s in split2)
                if (s.ToLower().Trim() != "")
                    sacods.Add(s.Trim().ToLower());
            if (sacods.Count == 0)
                sacods.Add("");

            foreach (string brandCode in brdcods)
            {
                for (int year = (int)smallyear; year <= bigyear; year++)
                {
                    foreach (string saCode in sacods)
                    {
                        commands.Add(new SqlCommand("SELECT SALESBUDGET.BUDGETTYPECODE AS TypeT, SALESBUDGET.YEAR AS YearT, SALESBUDGET.MONTH AS MonthT, SALESBUDGET.QTY, SALESBUDGET.VALUE AS VAL, SAPRODUCTS.SAPRODUCTCODE FROM SALESBUDGET INNER JOIN SAPRODUCTS ON SAPRODUCTS.SAPRODUCTCODE = SALESBUDGET.SAPRODUCTCODE WHERE (SAPRODUCTS.BRANDCODE = @brandcode) AND (SALESBUDGET.YEAR = @year) AND (SALESBUDGET.MONTH BETWEEN @startmonth AND @endmonth) AND (SAPRODUCTS.SAPRODUCTCODE LIKE '%' + @sacode + '%')"));
                        commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
                        commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
                        commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
                        commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
                        commands[commands.Count - 1].Parameters.AddWithValue("@sacode", saCode);
                    }
                }
            }

            SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Float),
                    new SqlMetaData("MonthT", SqlDbType.Int),
                    new SqlMetaData("YearT", SqlDbType.Int),
                    new SqlMetaData("TypeT", SqlDbType.NVarChar, 50),
                    new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
                    new SqlMetaData("VAL", SqlDbType.Float));

            pipe.SendResultsStart(record);

            foreach (SqlCommand cmd in commands)
            {
                try
                {
                    cmd.Connection = con;
                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr["montht"] == DBNull.Value || dr["yeart"] == DBNull.Value)
                            continue;
                        int month = Convert.ToInt32(dr["montht"]);
                        int year = Convert.ToInt32(dr["yeart"]);
                        double qty = Convert.ToDouble((dr["qty"] == DBNull.Value) ? 0 : dr["qty"]);
                        if (dr["typet"] == DBNull.Value)
                            continue;
                        string type = Convert.ToString(dr["typet"]);
                        if (dr["SAPRODUCTCODE"] == DBNull.Value)
                            continue;
                        string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
                        double val = Convert.ToDouble((dr["val"] == DBNull.Value) ? 0 : dr["val"]);

                        record.SetDouble(0, qty);
                        record.SetInt32(1, month);
                        record.SetInt32(2, year);
                        record.SetString(3, type);
                        record.SetString(4, saproductcode);
                        record.SetDouble(5, val);
                        pipe.SendResultsRow(record);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (con != null)
                        con.Close();
                }
            }
            pipe.SendResultsEnd();
        }
    }

    public static void IR_SM_TotalActual_PerMonth(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string saCodes)
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();
            try
            {
                SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
                SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;

                List<string> brdcods = new List<string>();
                string[] split = brandCodes.Split('-');
                foreach (string s in split)
                    if (s.ToLower().Trim() != "")
                        brdcods.Add(s.Trim().ToLower());

                List<string> sacods = new List<string>();
                string[] split2 = saCodes.Split('-');
                foreach (string s in split2)
                    if (s.ToLower().Trim() != "")
                        sacods.Add(s.Trim().ToLower());
                if (sacods.Count == 0)
                    sacods.Add("");

                for (int year = (int)smallyear; year <= bigyear; year++)
                {
                    foreach (string brandCode in brdcods)
                    {
                        foreach (string saCode in sacods)
                        {
                            commands.Add(new SqlCommand("SELECT SUM(IR_Total_Country.SaleoutQTY) AS QTY, IR_Total_Country.RelatedMonth AS MonthT, IR_Total_Country.RelatedYear AS YearT, SAPRODUCTS.SAPRODUCTCODE, IR_Total_Country.OutValue AS VAL FROM IR_Total_Country INNER JOIN SAPRODUCTS ON SAPRODUCTS.SAPRODUCTCODE = IR_Total_Country.SAProductCode WHERE (SAPRODUCTS.BRANDCODE LIKE '%' + @brandcode + '%') AND (IR_Total_Country.RelatedYear = @year) AND (IR_Total_Country.RelatedMonth BETWEEN @startmonth AND @endmonth)  AND (SAPRODUCTS.SAPRODUCTCODE LIKE '%' + @sacode + '%') GROUP BY IR_Total_Country.RelatedMonth, IR_Total_Country.RelatedYear, SAPRODUCTS.SAPRODUCTCODE, IR_Total_Country.OutValue"));
                            commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
                            commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
                            commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
                            commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
                            commands[commands.Count - 1].Parameters.AddWithValue("@sacode", saCode);
                        }
                    }
                }

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData("QTY", SqlDbType.Float),
                new SqlMetaData("MonthT", SqlDbType.Int),
                new SqlMetaData("YearT", SqlDbType.Int),
                new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar,20),
                new SqlMetaData("VAL", SqlDbType.Float));
                pipe.SendResultsStart(record);
                foreach (SqlCommand cmd in commands)
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();

                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            double qty = Convert.ToDouble(dr["qty"]);
                            int month = Convert.ToInt32(dr["montht"]);
                            int year = Convert.ToInt32(dr["yeart"]);
                            string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
                            double val = Convert.ToDouble(dr["val"]);    
                            record.SetDouble(0, qty);
                            record.SetInt32(1, month);
                            record.SetInt32(2, year);
                            record.SetString(3, saproductcode);
                            record.SetDouble(4, val);
                            pipe.SendResultsRow(record);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (con != null)
                            con.Close();
                    }
                }
                pipe.SendResultsEnd();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }