public static DataTable GetDataTable(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { if (conn == null) { throw new ArgumentNullException("conn"); } if (string.IsNullOrEmpty(cmdText)) { throw new ArgumentNullException("cmdText"); } try { if (conn.State != ConnectionState.Open) { conn.Open(); } var cmd = DBFactory.NewCommand(conn); cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (commandParameters != null) { foreach (var param in commandParameters) { cmd.Parameters.Add(param); } } var da = DBFactory.NewDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); //填充数据 cmd.Parameters.Clear(); conn.Close(); if (ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(null); } } catch (Exception ex) { EventLogger.Log("SqlHelper.GetDataSet 【" + cmdText + "】", ex); RefreshSqlConnection(); return(null); } }
public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, List <string> sqlList) { if (conn == null) { throw new ArgumentNullException("conn"); } try { if (conn.State != ConnectionState.Open) { conn.Open(); } var cmd = DBFactory.NewCommand(dbType, conn); using (var trans = conn.BeginTransaction()) { cmd.Transaction = trans; try { foreach (string sql in sqlList) { if (string.IsNullOrWhiteSpace(sql)) { continue; } cmd.CommandText = sql; cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { EventLogger.Log("SqlHelper.ExecuteNonQuerys SQL【" + string.Join(";", sqlList.ToArray()) + "】 ", ex); trans.Rollback(); //回滚 OpenConnection(dbType, conn); //重新连接 return(ex.Message); } } conn.Close(); return("OK"); } catch (Exception ex) { EventLogger.Log("SqlHelper.ExecuteNonQuery 错误.", ex); OpenConnection(dbType, conn); //重新连接 return(ex.Message); } }
public static string GetFirstValue(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { if (conn == null) { throw new ArgumentNullException("conn"); } if (string.IsNullOrEmpty(cmdText)) { throw new ArgumentNullException("cmdText"); } try { if (conn.State != ConnectionState.Open) { conn.Open(); } var cmd = DBFactory.NewCommand(conn); cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (commandParameters != null) { foreach (var param in commandParameters) { cmd.Parameters.Add(param); } } object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); conn.Close(); return(val?.ToString()); } catch (Exception ex) { EventLogger.Log("SqlHelper.GetFirstValue 【" + cmdText + "】", ex); RefreshSqlConnection(); return(null); } }
/// <summary> /// /// </summary> /// <param name="conn"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <returns>OK;错误信息</returns> public static string ExecuteNonQuery(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { if (conn == null) { throw new ArgumentNullException("conn"); } if (string.IsNullOrEmpty(cmdText)) { throw new ArgumentNullException("cmdText"); } try { if (conn.State != ConnectionState.Open) { conn.Open(); } var cmd = DBFactory.NewCommand(conn); cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (commandParameters != null) { foreach (var param in commandParameters) { cmd.Parameters.Add(param); } } cmd.ExecuteNonQuery(); //执行 cmd.Parameters.Clear(); conn.Close(); return("OK"); } catch (Exception ex) { EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex); RefreshSqlConnection(); return(ex.Message); } }
public static DbDataReader ExecuterReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { if (string.IsNullOrEmpty(connectionString)) { throw new ArgumentNullException("connectionString"); } if (string.IsNullOrEmpty(cmdText)) { throw new ArgumentNullException("cmdText"); } try { var conn = DBFactory.NewConnection(connectionString); conn.Open(); var cmd = DBFactory.NewCommand(conn); cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (commandParameters != null) { foreach (var param in commandParameters) { cmd.Parameters.Add(param); } } var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return(dr); } catch (Exception ex) { EventLogger.Log("SqlHelper.ExecuterReader 【" + cmdText + "】", ex); RefreshSqlConnection(); return(null); } }