public void ExecNonQuery <TModel, TOut>(string sqlCommandString, TModel objectmodel, string outPutName,
out TOut outPutValue) where TModel : class
{
try
{
var dataTable = ConvertToDataTable(objectmodel);
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
var returnVal = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
returnVal.Direction = ParameterDirection.Output;
var param = sqlCommand.Parameters.AddWithValue("@dt", dataTable);
param.SqlDbType = SqlDbType.Structured;
sqlCommand.ExecuteNonQuery();
outPutValue = (TOut)returnVal.Value;
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
private void CreateTable()
{
//不再使用 Date 类型，因为 Oracle 和 SQLServer 里面的数据的精度不一样。改为使用 LONG
//Oracle 中的 DateTime 类型为 Date
var timeType = DbSetting.IsOracleProvider(this.DbSetting) ?
OracleDbTypeHelper.ConvertToOracleTypeString(DbType.Int64) :
SqlDbTypeHelper.ConvertToSQLTypeString(DbType.Int64);
this.DBA.RawAccesser.ExecuteText(string.Format(@"CREATE TABLE {1}(ID INT NOT NULL,Value {0} NOT NULL,PRIMARY KEY (ID))", timeType, TableName));
this.DBA.ExecuteText("INSERT INTO " + TableName + " (ID,VALUE) VALUES (1, {0})", DefaultMinTime.Ticks);
}
private void LoadProperties(Type type)
{
foreach (var currentProperty in type.GetProperties())
{
if (currentProperty.GetCustomAttribute <DBIgnoreAttribute>() != null)
{
continue;
}
if (SqlDbTypeHelper.IsValidSqlDbType(currentProperty.PropertyType) == false)
{
throw new DBPropertyNonValidTypeException(type, currentProperty);
}
var primaryKeyAttribute = currentProperty.GetCustomAttribute <DBPrimaryKeyAttribute>();
if (primaryKeyAttribute != null)
{
var columnName = string.IsNullOrWhiteSpace(primaryKeyAttribute.ColumnName) == false
? primaryKeyAttribute.ColumnName
: currentProperty.Name;
this.PrimaryKeyPropertyMapping = new DBPropertyMapping
{
ColumnName = columnName,
Property = currentProperty
};
}
var propertyAttribute = currentProperty.GetCustomAttribute <DBPropertyAttribute>();
if (propertyAttribute != null)
{
var columnName = string.IsNullOrWhiteSpace(propertyAttribute.ColumnName) == false
? propertyAttribute.ColumnName
: currentProperty.Name;
this._properties.Add(new DBPropertyMapping
{
ColumnName = columnName,
Property = currentProperty
});
}
}
if (type.BaseType != null)
{
this.LoadProperties(type.BaseType);
}
}
public TModel[] ExecProcGetModels <TModel, TOut>(string sqlCommandString, string outPutName, out TOut outPutValue,
params SqlParameter[] inputParametrs) where TModel : class, new()
{
var result = default(TModel[]);
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
var outPutParametr = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
outPutParametr.Direction = ParameterDirection.Output;
sqlCommand.Parameters.AddRange(inputParametrs);
using (var dataTable = new DataTable())
{
dataTable.Load(sqlCommand.ExecuteReader());
result = dataTable.ConvertToArrayOfModels <TModel>();
outPutValue = (TOut)outPutParametr.Value;
}
return(result);
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
public void ExecNonQuery <TOut>(string sqlCommandString, string outPutName, out TOut outPutValue, params SqlParameter[] parametrs)
{
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString))
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
{
var outPutParametr = sqlCommand.Parameters.Add($"@{outPutName}", SqlDbTypeHelper.GetDbType(typeof(TOut)));
outPutParametr.Direction = ParameterDirection.Output;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddRange(parametrs);
sqlCommand.ExecuteNonQuery();
outPutValue = (TOut)outPutParametr.Value;
}
}
}
catch (Exception ex)
{
var serviceDataError = new DataErrorDto();
serviceDataError.ErrorMessage = ex.Message;
serviceDataError.ErrorDetails = ex.ToString();
log.Error($"Type Error: {serviceDataError.ErrorMessage}\n ErrorDetails {ex.ToString()}");
throw new FaultException <DataErrorDto>(serviceDataError, ex.ToString());
}
}
