Parameterized splicing in query conditions, personal backup

/// <Summary>
/// Query data reports
/// </Summary>
/// <Param name = "trueouttimeto"> end of date range </param>
/// <Param name = "anslyse"> analysis angle </param>
/// <Param name = "model"> dispatch task model </param>
/// <Returns> </returns>
Public datatable getvehdisreport (string trueouttimeto, string anslyse, vehdisptaskmodel Model)
{
String SQL = string. Empty; // store the SQL statement Header
Stringbuilder sqlwhere = new stringbuilder (); // SQL statement that stores query Conditions

List <sqlparameter> liststr = new list <sqlparameter> (); // convert the dynamic storage parameter to sqlparameter [].

String [] arrlicenseplate = model. licenceplate. Split (','); // store all the selected license plates
If (! String. isnullorempty (model. licenceplate. Trim ()))
{
// Parameterized concatenation in ('','', '') query statement
String slicenseplatecondition = "and licenceplate in (";
For (INT I = 0; I <arrlicenseplate. length; I ++)
{
If (! String. isnullorempty (arrlicenseplate [I])
{
Slicenseplatecondition + = "@ plate" + I + ",";
Liststr. Add (New sqlparameter ("@ plate" + I, arrlicenseplate [I]);
}
}
Slicenseplatecondition = slicenseplatecondition. trimend (',');
Slicenseplatecondition + = ")";
// Add query Conditions
Sqlwhere. append (slicenseplatecondition );
}


String [] arrdriverid = model. driverid. Split (','); // store all the selected driver ID
If (! String. isnullorempty (model. driverid ))
{
// Parameterized concatenation in ('','', '') query statement
String sdriveridcondition = "and driverid in (";
For (INT I = 0; I <arrdriverid. length; I ++)
{
If (! String. isnullorempty (arrdriverid [I])
{
Sdriveridcondition + = "@ driverid" + I + ",";
Liststr. Add (New sqlparameter ("@ driverid" + I, arrdriverid [I]);
}
}
Sdriveridcondition = sdriveridcondition. trimend (',');
Sdriveridcondition + = ")";
// Add query Conditions
Sqlwhere. append (sdriveridcondition );

}
If (! String. isnullorempty (model. vehdisptime. tostring ())&&! String. isnullorempty (trueouttimeto ))
{
Liststr. Add (New sqlparameter ("@ outtimefrom", model. vehdisptime ));
Liststr. Add (New sqlparameter ("@ outtimeto", convert. todatetime (trueouttimeto )));
Sqlwhere. append ("and vehdisptime between cast (@ outtimefrom as datetime) and cast (@ outtimeto as datetime )");
}
If (anslyse = "1") // if the analysis angle is 1, it indicates the driver; otherwise, it indicates the official vehicle.
{
SQL = string. format (@ "select driverid as 'driver ID driverid', drivername as 'driver name drivername', convert (varchar (10), vehdisptime, 120) as 'actual dispatch date vehdisptime ',
Round (sum (datediff (hour, trueouttime, truebacktime), 0) as 'hours' during car dispatch from [DBO]. [vwvehdisptask]
Where status = 'finished' "+" {0} "+" group by driverid, drivername, convert (varchar (10), vehdisptime, 120) ", sqlwhere );
}
Else
{
SQL = string. format (@ "select licenceplate as 'license plate licenceplate ', vehmodel as 'Vehicle model', convert (varchar (10), vehdisptime, 120) as 'actual Vehicle dispatch date vehdisptime ',
Round (sum (datediff (hour, trueouttime, truebacktime), 0) as 'hours' during car dispatch from [DBO]. [vwvehdisptask]
Where status = 'finished' "+" {0} "+" group by convert (varchar (10), vehdisptime, 120), licenceplate, vehmodel ", sqlwhere );
}
Sqlparameter [] Param = liststr. toarray ();
Return dbhelper. getdataset (SQL, Param );
}