Click here to Skip to main content
13,737,212 members
Rate this:
 
Please Sign up or sign in to vote.
I have been tasked with building an HTML5 website. The website will need to connect to an MS SQL database and pass data from it to the Website for the user to review. I have to build a Web Service which connects to the SQL database. The Methods I had intended to pass data to the Web page to populate a List box and to use the listbox selection populate a graph with the data. I can see the data from the IIS server and the WSDL website. I can see the data using SOAPUI if I point it at the Web address. However, when I run the AJAX script within Javascript it is not returning the data. I am puzzled.

I have attached the Webservice Code as well as the code I am using in the Javascript to call the web service.

What I have tried:

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.Script.Serialization;





[WebService(Namespace = "http://tempuri.org/",
    Description = "Database Connection Service",
    Name = "UnivariateWebService")]
//[System.ComponentModel.ToolboxItem(false)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
 [System.Web.Script.Services.ScriptService]

public class Service : System.Web.Services.WebService
{
    public Service () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public DataSet GetBatchData()
    {

        //Modify this connection string to use your SQL Server and log on information.
        var con = new SqlConnection("server=DEVELOPMENT-SER;uid=******;pwd=******;database=UnivariateRuns");

        //Open the Customers table to serve as the parent table.
        var daRunSet = new SqlDataAdapter("Select * From dbo.Products Order By product desc",
            con);

        //Create a client-side DataSet to hold the Customers and Orders tables.
        var ds = new DataSet();

        //Explicitly open the connection to allow explicit closing.
        con.Open();

        //Fill the DataSet with the Customers table and the Orders table.
        daRunSet.Fill(ds, "Batches");
        //string myJsonString = (new JavaScriptSerializer()).Serialize(ds);     // might need to phase the dataset as a string
        //Explicitly close the connection - do not wait for garbage collection.
        con.Close();

        //Return the DataSet to the client.
        return ds;
    }

    [WebMethod]
    public DataSet GetSampleData(string product)
    {
       
        //Modify this connection string to use your SQL Server and log on information.
        var con = new SqlConnection("server=DEVELOPMENT-SER;uid=******;pwd=******;database=UnivariateRuns");

        //Open the Customers table to serve as the parent table.
        var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like '%" + product + "%' Order by product desc", con);

        //Create a client-side DataSet to hold the Customers and Orders tables.
        var ds=new DataSet();

        //Explicitly open the connection to allow explicit closing.
        con.Open();

        //Fill the DataSet with the Customers table and the Orders table.
        daRunSet.Fill(ds, "RunSet");
        //daOrders.Fill(ds, "Orders");

        //Explicitly close the connection - do not wait for garbage collection.
        con.Close();

        //Return the DataSet to the client.
        return ds;
    }
    
}



Javascript Function:

function DataConnect() {
   		$.ajax({
   			type: 'GET',
   			url: 'http://192.168.1.103/Web%20Service/Service.asmx/GetBatchData',
   			dataType: 'json',
   			contentType: 'application/json;charset=utf-8',
   			success: function(responce) {
   				var names = response.d;
                 alert(names);
            },
   			failure: function(error) {
   				 alert(response.d); 
   			}
   		});
   };

// How do I get the data passed to the Plot Array from the AJAX call?
Posted 12-Jun-18 9:16am
Updated 14-Jun-18 11:34am
Comments
Richard Deeming 12-Jun-18 15:24pm
   
var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like '%" + product + "%' Order by product desc", con);


Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like @product Order by product desc", con);
daRunSet.SelectCommand.Parameters.AddWithValue("@product", "%" + product + "%");
‭011111100010‬ 12-Jun-18 15:44pm
   
I don't think JavaScript can do anything with a DataSet. You'll need to jsonify it or return it as string.
12-Jun-18 19:24pm
   
So I was able to convert one of my Datasets to JSON. But the other Dataset which uses the parameter is giving me a System.OutOfMemoryException error. I think this might be because of the amount of data this particular query returns.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

using System.Data;
using System.Web.Services;
using System.Data.SqlClient;
using Newtonsoft.Json;


[WebMethod]
    public string GetSampleData(string product)
    {
       
        //Modify this connection string to use your SQL Server and log on information.
        var con = new SqlConnection("server=DEVELOPMENT-SER;uid=*****;pwd=******;database=UnivariateRuns");

        //Open the Customers table to serve as the parent table.
        var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like @product Order by product desc", con);
        daRunSet.SelectCommand.Parameters.AddWithValue("@product", "%" + product + "%");

        //Create a client-side DataSet to hold the Customers and Orders tables.
        var ds=new DataSet();

        

        //Explicitly open the connection to allow explicit closing.
        con.Open();

        //Fill the DataSet with the Customers table and the Orders table.
        daRunSet.Fill(ds, "RunSet");
        DataTable table = new DataTable();
        DataColumn idColumn = new DataColumn("id", typeof(int));
        idColumn.AutoIncrement = true;

        DataColumn itemColumn = new DataColumn("item");
        table.Columns.Add(idColumn);
        table.Columns.Add(itemColumn);
        ds.Tables.Add(table);

        for (int i = 0; i < 2; i++)
        {
            DataRow newRow = table.NewRow();
            newRow["item"] = "item " + i;
            table.Rows.Add(newRow);
        }

        ds.AcceptChanges();
        string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
        //daOrders.Fill(ds, "Orders");

        //Explicitly close the connection - do not wait for garbage collection.
        con.Close();
        
        //Return the DataSet to the client.
        return json;
    }


This is code is resulting in an Out of Memory Exception, is there a better way to arrange it?
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

was returning too big of dataset
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180920.1 | Last Updated 14 Jun 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100