Require a C# program that will query a database with dynamic SQL statement and format the returned records into a Json string.
The database will be SQL Server.
The SQL query can be in any type such as Inner, outer, left right, union, join.
Inner Join and retreive the records and format the records into a Json string.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
Outer Join
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
The “using [login to view URL]” object will be used to format the Json string.
The Json string shall be formatted as follow:
Below is the SQL statement:
SELECT customer_company_team_id, customer_company_id, customer_company_team_fname, customer_company_team_lname, customer_company_team_title, customer_company_team_email, customer_company_team_linkedin_link, date_entered, customer_company_team_phone, customer_company_team_bio FROM [MillsVC_Trade].[MillsVC_Trade_Database].[customer_company_teams] WHERE customer_company_team_id=1055
Union
SELECT customer_company_id, customer_id, customer_company_fname, customer_company_team_lname, customer_company_team_title, customer_company_team_email, customer_company_team_linkedin_link, date_entered, customer_company_team_phone, customer_company_team_bio FROM [MillsVC_Trade].[MillsVC_Trade_Database].[customer_company] WHERE customer_company_team_id=1055
Below is the Json returned string:
{
"database_name":"Trader_database",
"table":"customer_company_teams, customer_company",
"date":"07/18/2016",
"time":"12:30:44 PM",
"sql":"SELECT customer_company_team_id, customer_company_id, customer_company_team_fname, customer_company_team_lname, customer_company_team_title, customer_company_team_email, customer_company_team_linkedin_link, date_entered, customer_company_team_phone, customer_company_team_bio FROM [MillsVC_Trade].[MillsVC_Trade_Database].[customer_company_teams] WHERE customer_company_team_id=1055
Union
SELECT customer_company_team_id, customer_company_id, customer_company_team_fname, customer_company_team_lname, customer_company_team_title, customer_company_team_email, customer_company_team_linkedin_link, date_entered, customer_company_team_phone, customer_company_team_bio FROM [MillsVC_Trade].[MillsVC_Trade_Database].[customer_company] WHERE customer_company_team_id=1055
",
"fields":"customer_company_team_id, customer_company_id, customer_company_team_fname, customer_company_team_lname, customer_company_team_title, customer_company_team_email, customer_company_team_linkedin_link, date_entered, customer_company_team_phone, customer_company_team_bio",
"data":[
{
"customer_company_team_id":"1055",
"customer_company_id":"35",
"customer_company_team_fname":"Mike",
"customer_company_team_lname":"Miller",
"customer_company_team_title":"President",
"customer_company_team_email":"email address",
"customer_company_team_linkedin_link":"",
"date_entered":"9/1/2015 1:02:07 PM",
"customer_company_team_phone":"aaa-aaa-aaaa",
"customer_company_team_bio":"To make investments on behalf of an entity (eg. a venture capital firm or trust), fill out the entity details in the form below. Once the entity is created, you can make investments on its behalf. Later, you can invite other team members to make investments on its behalf as well."
}
]
}
Testing
The application will be tested with various SQL script to make sure it is working correctly. Any issues with the SQL query will be fixed and tested again.
Please provide a demo on your side and show me so I know that you know how to do the work.
This should not be difficult to do.