/// <summary> /// 调用存储过程返回指定消息(不带事务) /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="ht">Hashtable</param> /// <param name="msg">OutPut Msg</param> public int ExecuteByProcNotTranReturnMsg(string procName, Hashtable ht, ref object msg) { int num = 0; try { DbCommand storedProcCommand = this.GetInstance().GetStoredProcCommand(procName); DbCommon.AddInParameter(db, storedProcCommand, ht); using (DbConnection connection = this.db.CreateConnection()) { try { connection.Open(); num = this.db.ExecuteNonQuery(storedProcCommand); num = 1; } catch (Exception e) { DbLog.WriteException(e); } finally { connection.Close(); connection.Dispose(); } } msg = this.db.GetParameterValue(storedProcCommand, ":Msg"); } catch (Exception e) { DbLog.WriteException(e); } return(num); }
/// <summary> /// 批量调用存储过程 /// </summary> /// <param name="text"></param> /// <param name="param"></param> /// <returns></returns> public int BatchExecuteByProc(object[] text, object[] param) { int num = 0; try { using (DbConnection connection = this.GetInstance().CreateConnection()) { connection.Open(); dbTransaction = connection.BeginTransaction(); try { for (int i = 0; i < text.Length; i++) { string strtext = text[i].ToString().ToUpper().Replace("@", ":"); if (strtext != null) { SqlParam[] paramArray = (SqlParam[])param[i]; DbCommand command = null; if (strtext.StartsWith("PROC_")) { command = this.db.GetStoredProcCommand(strtext); } else { command = this.db.GetSqlStringCommand(strtext); } DbCommon.AddInParameter(db, command, paramArray); this.db.ExecuteNonQuery(command, dbTransaction); } } dbTransaction.Commit(); num = 1; } catch (Exception e) { num = -1; dbTransaction.Rollback(); DbLog.WriteException(e); } finally { connection.Close(); connection.Dispose(); dbTransaction.Dispose(); } } } catch (Exception e) { DbLog.WriteException(e); } return(num); }
/// <summary> /// 摘要: /// 执行一存储过程DataTable /// 参数: /// procName:存储过程名称 /// Hashtable:传入参数字段名 /// </summary> public DataTable GetDataTableProc(string procName, Hashtable ht) { try { dbCommand = this.GetInstance().GetStoredProcCommand(procName); DbCommon.AddInParameter(db, dbCommand, ht); return(DbReader.ReaderToDataTable(db.ExecuteReader(dbCommand))); } catch (Exception e) { DbLog.WriteException(e); return(null); } }
/// <summary> /// 根据 SQL 返回 DataSet 数据集,带参数 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">参数化</param> /// <returns>DataSet</returns> public DataSet GetDataSetBySQL(StringBuilder sql, SqlParam[] param) { try { dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString())); DbCommon.AddInParameter(db, dbCommand, param); return(db.ExecuteDataSet(dbCommand)); } catch (Exception e) { DbLog.WriteException(e); return(null); } }
/// <summary> /// 根据 SQL 返回 DataTable 数据集,带参数 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">参数化</param> /// <returns>DataTable</returns> public DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param) { try { dbCommand = this.GetInstance().GetSqlStringCommand(sql.ToString()); DbCommon.AddInParameter(db, dbCommand, param); return(DbReader.ReaderToDataTable(db.ExecuteReader(dbCommand))); } catch (Exception e) { DbLog.WriteException(e); return(null); } }
/// <summary> /// 根据SQL返回影响行数,带参数 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">参数化</param> /// <returns></returns> public object GetObjectValue(StringBuilder sql, SqlParam[] param) { try { dbCommand = this.GetInstance().GetSqlStringCommand(sql.ToString()); DbCommon.AddInParameter(db, dbCommand, param); return(db.ExecuteScalar(dbCommand)); } catch (Exception e) { DbLog.WriteException(e); return(null); } }
/// <summary> /// 摘要: /// 执行一存储过程DataSet /// 参数: /// procName:存储过程名称 /// Hashtable:传入参数字段名 /// </summary> public DataSet GetDataSetProc(string procName, Hashtable ht) { try { dbCommand = this.GetInstance().GetStoredProcCommand(procName); DbCommon.AddInParameter(db, dbCommand, ht); DataSet ds = db.ExecuteDataSet(dbCommand); return(ds); } catch (Exception e) { DbLog.WriteException(e); return(null); } }
/// <summary> /// 批量执行SQL语句 /// </summary> /// <param name="sqls">sql语句</param> /// <param name="m_param">参数化</param> /// <returns></returns> public int BatchExecuteBySql(object[] sqls, object[] param) { int num = 0; try { using (DbConnection connection = this.GetInstance().CreateConnection()) { connection.Open(); dbTransaction = connection.BeginTransaction(); try { for (int i = 0; i < sqls.Length; i++) { StringBuilder builder = (StringBuilder)sqls[i]; if (builder != null) { SqlParam[] paramArray = (SqlParam[])param[i]; DbCommand sqlStringCommand = this.db.GetSqlStringCommand(builder.ToString().Replace("@", ":")); DbCommon.AddInParameter(db, sqlStringCommand, paramArray); this.db.ExecuteNonQuery(sqlStringCommand, dbTransaction); } } dbTransaction.Commit(); num = 1; } catch (Exception e) { num = -1; dbTransaction.Rollback(); DbLog.WriteException(e); } finally { connection.Close(); connection.Dispose(); dbTransaction.Dispose(); } } } catch (Exception e) { DbLog.WriteException(e); } return(num); }
/// <summary> /// 根据SQL执行,带参数,不带事务 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">参数化</param> /// <returns>object</returns> public int ExecuteBySqlNotTran(StringBuilder sql, SqlParam[] param) { int num = 0; try { dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString())); DbCommon.AddInParameter(db, dbCommand, param); num = db.ExecuteNonQuery(dbCommand); } catch (Exception e) { num = -1; DbLog.WriteException(e); } return(num); }
/// <summary> /// 调用存储过程(带事务) /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="ht">参数化</param> public int ExecuteByProc(string procName, Hashtable ht) { int num = 0; try { DbCommand storedProcCommand = this.GetInstance().GetStoredProcCommand(procName); DbCommon.AddInParameter(db, storedProcCommand, ht); using (DbConnection connection = this.db.CreateConnection()) { connection.Open(); dbTransaction = connection.BeginTransaction(); try { num = this.db.ExecuteNonQuery(storedProcCommand, dbTransaction); dbTransaction.Commit(); } catch (Exception e) { num = -1; dbTransaction.Rollback(); DbLog.WriteException(e); } finally { connection.Close(); connection.Dispose(); dbTransaction.Dispose(); } } } catch (Exception e) { DbLog.WriteException(e); } return(num); }
/// <summary> /// 根据SQL执行,带参数 /// </summary> /// <param name="sql">sql语句</param> /// <param name="param">参数化</param> /// <returns>object</returns> public int ExecuteBySql(StringBuilder sql, SqlParam[] param) { int num = 0; try { dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString())); DbCommon.AddInParameter(db, dbCommand, param); using (DbConnection connection = db.CreateConnection()) { connection.Open(); dbTransaction = connection.BeginTransaction(); try { num = db.ExecuteNonQuery(dbCommand, dbTransaction); dbTransaction.Commit(); } catch (Exception e) { dbTransaction.Rollback(); num = -1; DbLog.WriteException(e); } finally { connection.Close(); connection.Dispose(); dbTransaction.Dispose(); } } } catch (Exception e) { DbLog.WriteException(e); } return(num); }
/// <summary> /// 根据 SQL 返回 IList,带参数 (比DataSet效率高) /// </summary> /// <typeparam name="T">实体类</typeparam> /// <param name="sql">Sql语句</param> /// <param name="param">参数化</param> /// <returns></returns> public IList GetDataListBySQL <T>(StringBuilder sql, SqlParam[] param) { dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString())); DbCommon.AddInParameter(db, dbCommand, param); return(DbReader.ReaderToList <T>(db.ExecuteReader(dbCommand))); }