管理员生成器第 1-3 部分 - INSERT 视图

引言

本教程的目的是为每位开发者提供一种基于表结构的预定义和预设规则生成界面的方法。

此代码生成的结果是让开发者专注于创建数据库结构而不是编写代码。尽管网上有许多界面或代码生成器，但它们都提供 DLL，并且大多使用 XML 模式来生成这些视图。

通过这个简单的教程，任何开发者都可以使用任何控件更改和更新/插入/删除代码。可以将控件更改为在线可用的任何控件，例如 TELERIK 控件，甚至是 ASP.NET 开发的内置控件。

因此，本文将作为由 3 部分组成的教程的第一部分，该教程旨在创建一个完整的 C# 类文件，以针对每个表的常见插入/更新/删除过程生成视图。我将随后在这些教程中添加内容，以改进类文件的功能。

背景（需要考虑的事项/先决条件）

我使用的是 Visual Studio 2010 专业版。
我的开发环境和语言是 C#.NET 4.0。

我正在创建一个自定义用户控件（希望将其扩展为功能齐全的 DLL）。

我还将 SQL Server 2008 R2 标准版用作我的数据库。

注意：一些 TSQL 不受其他 DBML 支持，因此您可能需要 Google 不同数据库的正确语法。

另一个先决条件是位于web.config文件中的连接字符串。

  <connectionStrings>
    <add name="DBASE" connectionString="Data Source=ARESHSAHARKHIZ;
     Initial Catalog=TABLENAME;Integrated Security=True"
     providerName="System.Data.SqlClient" />
  </connectionStrings>

请注意名称“DBASE”（请勿更改）。但是，您可以将初始目录更改为您想要使用的数据库。

Using the Code

在深入研究代码之前，让我们在数据库中创建一个表。

通过右键单击数据库的TABLES文件夹来创建表。

添加列及其数据类型。哎呀，您需要遵循一些规则，以便我们的管理员生成器代码可以遵循这些规则。

您必须遵循这些约定

1. 尽可能确保所有表都已规范化。
2. 确保您的表具有关系。如果定义了外键并连接到主键，您将在数据库图文件夹中看到这一点。
3. 表名应包含前缀“tbl_”，后跟其名称。示例：tbl_user 主键应始终是一个整数，其 identity specific 选项设置为true（用于在网格中删除和更新）。
4. 主键应包含“pk_”前缀，后跟表名和“_id”。示例：pk_user_id (来自 tbl_user)，pk_inventory_id (来自 tbl_inventory)。
5. 外键应包含“fk_”前缀，后跟外表名和“_id”。示例：fk_role_id (链接到 tbl_role)，fk_stock_id (链接到 tbl_stock)。
6. 如果某个列不需要在管理视图中显示，请在列名后添加“_non_”。示例：userHash_non_, userSalt_non_。
7. 对于所有具有datetime数据类型的列，添加“date_”。
8. （可选，用于审计目的）所有表都应包含以下列名：
   
   1. user_created nvarchar(50)
   2. date_created datetime
   3. status nvarchar(50)
   4. name nvarchar(250)。用于外键引用名称。

创建表后，您会注意到每个列属性下都有一个名为“DESCRIPTION”的属性。使用此属性为每个列添加有意义的描述。您可以从上图中看到我为“name”列放置了描述。

现在开始编码

首先，我需要一些 TSQL 语句来获取数据库模式。

给我一个完整的数据库模式

private String Completeschema 
    { get { return "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"; } }

给我一个特定模式或数据库中的所有表

private String Tables 
    { get { return "SELECT distinct TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS"; } }

返回给定表名的所有列名和这些列的数据类型

private String Columns { get 
    { return "SELECT * FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = '{0}' 
    order by ORDINAL_POSITION ASC"; } }
private String DataTypes { get 
    { return "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS Where  
    TABLE_NAME = '{0}' and COLUMN_NAME = '{1}'"; } }

返回列描述并给出列的名称及其表

private String ColumnDescription { get 
    { return "SELECT [Description] = ex.value FROM sys.columns 
    c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = 
    c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' 
    WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND 
    OBJECT_NAME(c.object_id) = '{0}' AND c.name = '{1}' 
    ORDER BY OBJECT_NAME(c.object_id), c.column_id"; } }

所以我们准备好查看数据库中的内容。由于我们有关系，我们希望外键是一个下拉列表，并使用主键作为值项属性，使用 [name] 列作为文本项属性。

private String FKTable { get { return "select * from {0}"; } }

就是这样。我们拥有所需的所有 SQL 语句。

注意：您会注意到我正在使用字符串格式来构造这些语句，它们容易受到注入。但是，有很多方法可以克服这些问题。但是对于数据库的 INSERT/UPDATE/DELETE，我构造了 SQL 参数以避免该部分的任何注入。

连接

我们使用以下private static方法获取并打开每个事务的连接。

///gets the Sql Connection of DBASE
private static SqlConnection getSqlConn()
{
    ConnectionStringSettingsCollection connections =
    ConfigurationManager.ConnectionStrings;

    string SqlConn = connections["DBASE"].ConnectionString;

    SqlConnection con = new SqlConnection(SqlConn);
    try
    {
        con.Open();
    }
    catch (Exception)
    { }
        return con;
} 

为了动态创建控件，我们需要将这些控件放置在一个占位符中，以便以后可以引用它们。我们始终知道这些控件将根据列的顺序生成。因此，如果我们在用户名之前定义了一个列名密码，则输出将是文本框密码，然后是文本框用户名。因此，您需要安排数据库结构中的此顺序。所以我将使用这个变量来保存这些控件。

private ArrayList TextboxNames = new ArrayList(); 

控制方式

我们创建了几个private方法，它们将创建控件并将它们添加到控件持有者中，并将该控件返回以添加到页面占位符。

我们需要为每种数据类型创建一些特定的控件。我将在这里使用 Telerik 控件。

注意：Telerik 控件有一个数字文本框。因此，对于任何 decimal 或 int 数据类型，程序都会生成一个数字文本框。如果您使用其他控件，您可以将其更改为textbox或特殊控件。

所以我们这里需要一些控件

• Textbox 用于nvarchar/varchar
• NumericTextbox 用于int/Double/Float
• DateTimePicker 用于Date/DateTime
• Combobox 用于外键 int
• Radbutton 用于Checkbox bit

对于 ASP.NET 控件，您将使用

• Textbox 用于nvarchar/varchar/int/double/float/datetime
• DropDownList 用于外键 int
• Checkbox 用于 bit

    private RadTextBox Textbox(String ID, String EmptyText, String Label)
    {
        RadTextBox txtbox = new RadTextBox();
        txtbox.ID = "txt" + ID;
        txtbox.EmptyMessage = "Please Enter";// EmptyText;
        txtbox.Label = Label;
        txtbox.Width = Unit.Pixel(200);
        if (Label == "Password:")
        {
            txtbox.TextMode = InputMode.Password;
        }
        TextboxNames.Add(txtbox); //Add Created Textbox to ArrayList
        return txtbox;
    }
    private RadNumericTextBox intBox(String ID, String lbl)
    {
        RadNumericTextBox bx = new RadNumericTextBox();
        bx.Label = lbl;
        bx.Value = 0.0;
        bx.ShowSpinButtons = true;
        bx.Width = Unit.Pixel(120);
        TextboxNames.Add(bx); //Add Created RadNumericTextBox to ArrayList
        return bx;
    }
    private RadButton Button(String ID, String Text)
    {
        RadButton btn = new RadButton();
        btn.ID = "btn" + ID;
        btn.Text = Text;
        btn.Click += new EventHandler(SaveButton_Click);
        return btn;
    }
    private RadComboBox ComboBox(String ID,String ColumnName, String Label)
    {
        RadComboBox cmb = new RadComboBox();
        cmb.EmptyMessage = "Select the "+ Label;
        cmb.Width = Unit.Pixel(200);
        cmb.ID = "cmb" + ID;
        cmb.DataSource = getFKTable(ColumnName);
        cmb.DataTextField = "name"; //always name
        cmb.DataValueField = ColumnName.Replace("fk_", "pk_");
        cmb.DataBind();
        //cmb.Label = Label;
        TextboxNames.Add(cmb); //Add Created RadComboBox to ArrayList
        return cmb;
    }
    private RadDateTimePicker DatePick(String ID)
    {
        RadDateTimePicker date = new RadDateTimePicker();
        date.ID = "dt" + ID;
        TextboxNames.Add(date); //Add Created Textbox to ArrayList
        return date;
    }
    private RadButton Checkbox(String ID, String Label)
    {
        RadButton chk = new RadButton();
        chk.ID = "chk" + ID;
        chk.ButtonType = RadButtonType.ToggleButton;
        chk.ToggleType = ButtonToggleType.CheckBox; 
        chk.Text = Label;
        chk.ToggleStates.Add(new RadButtonToggleState(Label + " Accepted"));
        chk.ToggleStates.Add(new RadButtonToggleState(Label + " Rejected"));
        chk.AutoPostBack = false;
        TextboxNames.Add(chk); //Add Created Textbox to ArrayList
        return chk;
    }

对于按钮，创建一个保存按钮处理程序。

（我将在本教程的下一部分讨论 SQL 构造和参数。）

protected void SaveButton_Click(object sender, EventArgs e)
    {
        //SqlCommand com = new SqlCommand(constructSql());
        //com = AddParameters(com);
        //addRow(com);
        RadAjaxManager.GetCurrent(Page).Alert("The Information is Saved.");
    }

现在是最后一部分，我们需要扩展我们的类以使用usercontrol类...

public class adminGenerator : System.Web.UI.UserControl 

...之后我们需要重写createchildcontrols方法

protected override void CreateChildControls()
    {
        base.CreateChildControls();
        String TableName = Request.QueryString["tbl"] == null ? 
            "Test" : Request.QueryString["tbl"].ToString();
        String Operation = Request.QueryString["mod"] == null ? 
            "list" : Request.QueryString["mod"].ToString();
        
        #region INSERT Generator
        if (Operation == "add")
        {
            DataSet Columns = getColumns(TableName.ToString());
            foreach (DataRow c in Columns.Tables[0].Rows)
            {
                //Controls.Add(new LiteralControl("<br/>+" +
                //c["COLUMN_NAME"])); //DEBUG: Show Column for each Table
                String Description = getDescription
            (TableName, c["COLUMN_NAME"].ToString());
                ProduceOutput(Controls, c["COLUMN_NAME"].ToString(), 
            TableName, Description);
                Controls.Add(new LiteralControl("<br/>"));
            }
            Controls.Add(Button("Saveme", "Save"));
            Controls.Add(Button("Cancelme", "Cancel"));
        }
        #endregion
        #region LIST GENERATOR
        if (Operation == "list")
        {
            
        }
        #endregion
    } 

请注意查询字符串。我们正在寻找 2 个值，“tbl”确定表名，“mod”确定操作类型。就是这样。我们都准备好了。我们只需要构造用于模式的 SQL 语句，然后我们就可以将其添加到任何页面并运行它。

///Construct Dynamic SQL to grab DataType of a particular column and table
    private String getSQLDataType(String TableName, String ColumnName)
    {
        return String.Format(DataTypes, TableName, ColumnName);
    }
    ///Construct Dynamic SQL to grab Columns of a table
    private String getSQLColumn(String TableName)
    {
        return String.Format(Columns, TableName);
    } 

private DataSet getTables()
    {
        DataSet ds = new DataSet();
        using (SqlConnection conn = getSqlConn())
        {
            SqlCommand command = new SqlCommand(Tables, conn);
            command.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds, "CompleteSchemaDatabase");
        }
        return ds;
    }
    private DataSet getColumns(String TableName)
    {
        DataSet ds = new DataSet();
        using (SqlConnection conn = getSqlConn())
        {

            SqlCommand command = new SqlCommand(String.Format(Columns, TableName), conn);
            command.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds, "CompleteColumnDatabase");
        }
        return ds;
    }
    private String getDescription(String TableName, String ColumnName)
    {
        using (SqlConnection conn = getSqlConn())
        {

            SqlCommand command = new SqlCommand
        (String.Format(ColumnDescription, TableName, ColumnName), conn);
            command.CommandType = CommandType.Text;
            Object desc = command.ExecuteScalar();
            return desc.ToString();
        }
    }
    private String getDataType(String TableName, String ColumnName)
    {
        using (SqlConnection conn = getSqlConn())
        {

            SqlCommand command = new SqlCommand(String.Format
            (DataTypes, TableName, ColumnName), conn);
            command.CommandType = CommandType.Text;
            Object datatype = command.ExecuteScalar();
            return datatype.ToString();
        }
    }
    private DataSet getFKTable(String ColumnName)
    {
        DataSet ds = new DataSet();
        try
        {
            using (SqlConnection conn = getSqlConn())
            {
                String tblName = ColumnName.Replace("fk_", "");
                tblName = "tbl_" + tblName.Replace("_id", "");
                SqlCommand command = new SqlCommand
            (String.Format(FKTable, tblName), conn);
                command.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds, "CompleteSchemaFKDatabase");
            }
        }
        catch (Exception Ex)
        { }
        return ds;
    }

代码的最后一部分是根据列数据类型或名称生成视图。

/// <summary>
/// Generates specific output controls based on the column name 
/// </summary>
/// <param name="sc"></param>
/// <param name="ColumnName"></param>
/// <param name="TableName"></param>
/// <param name="lbl"></param>
private void ProduceOutput
    (ControlCollection sc, String ColumnName, String TableName, String lbl)
{
    String datatype = getDataType(TableName, ColumnName);
    
    if ((ColumnName.Contains("user_created") == true) ||
        (ColumnName.Contains("date_created") == true) ||
        (ColumnName.Contains("status") == true) ||
        (ColumnName.Contains("_non_") == true) ||
        (ColumnName.Contains("pk_") == true)
        )
    {}
    else
    {
        if (ColumnName.Contains("fk_"))
        {
            sc.Add(ComboBox(ColumnName, ColumnName, lbl));
            return;
        }
        if (ColumnName.Contains("date_"))
        {
            sc.Add(new LiteralControl(lbl));
            sc.Add(DatePick(ColumnName));
            return;
        }
        switch (datatype)
        {
            case "int":
            case "double":
            case "float":
            case "decimal":
                RadNumericTextBox i = intBox(ColumnName, lbl);
                sc.Add(i);
                break;
            case "bit":
                RadButton btn = Checkbox(ColumnName, lbl);
                sc.Add(btn);
                break;
            default:
                RadTextBox d = Textbox(ColumnName, ColumnName, lbl);
                sc.Add(d);
                break;
        }
        return;
    }
}

就是这样。您可以更改datatype的大小写以表示您自己的控件或 ASP.NET 控件。

创建动态插入语句（使用字符串格式化）

这就是我使用字符串创建 SQL insert 语句的方式。

private String constructSql()
    {
        String TableName = Request.QueryString["tbl"].ToString();
        String sql = "INSERT INTO " + TableName + " (";
        DataSet Columns = getColumns(TableName.ToString());
        int i = 0;
        foreach (DataRow c in Columns.Tables[0].Rows)
        {
            if (i > 0)
            {
                sql += c["COLUMN_NAME"].ToString() + ",";
            }
            i++;
        }
        sql += "ats) VALUES(";
        foreach (var textboxes in TextboxNames)
        {
            if (textboxes is RadTextBox)
            {
                RadTextBox txt = textboxes as RadTextBox;
                sql += "@" + txt.ID + ",";
                i--;
            }
            if (textboxes is RadNumericTextBox)
            {
                RadNumericTextBox txt = textboxes as RadNumericTextBox;
                sql += "@" + txt.ID + ",";
                i--;
            }
            if (textboxes is RadComboBox)
            {
                RadComboBox txt = textboxes as RadComboBox;
                sql += "@" + txt.ID + ",";
                i--;
            }
            if (textboxes is RadDateTimePicker)
            {
                RadDateTimePicker txt = textboxes as RadDateTimePicker;
                sql += "@" + txt.ID + ",";
                i--;
            }
            if (textboxes is RadButton)
            {
                RadButton txt = textboxes as RadButton;
                sql += "@" + txt.ID + ",";
                i--;
            }
        }
        sql += "'User','" + DateTime.Now.ToString("s") +"','NEW')";
        i = i - 3;
        for (int j = i; j > 1; j--)
        {
            sql += "null,";
        }
        sql = sql.Replace(",ats)", ")");
        return sql;
    } 

SQL 语句的最后添加是数据库的 3 个附加字段：“user_created”、“date_created”和“status”。由于我不知道如何处理多余的逗号，我决定添加并用通配符“ats”替换值。

该方法的最终结果是一个 SQL 语句，它会注意在运行时创建的控件。

private SqlCommand AddParameters(SqlCommand com)
    {
        foreach (var textboxes in TextboxNames)
        {
            if (textboxes is RadTextBox)
            {
                RadTextBox txt = textboxes as RadTextBox;
                com.Parameters.Add("@" + txt.ID, SqlDbType.NVarChar).Value = txt.Text;
            }
            if (textboxes is RadNumericTextBox)
            {
                RadNumericTextBox txt = textboxes as RadNumericTextBox;
                com.Parameters.Add("@" + txt.ID, SqlDbType.Decimal).Value = txt.Value;
            }
            if (textboxes is RadComboBox)
            {
                RadComboBox txt = textboxes as RadComboBox;
                com.Parameters.Add("@" + txt.ID, SqlDbType.NVarChar).Value = 
                            txt.SelectedValue;
            }
            if (textboxes is RadDateTimePicker)
            {
                RadDateTimePicker txt = textboxes as RadDateTimePicker;
                com.Parameters.Add("@" + txt.ID, SqlDbType.DateTime).Value = 
                            txt.SelectedDate;
            }
            if (textboxes is RadButton)
            {
                RadButton txt = textboxes as RadButton;
                com.Parameters.Add("@" + txt.ID, SqlDbType.Bit).Value = 
                        txt.SelectedToggleState.Selected;
            }
        }
        return com;
    } 

此方法获取SqlCommand并根据控件添加参数。因此，保存按钮的最终INSERT语句将是

 SqlCommand com = new SqlCommand(constructSql());
 com = AddParameters(com); 

之后，SQL 命令被发送到 execute query 方法

private Boolean addRow(SqlCommand com)
    {
        using (SqlConnection conn = getSqlConn())
        {
            com.Connection = conn;
            int returned = com.ExecuteNonQuery();
            if (returned != 1)
            {
                return false;
            }
            else return true;
        }
    } 

保存按钮的insert语句我已经完成了。

如果有更好的方法来构造此 SQL 语句和参数，我很高兴听取您的专家意见。

关注点

就是这样。您已经注意到我大量使用了string格式化和string操作来实现这一点。而且我在数据库方面有一套严格的规则。然而，其最终结果更有趣。

这项工作尝试将通用功能扩展到其他数据库。通过更改 SQL 语句，可以轻松地使代码适应以支持其他数据库。

历史

• 初稿 - 版本 1 (Aresh Saharkhiz)
• 添加了INSERT语句和参数（2011 年 11 月 16 日）