//this function serves data service
//OData feed has only rudimentary SQL server tables/views metadata
public sqlEntitySetMetadata sqlGetEntitySetMetaData(string entitySet)
{
DataTable reader = GetReader(@"SELECT * FROM odpdataservicemetadata WHERE EntitysetName='" + entitySet + "';");
sqlEntitySetMetadata md = new sqlEntitySetMetadata();
if (reader.Rows.Count == 0)
{
throw (new Exception(string.Format("Entity {0} is not confirgured in meta data table.", entitySet)));
}
foreach (DataRow row in reader.Rows)
{
md.EntitysetName = ObjToString(entitySet);
md.TableName = ObjToString(row["TableName"]);
md.RowId = ObjToString(row["RowId"]);
md.Longitude = ObjToString(row["Longitude"]);
md.Latitude = ObjToString(row["Latitude"]);
md.KmlSnippet = ObjToString(row["KmlSnippet"]);
md.KmlName = ObjToString(row["KmlName"]);
md.KmlDescription = ObjToString(row["KmlDescription"]);
}
return(md);
}
public DataTable GetReader(sqlEntitySetMetadata md, string columns, string WCFquery, int ntop, string nextRowKey, int skip, string order)
{
MySqlConnection connection = null;
DataTable retVal = new DataTable();
string sql = string.Empty;
string sColumns = string.Empty;
string sOrder = string.Empty;
string sWhere = WcfToSqlFilter(WCFquery); // inner where statement for the filter from the url, odprownumber clause will be in outer query
if (!string.IsNullOrEmpty(sWhere))
{
sWhere = " WHERE (" + sWhere + ")";
}
if (!string.IsNullOrEmpty(columns))
{
sColumns = columns;
}
else
{
sColumns = getColumnNames(md.TableName);
}
if (!string.IsNullOrEmpty(order))
{
sOrder = order;
}
else
{
sOrder = md.RowId;
}
bool isRowId = !string.IsNullOrEmpty(md.RowId);
if (!isRowId)
{
throw new Exception("RowId not defined for entity set '" + md.EntitysetName + "'.");
}
if (ntop == -1)
{
sql = string.Format(
@"
SELECT {0}
FROM {1}
{2}
ORDER BY {3};
", sColumns, md.TableName, sWhere, sOrder);
}
else
{
sql = string.Format(
@"
SELECT {0}
FROM {1}
{2}
ORDER BY {3}
LIMIT {4}, {5};
", sColumns, md.TableName, sWhere, sOrder, skip, ntop);
}
try
{
MySqlDataAdapter adapt = new MySqlDataAdapter();
connection = new MySqlConnection(connStr);
MySqlCommand query = new MySqlCommand();
query.Connection = connection;
query.CommandText = sql;
adapt.SelectCommand = query;
adapt.Fill(retVal);
}
catch (Exception ex)
{
Odp.Data.ErrorLog.WriteError(ex.Message);
}
finally
{
connection.Close();
}
return(retVal);
}
