我如何表达这一陈述 [英] HOW DO I REPRESENT THIS STATEMENT

查看：48
本文介绍了我如何表达这一陈述的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Hello Guys，

2016年度假和祝福，下面是我的C＃代码，我想返回三个表（tbl_HTC1，tbl_HTC2和tbl_HTC3）中所有字段的总和br $> b $ b

Hello Guys,
happy holiday and best wishes in 2016, below is my C# code where i want to return the aggregated sum of all the fields in three tables (tbl_HTC1,tbl_HTC2 and tbl_HTC3)

protected int SumSQL()
{
   string SQL = "Select Sum(a) from (";
       SQL +="Select CONVERT(INT,TOTALG1)+CONVERT(INT,SUBG1MA)+CONVERT(INT,G1MA1)+CONVERT(INT,G1MA1_4)+CONVERT(INT,G1MA5_9)+CONVERT(INT,G1MA10_14)+CONVERT(INT,G1MA15_19)+CONVERT(INT,G1MA20_24)+CONVERT(INT,G1MA25_49)";
       SQL +="+CONVERT(INT,G1MA50)+CONVERT(INT,SUBG1FE)+CONVERT(INT,G1FE1)+CONVERT(INT,G1FE1_4)+CONVERT(INT,G1FE5_9)+CONVERT(INT,G1FE10_14)+CONVERT(INT,G1FE15_19)+CONVERT(INT,G1FE20_24)+CONVERT(INT,G1FE25_49)+CONVERT(INT,G1FE50)";
       SQL +="+CONVERT(INT,TOTALG2)+CONVERT(INT,SUBG2M)+CONVERT(INT,G2MA1)+CONVERT(INT,G2MA1_4)+CONVERT(INT,G2MA5_9)+CONVERT(INT,G2MA10_14)+CONVERT(INT,G2MA15_19)+CONVERT(INT,G2MA20_24)+CONVERT(INT,G2MA25_49)+CONVERT(INT,G2MA50)";
       SQL +="+CONVERT(INT,SUBG2F)+CONVERT(INT,G2FE1)+CONVERT(INT,G2FE1_4)+CONVERT(INT,G2FE5_9)+CONVERT(INT,G2FE10_14)+CONVERT(INT,G2FE15_19)+CONVERT(INT,G2FE20_24)+CONVERT(INT,G2FE25_49)+CONVERT(INT,G2FE50)";
       SQL +="+CONVERT(INT,TOTALG3)+CONVERT(INT,SUBG3M)+CONVERT(INT,G3MA1)+CONVERT(INT,G3MA1_4)+CONVERT(INT,G3MA5_9)+CONVERT(INT,G3MA10_14)+CONVERT(INT,G3MA15_19)+CONVERT(INT,G3MA20_24)+CONVERT(INT,G3MA25_49)+CONVERT(INT,G3MA50)";
       SQL +="+CONVERT(INT,SUBG3F)+CONVERT(INT,G3FE1)+CONVERT(INT,G3FE1_4)+CONVERT(INT,G3FE5_9)+CONVERT(INT,G3FE10_14)+CONVERT(INT,G3FE15_19)+CONVERT(INT,G3FE20_24)+CONVERT(INT,G3FE25_49)+CONVERT(INT,G3FE50)+CONVERT(INT,TOTALG4)+CONVERT(INT,SUBG4P)";
       SQL +="+CONVERT(INT,G4P1)+CONVERT(INT,G4P1_4)+CONVERT(INT,G4P5_9)+CONVERT(INT,G4P10_14)+CONVERT(INT,G4P15_19)+CONVERT(INT,G4P20_24)+CONVERT(INT,G4P25_49)+CONVERT(INT,G4P50)+CONVERT(INT,SUB4N)+CONVERT(INT,G4N1)+CONVERT(INT,G4N1_4)";
       SQL +="+CONVERT(INT,G4N5_9)+CONVERT(INT,G4N10_14)+CONVERT(INT,G4N15_19)+CONVERT(INT,G4N20_24)+CONVERT(INT,G4N25_49)+CONVERT(INT,G4N50)+CONVERT(INT,TOTALG5)+CONVERT(INT,SUBG5M)+CONVERT(INT,G5MA1)+CONVERT(INT,G5M1_4)+CONVERT(INT,G5MA5_9)";
       SQL +="+CONVERT(INT,G5MA10_14)+CONVERT(INT,G5MA15_19)+CONVERT(INT,G5MA20_24)+CONVERT(INT,G5MA25_49)+CONVERT(INT,G5MA50)+CONVERT(INT,SUBG5F)+CONVERT(INT,G5FE1)+CONVERT(INT,G5FE1_4)+CONVERT(INT,G5FE5_9)+CONVERT(INT,G5FE10_14)+CONVERT(INT,G5FE15_19)";
       SQL +="+CONVERT(INT,G5FE20_24)+CONVERT(INT,G5FE25_49)+CONVERT(INT,G5FE50)+CONVERT(INT,TOTALG6)+CONVERT(INT,SUBG6M)+CONVERT(INT,G6MA1)+CONVERT(INT,G6M1_4)+CONVERT(INT,G6MA5_9)+CONVERT(INT,G6MA10_14)+CONVERT(INT,G6MA15_19)+CONVERT(INT,G6MA20_24)";
       SQL +="+CONVERT(INT,G6MA25_49)+CONVERT(INT,G6MA50)+CONVERT(INT,SUBG6F)+CONVERT(INT,G6FE1)+CONVERT(INT,G6FE1_4)+CONVERT(INT,G6FE5_9)+CONVERT(INT,G6FE10_14)+CONVERT(INT,G6FE15_19)+CONVERT(INT,G6FE20_24)+CONVERT(INT,G6FE25_49)+CONVERT(INT,G6FE50)";
       SQL +="+CONVERT(INT,TOTALG7)+CONVERT(INT,SUBG7M)+CONVERT(INT,G7MA1)+CONVERT(INT,G7M1_4)+CONVERT(INT,G7MA5_9)+CONVERT(INT,G7MA10_14)+CONVERT(INT,G7MA15_19)+CONVERT(INT,G7MA20_24)+CONVERT(INT,G7MA25_49)+CONVERT(INT,G7MA50)+CONVERT(INT,SUBG7F)";
       SQL +="+CONVERT(INT,G7FE1)+CONVERT(INT,G7FE1_4)+CONVERT(INT,G7FE5_9)+CONVERT(INT,G7FE10_14)+CONVERT(INT,G7FE15_19)+CONVERT(INT,G7FE20_24)+CONVERT(INT,G7FE25_49)+CONVERT(INT,G7FE50)+CONVERT(INT,TOTALG8)+CONVERT(INT,SUBG8M)+CONVERT(INT,G8MA10_14)";
       SQL +="+CONVERT(INT,G8M15_19)+CONVERT(INT,G8MA20_24)+CONVERT(INT,G8MA25_49)+CONVERT(INT,G8MA50)+CONVERT(INT,SUBG8P)as a from tbl_htc1";
       SQL +="union all";
       SQL +="Select CONVERT(INT,TOTALG9)+CONVERT(INT,SUBG9M)+CONVERT(INT,G9MA1) +CONVERT(INT,G9MA1_4)+CONVERT(INT,G9MA5_9)+CONVERT(INT,G9MA10_14)+CONVERT(INT,G9MA15_19)+CONVERT(INT,G9MA20_24)+CONVERT(INT,G9MA25_49)+CONVERT(INT,G9MA50)+CONVERT(INT,SUBG9F)";
       SQL +="+CONVERT(INT,G9FE1)+CONVERT(INT,G9FE5_9)+CONVERT(INT,G9FE10_14)+CONVERT(INT,G9FE15_19)+CONVERT(INT,G9FE20_24)+CONVERT(INT,G9FE25_49)+CONVERT(INT,G9FE50)+CONVERT(INT,TOTALG10)+CONVERT(INT,SUBG10P)+CONVERT(INT,G10P1)+CONVERT(INT,G10P1_4)";
       SQL +="+CONVERT(INT,G10P5_9)+CONVERT(INT,G10P10_14)+CONVERT(INT,G10P15_19)+CONVERT(INT,G10P20_24)+CONVERT(INT,G10P25_49)+CONVERT(INT,G10P50)+CONVERT(INT,SUBG10N)+CONVERT(INT,G10N1)+CONVERT(INT,G10N1_4)+CONVERT(INT,G10N5_9)+CONVERT(INT,G10N10_14)";
       SQL +="+CONVERT(INT,G10N15_19)+CONVERT(INT,G10N20_24)+CONVERT(INT,G10N25_49)+CONVERT(INT,G10N50)+CONVERT(INT,TOTALG11)+CONVERT(INT,SUBG11M)+CONVERT(INT,G11MA1)+CONVERT(INT,G11MA1_4)+CONVERT(INT,G11MA10_14)+CONVERT(INT,G11MA15_19)+CONVERT(INT,G11MA20_24)";
       SQL +="+CONVERT(INT,G11MA25_49)+CONVERT(INT,G11MA50)+CONVERT(INT,SUBG11F)+CONVERT(INT,G11FE1)+CONVERT(INT,G11FE1_4)+CONVERT(INT,G11FE5_9)+CONVERT(INT,G11FE10_14)+CONVERT(INT,G11FE15_19)+CONVERT(INT,G11FE20_24)+CONVERT(INT,G11FE25_49)+CONVERT(INT,G11FE50)";
       SQL +="+CONVERT(INT,TOTALG12)+CONVERT(INT,SUBG12M)+CONVERT(INT,G12MA1)+CONVERT(INT,G12MA1_4)+CONVERT(INT,G12MA5_9)+CONVERT(INT,G12MA10_14)+CONVERT(INT,G12MA15_19)+CONVERT(INT,G12MA20_24)+CONVERT(INT,G12MA25_49)+CONVERT(INT,G12MA50)+CONVERT(INT,SUBG12F)";
       SQL +="+CONVERT(INT,G12FE1)+CONVERT(INT,G12FE1_4)+CONVERT(INT,G12FE5_9)+CONVERT(INT,G12FE10_14)+CONVERT(INT,G12FE15_19)+CONVERT(INT,G12FE20_24)+CONVERT(INT,G12FE25_49)+CONVERT(INT,G12FE50)+CONVERT(INT,TOTALG13)+CONVERT(INT,SUBG13M)+CONVERT(INT,G13MA1)";
       SQL +="+CONVERT(INT,G13MA1_4)+CONVERT(INT,G13MA5_9)+CONVERT(INT,G13MA10_14)+CONVERT(INT,G13MA15_19)+CONVERT(INT,G13MA20_24)+CONVERT(INT,G13MA25_49)+CONVERT(INT,G13MA50)+CONVERT(INT,SUBG13F)+CONVERT(INT,G13FE1)+CONVERT(INT,G13FE1_4)+CONVERT(INT,G13FE5_9)";
       SQL +="+CONVERT(INT,G13FE10_14)+CONVERT(INT,G13FE15_19)+CONVERT(INT,G13FE20_24)+CONVERT(INT,G13FE25_49)+CONVERT(INT,G13FE50)+CONVERT(INT,TOTALG14)+CONVERT(INT,SUBG14M)+CONVERT(INT,G14MA1)+CONVERT(INT,G14MA1_4)+CONVERT(INT,G14MA5_9)+CONVERT(INT,G14MA10_14)";
       SQL +="+CONVERT(INT,G14MA15_19)+CONVERT(INT,G14MA20_24)+CONVERT(INT,G14MA25_49)+CONVERT(INT,G14MA50)+CONVERT(INT,SUBG14F)+CONVERT(INT,G14FE1)+CONVERT(INT,G14FE1_4)+CONVERT(INT,G14FE5_9)+CONVERT(INT,G14FE10_14)+CONVERT(INT,G14FE15_19)+CONVERT(INT,G14FE20_24)";
       SQL +="+CONVERT(INT,G14FE50)+CONVERT(INT,TOTALG15)+CONVERT(INT,SUBG15M)+CONVERT(INT,G15MA15_19)+CONVERT(INT,G15MA20_24)+CONVERT(INT,G15MS25_49)+CONVERT(INT,G15MA50)+CONVERT(INT,SUBG15F)+CONVERT(INT,G15FE15_19)+CONVERT(INT,G15FE20_24)+CONVERT(INT,G15FE25_49)";
       SQL +="+CONVERT(INT,G15FE50)+CONVERT(INT,TOTALG16)+CONVERT(INT,SUBG16M)+CONVERT(INT,G16MA15_19)+CONVERT(INT,G16MS25_49)+CONVERT(INT,G16MA50)+CONVERT(INT,SUBG16F)+CONVERT(INT,G16FE15_19)+CONVERT(INT,G16FE20_24)+CONVERT(INT,G16FE25_49)+CONVERT(INT,G16FE50)as a from tbl_htc2";
       SQL +="union all";
       SQL +="Select CONVERT(INT,TOTALG17)+CONVERT(INT,SUBG17M)+CONVERT(INT,G17MA1)+CONVERT(INT,G17MA1_4)+CONVERT(INT,G17MA5_9)+CONVERT(INT,G17MA10_14)+CONVERT(INT,G17MA15_19)+CONVERT(INT,G17MA20_24)+CONVERT(INT,G17MA25_49)+CONVERT(INT,G17MA50)+CONVERT(INT,SUBG17F)+CONVERT(INT,G17FE1) ";
       SQL +="+CONVERT(INT,G17FE1_4)+CONVERT(INT,G17FE5_9)+CONVERT(INT,G17FE10_14)+CONVERT(INT,G17FE15_19)+CONVERT(INT,G17FE20_24)+CONVERT(INT,G17FE25_49)+CONVERT(INT,G17FE50)+CONVERT(INT,TOTALG18)+CONVERT(INT,SUBG18M)+CONVERT(INT,G18MA1)+CONVERT(INT,G18MA1_4)+CONVERT(INT,G18MA5_9)+CONVERT(INT,G18MA10_14)";
       SQL +="+CONVERT(INT,G18MA15_19)+CONVERT(INT,G18MA20_24)+CONVERT(INT,G18MA25_49)+CONVERT(INT,G18MA50)+CONVERT(INT,SUBG18F)+CONVERT(INT,G18FE1)+CONVERT(INT,G18FE1_4)+CONVERT(INT,G18FE5_9)+CONVERT(INT,G18FE10_14)+CONVERT(INT,G18FE15_19)+CONVERT(INT,G18FE20_24)";
       SQL +="+CONVERT(INT,G18FE25_49)+CONVERT(INT,G18FE50)+CONVERT(INT,TOTALG19)+CONVERT(INT,SUBG19M)+CONVERT(INT,G19MA1)+CONVERT(INT,G19MA1_4)+CONVERT(INT,G98MA5_9)+CONVERT(INT,G19MA10_14)+CONVERT(INT,G19MA15_19)+CONVERT(INT,G19MA20_24)+CONVERT(INT,G19MA25_49)";
       SQL +="+CONVERT(INT,G19MA50)+CONVERT(INT,SUBG19F)+CONVERT(INT,G19FE1)+CONVERT(INT,G19FE1_4)+CONVERT(INT,G19FE5_9)+CONVERT(INT,G19FE10_14)+CONVERT(INT,G19FE15_19)+CONVERT(INT,G19FE20_24)+CONVERT(INT,G19FE25_49)+CONVERT(INT,G19FE50)+CONVERT(INT,TOTALG20)";
       SQL +="+CONVERT(INT,SUBG0M)+CONVERT(INT,G20MA1) +CONVERT(INT,G20MA1_4)+CONVERT(INT,G20MA5_9)+CONVERT(INT,G20MA10_14)+CONVERT(INT,G20MA15_19)+CONVERT(INT,G20MA20_24)+CONVERT(INT,G20MA25_49)+CONVERT(INT,G20MA50)+CONVERT(INT,SUBG20F)+CONVERT(INT,G20FE1)";
       SQL +="+CONVERT(INT,G20FE1_4)+CONVERT(INT,G20FE5_9)+CONVERT(INT,G20FE10_14)+CONVERT(INT,G20FE15_19)+CONVERT(INT,G20FE20_24)+CONVERT(INT,G20FE25_49)+CONVERT(INT,G20FE50)+CONVERT(INT,TOTALG21)+CONVERT(INT,SUBG21M)+CONVERT(INT,G21MA1)+CONVERT(INT,G21MA1_4)";
       SQL +="+CONVERT(INT,G21MA5_9)+CONVERT(INT,G21MA10_14)+CONVERT(INT,G21MA15_19)+CONVERT(INT,G21MA20_24)+CONVERT(INT,G21MA25_49)+CONVERT(INT,G21MA50)+CONVERT(INT,SUBG21F)+CONVERT(INT,G21FE1)+CONVERT(INT,G21FE1_4)+CONVERT(INT,G21FE5_9)+CONVERT(INT,G21FE10_14)";
       SQL +="+CONVERT(INT,G21FE15_19)+CONVERT(INT,G21FE20_24)+CONVERT(INT,G21FE25_49)+CONVERT(INT,G21FE50)+CONVERT(INT,TOTALG22)+CONVERT(INT,SUBG22M)+CONVERT(INT,G22MA1)+CONVERT(INT,G22MA1_4)+CONVERT(INT,G22MA5_9)+CONVERT(INT,G22MA10_14)+CONVERT(INT,G22MA15_19)";
       SQL +="+CONVERT(INT,G22MA20_24)+CONVERT(INT,G22MA25_49)+CONVERT(INT,G22MA50)+CONVERT(INT,SUBG22F)+CONVERT(INT,G22FE1)+CONVERT(INT,G22FE1_4)+CONVERT(INT,G22FE5_9)+CONVERT(INT,G22FE10_14)+CONVERT(INT,G22FE15_19)+CONVERT(INT,G22FE20_24)+CONVERT(INT,G22FE25_49)";
       SQL +="+CONVERT(INT,G22FE50)+CONVERT(INT,G23_1)+CONVERT(INT,G23_2)+CONVERT(INT,TOTALG24)+CONVERT(INT,SUBG24M)+CONVERT(INT,G24MA15_19)+CONVERT(INT,G24MA20_24)+CONVERT(INT,G24MA25_49)+CONVERT(INT,G24MA50)+CONVERT(INT,SUBG24F)+CONVERT(INT,G24FE15_19)";
       SQL +="+CONVERT(INT,G24FE20_24)+CONVERT(INT,G24FE25_49)+CONVERT(INT,G24FE50)+CONVERT(INT,TOTALG25)+CONVERT(INT,SUBG25M)+CONVERT(INT,G25MA15_19)+CONVERT(INT,G25MA20_24)+CONVERT(INT,G25MA25_49)+CONVERT(INT,G25MA50)+CONVERT(INT,SUBG25F)+CONVERT(INT,G25FE15_19)";
       SQL +="+CONVERT(INT,G25FE20_24)+CONVERT(INT,G25FE25_49)+CONVERT(INT,G25FE50)+CONVERT(INT,TOTALG26)+CONVERT(INT,G26MA1)+CONVERT(INT,G26FE1)as a from tbl_htc3)as TOTAL ";
    try
    {
        int Total = 0;
        SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
        if (cn.State != ConnectionState.Open )
        {
            cn.Open();
        }

        SqlCommand cmd = new SqlCommand(SQL, cn);
        SqlDataReader rd = cmd.ExecuteReader();
        while (rd.Read())
        {
            Total = Convert.ToInt32( rd["TOTAL"].ToString());
        }
        return Total;
    }
    catch (Exception ex)
    {
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "'"+ex.Message.ToString()+"'", true);
        return 0;
    }
}

TextBox3.Text = Convert.ToString(Sum());





脚本在SQL Management studio中运行正常，试图将它与C＃代码一起使用，它抛出异常关键字'CONVERT'附近的语法不正确。



i需要你的建议我使用存储过程吗？或者是他们的任何错误，示例代码将帮助很多。

因为这是紧急的。



感谢伙计



the script worked fine in SQL Management studio, trying to use it with C# code it throwing exception "Incorrect syntax near the keyword 'CONVERT'".

i need your recommendation do i use stored procedure? or is their any thing wrong, sample code will help alot.
as this is urgent.

thanks Guys

推荐答案

首先，这个问题对您而言非常紧迫，而不是CodeProject的其他任何人。



功能你想要我会使用一个返回总和的存储函数。

然后使用ExecuteScalar来获得结果。



的好处是你更容易调试SQL语句，并从客户端隐藏SQL查询的实现细节。

客户端只对最终结果感兴趣，总和，对吗？



[更新]

您可以这样做：

First of all, this problem is only urgent to you, not to anyone else here at CodeProject.

For the functionality you want I would use a stored function that returns the total sum.
Then use ExecuteScalar in order to get the result.

The benefits are that it is easier for you to debug the SQL statement and you hide the implementation details of the SQL query from the client.
The client is only interested in the final result, the total sum, Right?

[UPDATE]
You can do something like this:
CREATE FUNCTION dbo.CalculateTotalSum()
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE varResult INT;

    -- Add your calculation here
    SET varResult = ???

    -- Return the result of the calculation
    RETURN varResult;

END





然后在C＃中与此类似的东西：



Then in C# do something similar to this:

using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand com = new SqlCommand("Execute dbo.CalculateTotalSum", con);
    int totalSum = (int)com.ExecuteScalar();
}







新年快乐。




Happy New Year.


这篇关于我如何表达这一陈述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆