使用c#运行SQL Server查询

本文关键字：Server 查询 SQL 运行 使用 | 更新日期: 2023-09-27 18:27:18

我有以下代码：

Connection c = new Connection();
string select1 =      
   @"SELECT 
       E.employeeNumber AS 'Employee Number', 
       E.employeePrivateName + ' ' + E.employeeFamilyName AS 'Employee Name', 
       DATEDIFF (MONTH, E.startWorkingDate, GETDATE()) AS 'Seniority in Month', 
       M.machineName AS 'Machine Name', J.jobName AS 'Job Name', 
       COUNT(E.employeeNumber) AS 'Number of Times on Machine in Specif Job', 
       SUM(Number_Of_Days_During_The_Period) AS 'Total Working Days on Machine in Specif Job', 
       SUM(Salary_per_Period) AS 'The Salary For working on Machine in Specif Job' 
    FROM 
       TblEmployee E 
    INNER JOIN 
       AllSchedules A_S on E.employeeNumber = A_S.employeeNumber 
    INNER JOIN 
       TblJob J on J.jobNumber = A_S.jobNumber 
    INNER JOIN 
       TblMachine M on M.machineNumber = A_S.machineNumber 
    INNER JOIN 
       TblPeriod P on P.Number = A_S.periodNumber 
    WHERE 
       Month(P.fromDate) = Month(@Month) 
    GROUP BY 
       E.employeeNumber, E.employeePrivateName, E.employeeFamilyName, 
       E.startWorkingDate, M.machineName, J.jobName 
    ORDER BY 
       E.employeeFamilyName , E.employeePrivateName";
SqlCommand cmd = new SqlCommand(select1, c.con);
DateTime month = comboBox1.Text;
cmd.Connection = c.con;
cmd.Parameters.AddWithValue("@Month", month);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); //c.con is the      connection string
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
dataGridView1.ReadOnly = true;
dataGridView1.DataSource = ds.Tables[0];

我想运行查询，但是我需要定义一个日期时间值，以便它成为查询的搜索参数。如何保存DateTime变量，使其包含月份编号（10表示十月等）

感谢

使用c#运行SQL Server查询

在您的查询中，有一行用于WHERE条件

WHERE Month(P.fromDate)= Month(@Month)

T-SQL的MONTH函数希望收到其参数的Date，所以您可能只需要

DateTime dt = new DateTime(DateTime.Today.Year, month, 1);

并将该值传递给参数@Month

cmd.Parameters.AddWithValue("@Month", dt);

编辑：如果你有一个组合框，里面的项目填写了订购的月份名称：（一月、二月……）

// In array the indexes start at zero.....
if(cboMonths.SelectedIndex >= 0)
{
    int month = cboMonts.SelectedIndex + 1;
}

您可以使用

string monthString = DateTime.ParseExact(comboBox1.Text, "MMMM", CultureInfo.CurrentCulture ).Month.ToString("MM");

并将其转换为整数：

int month = int.Parse(monthString);

string sMonth = DateTime.Now.ToString("MM");

假设SQL Month函数返回一个需要替换的整数（Source）：

"... WHERE Month(P.fromDate)= Month(@Month) GROUP BY ..."

通过

"... WHERE Month(P.fromDate) = " + intMonth.ToString() + " GROUP BY ..."

其中intMonth是从组合框中选择的月份的整数值。

但是，您需要确保它只是一个整数，不能用作注入攻击。您最好将所有SQL放入存储过程中，并将月份作为整数参数传递。

相关文章：