//delphi 調用代碼 隻示範了簡單查詢的代碼,分頁查詢代碼類似
procedure Tform1.button1Click(Sender: TObject);
var
jo:ISuperObject;
json,_message:string;
_result:string;
_msg,_res:string;
reqData, resData:Tstringstream;
vHttp: Tnethttpclient;
ja:iSuperArray;
begin
jo:=SO();
jo.S['keystore']:='xxx';
jo.S['method']:='simplequery';
jo.S['sql']:='select * from employee where id=2';
try
vHttp := Tnethttpclient.Create(nil);
reqData := TStringStream.Create(jo.AsJSON);
resData := TStringStream.Create('',TEncoding.GetEncoding(65001));
try
with vHttp do
begin
ConnectionTimeout := 20000;
ResponseTimeout := 100000;
AcceptCharSet := 'utf-8';
AcceptEncoding := '65001';
AcceptLanguage := 'zh-CN';
ContentType := 'application/json';
UserAgent := 'Embarcadero URI Client/1.0';
end;
vHttp.Post('http://localhost:59223/api/remote', reqData, resData);
json :=resData.DataString.Trim;
jo:=SO(json);
ja:=jo.A['data'];
XtoCds(ja,clientdataset1);//轉換為clientdataset
except
on e:exception do
begin
jo:=SO();
jo.S['result']:='-1';
jo.S['message']:=e.Message.Trim;
json:=jo.AsJSON();
end;
end;
finally
reqData.Free;
resData.Free;
FreeAndNil(vHttp);
end;
end;
//asp.net core 伺服器webapi控制器,asp.net可以配置在 win linux mac上面
//在windows上面可以架設在IIS上 其他平台請自行查找
//可以nginx來提高并發量,可以滿足大型或小型的服務需求
public class RemoteController : Controller
{
private readonly Chloe.IDbContext _dbContext;
private IHostingEnvironment _environment;
private IConfiguration _configuration;
public RemoteController(Chloe.IDbContext dbContext, IHostingEnvironment environment, IConfiguration Configuration)
{
_dbContext = dbContext;
_environment = environment;
_configuration = Configuration;
}
// POST api/<controller>
[HttpPost]
public string Post([FromBody] JObject _value)
{
StringBuilder builder = new StringBuilder();
JObject jo = _value;
string keystore = jo.Value<string>("keystore");
string method = jo.Value<string>("method").Trim();
string _result = "";
if (keystore == null)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"驗證失敗\"");
builder.Append("}");
return builder.ToString();
}
if (keystore != _configuration.GetSection("KeysString")["webapiKeyStore"]) //傳入的keystore和configuration中的是否一緻
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"驗證失敗\"");
builder.Append("}");
return builder.ToString();
}
if (jo == null)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"傳入參數錯誤\"");
builder.Append("}");
return builder.ToString();
}
switch (method)
{
case "0": //判斷網絡是否暢通
builder.Append("{");
builder.Append("\"result\":\"9\",");
builder.Append("\"message\":\"網絡暢通\"");
builder.Append("}");
_result = builder.ToString();
break;
case "simplequery"://簡單sql查詢
_result = _simplequery(jo);
break;
case "execsql"://執行sql語句
_result = _execsql(jo);
break;
case "pagequery"://分頁查詢
_result = _pagequery(jo);
break;
case "openproc"://存儲過程查詢
_result = _openproc(jo);
break;
case "execproc"://存儲過程執行
_result = _execproc(jo);
break;
default:
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"操作錯誤\"");
builder.Append("}");
_result = builder.ToString();
break;
}
return _result;
}
private string _simplequery(JObject _jo)//簡單sql查詢
{
StringBuilder builder = new StringBuilder();
try
{
string _sql = _jo.Value<string>("sql"); //擷取sql語句
//var result = _dbContext.SqlQuery<dynamic>(_sql).ToList().Skip((1 - 1) * 10).Take(10); //分頁查詢
var result = _dbContext.SqlQuery<dynamic>(_sql).ToList();
if (result == null)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"執行sql語句錯誤\"");
builder.Append("}");
return builder.ToString();
}
int _count = result.Count();
string _json = JsonConvert.SerializeObject(result);
builder.Append("{");
builder.Append("\"result\":\"0\",");
builder.Append("\"message\":\"\",");
builder.Append("\"count\":" + _count + ",");
builder.Append("\"data\":");
builder.Append(_json);
builder.Append("}");
return builder.ToString();
}
catch (Exception e)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append(string.Format("\"message\":\"{0}\"", e.Message));
builder.Append("}");
return builder.ToString();
}
}
private string _execsql(JObject _jo)//執行sql語句
{
StringBuilder builder = new StringBuilder();
try
{
string _sql = _jo.Value<string>("sql"); //擷取sql語句
int _count = _dbContext.Session.ExecuteNonQuery(_sql);
if (_count == 0)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append("\"message\":\"執行sql語句錯誤\"");
builder.Append("}");
return builder.ToString();
}
else
{
builder.Append("{");
builder.Append("\"result\":\"0\",");
builder.Append("\"message\":\"執行成功\"");
builder.Append("}");
return builder.ToString();
}
}
catch (Exception e)
{
builder.Append("{");
builder.Append("\"result\":\"1\",");
builder.Append(string.Format("\"message\":\"{0}\"",e.Message));
builder.Append("}");
return builder.ToString();
}
}