public async Task <SqlResponse> GetAsync(string uri, SqlRequest request)
{
var wreq = GetWebRequest(uri, request);
var httpResponse = await wreq.GetResponseAsync();
var rawdata = "";
var validresponse = false;
using (var stream = httpResponse.GetResponseStream())
{
if (stream != null)
{
using (var sr = new StreamReader(stream))
{
rawdata = sr.ReadToEnd();
validresponse = true;
}
}
}
if (!validresponse)
{
throw new CrateException("Invalid or empty response!");
}
if (((HttpWebResponse)httpResponse).StatusCode != HttpStatusCode.OK)
{
throw new CrateException("Invalid response status code: " + ((HttpWebResponse)httpResponse).StatusCode);
}
return(JsonConvert.DeserializeObject <SqlResponse>(rawdata));
}
/// <summary>
/// 获取数据表的主键
/// </summary>
/// <param name="tableName">表名</param>
private void GetKeyName(string tableName)
{
if (string.IsNullOrWhiteSpace(tableName))
{
return;
}
string sql = " SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName ";
List <SqlParameter> s_param = new List <SqlParameter>();
s_param.Add(new SqlParameter("@TableName", tableName));
try
{
SqlDataReader reader = SqlRequest.GetReader(sql, s_param.ToArray());
string field_name = string.Empty;
while (reader.Read())
{
KeyName = reader["COLUMN_NAME"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw new Exception("获取数据表的主键名称出错了", ex);
}
}
private HttpWebRequest GetWebRequest(string uri, SqlRequest request)
{
var wreq = (HttpWebRequest)WebRequest.Create(uri);
wreq.Method = "POST";
wreq.ContentType = "application/json";
wreq.Timeout = _timeout;
if (request == null)
{
throw new ArgumentNullException("request");
}
var dataload = JsonConvert.SerializeObject(request,
new JsonSerializerSettings()
{
NullValueHandling = NullValueHandling.Ignore, Formatting = Formatting.None
});
var buffer = Encoding.UTF8.GetBytes(dataload);
using (var sw = wreq.GetRequestStream())
{
sw.Write(buffer, 0, buffer.Length);
}
return(wreq);
}
/// <summary>
/// 根据主键获取一行记录
/// </summary>
/// <param name="table"></param>
/// <param name="key"></param>
/// <returns></returns>
public CModel GetModelByKey(string table, object key)
{
CModel m_table = new CModel(table);
string sql = SqlSplice.GetSelect(m_table, key);
CModel model = null;
try
{
SqlDataReader reader = SqlRequest.GetReader(sql, SqlSplice.SqlParams.ToArray());
while (reader.Read())
{
model = new CModel(table);
foreach (var field in model.Fields)
{
model[field] = reader[field];
}
}
return(model);
}
catch (Exception ex)
{
throw new Exception("根据主键查询一行记录出错了", ex);
}
}
/// <summary>
/// 查询一行记录
/// </summary>
/// <param name="filter">过滤器</param>
/// <returns>返回单条数据</returns>
public CModel GetModel(ModelFilter filter)
{
string sql = filter.ExecuteSql.ToString();
CModel model = null;
try
{
SqlDataReader reader = SqlRequest.GetReader(sql, filter.GetParams());
while (reader.Read())
{
model = new CModel(filter.TableName);
foreach (var field in filter.Fields)
{
model[field] = reader[field];
}
}
reader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
public Basic.Framework.Web.BasicResponse <string> QueryTableBySqlString(SqlRequest req)
{
var responseStr = HttpClientHelper.Post(Webapi + "/v1/Sql/QueryTableStringBySql?token=" + Token, JSONHelper.ToJSONString(req));
var res = JSONHelper.ParseJSONString <BasicResponse <string> >(responseStr);
return(res);
}
public virtual ISqlRequest BulkAddModels(IEnumerable <Database.UserDefinedTypes.Demo.UdtModel> models)
{
var request = new SqlRequest(_queryChain, "Demo", "BulkAddModels");
request.Parameters.Add(new StoredProcedureTableTypeParameter("@Models", "table type", ParameterDirection.Input, "Demo", "udtModel", models));
return(request);
}
public static async ValueTask <ObjectSchema> GetObjectSchema(this DbConnection @this, string name)
{
var objectName = name.Split('.').Get(^ 1) !.TrimStart('[').TrimEnd(']');
var schemaName = name.Contains("..") ? (object)DBNull.Value : name.Split('.').Get(^ 2) !.TrimStart('[').TrimEnd(']');
var request = new SqlRequest(ObjectSchema.SQL);
request.Parameters.Add(ObjectSchema.OBJECT_NAME, objectName);
request.Parameters.Add(ObjectSchema.SCHEMA_NAME, schemaName);
var(tableRowSet, columnRowSet, parameterRowSet, _) = await @this.RunAsync(request);
if (tableRowSet?.Rows.Any() is not true)
{
throw new ArgumentException($"{nameof(DbConnection)}.{nameof(GetObjectSchema)}: Database object was not found.", objectName);
}
var columns = Array <ColumnSchema> .Empty;
if (columnRowSet?.Rows.Any() is true)
{
columns = 0.Range(columnRowSet !.Rows.Length).To(i => new ColumnSchema
{
Hidden = (bool)columnRowSet[i, nameof(ColumnSchema.Hidden)] !,
Id = (int)columnRowSet[i, nameof(ColumnSchema.Id)] !,
Identity = (bool)columnRowSet[i, nameof(ColumnSchema.Identity)] !,
Length = (int)columnRowSet[i, nameof(ColumnSchema.Length)] !,
Name = (string)columnRowSet[i, nameof(ColumnSchema.Name)] !,
Nullable = (bool)columnRowSet[i, nameof(ColumnSchema.Nullable)] !,
PrimaryKey = (bool)columnRowSet[i, nameof(ColumnSchema.PrimaryKey)] !,
ReadOnly = (bool)columnRowSet[i, nameof(ColumnSchema.ReadOnly)] !,
Type = (SqlDbType)columnRowSet[i, nameof(ColumnSchema.Type)] !
}).ToArray();
public virtual ISqlRequest GetModelsByBrandIdAndYear(int?brandId, int?year)
{
var request = new SqlRequest(_queryChain, "Demo", "GetModelsByBrandIdAndYear");
request.Parameters.Add(new StoredProcedureSimpleParameter("@BrandId", "int", ParameterDirection.Input, brandId));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Year", "int", ParameterDirection.Input, year));
return(request);
}
public BasicResponse <int> GetReportTotalRecord(Sys.Safety.Request.ReportGetReportTotalRecordRequest reportRequest)
{
// 20171106
BasicResponse <int> response = new BasicResponse <int>();
int count = 0;
var _strFreQryCondition = reportRequest._strFreQryCondition;
var ListID = reportRequest.ListID;
var _listdate = reportRequest._listdate;
var ListDataID = reportRequest.ListDataID;
int filterType = reportRequest.FilterType;
int removeRepetType = reportRequest.RemoveRepetType;
string arrangeTime = reportRequest.ArrangeTime;
SetDayTableSql(_strFreQryCondition, ListID, _listdate, filterType, removeRepetType);
if (_strFreQryCondition.ToLower().IndexOf("datsearch") > 0)
{
string strdate = _strFreQryCondition.Substring(_strFreQryCondition.ToLower().IndexOf("datsearch") + 10, 55);
_strFreQryCondition = _strFreQryCondition.Replace(strdate, " <> '1900-1-1'");
}
//string strsql = "select strListSQL from BFT_ListDataEx where ListID=" + ListID + " and ListDataID=" + ListDataID + " LIMIT 0,1 ";
//DataTable dtSourceSql = _Repository.QueryTable(strsql);
var item = _ListdataexRepository.Datas.FirstOrDefault(o => o.ListID == ListID && o.ListDataID == ListDataID);
string strsql = string.Empty;
if (item != null)
{
string strSortWhere = "";
IList <ListdatalayountInfo> listDataLayoutDTO = this.GetListLayout(filterType, arrangeTime, ListDataID);
strSortWhere = listDataLayoutDTO != null && listDataLayoutDTO.Count > 0
? "and " + listDataLayoutDTO[0].StrConTextCondition
: "";
strsql = item.StrListSQL;// Convert.ToString(dtSourceSql.Rows[0]["strListSQL"]);
strsql = strsql.Replace("where 1=1", "where 1=1 " + _strFreQryCondition + strSortWhere);
}
if (this.GetDBType() == "mysql")
{
strsql = strsql.Replace("ISNULL", "IFNULL").Replace("isnull", "ifnull");
}
this.GetDistinctSql(ref strsql, ListID);
//count = _Repository.GetTotalRecord(strsql);
SqlRequest countsqlrequest = new SqlRequest
{
Sql = strsql
};
DataTable temptable = _SqlService.QueryTableBySql(countsqlrequest).Data;
if (temptable != null && temptable.Rows.Count > 0)
{
count = temptable.Rows.Count;
}
response.Data = count;
return(response);
}
public BasicResponse <int> ExecuteNonQueryBySql(SqlRequest req)
{
var ret = _listexRepository.ExecuteNonQueryBySql(req.Sql);
return(new BasicResponse <int>()
{
Data = ret
});
}
public Basic.Framework.Web.BasicResponse <string> QueryTableStringBySql(SqlRequest req)
{
var res = _sqlService.QueryTableBySql(req);
return(new BasicResponse <string>()
{
Data = ObjectConverter.ToBase64String(res.Data)
});
}
public virtual ISqlRequest AddModel(int?brandId, string name, int?horsePower, int?year)
{
var request = new SqlRequest(_queryChain, "Demo", "AddModel");
request.Parameters.Add(new StoredProcedureSimpleParameter("@BrandId", "int", ParameterDirection.Input, brandId));
request.Parameters.Add(new StoredProcedureSimpleParameter("@HorsePower", "int", ParameterDirection.Input, horsePower));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Name", "nvarchar", ParameterDirection.Input, name));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Year", "int", ParameterDirection.Input, year));
return(request);
}
public BasicResponse <DataTable> QueryTableBySql(SqlRequest req)
{
var dt = _listexRepository.QueryTableBySql(req.Sql);
var ret = new BasicResponse <DataTable>()
{
Data = dt
};
return(ret);
}
/// <summary>
/// Executes the command
/// </summary>
/// <param name="request">The command request</param>
/// <returns>The result of the command</returns>
public override SqlNonQueryResult Execute(SqlRequest request)
{
using SqlConnection connection = _connectionProvider.GetConnection(request.Options);
using SqlCommand command = GetSqlCommand(connection, request);
int numberOfRowsAffected = command.ExecuteNonQuery();
return(new SqlNonQueryResult(numberOfRowsAffected));
}
public async Task <Request> GetById(string id)
{
if (int.TryParse(id, out int reqId))
{
SqlRequest sqlRequest = await dbcontext.SqlRequests.FirstOrDefaultAsync(x => x.Id == reqId);
return(mapper.Map <Request>(sqlRequest));
}
return(null);
}
public IList <Student> GetAll()
{
SqlRequest request = new SqlRequest()
{
CommandName = SqlName.spAllStudent,
CommandParams = null
};
return(context.ExecuteSqlCommand <Student>(request).ToList());
}
/// <summary>
/// Executes the command asynchronously
/// </summary>
/// <param name="request">The command request</param>
/// <param name="cancellationToken">The cancellation token in scope for the operation</param>
/// <returns>The result of the command</returns>
public override async Task <SqlNonQueryResult> ExecuteAsync(SqlRequest request, CancellationToken cancellationToken)
{
await using SqlConnection connection = await _connectionProvider.GetConnectionAsync(cancellationToken);
await using SqlCommand command = GetSqlCommand(connection, request);
int numberOfRowsAffected = await command.ExecuteNonQueryAsync(cancellationToken);
return(new SqlNonQueryResult(numberOfRowsAffected));
}
/// <summary>
/// Executes the command
/// </summary>
/// <param name="request">The command request</param>
/// <returns>The result of the command</returns>
public override SqlQueryResult Execute(SqlRequest request)
{
using SqlConnection connection = _connectionProvider.GetConnection(request.Options);
using SqlCommand command = GetSqlCommand(connection, request);
DataTable dataTable = _commandExecutor.Execute(command);
return(new SqlQueryResult(dataTable));
}
public Basic.Framework.Web.BasicResponse <DataTable> QueryTableBySql(SqlRequest req)
{
var res = QueryTableBySqlString(req);
var resZh = ObjectConverter.FromBase64String <DataTable>(res.Data);
return(new BasicResponse <DataTable>
{
Data = resZh
});
}
public void TestThatStatementParameterPlaceholderGetParsedCorrectly()
{
var stmt = @"select * from schema.test where name = @name and form_id = @form_id";
var expectedResult = @"select * from schema.test where name = $1 and form_id = $2";
var parameters = new object[] { "theName", 1 };
var sqlRequest = new SqlRequest(stmt, parameters);
Assert.AreEqual(expectedResult, sqlRequest.Stmt);
}
public void Initialize()
{
foreach (var map in SimpleORM.Current.Mappings.Values)
{
var result = connection.ExecuteQuery <int?>("SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + map.Name + "'").FirstOrDefault();
if (result.HasValue && result == 0)
{
StringBuilder createTableCommand = new StringBuilder();
createTableCommand.Append("CREATE TABLE [" + map.Name + "](");
foreach (var column in map.Data)
{
var fieldReferences = false;
foreach (var reference in map.References)
{
if (reference.Value.Value == column.Key.Name)
{
fieldReferences = true;
break;
}
}
var valueToAppend = column.Value;
if (fieldReferences)
{
valueToAppend = valueToAppend.Replace("MAX", "500");
}
if (map.SecondaryKeyNames.Contains(column.Key.Name))
{
valueToAppend = valueToAppend.Replace("MAX", "500");
valueToAppend += " UNIQUE NOT NULL";
}
if (map.PrimaryKeyName == column.Key.Name && !map.SecondaryKeyNames.Contains(column.Key.Name))
{
valueToAppend += " NOT NULL";
}
valueToAppend += ",";
createTableCommand.Append(valueToAppend);
}
createTableCommand.Append(")");
var request = new SqlRequest(createTableCommand.ToString());
Execute(request);
}
}
BuildReferences();
}
public async Task Invoke(HttpContext httpContext)
{
using var reader = new StreamReader(httpContext.Request.Body);
var request = new SqlRequest
{
SQL = reader.ReadToEnd()
};
httpContext.Request.Query.Do(query => request.Parameters[query.Key] = query.Value.First());
await this.HandleRequest<SqlRequest, RowSet[]>(request, httpContext);
}
/// <summary>
/// Executes the command
/// </summary>
/// <param name="request">The command request</param>
/// <returns>The result of the command</returns>
public override TResult Execute(SqlRequest request)
{
using SqlConnection connection = _connectionProvider.GetConnection(request.Options);
using SqlCommand command = GetSqlCommand(connection, request);
object result = command.ExecuteScalar();
return(result == null || result == DBNull.Value
? default
: (TResult)result);
}
/// <summary>
/// Executes the command asynchronously
/// </summary>
/// <param name="request">The command request</param>
/// <param name="cancellationToken">The cancellation token in scope for the operation</param>
/// <returns>The result of the command</returns>
public override async Task <TResult> ExecuteAsync(SqlRequest request, CancellationToken cancellationToken)
{
await using SqlConnection connection = await _connectionProvider.GetConnectionAsync(cancellationToken);
await using SqlCommand command = GetSqlCommand(connection, request);
object result = await command.ExecuteScalarAsync(cancellationToken);
return(result == null || result == DBNull.Value
? default
: (TResult)result);
}
public Student Find(int id)
{
SqlRequest request = new SqlRequest
{
CommandName = SqlName.spFindStudent,
CommandParams = new
{
StudentId = id
}
};
return(context.ExecuteSqlCommand <Student>(request).SingleOrDefault());
}
/// <summary>
/// 删除一行或多行记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter"></param>
public void DeleteModel(ModelFilter filter)
{
string sql = SqlSplice.GetDelete(filter);
try
{
SqlRequest.Update(sql, filter.Params.ToArray());
}
catch (Exception ex)
{
throw new Exception("删除记录出错了", ex);
}
}
/// <summary>
/// 查询表的数据总数量
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public int SelectCount(string tableName)
{
string sql = $"SELECT COUNT(1) as DATA_COUNT FROM {tableName}";
SqlDataReader reader = SqlRequest.GetReader(sql);
int data_count = 0;
if (reader.Read())
{
data_count = Convert.ToInt32(reader["DATA_COUNT"]);
}
return(data_count);
}
/// <summary>
/// 查询多行记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter">过滤器</param>
/// <returns></returns>
public List <T> GetModels <T>(ModelFilter filter)
{
Type type = typeof(T);
string sql = filter.ExecuteSql.ToString();
List <CModel> models = new List <CModel>();
try
{
SqlDataReader reader = SqlRequest.GetReader(sql, filter.Params.ToArray());
while (reader.Read())
{
CModel model = new CModel(type);
foreach (var field in model.Fields)
{
model[field] = reader[field];
}
models.Add(model);
}
reader.Close();
List <T> m_objs = new List <T>();
if (models.Count <= 0)
{
return(m_objs);
}
foreach (var model in models)
{
T m_obj = JsonConverter.CModelToModel <T>(model);
m_objs.Add(m_obj);
}
return(m_objs);
}
catch (Exception ex)
{
throw new Exception("查询多行记录出错了", ex);
}
}
protected SqlCommand GetSqlCommand(SqlConnection connection, SqlRequest request)
{
using var command = new SqlCommand(request.Sql, connection);
if (request.Parameters != null)
{
command.Parameters.AddRange(ToSqlParameters(request.Parameters));
}
if (request.Timeout.HasValue)
{
command.CommandTimeout = request.Timeout.Value.Seconds;
}
return(command);
}
public virtual ISqlRequest AddBrand(string name, string slogan, string country, DateTime? founded)
{
var request = new SqlRequest(_queryChain, "Demo", "AddBrand");
request.Parameters.Add(new StoredProcedureSimpleParameter("@Country", "nvarchar", ParameterDirection.Input, country));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Founded", "datetime2", ParameterDirection.Input, founded));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Name", "nvarchar", ParameterDirection.Input, name));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Slogan", "nvarchar", ParameterDirection.Input, slogan));
return request;
}
public virtual ISqlRequest ScalarNoParameters()
{
var request = new SqlRequest(_queryChain, "IntegrationTest", "Scalar_NoParameters");
return request;
}
public virtual ISqlRequest NonQueryNoParameters()
{
var request = new SqlRequest(_queryChain, "IntegrationTest", "NonQuery_NoParameters");
return request;
}
public virtual ISqlRequest MultipleResultSetsNoParameters()
{
var request = new SqlRequest(_queryChain, "IntegrationTest", "MultipleResultSets_NoParameters");
return request;
}
public virtual ISqlRequest GetModelsByMinimumYearAndMinimumHorsePower(int? minimumYear, int? minimumHorsePower)
{
var request = new SqlRequest(_queryChain, "Demo", "GetModelsByMinimumYearAndMinimumHorsePower");
request.Parameters.Add(new StoredProcedureSimpleParameter("@MinimumHorsePower", "int", ParameterDirection.Input, minimumHorsePower));
request.Parameters.Add(new StoredProcedureSimpleParameter("@MinimumYear", "int", ParameterDirection.Input, minimumYear));
return request;
}
public virtual ISqlRequest GetAllBrandsAndModels()
{
var request = new SqlRequest(_queryChain, "Demo", "GetAllBrandsAndModels");
return request;
}
public virtual ISqlRequest BulkAddModels(IEnumerable<Database.UserDefinedTypes.Demo.UdtModel> models)
{
var request = new SqlRequest(_queryChain, "Demo", "BulkAddModels");
request.Parameters.Add(new StoredProcedureTableTypeParameter("@Models", "table type", ParameterDirection.Input, "Demo", "udtModel", models));
return request;
}
public virtual ISqlRequest AddModel(int? brandId, string name, int? horsePower, int? year)
{
var request = new SqlRequest(_queryChain, "Demo", "AddModel");
request.Parameters.Add(new StoredProcedureSimpleParameter("@BrandId", "int", ParameterDirection.Input, brandId));
request.Parameters.Add(new StoredProcedureSimpleParameter("@HorsePower", "int", ParameterDirection.Input, horsePower));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Name", "nvarchar", ParameterDirection.Input, name));
request.Parameters.Add(new StoredProcedureSimpleParameter("@Year", "int", ParameterDirection.Input, year));
return request;
}
