/// <summary>
/// Check exist StoreProcedure in Database
/// </summary>
/// <param name="operation">Get a relative StoreProcedureName</param>
/// <returns>Boolean result: if true exist</returns>
public bool CheckExistStoreProcedure(DBOperationEnum operation)
{
try
{
if (!FlyConfiguration.StoreProcedureSettings.Exists(s => s.Operation == operation))
{
return(false);
}
string onlynameSP = FlyConfiguration.StoreProcedureSettings.Find(s => s.Operation == operation).StoreProcedureName;
if (string.IsNullOrEmpty(onlynameSP))
{
return(false);
}
if (onlynameSP.Contains(".") && onlynameSP.Split('.').Length > 1)
{
onlynameSP = onlynameSP.Split('.')[1];
}
string checkSP = String.Format("select count(*) from sys.objects where type = 'p' and name='{0}'",
onlynameSP);
_conn = new SqlConnection();
_conn.ConnectionString = this.ConnectionString;
_conn.Open();
SqlCommand cmd = new SqlCommand(checkSP, _conn);
bool Exist = Convert.ToInt32(cmd.ExecuteScalar()) > 0;
_conn.Close();
return(Exist);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// Performs correct store procedures and it process results into Table
/// </summary>
/// <param name="operation">Operations Type (indicates the store procedure to execute)</param>
/// <param name="parameter">Parameters to be passed to the store procedure</param>
/// <returns>Returns a Table of response</returns>
public DataTable ExecutetoTable(DBOperationEnum operation, List <IParameterValue> parameter)
{
try
{
_conn = new SqlConnection();
_conn.ConnectionString = this.ConnectionString;
_conn.Open();
SqlCommand cmd = new SqlCommand(GetStoreRef(operation), _conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameter != null)
{
parameter.ForEach(p => cmd.Parameters.Add(((ParameterValue)p).CreateParameter()));
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable RisTable = new DataTable();
da.Fill(RisTable);
_conn.Close();
return(RisTable);
}
catch (SdmxException) { throw; }
catch (Exception e)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
}
}
/// <summary>
/// Performs correct store procedures and it process results
/// </summary>
/// <param name="operation">Operations Type (indicates the store procedure to execute)</param>
/// <param name="parameter">Parameters to be passed to the store procedure</param>
/// <returns>Returns a list of xmlNode with which the builder that identifies the response</returns>
public List <XmlNode> Execute(DBOperationEnum operation, List <IParameterValue> parameter)
{
try
{
_conn = new SqlConnection();
_conn.ConnectionString = this.ConnectionString;
_conn.Open();
SqlCommand cmd = new SqlCommand(GetStoreRef(operation), _conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameter != null)
{
parameter.ForEach(p => cmd.Parameters.Add(((ParameterValue)p).CreateParameter()));
}
StringBuilder dbres = new StringBuilder();
using (SqlDataReader rea = cmd.ExecuteReader())
{
while (rea.Read())
{
dbres.Append(rea.GetValue(0).ToString());
}
}
// cmd.ExecuteScalar();//perche ritorna sempre un valore E'LIMITATO A 2033 CARATTERI
_conn.Close();
if (dbres == null || string.IsNullOrEmpty(dbres.ToString()))
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse);
}
XmlDocument risposta = new XmlDocument();
risposta.LoadXml(dbres.ToString());
if (risposta.ChildNodes.Count > 0)
{
StringBuilder Par = new StringBuilder();
foreach (var item in parameter)
{
Par.AppendLine(String.Format("{0} \t\t-> {1}", item.Item, item.Value));
}
FlyLog.WriteLog(this, FlyLog.LogTypeEnum.All, "Execution Store procedure {0} for {1} Successfully. Reading results {2}", cmd.CommandText, operation.ToString(), Par);
return(new List <XmlNode>(risposta.ChildNodes[0].ChildNodes.Cast <XmlNode>()));
}
else
{
FlyLog.WriteLog(this, FlyLog.LogTypeEnum.Error, "Execution Store procedure {0} for {1}. NO RESULT", cmd.CommandText, operation.ToString());
}
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse);
}
catch (SdmxException) { throw; }
catch (Exception e)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
}
}
/// <summary>
/// Mapping enum DBOperationEnum, with names in the store procedure to execute
/// </summary>
/// <param name="operation">DBOperationEnum value to convert in StoreProcedure name</param>
/// <returns>Name of StoreProcedure</returns>
private string GetStoreRef(DBOperationEnum operation)
{
try
{
if (FlyConfiguration.StoreProcedureSettings.Exists(s => s.Operation == operation))
{
return(FlyConfiguration.StoreProcedureSettings.Find(s => s.Operation == operation).StoreProcedureName);
}
else
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.InternalError, new Exception("DB Operation not recognized"));
}
}
catch (SdmxException) { throw; }
catch (Exception ex)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.InternalError, ex);
}
}
/// <summary>
/// 操作类型，读或写
/// </summary>
/// <param name="DbType">the database type, if t is Default, the value in appsettings will be used</param>
/// <param name="Operation">Operation</param>
/// <param name="CsName">the key of the ConnectionString in appsettings</param>
public FixConnectionAttribute(DBTypeEnum DbType, DBOperationEnum Operation = DBOperationEnum.Default, string CsName = "")
{
this.CsName = CsName;
this.Operation = Operation;
this.DbType = DbType;
}
/// <summary>
/// 新建固定连接字符串标记
/// </summary>
/// <param name="Operation">Operation</param>
/// <param name="CsName">连接字符串名称</param>
public FixConnectionAttribute(DBOperationEnum Operation = DBOperationEnum.Default, string CsName = "")
{
this.CsName = CsName;
this.Operation = Operation;
}
