public List <string> GetRandomColumns(string table, string column, int numToReturn) { SqlConnection connection = GetConnection(); List <int> usedRandomNumbers = new List <int>(); string databaseAndTable = _middleDatabaseString + table; SqlDataReader myReader = null; int numRows = GetNumberOfRowsInTable(databaseAndTable); List <string> listToReturn = new List <string>(); while (listToReturn.Count < numToReturn) { int randomNum = RandomNumberGeneratorUtilities.GetRandomNumber(numRows); while (usedRandomNumbers.Contains(randomNum)) { randomNum = RandomNumberGeneratorUtilities.GetRandomNumber(numRows); } usedRandomNumbers.Add(randomNum); SqlCommand myCommand = new SqlCommand($"select top 1 {column} from {databaseAndTable} where {column} " + $"in (select top {randomNum} {column} from {databaseAndTable} order " + $"by {column} asc) order by {column} desc", connection); myReader = myCommand.ExecuteReader(); myReader.Read(); listToReturn.Add(myReader[column].ToString()); myReader.Close(); } CloseConnection(connection); return(listToReturn); }
public Dictionary <string, string> GetColumnsFromRandomRow(string table, List <string> columns) { SqlConnection connection = GetConnection(); string databaseAndTable = _middleDatabaseString + table; SqlDataReader myReader = null; Dictionary <string, string> dictionaryToReturn = new Dictionary <string, string>(); int randomNum = RandomNumberGeneratorUtilities.GetRandomNumber(GetNumberOfRowsInTable(databaseAndTable)); foreach (string column in columns) { SqlCommand myCommand = new SqlCommand($"select top 1 {column} from {databaseAndTable} where {column} " + $"in (select top {randomNum} {column} from {databaseAndTable} order " + $"by {column} asc) order by {column} desc", connection); myReader = myCommand.ExecuteReader(); myReader.Read(); string strippedColumnName = column.Substring(1, column.Length - 2); dictionaryToReturn.Add(strippedColumnName, myReader[strippedColumnName].ToString()); myReader.Close(); } CloseConnection(connection); return(dictionaryToReturn); }