通过泛型插入(更新)实体数据
来源:互联网 发布:wiley数据库介绍 编辑:程序博客网 时间:2024/06/06 03:21
/// <summary>
/// 通过泛型插入数据
/// </summary>
/// <typeparam name="T">类名称</typeparam>
/// <param name="obj">类对象,如果要插入空值,请使用@NULL</param>
/// <returns>插入的新记录ID</returns>
public static int Insert<T>(T obj)
{
StringBuilder strSQL = new StringBuilder();
strSQL = GetInsertSQL(obj);
// 插入到数据库中
object result = SQLPlus.ExecuteScalar(CommandType.Text, strSQL, null);
return Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
}
/// <summary>
/// 通过泛型更新数据
/// </summary>
/// <typeparam name="T">类名称</typeparam>
/// <param name="obj">类对象,如果要更新空值,请使用@NULL</param>
/// <returns>更新结果,大于0为更新成功</returns>
public static int Update<T>(T obj)
{
StringBuilder strSQL = new StringBuilder();
strSQL = GetUpdateSQL(obj);
if (String.IsNullOrEmpty(strSQL.ToString()))
{
return 0;
}
// 更新到数据库中
object result = SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, null);
int returnValue = Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
return returnValue;
}
/// <summary>
/// 获取实体的插入语句
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="obj">实体对象</param>
/// <returns>返回插入语句</returns>
public static StringBuilder GetInsertSQL<T>(T obj)
{
string tableKey = GetPropertyValue(obj, BaseSet.PrimaryKey);
string keyValue = GetPropertyValue(obj, tableKey);
string tableName = GetPropertyValue(obj, BaseSet.TableName);
Type t = obj.GetType();//获得该类的Type
StringBuilder strSQL = new StringBuilder();
strSQL.Append("insert into " + tableName + "(");
string fields = "";
string values = "";
//再用Type.GetProperties获得PropertyInfo[]
foreach (PropertyInfo pi in t.GetProperties())
{
object name = pi.Name;//用pi.GetValue获得值
// 替换Sql注入符
string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
//string dataType = pi.PropertyType.ToString().ToLower();
string properName = name.ToString().ToLower();
if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
{
// 判断是否为空
if (value1 == BaseSet.NULL)
{
value1 = "";
}
fields += Convert.ToString(name) + ",";
values += "'" + value1 + "',";
}
}
// 去掉最后一个,
fields = fields.TrimEnd(',');
values = values.TrimEnd(',');
// 拼接Sql串
strSQL.Append(fields);
strSQL.Append(") values (");
strSQL.Append(values);
strSQL.Append(")");
strSQL.Append(";SELECT @@IDENTITY;");
return strSQL;
}
/// <summary>
/// 获取实体的更新SQL串
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="obj">实体对象</param>
/// <returns>返回插入语句</returns>
private static StringBuilder GetUpdateSQL<T>(T obj)
{
string tableKey = GetPropertyValue(obj, BaseSet.PrimaryKey);
string keyValue = GetPropertyValue(obj, tableKey);
string tableName = GetPropertyValue(obj, BaseSet.TableName);
StringBuilder strSQL = new StringBuilder();
if (string.IsNullOrEmpty(keyValue))
{
return strSQL;
}
Type t = obj.GetType();//获得该类的Type
strSQL.Append("update " + tableName + " set ");
string subSQL = "";
string condition = " where " + tableKey + "='" + keyValue.Replace("'", "''") + "'";
//再用Type.GetProperties获得PropertyInfo[]
foreach (PropertyInfo pi in t.GetProperties())
{
object name = pi.Name;//用pi.GetValue获得值
// 替换Sql注入符
string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
//string dataType = pi.PropertyType.ToString().ToLower();
string properName = name.ToString().ToLower();
if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
{
// 判断是否为空
if (value1 == BaseSet.NULL)
{
value1 = "";
}
subSQL += Convert.ToString(name) + "='" + value1 + "',";
}
}
// 去掉最后一个,
subSQL = subSQL.TrimEnd(',');
// 拼接上更新子句
strSQL.Append(subSQL);
// 加入更新条件
strSQL.Append(condition);
return strSQL;
}
public class BaseSet
{
public static string NULL
{
get { return "@null"; }
}
public static string DateTimeShortNull
{
get { return "0001-1-1 0:00:00"; }
}
public static string DateTimeLongNull
{
get { return "0001-01-01 00:00:00"; }
}
public static string PrimaryKey
{
get { return "PrimaryKey"; }
}
public static string TableName
{
get { return "TableName"; }
}
}
#region 实体样例
[Serializable]
public class SortsInfo
{
private int _SortID;
private string _SortName;
public string TableName
{
get { return "Sorts"; }
}
public string PrimaryKey
{
get { return "SortID"; }
}
public int SortID
{
get { return _SortID; }
set
{
_SortID = value;
}
}
public string SortName
{
get { return _SortName; }
set
{
_SortName = value;
}
}
}
#endregion
/// 通过泛型插入数据
/// </summary>
/// <typeparam name="T">类名称</typeparam>
/// <param name="obj">类对象,如果要插入空值,请使用@NULL</param>
/// <returns>插入的新记录ID</returns>
public static int Insert<T>(T obj)
{
StringBuilder strSQL = new StringBuilder();
strSQL = GetInsertSQL(obj);
// 插入到数据库中
object result = SQLPlus.ExecuteScalar(CommandType.Text, strSQL, null);
return Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
}
/// <summary>
/// 通过泛型更新数据
/// </summary>
/// <typeparam name="T">类名称</typeparam>
/// <param name="obj">类对象,如果要更新空值,请使用@NULL</param>
/// <returns>更新结果,大于0为更新成功</returns>
public static int Update<T>(T obj)
{
StringBuilder strSQL = new StringBuilder();
strSQL = GetUpdateSQL(obj);
if (String.IsNullOrEmpty(strSQL.ToString()))
{
return 0;
}
// 更新到数据库中
object result = SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, null);
int returnValue = Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);
return returnValue;
}
/// <summary>
/// 获取实体的插入语句
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="obj">实体对象</param>
/// <returns>返回插入语句</returns>
public static StringBuilder GetInsertSQL<T>(T obj)
{
string tableKey = GetPropertyValue(obj, BaseSet.PrimaryKey);
string keyValue = GetPropertyValue(obj, tableKey);
string tableName = GetPropertyValue(obj, BaseSet.TableName);
Type t = obj.GetType();//获得该类的Type
StringBuilder strSQL = new StringBuilder();
strSQL.Append("insert into " + tableName + "(");
string fields = "";
string values = "";
//再用Type.GetProperties获得PropertyInfo[]
foreach (PropertyInfo pi in t.GetProperties())
{
object name = pi.Name;//用pi.GetValue获得值
// 替换Sql注入符
string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
//string dataType = pi.PropertyType.ToString().ToLower();
string properName = name.ToString().ToLower();
if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
{
// 判断是否为空
if (value1 == BaseSet.NULL)
{
value1 = "";
}
fields += Convert.ToString(name) + ",";
values += "'" + value1 + "',";
}
}
// 去掉最后一个,
fields = fields.TrimEnd(',');
values = values.TrimEnd(',');
// 拼接Sql串
strSQL.Append(fields);
strSQL.Append(") values (");
strSQL.Append(values);
strSQL.Append(")");
strSQL.Append(";SELECT @@IDENTITY;");
return strSQL;
}
/// <summary>
/// 获取实体的更新SQL串
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="obj">实体对象</param>
/// <returns>返回插入语句</returns>
private static StringBuilder GetUpdateSQL<T>(T obj)
{
string tableKey = GetPropertyValue(obj, BaseSet.PrimaryKey);
string keyValue = GetPropertyValue(obj, tableKey);
string tableName = GetPropertyValue(obj, BaseSet.TableName);
StringBuilder strSQL = new StringBuilder();
if (string.IsNullOrEmpty(keyValue))
{
return strSQL;
}
Type t = obj.GetType();//获得该类的Type
strSQL.Append("update " + tableName + " set ");
string subSQL = "";
string condition = " where " + tableKey + "='" + keyValue.Replace("'", "''") + "'";
//再用Type.GetProperties获得PropertyInfo[]
foreach (PropertyInfo pi in t.GetProperties())
{
object name = pi.Name;//用pi.GetValue获得值
// 替换Sql注入符
string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
//string dataType = pi.PropertyType.ToString().ToLower();
string properName = name.ToString().ToLower();
if (!string.IsNullOrEmpty(value1) && properName != tableKey.ToLower() && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
{
// 判断是否为空
if (value1 == BaseSet.NULL)
{
value1 = "";
}
subSQL += Convert.ToString(name) + "='" + value1 + "',";
}
}
// 去掉最后一个,
subSQL = subSQL.TrimEnd(',');
// 拼接上更新子句
strSQL.Append(subSQL);
// 加入更新条件
strSQL.Append(condition);
return strSQL;
}
public class BaseSet
{
public static string NULL
{
get { return "@null"; }
}
public static string DateTimeShortNull
{
get { return "0001-1-1 0:00:00"; }
}
public static string DateTimeLongNull
{
get { return "0001-01-01 00:00:00"; }
}
public static string PrimaryKey
{
get { return "PrimaryKey"; }
}
public static string TableName
{
get { return "TableName"; }
}
}
#region 实体样例
[Serializable]
public class SortsInfo
{
private int _SortID;
private string _SortName;
public string TableName
{
get { return "Sorts"; }
}
public string PrimaryKey
{
get { return "SortID"; }
}
public int SortID
{
get { return _SortID; }
set
{
_SortID = value;
}
}
public string SortName
{
get { return _SortName; }
set
{
_SortName = value;
}
}
}
#endregion
- 通过泛型插入(更新)实体数据
- 通过泛型插入(更新)实体数据
- 插入更新删除数据
- ORACLE 更新插入数据
- 插入、更新、删除数据
- 插入、更新、删除数据
- 通过DataSet更新、插入数据库
- 插入数据与更新数据
- 插入数据时,实体类的克隆
- 通过DataTable更新数据
- 绕过Hibernate API,直接通过JDBC API批量更新和插入数据
- mysql通过一条sql完成没有则更新,有则插入新数据
- EF实体中的数据修改更新
- android通过剪切板传递实体类数据
- c#数据删除,插入,更新
- drupal插入/更新数据db_merge()
- 插入、更新以及删除数据
- 数据的插入、更新、删除
- madCodeHook研究日记
- 用css3和jquery实现的渐变的动态进度条
- 高通下注中国市场:放下身段进军低端市场,国内的山寨机性能是不是又要提高了
- C语言中volatile关键字的作用
- gcc常用编译参数
- 通过泛型插入(更新)实体数据
- 用perl实现http服务-用来传文件
- Fedora nfs重启命令
- sqlplus 中执行sql 遇到特殊字符&
- Linux下字符串转时间tm结构函数strptime
- [转]TCP状态转移图学习总结
- Android有效解决加载大图片内存溢出问题及优化虚拟机内存
- 从JM8.6解码器中学习多级指针的应用
- Linux 静态库&动态库调用