C＃SQL select语句 - 如何更改拉取的值？ [英] C# SQL select statement - how do I change value pulled?

问题描述

Hello All -



我希望这是一个正确的论坛。我使用下面的代码将SQL中的值拉入datagridview。一切都运行良好，除了我希望结果单元格（将返回为1,2或3）在结果被查询为已启动，已停止，停止时进行更改。此外，如果值数字不是1,2或3，那么我想让datagridview说未知



所以，例如，在我的表中我可以：

FirstName = Mike

LastName = Jones

价值= 1



FirstName = Steve

LastName = McDanields

价值= 3



FirstName = Chad

LastName = Smith

价值= 0



我希望datagridveiw显示的是：



FirstName = Mike

LastName = Jones

价值=开始



FirstName = Steve

LastName = McDanields

价值=停止



FirstName = Chad

LastName =史密斯

价值=未知





我正在使用的代码：

Hello All -

I hope this is the correct forum to ask this. I am using the below code to pull values from SQL into a datagridview. Everything works well, except I would like the "Value" cell (which will either come back as 1, 2, or 3) to change when the results are queried to Started, Stopped, Stopping. Also, if the Value number is not 1, 2, or 3 then I would like to have the datagridview say "Unknown"

So, for example, in my table I could have:
FirstName = Mike
LastName = Jones
Value = 1

FirstName = Steve
LastName = McDanields
Value = 3

FirstName = Chad
LastName = Smith
Value = 0

What I would like the datagridveiw to show is:

FirstName = Mike
LastName = Jones
Value = Started

FirstName = Steve
LastName = McDanields
Value = Stopping

FirstName = Chad
LastName = Smith
Value = Unknown


Code I am using:

SqlConnection myConnection = new SqlConnection("Server=mylaptop; Database = users; User Id = test; Password = testpass;");


myConnection.Open();
           
SqlCommand objcmd = new SqlCommand("SELECT LastName, FirstName, Value, FROM table1 ", myConnection);

     
SqlDataAdapter adp = new SqlDataAdapter(objcmd);
DataTable dt = new DataTable();

adp.Fill(dt);
dataGridView1.DataSource = dt;

希望有人可以提前帮助并谢谢你。



我尝试过的事情：



Google，如果在代码中放置语句，但无法弄清楚如何操作。

Hope someone can help and thank you in advanced.

What I have tried:

Google, Placing if then statements in code, but cannot figure out how to do it.

推荐答案

你可以使用case语句设置sql的值，例如

You can set the value from sql using case statement such as

SELECT 
LastName, 
FirstName, 
( case Value 
   when 1 then 'Started'
   when 2 then 'Stopped'
   when 3 then 'Stopping'
   else 'Unknown'
end ) as Status
FROM table1

注意case语句可以用于更复杂的sql

Note that the case statement can be used for more complex sql

另外通过 P_Z 解决方案1 ​​[ ^ ]，我建议创建帮助表，例如：

In addition to solution 1 by P_Z[^], i'd suggest to create helper table, for example:

--create variable, type of Table
DECLARE @tmp TABLE([Value] INT, [Description] VARCHAR(150))

INSERT INOT @tmp ([Value], [Description])
VALUES(0, 'Unknow'), (1, 'Started'), (3, 'Stopped')

SELECT t1.LastName, t1.FirstName, t2.[Description]
FROM table1 t1 INNER JOIN @tmp t2 ON t1.[Value] = t2.[Value]

请注意，你可以创建真正的桌子。



注意＃2 - 删除最后一个逗号，就在 FROM 语句之前。

Note that, you can create real table.

Note #2 - remove last comma, just before FROM statement.

SELECT LastName, FirstName, Value, FROM table1 

有关加入表格的更多详情，请参阅： SQL连接的可视化表示 [ ^ ]

这篇关于C＃SQL select语句 - 如何更改拉取的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！