How to put between within a code query?

Question

I have the following consultation and I would like to put in the clause Where 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 didn’t 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");
}

Answer 1

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 as an answer. but it’s good to hear the recommended ones given in comments and in the other answer.}

Answer 2

Gypsy is right, this approach is not correct by the possibility of SQL Injection, even if it is working. I suggest you read this article from MSDN: http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110). aspx

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