maskx.OData support build a Odata database proxy turn your database into a OData WebApi server. Dynamic generate OData Controller from database.

With the web server build by maskx.odata, the client can do:

• Query, insert, delete, update the database table
• Query the view and table-valued function
• Invoke the stored procedure

The Web API is followed the OData protocol (http://www.odata.org/)

• Create a asp.net core Web API project

• Install nuget package from https://www.nuget.org/packages/maskx.OData/

• Configure the controller

  class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddOData();
            services.AddMvc();
        }
        public void Configure(IApplicationBuilder app)
        {
            app.UseMvc(routeBuilder => {
                routeBuilder.MapDynamicODataServiceRoute("odata","db1",
                    new maskx.OData.Sql.SQL2012("odata", "Data Source=.;Initial Catalog=Group;Integrated Security=True"));
            });
        }
    }

• Hit F5 to start the web server or deploy the project

Now you can access the database through the web API. you can visit this page for basic OData knowledge: http://www.odata.org/getting-started/understand-odata-in-6-steps/

OData is case-sensitive, if you want case-insensitive, see Configuration

• Query Table

  $.get('db1/<table name>')
  .done(function (data) {alert(data.value) });

• Query View

 $.get('db1/<view name>')
 .done(function (data) {alert(data.value) });

• Query Table-valued function

 $.get('db1/<Table-valued function name>()')
 .done(function (data) {alert(data.value) });

  $.get('db1/<table name>(<the value of ID>)')
  .done(function (data) {alert(data) });

not support yet

you can user:

• $filter
• $orderby
• $top
• $skip
• $count
• $expand
• $select
• $search

• Create an Entity

  $.post('db1/<table>',{
    'col1':'col1 value',
    'col2':'col2 value',
    ...
  }).done(function (data) {alert(data.value) });

• Update an Entity

  $.ajax({
    url:'db1/<table>(<ID>)',
    type:'PUT',
    data:{
      'col1':'col1 value',
      'col2':'col2 value',
      ...
    }
  }).done(function (data) {alert(data)});

• Merge an Entity

$.ajax({
    url:'db1/<table>(<ID>)',
    type:'PATCH',
    data:{
      'col1':'col1 value',
      'col2':'col2 value',
      ...
    }
  }).done(function (data) {alert(data)});

• Delete an Entity

  $.ajax({
    url:'db1/<table>(<ID>)',
    type:'DELETE'
  }).done(function (data) {alert(data) });

for view, only query is supported

$.get('db1/<view>').done(function (data) {alert(data.value) });

 $.post('db1/<Stroed procedure name >()',{
    'par1':'par1 value',
    'par2':'par2 value',
    ...
  }).done(function (data) {alert(data) });

$.get('db1/<table-valued function name>()')
.done(function (data) {alert(data.value) });

• Parameter

   $.get('db1/<table-valued function name>(ParameterName1=arameterValue1，ParameterName2=ParameterValue2)')
   .done(function (data) {alert(data.value) });

• Querying

you can query a table-valued function as a table

$.get('db1/<table-valued function name>()')
.done(function (data) {alert(data.value) });

the default schema of sql server is dbo, so you can query the table by name directly when the table's shcema is dbo

when the schema of a table is not dbo, you must query the table with schema name

$.get('db1/<schema name>.<table name>')
.done(function (data) {alert(data.value) });

• Customer default schema

If you want make another schema( not dbo) as your default schema for the query url convenient, you can change it

public void Configure(IApplicationBuilder app)
{
  app.UseMvc(routeBuilder =>
  {
     var dataSource = new maskx.OData.Sql.SQL2012("odata", "Data Source=.;Initial Catalog=Group;Integrated Security=True");
     dataSource.Configuration.DefaultSchema = "schemaB";
     routeBuilder.MapDynamicODataServiceRoute("odata1", "db1", dataSource);
  });
}

SQLDataSource has a BeforeExcute property, you can judge user's permission in there

new maskx.OData.Sql.SQL2012(<DataSourceName>)
{
   BeforeExcute = (ri) =>{
      if (ri.QueryOptions != null && ri.QueryOptions.SelectExpand != null) {

      }
      Console.WriteLine("BeforeExcute:{0}", ri.Target);
   }
 };

SQLDataSource has a BeforeExcute and AfterExcute properties, you can judge user's permission in there

• DefaultSchema :Defalut Schema name, default is dbo
• LowerName: make the name of database object to lower, default is false

Contributions are absolutely welcome!

1. Fork it!
2. Create your feature branch: git checkout -b my-new-feature
3. Commit your changes: git commit -am 'Add some feature'
4. Push to the branch: git push origin my-new-feature Submit a pull request :D

The MIT License (MIT) - See file 'LICENSE' in this project