Executing Dynamic Queries using Provider Independant Code

<%@ 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>
Executing Dynamic Queries using Provider Independant Code : DbProviderFactories « ADO.net Database « ASP.Net

Related examples in the same category
