DevelopmentHelpers.QueryBuilder
1.0.3
There is a newer version of this package available.
See the version list below for details.
See the version list below for details.
dotnet add package DevelopmentHelpers.QueryBuilder --version 1.0.3
NuGet\Install-Package DevelopmentHelpers.QueryBuilder -Version 1.0.3
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="DevelopmentHelpers.QueryBuilder" Version="1.0.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DevelopmentHelpers.QueryBuilder --version 1.0.3
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: DevelopmentHelpers.QueryBuilder, 1.0.3"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install DevelopmentHelpers.QueryBuilder as a Cake Addin #addin nuget:?package=DevelopmentHelpers.QueryBuilder&version=1.0.3 // Install DevelopmentHelpers.QueryBuilder as a Cake Tool #tool nuget:?package=DevelopmentHelpers.QueryBuilder&version=1.0.3
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
DevelopmentHelpers.QueryBuilder
This library allows a way to build Query and export data in Json and Xml from SQL SERVER .net core applicaitons
Code Example
1. To Create a Query From Xml File, Readme file contain examples of Query in Xml format
XElement queryElement = XElement.Load(filePath);
var helper = new QueryMapper();
Query query = helper.Get(queryElement);
2a. Create MSSQL server Data Store Helper
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
2b. Create a Datatable from Query for MSSQL Server FOR SELECT Statement
DataTable dataTable = await dataStoreHelper.GetDataTableAsync(query);
2c. Create a table object from Select Query
Table table = await dataStoreHelper.GetTableAsync(query);
3. Add Query to Database
List<Parameter> parameters = await dataStoreHelper.AddAsync(query);
var primaryKeyParameter = parameterMapper.GetParameter("Your Parameter Name ",parameters);
4. Delete Query
bool deleted = await dataStoreHelper.DeleteAsync(query);
5. Update Or Add Query without parameters, return no of rows affected
int saved = await dataStoreHelper.SaveAsync(query);
6. Create a Table Mapper for xml and Json formats
TableMapper tableMapper = new TableMapper();
7. Create a Table from DataTable
Table table = tableMapper.Get(dataTable);
8. Create xml From Table Mapper
XElement xElement = tableMapper.GetXml(table);
9. Create Json from Table Mapper
JsonDocument json = tableMapper.GetJson(model);
10. Validate Json/Xml with var validateHelper = new ValidateHelper();
bool isXmlValid = validateHelper.IsValidXml(xElement.ToString());
bool isJsonValid = validateHelper.IsJsonObject(json);
11. Sample Xml for Add Query
<Query>
<Name>Add</Name>
<DataProviderType>System.Data.SqlClient</DataProviderType>
<DataStoreType>Microsoft SQL Server</DataStoreType>
<QueryType>Add</QueryType>
<Statement>SET @CategoryID=(SELECT IsNull(MAX(CategoryID)+1,1) FROM [dbo].[Categories])
INSERT INTO [dbo].[Categories]
(
[CategoryName]
,[Description]
,[Picture]
)
values
(
@CategoryName
,@Description
,@Picture
)
</Statement>
<Parameters>
<Parameter>
<Name>CategoryID</Name>
<IsNullable>false</IsNullable>
<ParameterDirection>InputOutput</ParameterDirection>
<DataStoreDataType>int</DataStoreDataType>
<DbType>Int32</DbType>
<Precision>10</Precision>
<Scale>0</Scale>
<Value>1</Value>
</Parameter>
<Parameter>
<Name>CategoryName</Name>
<IsNullable>false</IsNullable>
<ParameterDirection>Input</ParameterDirection>
<DataStoreDataType>nvarchar</DataStoreDataType>
<DbType>String</DbType>
<Precision>0</Precision>
<Scale>0</Scale>
<Value>Beverages</Value>
</Parameter>
<Parameter>
<Name>Description</Name>
<IsNullable>true</IsNullable>
<ParameterDirection>Input</ParameterDirection>
<DataStoreDataType>ntext</DataStoreDataType>
<DbType>String</DbType>
<Precision>0</Precision>
<Scale>0</Scale>
<Value>Soft drinks, coffees, teas, beers, and ales</Value>
</Parameter>
<Parameter>
<Name>Picture</Name>
<IsNullable>true</IsNullable>
<ParameterDirection>Input</ParameterDirection>
<DataStoreDataType>image</DataStoreDataType>
<DbType>Object</DbType>
<Precision>0</Precision>
<Scale>0</Scale>
<Value>祓瑳浥䈮瑹孥�</Value>
</Parameter>
</Parameters>
</Query>
12. Sample Xml for Paging Query
<Query>
<Name>GetByPaging</Name>
<DataProviderType>System.Data.SqlClient</DataProviderType>
<DataStoreType>Microsoft SQL Server</DataStoreType>
<QueryType>SelectByPaging</QueryType>
<Statement>
Select
[CategoryID] as 'CategoryID',
[CategoryName] as 'CategoryName',
[Description] as 'Description',
[Picture] as 'Picture'
from [dbo].[Categories]
</Statement>
<OrderByColumnName>CategoryID</OrderByColumnName>
</Query>
13. Sample Xml for Delete Query
<Query>
<Name>Delete</Name>
<DataProviderType>System.Data.SqlClient</DataProviderType>
<DataStoreType>Microsoft SQL Server</DataStoreType>
<QueryType>Delete</QueryType>
<Statement> Delete From [dbo].[Categories]
Where [CategoryID] =@CategoryID
</Statement>
<Parameters>
<Parameter>
<Name>CategoryID</Name>
<IsNullable>false</IsNullable>
<ParameterDirection>InputOutput</ParameterDirection>
<DataStoreDataType>int</DataStoreDataType>
<DbType>Int32</DbType>
<Precision>10</Precision>
<Scale>0</Scale>
<Value>1</Value>
</Parameter>
</Parameters>
</Query>
14. Sample Xml for Get Query
<Query>
<Name>Get</Name>
<DataProviderType>System.Data.SqlClient</DataProviderType>
<DataStoreType>Microsoft SQL Server</DataStoreType>
<QueryType>Select</QueryType>
<Statement>
Select
[CategoryID] as 'CategoryID',
[CategoryName] as 'CategoryName',
[Description] as 'Description',
[Picture] as 'Picture'
from [dbo].[Categories]
</Statement>
</Query>
15. Sample Xml for Get Single Item Query
<Query>
<Name>GetByPk</Name>
<DataProviderType>System.Data.SqlClient</DataProviderType>
<DataStoreType>Microsoft SQL Server</DataStoreType>
<QueryType>Select</QueryType>
<Statement> Select
[CategoryID] as 'CategoryID',
[CategoryName] as 'CategoryName',
[Description] as 'Description',
[Picture] as 'Picture'
from [dbo].[Categories]
Where [CategoryID] =@CategoryID
</Statement>
<Parameters>
<Parameter>
<Name>CategoryID</Name>
<IsNullable>false</IsNullable>
<ParameterDirection>InputOutput</ParameterDirection>
<DataStoreDataType>int</DataStoreDataType>
<DbType>Int32</DbType>
<Precision>10</Precision>
<Scale>0</Scale>
<Value>1</Value>
</Parameter>
</Parameters>
</Query>
16. Sample Xml for Update Query
<Query>
<Name>Update</Name> <DataProviderType>System.Data.SqlClient</DataProviderType> <DataStoreType>Microsoft SQL Server</DataStoreType> <QueryType>Update</QueryType> <Statement> UPDATE [dbo].[Categories] SET [CategoryName]=@CategoryName ,[Description]=@Description ,[Picture]=@Picture Where [CategoryID] =@CategoryID </Statement> <Parameters> <Parameter> <Name>CategoryID</Name> <IsNullable>false</IsNullable> <ParameterDirection>InputOutput</ParameterDirection> <DataStoreDataType>int</DataStoreDataType> <DbType>Int32</DbType> <Precision>10</Precision> <Scale>0</Scale> <Value>1</Value> </Parameter> <Parameter> <Name>CategoryName</Name> <IsNullable>false</IsNullable> <ParameterDirection>Input</ParameterDirection> <DataStoreDataType>nvarchar</DataStoreDataType> <DbType>String</DbType> <Precision>0</Precision> <Scale>0</Scale> <Value>Beverages</Value> </Parameter> <Parameter> <Name>Description</Name> <IsNullable>true</IsNullable> <ParameterDirection>Input</ParameterDirection> <DataStoreDataType>ntext</DataStoreDataType> <DbType>String</DbType> <Precision>0</Precision> <Scale>0</Scale> <Value>Soft drinks, coffees, teas, beers, and ales</Value> </Parameter> <Parameter> <Name>Picture</Name> <IsNullable>true</IsNullable> <ParameterDirection>Input</ParameterDirection> <DataStoreDataType>image</DataStoreDataType> <DbType>Object</DbType> <Precision>0</Precision> <Scale>0</Scale> <Value>祓瑳浥䈮瑹孥�</Value> </Parameter> </Parameters> </Query>
Motivation
I needed a way to transport data from sql server to xml and json format, and to read it back for inserting and recreating data store between two servers on a on demand basis
API Reference
Tests
[TestClass]
public class SqlTableTests
{
FileHelper fileHelper;
QueryMapper helper;
ValidateHelper validateHelper;
TableMapper tableMapper;
ILogger<QueryHelperTest> _logger;
[TestInitialize]
public void TestInitialize()
{
fileHelper = new FileHelper();
helper = new QueryMapper();
validateHelper = new ValidateHelper();
tableMapper = new TableMapper();
_logger = (ILogger<QueryHelperTest>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<QueryHelperTest>));
if (!Directory.Exists(KernelMapper.TableFolderPath))
{
Directory.CreateDirectory(KernelMapper.TableFolderPath);
}
}
[DataTestMethod]
[DataRow(nameof(Categories))]
[DataRow(nameof(CustomerCustomerDemo))]
[DataRow(nameof(CustomerDemographics))]
[DataRow(nameof(Customers))]
[DataRow(nameof(Employees))]
[DataRow(nameof(EmployeeTerritories))]
[DataRow(nameof(Orders))]
[DataRow(nameof(Products))]
[DataRow(nameof(Region))]
[DataRow(nameof(Shippers))]
[DataRow(nameof(Suppliers))]
[DataRow(nameof(Territories))]
[DataRow(nameof(Test))]
public async Task Get_DataTable_And_Generate_Xml_Test(string tableName)
{
Stopwatch watch = new();
watch.Start();
_logger.LogInformation($"Executing Get_DataTable_And_Generate_Xml_Test for Table:{tableName}");
Query query = new()
{
DataProviderType = QueryBuilder.Helpers.Enums.DataProviderType.SqlClient,
DataStoreType = QueryBuilder.Helpers.Enums.DataStoreType.Mssql,
Name = tableName,
QueryType = QueryBuilder.Helpers.Enums.QueryType.Select,
Statement = $"Select * from {tableName}"
};
_logger.LogInformation($"Starting Request for Query: {query.Name} of QueryType {query.QueryType.GetDescription()}");
_logger.LogInformation($"Statement{query.Statement}");
watch.Restart();
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
DataTable dataTable = await dataStoreHelper.GetDataTableAsync(query);
dataTable.TableName = tableName;
Assert.IsTrue(dataTable != null);
_logger.LogInformation($"Record Count: {dataTable.Rows.Count}");
_logger.LogInformation($"Finishing Converting to Datatable for request Query: {query.Name} of QueryType {query.QueryType.GetDescription()} and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
watch.Restart();
//Test for the table record
Table model = tableMapper.Get(dataTable);
Assert.IsNotNull(model);
Assert.IsTrue(model.Columns.Count > 0);
//Generate Xml file
XElement xElement = tableMapper.GetXml(model);
Assert.IsTrue(validateHelper.IsValidXml(xElement.ToString()));
//Save the file
fileHelper.SaveXElement(xElement, Path.Combine(KernelMapper.TableFolderPath, $"{tableName}.xml"));
_logger.LogInformation($"Finishing Converting to Xml file and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
//Test back with Model
Table tableModelAgain = tableMapper.Get(xElement);
Assert.IsTrue(model.Name == tableModelAgain.Name);
//Read Xml File
var xmlDataFromXmlFile = fileHelper.ReadFileAsString(Path.Combine(KernelMapper.TableFolderPath, $"{tableName}.xml"));
XElement tableXElement = XElement.Parse(xmlDataFromXmlFile.ToString());
var tableFromFileModel = tableMapper.Get(tableXElement);
_logger.LogInformation($"Finishing Reading from Xml file and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
watch.Stop();
Assert.IsTrue(tableFromFileModel.Name == tableModelAgain.Name);
_logger.LogInformation($"End Executing Get_DataTable_And_Generate_Xml_Test for Table:{tableName}");
}
[DataTestMethod]
[DataRow(nameof(Categories))]
[DataRow(nameof(CustomerCustomerDemo))]
[DataRow(nameof(CustomerDemographics))]
[DataRow(nameof(Customers))]
[DataRow(nameof(Employees))]
[DataRow(nameof(EmployeeTerritories))]
[DataRow(nameof(Orders))]
[DataRow(nameof(Products))]
[DataRow(nameof(Region))]
[DataRow(nameof(Shippers))]
[DataRow(nameof(Suppliers))]
[DataRow(nameof(Territories))]
[DataRow(nameof(Test))]
public async Task Get_DataTable_And_Generate_Json_Test(string tableName)
{
Stopwatch watch = new();
watch.Start();
_logger.LogInformation($"Executing Get_DataTable_And_Generate_Xml_Test for Table:{tableName}");
Query query = new()
{
DataProviderType = QueryBuilder.Helpers.Enums.DataProviderType.SqlClient,
DataStoreType = QueryBuilder.Helpers.Enums.DataStoreType.Mssql,
Name = tableName,
QueryType = QueryBuilder.Helpers.Enums.QueryType.Select,
Statement = $"Select * from {tableName}"
};
_logger.LogInformation($"Starting Request for Query: {query.Name} of QueryType {query.QueryType.GetDescription()}");
_logger.LogInformation($"Statement{query.Statement}");
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
DataTable dataTable = await dataStoreHelper.GetDataTableAsync(query);
dataTable.TableName = tableName;
Assert.IsTrue(dataTable != null);
_logger.LogInformation($"Record Count: {dataTable.Rows.Count}");
_logger.LogInformation($"Finishing Converting to Datatable for request Query: {query.Name} of QueryType {query.QueryType.GetDescription()} and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
watch.Restart();
//Test for the table record
Table model = tableMapper.Get(dataTable);
Assert.IsNotNull(model);
Assert.IsTrue(model.Columns.Count > 0);
//Generate Json file
JsonDocument json = tableMapper.GetJson(model);
Assert.IsTrue(validateHelper.IsJsonObject(json));
_logger.LogInformation($"Finishing Converting to Json file and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
watch.Restart();
//Save the file
fileHelper.SaveJson(json, Path.Combine(KernelMapper.TableFolderPath, $"{tableName}.json"));
//Test back with Model
Table tableModelAgain = tableMapper.Get(json);
Assert.IsTrue(model.Name == tableModelAgain.Name);
//Read Xml File
var fileJson = fileHelper.ReadFileAsString(Path.Combine(KernelMapper.TableFolderPath, $"{tableName}.json"));
JsonDocument jsonDocument = JsonDocument.Parse(fileJson.ToString());
var tableFromFileModel = tableMapper.Get(jsonDocument);
_logger.LogInformation($"Finishing Reading from Json file and it took {watch.Elapsed.Hours}:{watch.Elapsed.Minutes}:{watch.Elapsed.Seconds}:{watch.Elapsed.Milliseconds / 10}");
watch.Stop();
_logger.LogInformation($"End Executing Get_DataTable_And_Generate_Json_Test for Table:{tableName}");
Assert.IsTrue(tableFromFileModel.Name == tableModelAgain.Name);
}
[DataTestMethod]
[DataRow(10)]
public async Task Insert_Large_RecordSetAsync(int noOfRecords)
{
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
Random random = new();
string tableName = "Test";
Stopwatch watch = new();
watch.Start();
_logger.LogInformation($"Executing Insert_Large_RecordSet for Table:{tableName}, no of Records to be inserted:{noOfRecords}");
string statement = $"INSERT INTO [dbo].[Test]([Id],[Name],[Email],[Country]) VALUES ( @Id,@Name,@Email,@Country)";
int count = 1;
for (count = 1; count <= noOfRecords; count++)
{
string name = $"muatassim_{count}";
var query = new Query
{
QueryType = QueryType.Add,
DataProviderType = DataProviderType.SqlClient,
DataStoreType = DataStoreType.Mssql,
Statement = statement
};
#region //Create Parameters LIst
List<Parameter> parametersList = new();
//Id Parameter
Parameter IdParameter = DataHelper.GetParameter(
nameof(Helpers.Northwind.Entities.dbo.Test.Id),
Guid.NewGuid(),
System.Data.ParameterDirection.InputOutput,
System.Data.DbType.Guid, "",
query.DataProviderType, query.DataStoreType);
parametersList.Add(IdParameter);
////Name Parameter
Parameter nameParameter = DataHelper.GetParameter(
nameof(Helpers.Northwind.Entities.dbo.Test.Name),
name,
System.Data.ParameterDirection.InputOutput,
System.Data.DbType.String, SqlDbType.NVarChar.GetDescription(),
query.DataProviderType, query.DataStoreType);
parametersList.Add(nameParameter);
////Email Parameter
Parameter emailParameter = DataHelper.GetParameter(
nameof(Helpers.Northwind.Entities.dbo.Test.Email),
$"{name}@hotmail.com",
System.Data.ParameterDirection.InputOutput,
System.Data.DbType.String, SqlDbType.NVarChar.GetDescription(),
query.DataProviderType, query.DataStoreType);
parametersList.Add(emailParameter);
////Country Parameter
int index = random.Next(DataHelper.Countries.Count);
Parameter countryParameter = DataHelper.GetParameter(
nameof(Helpers.Northwind.Entities.dbo.Test.Country),
DataHelper.Countries.ElementAt(index).Key,
System.Data.ParameterDirection.InputOutput,
System.Data.DbType.String, SqlDbType.NVarChar.GetDescription(),
query.DataProviderType, query.DataStoreType);
parametersList.Add(countryParameter);
//Parameter createdOnParameter = DataHelper.GetParameter(
// nameof(Helpers.Northwind.Entities.dbo.Test.CreatedOn),
// DateTime.UtcNow,
// System.Data.ParameterDirection.InputOutput,
// System.Data.DbType.DateTime, SqlDbType.DateTime.GetDescription(),
// query.DataProviderType, query.DataStoreType);
//parametersList.Add(createdOnParameter);
#endregion
query.Name = $"Add {name}";
query.Parameters = parametersList;
List<Parameter> parameters = await dataStoreHelper.AddAsync(query);
Assert.IsTrue(parameters != null);
}
Assert.IsTrue(true);
//Add Parameters
watch.Stop();
_logger.LogInformation($"End Executing Insert_Large_RecordSet for Table:{tableName}");
}
[DataRow(nameof(Territories))]
[DataRow(nameof(Test))]
public async Task Get_Table_Test(string tableName)
{
Stopwatch watch = new();
watch.Start();
_logger.LogInformation($"Executing Get_Table_Test for Table:{tableName}");
Query query = new()
{
DataProviderType = QueryBuilder.Helpers.Enums.DataProviderType.SqlClient,
DataStoreType = QueryBuilder.Helpers.Enums.DataStoreType.Mssql,
Name = tableName,
QueryType = QueryBuilder.Helpers.Enums.QueryType.Select,
Statement = $"Select * from {tableName}"
};
_logger.LogInformation($"Starting Request for Query: {query.Name} of QueryType {query.QueryType.GetDescription()}");
_logger.LogInformation($"Statement{query.Statement}");
watch.Restart();
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
//Test for the table record
Table model = await dataStoreHelper.GetTableAsync(query);
Assert.IsNotNull(model);
Assert.IsTrue(model.Columns.Count > 0);
watch.Stop();
_logger.LogInformation($"End Executing Get_Table_Test for Table:{tableName}");
}
[DataTestMethod]
[DataRow(nameof(Products), "GetByPaging.xml")]
[DataRow(nameof(Test), "GetByPaging.xml", 100000)]
public async Task GetByPaging_Table_Test(string tableName, string fileName, int pageSize)
{
Stopwatch watch = new();
watch.Start();
_logger.LogInformation($"Executing GetByPaging_Table_Test for Table:{tableName}, no of Records to be read:{pageSize}");
Query query = ValidateAndReturnQuery(tableName, fileName);
var logger = (ILogger<SqlDataStoreHelper>)KernelMapper.ServiceProvider.GetService(typeof(ILogger<SqlDataStoreHelper>));
IDataStoreHelper dataStoreHelper = new SqlDataStoreHelper(logger, KernelMapper.NorthWindConnectionString);
Models.Table dataTable = await dataStoreHelper.GetTableByPagingAsync(query,100000);
Assert.IsNotNull(dataTable);
watch.Stop();
_logger.LogInformation($"Number of Records read {dataTable.RowValues.Count} with PageSize {pageSize}");
_logger.LogInformation($"End Executing GetByPaging_Table_Test for Table:{tableName}");
Assert.IsTrue(dataTable.RowValues.Count > 0);
}
}
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net7.0 is compatible. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
-
net7.0
- Microsoft.Data.SqlClient (>= 5.1.0)
- Microsoft.Extensions.Logging.Abstractions (>= 7.0.0)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Added Table method in DataStore and Select Paging method for large records