public static DataSet ExecuteStoredProcedure(string storedProcedureName, string dataSetName)
{
DataSet returnData = new DataSet();
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
returnData.DataSetName = dataSetName;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(returnData);
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, null, thrownException);
}
return(returnData);
}
public static void ExecuteNonQuery(string storedProcedureName)
{
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, null, thrownException);
}
}
private ConnectionManager()
{
int limit = AppSettingHelper.GetInteger(Constant.Appsetting_MaxConnectionCount, Constant.Appsetting_MaxConnectionCount_Default);
_connectionString = AppSettingHelper.GetConnectionString(Constant.Connection_Name, Constant.Connection_String_Default);
_connectionLimit = new Semaphore(limit, limit);
}
public static DataSet ExecuteStoredProcedure(string storedProcedureName, List <SqlParameter> parameters, string outputParmName, SqlDbType outputParmType, out string outValue, int Size = 0)
{
DataSet returnData = new DataSet();
outValue = string.Empty;
SqlParameter outputParm;
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
foreach (SqlParameter prm in parameters)
{
cmd.Parameters.Add(prm);
}
if (Size == 0)
{
outputParm = new SqlParameter("@" + outputParmName, outputParmType);
}
else
{
outputParm = new SqlParameter("@" + outputParmName, outputParmType, Size);
}
outputParm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParm);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName);
dataAdapter.Fill(returnData);
outValue = outputParm.Value.ToString();
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters, thrownException);
}
return(returnData);
}
public static DataSet ExecuteStoredProcedure(string connectionString, string dbName, string storedProcedureName, List <SqlParameter> parameters,
string dataSetName = null)
{
DataSet returnData = new DataSet();
Exception thrownException = null;
try
{
string conStr;
if (!string.IsNullOrEmpty(connectionString))
{
conStr = connectionString;
}
else
{
conStr = AppSettingHelper.GetConnectionString(dbName);
}
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
foreach (SqlParameter prm in parameters)
{
cmd.Parameters.Add(prm);
}
if (dataSetName != null)
{
returnData.DataSetName = dataSetName;
}
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters);
dataAdapter.Fill(returnData);
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters, thrownException);
}
return(returnData);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
try
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseMySQL(AppSettingHelper.GetConnectionString("dbconn"));
}
}
catch (Exception)
{
return;
}
}
public static object ExecuteStoredProcedure(string storedProcedureName, List <SqlParameter> parameters, SqlDbType primaryKey)
{
int pkIndex;
const string pk = "@PrimaryKey";
object returnVal = null;
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
SqlParameter parameter;
parameter = new SqlParameter(pk, primaryKey);
parameter.Direction = ParameterDirection.ReturnValue;
parameters.Add(parameter);
foreach (SqlParameter prm in parameters)
{
cmd.Parameters.Add(prm);
}
cmd.ExecuteNonQuery();
pkIndex = cmd.Parameters.IndexOf(pk);
if (pkIndex >= 0)
{
returnVal = cmd.Parameters[pkIndex].Value;
}
}
}
catch (Exception ex)
{
thrownException = ex;
string msg = ex.Message;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters, thrownException);
}
return(returnVal);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
//services.AddSignalR();
services.AddControllersWithViews();
services.AddDistributedMemoryCache();
//获取配置的session丢失时间
var timeSpan = AppSettingHelper.GetSectionValue("IdleTimeout");
services.AddSession(options =>
{
options.IdleTimeout = TimeSpan.FromSeconds(Convert.ToDouble(timeSpan));
options.Cookie.HttpOnly = false;
options.Cookie.IsEssential = true;
});
try
{
services.AddDbContext <MyContext>(options => options.UseMySQL(AppSettingHelper.GetConnectionString("dbconn")));
}
catch (Exception)
{
return;
}
services.AddRazorPages(options =>
{
//options.Conventions.Add(new DefaultRouteRemovalPageRouteModelConvention(String.Empty));
options.Conventions.AddPageRoute("/Login", "Index");
});
services.AddMvc().AddRazorPagesOptions(o =>
{
o.Conventions.ConfigureFilter(new IgnoreAntiforgeryTokenAttribute());
});
services.AddAntiforgery(o => o.HeaderName = "XSRF-TOKEN");
//注册心跳
services.AddSingleton <IHostedService, HeartService>();
//DinkToPdf注入
services.AddSingleton(typeof(IConverter), new SynchronizedConverter(new PdfTools()));
services.AddTransient <IPDFService, PDFService>();
services.AddTimedJob();
}
public static string ExecuteScalar(string storedProcedureName, List <SqlParameter> inputParameters)
{
string returnData = string.Empty;
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = defaultCommandTimeout;
if (inputParameters != null)
{
foreach (SqlParameter prm in inputParameters)
{
cmd.Parameters.Add(prm);
}
}
returnData = Convert.ToString(cmd.ExecuteScalar());
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, inputParameters, thrownException);
}
return(returnData);
}
public static void ExecuteStoredProcedure(string storedProcedureName, List <SqlParameter> parameters
, int commandTimeoutInSeconds)
{
int returnVal = 0;
Exception thrownException = null;
try
{
string connString = AppSettingHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
cmd.CommandTimeout = commandTimeoutInSeconds;
foreach (SqlParameter prm in parameters)
{
cmd.Parameters.Add(prm);
}
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters);
returnVal = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
thrownException = ex;
throw ex;
}
finally
{
WriteExecutionInformation(DataAccessCallType.StoredProcedure, storedProcedureName, parameters, thrownException);
}
}
public static DataSet ExecuteSelectSql(string selectSql)
{
return(ExecuteSelectSql(AppSettingHelper.GetConnectionString(), selectSql));
}