/// <summary> /// 执行SQL语句返回结果集object /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static object ExecuteScalar(string sql, params IDbDataParameter[] cmdParms) { sql = DataBaseFactory.GetSQL(sql); DataResult result = new DataResult(); result.Sql = sql + ";"; using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); object rows = IDbComm.ExecuteScalar(); IDbConn.Close(); IDbComm.Parameters.Clear(); return(rows); } catch (Exception E) { IDbConn.Close(); return(null); throw new Exception(E.Message); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句,并返加影响的行数 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public int ExecuteNonQuery(string sql, params IDbDataParameter[] cmdParms) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); int rows = IDbComm.ExecuteNonQuery(); IDbConn.Close(); IDbComm.Parameters.Clear(); return(rows); } catch (Exception E) { IDbConn.Close(); return(0); throw new Exception(E.Message); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句返回结果集DataSet /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public DataSet GetDataSet(string sql, params IDbDataParameter[] cmdParms) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); IDbDataAdapter dataAdapter = DataBaseFactory.GetIDbDataAdapter(); dataAdapter.SelectCommand = IDbComm; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); IDbComm.Parameters.Clear(); return(dataSet); } catch (Exception E) { IDbConn.Close(); return(null); throw new Exception(E.Message); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句,并返加影响的行数 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public int ExecuteNonQuery(string sql) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand(sql)) { try { int rows = IDbComm.ExecuteNonQuery(); IDbConn.Close(); return(rows); } catch (Exception E) { IDbConn.Close(); return(0); throw new Exception(E.Message); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句返回结果集DataSet /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public DataSet GetDataSet(string sql) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); DataSet dataset = new DataSet(); try { IDbComm = DataBaseFactory.GetIDbCommand(sql); //IDbComm.CommandText = sql; //IDbComm.CommandType = CommandType.Text; IDbAdpter = DataBaseFactory.GetIDbDataAdapter(IDbComm); //IDbAdpter.SelectCommand = IDbComm; IDbAdpter.Fill(dataset); } catch (Exception e) { IDbConn.Close(); throw new Exception(e.Message); } finally { IDbConn.Close(); } return(dataset); } }
/// <summary> /// 执行SQL语句返回结果中上的第一行第一列的值,忽略额外的列和行 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public object ExecuteScalar(string sql) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand(sql)) { try { //IDbComm.CommandText = sql; //IDbComm.CommandType = CommandType.Text; object rows = IDbComm.ExecuteScalar(); IDbConn.Close(); return(rows); } catch (Exception E) { IDbConn.Close(); return(null); throw new Exception(E.Message); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句,并返加影响的行数 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static DataResult ExecuteNonQuery(string sql, params IDbDataParameter[] cmdParms) { sql = DataBaseFactory.GetSQL(sql); DataResult result = new DataResult(); result.Sql = sql + ";"; result.IDbDataParameter = cmdParms; using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); int rows = IDbComm.ExecuteNonQuery(); IDbConn.Close(); IDbComm.Parameters.Clear(); result.RecordCount = rows; return(result); } catch (Exception E) { IDbConn.Close(); result.RecordCount = 0; result.Error = E.Message; return(result); } finally { IDbConn.Close(); } } } }
/// <summary> /// 执行SQL语句返回结果集IDataReader /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static DataResult GetDataReader(string sql, params IDbDataParameter[] cmdParms) { sql = DataBaseFactory.GetSQL(sql); DataResult result = new DataResult(); result.Sql = sql + ";"; result.IDbDataParameter = cmdParms; try { if (IDbConn == null || IDbConn.State == ConnectionState.Closed) { IDbConn = DataBaseFactory.GetIDbConnection(); IDbConn.Open(); IDbComm = DataBaseFactory.GetIDbCommand(); PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); // = IDbcmd.ExecuteReader(); result.IDataReader = IDbComm.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection)); return(result); // IDbComm.Parameters.Clear(); } else { return(result); } } catch (Exception e) { IDbConn.Close(); result.Error = e.Message; return(result); } }
/// <summary> /// 执行SQL语句,并返加影响的行数 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public static DataResult ExecuteNonQuery(string sql) { DataResult result = new DataResult(); result.Sql = sql + ";"; using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand(sql)) { try { int rows = IDbComm.ExecuteNonQuery(); IDbConn.Close(); result.RecordCount = rows; } catch (Exception E) { result.Error = E.Message; IDbConn.Close(); result.RecordCount = 0; return(result); } finally { IDbConn.Close(); } } } return(result); }
/// <summary> /// 执行SQL语句返回结果集DataSet /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public static DataResult GetDataSet(string sql) { sql = DataBaseFactory.GetSQL(sql); DataResult result = new DataResult(); result.Sql = sql + ";"; using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, IDbParams); IDbDataAdapter dataAdapter = DataBaseFactory.GetIDbDataAdapter(); dataAdapter.SelectCommand = IDbComm; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); IDbComm.Parameters.Clear(); result.DataSet = dataSet; } catch (Exception E) { IDbConn.Close(); result.Error = E.Message; return(result); } finally { IDbConn.Close(); } } } return(result); //using (IDbConn = DataBaseFactory.GetIDbConnection()) //{ // IDbConn.Open(); // DataSet dataset = new DataSet(); // try // { // IDbComm = DataBaseFactory.GetIDbCommand(sql); // //IDbComm.CommandText = sql; // //IDbComm.CommandType = CommandType.Text; // IDbAdpter = DataBaseFactory.GetIDbDataAdapter(IDbComm); // //IDbAdpter.SelectCommand = IDbComm; // IDbAdpter.Fill(dataset); // } // catch (Exception e) // { // IDbConn.Close(); // throw new Exception(e.Message); // } // finally // { // IDbConn.Close(); // } // return dataset; //} }
/// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (IDbConn != null) { if (IDbConn.State == ConnectionState.Open) { IDbConn.Close(); IDbConn.Dispose(); } IDbConn = null; IDbComm = null; IDbAdpter = null; IDbTrans = null; } }
/// <summary> /// 通过事务提执行SQL语句(必须放在BeginTransaction()方法与CommitTransaction()方法中间) /// </summary> /// <param name="sql">SQL语句</param> public static DataResult ExecuteSQLByTransaction(string sql) { DataResult result = new DataResult(); sql = DataBaseFactory.GetSQL(sql); result.Sql = sql + ";"; try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, IDbParams); int rows = IDbComm.ExecuteNonQuery(); IDbComm.Parameters.Clear(); } catch (Exception E) { result.Error = E.Message; IDbConn.Close(); } return(result); }
/// <summary> /// 通过事务执行多条SQL语句 /// </summary> /// <param name="sqlList">SQL语句集</param> /// <returns></returns> public static DataResult ExecuteByTransaction(List <string> sqlList) { DataResult result = new DataResult(); using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); IDbComm = DataBaseFactory.GetIDbCommand(); IDbComm.Connection = IDbConn; IDbTrans = IDbConn.BeginTransaction(IsolationLevel.ReadCommitted);// DataBaseFactory.GetIDbTransaction(); if (IDbTrans != null) { IDbComm.Transaction = IDbTrans; // IDbTrans = IDbConn.BeginTransaction(IsolationLevel.ReadCommitted); } try { for (int i = 0; i < sqlList.Count; i++) { string str = sqlList[i].ToString(); if (str.Trim().Length > 1) { IDbComm.CommandText = str; result.RecordCount += IDbComm.ExecuteNonQuery(); } } IDbTrans.Commit(); return(result); } catch (Exception exception) { IDbTrans.Rollback(); IDbConn.Close(); result.Error = exception.Message; return(result); } finally { IDbConn.Close(); } } }
/// <summary> /// 通过事务执行多条SQL语句 /// </summary> /// <param name="sqlList">SQL语句集</param> /// <returns></returns> public bool ExecuteByTransaction(List <string> sqlList) { using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); IDbComm = DataBaseFactory.GetIDbCommand(); IDbComm.Connection = IDbConn; IDbTrans = IDbConn.BeginTransaction();// DataBaseFactory.GetIDbTransaction(); if (IDbTrans != null) { IDbComm.Transaction = IDbTrans; IDbTrans = IDbConn.BeginTransaction(IsolationLevel.ReadCommitted); } try { for (int i = 0; i < sqlList.Count; i++) { string str = sqlList[i].ToString(); if (str.Trim().Length > 1) { IDbComm.CommandText = str; IDbComm.ExecuteNonQuery(); } } IDbTrans.Commit(); return(true); } catch (Exception exception) { IDbTrans.Rollback(); IDbConn.Close(); return(false); throw new Exception(exception.Message); } finally { IDbConn.Close(); } } }
/// <summary> /// 提交事务 /// </summary> public static DataResult CommitTransaction() { DataResult result = new DataResult(); try { IDbTrans.Commit(); return(result); } catch (Exception exception) { IDbTrans.Rollback(); IDbConn.Close(); result.Error = exception.Message; return(result); } finally { IDbConn.Close(); } }
public static DataResult ExistsRecord(string sql) { DataResult result = new DataResult(); result.Sql = sql + ";"; IDataReader ireader = null; try { if (IDbConn == null || IDbConn.State == ConnectionState.Closed) { IDbConn = DataBaseFactory.GetIDbConnection(); IDbConn.Open(); IDbComm = DataBaseFactory.GetIDbCommand(sql); //IDbComm.CommandText = sql; //IDbComm.CommandType = CommandType.Text; ireader = IDbComm.ExecuteReader(); if (ireader.Read()) { result.RecordCount = 1; } else { result.RecordCount = 0; } } else { result.RecordCount = 1; } } catch (Exception e) { IDbConn.Close(); result.Error = e.Message; return(result); } return(result); }
/// <summary> /// 执行SQL语句返回结果集DataSet /// </summary> /// <param name="sql">SQL语句</param> /// <param name="cmdParms">参数</param> /// <returns></returns> public static DataResult GetDataSet(string sql, params IDbDataParameter[] cmdParms) { sql = DataBaseFactory.GetSQL(sql); DataResult result = new DataResult(); result.Sql = sql + ";"; result.IDbDataParameter = cmdParms; using (IDbConn = DataBaseFactory.GetIDbConnection()) { IDbConn.Open(); using (IDbComm = DataBaseFactory.GetIDbCommand()) { try { PrepareCommand(IDbComm, IDbConn, null, CommandType.Text, sql, cmdParms); IDbDataAdapter dataAdapter = DataBaseFactory.GetIDbDataAdapter(); dataAdapter.SelectCommand = IDbComm; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); IDbComm.Parameters.Clear(); result.DataSet = dataSet; } catch (Exception E) { IDbConn.Close(); result.Error = E.Message; return(result); } finally { IDbConn.Close(); } } } return(result); }