ORM小练习代码
DOG类
namespaceRupengORM
{publicclassDog
{publicDog()
{
}///<summary>///显示提供无参构造函数///</summary>///<param name="aa"></param>publicDog(intaa)
{
}publicintId {get;set; }publicstringName {get;set; }publicintWeight {set;get; }
}
}
Sqlhelper:
usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data;usingMySQL.Data.MySqlClient;namespaceRupengORM
{publicclassDbSqlhelper
{PRivatestaticreadonlystringSqlconnstr = ConfigurationManager.ConnectionStrings["mysqlconn"].ConnectionString;publicstaticMySqlConnection CreateConnection()
{
MySqlConnection conn=newMySqlConnection(Sqlconnstr);
conn.Open();returnconn;
}publicstaticintExecuteNonQuery(MySqlConnection conn,stringsql,paramsMySqlParameter[] parameters)
{using(MySqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText=sql;
cmd.Parameters.AddRange(parameters);returncmd.ExecuteNonQuery();
}
}publicstaticintExecuteNonQuery(stringsql,paramsMySqlParameter [] parameters)
{using(MySqlConnection conn =CreateConnection())
{returnExecuteNonQuery(conn, sql, parameters);
}
}publicstaticobjectExecuteScalar(MySqlConnection conn,stringsql, Dictionary<string,object>dictionary)
{using(MySqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText=sql;foreach(varkvpindictionary)
{
IDbDataParameter parameter=cmd.CreateParameter();
parameter.ParameterName=kvp.Key;
parameter.Value=kvp.Value;
cmd.Parameters.Add(parameter);
}returncmd.ExecuteScalar();
}
}publicstaticobjectExecuteScalar(stringsql, Dictionary<string,object>dictionary)
{using(MySqlConnection conn=CreateConnection())
{returnExecuteScalar(conn, sql, dictionary);
}
}publicstaticDataTable ExecuteQuery(MySqlConnection conn,stringsql, Dictionary<string,object>dictionary)
{
DataTable dataTable=newDataTable();using(MySqlCommand cmd=conn.CreateCommand())
{
cmd.CommandText=sql;foreach(varkvpindictionary)
{
IDbDataParameter parameter=cmd.CreateParameter();
parameter.ParameterName=kvp.Key;
parameter.Value=kvp.Value;
cmd.Parameters.Add(parameter);using(IDataReader reader=cmd.ExecuteReader())
{
dataTable.Load(reader);
}
}
}returndataTable;
}publicstaticDataTable ExecuteQuery(stringsql, Dictionary<string,object>dictionary)
{using(MySqlConnection conn=CreateConnection())
{returnExecuteQuery(conn, sql, dictionary);
}
}
}
}
RProm 实现过程：
usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingMySql.Data.MySqlClient;namespaceRupengORM
{internalclassRPorm
{//约定：1、类名要和表名一样//2、字段名和数据库列名一样//3、主键的名字必须叫Id，必须是自动递增，int类型//////////publicstaticvoidInsert(objectobj)
{//获得obj对象的类名vartype = obj.GetType();//typeof(Person)varclassName = type.Name;//类名:Person//propertyInfos获得类里面所有的属性varpropertyInfos =type.GetProperties();varpropNames =newstring[propertyInfos.Length -1];//排除掉IdvarparamNames =newstring[propertyInfos.Length -1];varsqlParameters =newMySqlParameter[propertyInfos.Length -1];//Dictionary<string, object> dic = new Dictionary<string, object>();varcount =0;foreach(varpropInfoinpropertyInfos)
{varpropName =propInfo.Name;if(propName !="Id")//排除Id{//遍历赋值,包含ID不进入赋值propNames[count] =propName;
paramNames[count]="@"+propName;varmySqlParameter =newMySqlParameter();
mySqlParameter.ParameterName="@"+propName;
mySqlParameter.Value= propInfo.GetValue(obj);//去obj对象属性的值sqlParameters[count] =mySqlParameter;
count++;
}
}//拼接生成insert语句varsbSql =newStringBuilder();
sbSql.Append("insert into")
.Append(className)
.Append("(")
.Append(string.Join(",", propNames))
.Append(")");
sbSql.Append("values (").Append(string.Join(",", paramNames)).Append(")");
DbSqlhelper.ExecuteNonQuery(sbSql.ToString(), sqlParameters);//params可变长度参数本质上就是一个数组}publicstaticobjectSelectById(Type type,intid)
{//将表名获取到varclassname =type.Name;varsql ="select * from"+ classname +"where id=@id";vardictionary =newDictionary<string,object>();
dictionary["@id"] =id;vardataTable =DbSqlhelper.ExecuteQuery(sql, dictionary);if(dataTable.Rows.Count <=0)
{returnnull;
}if(dataTable.Rows.Count >1)
{thrownewException("查到多条ID="+ id +"的数据");
}varrow = dataTable.Rows[0];//创建type类的一个对象varobj =Activator.CreateInstance(type);//给obj对象的每一个属性(包括Id）赋值,得到id name weightforeach(varpropInfointype.GetProperties())
{varpropName = propInfo.Name;//属性名就是别名varvalue = row[propName];//获取数据库中列的值propInfo.SetValue(obj, value);//给obj对象的propinfo属性赋值为value}returnobj;
}publicstaticT SelectById<T>(intid)whereT :new()//泛型约束，约束T必须有一个无参的构造函数{vartype =typeof(T);//typeof(Person)varclassname =type.Name;varsql ="select * from"+ classname +"where id=@id";vardictionary =newDictionary<string,object>();
dictionary["@id"] =id;vardataTable =DbSqlhelper.ExecuteQuery(sql, dictionary);if(dataTable.Rows.Count <=0)
{returndefault(T);//default(T)运算符用来获得类型的默认值//default(int)→0 default(bool)→false default(Person)→null}if(dataTable.Rows.Count >1)
{thrownewException("查到多条ID="+ id +"的数据");
}varrow = dataTable.Rows[0];//创建type类的一个对象//var obj = Activator.CreateInstance(type);varobj =newT();//泛型约束//给obj对象的每一个属性(包括Id）赋值 返回当前 Type 的所有公共属性。foreach(varpropInfointype.GetProperties())
{varpropName = propInfo.Name;//属性名就是别名varvalue = row[propName];//获取数据库中列的值propInfo.SetValue(obj, value);//给obj对象的propinfo属性赋值为value}returnobj;
}publicstaticboolDeleteById(Type type,intid)
{varclassname =type.Name;varsql ="delete from"+ classname +"where id=@id";vari = DbSqlhelper.ExecuteNonQuery(sql,newMySqlParameter {ParameterName ="@id", Value =id});//delete from dog where name='孔老二4'returni >0;
}publicstaticboolUpdateById(objectobj)
{vartype =obj.GetType();varclassname = type.Name;//获得表名varpropertyInfos = type.GetProperties();//获得表名中的功能属性varpropNames =newstring[propertyInfos.Length];//获取该属性的长度varparamNames =newstring[propertyInfos.Length];varsqlParameters =newMySqlParameter[propertyInfos.Length];varcount =0;foreach(varpropInfoinpropertyInfos)
{varpropName =propInfo.Name;varmySqlParameter =newMySqlParameter();
mySqlParameter.ParameterName="@"+propName;
mySqlParameter.Value= propInfo.GetValue(obj);//去obj对象属性的值sqlParameters[count] =mySqlParameter;if(propName !="Id")//排除Id{//遍历赋值,包含ID不进入赋值propNames[count] = propName;//nameparamNames[count]= propName +"=@"+ propName;//@name}
count++;
}varoop =string.Join(",", paramNames).Substring(4);//sqlParameters;varsb =newStringBuilder();
sb.Append("update").Append(classname).Append("set").Append(oop).Append("where id=@id");varsqltxt =sb.ToString();vari =DbSqlhelper.ExecuteNonQuery(sqltxt, sqlParameters);//生成update语句//update dog set name=@name weight=@weight where id=@id//怎么知道那一列被修改了呢//把所有列都更新一下。反正不变的还是不变returni >0;
}
}
}
主程序：
usingSystem;namespaceRupengORM
{internalclassProgram
{privatestaticvoidMain(string[] args)
{//ORM:EF(entity framework,Dapper,Nhibernate)//Person p1=new Person();//p1.Name = "rupeng";//p1.Age = 7;//RPorm.Insert(p1);for(vari =0; i <10; i++)
{vard1 =newDog();
d1.Name="孔老二"+i;
d1.Weight=30;
RPorm.Insert(d1);
}//Person p1 = (Person)RPorm.SelectById(typeof(Person),1);//Console.WriteLine(p1.Name+"的年龄是"+p1.Age);//Dog p2 = (Dog)RPorm.SelectById(typeof(Dog), 1);//if (p2 == null)//{//Console.WriteLine("没找到狗");//}//else//{//Console.WriteLine(p2.Name);//}//Dog dog = RPorm.SelectById<Dog>(1);//Console.WriteLine(dog.Name);//Type type = new Type typeof(Dog);//bool aa= RPorm.DeleteById(typeof(Dog),2);//Console.WriteLine(aa);vardog =newDog();
dog.Weight++;
dog.Name="孔老二";
dog.Id=9;varupdate =RPorm.UpdateById(dog);
Console.WriteLine(update);
Console.ReadKey();
}
}
}