DataSet IXapDataProvider.ExecuteDataSet()
{
try {
OpenConnection();
XapDataReader dr = new XapDataReader(_dbCommand.ExecuteReader());
DataSet dataSet = new DataSet();
DataTable dataTable = null;
do
{
dataTable = new DataTable();
AddDataTableColumns(dataTable, dr);
dataSet.Tables.Add(dataTable);
while (dr.Read())
{
AddDataRow(dataTable, dr);
}
} while (dr.NextResult());
return(dataSet);
} catch (Exception ex) {
throw new XapException($"Error executing dataset for {_dbConnectionContext.DbKey}", ex);
} finally {
((IXapDataProvider)this).CloseConnection();
}
}
private void AddDataTableColumns(DataTable dataTable, XapDataReader dr)
{
for (int x = 0; x <= dr.FieldCount - 1; x++)
{
DataColumn column = new DataColumn(dr.GetName(x), dr.GetFieldType(x));
dataTable.Columns.Add(column);
}
}
XmlDocument IXapDataProvider.ExecuteXml()
{
ArrayList columnNames = null;
try {
OpenConnection();
XapDataReader dr = new XapDataReader(_dbCommand.ExecuteReader());
XmlDocument xDoc = new XmlDocument();
XmlElement rootNode = xDoc.CreateElement("ResultSets");
xDoc.AppendChild(rootNode);
do
{
columnNames = new ArrayList(dr.FieldCount);
for (int x = 0; x <= dr.FieldCount - 1; x++)
{
columnNames.Add(dr.GetName(x));
}
string obj = string.Empty;
XmlElement resultNode = xDoc.CreateElement("ResultSet");
XmlElement rowNode = null;
XmlElement columnNode = null;
while (dr.Read())
{
rowNode = xDoc.CreateElement("Row");
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
columnNode = xDoc.CreateElement(columnNames[i].ToString());
obj = dr.GetValue(i).ToString().Trim();
if (obj == null || string.IsNullOrEmpty(obj.ToString()))
{
obj = string.Empty;
}
columnNode.InnerText = obj.ToString();
rowNode.AppendChild(columnNode);
}
resultNode.AppendChild(rowNode);
}
rootNode.AppendChild(resultNode);
} while (dr.NextResult());
return(xDoc);
} catch (Exception ex) {
throw new XapException($"Error executing xml for {_dbConnectionContext.DbKey}", ex);
} finally {
((IXapDataProvider)this).CloseConnection();
}
}
string IXapDataProvider.ExecuteJson()
{
StringBuilder json = new StringBuilder();
string retVal = string.Empty;
try {
OpenConnection();
XapDataReader dr = new XapDataReader(_dbCommand.ExecuteReader());
json.AppendLine("{\"data\":[");
do
{
string obj = string.Empty;
string colName = string.Empty;
while (dr.Read())
{
json.AppendLine("{");
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
obj = dr.GetValue(i).ToString().Trim();
colName = dr.GetName(i);
if (obj == null || string.IsNullOrEmpty(obj.ToString()))
{
obj = string.Empty;
}
json.AppendLine(string.Format("\"{0}\":\"{1}\",", colName, obj.ToString()));
}
json = json.RemoveLast(",");
json.AppendLine("},");
}
} while (dr.NextResult());
json = json.RemoveLast(",");
json.AppendLine("]}");
retVal = json.ToString();
return(retVal);
} catch (Exception ex) {
throw new XapException($"Error executing json for {_dbConnectionContext.DbKey}", ex);
} finally {
((IXapDataProvider)this).CloseConnection();
}
}
DataTable IXapDataProvider.ExecuteDataTable()
{
try {
OpenConnection();
XapDataReader dr = new XapDataReader(_dbCommand.ExecuteReader());
DataTable dataTable = new DataTable();
AddDataTableColumns(dataTable, dr);
while (dr.Read())
{
AddDataRow(dataTable, dr);
}
return(dataTable);
} catch (Exception ex) {
throw new XapException($"Error executing datatable for {_dbConnectionContext.DbKey}", ex);
} finally {
((IXapDataProvider)this).CloseConnection();
}
}
private void AddDataRow(DataTable dataTable, XapDataReader dr)
{
object obj = null;
Type t = null;
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
obj = dr.GetValue(i);
t = dr.GetFieldType(i);
if (obj == null || string.IsNullOrEmpty(obj.ToString()))
{
dataRow[i] = t.GetDefaultValue();
}
else
{
dataRow[i] = dr.GetValue(i);
}
}
dataTable.Rows.Add(dataRow);
}
//TODO: Look at injecting XapEnvironment class into DbProvider
void IXapDataProvider.ExecuteCsv(string filePath)
{
StringBuilder sb = new StringBuilder();
StreamWriter _logger = null;
string tmp = string.Empty;
try {
_logger = new StreamWriter(XapEnvironment.Instance.MapFolderPath(filePath), false);
OpenConnection();
XapDataReader dr = new XapDataReader(_dbCommand.ExecuteReader());
for (int x = 0; x < dr.FieldCount; x++)
{
if (x + 1 < dr.FieldCount)
{
_logger.Write("\"" + dr.GetName(x) + "\"" + ",");
}
else
{
_logger.Write("\"" + dr.GetName(x) + "\"\n");
}
}
string obj = string.Empty;
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
obj = dr.GetValue(i).ToString();
if (obj == null || string.IsNullOrEmpty(obj.ToString()))
{
if (i + 1 < dr.FieldCount)
{
_logger.Write("\"" + string.Empty + "\"" + ",");
}
else
{
_logger.Write("\"" + string.Empty + "\"\n");
}
}
else
{
if (i + 1 < dr.FieldCount)
{
_logger.Write("\"" + dr.GetValue(i).ToString().TrimEnd() + "\"" + ",");
}
else
{
_logger.Write("\"" + dr.GetValue(i).ToString().TrimEnd() + "\"\n");
}
}
}
}
} catch (Exception ex) {
throw new XapException($"Error executing csv for {_dbConnectionContext.DbKey}", ex);
} finally {
_logger.Close();
((IXapDataProvider)this).CloseConnection();
}
}
