/// <summary>
/// Builds the insert command.
/// </summary>
/// <param name="cmd">The CMD.</param>
/// <param name="obj">The obj.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="dbSettings">The db settings.</param>
/// <param name="includeFields">The include fields.</param>
/// <param name="excludeFields">The exclude fields.</param>
/// <returns>DbCommand.</returns>
public static DbCommand BuildInsertCommand(
this DbCommand cmd,
object obj,
string tableName,
DbSettings dbSettings,
string[] includeFields,
string[] excludeFields)
{
IMappingConfigurator config = new AddDbCommandsMappingConfig(
dbSettings,
includeFields,
excludeFields,
"insertop_inc_" + includeFields.ToCsv("_") + "_exc_" + excludeFields.ToCsv("_"));
var mapper = Mapper.Default.GetMapper(obj.GetType(), typeof(DbCommand), config);
var fields = mapper.StoredObjects.OfType <SrcReadOperation>().Select(m => m.Source.MemberInfo.Name);
var cmdStr = "INSERT INTO " + tableName + "(" + fields.Select(dbSettings.GetEscapedName).ToCsv(",") + ") VALUES ("
+ fields.Select(dbSettings.GetParamName).ToCsv(",") + ")";
cmd.CommandText = cmdStr;
cmd.CommandType = CommandType.Text;
mapper.Map(obj, cmd, null);
return(cmd);
}
public static DbCommand BuildInsertCommand(
this DbCommand cmd,
object obj,
string tableName,
DbSettings dbSettings,
string[] includeFields,
string[] excludeFields, Boolean isOutGuid, String outKey
)
{
IMappingConfigurator config = new AddDbCommandsMappingConfig(
dbSettings,
includeFields,
excludeFields,
"insertop_inc_" + includeFields.ToCSV("_") + "_exc_" + excludeFields.ToCSV("_")
);
var mapper = ObjectMapperManager.DefaultInstance.GetMapperImpl(
obj.GetType(),
typeof(DbCommand),
config
);
string[] fields = mapper.StroredObjects.OfType <SrcReadOperation>().Select(m => m.Source.MemberInfo.Name).ToArray();
var sb = new StringBuilder();
if (isOutGuid)
{
sb.Append("DECLARE @RESULT TABLE (pkey UNIQUEIDENTIFIER)");
sb.Append("INSERT INTO " + tableName + "(" + fields.Select(dbSettings.GetEscapedName).ToCSV(",") + ") ");
sb.AppendFormat("OUTPUT INSERTED.{0} INTO @RESULT ", outKey);
sb.Append("VALUES (" + fields.Select(dbSettings.GetParamName).ToCSV(",") + @")");
sb.Append("SELECT pkey FROM @RESULT");
}
else
{
sb.Append("INSERT INTO " + tableName + "(" + fields.Select(dbSettings.GetEscapedName).ToCSV(",") + ") ");
sb.Append("VALUES (" + fields.Select(dbSettings.GetParamName).ToCSV(",") + @") SELECT @@IDENTITY");
}
cmd.CommandText = sb.ToString();
cmd.CommandType = System.Data.CommandType.Text;
mapper.Map(obj, cmd, null);
return(cmd);
}
public static DbCommand BuildInsertCommand(
this DbCommand cmd,
object obj,
string tableName,
string[] includeFields,
string[] excludeFields
)
{
IMappingConfigurator config = new AddDbCommandsMappingConfig(
includeFields,
excludeFields,
"insertop_inc_" + includeFields.ToCSV("_") + "_exc_" + excludeFields.ToCSV("_")
);
var mapper = ObjectMapperManager.DefaultInstance.GetMapperImpl(
obj.GetType(),
typeof(DbCommand),
config
);
string[] fields = mapper.StroredObjects.OfType <SrcReadOperation>().Select(m => m.Source.MemberInfo.Name).ToArray();
var cmdStr =
"INSERT INTO "
+ tableName +
"("
+ fields
.Select(f => f)
.ToCSV(",")
+ ") VALUES ("
+ fields
.Select(f => Constants.ParameterPrefix + f)
.ToCSV(",")
+ ")"
;
cmd.CommandText = cmdStr;
cmd.CommandType = System.Data.CommandType.Text;
mapper.Map(obj, cmd, null);
return(cmd);
}
public static bool BuildUpdateCommand(
this DbCommand cmd,
object obj,
string tableName,
IEnumerable <string> idFieldNames,
IEnumerable <string> includeFields,
IEnumerable <string> excludeFields,
ObjectsChangeTracker changeTracker,
DbSettings dbSettings
)
{
if (idFieldNames == null)
{
idFieldNames = new string[0];
}
//idFieldNames = idFieldNames.Select(n => n.ToUpper()).ToArray(); //不Upper会怎样
if (changeTracker != null)
{
ObjectsChangeTracker.TrackingMember[] changedFields = changeTracker.GetChanges(obj);
if (changedFields != null)
{
includeFields = includeFields == null?changedFields.Select(c => c.name).Except(idFieldNames).ToArray()
: includeFields.Intersect(changedFields.Select(c => c.name)).Except(idFieldNames).ToArray();
}
}
if (includeFields != null)
{
includeFields = includeFields.Concat(idFieldNames);
}
IMappingConfigurator config = new AddDbCommandsMappingConfig(
dbSettings,
includeFields,
excludeFields,
"updateop_inc_" + includeFields.ToCSV("_") + "_exc_" + excludeFields.ToCSV("_")
);
ObjectsMapperBaseImpl mapper = ObjectMapperManager.DefaultInstance.GetMapperImpl(
obj.GetType(),
typeof(DbCommand),
config
);
string[] fields = mapper
.StroredObjects
.OfType <SrcReadOperation>()
.Select(m => m.Source.MemberInfo.Name)
.Where(f => !idFieldNames.Contains(f))
.Except(idFieldNames)
.ToArray(); //2014-4-29 cannot change id.
if (fields.Length == 0)
{
return(false);
}
var enableCol = new string[] { "TableName", "IdName" };
fields = fields.Where(c => !enableCol.Contains(c)).ToArray();
string cmdStr =
"UPDATE " +
tableName +
" SET " +
fields.Select(
f => dbSettings.GetEscapedName(f) + "=" + dbSettings.GetParamName(f)
)
.ToCSV(",") +
" WHERE " +
idFieldNames.Select(fn => dbSettings.GetEscapedName(fn) + "=" + dbSettings.GetParamName(fn))
.ToCSV(" AND ")
;
cmd.CommandText = cmdStr;
cmd.CommandType = CommandType.Text;
mapper.Map(obj, cmd, null);
return(true);
}
/// <summary>
/// Builds the update command.
/// </summary>
/// <param name="cmd">The CMD.</param>
/// <param name="obj">The obj.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="idFieldNames">The id field names.</param>
/// <param name="includeFields">The include fields.</param>
/// <param name="excludeFields">The exclude fields.</param>
/// <param name="changeTracker">The change tracker.</param>
/// <param name="dbSettings">The db settings.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns>
public static bool BuildUpdateCommand(
this DbCommand cmd,
object obj,
string tableName,
IEnumerable <string> idFieldNames,
IEnumerable <string> includeFields,
IEnumerable <string> excludeFields,
ObjectsChangeTracker changeTracker,
DbSettings dbSettings)
{
if (idFieldNames == null)
{
idFieldNames = new string[0];
}
idFieldNames = idFieldNames.Select(n => n.ToUpper());
if (changeTracker != null)
{
var changedFields = changeTracker.GetChanges(obj);
if (changedFields != null)
{
if (includeFields == null)
{
includeFields = changedFields.Select(c => c.Name);
}
else
{
includeFields = includeFields.Intersect(changedFields.Select(c => c.Name));
}
}
}
if (includeFields != null)
{
includeFields = includeFields.Concat(idFieldNames);
}
IMappingConfigurator config = new AddDbCommandsMappingConfig(
dbSettings,
includeFields,
excludeFields,
"updateop_inc_" + includeFields.ToCsv("_") + "_exc_" + excludeFields.ToCsv("_"));
var mapper = Mapper.Default.GetMapper(obj.GetType(), typeof(DbCommand), config);
var fields = mapper.StoredObjects.OfType <SrcReadOperation>().Select(m => m.Source.MemberInfo.Name)
.Where(f => !idFieldNames.Contains(f));
if (!fields.Any())
{
return(false);
}
var cmdStr = "UPDATE " + tableName + " SET "
+ fields.Select(
f => dbSettings.GetEscapedName(f.ToUpper()) + "=" + dbSettings.GetParamName(f.ToUpper())).ToCsv(",")
+ " WHERE " + idFieldNames
.Select(fn => dbSettings.GetEscapedName(fn) + "=" + dbSettings.GetParamName(fn)).ToCsv(" AND ");
cmd.CommandText = cmdStr;
cmd.CommandType = CommandType.Text;
mapper.Map(obj, cmd, null);
return(true);
}
/// <summary>
/// 新增SQL
/// </summary>
/// <returns></returns>
internal SqlCommand GetInsertCmd()
{
IMappingConfigurator config = new AddDbCommandsMappingConfig(
null,
null,
"insertop_inc__exc_"
);
var mapper = ObjectMapperManager.DefaultInstance.GetMapperImpl(
this.GetType(),
typeof(DbCommand),
config
);
SqlCommand cmd = new SqlCommand();
string[] fields = mapper.StroredObjects.OfType <SrcReadOperation>().Select(m => m.Source.MemberInfo.Name).ToArray();
var cmdStr =
"INSERT INTO "
+ TableName +
"("
+ fields
.Select(f => f).Where(f => f != Identity)
.ToCSV(",")
+ ") VALUES ("
+ fields
.Where(f => f != Identity).Select(f => Constants.ParameterPrefix + f)
.ToCSV(",")
+ ")"
;
cmd.CommandText = cmdStr;
cmd.CommandType = System.Data.CommandType.Text;
mapper.Map(this, cmd, null);
return(cmd);
/* var changes = _tracker.GetChanges(this);
* string insertSQl = "insert into {0} ({1}) values ({2}) ";
*
* var colBuilder = new StringBuilder();
* var valBuilder = new StringBuilder();
* var parameters = new Dictionary<string, object>();
* int idx = 0;
* foreach (var changedProp in changes)
* {
* if (idx == changes.Length - 1)
* {
* colBuilder.Append(changedProp.name + "");
* valBuilder.Append("@" + changedProp.name + "");
* parameters.Add("@" + changedProp.name, changedProp.value);
* }
* else
* {
* colBuilder.Append(changedProp.name + ",");
* valBuilder.Append("@" + changedProp.name + ",");
* parameters.Add("@" + changedProp.name, changedProp.value);
* }
* idx++;
* }
* var sql = string.Format(insertSQl, TableName, colBuilder, valBuilder);*/
//IOctDbCommand retcmd = new OctDbCommand(cmdStr, cmd.Parameters);
//return retcmd;
}
