using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SimpleORM
{
public class PropertyAttribute : Attribute
{
public string tableName;
public ColumnKeyType columnKeyType;
/// <summary>
/// 重构方法默认值
/// </summary>
public PropertyAttribute()
{
this.columnKeyType = ColumnKeyType.Default;
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
public PropertyAttribute(string tableName)
{
this.tableName = tableName;
}
public PropertyAttribute(ColumnKeyType columnKeyType)
{
this.columnKeyType = columnKeyType;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SimpleORM
{
[Serializable]
[Property("TB_People")]//对应数据库中的表
public class M_People
{
string _Pl_ID;
/// <summary>
/// 主键
/// </summary>
[Property(ColumnKeyType.Identity)]
public string Pl_ID
{
get { return _Pl_ID; }
set { _Pl_ID = value; }
}
int _PL_Age;
public int PL_Age
{
get { return _PL_Age; }
set { _PL_Age = value; }
}
string _Pl_Sex;
public string Pl_Sex
{
get { return _Pl_Sex; }
set { _Pl_Sex = value; }
}
string _Pl_LoginName;
public string Pl_LoginName
{
get { return _Pl_LoginName; }
set { _Pl_LoginName = value; }
}
string _Pl_TrueName;
public string Pl_TrueName
{
get { return _Pl_TrueName; }
set { _Pl_TrueName = value; }
}
string _PL_Pwd;
public string PL_Pwd
{
get { return _PL_Pwd; }
set { _PL_Pwd = value; }
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Text.RegularExpressions;
namespace SimpleORM
{
public class EntityHelper
{/// <summary>
/// 从Model模型中获取数据表名
/// </summary>
public static string GetTableName(Type type)
{
PropertyAttribute property = (PropertyAttribute)(type.GetCustomAttributes(false)[0]);
return property.tableName;
}
/// <summary>
/// 从Model模型中获取数据主键名
/// </summary>
public static PropertyInfo GetTableIdentity(PropertyInfo[] pis)
{
object[] infos = null;
PropertyAttribute attribute = null;
foreach (PropertyInfo pi in pis)
{
infos = pi.GetCustomAttributes(false);
if (infos.Length > 0)
{
attribute = (PropertyAttribute)(infos[0]);
if (attribute.columnKeyType == ColumnKeyType.Identity)
{
return pi;
}
}
}
return null;
}
/// <summary>
/// 获取需要的读取数据源的字段集
/// </summary>
/// <param name="pis">Model模型所有属性集合</param>
/// <param name="filter"></param>
/// <param name="customColumns">自定义查询列名集合,使用逗号分隔。如不需要则为null</param>
/// <returns></returns>
public static List<string> GetTableColumns(PropertyInfo[] pis, ColumnKeyType filter, string customColumns)
{
string col = "";
return GetTableColumns(pis, filter, customColumns, ref col);
}
/// <summary>
/// 获取需要的读取数据源的字段集
/// </summary>
/// <param name="pis">Model模型所有属性集合</param>
/// <param name="filter"></param>
/// <param name="customColumns">自定义查询列名集合,使用逗号分隔。如不需要则为null</param>
/// <returns></returns>
public static List<string> GetTableColumns(PropertyInfo[] pis, ColumnKeyType filter, string customColumns, ref string outCol)
{
List<string> columns = new List<string>();
if (customColumns != null && customColumns.Length > 0)
{
/*
* 需要安全处理
* 限制字段不包含空格
*/
customColumns = customColumns.Trim();
string[] strs = customColumns.Split(',');
foreach (string str in strs)
{
if (IsRegexMatch(str, @"^(\w[^\s';]+)$"))
{
columns.Add(str);
}
}
outCol = customColumns;
}
else
{
object[] infos = null;
PropertyAttribute attribute = null;
foreach (PropertyInfo pi in pis)
{
//删除外部扩展对象项
infos = pi.GetCustomAttributes(false);
if (infos.Length > 0)
{
attribute = (PropertyAttribute)(infos[0]);
if (attribute.columnKeyType == (filter & attribute.columnKeyType))
{
continue;
}
}
outCol += string.Concat(",", pi.Name);
columns.Add(pi.Name);
}
outCol = outCol.Remove(0, 1);
}
return columns;
}
/// <summary>
/// 检查是否满足某种正则表达式
/// </summary>
private static bool IsRegexMatch(string str, string Express)
{
if (string.IsNullOrEmpty(str))
{
return false;
}
return Regex.IsMatch(str, Express);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
namespace SimpleORM
{
class Program
{
static void Main(string[] args)
{
M_People mp = new M_People();
mp.PL_Age = 26;
mp.Pl_ID = "001";
mp.Pl_LoginName = "Test1";
mp.PL_Pwd = "123";
mp.Pl_Sex = "Female";
mp.Pl_TrueName = "Mary";
int Insert_Key = Insert<M_People>(mp, false, DBReturnType.Identity);
}
#region 把对象内容保存到数据库中 Insert
/// <summary>
/// 把对象内容保存到数据库中
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="isIncludeKeyColumn">插入语句中是否包含对主键的插入,当主键值为自动增加时为false</param>
/// <param name="returnType">返回的数据类型:DBReturnType.EffectRow 为返回受影响行数;DBReturnType.IdEntity 返回最新插入主键值(isIncludeKeyColumn == false时有效)</param>
public static int Insert<T>(T model, bool isIncludeKeyColumn, DBReturnType returnType) where T : class
{
int i = 0;
Type type = typeof(T);
//获取表名
string tableName = EntityHelper.GetTableName(type);
PropertyInfo[] pis = type.GetProperties();
//获取所有字段和主键名称
List<string> columns = null;
//处理是否包含主键插入
if (isIncludeKeyColumn == false)
{
columns = EntityHelper.GetTableColumns(pis, ColumnKeyType.Identity | ColumnKeyType.Extend, null);
}
else
{
columns = EntityHelper.GetTableColumns(pis, ColumnKeyType.Extend, null);
}
//生成INSERT语句
StringBuilder sqlText = new StringBuilder();
sqlText.Append("INSERT INTO ");
sqlText.Append(tableName);
sqlText.Append(" (");
//第一个字段
sqlText.Append(columns[0]);
//第二个起所有字段
int loop = columns.Count;
for (i = 1; i < loop; i++)
{
sqlText.Append(",");
sqlText.Append(columns[i]);
}
sqlText.Append(") VALUES (");
//第一个字段
sqlText.Append("@");
sqlText.Append(columns[0]);
//第二个起所有字段
for (i = 1; i < loop; i++)
{
sqlText.Append(",@");
sqlText.Append(columns[i]);
}
sqlText.Append(");");
//生成MySqlParamter
PropertyInfo propertyInfo = null;
SqlParameter[] paras = new SqlParameter[loop];
for (i = 0; i < loop; i++)
{
propertyInfo = type.GetProperty(columns[i]);
paras[i] = new SqlParameter(columns[i], GetMySqlDbType(propertyInfo.PropertyType), -1);
paras[i].Value = propertyInfo.GetValue(model, null);
}
//根据两种情况返回不同的值
if (isIncludeKeyColumn == false && returnType == DBReturnType.Identity)
{
sqlText.Append(" SELECT @@identity AS RetId");
SqlDataReader sdr = DataReader(sqlText.ToString(), CommandType.Text, paras);
int keyId = 0;
if (sdr.Read())
{
keyId = Convert.ToInt32(sdr["RetId"]);
}
sdr.Close();
return keyId;
}
else
{
return NonQuery(sqlText.ToString(), CommandType.Text, paras);
}
}
#endregion
#region 根据Type类型获取SQL的数据类型
/// <summary>
/// 根据Type类型获取MySQL的数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private static SqlDbType GetMySqlDbType(Type type)
{
SqlDbType dbtype = SqlDbType.VarChar;
if (type.Equals(typeof(string)))
{
}
else if (type.Equals(typeof(int)))
{
dbtype = SqlDbType.Int;
}
else if (type.Equals(typeof(bool)))
{
dbtype = SqlDbType.Bit;
}
else if (type.Equals(typeof(DateTime)))
{
dbtype = SqlDbType.DateTime;
}
else if (type.Equals(typeof(decimal)))
{
dbtype = SqlDbType.Decimal;
}
else if (type.Equals(typeof(float)))
{
dbtype = SqlDbType.Float;
}
else if (type.Equals(typeof(double)))
{
dbtype = SqlDbType.Float;
}
return dbtype;
}
#endregion
/// 配置字符串参数
/// </summary>
private static void PrepareCommand(SqlConnection conn, SqlTransaction trans, SqlCommand sqlCommand, string sqlText, CommandType commandType, SqlParameter[] parms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
sqlCommand.Connection = conn;
sqlCommand.CommandText = sqlText;
sqlCommand.CommandType = commandType;
if (trans != null)
{
sqlCommand.Transaction = trans;
}
if (parms != null)
{
foreach (SqlParameter parm in parms)
{
sqlCommand.Parameters.Add(parm);
}
}
}
/// <summary>
/// 执行SQL语句,返回数据集
/// </summary>
public static SqlDataReader DataReader(string sqlText, CommandType commandType, SqlParameter[] parms)
{
SqlConnection conn = new SqlConnection(@"Data Source=HAOFUQI\SQLEXPRESS;Initial Catalog=Fukusuke;Persist Security Info=True;User ID=sa;pwd=123");
SqlCommand sqlCommand = new SqlCommand();
PrepareCommand(conn, null, sqlCommand, sqlText, commandType, parms);
SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
sqlCommand.Dispose();
return reader;
}
/// <summary>
/// 执行SQL语句,并返回影响行数
/// </summary>
public static int NonQuery(string sqlText, CommandType commandType, SqlParameter[] parms)
{
int reVal = 0;
using (SqlConnection conn = new SqlConnection(@"Data Source=HAOFUQI\SQLEXPRESS;Initial Catalog=Fukusuke;Persist Security Info=True;User ID=sa;pwd=123"))
{
SqlCommand sqlCommand = new SqlCommand();
PrepareCommand(conn, null, sqlCommand, sqlText, commandType, parms);
reVal = sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
sqlCommand.Dispose();
}
return reVal;
}
}
[Serializable]
[Flags]
public enum ColumnKeyType
{
/// <summary>
/// 默认状态
/// </summary>
Default = 1,
/// <summary>
/// 标识为主键
/// </summary>
Identity = 2,
/// <summary>
/// Extend状态下,不参与读取、增加、修改
/// </summary>
Extend = 4,
/// <summary>
/// Read状态下不参与增加、修改
/// </summary>
Read = 8
}
//返回值做了枚举:
public enum DBReturnType
{ /// <summary>
/// 返回受影响的行数
/// </summary>
EffectRow,
/// <summary>
/// 返回最后插入的主键值
/// </summary>
Identity
}
}