public virtual void Insert(DataItem dataitem)
{
bool needToClose = false;
if (this.Connection.State != System.Data.ConnectionState.Open)
{
needToClose = true;
this.Connection.Open();
}
string pkid = dataitem.KeyName;
var fieldValues = dataitem.GetFieldValues(true);
if (fieldValues.Length == 0)
{
return;
}
StringBuilder str_fields = new StringBuilder();
StringBuilder str_values = new StringBuilder();
try
{
using (var command = CreateCommand(null))
{
int parameterIndex = 1;
foreach (var field in fieldValues)
{
if (str_fields.Length > 0)
{
str_fields.Append(',');
}
str_fields.Append(FormatObjectName(field.FieldName));
var parameter = command.CreateParameter();
parameter.ParameterName = "@p" + (parameterIndex++);
parameter.Value = field.Value;
command.Parameters.Add(parameter);
if (str_values.Length > 0)
{
str_values.Append(',');
}
str_values.Append(parameter.ParameterName);
}
string sql;
if (GetInsertIDValueSqlStringInOneSql())
{
sql = string.Format("insert into {0} ({1}) values ({2}) {3}", FormatObjectName(dataitem.TableName), str_fields, str_values, this.GetInsertIDValueSqlString(pkid));
command.CommandText = sql;
object id = command.ExecuteScalar();
command.Parameters.Clear();
if (id != null && !string.IsNullOrEmpty(pkid))
{
dataitem.SetValue(pkid, id);
}
}
else
{
sql = string.Format("insert into {0} ({1}) values ({2})", FormatObjectName(dataitem.TableName), str_fields, str_values);
command.CommandText = sql;
command.ExecuteNonQuery();
command.Parameters.Clear();
sql = this.GetInsertIDValueSqlString(pkid);
if (sql != null)
{
command.CommandText = sql;
object id = command.ExecuteScalar();
if (id != null && !string.IsNullOrEmpty(pkid))
{
dataitem.SetValue(pkid, id);
}
}
}
}
dataitem.ChangedProperties.Clear();
}
catch (Exception ex)
{
this.ThrowSqlException(dataitem.TableType, ex);
}
finally
{
if (needToClose)
{
this.Connection.Close();
}
}
}
public virtual void Update(DataItem dataitem)
{
string pkid = dataitem.KeyName;
object pkvalue = dataitem.PKValue;
if (pkvalue == null && pkid != null)
{
Insert(dataitem);
return;
}
bool needToClose = false;
if (this.Connection.State != System.Data.ConnectionState.Open)
{
needToClose = true;
this.Connection.Open();
}
try
{
StringBuilder str_fields = new StringBuilder();
int parameterIndex = 1;
using (var command = CreateCommand(null))
{
var fieldValues = dataitem.GetFieldValues(false);
if (fieldValues.Length == 0)
{
return;
}
foreach (var fieldValue in fieldValues)
{
if (str_fields.Length > 0)
{
str_fields.Append(',');
}
str_fields.Append(FormatObjectName(fieldValue.FieldName));
str_fields.Append('=');
object value = fieldValue.Value;
if (value == DBNull.Value || value == null)
{
str_fields.Append("null");
}
else
{
string parameterName = "@p" + (parameterIndex++);
var parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
command.Parameters.Add(parameter);
str_fields.Append(parameterName);
}
}
if (pkvalue != null)
{
var parameter = command.CreateParameter();
parameter.ParameterName = "@pid";
parameter.Value = pkvalue;
command.Parameters.Add(parameter);
command.CommandText = string.Format("update {0} set {1} where {2}=@pid", FormatObjectName(dataitem.TableName), str_fields, FormatObjectName(pkid.ToLower()));
}
else
{
command.CommandText = string.Format("update {0} set {1}", FormatObjectName(dataitem.TableName), str_fields);
}
command.ExecuteNonQuery();
command.Parameters.Clear();
}
}
catch (Exception ex)
{
ThrowSqlException(dataitem.TableType, ex);
}
finally
{
if (needToClose)
{
this.Connection.Close();
}
}
}
public virtual void Insert(DataItem dataitem)
{
bool needToClose = false;
if (this.Connection.State != System.Data.ConnectionState.Open)
{
needToClose = true;
this.Connection.Open();
}
var tableSchema = SchemaManager.GetSchemaTable(dataitem.GetType());
string pkid = null;
if (tableSchema != null)
{
pkid = tableSchema.KeyColumn.PropertyName;
}
var fieldValues = dataitem.GetFieldValues(true);
if (fieldValues.Count == 0)
{
return;
}
if (tableSchema != null && tableSchema.AutoSetPropertyNameOnInsert != null)
{
if (dataitem.ChangedProperties.Any(m => m.Key == tableSchema.AutoSetPropertyNameOnInsert) == false)
{
var val = tableSchema.AutoSetPropertyValueOnInsert;
if (!SupportEnum && val != null && val.GetType().IsEnum)
{
val = Convert.ToInt32(val);//PostgreSql不支持枚举
}
var fieldname = tableSchema.AutoSetPropertyNameOnInsert.ToLower();
var fv = fieldValues.FirstOrDefault(m => m.FieldName == fieldname);
if (fv != null)
{
fv.Value = val;
}
else
{
fieldValues.Add(new FieldValue()
{
FieldName = tableSchema.AutoSetPropertyNameOnInsert.ToLower(),
Value = val
});
}
}
}
StringBuilder str_fields = new StringBuilder();
StringBuilder str_values = new StringBuilder();
try
{
using (var command = CreateCommand(null))
{
int parameterIndex = 1;
foreach (var field in fieldValues)
{
if (str_fields.Length > 0)
{
str_fields.Append(',');
}
str_fields.Append(FormatObjectName(field.FieldName));
var parameter = command.CreateParameter();
parameter.ParameterName = "@p" + (parameterIndex++);
parameter.Value = field.Value;
if (!SupportEnum && field.Value != null && field.Value.GetType().IsEnum)
{
parameter.Value = Convert.ToInt32(field.Value);
}
command.Parameters.Add(parameter);
if (str_values.Length > 0)
{
str_values.Append(',');
}
str_values.Append(parameter.ParameterName);
}
string sql;
if (GetInsertIDValueSqlStringInOneSql())
{
sql = string.Format("insert into {0} ({1}) values ({2}) {3}", FormatObjectName(dataitem.TableName), str_fields, str_values, this.GetInsertIDValueSqlString(pkid));
command.CommandText = sql;
object id = command.ExecuteScalar();
command.Parameters.Clear();
if (id != null && id != DBNull.Value && !string.IsNullOrEmpty(pkid))
{
dataitem.SetValue(pkid, id);
}
}
else
{
sql = string.Format("insert into {0} ({1}) values ({2})", FormatObjectName(dataitem.TableName), str_fields, str_values);
command.CommandText = sql;
command.ExecuteNonQuery();
command.Parameters.Clear();
sql = this.GetInsertIDValueSqlString(pkid);
if (sql != null)
{
command.CommandText = sql;
object id = command.ExecuteScalar();
if (id != null && id != DBNull.Value && !string.IsNullOrEmpty(pkid))
{
dataitem.SetValue(pkid, id);
}
}
}
}
dataitem.ChangedProperties.Clear();
}
catch (Exception ex)
{
this.ThrowSqlException(dataitem.TableType, ex);
}
finally
{
if (needToClose)
{
this.Connection.Close();
}
}
}