自动生成sql

添加下面这个类

public static class GetAllAttribute<T> where T : class
{
public static string Names;
public static string Values;
public static ArrayList array;
/// <summary>
/// 获取当前对象的所有属性名称,以逗号隔开
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static string GetNames(T entity)
{
PropertyInfo[] p = entity.GetType().GetProperties();
string name = string.Empty;
foreach (PropertyInfo item in p)
{
name += item.Name + ",";
}
name = name.Substring(0, name.Length - 1);
return name;
}
/// <summary>
/// 获取当前对象的所有属性值,以逗号隔开
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static string GetValues(T entity)
{
PropertyInfo[] p = entity.GetType().GetProperties();
string value = string.Empty;
foreach (PropertyInfo item in p)
{
if (item.PropertyType.Name.Equals("String"))
{
value += "'" + item.GetValue(entity, null) + "',";
}
if (item.PropertyType.Name.Equals("Int32"))
{
value += item.GetValue(entity, null) + ",";
}
if (item.PropertyType.Name.Equals("DateTime"))
{
value += "to_Date('" + item.GetValue(entity, null) + "','yyyy-MM-dd HH:mm:ss'),";
}
}
value = value.Substring(0, value.Length - 1);
return value;
}
/// <summary>
/// 根据条件设置字段名称和字段对应的值
/// </summary>
/// <param name="entity"></param>
/// <param name="names"></param>
public static void SetNamesAndValues(T entity, string names)
{
Names = string.Empty;
Values = string.Empty;

string[] _Names = names.Split(',');
PropertyInfo[] p = entity.GetType().GetProperties();
foreach (PropertyInfo item in p)
{
bool flg1 = false;
for (int i = 0; i < _Names.Length; i++)
{
string str_Name = _Names[i];
bool flg = item.Name.Equals(str_Name);
if (flg)
{
flg1 = true;
}
}
if (!flg1)
{
Names += item.Name + ",";
Values += item.GetValue(entity, null) + ",";
}
}
Names = Names.Substring(0, Names.Length - 1);
Values = Values.Substring(0, Values.Length - 1);
}
/// <summary>
/// 根据条件设置字段名称和字段对应的值
/// </summary>
/// <param name="entity"></param>
/// <param name="names"></param>
public static void SetNamesAndValues1(T entity, string names)
{
Names = string.Empty;
Values = string.Empty;

string[] _Names = names.Split(',');
PropertyInfo[] p = entity.GetType().GetProperties();
foreach (PropertyInfo item in p)
{
bool flg1 = false;
for (int i = 0; i < _Names.Length; i++)
{
string str_Name = _Names[i];
bool flg = item.Name.Equals(str_Name);
if (flg)
{
flg1 = true;
}
}
if (flg1)
{
Names += item.Name + ",";
Values += item.GetValue(entity, null) + ",";
}
}
Names = Names.Substring(0, Names.Length - 1);
Values = Values.Substring(0, Values.Length - 1);
}

#region 获取Insert语句
/// <summary>
/// 获取Insert语句
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static string Insert(T entity)
{
try
{
array = new ArrayList();
Type type = entity.GetType();
PropertyInfo[] p = type.GetProperties();
//表名
string tableName = "";
foreach (Object attr in type.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断是否有属性修饰，并判断属性值
if (attr is TableAttribute)
{
TableAttribute currAttribute = attr as TableAttribute;
if (!string.IsNullOrEmpty(currAttribute.TableName))
{
tableName = currAttribute.TableName;
}
}
}
//string strSql = "Insert into " + tableName + "(";
string strSql = "Insert into {0}({1}) values({2})";

//字段名称列表,以逗号隔开
string Files = "";
//字段名称对应的值的列表,以逗号隔开
string Values = "";

foreach (PropertyInfo item in p)
{
OracleParameter par = null;

//当前字段值
string value = item.GetValue(entity, null) + "";
string name = item.Name;
//当前值的类型(String,Int32,Decimal,Double,DateTime)
string Pname = item.PropertyType.Name;

foreach (Object attr in item.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断当前字段是否是数据库字段
if (attr is FieldAttribute)
{
FieldAttribute fileld = attr as FieldAttribute;

if (!string.IsNullOrEmpty(fileld.Field))
{
name = fileld.Field;
}
}
// 判断当前字段是否是主键
else if (attr is PrimaryKeyAttribute)
{
PrimaryKeyAttribute currAttribute = attr as PrimaryKeyAttribute;
if (!string.IsNullOrEmpty(currAttribute.Id))
{
name = currAttribute.Id;
}
}
Files += name + ",";
Values += ":" + name + ",";

if (Pname.Equals("String"))
{
par = new OracleParameter(name, OracleDbType.Varchar2);
par.Value = value;
array.Add(par);
}
else if (Pname.Equals("Int32"))
{
par = new OracleParameter(name, OracleDbType.Int32);
int temp = Convert.ToInt32(value);
if (temp <= -1)
{
par.Value = DBNull.Value;
}
else
{
par.Value = temp;
}
array.Add(par);
}
else if (Pname.Equals("Decimal"))
{
par = new OracleParameter(name, OracleDbType.Decimal);
decimal temp = Convert.ToDecimal(value);
if (temp <= -1)
{
par.Value = DBNull.Value;
}
else
{
par.Value = temp;
}
array.Add(par);
}
else if (Pname.Equals("Double"))
{
par = new OracleParameter(name, OracleDbType.Double);
par.Value = Convert.ToInt32(value);
array.Add(par);
}
else if (Pname.Equals("DateTime"))
{
par = new OracleParameter(name, OracleDbType.Date);
string obj = value;
if (!obj.Equals("0001/1/1 0:00:00"))
{
par.Value = Convert.ToDateTime(value);
}
else
{
par.Value = DBNull.Value;
}
array.Add(par);
}
}
#region
//Files += item.Name + ",";
//Values += ":" + item.Name + ",";

//if (item.PropertyType.Name.Equals("String"))
//{
// par = new OracleParameter(item.Name, OracleDbType.Varchar2);
// par.Value = item.GetValue(entity, null) + "";
// array.Add(par);
//}
//else if (item.PropertyType.Name.Equals("Int32"))
//{
// par = new OracleParameter(item.Name, OracleDbType.Int32);
// int temp = Convert.ToInt32(item.GetValue(entity, null));
// if (temp <= -1)
// {
// par.Value = DBNull.Value;
// }
// else
// {
// par.Value = temp;
// }
// array.Add(par);
//}
//else if (item.PropertyType.Name.Equals("Decimal"))
//{
// par = new OracleParameter(item.Name, OracleDbType.Decimal);
// par.Value = Convert.ToDecimal(item.GetValue(entity, null));
// array.Add(par);
//}
//else if (item.PropertyType.Name.Equals("Double"))
//{
// par = new OracleParameter(item.Name, OracleDbType.Double);
// par.Value = Convert.ToInt32(item.GetValue(entity, null));
// array.Add(par);
//}
//else if (item.PropertyType.Name.Equals("DateTime"))
//{
// par = new OracleParameter(item.Name, OracleDbType.Date);
// string obj = item.GetValue(entity, null).ToString();
// if (!obj.Equals("0001/1/1 0:00:00"))
// {
// par.Value = Convert.ToDateTime(item.GetValue(entity, null));
// }
// else
// {
// par.Value = DBNull.Value;
// }
// array.Add(par);
//}
#endregion
}
if (!String.IsNullOrEmpty(Files))
{
Files = Files.Substring(0, Files.Length - 1);
}
if (!String.IsNullOrEmpty(Values))
{
Values = Values.Substring(0, Values.Length - 1);
}

//strSql += Files + ") values(" + Values + ")";

strSql = string.Format(strSql, tableName, Files, Values);

return strSql;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion

#region 获取根据主键查询总记录数的语句
/// <summary>
/// 获取根据主键查询总记录数的语句
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static string GetCountById(T entity)
{
try
{
array = new ArrayList();
Type type = entity.GetType();
PropertyInfo[] p = type.GetProperties();

//表名
string tableName = type.Name;
foreach (Object attr in type.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断是否有属性修饰，并判断属性值
if (attr is TableAttribute)
{
TableAttribute currAttribute = attr as TableAttribute;
if (!string.IsNullOrEmpty(currAttribute.TableName))
{
tableName = currAttribute.TableName;
}
}
}
//sql语句
string strSql = "select count(*) from " + tableName + " where ";

//主键(用来做update的where条件)
OracleParameter parId = null;

foreach (PropertyInfo item in p)
{
//当前字段值
string value = item.GetValue(entity, null) + "";
string name = item.Name;

foreach (Object attr in item.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断当前字段是否是主键
if (attr is PrimaryKeyAttribute)
{
PrimaryKeyAttribute currAttribute = attr as PrimaryKeyAttribute;
if (!string.IsNullOrEmpty(currAttribute.Id))
{
name = currAttribute.Id;
}
if (item.PropertyType.Name.Equals("String"))
{
strSql += name + "='" + value + "'";
}
else
{
strSql += name + "=" + value;
}

if (item.PropertyType.Name.Equals("Int32"))
{
parId = new OracleParameter(name, OracleDbType.Int32);
parId.Value = Convert.ToInt32(value);
}
else if (item.PropertyType.Name.Equals("String"))
{
parId = new OracleParameter(name, OracleDbType.Varchar2);
parId.Value = value;
}
}
}
//---------------------end---------------------
}

if (parId != null)
{
array.Add(parId);
return strSql;
}
else
{
return "";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion

#region 获取Update语句
/// <summary>
/// 获取Update语句
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static string Update(T entity)
{
try
{
array = new ArrayList();
Type type = entity.GetType();
PropertyInfo[] p = type.GetProperties();

//表名
string tableName = type.Name;
foreach (Object attr in type.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断是否有属性修饰，并判断属性值
if (attr is TableAttribute)
{
TableAttribute currAttribute = attr as TableAttribute;
if (!string.IsNullOrEmpty(currAttribute.TableName))
{
tableName = currAttribute.TableName;
}
}
}
//sql语句
string strSql = "Update " + tableName + " set ";

//字段名称列表,以逗号隔开
string Files = "";

string sqlWhere = "";
//主键(用来做update的where条件)
OracleParameter parId = null;

foreach (PropertyInfo item in p)
{
//------------update后面的where条件------------
OracleParameter par = null;
//当前字段值
string value = item.GetValue(entity, null) + "";
string name = item.Name;

foreach (Object attr in item.GetCustomAttributes(false))// 遍历该属性的所有注解
{
// 判断当前字段是否是数据库字段
if (attr is FieldAttribute)
{
FieldAttribute fileld = attr as FieldAttribute;

if (!string.IsNullOrEmpty(fileld.Field))
{
name = fileld.Field;
}
Files += name + "=:" + name + ",";

if (item.PropertyType.Name.Equals("String"))
{
par = new OracleParameter(name, OracleDbType.Varchar2);
par.Value = value;
array.Add(par);
}
else if (item.PropertyType.Name.Equals("Int32"))
{
par = new OracleParameter(name, OracleDbType.Int32);
int temp = Convert.ToInt32(value);
if (temp <= -1)
{
par.Value = DBNull.Value;
}
else
{
par.Value = temp;
}
array.Add(par);
}
else if (item.PropertyType.Name.Equals("Decimal"))
{
par = new OracleParameter(name, OracleDbType.Decimal);
decimal temp = Convert.ToDecimal(value);
if (temp <= -1)
{
par.Value = DBNull.Value;
}
else
{
par.Value = temp;
}
array.Add(par);
}
else if (item.PropertyType.Name.Equals("Double"))
{
par = new OracleParameter(name, OracleDbType.Double);
par.Value = Convert.ToInt32(value);
array.Add(par);
}
else if (item.PropertyType.Name.Equals("DateTime"))
{
par = new OracleParameter(name, OracleDbType.Date);
string obj = value;
if (!obj.Equals("0001/1/1 0:00:00"))
{
par.Value = Convert.ToDateTime(value);
}
else
{
par.Value = DBNull.Value;
}
array.Add(par);
}
}
// 判断当前字段是否是主键
else if (attr is PrimaryKeyAttribute)
{
PrimaryKeyAttribute currAttribute = attr as PrimaryKeyAttribute;
if (!string.IsNullOrEmpty(currAttribute.Id))
{
name = currAttribute.Id;
}
sqlWhere += " Where " + name + "=:" + name;

if (item.PropertyType.Name.Equals("Int32"))
{
parId = new OracleParameter(name, OracleDbType.Int32);
parId.Value = Convert.ToInt32(value);
}
else if (item.PropertyType.Name.Equals("Decimal"))
{
parId = new OracleParameter(name, OracleDbType.Decimal);
parId.Value = Convert.ToDecimal(value);
}
else if (item.PropertyType.Name.Equals("String"))
{
parId = new OracleParameter(name, OracleDbType.Varchar2);
parId.Value = value;
}
}
}
//---------------------end---------------------
}
if (!String.IsNullOrEmpty(Files))
{
Files = Files.Substring(0, Files.Length - 1);
}

strSql += Files + sqlWhere;

if (parId != null)
{
array.Add(parId);
return strSql;
}
else
{
return "";
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
}

 

使用：