如何在ASP.NET中动态创建SQL表？ [英] How do I dynamically create a SQL table in ASP.NET?

问题描述

我正在尝试确定解决以下问题的最佳方法：



我的应用程序需要允许用户定义表结构，然后创建表在SQL中出了这个结构。然后应用程序需要能够允许用户在新表上执行CRUD操作。



我认为我正在寻找的是类似于CMS应用程序的操作当他们创建自定义内容时。



完成此任务的最佳方法是什么？



我的尝试：



我一直在做很多谷歌搜索，但我没有找到类似于我一直在寻找的东西。

I am trying to determine the best way to solve the following problem:

My application needs to allow a user to define a table structure, then create a table in SQL out of this structure. Then the application needs to be able to allow the user to perform CRUD operations on the new table.

I think what I am looking for is similar to what CMS applications do when they create custom content.

What is the best way to accomplish this?

What I have tried:

I've been doing a lot of googling but I haven't found anything similar to what I have been looking for.

推荐答案

0）允许用户指定列名和类型。



1）创建一个存储对象的集合您的列名称和类型。确保添加代码以防止重复的列名称。您可以使用多种技术来对此代码进行黑名单，以便隐藏验证复杂性。我会让你成为一名程序员，并指出需要多少验证，以及应该在哪里实现。



2）构建一个表示所需查询的字符串。

0) Allow the user to specify the column names and types.

1) Create a collection of an object that store your column names and types. Make sure you add code to prevent duplicate column names. There are several techniques you can use to black-box this code so you can hide validation complexity. I'll let you be a programmer and finger out how much validation is necessary, and where it should be implemented.

2) Build a string that represents the desired query.

string userTable = "MyTable";
HashSet columns = new HashSet<string,string>(); // populate this somewhere else

StringBuilder query = new StringBuilder();
query.AppendFormat("IF OBJECT_ID('{0}','U') IS NOT NULL DROP TABLE dbo.{0}; ", userTable).AppendLine();
query.AppendFormat("CREATE TABLE dbo.{0} (", userTable);

MyColumnItem lastColumn = columns.Last();

foreach(MyColumnItem item in columns)
{
    query.AppendFormat("{0} {1}", item.ColumnName, item.ColumnType);
    it (!item.Equals(lastItem)
    {
        query.Append(",");
    }
}
query.Append(");");

3）使用ADO类执行查询。

3) Use ADO classes to execute the query.

这篇关于如何在ASP.NET中动态创建SQL表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！