/// <summary>
/// Once SubmitChanges is called without exception thrown, the transaction will be reset and all Sql Statement will cleared.
/// </summary>
/// <returns></returns>
public Dictionary <Guid, object> SubmitChanges(List <SqlReference> sqlList)
{
if (!(sqlList is List <SqlReference>))
{
throw new ArgumentException("sqlList must be List<DBO.DataService.SqlReference>.");
}
var _sqlList = sqlList as List <SqlReference>;
DataServiceClient client = new DataServiceClient(this.ConnectionString);
try
{
if (this.CommandTimeout.HasValue)
{
client.CommandTimeout = this.CommandTimeout;
}
var result = client.SubmitChanges(_sqlList);
foreach (var sqlReference in _sqlList)
{
if (result.ContainsKey(sqlReference.Guid))
{
sqlReference.ReturnValue = result[sqlReference.Guid];
}
}
this.Transaction.ResetTransaction();
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//if (client.State != CommunicationState.Faulted)
// client.Close();
}
}
/// <summary>
/// Call this method for any custom SQL statement. Ignore any exception from database. Not recomment to use this.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlBuilder"></param>
/// <returns></returns>
public List <T> ExecuteWithoutException <T>(SqlBuilder sqlBuilder)
{
DataServiceClient client = new DataServiceClient(this.ConnectionString);
try
{
if (this.CommandTimeout.HasValue)
{
client.CommandTimeout = this.CommandTimeout;
}
var result = new List <T>();
var sql = sqlBuilder.Sql;
var dataSource = client.ExecuteWithoutException(sql);
if (typeof(DataSource).IsAssignableFrom(typeof(T)))
{
(result as IList).Add(dataSource);
return(result);
}
var properties = typeof(T).GetProperties();
var propertyDict = new Dictionary <string, PropertyInfo>();
foreach (var propertyInfo in properties)
{
propertyDict.Add(propertyInfo.Name.ToUpper(), propertyInfo);
}
foreach (var row in dataSource.Rows)
{
var newItem = (T)Activator.CreateInstance(typeof(T));
for (var i = 0; i < dataSource.ColumnInfos.Count; i++)
{
var columnName = dataSource.ColumnInfos[i].Name;
if (propertyDict.ContainsKey(columnName.ToUpper()))
{
propertyDict[columnName.ToUpper()].SetValue(newItem, row.Columns[i], null);
}
}
if (newItem is IModified)
{
(newItem as IModified).IsModified = false;
}
if (newItem is IPropertyModified)
{
(newItem as IPropertyModified).MarkAsDefault();
}
result.Add(newItem);
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//if (client.State != CommunicationState.Faulted)
// client.Close();
}
}
/// <summary>
/// Call this method for any custom SQL statement. Not recomment to use this.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlBuilder"></param>
/// <returns></returns>
public List <T> Execute <T>(SqlBuilder sqlBuilder)
{
DataServiceClient client = new DataServiceClient(this.ConnectionString);
try
{
if (this.CommandTimeout.HasValue)
{
client.CommandTimeout = this.CommandTimeout;
}
var result = new List <T>();
var sql = sqlBuilder.Sql;
var dataSource = client.Execute(sql);
if (typeof(DataSource).IsAssignableFrom(typeof(T)))
{
(result as IList).Add(dataSource);
return(result);
}
var properties = typeof(T).GetProperties();
var propertyDict = new Dictionary <string, PropertyInfo>();
foreach (var propertyInfo in properties)
{
propertyDict.Add(propertyInfo.Name.ToUpper(), propertyInfo);
}
// in case the Column name in tables or stored procedures contains char which invalid for build Property in class
//for (var i = 0; i < dataSource.ColumnNames.Length; i++)
// dataSource.ColumnNames[i] = ConvertToValidPropertyName(dataSource.ColumnNames[i]);
var columnNames = new List <string>();
for (var i = 0; i < dataSource.ColumnInfos.Count; i++)
{
columnNames.Add(ValidateColumnName(dataSource.ColumnInfos[i].Name).ToUpper());
}
foreach (var row in dataSource.Rows)
{
var newItem = (T)typeof(T).GetDefaultValue();
for (var i = 0; i < dataSource.ColumnInfos.Count; i++)
{
var columnName = columnNames[i];
if (propertyDict.ContainsKey(columnName))
{
var property = propertyDict[columnName];
property.SetValue(newItem, row.Columns[i].ConvertToTargetTypeValue(property.PropertyType), null);
}
}
if (newItem is IModified)
{
(newItem as IModified).IsModified = false;
}
if (newItem is IPropertyModified)
{
(newItem as IPropertyModified).MarkAsDefault();
}
result.Add(newItem);
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
//if (client.State != CommunicationState.Faulted)
// client.Close();
}
}