Data Form Wizard in ASP.Net Part I

Having a Data Form Wizard will ease your development effort especially when it come to plain master forms like Customers, Clients, Employees, etc.

In this blog I will show you displaying tables and fields of a selected database.

This example makes use of web.config and appSettings for storing Connection String values. If you are not familiar working with AppSetting visit my blog Web.config's appSettings section to avoid hard coded memory variable or my video Asp.Net web.config file and appSettings.

I assume you are familiar with Wizard control and T-Sql Command like sp_helpdb, SELECT * FROM sysobjects and sp_help . If not visit my article on Transact Sql.

In the second part of blog I will take up the issue of creating the actual form with the fields selected.

The code is pretty simple except the final part ie. filling the fields.
sp_help returns multiple resultsets. What we want is the coulmn_name which is present in the second resultset. That is why i am invoking the SqlDataReader's NextResult method.

using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();
SqlCommand cmd;
SqlDataReader dr;

string strDb;
protected void Page_Load(object sender, EventArgs e)
{
cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("Cnn"));
cnn.Open();
da = new SqlDataAdapter("sp_helpdb", cnn);
da.Fill(ds, "Db");
if (!IsPostBack)
{
ddlDb.DataSource = ds.Tables["Db"];
ddlDb.DataTextField = "name";
DataBind();
}
}
protected void ddlDb_SelectedIndexChanged(object sender, EventArgs e)
{

strDb = ddlDb.SelectedItem.ToString();
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=" + strDb);
da = new SqlDataAdapter("SELECT * FROM sysobjects WHERE xtype = 'u'", cnn);
da.Fill(ds, "Tb");
ddlTable.DataSource = ds.Tables["Tb"];
ddlTable.DataTextField = "name";
DataBind();
}

Make sure that you set the AutoPostBack property of DropDownList to True.

protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
{
cmd = new SqlCommand("sp_help " + ddlTable.SelectedItem.ToString(), cnn);
dr = cmd.ExecuteReader();
dr.NextResult();
while (dr.Read())
lstFields.Items.Add(dr["column_name"].ToString());
}

using System.Data.SqlClient; using System.IO;

Windows Forms Code

Here is the equivalent code of the first part in Windows Forms. Here I am using TabControl.

using System.Data.SqlClient;

SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();
SqlCommand cmd;
SqlDataReader dr;

string strDb;
private void Form1_Load(object sender, EventArgs e)
{
cnn = new SqlConnection("Integrated Security=sspi; Data Source= .\\sqlexpress; Initial Catalog="+comboBox1.Text);
cnn.Open();
da = new SqlDataAdapter("sp_helpdb", cnn);
da.Fill(ds, "Db");

comboBox1.DataSource = ds.Tables["Db"];
comboBox1.DisplayMember = "name";

}

private void btnNext_Click(object sender, EventArgs e)
{
tabControl1.SelectedIndex = 1;
strDb = comboBox1.Text.ToString();
cnn = new SqlConnection("Integrated Security=sspi;Data Source=.\\sqlexpress;Initial Catalog=" + strDb);
cnn.Open();
da = new SqlDataAdapter("SELECT * FROM sysobjects WHERE xtype = 'u'", cnn);
da.Fill(ds, "Tb");
ddlTable.DataSource = ds.Tables["Tb"];
ddlTable.DisplayMember = "name";
}

private void btnTblNext_Click(object sender, EventArgs e)
{
tabControl1.SelectedIndex = 2;
cmd = new SqlCommand("sp_help " + ddlTable.Text.ToString(), cnn);
dr = cmd.ExecuteReader();
dr.NextResult();
while (dr.Read())
lstFields.Items.Add(dr["column_name"].ToString());

dr.Close();
}