如何在C＃中从Sql数据库中选择特定数据？ [英] How Do I Select Particular Data From Sql Database In C#?

问题描述

大家好，

以下是我的sql select语句：

  SELECT  product.P_ID，Product.P_Name，Product.Leadtime，Product.SafetyStockamount，
 Monthlysales.Month，Monthlysales.totalsalesamount，（totalsalesamount / 30） as  Averagedailysales，（（totalsalesamount / 30）*提前期+ SafetyStockamount） as  reorderpoint 
  FROM 产品，每月销售额
 其中 Product.P_ID = Monthlysales.P_ID 

产品表中有产品ID，名称，提前期，安全库存栏，同时在月销售表中，有产品，月份（以整数计）和总计每月。



问题是：



i希望从visual studio c中的select语句中检索再订货点结果＃

界面将包含产品id，月选项，按钮和标签显示结果。

再订货点结果将基于月份，例如

如果用户选择

pid 1：1

月份期权：2（2月）

然后标签结果将显示2月份pid 1的再订货点金额。



i还想知道我是否可以根据产品ID及其在sql server中的月份直接计算再订货点结果，并直接在c＃中调用它。



我怎么能这样做？请帮帮我

谢谢你

解决方案

你需要在你的where条件下使用参数如下

< pre lang =SQL> SELECT product.P_ID，Product.P_Name，Product.Leadtime，Product.SafetyStockamount，
Monthlysales.Month，Monthlysales.totalsalesamount， （totalsalesamount / 30） as Averagedailysales，（（totalsalesamount / 30）* Leadtime + SafetyStockamount） as reorderpoint
FROM 产品，每月销售额
其中 Product.P_ID = Monthlysales.P_ID AND Product.P_ID = @ P_ID AND Monthlysales.Month = @ Month

C＃代码：

 使用（SqlConnection connection =  new  SqlConnection（connec tionString））
 {
 connection.Open（）; 
 
 使用（SqlCommand command =  new  SqlCommand（
   SELECT product.P_ID，Product.P_Name，Product.Leadtime，Product.SafetyStockamount， + 
   Monthlysales.Month，Monthlysales.totalsalesamount，（totalsalesamount / 30）为Averagedailysales，（（totalsalesamount / 30）*提前期+ SafetyStockamount）作为reorderpoint + 
   FROM Product，Monthlysales + 
  其中Product.P_ID = Monthlysales.P_ID AND Product.P_ID = @ P_ID AND Monthlysales.Month = @ Month ，connection））
 {
 
 command.Parameters.Add（ new  SqlParameter（  P_ID，pid））; 
 command.Parameters.Add（ new  SqlParameter（ 月，月））; 
 SqlDataAdapter adapter =  new  SqlDataAdapter（command）; 
 DataSet ds =  new  DataSet（）; 
 adapter.Fill（ds）; 
  //  现在您拥有数据集中的数据ds  
} 
} 

hello guys,
below is my sql select statement :

SELECT product.P_ID, Product.P_Name,Product.Leadtime, Product.SafetyStockamount,
Monthlysales.Month, Monthlysales.totalsalesamount, (totalsalesamount/30) as Averagedailysales, ((totalsalesamount/30) * Leadtime + SafetyStockamount) as reorderpoint
FROM Product, Monthlysales
where Product.P_ID = Monthlysales.P_ID

in product table there are product id, name, lead time, safety stock column, meanwhile in monthly sales table, there are productid, month (in int), and totalsalespermonth.

the problem is :

i want to retrieve the reorder point result from the select statement above in visual studio c#
the interface will include product id , month options, button and label to show the result.
the reorder point result will be based on the month, for example
if the user choose
pid 1 : 1
month options: 2 (february)
then label result will show the reorder point amount for pid 1 in february.

i also wondering whether i can directly calculate the reorder point result based on product id and its month in sql server and directly call it in c#.

how could i do it?? please help me
thank you

解决方案

you need to use parameters in your where condition as below

SELECT product.P_ID, Product.P_Name,Product.Leadtime, Product.SafetyStockamount,
Monthlysales.Month, Monthlysales.totalsalesamount, (totalsalesamount/30) as Averagedailysales, ((totalsalesamount/30) * Leadtime + SafetyStockamount) as reorderpoint
FROM Product, Monthlysales
where Product.P_ID = Monthlysales.P_ID AND Product.P_ID =@P_ID AND Monthlysales.Month =@Month 

C# code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(
    "SELECT product.P_ID, Product.P_Name,Product.Leadtime, Product.SafetyStockamount," +
    "Monthlysales.Month, Monthlysales.totalsalesamount, (totalsalesamount/30) as Averagedailysales, ((totalsalesamount/30) * Leadtime + SafetyStockamount) as reorderpoint " +
    "FROM Product, Monthlysales "+
    "where Product.P_ID = Monthlysales.P_ID AND Product.P_ID =@P_ID AND Monthlysales.Month =@Month ", connection))
    {

        command.Parameters.Add(new SqlParameter("P_ID", pid));
        command.Parameters.Add(new SqlParameter("Month", month));
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        // now you have the data in Dataset ds
    }
}

这篇关于如何在C＃中从Sql数据库中选择特定数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！