通过泛型插入(更新)实体数据
来源:互联网 发布:端口阻塞黄灯 编辑:程序博客网 时间:2024/06/03 13:18
经过长时间的。net开发,发现每次操作数据这块,很繁琐,要么是直接拼接sql语句,要么是利用实体类,但是还是需要拼接sql语句,如何找到一个能减少拼接sql语句的并且还能用到实体类数据的方法呢,我在一直摸索。于是得出了一下方法:、
现举例示范代码如下,当然我觉得还可以改进(比如重复的地方还可以写成公共方法进行调用)。
-
-
-
-
-
-
- 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);
- }
-
-
-
-
-
-
-
- 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;
-
- }
-
-
-
-
-
-
-
- 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();
-
- StringBuilder strSQL = new StringBuilder();
-
- strSQL.Append("insert into " + tableName + "(");
-
- string fields = "";
- string values = "";
-
-
- foreach (PropertyInfo pi in t.GetProperties())
- {
-
- object name = pi.Name;
-
-
- string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
-
-
-
- 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(',');
-
-
- strSQL.Append(fields);
- strSQL.Append(") values (");
- strSQL.Append(values);
- strSQL.Append(")");
-
- strSQL.Append(";SELECT @@IDENTITY;");
-
- return strSQL;
- }
-
-
-
-
-
-
-
- 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();
-
- strSQL.Append("update " + tableName + " set ");
-
- string subSQL = "";
-
- string condition = " where " + tableKey + "='" + keyValue.Replace("'", "''") + "'";
-
-
-
- foreach (PropertyInfo pi in t.GetProperties())
- {
-
- object name = pi.Name;
-
-
- string value1 = Convert.ToString(pi.GetValue(obj, null)).Replace("'", "''");
-
-
-
- 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