Data table with AJAX C # ASP.NET SqlDataReader

Question

Data table with AJAX C # ASP.NET SqlDataReader

Navigation

#1 by (3 votes)

2

Hi, I'm working with ASP.Net / SqlDataReader / C # and razor I have the sig. complication ...
I'm trying to show a table using AJAX but I'm not sure I'm doing it right, so I understand I have to make a method for my query to be stored in a list, I do not know if this list should go in the controller or in My model. At this moment I have only got the printed answer, but I need to remove the JSON format and put it in a table. I would greatly appreciate some help or illustration. This is what I have.

<script src="~/Scripts/jquery-1.10.2.js"></script>
<div id="result"></div>
<input type="button" name="name" value="try me" onclick="DepListQuery()" />


<script>


    function DepListQuery() {


        $.ajax({
            type: 'GET',
            url: '@Url.Action("GetData","Home")',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {

                $('#result').text(response.message);
            },
            failure: function (response) {
                alert("hubo un error del diablo u.u");
            }
        });
    }



</script>

You are my controller

  //GetData function, simple ajax
        [HttpGet]
        public JsonResult GetData()
        {
            string stdb = "Data Source=DMX87025;Initial Catalog=DB_PCC;Integrated Security=True";
            SqlConnection conn = new SqlConnection(stdb);
            string sql = "SELECT *FROM[DB_PCC].[dbo].[Departments]";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();

            SqlDataReader rd = cmd.ExecuteReader();



            return Json(new { success = true, message = rd },
                          JsonRequestBehavior.AllowGet);
        }

sql javascript c# ajax asp.net

asked by E.Rawrdríguez.Ophanim 03.10.2017 в 23:17

source

1 answer

Compare the first character of the text Stop a setInterval () in JavaScript

score 3 · Accepted Answer

Consider the following:

The connection string goes in a configuration file, so you can modify that file without having to recompile the solution once it is in production.

The administration of queries to the database goes in a separate layer: Access to Data (DAO), Repository.

Check the performance of 2 queries SELECT : one with the * and the other specifying all the columns, Which gives you greater performance?

Enable CORS and specify that it only admits the domain of your application, otherwise, any person or bot can access the information from a browser.

Now, as the Squirtle meme would say: vamo al código:

[HttpGet]
public JsonResult GetData()
{
    string stdb = "Data Source=DMX87025;Initial Catalog=DB_PCC;Integrated Security=True";
    SqlConnection conn = new SqlConnection(stdb);
    string sql = "SELECT * FROM[DB_PCC].[dbo].[Departments]";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();

    List<ClaseConAtributosDeTuConsulta> listaParaJson = new List<ClaseConAtributosDeTuConsulta>();

    SqlDataReader rd = cmd.ExecuteReader();
    while (rd.Read())
    {
        listaParaJson.Add(new ClaseConAtributosDeTuConsulta(){
            PropiedadDeTuClaseInt32 = rd.GetInt32(rd.GetOrdinal("NombreDeLaColumnaInt32")),
            PropiedadDeTuClaseString = rd.GEtStrin(rd.GetOrdinal("NombreDeLaColumnaString")),
            PropiedadDeTuClaseDateTime = rd.GetDateTime(rd.GetOrdinal("NombreDeLaColumnaDateTime"))
            //En caso necesites manejar campos que sean nulos: rd.IsDBNull()
        });    
    }
    return Json(new { success = true, message = listaParaJson },JsonRequestBehavior.AllowGet);
}

With this, that console.log (response) should theoretically give you a response.success with the value true and a response.message with the list of elements.

On the front-end:

<table class="table table-striped" cellspacing="0">
    <colgroup>               
        <col>
        <col>
        <col>
        <col>
    </colgroup>
    <thead class="">
        <tr>
            <th class="" scope="col">PropiedadDeTuClaseInt32</th>
            <th class="" scope="col">PropiedadDeTuClaseString</th>
            <th class="" scope="col">PropiedadDeTuClaseDateTime</th>           
            <th class="" scope="col">Operaciones</th>   
        </tr>
    </thead>
    <tbody id="renderizando">
    </tbody>
</table>

<script>
function DepListQuery() {
    $.ajax({
        type: 'GET',
        url: '@Url.Action("GetData","Home")',
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (response) {
            $('#result').text(response.message);
            RenderizarTabla(response.message);
        },
        failure: function (response) {
            alert("hubo un error, mira el log");
            console.log(response);
        }
    });
}

function GetJsonDateTime(str) {
    return eval("new " + str.replace(/\//g, "") + ".toLocaleDateString('en-US')");
}

function RenderizarTabla(informacion){
    var table = $("#renderizando");
    $.each(informationToRender, function (rowIndex, r) {
        var row = $("<tr/>");

        var campoA = r["PropiedadDeTuClaseInt32"];
        var campoB = r["PropiedadDeTuClaseString"];
        var campoC = GetJsonDateTime(r["PropiedadDeTuClaseDateTime"]);

        row.append($("<td/>").text(campoA));
        row.append($("<td/>").text(campoB));
        row.append($("<td/>").text(campoC));
        //En caso necesites que haya algun enlace que ejecute una acción:
        row.append($("<td/>")
                            .append(
                            $('<a>', {
                                text: 'Editar',
                                href: 'Aqui va algun enlace'
                            })));
        table.append(row);
    });
}
</script>