/// <summary>
/// 删除当前Record
/// </summary>
public virtual void Delete()
{
string sql = string.Format("Delete from [{0}] where [{1}]='{2}'", this.tableName, this.identifierField.Column.Name, this.identifierField.Value);
SQLDBOperator opt = new SQLDBOperator(connectionString, true);
opt.ExecuteNonQuerySQL(sql);
}
/// <summary>
/// 更新数据
/// </summary>
/// <returns></returns>
protected int Update()
{
int effectLines = 0;
string sql = string.Format("update [{0}] set ", this.tableName);
List <object> parameters = new List <object>(5);
for (int i = 0; i < fields.Count; i++)
{
sql += string.Format("[{0}]=@{1}", fields[i].Column.Name, i.ToString());
if (!IsLastField(i))
{
sql += ",";
}
parameters.Add(fields[i].Value);
}
if (this.identifierField == null)
{
throw new Exception("未找到标识字段，请检查初始化DBTable对象时的标识字段名称是否正确！");
}
sql += string.Format(" where {0}='{1}'", this.identifierField.Column.Name, this.identifierField.Value);
SQLDBOperator opt = new SQLDBOperator(this.connectionString);
opt.IsLaunchTransaction = true;
effectLines = opt.ExecuteNonQuerySQL(sql, parameters.ToArray());
return(effectLines);
}
/// <summary>
/// 清除全部数据
/// </summary>
public void ClearRecords()
{
string sql = "";
sql = string.Format("delete from [{0}] ", this.tableName);
SQLDBOperator opt = new SQLDBOperator(this.connectionString);
opt.ExecuteNonQuerySQL(sql);
}
/// <summary>
/// 填充表数据Records
/// </summary>
/// <param name="sql">查询语句</param>
protected override void FillRecords(string sql)
{
SQLDBOperator opt = new SQLDBOperator(this.ConnectionString);
object[][] dataRows = opt.GetDataRows(sql);
foreach (object[] dataRow in dataRows)
{
SQLDBRecord record = new SQLDBRecord(this.TableName, this.IdentifierName, this.ConnectionString, Columns);
for (int i = 0; i < dataRow.Length; i++)
{
record.Fields[i].Value = dataRow[i];
}
record.State = Enums.DBRecordState.Saved;
this.Records.Add(record);
}
}
/// <summary>
/// 初始化全部列
/// </summary>
protected override void InitializeColumn()
{
string sql = @"SELECT sys.syscolumns.name, sys.systypes.name AS type
FROM sys.syscolumns INNER JOIN
sys.sysobjects ON sys.syscolumns.id = sys.sysobjects.id AND sys.sysobjects.name = @0 AND
sys.sysobjects.xtype = 'U' INNER JOIN
sys.systypes ON sys.systypes.xusertype = sys.syscolumns.xusertype";
SQLDBOperator dbOperator = new SQLDBOperator(this.ConnectionString);
object[][] rows = dbOperator.GetDataRows(sql, this.TableName);
for (int i = 0; i < rows.Length; i++)
{
Enums.DataType type = Enums.GetColumnDataTypeByNameString(rows[i][1].ToString());
DBColumn column = new DBColumn(rows[i][0].ToString(), type);
this.Columns.Add(column);
}
}
/// <summary>
/// 根据条件删除记录
/// </summary>
/// <param name="sqlWhere">查询条件，注意该条件在写SQL语句时不需要加“Where”字样，直接写查询条件。值为null或空时删除全部数据。</param>
public void DeleteRecords(string sqlWhere)
{
if (string.IsNullOrEmpty(sqlWhere))
{
throw new Exception("查询条件不能为空！");
}
string sql = "";
if (string.IsNullOrEmpty(sqlWhere) || string.IsNullOrWhiteSpace(sqlWhere))
{
sql = string.Format("delete from [{0}] ", this.tableName);
}
else
{
sql = string.Format("delete from [{0}] where {1}", this.tableName, sqlWhere);
}
SQLDBOperator opt = new SQLDBOperator(this.connectionString);
opt.ExecuteNonQuerySQL(sql);
}
/// <summary>
/// 新增数据
/// </summary>
/// <returns></returns>
protected int Insert()
{
int effectLines = 0;
string sql = string.Format(@"insert into [{0}] (", this.tableName);
for (int i = 0; i < fields.Count; i++)
{
sql += string.Format(@"[{0}]", fields[i].Column.Name);
if (!IsLastField(i))
{
sql += ",";
}
}
sql += ") values (";
List <object> parameters = new List <object>(5);
for (int i = 0; i < fields.Count; i++)
{
sql += string.Format("@{0}", i.ToString());
if (!IsLastField(i))
{
sql += ",";
}
parameters.Add(fields[i].Value);
}
sql += ")";
SQLDBOperator opt = new SQLDBOperator(this.connectionString, true);
effectLines = opt.ExecuteNonQuerySQL(sql, parameters.ToArray());
return(effectLines);
}
/// <summary>
/// 根据标识字段删除记录
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="id">标识字段值</param>
protected override void DeleteRecord(string sql, object id)
{
SQLDBOperator opt = new SQLDBOperator(this.ConnectionString, true);
opt.ExecuteNonQuerySQL(sql, id);
}
