Mega Code Archive
Executing Dynamic Queries using Provider Independant Code
<![CDATA[
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<script runat="server">
void Page_Load(object source, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable table = DbProviderFactories.GetFactoryClasses();
ddlProvider.DataSource = table;
ddlProvider.DataTextField = "Name";
ddlProvider.DataValueField = "InvariantName";
ddlProvider.DataBind();
}
}
void btnExecute_Click(object sender, EventArgs e)
{
string sql = "Select * from " + txtTableName.Text;
ExecuteQuery(ddlProvider.SelectedItem.Value, sql);
}
void ExecuteQuery(string providerName, string sql)
{
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
string connectionString = CreateConnectionString(factory.CreateConnectionStringBuilder());
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = conn.CreateCommand();
adapter.SelectCommand.CommandText = sql;
DataTable table = new DataTable("Table");
adapter.Fill(table);
gridResults.DataSource = table;
gridResults.DataBind();
}
}
}
private string CreateConnectionString(DbConnectionStringBuilder builder)
{
builder.Add("Integrated Security", true);
builder.Add("Initial Catalog", txtDatabaseName.Text);
builder.Add("Data Source", txtServerName.Text);
return builder.ConnectionString;
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Executing Dynamic Queries using Provider Independant Code</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>Select Provider:</td>
<td><asp:DropDownList ID="ddlProvider" runat="server" Width="190px"/></td>
</tr>
<tr>
<td>Server Name:</td>
<td><asp:TextBox ID="txtServerName" runat="server" Width="183px"/></td>
</tr>
<tr>
<td>Database Name: </td>
<td><asp:TextBox ID="txtDatabaseName" runat="server" Width="180px"/></td>
</tr>
<tr>
<td>Table Name: </td>
<td><asp:TextBox ID="txtTableName" runat="server" Width="176px"/></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnExecute" runat="server" OnClick="btnExecute_Click" Text="Execute Query" />
</td>
</tr>
</table>
<asp:GridView HeaderStyle-BackColor="Control"
HeaderStyle-ForeColor="Brown"
RowStyle-BackColor="Snow"
runat="Server"
ID="gridResults">
</asp:GridView>
</div>
</form>
</body>
</html>
]]>