internal List <Picklist> GetValuesInDb(string serverInstance, string database)
{
List <Picklist> existingValuesInDb = new List <Picklist>();
string sql = "SELECT a.PicklistName, b.PicklistValue FROM PnPPicklists a LEFT JOIN PnPPicklistValues b ON a.PnPID = b.PicklistId";
try
{
string connectionString = GetConnectionString(serverInstance, database);
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Picklist picklist = new Picklist(dr["PicklistName"].ToString(), dr["PicklistValue"].ToString());
existingValuesInDb.Add(picklist);
}
}
}
con.Close();
}
return(existingValuesInDb);
}
catch (Exception ex)
{
throw ex;
}
}
internal List <Picklist> GetValuesInCsvFile(string csvFile)
{
try
{
if (csvFile == string.Empty)
{
throw new Exception("Csv file must all be provided");
}
if (!File.Exists(csvFile))
{
throw new Exception("Csv file does not exist");
}
List <Picklist> csvValues = new List <Picklist>();
using (var reader = new StreamReader(csvFile))
{
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
Picklist picklist = new Picklist(values[1], values[0]);
csvValues.Add(picklist);
}
csvValues.RemoveAt(0);
}
csvValues.RemoveAll(x => x.Value == "");
csvValues = csvValues.Where(s => !string.IsNullOrWhiteSpace(s.Value)).ToList();
csvValues = csvValues.Distinct(new PicklistComparer()).ToList();
csvValues = csvValues.OrderBy(x => x.Name).ThenBy(x => x.Value).ToList();
return(csvValues);
}
catch (Exception ex)
{
throw ex;
}
}