Introduction

Have you ever spent a good deal of time developing forms to enter or manipulate sample or test data for your projects? Even worse, you have filled your form and clicked the submit button and it comes with an error message "Foreign Key FK_parent_child_key Violated". How about when the table structure changed in the middle of the project and the web form needs changes. Frustrating isn’t it? You have probably called your DBA to help you enter correct data through the network of primary and foreign key relationships.

This article demonstrates how to build a web based generic data entry tool using .NET and stored procedures that display relevant metadata on a single web page. It also shows you how you can audit data entry operations with simple additions to the web text format into a separate audit database using dynamic SQL or custom stored procedures. The tool is developed using Microsoft SQL Server but can be developed to work with any ODBC compliant databases that support stored procedures and dynamic SQL.

I will also define a mechanism for generating dynamic SQL statements from a formatted string that originates from an application. This can be implemented in various databases by converting the database side code to a specific database. I’ll also provide some insight into the advantages of this design, examples of some generic column transformations and I’ll point out areas where further development or customization could be done.

System Requirements

Separate versions of the code for VS2005/SQL Server 2005 and VS.NET 2003/SQL Server 2003 have been provided, so you can work with either platform. To run the sample code for this article you should have the following:

• A web server running on Windows 2000 or later. IIS is probably the best for this, but any web server capable of running ASP.NET pages should do


• The .NET Framework version 1.x/v2.0


• Visual Studio .NET 2003/2005


• MSDE/SQLExpress or MS SQL Server 2000/2005 with the Northwind database installed

Install and Compile

To install the sample code, create a database called Audit and then open up SQL Query Analyzer and run the attached file DataAdmin.sql and DataAdminAudit.sql . This will create the necessary database stored procedures and functions in the Northwind and Audit databases. The solution included in the download can be installed in the c:inetpubwwwroot directory and opened up within Visual Studio. See the Readme.txt file for specific details on installing the sample.

Overview of the Solution

The data entry tool developed throughout this article performs the basic data entry functionality and is limited to work with a single MS SQL Server, but can be extended or customized to work against other databases as necessary. The application performs the following functions as given below:

• Dynamically generates a web form based on the selection of a database, a table and a primary key from a MS SQL Server database


• Displays the foreign key lookup values from the primary table


• Shows metadata information such as indexes, primary and foreign key information on a single page on the column and table label tool tips


• Performs basic insert, update and delete operations on a table


• Allows for the viewing of the underlying SQL code without actually executing it


• Shows how transactional control can be implemented using a web.config parameter


• Shows how auditing of DML statements can be done using SQL or stored procedures

Displaying Data and Foreign Key Lookups

The sample tool displays a web form with the associated record from a table upon user selection of the primary key value of the user selected table and database. Once a record is displayed, the user can change the values to create a new record with the insert button, update or delete buttons respectively as shown in Figure 1. The application grays out the primary key and the foreign key columns and displays any foreign key columns as a dropdown list of the primary table records.

Figure 1. Web form with foreign key dropdown showing lookup table values

Displaying Generated Code

Pressing the Code button sets the application in a mode to generate and display the SQL code generated instead of executing it. The Code button is a toggle between displaying the code and actually executing it. This is shown in Figure 2and is useful in understanding how the application works internally.

Figure 2. Generated SQL code for insert, update and delete

The textbox on the left displays the web text string constructed from the form fields while the one on the right displays the corresponding SQL after translation. A detailed discussion of the web text string format, the architecture and the translation mechanism can be found in The SQL Processing Framework section below.

Displaying Table Metadata

The form displays all of the column and table related metadata information in the column and the table label tool tips as shown in Figure 3 below.

The tool has an option to audit the insert, update and delete statements to an audit database. The tool also illustrates how auditing can be performed by SQL statements or a custom stored procedure and can be configured by the audit parameters in the web.config file. The readme.doc file included with sample application provides detail instructions on the configuration parameters.

Figure 3. Table metadata

Setting Configuration Parameters

The tool can be configured to audit DML statements into an audit database with the help of dynamic SQL or a stored procedure which is controlled by the <appsettings> section in the web.config file. Also the foreign key dropdownlist can be displayed as a textbox if desired for ease of display. The readme.doc file attached with the solution has detail description and usage of the configuration parameters

The Code

The code for the sample application can be divided into two sections:

• The .NET sample application code


• The database stored procedures

The .NET application code consists of three main events where most of the processing is done. The database calls are made only through stored procedures and consists of procedures for retrieving data, metadata and the executing SQL insert, update and delete operations. Figure 4 describes the various .NET methods and the calls to various stored procedures that the application makes.

Figure 4. Flowchart for the Data Entry Application

PageLoad Event

The database and the table selection are processed on the page load event and are captured in the session variables for comparison in future form postbacks.

The various steps in this process are:

The initial population of the database, the table dropdown list and the primary key list box is based on the value in the web.config file. The methods GetDatabases() and GetTables() populate the list of databases and tables respectively.

if(!IsPostBack)

{

// Initialize dropdown lists and list boxes based on web.config 

// configuration. 
  GetDatabases();
  DatabaseName = ConfigurationSettings.AppSettings["InitialDatabase"];
  Session["DatabaseName"]=DatabaseName;
  GetTables(DatabaseName) ;
  tablename=ConfigurationSettings.AppSettings["InitialTable"];
  Session["TableName"]=tablename;  
  GetTableDataAndKeys(DatabaseName,tablename);
  PopulateListBox();

. . .

}

• The method GetTableDataAndKeys() retrieves all the table metadata and data from the database into multiple tables of a single dataset upon table selection on the form postback. It then calls the PopulateListBox() method thus populating or refreshing the Primary Key Value listbox based on table selection.




if(!IsPostBack)

{. . . . //Code not shown

}

else

{

. . . //Code for database selection (not shown)
  {

// Processing for a table selection .
  tablename=Session["TableName"].ToString();
  if (tablename != DropDownList1.SelectedValue &&  
    DropDownList1.SelectedValue != "")
  {
    tablename = DropDownList1.SelectedValue;
    //Opens a DB Connection and gets the table and the metadata in 
    // a single dataset.
    GetTableDataAndKeys(DatabaseName,tablename); 
    PopulateListBox();
    Session["TableName"]=tablename;
  }

.. .

}

• The GetTableDataAndKeys() methodfetches the table data and metadata with the stored procedures GetTableData() and GetTableMetadata() passing various options. Both of these stored procedures build the SQL dynamically based on the database and the table names and return it to the above method. A code fragment of the stored procedure is shown below.




CREATE PROCEDURE dbo.usp_GetTableMetadata 
  DatabaseName  varchar(50) = null,
  TableName   varchar(100) = null,
  Option  int = 0, -- -2 DatabaseList -1 TableList 0 All,
    1 -- Definition , 2 -- FK, 3 -- Index -- 4 Triggers 
    5--Dep Stored procs 6 - PK
  InitialDB  varchar(50) = ‘Northwind’,
  debug    int = 0 -- 0 No debug 1 - debug
AS
SET NOCOUNT ON
...
  if Option in (0,2)
  Begin
  — Dynamic SQL to retrieve the Primary Key and Foreign Key columns
  set sql = '
  select a.table_name,
    a.constraint_name as FK_constraint,
    b.unique_constraint_name PK_constraint,
    c.column_name as FK_Column_name,
    d.table_name as PK_Table_name,
    d.column_name as PK_Column_name,
    c.ordinal_position as position
  from '+DatabaseName+’.information_schema.table_constraints a 
  inner join ‘@DatabaseName‘.information_schema.referential_constraints b
    on  a.constraint_catalog = b.constraint_catalog
    and a.constraint_schema  = b.constraint_schema
    and a.constraint_name    = b.constraint_name
  inner join ‘@DatabaseName‘.information_schema.key_column_usage c
    on  a.constraint_catalog = c.constraint_catalog
    and a.constraint_schema  = c.constraint_schema
    and a.constraint_name    = c.constraint_name
  inner join ‘@DatabaseName‘.information_schema.key_column_usage d
    on  d.constraint_catalog = b.unique_constraint_catalog
    and d.constraint_schema  = b.unique_constraint_schema
    and d.constraint_name    = b.unique_constraint_name
    and c.ordinal_position = d.ordinal_position
  where a.constraint_type = ‘‘FOREIGN KEY’‘
  and   a.table_name = ‘’‘@TableName‘’‘
  order by a.constraint_name, c.ordinal_position’
  // Prints SQL or executes it based on debug values.
  exec cp_ExecOrDebug sql,@debug
  End

.. .

SET NOCOUNT OFF

Code Behind the Primary Key ListBox Selection

The code behind for the SelectedIndexChanged event gets executed when the user selects a key value from the Primary Key Value listbox which renders the form dynamically to generate the form fields. The method ProcessDataEntry() does the work. The steps are shown below.

This code can be summarized into the following steps:

• The method GetTableDataAndKeys() retrieves the data and metadata for the selected table.


• The table rows are then filtered based on the Primary Key listbox selection made with a dataview.




ProcessDataEntry(object sender,System.EventArgs e, int rowindex)

{
  // Get the Table Data and Keys from the database stored procedure
  GetTableDataAndKeys(DatabaseName,tablename);
  //Create a DataViews to filter based on table name and primary key
  DataView  dvColumns =new DataView(ds.Tables["Table"] ); 
  dvColumns.RowFilter = id + "=’" + selection + "’";

. . .

}

• The web form is then built by browsing the columns collections of the dataview. The selected row value populates the form fields. The metadata for the table and columns are populated on the label tooltip in the same loop as shown below.




  foreach(DataColumn dc in dvColumns.Table.Columns    )
  {
    Label lb= new Label(); TextBox tb = new TextBox(); 
    DropdownList lbb = new DropDownList();
    lb.ID = lb + dc.ColumnName ; // Labels Initialized
    tb.ID = tb + dc.ColumnName ; //Text boxes Initialized
    lbb.ID = lbb + dc.ColumnName ; //FK drop downs initialized
    //Filter the Primary and Foreign Key data views based on column name
    dvColMetadata.RowFilter = "column_name = ‘"dc.ColumnName"’";
    dvColFk.RowFilter = "FK_column_name = ‘"dc.ColumnName"’";
    dvColIndex.RowFilter = "ColumnName = ‘"dc.ColumnName"’";
    // Build the Column label Tooltip metadata string

lb.ToolTip="DataType:"dvColMetadata0["Data_type"].ToString()"rn";

lb.ToolTip=lb.ToolTip+
  "Length:"dvColMetadata0["Length"].ToString()"rn";
 lb.ToolTip=lb.ToolTip+"PrimaryKey:"+dvColMetadata0["PrimaryKey"].
  ToString()+"rn";
    // Populate the column values for the text box columns based on the
    // rowindex passed by user primary key selection
    tb.Text = dvColumns.Table.Rows[rowindex][dc.ColumnName ].ToString() ;
  }

• If the table has a foreign key column, foreign key metadata is populated in the column tool tip from the dvColFK dataview. The method PopulateForeignKeyDropdown() is then called to populate the foreign key dropdown listbox.




if (dvColFk.Count > 0)

{ //Display the Foreign Key information on the tool tip

lb.ToolTip=lb.ToolTip+"FKConstraint:"+dvColFk0["FK_Constraint"]
  .ToString()+"rn"+
  "FK->PrimaryTable:"dvColFk0["PK_Table_name"].ToString()"rn"+

"FK->PrimaryTableConstraint:"dvColFk0["PK_Constraint"].ToString()"rn"+

"FK->Primary Column:"dvColFk0["PK_Column_name"].ToString()"rn";
  . . . 
  //Populate the foreign key value information in the label tooltip for 
  //the FK column as it is not visible in the dropdown list.
  lb.ToolTip = lb.ToolTip+"ForeignKeyValue:"+strData;
  // Queries the Primary Key Table and populates the FK dropdownlist.
  PopulateForeignKeyDropdown(ref    
    lbb,dvColFk0["PK_Table_name"].ToString(),strData);

}

• The labels, text boxes and the dropdownlists are then added to the placeholder.




//Add the controls to the PlaceHolder

PlaceHolder2.Controls.Add (lb);  

if (dvColFk.Count  0)
{
  PlaceHolder2.Controls.Add (tb);  
}
else
{
  PlaceHolder2.Controls.Add (lbb);  
}

To distinguish the primary key and the auto generated columns, their names are marked with special tags like !PK! or !Identity! while generating the textboxes. This identification is used for excluding them from updates and generating the where clause for filtering and the code is included with the sample included with this article.

The metadata shown in this tool is for illustration purpose only. Since all the metadata is fetched in the context of a single stored procedure, changes or addition to metadata can be made in one place to customize the display of the desired metadata.

Processing Inserts, Updates and Deletes

The insert, update and the delete of records is processed in the click event of the insert , update and the delete buttons in a single method - Do_Click() . In this method:

• A text string called the web text string is built by filtering out the necessary keys and values required to build the string by browsing through the form collection. The web text string is built using a simple hierarchical format such as the following:

Database=<DatabaseName>:
  Table=<TableName>:
  ColumnName=
  <ColumnValue/ColumnAlias>:
  where=<WhereClause>:Group=GroupbyClause:
  :Action=<select/insert/update/delete>
  :Table=<TableName>:ColumnName=
  <ColumnValue/ColumnAlias>:
  Action=<Select/Insert/Update/Delete>. 

The relevant code is as follows

private void Do_Click(object sender, System.EventArgs e)
{
  System.Web.UI.WebControls.Button bt=(System.Web.UI.WebControls.Button)
  sender;
  string[] strKeys;
  string[] strValues;
  string strQs = "";
  // Build up web text string to match form collection.
  // Skip the view state field. 
  strKeys = Request.Form.AllKeys;
  for (int i = 0; i < Request.Form.Count; i++) 
  {
    strValues = Request.Form.GetValues(i);
    for (int j = 0; j <= strValues.GetUpperBound(0); j++) 
    { // Skipping the non column form keys
      if (strKeys[i] != "__VIEWSTATE" && strKeys[i] !="__EVENTTARGET" &&  
        strKeys[i] !="__EVENTARGUMENT" && strKeys[i] != "RowCounts" && 
        strKeys[i] != "ListBox1" )
      { // The web text string is constructed here
        strQs += strKeys[i]
  .Replace("System.Web.UI.WebControls.TextBox","")
  .Replace("System.Web.UI.WebControls.DropDownList","")
  .Replace("Dropdownlist2","Database")
  .Replace("DropDownList1","Table")+ "=" + strValues[j] + ":";
      }
    }
  }
}

• Detailed discussion of the web text format is included in The SQL Processing Framework section later in this article.
• A where clause string is also built based on the primary key textboxes. Also note that they are excluded from the web text string so that they cannot be updated.

        strQs +=  strKeys[i]
  .Replace("System.Web.UI.WebControls.TextBox","")
  .Replace("System.Web.UI.WebControls.DropDownList","")
  .Replace("Dropdownlist2","Database")
  .Replace("DropDownList1","Table")
  .Replace("!Identity!","")
  .Replace("!PK!","") + "=" + strValues[j] + ":";
      }
      if ((bt.Text  "delete" || bt.Text == "update") &&   
        strKeys[i].Replace("!PK!","").Length != strKeys[i].Length)
      { // Build the where clause for updates and deletes
        WhereClause = WhereClause +  strKeys[i]
  .Replace("System.Web.UI.WebControls.TextBox","")
  .Replace("System.Web.UI.WebControls.DropDownList","")
  .Replace("Dropdownlist2","Database")
  .Replace("DropDownList1","Table")
  .Replace("!Identity!","")
  .Replace("!PK!","") + "=" + strValues[j] + ":";
      }

• The remaining piece of the code (not shown) concatenates the where clause string to the previously built web text string and passes it to the stored procedure usp_GenericSQL() for execution. The stored procedure can display the generated SQL or execute it based on the selection of the Code button within the tool. For example to update a table Customer with only the columns ( CustomerID primary key, CustomerName and Customer Address ) in the Northwind database, the following web text gets generated:




Database=Northwind:Table=Customer:CustomerName=
  Albert:CustomerAddress=235 Northshire:where=CustomerID=9:Action=Update

The SQL Processing Framework

The SQL or DML (i.e. insert/updates/deletes) processing primarily consists of generating a text string called the web text string and passing the string to a stored procedure which parses the string in order to generate dynamic SQL and execute it. The web text is built with pre-defined tags in a hierarchical format. The tags are equated to actual values and separated by a delimiter (colon:) This is analogous to different parts of a SQL query.

An example of the web text string would be as follows:

Database=Northwind:Table=Customers:CustomerName=MyCust:Action=Insert

When this is passed to the stored procedure usp_GenericSQL() it will generate a SQL statement that looks like this:

Insert into Northwind.dbo.Customers (CustomerName) values (‘MyCust’) 

Now multiple tables can be defined for a database and multiple columns for a table. The Action tag marks the end of a query or a DML statement. The Action tag determines the type of SQL statement and can take values like select, insert , update , delete and execute . The value execute is used for executing stored procedures. Multiple queries or web text DML strings can be attached to build a string which would translate to multiple SQL statements as shown in the code below.

Declare WebText varchar(5000), debug int

Set debug = 1 -- This prints the SQL statement instead of executing it 
--The webtext string 
Set WebText= ‘Database=Northwind:Table=Categories:CategoryName=
  New Test Category:Description=Desc of a test category:Action=
  Insert: Table=Customers:CompanyName=
  Test:ContactName=Fred:where=CustomerID=’‘ALFKI’‘:Action=Update’

exec dbo.usp_GenericSQL  WebText, debug

The example shown here uses SQL Server Query Analyzer to create the web text string and run the stored procedure usp_GenericSQL() to generate the following SQL.

INSERT INTO Northwind.dbo.Categories(
 CategoryName,

Description) VALUES (‘New Test Category’,

‘Desc of a test category’)

UPDATE Northwind.dbo.Customers SET CompanyName=‘Test’,

ContactName=‘Fred’

where CustomerID=’‘ALFKI’‘ 

The column alias can be specified instead of values for Action=Select . The web text string and the translated SQL is shown below

Database=Northwind:Table=
  Categories:CategoryName=CatName:Description=CatDesc:Action=Select: 

SELECT CategoryName as ‘CatName’,

Description as ‘CatDesc’

FROM Northwind.dbo.Categories

Advantages of Using Web Text

The process of building a generic web text string in an application and parsing and generating the dynamic SQL in the database offers several advantages. Some of them are listed below:

• The .NET application code does not build embedded dynamic SQL and make direct calls hence making it portable across another database with minimal modifications.


• The web text format can include database independent functional tag values. For example: ColumnName=~ParentKey (Details below). This is database-independent but can perform the same function of inserting a child record that takes the parent table key from parent table.


• SQL statements are generated in the context of a database stored procedure. This enables the application to work against another database vendor by converting the stored procedure code and database connection changes in the application to keep its existing functionality.


• A single stored procedure can process any SQL statements for an application and hence saves time on developing multiple DML stored procedures or SQL statements.


• The application format (i, e the web text string) is translated to a common format (a table) by a function in the database. This can potentially help in supporting different web text formats from an application by writing a different function specific to new formats by converting to the standard table format required by the generic SQL.


• The generic stored procedure can be extended or customized by changing the format with minimal changes in the application code.

Web Text Translation

The translation of the web text string into a table format is done by parsing the web text string in the database function fn_GetListCols() . The resulting table is then used by the usp_GenericSQL() stored procedure to build and execute the sql statement. For example the following web text string is translated as:

‘Database=Northwind:Table=Categories: CategoryName=
  New Test Category:Description=Desc of a test category:Action=
  Insert: Table=
  Customers:CompanyName=Test:ContactName=Fred:where=
  CustomerID=’‘ALFKI’‘:Action=Update’

ID	Category	Value
1	Database	Northwind
2	Table	Categories
3	CategoryName	New Test Category
4	Description	Desc of a test category
5	Action	Insert
6	Table	Customers
7	CompanyName	Test
8	ContactName	Fred
9	Where	CustomerID=’‘ALFKI’‘
10	Action	Update

The code below parses the web text string based on the delimiter (Colon :) and separator (=) and returns a table in the above format. The code is pretty straightforward and it uses the SQL Server charindex() and the substring() functions to chop the web text string recursively in a loop to build the records in a table variable (format shown above). A sequential ID key is also generated as the records are generated in the loop. The table is then returned by the function. The various steps are commented in the code section below.

CREATE  FUNCTION dbo.fn_GetListCols (
  ListString varchar(2000),
  delimiter  varchar(10) = ‘:’,
  Separator  varchar(10) = '=')  
RETURNS ListTable table (
  ID int,
  String1 varchar(500),
  String2 varchar(500)) 

AS  

BEGIN 
 — Variable declaration section. (Code not included)
  . . . . 
  Set Count = 1
  Set TempString = ListString
  if TempString is not null or tempString <> ''
  Begin
   -- Builds the table records by recursively parsing the remaining
   -- parts of the unparsed string
  while charindex(delimiter,@TempString) <> 0
  Begin
    Set ColumnString =   
      substring(TempString,1,charindex(delimiter,tempString)-1)
    Set TempString = 
 substring(TempString,charindex(delimiter,tempString)+1,len(tempString))
    -- Insert the parsed record
    insert into ListTable 
    values (Count, 
    substring(ColumnString,1,charindex(Separator,ColumnString)-1),

substring(ColumnString,charindex(Separator,@ColumnString)+1,
  len(ColumnString)))
    set Count = Count + 1
  End
End
  -- Insert the remaining string part
  insert into ListTable 
  values (Count,
  substring(TempString,1,charindex(Separator,TempString)-1),
  substring(TempString,charindex(Separator,@TempString)+1,
  len(TempString)))
  RETURN 
END
<p></pre><!-- @END —>


Dynamic SQL Generation


The code shown here is a simplified version for illustration purposes only. Please refer to the   DataAdmin.sql  file found in the sample download for the actual code. The steps in the  usp_GenericSQL()  procedure are described below.


Pass the web text to the function  fn_GetListCols()  and place the result in the temporary table placeholder. This is an ordered list based on the order the tags appear in the web text string.




CREATE PROCEDURE dbo.usp_GenericSQL
  WebText  varchar(8000),
  delimiter  varchar(5) = ‘:’,
  Separator  varchar(5) = '='
AS
SET NOCOUNT ON
  declare Placeholder table (id int , ColumnName varchar(100),
    Value varchar(500))
  — Declaration of the variables.(All Code not included)
  declare MaxID     int,
  . . . 
   -- Get the webtext, parse it and put it into a table
   insert into Placeholder 
   select * 
   from dbo.fn_GetListCols(WebText,delimiter,@Separator)

SET NOCOUNT OFF

• Fetch all the databases and their positions in the cursor cDB selecting from the placeholder table. For each traverse of the loop, the position of the next database value is marked based on the ID as returned by the function fn_GetListCols() and captured in the Placeholder</span> table. This lets us process multiple databases if they are defined in the web text string.</li> <!-- @START —>
  

     —Get the ID and names of databases.
     declare cDB cursor for
     select ID as DatabaseID, 
    isnull(Value,’[’db_name()‘]’) as DatabaseName
     from PlaceHolder 
     where ColumnName = 'Database'  order by ID
  -- Process Database cursor
     open cDB
    fetch next from cDB into DatabaseID, DatabaseName
  . . . 
     while @fetch_status = 0
     Begin
    — Get the Next Database ID
         select top 1 NextDatabaseID = ID 
    from PlaceHolder 
    where columnname = ‘Database’ and ID > DatabaseID 
    order by ID
          if @rowcount = 0 
      select NextDatabaseID = MaxID
     . . . 
     fetch next from cDB into DatabaseID, DatabaseName
     End
     Close cDB
     Deallocate cDB
  
  

• For each database, fetch all the tables and their positions in the cursor cTables . For each traverse of the loop, the position of the next database value is marked based on the ID . This lets us process multiple tables within a database to generate separate queries or DML for each table.




  —Get the List of tables to be processed for the database.
     declare cTable cursor for
     select ID,
  ‘.dbo.’ as DBUser,
  ‘[’Value‘]’ as TableName
     from PlaceHolder
     where ColumnName in ('Table','Procedure')
     and ID &gt; DatabaseID and ID < NextDatabaseID
  open cTable
    fetch next from cTable into TableID, DBUser, TableName
  while fetch_status = 0
  Begin
    —Get the Next table ID
         select top 1 NextTableID = ID
    from PlaceHolder
    where ColumnName in (‘Table’,‘Procedure’)
    and   ID > TableID  and   ID &lt; NextDatabaseID
    order by ID
  . . . 
  fetch next from cTable into TableID,  DBUser,@TableName

End

close cTable

deallocate cTable

• For each table, build the column string recursively for the insert, update and delete statements. Since different DML statements have different syntaxes, the following code shows only the mechanics of column string generation. Notice how different column SQL strings are recursively built by filtering out the other irrelevant tags for different DML statements in a single select statement. Also notice how identity or autogenerated columns are filtered out in the insert statement generation. The detail code can be found in the attached solution file DataAdmin.sql .




select 

SelColumnList = SelColumnList + case when ColumnName in 
  (‘Action’,‘TableName’,‘where’,‘group’,‘order’,‘join’,‘left join’,
  ‘outer join’,‘right join’,‘full outer join’,‘on’,‘and’) 
  then ‘’ else ColumnName+’ as ‘’‘+Value +’‘’,’ +@RetVal end  ,

InsColumnList = InsColumnList + case when 
  ColumnProperty(object_id(TableName),ColumnName,'IsIdentity') = 1
  or ColumnName in 
  ('Action','TableName','where','group','order','join','left join',
  'outer join','right join','full outer join','on','and') 
  then '' else ColumnName+','+RetVal end ,

InsColumnVals = InsColumnVals + case when 
  ColumnProperty(object_id(TableName),ColumnName,'IsIdentity') = 1
  or ColumnName in 
  ('Action','TableName','where','group','order','join','left join',
  'outer join','right join','full outer join','on','and') then 
  '' else case when Value = 'null' or isNumeric(Value) = 1 or 
  charindex('(',Value) > 0 then Value else ''''+Value+'''' end+','
  +RetVal end,

. . . .

from PlaceHolder
where ID > TableID

and   ID < ActionID
</ul><p></pre><!-- @END —>


For each table, the where clause is built on the basis of the primary key, if it is not specified in the web text string.  The primary key columns are determined from the standard  information_schema  views in the database.




  if Action in ('update','delete') and len(WhereClause) = 0 

Begin
  select WhereClause = WhereClause + column_name+’=’+
    case when data_type in 
    (‘int’,‘bigint’,‘smallint’,‘tinyint’,‘float’,‘numeric,decimal’) 
        then value 
  else ‘’‘’b.value‘’‘’ end + ‘ and’+@RetVal
    from (select  a.table_catalog,   
      a.table_schema,    
      a.table_name,    
      a.constraint_name,    
      b.column_name,    
      b.ordinal_position,      
      c.data_type,   
      c.character_octet_length  
    from information_schema.table_constraints a  
    inner join information_schema.key_column_usage b
      on a.constraint_name = b.constraint_name
      and a.constraint_schema = b.constraint_schema
    inner join information_schema.columns c   
      on a.table_name = c.table_name    
      and a.table_schema = c.table_schema   
      and b.column_name = c.column_name  
    where a.constraint_type = ‘PRIMARY KEY’  

) a
    inner join PlaceHolder b
      on a.column_name = b.ColumnName
      and a.table_name = replace(replace(TableName,’[’,’‘),’]’,’‘)
      and b.ID > TableID
      and b.ID &lt; ActionID
    order by a.ordinal_position

if len(WhereClause) &gt; 0 
  select WhereClause = substring(WhereClause,1,len(WhereClause)-5)

End



• Build the final SQL statement based on the DML action (insert, update or delete).




  select sql = sql + RetVal+case when Action = ‘select’ then
    ‘SELECT ‘@SelColumnList
    RetVal+'FROM '+DatabaseName+@DBUser+@TableName + JoinClause+
    WhereClause+@GroupClause+@OrderClause
    when Action = 'insert' then 
    'INSERT INTO 
    '+DatabaseName+@DBUser+@TableName+’(’@RetVal@InsColumnList+’) 
    VALUES (’@InsColumnVals‘)’+@RetVal
    when Action = 'update' then 
. . . 
</ul><p></pre><!-- @END —>


Continue with the generation procedure for each table and database in the loop.


Return or execute the SQL string based on the  debug</span>  option.</li>
<!-- @START —>

   if debug = 1
  select sql as SQLString 
   else
  execute sp_executesql sql
</ul><p></pre><!-- @END —>


Some Examples and Usage


The web text format and the generic stored procedure used in the data entry tool is generic enough to support various applications willing to receive and transmit data via the web format instead of direct SQL statements. The following statements (run in SQL Query Analyzer) show the various uses of the web text string to generate the desired SQL.


Applications using Parent-Child Inserts


In parent child inserts, the inserted parent key is carried over to the child table for insertion of all the child records. The foreign key column  CategoryID  of the child table  Product  has a value  ~ParentKey  which is translated to the function  scope_identity()  in SQL Server inside the stored procedure  usp_GenericSQL() .




Set WebText =
   'Database=Northwind:Table=Categories:CategoryName=Test:
  Action=insert:Database=Northwind:Table=Product:
  ProductName=NewProduct:CategoryID=~ParentKey:Action=insert'
Exec dbo.usp_GenericSQL WebText



The generated SQL Query is as follows:




INSERT INTO Northwind.dbo.Categories(

CategoryName) VALUES (‘Test’)

INSERT INTO Northwind.dbo.Product(

ProductName,

CategoryID) VALUES (‘New Product’,

scope_identity())



However if the solution is implemented in another vendor’s database (presenting that code is not in the scope of this article), any vendor function performing the same function as  scope_identity()  can be used but no change would be required in the web text string. Only conversion of the  usp_GenericSQL()  to the vendor’s database would be required. Another example of such a function can be the T-SQL  ~Top(n)  function. This can be implemented differently in different databases but can perform the same from an application perspective.


Retrieving Data from Multiple Tables in a Single SQL Call


Many applications use a single SQL call to retrieve data from multiple tables. This section illustrates how multiple tables from different databases can be selected with custom filtering and ordering. Similarly multiple DML statements or combination of select and DML statements can be included in the same web text.


Here is the web text:




Set WebText='Database=Northwind:Table=Categories:
  CategoryID=CategoryID:CategoryName=CategoryName:
  Description=Description:where=CategoryID=2:
  Action=Select:Database=pubs:
  Table=authors:au_id=AuthID:
  au_lname=LastName:
  au_fname=FirstName:
  where=au_id='' 472-27-2349'':
  Order=au_id:Action=select'
exec dbo.usp_GenericSQL  WebText



And the resulting SQL is as follows:




 SELECT CategoryID as ‘CategoryID’,

CategoryName as ‘CategoryName’,

Description as ‘Description’

FROM Northwind.dbo.Categories

where CategoryID=2

SELECT au_id as ‘AuthID’,
  au_lname as ‘LastName’,
  au_fname as ‘FirstName’

FROM pubs.dbo.Authors

where au_id=’ 472-27-2349’

order by au_id



Database Specific or Generic Column Transformations


This example illustrates how column transformations can be applied to the select list columns directly within the web text string. The SQL Server function  ColumnProperty()  is passed as a column value in the web text string to determine whether it is an identity column. A generic column transformation similar to  ColumnProperty()  can also be built in the web text string. This would then translate to  ColumnProperty  in SQL Server or a different built-in or custom function in another vendor’s database. This will require conversion of the  usp_GenericSQL()  to the new database, but minimal code change on the .NET side is required.


The web text code showing a column transformation is shown below.




Set WebText='Database=Northwind:
  Table=information_schema.columns:
  table_name=TableName:
  column_name=ColumnName:
  ColumnProperty(object_id(table_name),column_name,''IsIdentity'')=
    IsIdentity
  :Data_Type=DataType:
  Is_nullable=IsNullable:
  Character_maximum_length=MaxLength:where=
    table_name=''Orders'':
  Order=ordinal_position:Action=Select'
exec dbo.usp_GenericSQL  WebText



And the resulting SQL is as follows.




SELECT table_name as ‘TableName’,

column_name as ‘ColumnName’,

ColumnProperty(object_id(table_name),column_name,‘IsIdentity’) as 
  ‘IsIdentity’,

Data_Type as ‘DataType’,

Is_nullable as ‘IsNullable’,

Character_maximum_length as ‘MaxLength’

FROM Northwind.information_schema.columns

WHERE table_name=‘Orders’

ORDER by ordinal_position



Dealing with Proprietary Databases


Many vendor applications encapsulate database tables and provide a stored procedure interface to the database in order to maintain data integrity. Since the web text string accepts execution of stored procedures, it can be used in the applications developed on top of these databases. An example of using web text to generate an SQL statement that calls a stored procedure can be seen below:




Set WebText = 'Database=Northwind:Procedure=CustomerOrderHist:
  CustomerID=’‘ALFKI’‘:Action=Execute’

Exec dbo.usp_GenericSQL WebText
The resulting SQL would look like the following.
EXECUTE Northwind.dbo.CustomerOrderHist CustomerID=’‘ALFKI’‘



Application Controlled Auditing


Many applications audit SQL statements, but many of them are controlled at the database with triggers. They also do not capture any application specific details like module or application methods. Using the web text format, auditing can be performed by the application by capturing the application specific modules and passing it to a generic stored procedure for inserting into the audit database. The Generic Data Entry Tool has audit options in the  web.config  to turn on the auditing feature. You may also view the generated web text and the corresponding SQL code by clicking the  Code  button.




Set WebText = 'Database=Northwind:Table=Categories:
  CategoryName=Test:
  Action=insert:Database=Audit:
  Table=Auditlog:
 DBName=Northwind:TabName=Categories:
  ApplicationMethod=DoClick:
  ActionType=INSERT:Action=insert'
Exec dbo.usp_GenericSQL WebText

INSERT INTO Northwind.dbo.Categories(

CategoryName) 

VALUES 

(‘Test’)

INSERT INTO Audit.dbo.Auditlog(

DBName,

ApplicationMethod,

ActionType) 

VALUES (‘Northwind’,

‘Categories’,

‘DoClick’,

‘INSERT’)



Further Work


There are several enhancements that can be done to make the application more robust when dealing with different database configurations and architectures.


Implementation Across Different Database Servers


In order to implement the data entry tool or the web text format across different servers, the server name can be accepted from the application, appended in the web text string and passed to the  usp_GenericSQL  stored procedure. Figure 5 shows you an example of this design:




Figure 5. A sample implementation scenario across multiple servers


The web text below shows the web text across different servers.




Set WebText='Server=MyServer1:
  Database=Northwind:
  Table=Categories:
  CategoryID=CategoryID:
  CategoryName=CategoryName:
  Description=Description:where=CategoryID=2:
  Action=Select:
  Server=MyServer2:
  Database=Northwind:
  Table=Customers:
  CompanyName=COMPANY:
  where=CustomerID like ''A%'':
  Order=CustomerID:Action=select'
exec dbo.usp_GenericSQL  WebText

The resulting SQL would be as follows:

SELECT CategoryID as ‘CategoryID’,

CategoryName as ‘CategoryName’,

Description as ‘Description’

FROM MyServer1.Northwind.dbo.Categories

where CategoryID=2

SELECT CompanyName as ‘COMPANY’

FROM MyServer2.Northwind.dbo.Customers

WHERE CustomerID like ‘A%’

ORDER by CustomerID



This can be achieved by simply extending the generic stored procedure  usp_GenericSQL  to accept the server tag as a top level tag instead of the database tag as shown above.


Implementation Across Various Database Vendors


The web text to dynamic SQL translation can be extended to support different databases by converting the database side stored procedures and functions to use the vendor’s specific functions. You will also need to change the database connection from the application. In this manner generic transformations defined in the web text layer can be translated to database specific implementation of  usp_GenericSQL  or similar procedures.


For example, transformations like  parent key carryover to child table, auto generated keys, top(n) select, update based on joins perform the same function at the application level but implemented differently on different database platforms. The following web text shows this in action by first passing a database type of MS SQL and then a second example passing in Oracle:




Set WebText='DBType=SQL:
  Database=Northwind:
  Table=Categories:
  SelectAddOn=top10:
  CategoryID=CategoryID:
  CategoryName=CategoryName:
  Description=Description:
  where=CategoryID=2:Action= '
exec dbo.usp_GenericSQL  WebText

The specific MS SQL Server SQL that is generated:

SELECT top 10 CategoryID as ‘CategoryID’,

CategoryName as ‘CategoryName’,

Description as ‘Description’

FROM Northwind.dbo.Categories

where CategoryID=2



A database type of Oracle is passed in:




Set WebText='DBType=Oracle:
  Database=Northwind
  :Table=Categories
  :SelectAddOn=top10
  :CategoryID=CategoryID
  :CategoryName=CategoryName
  :Description=Description
  :where=CategoryID=2:Action= '
exec dbo.usp_GenericSQL  WebText



The specific Oracle SQL that is generated:




SELECT CategoryID as "CategoryID",

CategoryName as "CategoryName",

Description as "Description"

FROM Northwind.dbo.Categories

where CategoryID=2

and rownum <= 10



On the current context of the Data Entry Tool, the web text generated by the admin tool can be made to work with another database by converting the stored procedure and the functions keeping the same functionality and with minimal changes on the application side as shown in Figure 6.




Figure 6. A sample implementation scenario across database vendors


Structure Comparison between Databases (A Different Web Text Format)


Structure comparison involves the listing of mismatched columns, data types, keys, size etc.  An example of this involves joining the metadata tables in the databases and comparing the relevant attributes for the same object. To illustrate a modified format of the web text an example is shown below. In this format, the table tag takes precedence over the database tag in terms of hierarchy. This format can be used by a different stored procedure to generate the SQL for structure comparison.


Alternately functions similar to the function  fn_GetListCols()  as described above can be developed to translate the web text format into the format required to generate the SQL. A sample of this new web text can be seen below.




declare webtext varchar(1000)
set WebText = ‘Table=Categories:
  Columns=*:
  Data_type=*:
  Database=Northwind:
  Database=NorthwindCopy:Action=Compare’



A sample of the desired generated SQL after translation of the above web format follows.




select a.*

from Northwind.information_schema.columns a 

inner join NorthwindCopy.information_schema.columns b
  on a.table_name = b.table_name
  and a.column_name = b.column_name
  and a.data_type = b.data_type





Figure 7. Sample implementation scenario with different web text formats


This provides an advantage where new different web text formats can be translated by the functions into the generic table format which can be used by  usp_GenericSQL()  thus maintaining extensibility across formats. In this way various data centric applications can be developed to use different web text formats for different purposes.


Conclusion


This article has described a generic data entry tool that can be developed that reads metadata from a databases system tables and how metadata can be displayed along with the data. Since the tool is metadata-based and it uses stored procedures, it can be implemented across various databases by converting the database code in most cases.


If customized and extended, this can save a lot of form and stored procedure development work as the dynamic SQL generated by a stored procedure does all the work. Table changes (For example. addition or deletion of tables) and column attribute changes do not affect the insert, update, and delete functionality of the tool. The metadata stored procedure can be converted to produce similar results. No application changes are required for maintaining the same functionality. The metadata is readily displayed with data in the label tool tips which provides a snapshot look at a table in a database.


I have also described how the mechanism of translating a web text string to dynamic SQL at the database can be implemented across many databases and servers and how generic transformations can be applied differently in different databases. Any database centric database independent functionality can be implemented at the web text layer. The translation logic specific to each database can be added by converting the database layer.