C＃中的动态odata服务来自运行时数据层

I'm trying to create a dynamic odata service from tables in my tables which are not known till runtime. So at the start of my web application, a user selects a database and in C# I find all the tables in that database.

我正在尝试从表中的表创建动态odata服务，这些表在运行时才知道。因此，在我的Web应用程序开始时，用户选择一个数据库，在C＃中我找到该数据库中的所有表。

Now the hard part is I want to create odata service endpoints for each table in the database and use them accordingly in my web application. The problem is I don't know how to do it dynamically. There are lots of examples with compile time known database tables, but in this case, I won't have them till the first time my user uses my application.

现在困难的部分是我想为数据库中的每个表创建odata服务端点，并在我的Web应用程序中相应地使用它们。问题是我不知道如何动态地做到这一点。编译时已知的数据库表有很多例子，但在这种情况下，直到我的用户第一次使用我的应用程序时才会有它们。

2 个解决方案

#1

There is an example that doesn't require a predefined class available here: ODataUntypedSample, but it does require a predefined controller.

有一个示例不需要此处提供的预定义类：ODataUntypedSample，但它确实需要预定义的控制器。

I have built on it another console application sample to be able to query any SQL server database using OData. I've used this nuget package to read the database schema and data: DatabaseSchemaReader. You'll need the following nuget package to be able to build it (plus dependencies):

我已经构建了另一个控制台应用程序示例，以便能够使用OData查询任何SQL Server数据库。我已经使用这个nuget包来读取数据库模式和数据：DatabaseSchemaReader。您需要以下nuget包才能构建它（以及依赖项）：

• Microsoft.Owin.Hosting
• Microsoft.Owin.Hosting
• Microsoft.Owin.Host.HttpListener
• Microsoft.Owin.Host.HttpListener
• Microsoft.AspNet.WebApi.Owin
• Microsoft.AspNet.WebApi.Owin
• Microsoft.AspNet.OData
• Microsoft.AspNet.OData

Here is the main program slightly modified so it declares Edm (for OData) entities from tables. I've tested the standard sample Adventure Works 2014 but it should work on any table hopefully:

这是稍微修改的主程序，因此它从表中声明了Edm（用于OData）实体。我已经测试了标准样本Adventure Works 2014，但它应该适用于任何表格：

class Program
{
    private static HttpClient client = new HttpClient();
    private static TableControllerSelector selector;
    private const string ServiceUrl = "http://localhost:12345";
    private const string connectionString = @"Server=MYSQLSERVER;Database=AdventureWorks2014;Integrated Security=SSPI";

    static void Main(string[] args)
    {
        using (WebApp.Start(ServiceUrl, Configuration))
        {
            Console.WriteLine("Server is listening at {0}", ServiceUrl);

            RunSample();

            Console.WriteLine("Press any key to continue . . .");
            Console.ReadKey();
        }
    }

    public static void Configuration(IAppBuilder builder)
    {
        HttpConfiguration configuration = new HttpConfiguration();

        // create a special dynamic controller selector
        selector = new TableControllerSelector(configuration);
        IEdmModel  model = TableController.GetEdmModel(connectionString, selector);
        configuration.Services.Replace(typeof(IHttpControllerSelector), selector);

        configuration.MapODataServiceRoute("odata", "odata", model); // needs using System.Web.OData.Extensions
        builder.UseWebApi(configuration);
    }

    public static void RunSample()
    {
        Console.WriteLine("1. Get Metadata.");
        GetMetadata();

        Console.WriteLine("\n2. Get Entity Set.");
        using (var dbReader = new DatabaseReader(connectionString, "System.Data.SqlClient"))
        {
            foreach (var table in dbReader.AllTables())
            {
                Console.WriteLine("\n 2.1 Get Entity Set '" + table.Name  + "'.");
                GetEntitySet(table.Name);
            }
        }
    }

    public static void GetMetadata()
    {
        HttpResponseMessage response = client.GetAsync(ServiceUrl + "/odata/$metadata").Result;
        PrintResponse(response);
    }

    public static void GetEntitySet(string tableName)
    {
        HttpResponseMessage response = client.GetAsync(ServiceUrl + "/odata/" + tableName + "?$filter=Id eq 1").Result;
        PrintResponse(response);
    }

    public static void PrintResponse(HttpResponseMessage response)
    {
        response.EnsureSuccessStatusCode();
        Console.WriteLine("Response:");
        Console.WriteLine(response);

        if (response.Content != null)
        {
            Console.WriteLine(response.Content.ReadAsStringAsync().Result);
        }
    }
}

And the special TableController and TableControllerSelector classes that allow to create an Edm model from any SQL Server database, and create controllers dynamically from the Edm entities in that model:

还有特殊的TableController和TableControllerSelector类，它们允许从任何SQL Server数据库创建Edm模型，并从该模型中的Edm实体动态创建控制器：

public class TableControllerSelector : DefaultHttpControllerSelector
{
    private Dictionary<string, HttpControllerDescriptor> _tables = new Dictionary<string, HttpControllerDescriptor>(StringComparer.OrdinalIgnoreCase);

    public TableControllerSelector(HttpConfiguration configuration)
        : base(configuration)
    {
        Configuration = configuration;
    }

    public HttpConfiguration Configuration { get; private set; }

    public override HttpControllerDescriptor SelectController(HttpRequestMessage request)
    {
        string name = GetControllerName(request);
        if (name != null) // is it a known table name?
        {
            HttpControllerDescriptor desc;
            if (_tables.TryGetValue(name, out desc))
                return desc;
        }

        return base.SelectController(request);
    }

    public void AddTable(string connectionString, DatabaseTable table)
    {
        if (connectionString == null)
            throw new ArgumentNullException("connectionString");

        if (table == null)
            throw new ArgumentNullException("table");

        // create a descriptor with extra properties that the controller needs
        var desc = new HttpControllerDescriptor(Configuration, table.Name, typeof(TableController));
        desc.Properties["table"] = table;
        desc.Properties["connectionString"] = connectionString;
        _tables[table.Name] = desc;
    }
}

public class TableController : ODataController
{
    // this will be called for standard OData access to collection
    public EdmEntityObjectCollection Get()
    {
        // get Edm type from request
        ODataPath path = Request.ODataProperties().Path; // ODataProperties() needs using System.Web.OData.Extensions
        IEdmType edmType = path.EdmType;

        IEdmCollectionType collectionType = (IEdmCollectionType)edmType;
        IEdmEntityType entityType = (IEdmEntityType)collectionType.ElementType.Definition;
        IEdmModel model = Request.ODataProperties().Model;

        ODataQueryContext queryContext = new ODataQueryContext(model, entityType, path);
        ODataQueryOptions queryOptions = new ODataQueryOptions(queryContext, Request);

        // TODO: apply the query option on the IQueryable here.

        // read all rows from table (could be optimized using query context)
        var table = (DatabaseTable)ControllerContext.ControllerDescriptor.Properties["table"];
        var cnx = (string)ControllerContext.ControllerDescriptor.Properties["connectionString"];

        return new EdmEntityObjectCollection(new EdmCollectionTypeReference(collectionType), ReadData(entityType, table, cnx));
    }

    public static IList<IEdmEntityObject> ReadData(IEdmEntityType type, DatabaseTable table, string connectionString)
    {
        List<IEdmEntityObject> list = new List<IEdmEntityObject>();

        // https://www.nuget.org/packages/DatabaseSchemaReader/
        Reader reader = new Reader(table, connectionString, "System.Data.SqlClient");
        reader.Read((r) =>
        {
            EdmEntityObject obj = new EdmEntityObject(type);
            foreach (var prop in type.DeclaredProperties)
            {
                int index = r.GetOrdinal(prop.Name);
                object value = r.GetValue(index);
                if (Convert.IsDBNull(value))
                {
                    value = null;
                }
                obj.TrySetPropertyValue(prop.Name, value);
            }

            list.Add(obj);
            // uncomment these 2 lines if you're just testing maximum 10 rows on a table 
            //if (list.Count == 10)
            //    return false;

            return true;
        });
        return list;
    }

    public static IEdmModel GetEdmModel(string connectionString, TableControllerSelector selector)
    {
        EdmModel model = new EdmModel();

        // create and add entity container
        EdmEntityContainer container = new EdmEntityContainer("NS", "DefaultContainer");
        model.AddElement(container);

        // https://www.nuget.org/packages/DatabaseSchemaReader/
        using (var dbReader = new DatabaseReader(connectionString, "System.Data.SqlClient"))
        {
            var schema = dbReader.ReadAll();
            foreach (var table in schema.Tables)
            {
                EdmEntityType tableType = new EdmEntityType("NS", table.Name);
                foreach (var col in table.Columns)
                {
                    var kind = GetKind(col);
                    if (!kind.HasValue) // don't map this
                        continue;

                    var prop = tableType.AddStructuralProperty(col.Name, kind.Value, col.Nullable);
                    if (col.IsPrimaryKey)
                    {
                        tableType.AddKeys(prop);
                    }
                }
                model.AddElement(tableType);

                EdmEntitySet products = container.AddEntitySet(table.Name, tableType);
                selector.AddTable(connectionString, table);
            }
        }

        return model;
    }

    // determine Edm kind from column type
    private static EdmPrimitiveTypeKind? GetKind(DatabaseColumn col)
    {
        var dt = col.DataType;
        if (col.DataType == null)
            return null;

        Type type = col.DataType.GetNetType();
        if (type == null)
            return null;

        if (type == typeof(string))
            return EdmPrimitiveTypeKind.String;

        if (type == typeof(short))
            return EdmPrimitiveTypeKind.Int16;

        if (type == typeof(int))
            return EdmPrimitiveTypeKind.Int32;

        if (type == typeof(long))
            return EdmPrimitiveTypeKind.Int64;

        if (type == typeof(bool))
            return EdmPrimitiveTypeKind.Boolean;

        if (type == typeof(Guid))
            return EdmPrimitiveTypeKind.Guid;

        if (type == typeof(DateTime))
            return EdmPrimitiveTypeKind.DateTimeOffset;

        if (type == typeof(TimeSpan))
            return EdmPrimitiveTypeKind.Duration;

        if (type == typeof(decimal))
            return EdmPrimitiveTypeKind.Decimal;

        if (type == typeof(byte) || type == typeof(sbyte))
            return EdmPrimitiveTypeKind.Byte;

        if (type == typeof(byte[]))
            return EdmPrimitiveTypeKind.Binary;

        if (type == typeof(double))
            return EdmPrimitiveTypeKind.Double;

        if (type == typeof(float))
            return EdmPrimitiveTypeKind.Single;

        return null;
    }
}

#2

1. Have you tried using a dictionary? I don't know if it works with OData, just an idea which came up as all kinds of data connection/deserialisers I've worked with also worked with dictionary.

你尝试过使用字典吗？我不知道它是否适用于OData，只是一个想法，因为我使用的各种数据连接/解串器也使用字典。

2. The idea I'm more into, is to retrieve a class from the data source, I found something here that might help you: Class to DataSet / DataSet to class, maybe if no more obstacles exist, using the data returned to build a structure to put data inside ...

2.我更喜欢的想法是从数据源中检索一个类，我在这里找到了一些可能对你有帮助的东西：Class to DataSet / DataSet to class，也许如果不存在更多障碍，使用返回的数据来构建将数据放入内部的结构......

Sorry for being somehow uncertain about the answers, just ideas came to my head, I don't know much about OData. Hope it helped.

很抱歉因为某种方式不确定答案，只是想法来到我的脑海，我对OData知之甚少。希望它有所帮助。