/// <summary> /// 返回查询结果中第一行第一列 /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="paraName"></param> /// <param name="paraValue"></param> /// <param name="type"></param> /// <param name="adapter"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static object ExcuteScalar(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, FluDataAdapter adapter = null, string dbString = null) { if (adapter == null) { using (FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString)) { da.OpenConnection(); object obj = da.SelectCommand.ExecuteScalar(); da.CloseConnection(); return(obj); } } adapter.ClearParameter(); adapter.CommandText = commandText; adapter.CommandType = cmdType; if (paraName != null && paraValue != null && paraName.Length == paraValue.Length) { for (int i = 0; i < paraName.Length; i++) { adapter.AddParameter(paraName[i], paraValue[i]); } } object result2 = adapter.SelectCommand.ExecuteScalar(); return(result2); }
/// <summary> /// 含事务处理DataAdapter /// </summary> /// <param name="action">DataApapter, Transaction需要手动commit or rollback</param> /// <param name="dbString">数据库名称</param> /// <returns></returns> public static bool ExcuteTransaction(Action <FluDataAdapter, DbTransaction> action, string dbString = null) { using (FluDataAdapter da = GetAdapter(dbString: dbString)) { da.OpenConnection(); using (DbTransaction ts = da.Connection.BeginTransaction()) { try { da.SelectCommand.Transaction = ts; action.Invoke(da, ts); return(true); } catch (Exception ex) { ts.Rollback(); throw ex; } finally { da.CloseConnection(); } } } }
/// <summary> /// 含事务处理DataAdapter /// </summary> /// <param name="action">一系列的数据库更新语句</param> /// <param name="dbString">数据库名称</param> public static bool ExcuteTransaction(Action <FluDataAdapter> action, string dbString = null) { using (FluDataAdapter da = GetAdapter(dbString: dbString)) { da.OpenConnection(); using (DbTransaction ts = da.Connection.BeginTransaction()) { try { da.SelectCommand.Transaction = ts; action.Invoke(da); ts.Commit(); return(true); } catch (Exception ex) { ts.Rollback(); //Logging4net.WriteError(ex, "事务执行出错:"+ da.SelectCommand.CommandText); throw ex; } finally { da.CloseConnection(); } } } }
/// <summary> /// 返回查询结果中第一行第一列 /// </summary> /// <param name="commandText"></param> /// <param name="parameters"></param> /// <param name="cmdType"></param> /// <param name="type"></param> /// <param name="adapter"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static object ExcuteScalar3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.OnlyRead, FluDataAdapter adapter = null, string dbString = null) { if (adapter == null) { using (FluDataAdapter da = GetAdapter3(commandText, parameters, cmdType, type, dbString)) { da.OpenConnection(); object obj = da.SelectCommand.ExecuteScalar(); da.CloseConnection(); return(obj); } } adapter.ClearParameter(); adapter.CommandText = commandText; adapter.CommandType = cmdType; if (parameters != null) { foreach (var p in parameters) { adapter.AddParameter(p); } } object result2 = adapter.SelectCommand.ExecuteScalar(); return(result2); }
object GetInsertId(DbProviderType type, FluDataAdapter adapter = null, string tableName = null, string seqName = null) { switch (type) { case DbProviderType.SqlServer: { string idSql = ";SELECT SCOPE_IDENTITY();"; return(idSql); //return DataHelper.ExcuteScalar(idSql, type: DbConntionType.WriteRead, adapter: adapter); } case DbProviderType.MySql: { string idSql = string.Format(";SELECT LAST_INSERT_ID() AS ID FROM {0} LIMIT 1;", tableName); return(idSql); //return DataHelper.ExcuteScalar(idSql, type: DbConntionType.WriteRead, adapter: adapter); } default: { string seq = string.Format("SELECT {0}.NEXTVAL FROM DUAL", seqName); return(DataHelper.ExcuteScalar(seq, type: DbConntionType.WriteRead, adapter: adapter, dbString: DBString)); } } }
/// <summary> /// 删除一系列对象 /// </summary> /// <param name="expression">where表达式</param> /// <param name="adapter"></param> /// <returns></returns> public int Delete(Expression <Func <V, bool> > expression, FluDataAdapter adapter = null) { PCDictionary pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); var command = GetPropertiesDelete(expression); return(DataHelper.ExcuteNonQuery(command.Key, CommandType.Text, command.Value.Key, command.Value.Value, adapter, DBString)); }
/// <summary> /// 返回DataReader,must using dispose /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="paraName"></param> /// <param name="paraValue"></param> /// <param name="type"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static IDataReader ExecuteReader(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, string dbString = null) { FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString); da.OpenConnection(); IDataReader dr = da.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection); return(dr); }
/// <summary> /// 取得分页数据 /// </summary> /// <param name="page"></param> /// <param name="type"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static DataTable GetPagingData(PagingInfo page, DbConntionType type = DbConntionType.OnlyRead, string dbString = null) { using (FluDataAdapter da = GetAdapter(type: type, dbString: dbString)) { da.OpenConnection(); DataTable dt = da.GetPagingData2(page); da.CloseConnection(); return(dt); } }
/// <summary> /// 取得查询数据 /// </summary> /// <param name="tableName"></param> /// <param name="columns"></param> /// <param name="conditions"></param> /// <param name="orderby"></param> /// <param name="groupby"></param> /// <param name="type"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static DataTable GetData(string tableName, string columns, string conditions, string orderby, string groupby, DbConntionType type = DbConntionType.OnlyRead, string dbString = null) { using (FluDataAdapter da = GetAdapter(type: type, dbString: dbString)) { da.OpenConnection(); DataTable dt = da.GetData2(tableName, columns, conditions, orderby, groupby); da.CloseConnection(); return(dt); } }
/// <summary> /// 返回DataTable /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="paraName"></param> /// <param name="paraValue"></param> /// <param name="type"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static DataTable Fill(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.OnlyRead, string dbString = null) { using (FluDataAdapter da = GetAdapter(commandText, cmdType, paraName, paraValue, type, dbString)) { da.OpenConnection(); DataTable dt = new DataTable(); da.Fill(dt); da.CloseConnection(); return(dt); } }
/// <summary> /// 返回DataTable /// </summary> /// <param name="commandText"></param> /// <param name="parameters">参数对象列表</param> /// <param name="cmdType"></param> /// <param name="type"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static DataTable Fill3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.OnlyRead, string dbString = null) { using (FluDataAdapter da = GetAdapter3(commandText, parameters, cmdType, type, dbString)) { da.OpenConnection(); DataTable dt = new DataTable(); da.Fill(dt); da.CloseConnection(); return(dt); } }
/// <summary> /// 删除对象 /// </summary> /// <param name="id"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public int Delete(object id, FluDataAdapter adapter = null) { PCDictionary pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); string sql = string.Format("DELETE FROM {0} WHERE {1} = {2}p__{1}", pc.TableName, pc.IdName, providerEx.ParameterType); string[] param = new string[] { providerEx.ParameterType + "p__" + pc.IdName }; object[] pv = new object[] { id }; return(DataHelper.ExcuteNonQuery(sql, CommandType.Text, param, pv, adapter, DBString)); }
/// <summary> /// 根据ID更新对象所有字段 /// </summary> /// <param name="model"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public int Update(V model, FluDataAdapter adapter = null) { var pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); D obj = Helper.Copy <D>(model); var command = GetPropertiesUpdate(obj); var pList = GetOraParameters(command); if (pList == null) { return(DataHelper.ExcuteNonQuery(command.Key, CommandType.Text, command.Value.Key, command.Value.Value, adapter, DBString)); } return(DataHelper.ExcuteNonQuery3(command.Key, pList, adapter: adapter, dbString: DBString)); }
/// <summary> /// 获取数据操作适配器,须执行dispose /// </summary> /// <param name="commandText"></param> /// <param name="parameters"></param> /// <param name="cmdType"></param> /// <param name="type"></param> /// <param name="dbString"></param> /// <returns></returns> public static FluDataAdapter GetAdapter3(string commandText = null, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.WriteRead, string dbString = null) { FluDataAdapter da = new FluDataAdapter(type, dbString); da.CommandText = commandText; da.CommandType = cmdType; if (parameters != null) { foreach (var item in parameters) { da.AddParameter(item); } } if (da.ShowSql) { SetSqlShow(commandText, dbParameters: parameters); } return(da); }
/// <summary> /// 获取数据操作适配器 /// </summary> /// <param name="commandText"></param> /// <param name="parameters"></param> /// <param name="cmdType"></param> /// <param name="type"></param> /// <param name="dbString"></param> /// <returns></returns> public static FluDataAdapter GetAdapter2(string commandText = null, IDictionary <string, object> parameters = null, CommandType cmdType = CommandType.Text, DbConntionType type = DbConntionType.WriteRead, string dbString = null) { FluDataAdapter da = new FluDataAdapter(type, dbString); da.CommandText = commandText; da.CommandType = cmdType; if (parameters != null) { foreach (var item in parameters) { da.AddParameter(item.Key, item.Value); } } if (da.ShowSql) { SetSqlShow(commandText, parameters: parameters); } return(da); }
/// <summary> /// 执行存储过程 /// </summary> /// <param name="commandText"></param> /// <param name="parameters"></param> /// <param name="dbString"></param> /// <returns></returns> public static object ExcuteStoredProcedure(string commandText, IList <IDbDataParameter> parameters = null, string dbString = null) { using (FluDataAdapter adapter = GetAdapter3(commandText, parameters, CommandType.StoredProcedure, DbConntionType.WriteRead, dbString)) { adapter.OpenConnection(); object result = adapter.SelectCommand.ExecuteNonQuery(); if (parameters != null) { foreach (var p in parameters) { if (p.Direction == ParameterDirection.Output) { return(p.Value); } } adapter.CloseConnection(); } return(result); } }
/// <summary> /// 获取数据操作适配器 /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="paraName"></param> /// <param name="paraValue"></param> /// <param name="type"></param> /// <param name="dbString"></param> /// <returns></returns> public static FluDataAdapter GetAdapter(string commandText = null, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, DbConntionType type = DbConntionType.WriteRead, string dbString = null) { FluDataAdapter da = new FluDataAdapter(type, dbString); da.CommandText = commandText; da.CommandType = cmdType; da.ClearParameter(); if (paraName != null && paraValue != null && paraName.Length == paraValue.Length) { for (int i = 0; i < paraName.Length; i++) { da.AddParameter(paraName[i], paraValue[i]); } } if (da.ShowSql) { SetSqlShow(commandText, paraName, paraValue); } return(da); }
/// <summary> /// 插入对象 /// </summary> /// <param name="model"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public object Insert(V model, FluDataAdapter adapter = null) { var pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); D obj = Helper.Copy <D>(model); object newId; var command = GetPropertiesInsert(obj, out newId, adapter); var pList = GetOraParameters(command); long result = 0; if (pList == null) //含oracletype配置项 { if (providerEx.ProviderType == DbProviderType.Oracle || !pc.IdIdentity) { result = DataHelper.ExcuteNonQuery(command.Key, CommandType.Text, command.Value.Key, command.Value.Value, adapter, DBString); } else { string idsql = GetInsertId(providerEx.ProviderType, adapter, pc.TableName).ToString(); newId = result = Convert.ToInt64(DataHelper.ExcuteScalar(command.Key + idsql, CommandType.Text, command.Value.Key, command.Value.Value, DbConntionType.WriteRead, adapter, DBString)); } } else { if (providerEx.ProviderType == DbProviderType.Oracle || !pc.IdIdentity) { result = DataHelper.ExcuteNonQuery3(command.Key, pList, adapter: adapter, dbString: DBString); } else { string idsql = GetInsertId(providerEx.ProviderType, adapter, pc.TableName).ToString(); newId = result = Convert.ToInt64(DataHelper.ExcuteScalar3(command.Key + idsql, pList, CommandType.Text, DbConntionType.WriteRead, adapter, DBString)); } } if (result > 0) { return(newId); } return(null); }
/// <summary> /// 批量插入对象 /// </summary> /// <param name="list"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public object InsertBatch(IList <V> list, FluDataAdapter adapter = null) { //string sql = "INSERT INTO TABLES(NAME,EMAIL) SELECT 'NAME','EMAIL' UNION SELECT 'NAME2','EMAIL2'"; return(null); }
/// <summary> /// 更新需要更新的字段 /// </summary> /// <param name="id"></param> /// <param name="expression"></param> /// <param name="newValues"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public int Update(object id, Expression <Func <V, object[]> > expression, object[] newValues, FluDataAdapter adapter = null) { PCDictionary pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); var updates = GetPropertiesUpdate(expression); string sql = string.Format("UPDATE {0} SET {2} WHERE {1} = {3}p__{1}", pc.TableName, pc.IdName, updates.Key, providerEx.ParameterType); updates.Value.Add(providerEx.ParameterType + "p__" + pc.IdName); var newV = newValues.ToList(); newV.Add(id); var pList = GetOraParameters(updates.Value.ToArray(), newV.ToArray()); if (pList == null) { return(DataHelper.ExcuteNonQuery(sql, CommandType.Text, updates.Value.ToArray(), newV.ToArray(), adapter, DBString)); } return(DataHelper.ExcuteNonQuery3(sql, pList, adapter: adapter, dbString: DBString)); }
/// <summary> /// 更新需要更新的字段 /// </summary> /// <param name="expression"></param> /// <param name="where"></param> /// <param name="adapter">事务adapter</param> /// <returns></returns> public int Update(Expression <Func <V> > expression, Expression <Func <V, bool> > where, FluDataAdapter adapter = null) { PCDictionary pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); var command = GetPropertiesUpdate(expression, where); var pList = GetOraParameters(command); if (pList == null) { return(DataHelper.ExcuteNonQuery(command.Key, CommandType.Text, command.Value.Key, command.Value.Value, adapter, DBString)); } return(DataHelper.ExcuteNonQuery3(command.Key, pList, adapter: adapter, dbString: DBString)); }
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesInsert(D obj, out object id, FluDataAdapter adapter = null) { string sql = "INSERT INTO {0}({1}) VALUES({2})"; var pc = DMClassMap.GetPcDictionary(TypeName); StringBuilder cols = new StringBuilder(); StringBuilder vals = new StringBuilder(); List <string> param = new List <string>(); List <object> pvalue = new List <object>(); int i = 0; id = null; foreach (var item in obj.GetType().GetProperties()) { string column = pc.GetColumn(item.Name); if (!string.IsNullOrEmpty(column)) { if (column == pc.IdName && pc.IdIdentity) { if (providerEx.ProviderType == DbProviderType.Oracle) { id = GetInsertId(providerEx.ProviderType, adapter, pc.TableName, pc.SeqName); cols.AppendFormat("{0},", pc.IdName); vals.AppendFormat("{1}p__{0},", pc.IdName, providerEx.ParameterType); param.Add(providerEx.ParameterType + "p__" + pc.IdName); pvalue.Add(id); } } else { string paraName = string.Format("{2}p{0}__{1}", i.ToString(), column, providerEx.ParameterType); cols.AppendFormat("{0},", column); vals.AppendFormat("{0},", paraName); param.Add(paraName); pvalue.Add(item.GetValue(obj, null)); if (pc.IdName == column) { id = item.GetValue(obj, null); } } i++; } } sql = string.Format(sql, pc.TableName, cols.ToString().TrimEnd(','), vals.ToString().TrimEnd(',')); return(new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>( param.ToArray(), pvalue.ToArray()))); }
private KeyValuePair <string, KeyValuePair <string[], object[]> > GetPropertiesInsert(Expression <Func <V> > expression, out object id, FluDataAdapter adapter = null) { var insertExp = expression.Body as MemberInitExpression; var insertList = insertExp.Bindings.Cast <MemberAssignment>().Select(a => new { Name = a.Member.Name, Value = GetMemeberValue(a) }); StringBuilder sqlInsert = new StringBuilder(); StringBuilder valueInsert = new StringBuilder(); int i = 0; id = null; List <string> param = new List <string>(); List <object> pvalue = new List <object>(); var pc = DMClassMap.GetPcDictionary(TypeName); if (pc.IdIdentity) { if (providerEx.ProviderType == DbProviderType.Oracle) { id = GetInsertId(providerEx.ProviderType, adapter, pc.TableName, pc.SeqName); sqlInsert.AppendFormat("{0},", pc.IdName); valueInsert.AppendFormat("{1}p__{0},", pc.IdName, providerEx.ParameterType); param.Add(providerEx.ParameterType + "p__" + pc.IdName); pvalue.Add(id); } } foreach (var item in insertList) { string column = pc.GetColumn(item.Name); if (!string.IsNullOrEmpty(column)) { if (pc.IdName == column && pc.IdIdentity) { } else { string pname = string.Format("{2}p{0}__{1}", i.ToString(), column, providerEx.ParameterType); sqlInsert.AppendFormat("{0},", column); valueInsert.AppendFormat("{0},", pname); param.Add(pname); pvalue.Add(item.Value); i++; if (column == pc.IdName) { id = item.Value; } } } } string insert = sqlInsert.ToString().TrimEnd(','); string values = valueInsert.ToString().TrimEnd(','); string sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", DMClassMap.GetPcDictionary(TypeName).TableName, insert, values); KeyValuePair <string, KeyValuePair <string[], object[]> > kvp = new KeyValuePair <string, KeyValuePair <string[], object[]> >(sql, new KeyValuePair <string[], object[]>(param.ToArray(), pvalue.ToArray())); return(kvp); }
/// <summary> /// 执行SQL语句 /// </summary> /// <param name="commandText"></param> /// <param name="cmdType"></param> /// <param name="paraName">参数名数组</param> /// <param name="paraValue">参数值数组</param> /// <param name="adapter">是否外部传递,如使用事务</param> /// <param name="dbString">分库名</param> /// <returns></returns> public static int ExcuteNonQuery(string commandText, CommandType cmdType = CommandType.Text, string[] paraName = null, object[] paraValue = null, FluDataAdapter adapter = null, string dbString = null) { if (adapter == null) { using (adapter = GetAdapter(commandText, cmdType, paraName, paraValue, DbConntionType.WriteRead, dbString)) { adapter.OpenConnection(); int result = adapter.SelectCommand.ExecuteNonQuery(); adapter.CloseConnection(); return(result); } } adapter.ClearParameter(); adapter.CommandText = commandText; adapter.CommandType = cmdType; if (paraName != null && paraValue != null && paraName.Length == paraValue.Length) { for (int i = 0; i < paraName.Length; i++) { adapter.AddParameter(paraName[i], paraValue[i]); } } int result2 = adapter.SelectCommand.ExecuteNonQuery(); return(result2); }
/// <summary> /// 执行SQL语句 /// </summary> /// <param name="commandText"></param> /// <param name="parameters">参数化列表</param> /// <param name="cmdType"></param> /// <param name="adapter"></param> /// <param name="dbString">分库名</param> /// <returns></returns> public static int ExcuteNonQuery3(string commandText, IList <IDbDataParameter> parameters = null, CommandType cmdType = CommandType.Text, FluDataAdapter adapter = null, string dbString = null) { if (adapter == null) { using (adapter = GetAdapter3(commandText, parameters, cmdType, DbConntionType.WriteRead, dbString)) { adapter.OpenConnection(); int result = adapter.SelectCommand.ExecuteNonQuery(); adapter.CloseConnection(); return(result); } } adapter.ClearParameter(); adapter.CommandText = commandText; adapter.CommandType = cmdType; if (parameters != null) { foreach (var p in parameters) { adapter.AddParameter(p); } } int result2 = adapter.SelectCommand.ExecuteNonQuery(); return(result2); }
public object Insert(Expression <Func <V, object[]> > expression, object[] values, FluDataAdapter adapter = null) { PCDictionary pc = DMClassMap.GetPcDictionary(TypeName); CheckSqlTable(pc); int index = -1; var cols = GetPropertiesInsert(expression, ref index); IList <string> param = cols.Value.ToList(); IList <object> vals = values.ToList(); object id = null; if (pc.IdIdentity) { if (providerEx.ProviderType == DbProviderType.Oracle) { id = GetInsertId(providerEx.ProviderType, adapter, pc.TableName, pc.SeqName); if (index >= 0) { vals.RemoveAt(index); } vals.Add(id); } } else if (index >= 0) //非自增主键ID { id = values[index]; } string sql = string.Format("INSERT INTO {0}{1}", pc.TableName, cols.Key); var pList = GetOraParameters(param.ToArray(), vals.ToArray()); int result = 0; if (pList == null) { result = DataHelper.ExcuteNonQuery(sql, CommandType.Text, param.ToArray(), vals.ToArray(), adapter, DBString); } else { result = DataHelper.ExcuteNonQuery3(sql, pList, adapter: adapter, dbString: DBString); } if (result > 0) { if (providerEx.ProviderType != DbProviderType.Oracle && id == null) { id = GetInsertId(providerEx.ProviderType, adapter, pc.TableName); } return(id); } return(null); }