//获取对象的新增SQL语句(只限于SqlServer数据库) public virtual string GenerateInsertSql() { Type thisType = this.GetType(); PropertyInfo[] ps = thisType.GetProperties(); StringBuilder insertSql = new StringBuilder(); string tableName; TableInfoAttribute tabattr = TableInfoAttribute.GetAttribute(thisType); if (tabattr == null) { tableName = thisType.Name; } else { tableName = tabattr.TableName; } insertSql.AppendFormat("INSERT INTO [{0}] (", tableName); StringBuilder values = new StringBuilder(); for (int i = 0, j = 0; i < ps.Length; i++) { DataFieldAttribute fieldattr = DataFieldAttribute.GetAttribute(ps[i]); //如果是自增长字段,跳过 if (fieldattr != null && fieldattr.Identity) { continue; } if (ps[i].Name == "IsNew") { continue; } if (j != 0) { insertSql.Append(","); values.Append(","); } insertSql.AppendFormat("[{0}]", ps[i].Name); object value = ps[i].GetValue(this, null); if (value == null) { values.Append("NULL"); } else { values.AppendFormat("'{0}'", value.ToString().Replace("'", "''")); } j++; } insertSql.Append(") VALUES("); insertSql.Append(values); insertSql.Append(")"); return(insertSql.ToString()); }
/// <summary> /// 生成对象的Update语句 /// </summary> /// <returns></returns> public virtual string GenerateUpdateSql() { Type thisType = this.GetType(); PropertyInfo[] ps = thisType.GetProperties(); TableInfoAttribute tableAttr = TableInfoAttribute.GetAttribute(thisType); if (tableAttr == null) { throw new Exception("对象未设置数据库对应关系"); } string primaryKey = tableAttr.PrimaryKey; string tableName = tableAttr.TableName; StringBuilder updateSql = new StringBuilder(); updateSql.AppendFormat("UPDATE {0} SET ", tableAttr.TableName); StringBuilder where = new StringBuilder("\n WHERE "); Dictionary <string, object> modifyValues = GetModifyValues(); //如果未做修改,返回空字符串 if (modifyValues.Count == 0) { return(string.Empty); } int fieldCount = 0; foreach (string key in modifyValues.Keys) { PropertyInfo p = ps.First(a => a.Name == key); DataFieldAttribute fieldAttr = DataFieldAttribute.GetAttribute(p); //如果定义为自增长字段,则跳过 if (fieldAttr != null && fieldAttr.Identity) { continue; } //如果是主键,则跳过 if (p.Name == primaryKey) { continue; } object value = p.GetValue(this, null); if (value == null) { updateSql.AppendFormat("[{0}]=NULL,", p.Name); } else { string valueStr = value.ToString().Replace("'", "''"); updateSql.AppendFormat("[{0}]='{1}',", p.Name, valueStr); } fieldCount++; } if (fieldCount == 0) { return(string.Empty); } //去掉最后一个逗号 updateSql = updateSql.Remove(updateSql.Length - 1, 1); //添加WHERE条件 PropertyInfo pk = ps.First(p => primaryKey == p.Name); where.AppendFormat("[{0}]='{1}'", primaryKey, _oldValues[primaryKey]); updateSql.Append(where); return(updateSql.ToString()); }
public int Save(string databasePath) { StringBuilder sql = new StringBuilder(); Type t = this.GetType(); TableInfoAttribute tableAttr = TableInfoAttribute.GetAttribute(t); PropertyInfo pk = t.GetProperty(tableAttr.PrimaryKey); DataFieldAttribute fieldAttr = DataFieldAttribute.GetAttribute(pk); DataAccess sda = new SqlDataAccess(databasePath); if (IsNew) { //调用新增方法保存 //返回新增记录的主键 sql.Append(GenerateInsertSql()); sql.AppendLine(); if (fieldAttr != null && fieldAttr.Identity) { //主键是自增长类型的 sql.Append("SELECT @@Identity AS " + tableAttr.PrimaryKey); } else { sql.AppendFormat("SELECT {0} AS {1}", pk.GetValue(this, null), tableAttr.PrimaryKey); } DataSet ds = sda.GetDataSet(sql.ToString()); this.IsNew = false; int primaryKey = int.Parse(ds.Tables[0].Rows[0][tableAttr.PrimaryKey].ToString()); pk.SetValue(this, primaryKey, null); //新增成功后,需要把现有的属性放入_oldValues,以防止下次保存时,数据不能提交 _oldValues = new Dictionary <string, object>(); foreach (var item in t.GetProperties()) { if (item.Name == "IsNew") { continue; } _oldValues.Add(item.Name, item.GetValue(this, null)); } return(primaryKey); } else { object primaryKey = pk.GetValue(this, null); string updatesql = GenerateUpdateSql(); if (!string.IsNullOrWhiteSpace(updatesql)) { sda.ExecuteSql(updatesql); _oldValues = new Dictionary <string, object>(); foreach (var item in t.GetProperties()) { if (item.Name == "IsNew") { continue; } _oldValues.Add(item.Name, item.GetValue(this, null)); } } return((int)primaryKey); } }