I'm having my app make a AJAX request for records from a SQL Server 2008 DB, by using .NET to handle the server side code. Currently, everything is being returned in DOM elements and , but I'd rather get the records fed into an array on the server-side, so when the response is sent, I cant just loop through them on my client side and manipulate as needed. I created the code below from another online resource doing the same thing, but he wanted his in the DOM Products elements (I'm a JavaScriptStack developer, so excuse my nativity).

.Net Code:

    {
        // 1. Initialize XML Response
        Response.ContentType = "text/xml";
        if (Request.HttpMethod.ToUpper() == "GET")
        {
            // 2. Open connection & Create command
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    // Select all by default (Unless there is an ID)
                    command.CommandText = @"SELECT *****(Hidden)";

                    // 3. Format XML as needed
                    // - Use <Products> as document element
                    // - Use <Product> as the record name
                    DataSet set = new DataSet("Products");
                    set.Tables.Add(new DataTable("Product"));
                    DataTable table = set.Tables[0];
                    try
                    {
                        // 4. Get Database content into DataTable
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();
                        table.Load(reader, LoadOption.Upsert);
                    } catch (Exception ex) {
                        // Handle exception 
                        writer.WriteLine(@"<Error>" + ex.Message + "</Error>");
                    }
                    // 5. Render XML Output
                    table.WriteXml(writer);
                }
            }
        }
    }
}

Note: Excuse the incorrect SQL statement, I dont feel comfortable posting our actual query on SO

Related posts

Recent Viewed