Pass SQL as parameter and return database as xml in asp.net
Account Home | Help | Blog | Contact us | Log Out


Welcome to Kbytes > Articles

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

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

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

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" %>
<script language="VB" runat="server">
 
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

© 2004 - 2018 1 Oak Hill Grove Surbiton Surrey KT6 6DS Phone: +44(020)82617371