//构造Delete Sql语句
private void GetSqlDelete()
{
//Add 'DELETE FROM' clause to the select statement
this.AddSqlClause("DELETE FROM ");
this.AddSqlClause(ThisClassMap.RelationalDatabase.QuotationMarksStart + ThisClassMap.GetAttributeMap(0).Column.Table.Name + ThisClassMap.RelationalDatabase.QuotationMarksEnd);
//Add 'WHERE key= "some value"' to the select statement
this._deleteClause = this.sql;
this.AddSqlClause(" WHERE 1=1 ");
AttributeMap attrMap;
ClassMap thisClassMap = this.ThisClassMap;
for (int i = 0; i < thisClassMap.GetKeySize(); i++)
{
attrMap = thisClassMap.GetKeyAttributeMap(i);
//该列是否Primary Key
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
this.partForObject += " AND " + this.clsMap.GetFullyQualifiedName(attrMap.Column.Name) + "=" +
this.clsMap.RelationalDatabase.GetStringParameter(attrMap.Name, i);
}
}
//tintown add by 2005-3-21 添加timestamp功能
if (ThisClassMap.TimestampAttribute != null)
{
this.AddSqlClause(" AND " + this.clsMap.GetFullyQualifiedName(ThisClassMap.TimestampAttribute.Column.Name) + "=");
this.AddSqlClause(ThisClassMap.RelationalDatabase.GetStringParameter(ThisClassMap.TimestampAttribute.Column.Name, ThisClassMap.GetKeySize()));
}
}
public override IDbCommand BuildForObject(PersistentObject obj)
{
AttributeMap attrMap;
IDbCommand cmd = m_ClassMap.PersistenceProvider.GetCommand();
object tmp;
cmd.CommandText = this.SqlString;
int size = ThisClassMap.GetSize();
for (int i = 0; i < size; i++)
{
attrMap = ThisClassMap.GetAttributeMap(i);
if (attrMap.Column.IsAutoValue)
{
continue;
}
IDataParameter p = cmd.CreateParameter();
p.ParameterName = "@" + attrMap.Name;
p.DbType = attrMap.Column.Type;
tmp = obj.GetAttributeValue(attrMap.Name);
p.Value = (tmp == null ? DBNull.Value : tmp);
Assert.Verify(attrMap.Column.Size > p.Value.ToString().Length, Error.DataTooLong, p.ParameterName.ToString().Replace('@', ' ') + "column exceeds database length limit");
cmd.Parameters.Add(p);
}
return(cmd);
}
//构造Sql语句前部分
private void GetSqlInsert()
{
this.AddSqlClause("INSERT INTO ");
this.AddSqlClause(ThisClassMap.RelationalDatabase.QuotationMarksStart + ThisClassMap.GetAttributeMap(0).Column.Table.Name + ThisClassMap.RelationalDatabase.QuotationMarksEnd);
this.AddSqlClause(" ");
string sqlParas = "";
//Add clauses for all attributes
AttributeMap am;
bool isFirst = true;
this.AddSqlClause("(");
int size = ThisClassMap.GetSize();
for (int i = 0; i < size; i++)
{
am = ThisClassMap.GetAttributeMap(i);
if (am.Column.IsAutoValue)
{
continue;
}
if (isFirst)
{
this.AddSqlClause(clsMap.RelationalDatabase.GetQuotationColumn(am.Column.Name));
//sqlParas="?";
sqlParas = this.clsMap.RelationalDatabase.GetStringParameter(am.Name, i);
}
else
{
this.AddSqlClause(",");
this.AddSqlClause(clsMap.RelationalDatabase.GetQuotationColumn(am.Column.Name));
//sqlParas+=",?";
sqlParas += "," + this.clsMap.RelationalDatabase.GetStringParameter(am.Name, i);
}
isFirst = false;
}
//tintown added at 2005 -3-22 添加对timestamp的添加
if (ThisClassMap.TimestampAttribute != null)
{
if (!isFirst)
{
this.AddSqlClause(",");
sqlParas += ",";
}
this.AddSqlClause(clsMap.RelationalDatabase.GetQuotationColumn(ThisClassMap.TimestampAttribute.Column.Name));
//sqlParas+=",?";
sqlParas += this.clsMap.RelationalDatabase.GetStringParameter(ThisClassMap.TimestampAttribute.Name, size);
}
this.AddSqlClause(")");
//Add clause "VALUES" to the insertStatement
this.AddSqlClause(" VALUES (");
this.AddSqlClause(sqlParas);
this.AddSqlClause(")");
}
private void BuildUpdateSQL()
{
AttributeMap attrMap;
bool isFirst = true;
BuildSqlClause("UPDATE ");
BuildSqlClause(ThisClassMap.GetAttributeMap(0).Column.Table.Name);
BuildSqlClause(" ");
BuildSqlClause("SET ");
m_UpdateFromClause = m_SqlString;
int size = ThisClassMap.GetSize();
for (int i = 0; i < size; i++)
{
attrMap = ThisClassMap.GetAttributeMap(i);
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey || attrMap.Column.IsAutoValue)
{
continue;
}
if (isFirst)
{
isFirst = false;
}
else
{
BuildSqlClause(",");
}
BuildSqlClause(ThisClassMap.GetAttributeMap(i).Column.Name + "=");
BuildSqlClause(ThisClassMap.PersistenceProvider.GetStringParameter(attrMap.Column.Name));
}
BuildSqlClause(" WHERE 1=1");
for (int i = 0; i < ThisClassMap.GetKeySize(); i++)
{
attrMap = ThisClassMap.GetKeyAttributeMap(i);
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
BuildSqlClause(" AND " + attrMap.Column.Name + "=");
BuildSqlClause(m_ClassMap.PersistenceProvider.GetStringParameter(attrMap.Column.Name));
}
}
}
/// <summary>
/// Construct delete statement @field
/// </summary>
/// <remarks>
/// DELETE FROM [Table1] WHERE 1=1 AND Column1=@Column1
/// </remarks>
private void BuildDeleteSQL()
{
BuildSqlClause("DELETE FROM ");
BuildSqlClause(ThisClassMap.GetAttributeMap(0).Column.Table.Name);
m_DeleteFromClause = m_SqlString;
BuildSqlClause(" WHERE 1=1");
AttributeMap attrMap;
for (int i = 0; i < ThisClassMap.GetKeySize(); i++)
{
attrMap = ThisClassMap.GetKeyAttributeMap(i);
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
BuildSqlClause(" AND " + attrMap.Column.Name + "=");
BuildSqlClause(m_ClassMap.PersistenceProvider.GetStringParameter(attrMap.Column.Name));
}
}
}
private void BuildInsertSQL()
{
string sqlParas = "";
AttributeMap attrMap;
bool isFirst = true;
BuildSqlClause("INSERT INTO ");
BuildSqlClause(ThisClassMap.GetAttributeMap(0).Column.Table.Name);
BuildSqlClause(" ");
BuildSqlClause("(");
int size = ThisClassMap.GetSize();
for (int i = 0; i < size; i++)
{
attrMap = ThisClassMap.GetAttributeMap(i);
if (attrMap.Column.IsAutoValue)
{
continue;
}
if (isFirst)
{
BuildSqlClause(attrMap.Column.Name);
sqlParas = m_ClassMap.PersistenceProvider.GetStringParameter(attrMap.Name);
}
else
{
BuildSqlClause(",");
BuildSqlClause(attrMap.Column.Name);
sqlParas += "," + m_ClassMap.PersistenceProvider.GetStringParameter(attrMap.Name);
}
isFirst = false;
}
BuildSqlClause(") ");
BuildSqlClause("VALUES (");
BuildSqlClause(sqlParas);
BuildSqlClause(")");
}
public override IDbCommand BuildForObject(EntityObject obj, bool WithNoLock)
{
AttributeMap attrMap;
IDbCommand cmd = this.clsMap.RelationalDatabase.GetCommand();
object tmp;
cmd.CommandText = this.SqlString;
int size = ThisClassMap.GetSize();
for (int i = 0; i < size; i++)
{
attrMap = ThisClassMap.GetAttributeMap(i);
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
continue;
}
//tintown add at 2004-09-23
//如果此列为自动增长,则在生成update语句时跳过
if (attrMap.Column.IsAutoValue == true)
{
continue;
}
//tintown add end
IDataParameter p = cmd.CreateParameter();
//p.SourceColumn=attrMap.Column.GetFullyQualifiedName(); //列名
if (this.ThisClassMap.RelationalDatabase.Vendor == DatabaseVendor.MySql)
{
p.ParameterName = "?" + attrMap.Name;
}
else
{
p.ParameterName = "@" + attrMap.Name;
}
p.DbType = attrMap.Column.Type; //列类型
tmp = obj.GetAttributeValue(attrMap.Name); //列值
if (tmp == null || (tmp is DateTime && (DateTime)tmp == DateTime.MinValue))
{
p.Value = DBNull.Value;
}
else
{
p.Value = tmp;
}
cmd.Parameters.Add(p);
}
//tintown added at 2005-3-22 更新timestamp的值
if (ThisClassMap.TimestampAttribute != null)
{
attrMap = ThisClassMap.TimestampAttribute;
IDataParameter p = cmd.CreateParameter();
//p.SourceColumn=attrMap.Column.GetFullyQualifiedName(); //列名
if (this.ThisClassMap.RelationalDatabase.Vendor == DatabaseVendor.MySql)
{
p.ParameterName = "?Update" + attrMap.Name;
}
else
{
p.ParameterName = "@Update" + attrMap.Name;
}
//p.ParameterName = "@Update" + attrMap.Name;
p.DbType = attrMap.Column.Type; //列类型
p.Value = DateTime.Now.Ticks; //此timestamp由now的ticks生成一串数字
cmd.Parameters.Add(p);
}
for (int i = 0; i < ThisClassMap.GetKeySize(); i++)
{
attrMap = ThisClassMap.GetKeyAttributeMap(i);
IDataParameter p = cmd.CreateParameter();
//p.SourceColumn=attrMap.Column.GetFullyQualifiedName(); //列名
if (this.ThisClassMap.RelationalDatabase.Vendor == DatabaseVendor.MySql)
{
p.ParameterName = "?" + attrMap.Name;
}
else
{
p.ParameterName = "@" + attrMap.Name;
}
//p.ParameterName = "@" + attrMap.Name;
p.DbType = attrMap.Column.Type; //列类型
p.Value = obj.GetAttributeValue(attrMap.Name); //列值
cmd.Parameters.Add(p);
}
//tintown add by 2005-3-21 添加timestamp功能
if (ThisClassMap.TimestampAttribute != null)
{
attrMap = ThisClassMap.TimestampAttribute;
IDataParameter p = cmd.CreateParameter();
//p.SourceColumn=attrMap.Column.GetFullyQualifiedName(); //列名
if (this.ThisClassMap.RelationalDatabase.Vendor == DatabaseVendor.MySql)
{
p.ParameterName = "?" + attrMap.Name;
}
else
{
p.ParameterName = "@" + attrMap.Name;
}
//p.ParameterName = "@" + attrMap.Name;
p.DbType = attrMap.Column.Type; //列类型
p.Value = obj.GetAttributeValue(attrMap.Name); //列值
cmd.Parameters.Add(p);
}
return(cmd);
}
//构造Update Sql语句
private void GetSqlUpdate()
{
this.AddSqlClause("UPDATE ");
this.AddSqlClause(ThisClassMap.RelationalDatabase.QuotationMarksStart + ThisClassMap.GetAttributeMap(0).Column.Table.Name + ThisClassMap.RelationalDatabase.QuotationMarksEnd);
this.AddSqlClause(" ");
//Add clauses for all attributes but key attributes
this.AddSqlClause("SET ");
AttributeMap attrMap;
bool isFirst = true;
for (int i = 0; i < ThisClassMap.GetSize(); i++)
{
attrMap = ThisClassMap.GetAttributeMap(i);
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
continue;
}
//add by tintown at 2004-09-23
//对于自动增长的列,不进行update
if (attrMap.Column.IsAutoValue == true)
{
continue;
}
//add by tintown end
if (isFirst)
{
//this.AddSqlClause(ThisClassMap.GetAttributeMap(i).Column.GetFullyQualifiedName()+"=?");
this.AddSqlClause(clsMap.GetFullyQualifiedName(ThisClassMap.GetAttributeMap(i).Column.Name) + "=");
this.AddSqlClause(ThisClassMap.RelationalDatabase.GetStringParameter(attrMap.Name, i));
isFirst = false;
}
else
{
this.AddSqlClause(",");
//this.AddSqlClause(ThisClassMap.GetAttributeMap(i).Column.GetFullyQualifiedName()+"=?");
this.AddSqlClause(clsMap.GetFullyQualifiedName(ThisClassMap.GetAttributeMap(i).Column.Name) + "=");
this.AddSqlClause(ThisClassMap.RelationalDatabase.GetStringParameter(attrMap.Name, i));
}
}
//tintown added at 2005-3-22 SQL语句中添加更新timestamp
if (ThisClassMap.TimestampAttribute != null)
{
this.AddSqlClause(",");
this.AddSqlClause(clsMap.GetFullyQualifiedName(ThisClassMap.TimestampAttribute.Column.Name) + "=");
this.AddSqlClause(ThisClassMap.RelationalDatabase.GetStringParameter("Update" + ThisClassMap.TimestampAttribute.Column.Name, ThisClassMap.GetSize() + 1));
}
//Add clause "WHERE" to the insertStatement
this.AddSqlClause(" WHERE 1=1 ");
for (int i = 0; i < ThisClassMap.GetKeySize(); i++)
{
attrMap = ThisClassMap.GetKeyAttributeMap(i);
//该列是否Primary Key
if (attrMap.Column.KeyType == ColumnKeyTypes.PrimaryKey)
{
this.AddSqlClause(" AND " + clsMap.GetFullyQualifiedName(attrMap.Column.Name) + "=");
this.AddSqlClause(clsMap.RelationalDatabase.GetStringParameter(attrMap.Name, ThisClassMap.GetSize() + i + 1));
}
}
//tintown add by 2005-3-21 添加timestamp功能
if (ThisClassMap.TimestampAttribute != null)
{
this.AddSqlClause(" AND " + clsMap.GetFullyQualifiedName(ThisClassMap.TimestampAttribute.Column.Name) + "=");
this.AddSqlClause(ThisClassMap.RelationalDatabase.GetStringParameter(ThisClassMap.TimestampAttribute.Column.Name, ThisClassMap.GetKeySize() + ThisClassMap.GetSize() + 2));
}
}