/// <summary>
/// Execute Select query, Get Xb.Db.ResultTable
/// SELECTクエリを実行し、結果を Xb.Db.ResultTable で返す
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
/// <remarks></remarks>
public ResultTable Query(string sql, DbParameter[] parameters = null)
{
lock (this._locker)
{
try
{
this._locker.Locked = true;
var command = this.GetCommand(parameters);
command.CommandText = sql;
var reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
var result = new ResultTable(reader);
reader.Dispose();
command.Dispose();
return(result);
}
catch (Exception ex)
{
Xb.Util.Out(ex);
throw new Exception("Xb.Db.DbBase.Query: failure \r\n" + ex.Message + "\r\n" + sql);
}
finally
{
this._locker.Locked = false;
}
}
}
/// <summary>
/// コンストラクタ
/// </summary>
/// <param name="table"></param>
/// <param name="dataRecord"></param>
public ResultRow(ResultTable table, IDataRecord dataRecord = null)
{
this._table = table;
this._items = new object[this._table.ColumnCount];
dataRecord?.GetValues(this._items);
}
/// <summary>
/// Dispose object
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
lock (this)
{
if (disposing)
{
this.Db = null;
this._templateTable = null;
if (this.PkeyColumns != null)
{
for (var i = 0; i < this.PkeyColumns.Length; i++)
{
this.PkeyColumns[i] = null;
}
}
if (this.Columns != null)
{
for (var i = 0; i < this.Columns.Length; i++)
{
this.Columns[i].Dispose();
this.Columns[i] = null;
}
}
this.TableName = null;
this.Columns = null;
this.PkeyColumns = null;
}
}
}
/// <summary>
/// Update the difference.
/// 新旧データテーブルを比較し、差分データ分のレコードを更新する。
/// </summary>
/// <param name="dtAfter"></param>
/// <param name="dtBefore"></param>
/// <param name="excludeColumnsOnUpdate"></param>
/// <returns></returns>
/// <remarks></remarks>
public Xb.Db.Model.Error[] ReplaceUpdate(ResultTable dtAfter
, ResultTable dtBefore = null
, params string[] excludeColumnsOnUpdate)
{
return(this.ReplaceUpdate(dtAfter.Rows,
dtBefore.Rows,
excludeColumnsOnUpdate));
}
/// <summary>
/// Execute Select query, Get Xb.Db.ResultTable
/// SELECTクエリを実行し、結果を Xb.Db.ResultTable で返す
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
/// <remarks></remarks>
public async Task <ResultTable> QueryAsync(string sql, DbParameter[] parameters = null)
{
ResultTable result = null;
await Task.Run(() =>
{
result = this.Query(sql, parameters);
}).ConfigureAwait(false);
return(result);
}
/// <summary>
/// Get matched Xb.Db.ResultTable
/// 条件に合致した全行データを返す。
/// </summary>
/// <param name="whereString"></param>
/// <param name="orderString"></param>
/// <returns></returns>
/// <remarks></remarks>
public virtual async Task <ResultTable> FindAllAsync(string whereString = null
, string orderString = null)
{
ResultTable result = null;
await Task.Run(() =>
{
result = this.FindAll(whereString, orderString);
});
return(result);
}
/// <summary>
/// Get matched all rows
/// 条件に合致した全行データを返す。
/// </summary>
/// <param name="tableName"></param>
/// <param name="whereString"></param>
/// <param name="orderString"></param>
/// <returns></returns>
/// <remarks></remarks>
public virtual async Task <ResultTable> FindAllAsync(string tableName
, string whereString = null
, string orderString = null)
{
ResultTable result = null;
await Task.Run(() =>
{
result = this.FindAll(tableName, whereString, orderString);
}).ConfigureAwait(false);
return(result);
}
public void Dispose()
{
if (this._items != null)
{
for (var i = 0; i < this._items.Length; i++)
{
this._items[i] = null;
}
}
this._items = null;
this._table = null;
}
/// <summary>
/// Update the difference.
/// 新旧データテーブルを比較し、差分データ分のレコードを更新する。
/// </summary>
/// <param name="dtAfter"></param>
/// <param name="dtBefore"></param>
/// <param name="excludeColumnsOnUpdate"></param>
/// <returns></returns>
/// <remarks></remarks>
public async Task <Xb.Db.Model.Error[]> ReplaceUpdateAsync(ResultTable dtAfter
, ResultTable dtBefore = null
, params string[] excludeColumnsOnUpdate)
{
Xb.Db.Model.Error[] result = null;
await Task.Run(() =>
{
result = this.ReplaceUpdate(dtAfter, dtBefore, excludeColumnsOnUpdate);
});
return(result);
}
/// <summary>
/// Constructor
/// コンストラクタ
/// </summary>
/// <param name="tableInfo"></param>
/// <remarks></remarks>
public Model(Db.DbBase db
, Xb.Db.DbBase.Structure[] infoRows)
{
if (infoRows == null ||
infoRows.Length <= 0)
{
Xb.Util.Out("Xb.Db.Model.New: Table infomation not found.");
throw new ArgumentException("Xb.Db.Model.New: Table infomation not found.");
}
//get Xb.Db.DbBase ref.
this.Db = db;
this.TableName = infoRows[0].TABLE_NAME;
this.Columns = new Xb.Db.Model.Column[infoRows.Length];
var pkeyColumns = new List <Xb.Db.Model.Column>();
//loop column count
for (var i = 0; i < infoRows.Length; i++)
{
var typeString = infoRows[i].TYPE.ToUpper();
var maxInteger = 0;
var maxDecimal = 0;
var maxLength = 0;
var type = default(Column.ColumnType);
if (TypesOfNumber.Contains(typeString))
{
type = Column.ColumnType.Number;
maxInteger = (int)infoRows[i].NUM_PREC;
maxDecimal = (int)infoRows[i].NUM_SCALE;
maxInteger -= maxDecimal;
maxLength = maxInteger
+ maxDecimal
+ 1 //minus sign
+ (maxDecimal > 0 ? 1 : 0); //decimal point
}
else if (TypesOfString.Contains(typeString))
{
type = Column.ColumnType.String;
//TODO: MySQL-LongText型のような巨大なテキスト型のとき、文字数制限をしないようにする。
maxLength = (int)infoRows[i].CHAR_LENGTH;
maxInteger = -1;
maxDecimal = -1;
}
else if (TypesOfDateTime.Contains(typeString))
{
type = Column.ColumnType.DateTime;
maxLength = 21;
maxInteger = -1;
maxDecimal = -1;
}
else
{
type = Column.ColumnType.Others;
maxLength = -1;
maxInteger = -1;
maxDecimal = -1;
}
var isPkey = (infoRows[i].IS_PRIMARY_KEY == 1);
var nullable = (infoRows[i].IS_NULLABLE == 1);
this.Columns[i] = new Xb.Db.Model.Column(infoRows[i].COLUMN_NAME
, maxLength
, maxInteger
, maxDecimal
, type
, isPkey
, nullable
, this);
if (isPkey)
{
pkeyColumns.Add(this.Columns[i]);
}
}
this._templateTable = this.Db.Query($"SELECT * FROM {this.TableName} WHERE 1 = 0 ");
this.PkeyColumns = pkeyColumns.ToArray();
}