private static void ConfigureDatabase(IJSONReaderRequestBody jsonReaderRequestBody, SqlConnection connection)
{
if (!string.IsNullOrEmpty(jsonReaderRequestBody.Database))
{
connection.ChangeDatabase(jsonReaderRequestBody.Database);
}
}
private void DebugJsonReaderRequestBody(IJSONReaderRequestBody jsonReaderRequestBody, string xmlIn)
{
StringBuilder debugText = new StringBuilder();
debugText.AppendLine(string.Format("USE {0}", jsonReaderRequestBody.Database));
debugText.AppendLine("GO");
debugText.AppendLine("DECLARE @XmlOut XML");
debugText.Append("EXEC ");
debugText.Append(jsonReaderRequestBody.Procedure);
debugText.Append(" '");
debugText.Append(xmlIn);
debugText.Append("', @XmlOut OUT");
debugText.AppendLine(Environment.NewLine);
Debug.WriteLine(debugText);
}
public string GetJson(IJSONReaderRequestBody jsonReaderRequestBody)
{
string jsonResultSet = string.Empty;
StringBuilder jsonExtraResultSet = new StringBuilder();
int extraResultSetCount = 0;
string xmlIn = BuildXmlIn(jsonReaderRequestBody);
#if DEBUG
DebugJsonReaderRequestBody(jsonReaderRequestBody, xmlIn);
#endif
string xmlOut = string.Empty;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(jsonReaderRequestBody.Procedure, connection))
{
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(SQL_PARAMETER_XML_IN, SqlDbType.Xml).Value = xmlIn;
command.Parameters.Add(SQL_PARAMETER_XML_OUT, SqlDbType.Xml).Direction = ParameterDirection.Output;
command.Connection = connection;
command.Connection.Open();
ConfigureDatabase(jsonReaderRequestBody, connection);
SqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
using (SqlDataReader dataReader = command.ExecuteReader())
{
try
{
jsonResultSet = DataReaderToJson(dataReader, jsonReaderRequestBody.TokenData);
while (!dataReader.IsClosed && dataReader.NextResult())
{
extraResultSetCount++;
jsonExtraResultSet.Append(string.Format("\t\t\t\t\t\t,\"resultSet{0}\" : ", extraResultSetCount));
jsonExtraResultSet.Append(DataReaderToJson(dataReader, jsonReaderRequestBody.TokenData));
jsonExtraResultSet.Append(Environment.NewLine);
#if DEBUG
//Em caso do funcionamento em DEBUG, ele coloca o xml de saída para ser utilizado na camada cliente.
#endif
}
if (extraResultSetCount > 0)
{
StringBuilder tempString = new StringBuilder();
tempString.AppendLine(jsonResultSet.Substring(0, jsonResultSet.LastIndexOf("}") - 1));
tempString.AppendLine(jsonExtraResultSet.ToString());
tempString.AppendLine(string.Concat(",\"resultSetsCount\" : ", extraResultSetCount));
jsonExtraResultSet.Append(Environment.NewLine);
tempString.AppendLine("}");
jsonResultSet = tempString.ToString();
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
xmlOut = command.Parameters[SQL_PARAMETER_XML_OUT].Value.ToString();
#if DEBUG
jsonResultSet = string.Concat(jsonResultSet, "\n\n/*\n", XDocument.Parse(command.Parameters[SQL_PARAMETER_XML_OUT].Value.ToString()).ToString(), "\n\n*/");
#endif
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (dataReader != null)
{
if (!dataReader.IsClosed)
{
dataReader.Close();
}
dataReader.Dispose();
}
}
}
command.Connection.Close();
try
{
jsonReaderRequestBody.OnProcess?.Invoke(XDocument.Parse(xmlOut));
}
catch (Exception ex)
{
}
}
}
return(jsonResultSet);
}
private string BuildXmlIn(IJSONReaderRequestBody jsonReaderRequestBody)
{
Dictionary <string, object> parametros = new Dictionary <string, object>();
Dictionary <string, SqlDbType> tipos = new Dictionary <string, SqlDbType>();
XElement xPostData = null;
XElement xParametros = new XElement(NODE_PARAMS);
GetParametros(jsonReaderRequestBody.Params, parametros, tipos);
for (var i = 0; i < parametros.Count; i++)
{
xParametros.Add(new XElement(parametros.ElementAt(i).Key, parametros.ElementAt(i).Value.ToString()));
}
XElement xHeaders = new XElement(NODE_HEADERS);
for (var i = 0; i < jsonReaderRequestBody.Headers.Count; i++)
{
string key = jsonReaderRequestBody.Headers.AllKeys[i];
xHeaders.Add(new XElement(key, jsonReaderRequestBody.Headers[key]));
}
XElement xServerVariables = new XElement(NODE_SERVER_VARIABLES);
if (jsonReaderRequestBody is SQLJSONReaderRequestBody)
{
((SQLJSONReaderRequestBody)jsonReaderRequestBody).ValidateServerVariables();
}
var serverVariables = jsonReaderRequestBody.ServerVariables.AllKeys.ToList();
for (var i = 0; i < serverVariables.Count; i++)
{
string key = serverVariables[i];
xServerVariables.Add(new XElement(key, jsonReaderRequestBody.ServerVariables[key]));
}
try
{
if (!string.IsNullOrEmpty(jsonReaderRequestBody.PostData))
{
xPostData = XDocument.Parse(jsonReaderRequestBody.PostData).Root;
}
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(jsonReaderRequestBody.PostData))
{
xPostData = new XElement("postData", jsonReaderRequestBody.PostData);
}
}
XElement xAuditoria = new XElement(NODE_AUDITORIA,
new XElement(NODE_USUARIO_ID, jsonReaderRequestBody.UserId),
new XElement(NODE_USUARIO_EMPRESA_ID, jsonReaderRequestBody.UserCompanyId)
);
if (!string.IsNullOrEmpty(jsonReaderRequestBody.Sort))
{
var tempSort = new StringBuilder();
dynamic sortItems = JsonConvert.DeserializeObject(jsonReaderRequestBody.Sort);
foreach (var sortItem in sortItems)
{
tempSort.Append(string.Concat(sortItem.property, " ", sortItem.direction, ","));
}
jsonReaderRequestBody.Sort = tempSort.ToString();
jsonReaderRequestBody.Sort = jsonReaderRequestBody.Sort.Substring(0, jsonReaderRequestBody.Sort.Length - 1);
}
string xmlIn = new XDocument(new XElement(NODE_BS,
new XElement(NODE_CONFIG,
new XElement(NODE_PAGE, jsonReaderRequestBody.Page),
new XElement(NODE_LIMIT, jsonReaderRequestBody.Limit),
new XElement(NODE_START, jsonReaderRequestBody.Start),
new XElement(NODE_GROUP, jsonReaderRequestBody.Group),
new XElement(NODE_SORT, jsonReaderRequestBody.Sort),
new XElement(NODE_QUERY, jsonReaderRequestBody.Query),
new XElement(NODE_FILTER, jsonReaderRequestBody.Filter)
),
xAuditoria,
xPostData,
xParametros,
xHeaders,
xServerVariables
)
).ToString(SaveOptions.None);
return(xmlIn);
}
