用C#实现通用的DAL Insert

来源：互联网发布：java idea maven 打包编辑：程序博客网时间：2024/05/16 06:38

写了几年的三层架，突发奇想，如果能写一套通用的Insert，Update 语句，不用每次都写存储过程，SQL，那是不是少了很多工作量。然后就试着写了个

首先定义一个实体类

public class Industry
{
private int id;
[AllTargetsAttribute(IsKey = true)]
public int Id
{
get { return id; }
set { id = value; }
}
private string name;

public string Name
{
get { return name; }
set { name = value; }
}
private int? pid;

public int? Pid
{
get { return pid; }
set { pid = value; }
}
}

AllTargetsAttribute是我自定义的一个标记，目的是为了标记实体映射表的主键列，true表示这个字段为主键 AllTargetsAttribute代码如下

// 该Attribute对所有的元素有效.
[AttributeUsage(AttributeTargets.All)]
public class AllTargetsAttribute : Attribute
{
private bool isKey = false;
public bool IsKey
{
get { return isKey; }
set { isKey = value; }
}
}

开始写DAL方法，首先我定义一个DbHelper抽象类，为了可扩展

public abstract class DbHelper
{
public abstract int AddModel<T>(T t);
}

定义了一个泛型的抽象添加方法AddModel，然后定义了一个SqlHelper类，继承DbHelper，实现AddModel方法，代码如下

public class SqlHelper:DbHelper
{
public override int AddModel<T>(T t)
{
return new SqlDb().ExecuteNonQuery(CommandType.Text, GetSqlInsert<T>(t), null);
}
public string GetSqlInsert<T>(T t)
{
Type type = t.GetType();
PropertyInfo[] properties = type.GetProperties();
string str = "Insert into " + type.Name + " ( ";
foreach (var proper in properties)
{
if (IsKey(proper))
{
continue;
}
else
{
str += proper.Name + ",";
}
}
str = str.Substring(0, str.LastIndexOf(","));
str += " ) values ( ";
foreach (var proper in properties)
{
if (IsKey(proper))
{
continue;
}
else
{
object val = proper.GetValue(t, null);
if (val is int || val is float || val is decimal|| val is double)
{
str += proper.GetValue(t, null) + ",";
}
else
{
if (val == null)
{
str +="null,";
}
else
{
str += "'" + proper.GetValue(t, null) + "'" + ",";
}

}
}
}
str = str.Substring(0, str.LastIndexOf(","));
str += " )";
return str;
}
/// <summary>
///判断是否是主键
/// </summary>
/// <param name="proper"></param>
/// <returns></returns>
private bool IsKey(PropertyInfo proper)
{
object[] objs = proper.GetCustomAttributes(typeof(AllTargetsAttribute), true);
bool f = false;
foreach (var obj in objs)
{
if (obj is AllTargetsAttribute)
{
AllTargetsAttribute alltarget = (AllTargetsAttribute)obj;
if (alltarget.IsKey)
{
f = true;
break;
}
}
}
return f;
}
}

GetSqlInsert方法生成了一个insert 语句，反射t属性，生成T-Sql，然后 new SqlDb().执行这个语句，SqlDb实际是微软官方的SqlHelper，网上很多，我就抄了一部分，如下

public class SqlDb
{
string connectionString = "server=.;uid=sa;pwd=sa;database=TestDb";
public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
}
private void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText,
SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//为命令对象设置连接
cmd.Connection = conn;
//为命令对象设置命令文本
cmd.CommandText = cmdText;
//如果存在事务操作
if (trans != null)
{
//为命令对象设置事务
cmd.Transaction = trans;
}
//为命令对象设置命令类型
cmd.CommandType = cmdType;
//如果参数集不为空
if (cmdParms != null)
{
//循环填充所有的命令参数至命令对象
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
}

Ok这样SqlHelper差不多就实现了

现在我在程序中调用

Industry model = new Industry();
model.Id = 1;
model.Name = "自定义行业";
for (int i = 0; i < 10; i++)
{
model.Name = model.Name + i;
new SqlHelper().AddModel<Industry>(model);
}

查看数据库,插入成功

0 0