I keep on getting the error: Item has already been added. Key in dictionary: '@QueryID' Key being added: '@QueryID'. I did some research on this and it tells me that I'm inserting duplicate keys into a dictionary object. Dictionary objects in .Net can only have unique keys. I don't know how to get rid of duplicate keys.
class Program
{
private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
static void Main()
{
EMMADatabase db = new EMMADatabase(""); //set string to pass in based on App.config
try
{
Hashtable Parameters = new Hashtable();
//Parameters.Add("@UserCredentials", UserCredentials);
DataTable result = db.Query("exec Metrics_ETL.dbo.uspDetermineQueriesToRunBasedOnSchedules", Parameters);
DataTable QueriesToRun = db.Query("select QueryID from Metrics_ETL.dbo.QueriesToRun", Parameters);
foreach(DataRow row in QueriesToRun.Rows)
{
Hashtable QueryParameters = new Hashtable();
Parameters.Add("@QueryID", row["QueryID"]);
DataTable QueryDetails = db.Query("select QueryID, Query, ObjectID, DataSourceID, DatabaseName, ServerName, ServerType, UserName, Password from Metrics_ETL.dbo.Queries where QueryID = @QueryID", Parameters);
//log.Info(row["QueryID"]);
//Console.WriteLine(row["QueryID"]);
//create datasource - query datasource - get data into a datatable
//write datatable to csv with correct filename (OR maybe C# can write to Excel)
}
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
Console.Read();
}
}
I think this is where the @QueryID value is added, but wasn't sure. I am a beginner to programming so I am very noob at this. Any suggestion would greatly be appreciated.
private DataTable QueryToDataTable(string QueryString, Hashtable Parameters)
{
DataTable dt = new DataTable();
using (new Impersonator("svc-emma-admin", "1dc", "618MId}QM"))
{
SqlCommand cmd = new SqlCommand(QueryString, DBConnection);
cmd.CommandTimeout = 120;
foreach (DictionaryEntry Parameter in Parameters)
{
cmd.Parameters.Add((string)Parameter.Key, SqlDbType.NVarChar);
cmd.Parameters[(string)Parameter.Key].Value = Parameter.Value;
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
return dt;
}
You've created a single Hashtable
outside your loop, then one on every iteration inside your loop. However, you're unconditionally adding to the single "outer" hashtable within the loop, so on the second iteration it's bound to fail.
You're then ignoring the "inner" hashtable entirely.
I suspect you want to get rid of the "outer" hashtable, and just use the "inner" one (QueryParameters
) when both adding the parameter and specifying the parameters for the query.
As an aside:
Dictionary<string, object>
. Non-generic collections are so 2004.camelCased
, e.g. queryParameters
, not QueryParameters
See more on this question at Stackoverflow