//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); }