//set agents file path
public static bool LoadAgentsFromFile(string filepath)
{
if (File.Exists(filepath))
{
try
{
_manDoc.Load(filepath);
_agents.Clear();
if (_manDoc.DocumentElement.HasChildNodes)
{
foreach (XmlNode node in _manDoc.DocumentElement.ChildNodes)
{
DbAgent agent = new DbAgent();
agent.GetAgentFromXml(node);
_agents.Add(agent.Name, agent);
}
return(true);
}
}
catch
{
return(false);
}
}
return(false);
}
public static void GetAgentFromXml(this DbAgent agent, XmlNode node)
{
// agent = new DbAgent();
agent.Name = node.Attributes[XmlName].Value;
agent.SetConnectionString(node.Attributes[XmlConStr].Value);
agent.SetState(Convert.ToBoolean(node.Attributes[XmlState].Value));
}
public static XmlNode ToXmlAgent(this DbAgent agent)
{
XmlElement node = _manDoc.CreateElement(XmlDbAgent);
node.SetAttribute(XmlName, agent.Name);
node.SetAttribute(XmlConStr, agent.ConnectionString);
node.SetAttribute(XmlState, agent.State.ToString());
return(node);
}
public static ExecResult ExecuteProcedureDataSet(DbAgent agent, DataSItem item, out DataSet set)
{
if (agent != null && agent.State)
{
return(ExecuteDS(item, out set, agent));
}
else
{
throw new Exception("Agent is null or state is false ");
}
}
public static ExecResult ExecuteNonQueryProcedure(DbAgent agent, DataSItem itm)
{
if (agent != null && agent.State)
{
return(ExecuteProcedureNonQuery(itm, agent));
}
else
{
throw new Exception("Agent is null or state is false ");
}
}
public static ExecResult ExecuteProcedureDataReaderByRef <T>(DbAgent agent, DataSItem item, out IBag <T> container)
{
if (agent != null && agent.State)
{
return(ExecuteProcedureDRByReflection <T>(item, out container, agent));
}
else
{
throw new Exception("Agent is null or the state is false ");
}
}
public static ExecResult ExecProcDataReadByInheritance <T>(DbAgent agent, DataSItem item, out IBag <T> container)
where T : DbObject
{
if (agent != null && agent.State)
{
return(ExecuteProcedureDR <T>(item, out container, agent));
}
else
{
throw new Exception("Agent is null or the state is false ");
}
}
public static async Task <ExecAsyncResult> ExecDataReadByInheritanceAsync <T>(this DataSItem item, DbAgent agent) where T : DbObject
{
//return Task.Factory.StartNew(() =>
// {
// ExecAsyncResult result = new ExecAsyncResult();
// IBag<T> container;
// ExecResult rs = ExecDataReadByInheritance<T>(item, agent, out container);
// result.ExecutedProcedure = item;
// result.Result = rs;
// result.Object = container;
// result.ExecutionType=AsyncExecutionType.ExecByINheritance;
// return result;
// });
ExecAsyncResult result = new ExecAsyncResult();
result.ExecutedProcedure = item;
IBag <T> container;
if (agent != null && agent.State)
{
// return ExecuteProcedureDRByReflection<T>(item, out container, agent.ConnectionString);
//---
SqlCommand cmd = null;
container = new Bag <T>();
result.StartMeasure();
try
{
// create and open a connection object "Data Source=FARID-PC;Initial Catalog=InsuranceFactory;Integrated Security=True"
//using (_conn = new SqlConnection(constr))
//{
SqlConnection con = null;
if (agent.ConnectionLevel == ConnectionLevel.Single)
{
con = agent.CreateConnection();
await con.OpenAsync();
}
else if (agent.ConnectionLevel == ConnectionLevel.AllInOne)
{
con = agent.Connection;
}
// check state of connection
cmd = new SqlCommand(item.Value, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = agent.RunTimeout;
if (agent.TransactionState == TransactionState.ActiveTransaction)
{
cmd.Transaction = agent.Transaction;
}
SqlParameter param;
foreach (var itm in item.Params.Values)
{
param = new SqlParameter();
param.ParameterName = itm.Name;
param.Value = itm.Value;
param.SqlDbType = SettingsHelperManager.DetermineSqlDbTYpe(itm.Type);
param.Direction = SettingsHelperManager.GetParametrDirection(itm.Direction);
cmd.Parameters.Add(param);
}
using (_reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await _reader.ReadAsync().ConfigureAwait(false))
{
container.SetFromReader(ref _reader);
}
}
//set outputparams values
if (item.HasOutputParam)
{
foreach (DataParam value in item.OutputParams.Values)
{
value.Value = cmd.Parameters[value.Name].Value.ToString();
}
}
if (cmd.Parameters.Count > 0)
{
List <DataParam> ret = item.GetparamsByDirection(ParamDirection.Return);
if (ret.Count > 0)
{
if (cmd.Parameters[ret[0].Name] != null)
{
result.Result.SetCode(
Convert.ToInt32(
cmd.Parameters[item.GetparamsByDirection(ParamDirection.Return)[0].Name].Value));
}
}
}
// }end of using
if (agent.ConnectionLevel == ConnectionLevel.Single && con != null) //&& agent.AgentState != AgentState.Disconnected)
{
con.Dispose();
}
result.Object = container;
}
catch (Exception exc)
{
throw exc;
}
result.StopMeasure();
result.ExecutionType = AsyncExecutionType.ExecByINheritance;
return(result);
//----
}
else
{
throw new Exception("Agent is null or the state is false ");
}
}
public static ExecResult ExecDataSet(this DataSItem item, DbAgent agent, out DataSet set)
{
return(ExecuteProcedureDataSet(agent, item, out set));
}
public static ExecResult ExecuteNonQuery(this DataSItem itm, DbAgent agent)
{
return(ExecuteNonQueryProcedure(agent, itm));
}
public static ExecResult ExecuteDataReaderByRef <T>(this DataSItem item, DbAgent agent, out IBag <T> container)
{
return(ExecuteProcedureDataReaderByRef(agent, item, out container));
}
public static ExecResult ExecDataReadByInheritance <T>(this DataSItem item, DbAgent agent, out IBag <T> container)
where T : DbObject
{
return(ExecProcDataReadByInheritance(agent, item, out container));
}
//add agent to temp
public static void AddAgent(DbAgent agent)
{
_agents.Add(agent.Name, agent);
_shouldBeSync = true;
}
} // update ,delete //insert
private static ExecResult ExecuteDS(DataSItem settingItem, out DataSet ds, DbAgent datasource)
{
ExecResult retval = new ExecResult();
// string cstr = datasource ?? settingItem.ConnectionString;
//using (_conn = new SqlConnection(cstr))
//{
retval.StartMeasure();
ds = new DataSet();
SqlConnection con = null;
if (datasource.ConnectionLevel == ConnectionLevel.Single)//&& datasource.AgentState != AgentState.Connected)
{
con = datasource.CreateConnection();
con.Open();
}
else if (datasource.ConnectionLevel == ConnectionLevel.AllInOne)
{
con = datasource.Connection;
}
// return if is not connected
if (con == null || con.State != ConnectionState.Open)
{
retval.SetCode(-2, "Not connected ! ");
retval.StopMeashure();
return(retval);
}
using (SqlCommand cmd = new SqlCommand(settingItem.Value, con))
{
SqlParameter param;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = datasource.RunTimeout;
//set transaction
if (datasource.TransactionState == TransactionState.ActiveTransaction)
{
cmd.Transaction = datasource.Transaction;
}
foreach (DataParam item in settingItem.Params.Values)
{
param = new SqlParameter();
param.ParameterName = item.Name;
param.Value = item.Value;
param.SqlDbType = SettingsHelperManager.DetermineSqlDbTYpe(item.Type);
param.Direction = SettingsHelperManager.GetParametrDirection(item.Direction);
cmd.Parameters.Add(param);
}
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = cmd;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds);
}
//set outputparams values
if (settingItem.HasOutputParam)
{
foreach (DataParam value in settingItem.OutputParams.Values)
{
value.Value = cmd.Parameters[value.Name].Value.ToString();
}
}
if (cmd.Parameters.Count > 0)
{
List <DataParam> returnparam = settingItem.GetparamsByDirection(ParamDirection.Return);
if (returnparam.Count > 0)
{
string name = returnparam[0].Name;
if (cmd.Parameters[name] != null)
{
retval.SetCode(Convert.ToInt32(cmd.Parameters[name].Value));
}
}
}
}
//end of using }
if (datasource.ConnectionLevel == ConnectionLevel.Single && con != null)
{
con.Dispose();
}
retval.StopMeashure();
return(retval);
}
private static ExecResult ExecuteProcedureDRByReflection <T>(DataSItem proc, out IBag <T> container, DbAgent datasource)
{
ExecResult result = new ExecResult(); // when user doesn't set return parameter
SqlCommand cmd = null;
container = new RefBag <T>();
result.StartMeasure();
try
{
// create and open a connection object "Data Source=FARID-PC;Initial Catalog=InsuranceFactory;Integrated Security=True"
//using (_conn = new SqlConnection(constr))
//{
SqlConnection con = null;
if (datasource.ConnectionLevel == ConnectionLevel.Single)//&& datasource.AgentState != AgentState.Connected)
{
con = datasource.CreateConnection();
con.Open();
}
else if (datasource.ConnectionLevel == ConnectionLevel.AllInOne)
{
con = datasource.Connection;
}
// return if is not connected
if (con == null || con.State != ConnectionState.Open)
{
result.SetCode(-2, "Not connected ! ");
result.StopMeashure();
return(result);
}
cmd = new SqlCommand(proc.Value, con)
{
CommandType = CommandType.StoredProcedure,
CommandTimeout = datasource.RunTimeout
};
SqlParameter param;
//set transaction
if (datasource.TransactionState == TransactionState.ActiveTransaction)
{
cmd.Transaction = datasource.Transaction;
}
cmd.CommandType = CommandType.StoredProcedure;
foreach (var item in proc.Params.Values)
{
param = new SqlParameter
{
ParameterName = item.Name,
Value = item.Value,
SqlDbType = SettingsHelperManager.DetermineSqlDbTYpe(item.Type),
Direction = SettingsHelperManager.GetParametrDirection(item.Direction)
};
cmd.Parameters.Add(param);
}
_reader = cmd.ExecuteReader();
while (_reader.Read())
{
container.SetFromReader(ref _reader);
}
// } end of using
if (datasource.ConnectionLevel == ConnectionLevel.Single && con != null)//&& con.State!=ConnectionState.Closed)
{
con.Dispose();
}
//set outputparams values
if (proc.HasOutputParam)
{
foreach (DataParam value in proc.OutputParams.Values)
{
value.Value = cmd.Parameters[value.Name].Value.ToString();
}
}
if (cmd.Parameters.Count > 0)
{
List <DataParam> ret = proc.GetparamsByDirection(ParamDirection.Return);
if (ret.Count > 0)
{
if (cmd.Parameters[ret[0].Name] != null)
{
result.SetCode(Convert.ToInt32(cmd.Parameters[proc.GetparamsByDirection(ParamDirection.Return)[0].Name].Value));
}
}
}
}
catch (Exception exc)
{
throw exc;
}
result.StopMeashure();
return(result);
}
public static async Task <ExecAsyncResult> ExecuteDataReaderByRefAsync <T>(this DataSItem item, DbAgent agent)
{
ExecAsyncResult result = new ExecAsyncResult();
result.ExecutedProcedure = item;
IBag <T> container;
if (agent != null && agent.State)
{
// return ExecuteProcedureDRByReflection<T>(item, out container, agent.ConnectionString);
//---
SqlCommand cmd = null;
container = new RefBag <T>();
result.StartMeasure();
try
{
//using (_conn = new SqlConnection(constr))
//{
SqlConnection con = null;
if (agent.ConnectionLevel == ConnectionLevel.Single)
{
con = agent.CreateConnection();
await con.OpenAsync();
// con = agent.CreateConnection();
//_shouldBeSync use
}
else if (agent.ConnectionLevel == ConnectionLevel.AllInOne)
{
con = agent.Connection;
}
cmd = new SqlCommand(item.Value, con)
{
CommandType = CommandType.StoredProcedure,
CommandTimeout = agent.RunTimeout
};
if (agent.TransactionState == TransactionState.ActiveTransaction)
{
cmd.Transaction = agent.Transaction;
}
SqlParameter param;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var itm in item.Params.Values)
{
param = new SqlParameter();
param.ParameterName = itm.Name;
param.Value = itm.Value;
param.SqlDbType = SettingsHelperManager.DetermineSqlDbTYpe(itm.Type);
param.Direction = SettingsHelperManager.GetParametrDirection(itm.Direction);
cmd.Parameters.Add(param);
}
using (_reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await _reader.ReadAsync().ConfigureAwait(false))
{
container.SetFromReader(ref _reader);
}
}
//set outputparams values
if (item.HasOutputParam)
{
foreach (DataParam value in item.OutputParams.Values)
{
value.Value = cmd.Parameters[value.Name].Value.ToString();
}
}
if (cmd.Parameters.Count > 0)
{
List <DataParam> ret = item.GetparamsByDirection(ParamDirection.Return);
if (ret.Count > 0)
{
if (cmd.Parameters[ret[0].Name] != null)
{
result.Result.SetCode(
Convert.ToInt32(
cmd.Parameters[item.GetparamsByDirection(ParamDirection.Return)[0].Name].Value));
}
}
}
// } end of using
if (agent.ConnectionLevel == ConnectionLevel.Single && con != null)
{
con.Dispose();
}
result.Object = container;
}
catch (Exception exc)
{
throw exc;
}
result.StopMeasure();
result.ExecutionType = AsyncExecutionType.ExecByRef;
return(result);
//----
}
else
{
throw new Exception("Agent is null or the state is false ");
}
}
/// <summary>
/// Changed!
/// </summary>
/// <param name="item"></param>
/// <param name="agent"></param>
/// <returns></returns>
public static async Task <ExecAsyncResult> ExecuteNonQueryAsync(this DataSItem item, DbAgent agent)
{
//return Task.Factory.StartNew(() =>
//{
// ExecAsyncResult result = new ExecAsyncResult();
// ExecResult rs = ExecuteNonQuery(item, agent);
// result.ExecutedProcedure = item;
// result.Result = rs;
// return result;
//});
ExecAsyncResult result = new ExecAsyncResult();
result.ExecutedProcedure = item;
if (agent != null && agent.State)
{
///Open Connection via agent
SqlConnection con = null;
result.StartMeasure();
if (agent.ConnectionLevel == ConnectionLevel.Single)
{
con = agent.CreateConnection();
await con.OpenAsync();
}
else if (agent.ConnectionLevel == ConnectionLevel.AllInOne)
{
con = agent.Connection;
}
using (SqlCommand cmd = new SqlCommand(item.Value, con))
{
if (agent.TransactionState == TransactionState.ActiveTransaction)
{
cmd.Transaction = agent.Transaction;
}
SqlParameter param;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = agent.RunTimeout;
foreach (var itm in item.Params.Values)
{
param = new SqlParameter();
param.ParameterName = itm.Name;
param.Value = itm.Value;
param.SqlDbType = SettingsHelperManager.DetermineSqlDbTYpe(itm.Type);
param.Direction = SettingsHelperManager.GetParametrDirection(itm.Direction);
cmd.Parameters.Add(param);
}
result.Result.AffectedRows = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
//set outputparams values
if (item.HasOutputParam)
{
foreach (DataParam value in item.OutputParams.Values)
{
value.Value = cmd.Parameters[value.Name].Value.ToString();
}
}
if (cmd.Parameters.Count > 0)
{
List <DataParam> returnparam = item.GetparamsByDirection(ParamDirection.Return);
if (returnparam.Count > 0)
{
string name = returnparam[0].Name;
if (cmd.Parameters[name] != null)
{
result.Result.SetCode(Convert.ToInt32(cmd.Parameters[name].Value));
}
}
}
}
if (agent.ConnectionLevel == ConnectionLevel.Single && con != null)
{
con.Dispose();
}
result.StopMeasure();
}
else
{
throw new Exception("Agent is null or state is false ");
}
return(result);
}
public static async Task <ExecAsyncResult> ExecDataSetAsync(this DataSItem item, DbAgent agent)
{
return(await Task.Factory.StartNew(() =>
{
ExecAsyncResult result = new ExecAsyncResult();
DataSet rsSet;
ExecResult rs = ExecDataSet(item, agent, out rsSet);
result.ExecutedProcedure = item;
result.Result = rs;
result.Object = rsSet;
result.ExecutionType = AsyncExecutionType.ExecDataSet;
return result;
}));
}