Sorry I was so vague with my question. I am experienced with SQL Server but
fairly new to .Net. Anyway, here's the gist of my problem. I have a number
of small tables in SQL Server and want to write an aspx page that will allow
users to select a table from a list, populate a datagrid (or other control,
datatable, etc) with the rows from that table and update, add, delete
records. I want to write it so that as I specify new tables that the users
can access, the code, datagird and the update/delete/ inserts adjust
according to the selected table which will have a varying number of columns.
I have already created part of the page which allows displays the data from
the available tables in the datagrid (with an editcolumn), however I do not
know how to put together the update/add/deletes so that they are generic
enough to work for any table I select. In addition, most of my lookup
tables have a key that is an Identity column that I do not want to allow the
user to change (but need to retain for updates).
Basically I want to write an scaled down ASP.Net version of the tool on
www.genericdb.com. I could write something specific for each table to allow
adds/updates/deletes as I would a normal datagrid similar to the QuickStart
samples, but obviously want to avoid this as I may have to adjust or
replicate code whenever I add a new lookup table to the list.
My code so far is as follows:
<%@ Control Language="C#"
Inherits="ASPNET.StarterKit.Portal.PortalModuleControl" %>
<%@ Register TagPrefix="Portal" TagName="Title"
Src="~/DesktopModuleTitle.ascx" %>
<%@ Register TagPrefix="cc1" Namespace="StrengthControls.Scrolling"
Assembly="StrengthControls.Scrolling" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<script language="C#" runat="server">
//##########################################################################
#####################
// Common strings/variables used throughout the procedure
//##########################################################################
#####################
public SqlConnection con = new
SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
public DataSet myDataSet = new DataSet();
String SQLStatement = null;
//##########################################################################
#####################
// Clears the DataGird when the page loads initially
//##########################################################################
#####################
void Page_Load(Object sender, EventArgs e) {
if(!IsPostBack) {
}
}
//##########################################################################
#####################
// Calls the function that populates the DataGird when a linkbutton is
clicked and sets the
// title label based on the Table selected
//##########################################################################
#####################
void SelTable(Object sender, CommandEventArgs Args) {
Title.Text = "Data for Table: " + Args.CommandName;
ViewState["SelTbl"] = Args.CommandName.ToString();
BindGrid();
}
//##########################################################################
#####################
// Function to Edit the DataGrid
//##########################################################################
#####################
void DataGrid1_Edit(Object obj, DataGridCommandEventArgs e){
DataGrid1.EditItemIndex = (int)e.Item.ItemIndex;
BindGrid();
}
//##########################################################################
#####################
// Function to Update the DB information from the DataGrid
//##########################################################################
#####################
void DataGrid1_Update(object source, DataGridCommandEventArgs E) {
string Identity = (E.Item.Cells[1]).Text;
// ??????????????????????????????????????????????????????????????
// ??????????????????????????????????????????????????????????????
// ??????????????????????????????????????????????????????????????
}
//##########################################################################
#####################
// Function to Cancel the Update from the DataGrid
//##########################################################################
#####################
void DataGrid1_Cancel(Object obj, DataGridCommandEventArgs e) {
DataGrid1.EditItemIndex = -1;
BindGrid();
}
//##########################################################################
#####################
// Function to Bind the Data to the Datagrid when it is populated from the
Database
//##########################################################################
#####################
void BindGrid(){
DataGrid1.DataSource=GetData().Tables["xxx"].DefaultView;
DataGrid1.DataBind();
}
//##########################################################################
#####################
// Dataset populated from the Select Statement called from the Databind
//##########################################################################
#####################
private DataSet GetData(){
SQLStatement="Select * FROM " + ViewState["SelTbl"];
SqlDataAdapter myCommand = new SqlDataAdapter(SQLStatement, con);
myCommand.Fill(myDataSet, "xxx");
return myDataSet;
}
</script>
<html>
<head>
<style>
..links {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px;
font-weight: bold; color: #660000; height="18"
}
..title{FONT-FAMILY: Verdana; FONT-WEIGHT: 600; FONT-SIZE: 14;
text-decoration:underline;}
</style>
</head>
<body bottommargin="0" leftmargin="0" topmargin="0" rightmargin="0"
marginheight="0" marginwidth="0">
<cc1:SmartScroller id="SmartScroller1" runat="server" />
<portal:title runat="server" />
<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tbody>
<tr>
<td width="150" valign="top" rowspan="2">
<span class="links"><asp:LinkButton runat="server" id="Departments"
Text="Departments" CommandName="Departments" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="ProjectTypes"
Text="ProjectTypes" CommandName="ProjectTypes" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="ProjectStatuses"
Text="ProjectStatuses" CommandName="ProjectStatuses" OnCommand="SelTable"
/></span><br />
<span class="links"><asp:LinkButton runat="server" id="Services"
Text="Services" CommandName="Services" OnCommand="SelTable" /></span><br />
</td>
<td valign="top">
<asp:Label runat="server" id="Title" cssclass="title" /><br />
</td>
</tr>
<tr>
<td valign="top">
<asp
ataGrid id="DataGrid1" cellpadding="4" BackColor="#EEDDEE"
runat="server"
GridLines="None" BorderWidth="1px" BorderColor="DarkSlateBlue"
ForeColor="Black"
Font-Size="8pt"
OnEditCommand="DataGrid1_Edit"
OnUpdateCommand="DataGrid1_Update"
OnCancelCommand="DataGrid1_Cancel"<FooterStyle backcolor="Tan" />
<HeaderStyle font-bold="True" backcolor="DarkSlateBlue"
ForeColor="white" />
<PagerStyle horizontalalign="Center" forecolor="DarkSlateBlue"
backcolor="#CCC7E5" />
<SelectedItemStyle forecolor="GhostWhite" backcolor="DarkSlateBlue" />
<AlternatingItemStyle backcolor="#CCC7E5" />
<Columns>
<asp:EditCommandColumn
EditText="<img src='/PortalCSSDK/images/edit.gif' border='0'
alt='Edit' />"
CancelText="Cancel"
UpdateText="Update"
Itemstyle-Wrap="False"
HeaderText="Edit" />
</Columns>
</asp
ataGrid>
<asp:Label runat="server" id="Message" />
</td>
</tr>
</tbody>
</table>
</body>
</html>
Any suggestions, recommendations, changes, rewrites, useful links, etc would
be greatly appreciated.
Thanks,
Jeff
