/*
* //
* public DataTable GetFieldMetaDataRecord(int fieldId)
* {
* SqlCommand com = DataAccessHelper.CreateCommand("spGetFieldMetadataRecord");
* DataAccessHelper.AddIntInputParam(com,"FieldId",fieldId);
* DataSet ds = DataAccessHelper.GetRecord(com);
*
* return ds.Tables[0];
* }
*/
//
/*
* public DataTable GetFieldValues(string tableName, string fieldName)
* {
* string sql = "SELECT " + tableName + "." + fieldName + " theValue, "
+ "Count(" + tableName + "." + fieldName + ") Count "
+ "FROM " + tableName + " "
+ "GROUP BY " + tableName + "." + fieldName + " "
+ "ORDER BY Count(" + tableName + "." + fieldName + ") DESC;";
+
+ SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
+ DataSet ds = DataAccessHelper.GetRecord(com);
+
+ return ds.Tables[0];
+ }
*/
public DataTable GetFieldValues(int fieldId, string tableName, string fieldName)
{
MetadataNewDa MNDa = new MetadataNewDa();
bool isVirtual = MNDa.MetaTableFieldIsVirtual(fieldId);
string sql;
if (isVirtual)
{
sql = "SELECT FieldValue theValue, Count(FieldValueId) Count "
+ "FROM MetadataFieldValues WHERE FieldId = " + fieldId + " GROUP BY FieldValue "
+ "ORDER BY Count(FieldValueId) DESC;";
}
else
{
sql = "SELECT " + tableName + "." + fieldName + " theValue, "
+ "Count(" + tableName + "." + fieldName + ") Count "
+ "FROM " + tableName + " "
+ "GROUP BY " + tableName + "." + fieldName + " "
+ "ORDER BY Count(" + tableName + "." + fieldName + ") DESC;";
}
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataSet ds = DataAccessHelper.GetRecord(com);
return(ds.Tables[0]);
}
public void recode(string table, string field, string theValue, string recodeTo, int fieldId)
{
//implicit data type conversion in SQL server
MetadataNewDa MNDa = new MetadataNewDa();
bool isVirtual = MNDa.MetaTableFieldIsVirtual(fieldId);
string sql;
if (!isVirtual)
{
sql = "UPDATE " + table + " ";
if ((recodeTo == null) || (recodeTo == "Null"))
{
sql = sql + "SET " + table + "." + field + " = Null ";
}
else
{
sql = sql + "SET " + table + "." + field + " = '" + recodeTo + "' ";
}
sql = sql + " WHERE (((" + table + "." + field + ")='" + theValue + "'))";
}
else
{
sql = "UPDATE MetadataFieldValues ";
if ((recodeTo == null) || (recodeTo == "Null"))
{
sql = sql + " SET FieldValue = Null ";
}
else
{
sql = sql + " SET FieldValue = '" + recodeTo + "' ";
}
sql = sql + " WHERE (FieldValue = '" + theValue + "' AND FieldId = " + fieldId + ") ";
}
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.ExecuteScalar(com);
}