sql server and oracle 不同之处只在于：
1·参数@和：
2·自增和序列
RPROM.cs //(写错愕，应该是RPORM)
RPROM.Insert(p1); //需求：DBS中插入一条数据
约定：
类名要和表名一样
字段名和数据库列名一样
主键的名字必须是Id,必须自动递增,int类型
class RupengROM
{
/// 插入对象
internal static void Insert(Object obj)
{
//获得类名
Type type = obj.GetType();
string className = type.Name;
//获得除id的所有列名
PropertyInfo[] properties = type.GetProperties();
string[] propertyNames = new string[properties.Length - 1]; //存除id的属性名(列名)
string[] propertyAtNames = new string[properties.Length - 1]; //存除id的属性+@名
//获得除id的所有参数
MySqlParameter[] mysqlParameters = new MySqlParameter[properties.Length - 1]; //存除id的参数名
int i = 0;
foreach(PropertyInfo property in properties)
{
if(property.Name!="Id")
{
propertyNames[i] = property.Name;
propertyAtNames[i] = "@" + property.Name;
MySqlParameter mysqlParam = new MySqlParameter();
mysqlParam.ParameterName = "@" + property.Name;
mysqlParam.Value = property.GetValue(obj); //获得指定对象的属性值
mysqlParameters[i] = mysqlParam;
i++;
}
}
string propertyNamesStr = string.Join(",", propertyNames);
string propertyAtNamesStr = string.Join(",", propertyAtNames);
//拼接sql语句
//insert into T_Person(Name,Age) values(@Name,@Age)
StringBuilder sb = new StringBuilder();
sb.Append("insert into T_").Append(className).Append("(").Append(propertyNamesStr).Append(") values(").Append(propertyAtNamesStr).Append(")");
int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), mysqlParameters);
string msg = im > 0 ? "插入成功" : "插入失败";
Console.WriteLine(msg);
}
/// 根据id查询对象
internal static Object SelectById(Type type,int id)
{
string className = type.Name;
//拼接sql语句
//select * from T_Person where Id=@Id
StringBuilder sb = new StringBuilder();
sb.Append("select * from T_").Append(className).Append(" where Id=@Id");
DataTable dt = MySqlHelper.ExecuteQuery(sb.ToString(), new MySqlParameter() { ParameterName = "@Id", Value = id });
if(dt.Rows.Count<=0)
{
return null;
}
else if(dt.Rows.Count>1)
{
throw new Exception("数据库发生异常，存在id=" + id + "的重复数据");
}
else
{
DataRow row = dt.Rows[0];
Object obj = Activator.CreateInstance(type); //动态创建指定类型的实例
PropertyInfo[] propertyInfoes = type.GetProperties();
foreach(PropertyInfo propertyInfo in propertyInfoes)
{
string propertyName = propertyInfo.Name; //属性名就是列名
object value = row[propertyName];
propertyInfo.SetValue(obj, value); //设置指定对象的属性值
}
return obj;
}
}
/// 根据Id更新实例
internal static void UpdateById(Object obj)
{
//获得类名
Type type = obj.GetType();
string className = type.Name;
//获得除id的列名变化形式 "Name=@Name"
PropertyInfo[] propertyInfoes = type.GetProperties();
string[] propertyChanges = new string[propertyInfoes.Length - 1]; //存除id的属性变化形式
MySqlParameter[] mysqlParameters = new MySqlParameter[propertyInfoes.Length]; //存sql参数
int i = 0;
foreach(PropertyInfo propertyInfo in propertyInfoes)
{
string propertyName = propertyInfo.Name;
if (propertyName != "Id")
{
propertyChanges[i] = propertyName + "=@" + propertyName;
i++;
}
MySqlParameter mysqlParam = new MySqlParameter();
mysqlParam.ParameterName = "@" + propertyName;
mysqlParam.Value = propertyInfo.GetValue(obj);
mysqlParameters[i] = mysqlParam;
}
string propertyChangesStr = string.Join(",", propertyChanges);
//拼接sql语句
//update T_Person set Name=@Name,Age=@Age where Id=@Id
StringBuilder sb = new StringBuilder();
sb.Append("update T_").Append(className).Append(" set ").Append(propertyChangesStr).Append(" where Id=@Id");
int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), mysqlParameters);
string msg = im > 0 ? "更新成功" : "更新失败";
Console.WriteLine(msg);
}
/// 根据id删除
internal static void DeleteById(Type type, int id)
{
//获得类名
string className = type.Name;
//拼接sql语句
//delete from T_Person where Id=@Id
StringBuilder sb = new StringBuilder();
sb.Append("delete from T_").Append(className).Append(" where Id=@Id");
int im = MySqlHelper.ExecuteNonQuery(sb.ToString(), new MySqlParameter() { ParameterName = "@Id", Value = id });
string msg = im > 0 ? "删除成功" : "删除失败";
Console.WriteLine(msg);
}
}
