How to put between inside a query in code?

Question

How to put between inside a query in code?

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I have the following query and would like to put in the Where clause the condition of being within a certain period.

public static DataTable stCloseCaseAll(int schoolId, string dataInicial, string datafinal)
{
    var sb = new StringBuilder();

    sb.Append(" SELECT C.SchoolID, C.DateClose, C.UserAdminID, C.CloseCaseID, C.Balance, C.PCName, U.Name ");
    sb.Append(" FROM  sales.CloseCase AS C INNER JOIN ");
    sb.Append(" [authorization].UserAdmin AS U ON C.UserAdminID = U.UserAdminID ");
    sb.Append(" WHERE  (C.SchoolID = " + schoolId + ") AND ... ");

    return AcessoDados.SelectDados(sb.ToString(), "conexao");
}

I'm getting the dates for the query through a textbox .

namespace IASD.ASCS.WebForm.reports.closemonth.Report
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            ReportDocument crReportDocument = new ReportDocument();
            dsClosemonth dsRelatorio = new dsClosemonth();

            int schoolId = 0;
            string dataInicial = null;
            string dataFinal = null;
            string schoolName = "";

            if (!string.IsNullOrEmpty(Request.QueryString[0]))
            {
                schoolId = int.Parse(Request.QueryString[0]);
            }

            if (!string.IsNullOrEmpty(Request.QueryString[1]))
            {
                schoolName = Request.QueryString[1];
            }

            if (!string.IsNullOrEmpty(Request.QueryString[2]))
            {
                dataInicial = Request.QueryString[2];
            }

            if (!string.IsNullOrEmpty(Request.QueryString[3]))
            {
                dataFinal = Request.QueryString[3];
            }

            dsRelatorio.CloseCase.Merge(Bussiness.CloseCase.stCloseCaseAll(schoolId, dataInicial, dataFinal));

            IB.Schools obj = IB.Schools.ListSchoolName(schoolId);

            crReportDocument.Load(Server.MapPath("crClosemonth.rpt"));
            crReportDocument.SetDataSource(dsRelatorio);
            crReportDocument.SetParameterValue("SchoolId", schoolId);
            crReportDocument.SetParameterValue("SchoolName",schoolName);
            crReportDocument.SetParameterValue("DataInicial", dataInicial);
            crReportDocument.SetParameterValue("DataFinal", dataFinal);

            //Utilizando exportação para pdf
            BinaryReader stream = new BinaryReader(crReportDocument.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat));

            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.ContentType = "application/pdf";
            HttpContext.Current.Response.BinaryWrite(stream.ReadBytes(Convert.ToInt32(stream.BaseStream.Length)));
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();
        }
    }
}

NavigateURL:

protected void LoadNavigateUrl()
{
    if ((!string.IsNullOrEmpty(ddlSchool.SelectedValue) && (!string.IsNullOrEmpty(txtDateInitial.Text)) &&
         (!string.IsNullOrEmpty(txtDateEnd.Text))))
    {
        hlPrint.Enabled = true;
        hlPrint.Visible = true;
        hlPrint.NavigateUrl = "/reports/closemonth/report/?SchoolID="+ddlSchool.SelectedValue+"&SchoolName="+ddlSchool.SelectedItem+"&InitialDate="+txtDateInitial.Text+"&EndDate="+txtDateEnd.Text;
    }
}

I tried that way but it did not work

public static DataTable stCloseCaseAll(int schoolId, string dataInicial, string datafinal)
{
    var sb = new StringBuilder();

    var @dataInincial = dataInicial;
    var @datafnal = datafinal;

    sb.Append(" SELECT C.SchoolID, C.DateClose, C.UserAdminID, C.CloseCaseID, C.Balance, C.PCName, U.Name ");
    sb.Append(" FROM  sales.CloseCase AS C INNER JOIN ");
    sb.Append(" [authorization].UserAdmin AS U ON C.UserAdminID = U.UserAdminID ");
    sb.Append(" WHERE  (C.SchoolID = " + schoolId + ") AND (DAY(C.DateClose) between @dataInicial and @datafinal )" );

    return AcessoDados.SelectDados(sb.ToString(), "conexao");
}

sql c# string .net ado.net

asked by anonymous 15.05.2014 / 19:41

2 answers

Django - How to redirect the user to the other pages of the application? Problems with AsyncTask crash the application

score 1 · Answer 1

Gypsy is right, this approach is not correct because of the possibility of SQL injection, even if it is working. I suggest you read this MSDN article: link

Use the command.Parameters.Add method, it will make your life easier with various data types in the parameters.

score 1 · Answer 2

Solution (The problem was in converting the dates).

sb.Append(" SELECT C.SchoolID, C.DateClose, C.UserAdminID, C.CloseCaseID, C.Balance, C.PCName, U.Name ");
sb.Append(" FROM  sales.CloseCase AS C INNER JOIN ");
sb.Append(" [authorization].UserAdmin AS U ON C.UserAdminID = U.UserAdminID ");
sb.Append(" WHERE  C.SchoolID = " + schoolId);
sb.Append(" AND C.DateClose BETWEEN " + "CONVERT(DATETIME ,'" +    DateTime.Parse(dataInicial).ToString("yyyy/MM/dd") + "') AND " + "CONVERT(DATETIME ,'" + DateTime.Parse(datafinal).ToString("yyyy/MM/dd") + " 23:59:59')");

I just put the OP solution in response. but it's good to hear the recommendations given in the comments and the other answer.