/// <summary>
/// Call DB query
/// </summary>
/// <param name="inputParameter"></param>
/// <param name="outputParameter"></param>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public MemoryStream CallDBquery(InputParameter inputParameter, OutputParameter outputParameter, string sqlQuery)
{
var xmlUtility = new XmlUtility();
sqlQuery = sqlQuery.Replace("\n", string.Empty);
if (Connection.State != System.Data.ConnectionState.Open)
{
Connection.Open();
}
using (var cmd = Connection.CreateCommand())
{
cmd.Connection = Connection;
cmd.CommandText = sqlQuery;
cmd.CommandTimeout = 300;
cmd.CommandType = System.Data.CommandType.Text;
foreach (var item in inputParameter.Properties)
{
cmd.Parameters.AddWithValue(string.Format("@{0}", item.PropertyName), item.PropertyValue);
}
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
foreach (var item in outputParameter.Properties)
{
item.PropertyValue = reader[item.PropertyName].ToString();
}
}
reader.Close();
reader.Dispose();
}
}
var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties);
return stream;
}
/// <summary>
/// Run Procedure
/// </summary>
/// <param name="storeProcName"></param>
/// <param name="parameters"></param>
/// <param name="inputParameter"></param>
/// <returns></returns>
private InputParameter RunProcedure(string storeProcName, List<SqlParameter> parameters, InputParameter inputParameter)
{
if (Connection.State != System.Data.ConnectionState.Open)
{
Connection.Open();
}
SqlCommand command = BuildQueryCommand(storeProcName, parameters);
command.ExecuteNonQuery();
foreach (var item in inputParameter.Properties)
{
item.PropertyValue = command.Parameters[string.Format("@{0}", item.PropertyName)].Value.ToString();
}
Connection.Close();
return inputParameter;
}
/// <summary>
/// Call Stored Proc
/// </summary>
/// <param name="inputParameter"></param>
/// <param name="outputParameter"></param>
/// <param name="storeProcName"></param>
/// <returns></returns>
public MemoryStream CallStoredProcedure(InputParameter inputParameter, OutputParameter outputParameter, string storedProcName)
{
List<SqlParameter> parameters = new List<SqlParameter>();
bool isOutput = inputParameter.Properties.Where(t => t.PropertyParameterType.Contains("True")).Any();
foreach (var item in inputParameter.Properties)
{
SqlParameter pa = CreateNewParameter(item.PropertyName,item.PropertyValue,item.PropertyParameterType,item.PropertyType,item.PropertyLength);
parameters.Add(pa);
}
if (isOutput || outputParameter.Properties.Count() == 0)
{
inputParameter = RunProcedure(storedProcName, parameters, inputParameter);
}
else
{
var reader = RunProcedure(storedProcName, parameters);
while (reader.Read())
{
foreach (var item in outputParameter.Properties)
{
item.PropertyValue = reader[item.PropertyName].ToString();
}
}
reader.Close();
reader.Dispose();
}
var xmlUtility = new XmlUtility();
var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties);
return stream;
}