Skip to content

Instantly share code, notes, and snippets.

@jackdouglas
Created February 27, 2017 09:28
Show Gist options
  • Save jackdouglas/31f126a82c32652d35add06a77938adc to your computer and use it in GitHub Desktop.
Save jackdouglas/31f126a82c32652d35add06a77938adc to your computer and use it in GitHub Desktop.
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Collections.Generic" %>
<script runat=server>
Protected Class Result
Public head As List(Of String)
Public align As List(Of Integer)
Public data As List(Of List(Of String))
Public message As String
End Class
Protected Class AllResults
Public result As List(Of Result)
Public [error] As String
End Class
</script>
<%
Dim sr = New StreamReader(Request.InputStream)
Dim ser = New System.Web.Script.Serialization.JavaScriptSerializer()
Dim queries = ser.Deserialize(Of List(Of String))(sr.ReadToEnd())
sr.Close()
Dim user = "fiddle_" & Guid.NewGuid.ToString().replace("-","")
Dim password = "Aa0" & Guid.NewGuid.ToString().replace("-","")
Dim connection As SqlConnection
Dim command As SqlCommand
connection = New SqlConnection("Initial Catalog=master;Data Source=localhost;Integrated Security=False;User Id=sa;password=SUPERSECRETPASSWORD")
connection.Open()
command = connection.CreateCommand
command.CommandText = "create database " & user
command.ExecuteNonQuery()
command.CommandText = "create login " & user & " with password='" & password & "', default_database=" & user
command.ExecuteNonQuery()
connection.ChangeDatabase(user)
command.CommandText = "create user " & user & " for login " & user
command.ExecuteNonQuery()
command.CommandText = "grant connect to " & user
command.ExecuteNonQuery()
command.CommandText = "grant control on schema::dbo to " & user
command.ExecuteNonQuery()
command.CommandText = "grant create table to " & user
command.ExecuteNonQuery()
command.CommandText = "grant select,insert,update,delete on database::" & user & " to " & user
command.ExecuteNonQuery()
connection.Close()
Dim reader As SqlDataReader
Dim table As DataTable
Dim allResults As AllResults
Dim result As Result
Dim row As List(Of String)
Dim ret = New List(Of AllResults)
connection = New SqlConnection("Initial Catalog=" & user & ";Data Source=localhost;Integrated Security=False;User Id=" & user & ";password=" & password)
connection.Open()
command = connection.CreateCommand
command.CommandText = "SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON"
command.ExecuteNonQuery()
command = connection.CreateCommand
command.CommandText = "SET NUMERIC_ROUNDABORT OFF"
command.ExecuteNonQuery()
For Each query As String In queries
allResults = New AllResults
allResults.result = New List(Of Result)
allResults.[error] = ""
Try
command = connection.CreateCommand
command.CommandText = query
reader = command.ExecuteReader()
Do
result = New Result
result.message = ""
If reader.HasRows() Then
result.head = New List(Of String)
result.align = New List(Of Integer)
For i As Integer = 0 To reader.FieldCount-1
result.head.Add(reader.GetName(i))
result.align.Add(If(reader.GetDataTypeName(i)="int",0,1))
Next
result.data = New List(Of List(Of String))
For i As Integer = 0 To reader.FieldCount-1
result.data.Add(New List(Of String))
Next
While reader.Read()
For i As Integer = 0 To reader.FieldCount-1
result.data(i).Add(reader(i))
Next
End While
Else
If reader.RecordsAffected() > 0 Then
result.message = reader.RecordsAffected() & " rows affected"
End If
End If
allResults.result.Add(result)
Loop While reader.NextResult()
reader.Close()
Catch ex As SqlException
For i As Integer = 0 To ex.Errors.Count - 1
allResults.[error] = allResults.[error] & Environment.NewLine & "Line " & ex.Errors(i).LineNumber & ": " & ex.Errors(i).Message
Next i
End Try
ret.Add(allResults)
Next
connection.Close()
Response.Write(ser.Serialize(ret))
connection = New SqlConnection("Initial Catalog=master;Data Source=localhost;Integrated Security=False;User Id=sa;password=SUPERSECRETPASSWORD")
connection.Open()
command = connection.CreateCommand
command.CommandText = "alter database " & user & " set single_user with rollback immediate"
command.ExecuteNonQuery()
command.CommandText = "drop database " & user
command.ExecuteNonQuery()
command.CommandText = "drop login " & user
command.ExecuteNonQuery()
connection.Close()
%>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment