Pass SQL as parameter and return database as xml in asp.net

Posted By: siteadmin on 21/03/2013 15:41:00

This asp.net script will connect to your SQL 2008/2012 database and return any SQL command you have sent in the querystring of the browser as XML.

So it can be called by:

http://example.com/export.aspx?sql=SELECT%20TOP%20100%20[sch_id]%20,[sch_name]%20FROM%20[teachers].[dbo].[sch_details]%20ORDER%20BY%20[sch_name]%20desc

Clearly this is not for production environments, but it helped us when we wanted to populate our expression blend combo dropdown list.

export.aspx:

<%@ Page Language="VB" Debug="true" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.XML" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Data.SQLClient" %>

Sub Page_Load(Source as Object, E as EventArgs)

Dim cnn As SqlConnection

Dim connectionString As String

Dim sqlAdp As SqlDataAdapter

Dim ds As New DataSet

connectionString = "Data Source=localhost; Initial Catalog=teachers; User ID=teacher_admin; Password="

cnn = New SqlConnection(connectionString)

cnn.Open()

'sqlAdp = New SqlDataAdapter("SELECT TOP 10 [sch_id] ,[sch_name] FROM [teachers].[dbo].[sch_details] ORDER BY [sch_name] desc ", cnn)

sqlAdp = New SqlDataAdapter(Request.QueryString("sql"), cnn)

cnn.Close() 'connection close here , that is disconnected from data source

sqlAdp.Fill(ds)

Response.Clear()

Response.ContentType = "text/xml"

response.Write("<XML>")

response.Write(ds.GetXml)

response.Write("</XML>")

end sub

</script>

blog comments powered by Disqus

Kbytes Home | Privacy Policy | Contact us | Testing Area