Run db query (sql server 2005) with ajax. Is it possible?

Question

I never worked with ajax.

I need to know if it is possible to use ajax to run a query on the db (sql server 2005).

My target is to run a query with refreshing the page.

Have you got any ideas?

Answer 1

Directly, no. And that is a very good thing, since the JavaScript is generally running on an untrusted machine.

But it should be pretty easy to have your AJAX fire off a callback (e.g. a post) and then have that do the query on the server side.

Answer 2

As MarkusQ has said, it is not possible to do this directly, but you can call a web service or page method to perform the database query and return the result to the client side.

Something like this for a Page Method (this is off the top of my head and untested. I'm also making the assumption you're using asp.net 3.5)

public partial class _Default : Page 
{
  [WebMethod]
  public static string PerformDatabaseQuery()
  {
      using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString)
      {
          using (SqlCommand cmd = con.CreateCommand())
          {
              cmd.CommandText = "SELECT records FROM myTable";
              cmd.CommandType = CommandType.Text;

              con.Open();

              SqlDataReader reader = cmd.ExecuteReader();
              StringBuilder sb = new StringBuilder();

              while (reader.Read())
              {
                   sb.Append((string)reader["records"]); 
                   //May want to do some other formatting here
              }

              return sb.ToString();
          }
      }
  }
}

then call the page method from the client side. I'm going to use jQuery here

$.ajax({
  type: "POST",
  url: "Default.aspx/PerformDatabaseQuery",
  data: "{}",
  contentType: "application/json; charset=utf-8",
  dataType: "json",
  success: function(result) {
    //Do something with the returned data
  }
});

You can call it using JavaScript and the JavaScript proxy class that is generated when you set EnablePageMethods = true on the ScriptManager control.

function CallPerformDatabaseQuery()
{
    PageMethods.PerformDatabaseQuery(onSuccess,onFailed);
}

function onSuccess(result,userContext,methodName)
{
  // Do something with returned data
}

function onFailed(error,userContext,methodName)
{
  alert("An error occurred")
}

Answer 3

AJAX => Client side PHP / ASP / X => Server side (databases as well)

Basically, anything client side is happening "right now", whilst server side scripts "already happened".

You can't perform a query to a database with JS or anything alike, AJAX is nothing but an user experience ilusion, even when we can speak of "fetching DB records through AJAX" that's not actually what's happening.

If AJAX is just more JavaScript (Asynchronus JS, that is, happening in it's own "timeline" / thread, not the main one) then that would mean that fetching data from a DB (or doing anything through AJAX for that matter) would be the same as having a link to scripts that statically do that, meaning, you click here, you're taken to a page that fetches (server side) and displays (client side) the information.

Now imagine that you just do exactly that, but instead of taking your main thread to that action, you fire an asynchronus thread (that will work just as any other link, for instance) which will post/get data and get an X response, which you can handle through JS later on, thus, giving you the ilusion of "real time queryes".