ASP.NET MVC Filter SQL Query with parameters

Question

This is what it must be, when i first opened the page, the table must not be loaded yet, it will only display if i searched a "SSN or TIN", preferably must search using AJAX, i've been trying alot of codes but i don't know how to make it work.

This is my codes:

Index.chtml

<div class="row">
<div class="col-md-10">
    <label class="col-md-2">Search:</label>
    <div class="col-md-10">
        <input type="text" class="form-control input-sm" id="txtSSN">
    </div>
</div>
<div class="col-md-2">
    <button class="btn btn-success btn-sm" id="btnSearch" type="submit"><i class="fa fa-search" aria-hidden="true"></i></button>
</div>
</div>

<table class="table table-hover table-bordered" id="IPSCICODatatable">
<thead>
    <tr>
        <th>SSN or TIN</th>
        <th>Customer ID</th>
        <th>Account Number</th>
        <th>Date Transaction</th>
        <th>Trans Code</th>
        <th>Trans Description</th>
        <th>Amount</th>
        <th>Cash in</th>
        <th>Cash out</th>
        <th>Source</th>
    </tr>
</thead>
</table>

CICO.cs

public class CICO
{
public double? ssn_or_tin { get; set; }
public double? cusid { get; set; }
public double? accountNo { get; set; }
public string dateTrans { get; set; }
public int? transCode { get; set; }
public string transdescription_1 { get; set; }
public double? amount { get; set; }
public double? cashin { get; set; }
public double? cashout { get; set; }
public string source { get; set; }
public int SSN { get; set; }
}

HomeController.cs

public List<CICO> GetCICO()
{
List<CICO> cicos = new List<CICO>();
using (SqlConnection con = new SqlConnection())
{
    con.ConnectionString = str;
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = con;
        cmd.CommandTimeout = 180;
        cmd.CommandText = "select distinct i.ssn_or_tin,i.cusid,i.accountNo,i.dateTrans,i.transCode,i.transdescription_1,(i.debit)as amount,(coalesce(c.debit,0))as cashin,(coalesce(o.debit,0))as cashout,i.source from source_ips i left join (select * from source_cash_in_original where  transCode ='966') as c on (i.ssn_or_tin =c.ssntin or i.cusid=c.cusid or i.accountNo=c.accountNo) and i.dateTrans=c.dateTrans left join (select * from source_cash_out_original where  transCode ='936') as o on (i.ssn_or_tin =o.ssntin or i.cusid=o.cusid or i.accountNo=o.accountNo) and i.dateTrans=o.dateTrans where i.ssn_or_tin = '2369646' and i.transCode ='131' and (i.dateTrans between '1/22/2015' and '1/22/2015') order by i.dateTrans ASC";
        con.Open();
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    CICO cico = new CICO()
                    {
                        ssn_or_tin = sdr["ssn_or_tin"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["ssn_or_tin"]),
                        cusid = sdr["cusid"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cusid"]),
                        accountNo = sdr["accountNo"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["accountNo"]),
                        dateTrans = sdr["dateTrans"].ToString(),
                        transCode = sdr["transCode"] == DBNull.Value ? (int?)null : Convert.ToInt32(sdr["transCode"]),
                        transdescription_1 = sdr["transdescription_1"].ToString(),
                        amount = sdr["amount"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["amount"]),
                        cashin = sdr["cashin"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cashin"]),
                        cashout = sdr["cashout"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cashout"]),
                        source = sdr["source"].ToString()
                    };
                    cicos.Add(cico);
                }
            }
        }
        con.Close();
    }
}
return cicos;
}

public JsonResult GetAllCICO()
    {
        var cicos = GetCICO().ToList();
        var jsonResult = Json(new{data = cicos}, JsonRequestBehavior.AllowGet);
        jsonResult.MaxJsonLength = int.MaxValue;
        return jsonResult;
    }

Javascript:

<script type="text/javascript">
$(document).ready(function () {
    $("#btnSearch").click(function() {
        var table = $('#IPSCICODatatable').DataTable({
            "ajax": {
                "url": '/Home/GetAllCICO',
                "type": "get",
                "datatype": "json",
                "data": function (d) {
                    d.searchParameters = {};
                    d.searchParameters.ssn_or_tin = $('#txtSSN').val();
                }
            },
            "columns": [
                { "data": "ssn_or_tin", "autoWidth": true },
                { "data": "cusid", "autoWidth": true },
                { "data": "accountNo", "autoWidth": true },
                { "data": "dateTrans", "autoWidth": true },
                { "data": "transCode", "autoWidth": true },
                { "data": "transdescription_1", "autoWidth": true },
                { "data": "amount", "autoWidth": true },
                { "data": "cashin", "autoWidth": true },
                { "data": "cashout", "autoWidth": true },
                { "data": "source", "autoWidth": true }
            ]
        });
    });
});
</script>

C.Fasolin · Accepted Answer · 2017-07-15 09:51:20Z

hi you can use queryParameter you function became:

 public List<CICO> GetCICO(List<SqlParameter> queryParams)
    {
        string str=string.Empty;
        List<CICO> cicos = new List<CICO>();
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = str;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandTimeout = 180;
                string q = " SELECT distinct i.ssn_or_tin,i.cusid,i.accountNo,i.dateTrans,i.transCode,i.transdescription_1,(i.debit) as amount,(coalesce(c.debit, 0)) as cashin,(coalesce(o.debit, 0)) as cashout,i.source";
                q += " FROM source_ips i ";
                q += " LEFT JOIN (SELECT * FROM source_cash_in_original where transCode = '966') as c ON(i.ssn_or_tin = c.ssntin or i.cusid = c.cusid or i.accountNo = c.accountNo) and i.dateTrans = c.dateTrans";
                q += " LEFT JOIN(select * from source_cash_out_original where transCode = '936') as o on(i.ssn_or_tin = o.ssntin or i.cusid = o.cusid or i.accountNo = o.accountNo) and i.dateTrans = o.dateTrans";
                q += " WHERE (i.ssn_or_tin = @ssn_or_tin OR @ssn_or_tin='' ) and i.transCode = '131' and(i.dateTrans between '1/22/2015' and '1/22/2015') order by i.dateTrans ASC";

                cmd.Parameters.AddRange(queryParams.ToArray());

you can note

WHERE (i.ssn_or_tin = @ssn_or_tin OR @ssn_or_tin='' )

here you the other methods

 public class SearchParameters{
        public string ssn_or_tin { get; set; }
        public SearchParameters()
        {
            this.ssn_or_tin = string.Empty;
        }

        internal List<SqlParameter> ToSqlParameterList()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("@ssn_or_tin", this.ssn_or_tin));
            return parameters;
        }
    }
[HttpPost]
    public JsonResult GetAllCICO(SearchParameters searchParameters=null)
    {
        searchParameters = searchParameters ?? new SearchParameters();
        List<SqlParameter> parameters = searchParameters.ToSqlParameterList();
        var cicos = GetCICO(parameters).ToList();
        var jsonResult = Json(new { data = cicos }, JsonRequestBehavior.AllowGet);
        jsonResult.MaxJsonLength = int.MaxValue;
        return jsonResult;
    }

then you must modify your javascript code

 var table=$('#IPSCICODatatable').DataTable({
            "ajax": {
                "url": '/Home/GetAllCICO',
                "type": "get",
                "datatype": "json",
                "data": function (d) {
                    d.searchParameters = {};
                    d.searchParameters.ssn_or_tin = $('#txtSSN').val();
                    //...
                }
            },
            "columns": [
                { "data": "ssn_or_tin", "autoWidth": true },
                { "data": "cusid", "autoWidth": true },
                { "data": "accountNo", "autoWidth": true },
                { "data": "dateTrans", "autoWidth": true },
                { "data": "transCode", "autoWidth": true },
                { "data": "transdescription_1", "autoWidth": true },
                { "data": "amount", "autoWidth": true },
                { "data": "cashin", "autoWidth": true },
                { "data": "cashout", "autoWidth": true },
                { "data": "source", "autoWidth": true }
            ]
        });

My working poc

Controller

 public class SOF45114671Controller : Controller
    {
        // GET: SOF45114671_
        public ActionResult Index()
        {
            return View();
        }

        private List<CICO> GetCICO(List<SqlParameter> queryParams)
        {
            string str = string.Empty;
            List<CICO> cicos = new List<CICO>();
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = str;
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandTimeout = 180;
                    string q = " SELECT distinct i.ssn_or_tin,i.cusid,i.accountNo,i.dateTrans,i.transCode,i.transdescription_1,(i.debit) as amount,(coalesce(c.debit, 0)) as cashin,(coalesce(o.debit, 0)) as cashout,i.source";
                    q += " FROM source_ips i ";
                    q += " LEFT JOIN (SELECT * FROM source_cash_in_original where transCode = '966') as c ON(i.ssn_or_tin = c.ssntin or i.cusid = c.cusid or i.accountNo = c.accountNo) and i.dateTrans = c.dateTrans";
                    q += " LEFT JOIN(select * from source_cash_out_original where transCode = '936') as o on(i.ssn_or_tin = o.ssntin or i.cusid = o.cusid or i.accountNo = o.accountNo) and i.dateTrans = o.dateTrans";
                    q += " WHERE (i.ssn_or_tin = @ssn_or_tin OR @ssn_or_tin='' ) and i.transCode = '131' and(i.dateTrans between '1/22/2015' and '1/22/2015') order by i.dateTrans ASC";

                    cmd.Parameters.AddRange(queryParams.ToArray());
                    cmd.CommandText = q;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        if (sdr.HasRows)
                        {
                            while (sdr.Read())
                            {
                                CICO cico = new CICO()
                                {
                                    ssn_or_tin = sdr["ssn_or_tin"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["ssn_or_tin"]),
                                    cusid = sdr["cusid"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cusid"]),
                                    accountNo = sdr["accountNo"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["accountNo"]),
                                    dateTrans = sdr["dateTrans"].ToString(),
                                    transCode = sdr["transCode"] == DBNull.Value ? (int?)null : Convert.ToInt32(sdr["transCode"]),
                                    transdescription_1 = sdr["transdescription_1"].ToString(),
                                    amount = sdr["amount"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["amount"]),
                                    cashin = sdr["cashin"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cashin"]),
                                    cashout = sdr["cashout"] == DBNull.Value ? (double?)null : Convert.ToDouble(sdr["cashout"]),
                                    source = sdr["source"].ToString()
                                };
                                cicos.Add(cico);
                            }
                        }
                    }
                    con.Close();
                }
            }
            return cicos;
        }

        [HttpPost]
        public JsonResult GetAllCICO(SearchParameters searchParameters = null)
        {
            searchParameters = searchParameters ?? new SearchParameters();

            // this lines get info from your DB
            //List<SqlParameter> parameters = searchParameters.ToSqlParameterList();
            //var cicos = this.GetCICO(parameters).ToList();

            //this lines is my My db - you can remove
            var cicoreps = new List<CICO>();
            cicoreps.Add(new CICO { ssn_or_tin = 1, accountNo = 1 });
            cicoreps.Add(new CICO { ssn_or_tin = 2, accountNo = 2 });
            // this line emulate your query into DB - you can remove
            var cicos = cicoreps.Where(i => i.ssn_or_tin.ToString() == searchParameters.ssn_or_tin || string.IsNullOrEmpty(searchParameters.ssn_or_tin) ).ToList();


            //your code
            var jsonResult = Json(new { data = cicos }, JsonRequestBehavior.AllowGet);
            jsonResult.MaxJsonLength = int.MaxValue;
            return jsonResult;
        }
    }

models

public class CICO
{
    public double? ssn_or_tin { get; set; }
    public double? cusid { get; set; }
    public double? accountNo { get; set; }
    public string dateTrans { get; set; }
    public int? transCode { get; set; }
    public string transdescription_1 { get; set; }
    public double? amount { get; set; }
    public double? cashin { get; set; }
    public double? cashout { get; set; }
    public string source { get; set; }
    public int SSN { get; set; }
}

public class SearchParameters
{
    public string ssn_or_tin { get; set; }
    public SearchParameters()
    {
        this.ssn_or_tin = string.Empty;
    }

    internal List<SqlParameter> ToSqlParameterList()
    {
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@ssn_or_tin",  this.ssn_or_tin??string.Empty));
        return parameters;
    }
}

Html / js

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link rel="stylesheet" href="//cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" />
    <link href="~/Content/bootstrap.css" rel="stylesheet" />
</head>
<body>

    <div class="container">

        <div class="row">
            <div class="col-md-10">
                <label class="col-md-2">Search:</label>
                <div class="col-md-10">
                    <input type="text" class="form-control input-sm" id="txtSSN">
                </div>
            </div>
            <div class="col-md-2">
                <button class="btn btn-success btn-sm" id="btnSearch" type="submit"><i class="fa fa-search" aria-hidden="true"></i> SEARCH</button>
            </div>
        </div>

        <table class="table table-hover table-bordered" id="IPSCICODatatable">
            <thead>
                <tr>
                    <th>SSN or TIN</th>
                    <th>Customer ID</th>
                    <th>Account Number</th>
                    <th>Date Transaction</th>
                    <th>Trans Code</th>
                    <th>Trans Description</th>
                    <th>Amount</th>
                    <th>Cash in</th>
                    <th>Cash out</th>
                    <th>Source</th>
                </tr>
            </thead>
        </table>


    </div><!-- /.container -->


    <script src="https://code.jquery.com/jquery-1.12.4.min.js"
            integrity="sha256-ZosEbRLbNQzLpnKIkEdrPv7lOy9C27hHQ+Xp8a4MxAQ="
            crossorigin="anonymous"></script>

    <script src="~/Scripts/bootstrap.js"></script>
    <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var table = $('#IPSCICODatatable').DataTable({
                "ajax": {
                    "url": '/SOF45114671/GetAllCICO',
                    "type": "POST",
                    "datatype": "json",
                    "data": function (d) {
                        d.searchParameters = {};
                        d.searchParameters.ssn_or_tin = $('#txtSSN').val();
                        //...
                    }
                },
                "columns": [
                    { "data": "ssn_or_tin", "autoWidth": true },
                    { "data": "cusid", "autoWidth": true },
                    { "data": "accountNo", "autoWidth": true },
                    { "data": "dateTrans", "autoWidth": true },
                    { "data": "transCode", "autoWidth": true },
                    { "data": "transdescription_1", "autoWidth": true },
                    { "data": "amount", "autoWidth": true },
                    { "data": "cashin", "autoWidth": true },
                    { "data": "cashout", "autoWidth": true },
                    { "data": "source", "autoWidth": true }
                ]
            });

            $('#btnSearch').on("click", function (e) {
                alert("btnSearch_click");
                table.ajax.reload();

            });


        })

    </script>

</body>
</html>

i trucate your code where i finish my develop... the command text is after cmd.Parameters.AddRange(queryParams.ToArray()); and its value is q please verify if query is valid.
Yeah the query is valid, thanks, i will now test with the javascript
sorry i note my error, I change http verb on js and in head of GetAllCICO signature method

Collectives™ on Stack Overflow

ASP.NET MVC Filter SQL Query with parameters

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related