数据库接口基础类 oracle，sql server
2024-08-24 15:46:07
1.为数据库读取基类
public class DBBase : IDisposable
{
public virtual void Dispose()
{
throw new NotImplementedException();
} public virtual int ExecuteSQL(string sql)
{
return ;
} public virtual DataTable DBGetDataTable(string sql)
{
return null;
}
/// </summary>
/// <param name="item"></param>
/// <param name="keyname"></param>
/// <param name="keyid"></param>
/// <returns></returns>
public int ExecuteSQL_Update(object item, string key, string keyvalue)
{
if (item == null)
return -;
var type = item.GetType();
var tablename = type.Name;
var atts = type.GetProperties();
string valueset = "";
foreach (var a in atts)
{
var aname = a.Name;
var value = a.GetValue(item);
valueset += aname + "=" + GetValue2String(value) + ",";
}
valueset = valueset.Trim(','); var sql = "UPDATE " + tablename + " set " + valueset + " where " + key + "= '" + keyvalue + "'";
return ExecuteSQL(sql);
} public int ExecuteSQL_Insert(object item)
{
if (item == null)
return -;
var type = item.GetType();
var tablename = type.Name;
var atts = type.GetProperties();
var keys = "";
var values = "";
foreach (var a in atts)
{
var aname = a.Name; //插入中对对象的处理
var attdescs = a.GetCustomAttributes(typeof(System.ComponentModel.DescriptionAttribute), false);
if (attdescs.Count() != )
{
var att = attdescs[] as System.ComponentModel.DescriptionAttribute;
if (att != null)
{
if (att.Description == "Serialize")
{
var vale = a.GetValue(item,null);
DataContractJsonSerializer ser = new DataContractJsonSerializer(vale.GetType());
MemoryStream ms = new MemoryStream();
ser.WriteObject(ms, vale);
string jsonString = Encoding.UTF8.GetString(ms.ToArray());
ms.Close();
keys += aname + ",";
values += GetValue2String(jsonString) + ",";
continue;
}
if (att.Description == "Ignore")
{
continue;
}
}
}
var value = a.GetValue(item);
keys += aname + ",";
values += GetValue2String(value) + ",";
} keys = keys.Trim(',');
values = values.Trim(','); var sql = "INSERT INTO " + tablename + " ( " + keys + " ) VALUES ( " + values + " )";
return ExecuteSQL(sql);
} /// <summary>
/// 获取对象列表
/// </summary>
/// <typeparam name="T">表所对应的对象名称</typeparam>
/// <param name="sql">查询语句</param>
/// <returns>返回获取到的对象实例列表</returns>
public List<T> QueryObjectList<T>(string sql) where T : new()
{
var table = DBGetDataTable(sql);
return ConvertTableToObject<T>(table);
} public List<T> ConvertTableToObject<T>(DataTable t) where T : new()
{
if (t == null)
return null;
List<T> list = new List<T>();
foreach (DataRow row in t.Rows)
{
T obj = new T();
GetObject(t.Columns, row, obj);
if (obj != null && obj is T)
list.Add(obj);
}
return list;
} public T ConvertToObject<T>(DataRow row) where T : new()
{
object obj = new T();
if (row != null)
{
DataTable t = row.Table;
GetObject(t.Columns, row, obj);
}
if (obj != null && obj is T)
return (T)obj;
else
return default(T);
} /// <summary>
/// 获取第一条数据中的某个值
/// </summary>
/// <param name="sql"></param>
/// <param name="key"></param>
/// <returns></returns>
public string QueryString(string sql, string key)
{
var table = DBGetDataTable(sql);
if (table != null)
{
if (table.Rows.Count >= )
{
var row = table.Rows[];
if (row != null)
{
return GetValue2String(row[key]);
}
}
}
return null;
} protected void GetObject(DataColumnCollection cols, DataRow dr, Object obj)
{
Type t = obj.GetType();
var props = t.GetProperties();
foreach (var pro in props)
{
if (cols.Contains(pro.Name))
{
if (dr[pro.Name] != DBNull.Value)
{
try
{
switch (pro.PropertyType.Name)
{
case "Int32":
{
Int32 value = Convert.ToInt32(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break; case "System.Nullable`1[System.Int32]":
{
Int32 value = Convert.ToInt32(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "Nullable`1"://数据库可为空的字段处理
{
var name = pro.ToString();
if (name.Contains("System.Nullable`1[System.Int32]"))
{
var intvalue = dr[pro.Name];
if (intvalue != null)
{
Int32 value = Convert.ToInt32(intvalue);
pro.SetValue(obj, value, null);
}
}
else if (name.Contains("System.Nullable`1[System.DateTime]"))
{
var intvalue = dr[pro.Name];
if (intvalue != null)
{
var value = Convert.ToDateTime(intvalue);
pro.SetValue(obj, value, null);
}
}
}
break;
case "Double":
{
double value = Convert.ToDouble(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "Single":
{
float value = Convert.ToSingle(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "Int64":
{
Int64 value = Convert.ToInt64(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "Int16":
{
Int16 value = Convert.ToInt16(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "Decimal":
{
Decimal value = Convert.ToDecimal(dr[pro.Name]);
pro.SetValue(obj, value, null);
}
break;
case "String":
{
try
{
var oldt = dr[pro.Name].GetType();
if (oldt.Name !="String")//这里处理类型不对应的情况，默认model类型是string的时候判断，不对应就转成string，便于model定义
{
pro.SetValue(obj, dr[pro.Name].ToString(), null);
}
else
{
pro.SetValue(obj, dr[pro.Name], null);
}
}
catch
{
pro.SetValue(obj, dr[pro.Name].ToString(), null);
}
}
break;
default:
{
//程序对对象的处理，对象上有Description特性，两种情况，序列化或忽略
var attdescs = pro.GetCustomAttributes(typeof(System.ComponentModel.DescriptionAttribute), false);
if (attdescs.Count() != )
{
var att = attdescs[] as System.ComponentModel.DescriptionAttribute;
if (att != null)
{
if (att.Description == "Serialize")
{
var vale = dr[pro.Name].ToString();
var serializer = new DataContractJsonSerializer(pro.PropertyType);
var ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(vale.ToCharArray()));
var objval = serializer.ReadObject(ms);
ms.Close();
pro.SetValue(obj, objval, null);
continue;
}
if (att.Description == "Ignore")
{
continue;
}
}
} pro.SetValue(obj, dr[pro.Name], null);
}
break;
}
}
catch
{
pro.SetValue(obj, null, null);
}
finally
{
}
}
else
{
pro.SetValue(obj, null, null);
}
}
}
} protected string GetValue2String(Object obj)
{
if (obj == null)
return "null";
Type t = obj.GetType();
try
{
switch (t.Name)
{
case "String":
{
return "'" + obj.ToString() + "'";
}
case "DateTime":
{
return "to_date('" + obj.ToString() + "','YYYY-MM-DD hh24:mi:ss')";//默认获取到的日期格式，可以根据具体需求改
}
default:
{
return obj.ToString();
}
}
}
catch
{
return "";
}
}
}
2.oracle 派生
public class DBSupport : DBBase
{
//public static string conncet = System.Configuration.ConfigurationManager.AppSettings["conn"].ToString(); string conn;
public DBSupport(string connect)
{
conn = connect;
if (mQracleConnecting == null)
mQracleConnecting = new OracleConnection(connect);
if (mQracleConnecting.State != ConnectionState.Open)
mQracleConnecting.Open();
} #region DB
OracleConnection mQracleConnecting = null;
public OracleConnection QracleConnecting
{
get
{
return mQracleConnecting;
}
} public override DataTable DBGetDataTable(string sql)
{
try
{
DataTable dataSet = new DataTable();
OracleDataAdapter OraDA = new OracleDataAdapter(sql, mQracleConnecting);
OraDA.Fill(dataSet);
return dataSet;
}
catch (Exception)
{
FileSupport.Instance.Write("数据库连接异常" + conn);
return null;
} } // 执行SQL语句,返回所影响的行数
public override int ExecuteSQL(string sql)
{
int Cmd = ;
OracleCommand command = new OracleCommand(sql, QracleConnecting);
try
{
Cmd = command.ExecuteNonQuery();
}
catch(Exception ex)
{
FileSupport.Instance.Write(ex.ToString());
}
return Cmd;
} #endregion public DataSet ReturnDataSet(string sql, string DataSetName)
{
DataSet dataSet = new DataSet();
OracleDataAdapter OraDA = new OracleDataAdapter(sql, QracleConnecting);
OraDA.Fill(dataSet, DataSetName);
return dataSet;
} public override void Dispose()
{
if(mQracleConnecting.State == ConnectionState.Open)
mQracleConnecting.Close();
}
}
3.sql server 派生
public class SQLDBSupport : DBBase
{
string conn;
public SQLDBSupport(string connect)
{
if (mConnecting == null)
{
conn = connect;
mConnecting = new SqlConnection(connect);
mConnecting.Open();
}
} #region DB
SqlConnection mConnecting = null;
public SqlConnection Connecting
{
get
{
return mConnecting;
}
} public override DataTable DBGetDataTable(string sql)
{
try
{
SqlDataAdapter myda = new SqlDataAdapter(sql, Connecting); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
return dt;
}
catch (Exception ex)
{
FileSupport.Instance.Write(ex.ToString());
return null;
} } // 执行SQL语句,返回所影响的行数
public override int ExecuteSQL(string sql)
{
int Cmd = ;
var Command= Connecting.CreateCommand();
try
{
Cmd = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
FileSupport.Instance.Write(ex.ToString());
}
return Cmd;
} #endregion
public override void Dispose()
{
if (Connecting.State == ConnectionState.Open)
Connecting.Close();
}
}
最新文章
- GUI 和 GUILayout 的区别
- 微信服务号模板消息接口新增"设置行业"和"添加模板"及细节优化
- SSH配置免密码登陆
- sharebutton
- js 删除确定
- CF Amr and Pins (数学)
- Spark Streaming揭秘 Day33 checkpoint的使用
- Handler 原理分析和使用（一）
- android ListView的上部下拉刷新下部点击加载更多具体实现及拓展
- F5当刷新页面，出现“要再次显示此页，web该浏览器，你曾经有过发送消息再次提交...点击重试&quot;，如何防止此对话框解决方案的出现，
- char a[] = "ab\0123\098"; 求a的长度
- python学习笔记1——基础
- Centos7 安装 python2.7
- HTML页面中插入CSS样式的三种方法
- javaweb中的乱码问题
- leetcode insertionSortList 对链表进行插入排序
- Java实现选择排序以及冒泡排序
- 启动HDFS时datanode无法启动的坑
- springMVC中如何访问WebContent中的资源文件
- java单机操作redis3.2.10和集群操作增删改查