/// <summary>
/// Write value to DB
/// 値をDBに書き込む。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
/// <remarks></remarks>
public Xb.Db.Model.Error[] Write(ResultRow row
, params string[] excludeColumnsOnUpdate)
{
var errors = this.Validate(row);
if (errors.Length > 0)
{
return(errors);
}
if (excludeColumnsOnUpdate == null ||
excludeColumnsOnUpdate.Length == 0)
{
excludeColumnsOnUpdate = new string[] { }
}
;
if (this.PkeyColumns.Length <= 0)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-", "-", Error.ErrorType.NotDefinedError,
"Write method needs Primary-Key")
});
}
var wheres = new List <string>();
foreach (Xb.Db.Model.Column col in this.PkeyColumns)
{
var value = row.Table.ColumnNames.Contains(col.Name)
? this.NullFormat(row[col.Name])
: null;
wheres.Add(col.GetSqlFormula(value));
}
var sql = $"SELECT 1 FROM {this.TableName} WHERE {string.Join(" AND ", wheres)} ";
var dt = this.Db.Query(sql);
if (dt == null ||
dt.RowCount <= 0)
{
errors = this.Insert(row);
if (errors.Length != 0)
{
return(errors);
}
}
else
{
errors = this.Update(row, null, excludeColumnsOnUpdate);
if (errors.Length != 0)
{
return(errors);
}
}
return(new Db.Model.Error[] { });
}
/// <summary>
/// DBレコードからロールを生成する。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private Role GetRole(Xb.Db.ResultRow row)
{
var role = new Role();
role.Id = Guid.Parse(row.Get <string>("Id"));
role.Name = row.Get <string>("Name");
role.NormalizedRoleName = row.Get <string>("NormalizedRoleName");
return(role);
}
/// <summary>
/// Do Delete
/// 該当キーの行を削除する。
/// </summary>
/// <param name="row"></param>
/// <param name="keyColumns"></param>
/// <returns></returns>
/// <remarks></remarks>
public Xb.Db.Model.Error[] Delete(ResultRow row
, params string[] keyColumns)
{
if (keyColumns == null ||
keyColumns.Length == 0)
{
keyColumns = this.PkeyColumns.Select(col => col.Name).ToArray();
}
var tmpKeys = new List <string>();
foreach (Xb.Db.Model.Column col in this.Columns)
{
if (keyColumns.Contains(col.Name))
{
tmpKeys.Add(col.Name);
}
}
keyColumns = tmpKeys.ToArray();
if (keyColumns.Length == 0)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, "key column not found")
});
}
var colValues = new Dictionary <string, string>();
foreach (string col in keyColumns)
{
colValues.Add(col,
row.Table.ColumnNames.Contains(col)
? this.NullFormat(row[col])
: null);
}
var wheres = new List <string>();
foreach (string col in keyColumns)
{
wheres.Add(this.GetColumn(col).GetSqlFormula(row[col]));
}
var sql = $"DELETE FROM {this.TableName} WHERE {string.Join(" AND ", wheres)}";
this.Db.Execute(sql);
return(new Db.Model.Error[] { });
}
/// <summary>
/// Get first matched row
/// 条件に合致した最初の行を返す
/// </summary>
/// <param name="tableName"></param>
/// <param name="whereString"></param>
/// <returns></returns>
/// <remarks></remarks>
public virtual async Task <ResultRow> FindAsync(string tableName, string whereString)
{
ResultRow result = null;
await Task.Run(() =>
{
result = this.Find(tableName, whereString);
}).ConfigureAwait(false);
return(result);
}
/// <summary>
/// Do Insert
/// INSERTを実行する。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public async Task <Xb.Db.Model.Error[]> InsertAsync(ResultRow row)
{
Xb.Db.Model.Error[] result = null;
await Task.Run(() =>
{
result = this.Insert(row);
});
return(result);
}
/// <summary>
/// Get first matched Xb.Db.ResultRow
/// 渡した主キー値配列に合致したDataRowを返す。
/// </summary>
/// <param name="primaryKeyValues"></param>
/// <returns></returns>
/// <remarks></remarks>
public async Task <ResultRow> FindAsync(params object[] primaryKeyValues)
{
ResultRow result = null;
await Task.Run(() =>
{
result = this.Find(primaryKeyValues);
});
return(result);
}
/// <summary>
/// Do Delete
/// 該当キーの行を削除する。
/// </summary>
/// <param name="row"></param>
/// <param name="keyColumns"></param>
/// <returns></returns>
/// <remarks></remarks>
public async Task <Xb.Db.Model.Error[]> DeleteAsync(ResultRow row
, params string[] keyColumns)
{
Xb.Db.Model.Error[] result = null;
await Task.Run(() =>
{
result = this.Delete(row, keyColumns);
});
return(result);
}
/// <summary>
/// Write value to DB
/// 値をDBに書き込む。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
/// <remarks></remarks>
public async Task <Xb.Db.Model.Error[]> WriteAsync(ResultRow row
, params string[] excludeColumnsOnUpdate)
{
Xb.Db.Model.Error[] result = null;
await Task.Run(() =>
{
result = this.Write(row, excludeColumnsOnUpdate);
});
return(result);
}
/// <summary>
/// DBレコードからユーザーを生成する。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private User GetUser(Xb.Db.ResultRow row)
{
var user = new User();
user.Id = Guid.Parse(row.Get <string>("Id"));
user.LoginName = row.Get <string>("LoginName");
user.NormalizedLoginName = row.Get <string>("NormalizedLoginName");
user.ScreenName = row.Get <string>("ScreenName");
user.Email = row.Get <string>("Email");
user.PasswordHash = row.Get <string>("PasswordHash");
return(user);
}
/// <summary>
/// Do Update
/// UPDATEを実行する。
/// </summary>
/// <param name="row"></param>
/// <param name="keyColumns"></param>
/// <param name="excludeColumns"></param>
/// <returns></returns>
public async Task <Db.Model.Error[]> UpdateAsync(ResultRow row
, string[] keyColumns = null
, string[] excludeColumns = null)
{
Xb.Db.Model.Error[] result = null;
await Task.Run(() =>
{
result = this.Update(row, keyColumns, excludeColumns);
});
return(result);
}
/// <summary>
/// Do Insert
/// INSERTを実行する。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public Xb.Db.Model.Error[] Insert(ResultRow row)
{
var colValues = new Dictionary <string, string>();
foreach (Xb.Db.Model.Column col in this.Columns)
{
colValues.Add(col.Name,
row.Table.ColumnNames.Contains(col.Name) //Any(rc => rc.ColumnName == col.Name)
? this.NullFormat(row[col.Name])
: null);
}
var targetColumns = this.Columns
.Where(col => row.Table.ColumnNames.Contains(col.Name))
.Where(col => !(col.IsPrimaryKey && col.IsNullable && colValues[col.Name] == ""))
.ToList();
var sql = $"INSERT INTO {this.TableName} ( {string.Join(", ", targetColumns.Select(col => col.Name))} ) "
+ $" VALUES ( {string.Join(", ", targetColumns.Select(col => col.GetSqlValue(colValues[col.Name])))} )";
try
{
if (this.Db.Execute(sql) != 1)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, $"Insert failure：{sql}")
});
}
}
catch (Exception)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, $"Insert failure：{sql}")
});
}
return(new Db.Model.Error[] { });
}
/// <summary>
/// Validate values
/// 値を検証する。
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
/// <remarks></remarks>
public Xb.Db.Model.Error[] Validate(ResultRow row)
{
var errors = new List <Xb.Db.Model.Error>();
foreach (Xb.Db.Model.Column col in this.Columns)
{
if (!row.Table.ColumnNames.Contains(col.Name))
{
continue;
}
var errorType = col.Validate(row[col.Name]);
if (errorType != Xb.Db.Model.Error.ErrorType.NoError)
{
errors.Add(new Xb.Db.Model.Error(col.Name, this.NullFormat(row[col.Name]), errorType));
}
}
return(errors.ToArray());
}
/// <summary>
/// Do Update
/// UPDATEを実行する。
/// </summary>
/// <param name="row"></param>
/// <param name="keyColumns"></param>
/// <param name="excludeColumns"></param>
/// <returns></returns>
public Db.Model.Error[] Update(ResultRow row
, string[] keyColumns = null
, string[] excludeColumns = null)
{
if (keyColumns == null)
{
keyColumns = this.PkeyColumns.Select(col => col.Name).ToArray();
}
if (excludeColumns == null)
{
excludeColumns = new string[] {}
}
;
var tmpKeys = new List <string>();
var tmpExcludes = new List <string>();
var colValues = new Dictionary <string, string>();
foreach (Xb.Db.Model.Column col in this.Columns)
{
colValues.Add(col.Name,
row.Table.ColumnNames.Contains(col.Name)
? this.NullFormat(row[col.Name])
: null);
if (keyColumns.Contains(col.Name))
{
tmpKeys.Add(col.Name);
}
if (excludeColumns.Contains(col.Name))
{
tmpExcludes.Add(col.Name);
}
}
keyColumns = tmpKeys.ToArray();
excludeColumns = tmpExcludes.ToArray();
if (keyColumns.Length <= 0)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, "Key column not found")
});
}
var targetColumns
= this.Columns.Where(col => row.Table.ColumnNames.Contains(col.Name) &&
!excludeColumns.Contains(col.Name)).ToList();
var updates = new List <string>();
var wheres = new List <string>();
foreach (Xb.Db.Model.Column col in targetColumns)
{
if (keyColumns.Contains(col.Name))
{
wheres.Add(col.GetSqlFormula(colValues[col.Name]));
}
else
{
updates.Add(col.GetSqlFormula(colValues[col.Name], false));
}
}
if (updates.Count <= 0)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, "update target value not found")
});
}
var sql = $" UPDATE {this.TableName} SET {string.Join(" , ", updates)} "
+ $" WHERE {string.Join(" AND ", wheres)}";
try
{
this.Db.Execute(sql);
}
catch (Exception)
{
return(new Xb.Db.Model.Error[]
{
new Xb.Db.Model.Error("-"
, "-"
, Error.ErrorType.NotDefinedError
, $"Update failure：{sql}")
});
}
return(new Db.Model.Error[] { });
}