Configure Data Source NReco.GraphQL documentation

GraphQL API's configuration takes place in two stages. The first stage is configuring DbAdapter to your database (this is component from NReco.Data open-source lib).

• MS SQL Server
• MySql
• Sqlite

// configure ADO.NET and NReco.Data components for SQL Server
var dbFactory = new DbFactory(System.Data.SqlClient.SqlClientFactory.Instance) {
	LastInsertIdSelectText = "SELECT @@IDENTITY"
};
var dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = "Data Source=mssql.customers.com,1433;Database=dbo.customers;User=graphqlUser;Password=user1;";

var dbCmdBuilder = new DbCommandBuilder(dbFactory);
dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}; ORDER BY (SELECT NULL)] @recordoffset[OFFSET {0} ROWS;OFFSET 0 ROWS] @recordcount[FETCH NEXT {0} ROWS ONLY]";
dbAdapter = new DbDataAdapter(dbConnection, dbCmdBuilder);
//Pagination: gets or sets flag that determines whether query record offset is applied during reading query results.
dbAdapter.ApplyOffset = false;

dbCmdBuilder.SelectTemplate = "SELECT @recordtop[TOP {0}] @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}]";
dbAdapter.ApplyOffset = true; // skip first N rows by cursor move

// configure ADO.NET and NReco.Data components
var dbFactory = new DbFactory(MySql.Data.MySqlClient.MySqlClientFactory.Instance) {
	LastInsertIdSelectText = "SELECT LAST_INSERT_ID()"
};
var dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = "Server=mysql.net;Database=nreco_graphql;Uid=mysqluser;Pwd=password;";

var dbCmdBuilder = new DbCommandBuilder(dbFactory);
dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table@where[ WHERE {0}]@orderby[ ORDER BY {0}] @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]";
dbAdapter = new DbDataAdapter(dbConnection, dbCmdBuilder);
//Pagination: gets or sets flag that determines whether query record offset is applied during reading query results.
dbAdapter.ApplyOffset = false;
					

// configure ADO.NET and NReco.Data components
var dbFactory = new DbFactory(Microsoft.Data.Sqlite.SqliteFactory.Instance) {
	LastInsertIdSelectText = "SELECT last_insert_rowid()"
};
var dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = "Filename=northwind.db";

var dbCmdBuilder = new DbCommandBuilder(dbFactory);
dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table@where[ WHERE {0}]@orderby[ ORDER BY {0}] @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]";
dbAdapter = new DbDataAdapter(dbConnection, dbCmdBuilder);
//Pagination: gets or sets flag that determines whether query record offset is applied during reading query results.
dbAdapter.ApplyOffset = false;
					

Second stage is defining Schema based on json configuration file (see example of "metaDbData.json" below):

var graphqlAdapter = new GraphqlDbAdapter(
	dbAdapter, JsonConvert.DeserializeObject<GraphqlConfiguration>(
		System.IO.File.ReadAllText(Path.Combine(ApplicationPath, "metaDbData.json"))
	)
);

or in code:

var graphqlConfiguration = new GraphqlConfiguration();
graphqlConfiguration.SchemaObjects = new ObjectSchema[] {
	new ObjectSchema {
		SingleName = "customer",
		ListName = "customers",
		Table = "db.customers",
		Fields = new FieldSchema[] {
			new FieldSchema {
				Name = "id",
				Column = "ID",
				DataType = TypeCode.Int32
			},
			new FieldSchema {
				Name = "name",
				DataType = TypeCode.String
			}
		}
	};
var graphqlAdapter = new GraphqlDbAdapter(dbAdapter, graphqlConfiguration);	

Configuration JSON example

{
	"Options": {
		"Pagination": {
			"ArgumentNameForFirst": "limitItems"
		}
	},
	"SchemaObjects": [
		{
			"SingleName": "customer",
			"ListName": "customers",
			"Table": "Customers",
			"Description": "Customer table - contains data about customer",
			"Fields": [
				{
					"Name": "id",
					"Column": "CustomerID",
					"DataType": "string",
					"Description": "Unique identificator - using also to connect with orders"
				},
				{
					"Name": "name",
					"DataType": "string"
				}
			],
			"RelatedObjects": [
				{
					"Name": "order", // refers to the name of related object
					"Relex": "orders(CustomerID=\"id\":var)[*]" // query used to load related object
				},
				{
					"Name": "orders",
					"Relex": "orders(CustomerID=\"id\":var)[*]"
				}
			]
		},
		{
			"SingleName": "order",
			"ListName": "orders",
			"Table": "Orders",
			"Fields": [
				{
					"Name": "orderId",
					"Column": "OrderID",
					"DataType": "int32"
				},
				{
					"Name": "customerId",
					"Column": "CustomerID",
					"DataType": "string"
				},
				{
					"Name": "ShipName",
					"DataType": "string"
				}
			]
		}
	]
}