private static void appendNewRows(HttpResponseMessage responseTillRun, int DBTopId, HttpClient client, AuthenticationHeaderValue authHeader)
{
int continuousTokenFinalRun = Convert.ToInt32(responseTillRun.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault());
int continuousToken = DBTopId + 1;
DataBaseConn db = new DataBaseConn();
Consolidated obj3 = new Consolidated();
try
{
for (int i = 0; i <= continuousTokenFinalRun;)
{
HttpResponseMessage responseNew = client.GetAsync("/engineering/_apis/release/releases?$top=10&$expand=environments&queryOrder=ascending&continuationToken=" + continuousToken).Result;
int continuousTokenNew = Convert.ToInt32(responseNew.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault());
Rootobject result2 = JsonConvert.DeserializeObject <Rootobject>(responseNew.Content.ReadAsStringAsync().Result);
obj3.classRootObject = result2;
obj3.classWorkItemClass = getWorkItemId(result2, client, authHeader);
db.getDBConn(obj3);
continuousToken = continuousTokenNew;
i = continuousToken;
}
//DoTheMainWork2(null, true);
}
catch (Exception ex)
{
Console.WriteLine("Error inside amendNewRows : " + ex.Message);
}
}
private static void ListProjects(AuthenticationHeaderValue authHeader)
{
try
{
DataBaseConn db = new DataBaseConn();
int DBTopId = db.getDBTopId();
// use the httpclient
using (var client = new HttpClient())
{
client.BaseAddress = new Uri(vstsCollectionUrl);
//client.BaseAddress = new Uri("");
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
client.DefaultRequestHeaders.Add("User-Agent", "ManagedClientConsoleAppSample");
client.DefaultRequestHeaders.Add("X-TFS-FedAuthRedirect", "Suppress");
client.DefaultRequestHeaders.Authorization = authHeader;
// connect to the REST endpoint
HttpResponseMessage responseTillRun = client.GetAsync("/engineering/_apis/release/releases?$top=100&$expand=environments").Result;
if (DBTopId != 0)
{
appendNewRows(responseTillRun, DBTopId, client, authHeader);
//Console.WriteLine("if condition called");
}
else
{
HttpResponseMessage response = client.GetAsync("/engineering/_apis/release/releases?$top=100&$expand=environments&queryOrder=ascending").Result;
//HttpResponseMessage response = client.GetAsync("/engineering/_apis/release/releases?$top=2&$expand=environments").Result;
//int continuousToken = (Convert.ToBoolean(response.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault())) ? Convert.ToInt32(response.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault()) : 1;
int continuousToken = Convert.ToInt32(response.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault());
int continuousTokenFinalRun = Convert.ToInt32(responseTillRun.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault());
// check to see if we have a succesfull respond
if (response.IsSuccessStatusCode)
{
//DoTheMainWork(response);
//**** DB Connection*****
Consolidated obj = new Consolidated();
Rootobject result = JsonConvert.DeserializeObject <Rootobject>(response.Content.ReadAsStringAsync().Result);
obj.classRootObject = result;
obj.classWorkItemClass = getWorkItemId(result, client, authHeader);
db.getDBConn(obj);
if (continuousToken != 0)
{
Consolidated obj2 = new Consolidated();
try
{
for (int i = 0; i <= continuousTokenFinalRun;)
{
HttpResponseMessage responseNew = client.GetAsync("/engineering/_apis/release/releases?$top=100&$expand=environments&queryOrder=ascending&continuationToken=" + continuousToken).Result;
int continuousTokenNew = Convert.ToInt32(responseNew.Headers.FirstOrDefault(x => x.Key == "x-ms-continuationtoken").Value.FirstOrDefault());
Rootobject result2 = JsonConvert.DeserializeObject <Rootobject>(responseNew.Content.ReadAsStringAsync().Result);
obj2.classRootObject = result2;
obj2.classWorkItemClass = getWorkItemId(result2, client, authHeader);
db.getDBConn(obj2);
continuousToken = continuousTokenNew;
i = continuousToken;
}
//DoTheMainWork2(null, true);
}
catch (Exception ex)
{
Console.WriteLine("Error inside ListProjects 1 : " + ex.Message);
}
}
}
else if (response.StatusCode == System.Net.HttpStatusCode.Unauthorized)
{
throw new UnauthorizedAccessException();
}
else
{
Console.WriteLine("{0}:{1}", response.StatusCode, response.ReasonPhrase);
Console.ReadLine();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error inside ListProjects 2 : " + ex.Message);
}
Console.WriteLine("Data gathered, press any key to exit!");
//Console.ReadLine();
}
public void getDBConn(Consolidated result)
{
try
{
// Connect to SQL
Console.Write("Connecting to SQL Server ... ");
//Local system db
using (SqlConnection connection = new SqlConnection("Data Source = FP-TECHSRV8; Initial Catalog = ReleaseBIReports; integrated security = SSPI"))
//using (SqlConnection connection = new SqlConnection("Data Source = FP-TECHSRV8; Initial Catalog = ReleaseBIReports; integrated security = SSPI"))
{
connection.Open();
Console.WriteLine("Done.");
// INSERT demo
Console.Write("Inserting a new row into table...");
//Console.ReadKey(true);
foreach (var res in result.classRootObject.value)
{
//foreach (var env in res.environments)
//{
//if (env.status == "succeeded" || env.status == "inProgress" || env.status == "failed")
//{
StringBuilder sb = new StringBuilder();
sb.Clear();
//sb.Append("INSERT INTO [dbo].[ReleaseBIReportsData] ([value_id],[value_name],[value_status],[value_createdOn],[value_createdOn_Sprint],[value_modifiedOn],[value_modifiedBy_id],[value_modifiedBy_displayName],[value_modifiedBy_uniqueName],[value_createdBy_id],[value_createdBy_displayName],[value_createdBy_uniqueName],[value_environments_id],[value_environments_releaseId],[value_environments_name],[value_environments_name_LockOrLive],[value_environments_status],[value_environments_rank],[value_environments_definitionEnvironmentId],[value_environments_release_id],[value_environments_release_name],[value_environments_releaseDefinition_id],[value_environments_releaseDefinition_name],[value_environments_releaseDefinition_name_teamName],[value_environments_releaseDefinition_name_projectName],[value_environments_releaseDefinition_name_repoName],[value_environments_releaseCreatedBy_id],[value_environments_releaseCreatedBy_displayName],[value_environments_triggerReason],[value_releaseDefinition_id],[value_releaseDefinition_name],[value_releaseDefinition_url],[value_description],[value_reason],[value_releaseNameFormat],[value_keepForever],[value_definitionSnapshotRevision]) ");
//sb.Append("VALUES (@value_id,@value_name,@value_status,@value_createdOn,@value_createdOn_Sprint,@value_modifiedOn,@value_modifiedBy_id,@value_modifiedBy_displayName,@value_modifiedBy_uniqueName,@value_createdBy_id,@value_createdBy_displayName,@value_createdBy_uniqueName,@value_environments_id,@value_environments_releaseId,@value_environments_name,@value_environments_name_LockOrLive,@value_environments_status,@value_environments_rank,@value_environments_definitionEnvironmentId,@value_environments_release_id,@value_environments_release_name,@value_environments_releaseDefinition_id,@value_environments_releaseDefinition_name,@value_environments_releaseDefinition_name_teamName,@value_environments_releaseDefinition_name_projectName,@value_environments_releaseDefinition_name_repoName,@value_environments_releaseCreatedBy_id,@value_environments_releaseCreatedBy_displayName,@value_environments_triggerReason,@value_releaseDefinition_id,@value_releaseDefinition_name,@value_releaseDefinition_url,@value_description,@value_reason,@value_releaseNameFormat,@value_keepForever,@value_definitionSnapshotRevision);");
sb.Append("INSERT INTO [dbo].[ReleaseBIReportsData] ([value_id],[value_name],[value_status],[value_createdOn],[value_createdOn_Sprint],[value_modifiedOn],[value_modifiedBy_id],[value_modifiedBy_displayName],[value_modifiedBy_uniqueName],[value_createdBy_id],[value_createdBy_displayName],[value_createdBy_uniqueName],[value_releaseDefinition_id],[value_releaseDefinition_name],[value_releaseDefinition_url],[value_description],[value_reason],[value_releaseNameFormat],[value_keepForever],[value_definitionSnapshotRevision]) ");
sb.Append("VALUES (@value_id,@value_name,@value_status,@value_createdOn,@value_createdOn_Sprint,@value_modifiedOn,@value_modifiedBy_id,@value_modifiedBy_displayName,@value_modifiedBy_uniqueName,@value_createdBy_id,@value_createdBy_displayName,@value_createdBy_uniqueName,@value_releaseDefinition_id,@value_releaseDefinition_name,@value_releaseDefinition_url,@value_description,@value_reason,@value_releaseNameFormat,@value_keepForever,@value_definitionSnapshotRevision);");
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@value_id", res.id);
command.Parameters.AddWithValue("@value_name", res.name);
command.Parameters.AddWithValue("@value_status", res.status);
command.Parameters.AddWithValue("@value_createdOn", Convert.ToDateTime(res.createdOn).Date);
//******SPRINT CODE START******
if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("9-11-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("9-24-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-19");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("9-25-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("10-8-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-20");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("10-9-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("10-22-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-21");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("10-23-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("11-5-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-22");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("11-6-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("11-19-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-23");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("11-20-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("12-3-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-24");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("12-4-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("12-17-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-25");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("12-18-2017") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("12-31-2017"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-26");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("1-1-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("1-14-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-1 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("1-15-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("1-28-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-2 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("1-29-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("2-11-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-3 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("2-12-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("2-25-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-4 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("2-26-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("3-11-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-5 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("3-12-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("3-25-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-6 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("3-26-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("4-8-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-7 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("4-9-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("4-22-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-8 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("4-23-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("5-6-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-9 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("5-7-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("5-20-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-10 2018");
}
else if (Convert.ToDateTime(res.createdOn).Date >= Convert.ToDateTime("5-21-2018") && Convert.ToDateTime(res.createdOn).Date <= Convert.ToDateTime("6-3-2018"))
{
command.Parameters.AddWithValue("@value_createdOn_Sprint", "Sprint-11 2018");
}
//******SPRINT CODE START******
command.Parameters.AddWithValue("@value_modifiedOn", res.modifiedOn);
command.Parameters.AddWithValue("@value_modifiedBy_id", res.modifiedBy.id);
command.Parameters.AddWithValue("@value_modifiedBy_displayName", res.modifiedBy.displayName);
command.Parameters.AddWithValue("@value_modifiedBy_uniqueName", res.modifiedBy.uniqueName);
command.Parameters.AddWithValue("@value_createdBy_id", res.createdBy.id);
command.Parameters.AddWithValue("@value_createdBy_displayName", res.createdBy.displayName);
command.Parameters.AddWithValue("@value_createdBy_uniqueName", res.createdBy.uniqueName);
//*****Environment table entry start*****
//StringBuilder sbEnv = new StringBuilder();
//sbEnv.Clear();
//sbEnv.Append("INSERT INTO [dbo].[ReleaseBIReportsDataEnv] ([value_environments_id],[value_environments_releaseId],[value_environments_name],[value_environments_name_LockOrLive],[value_environments_status],[value_environments_rank],[value_environments_definitionEnvironmentId],[value_environments_release_id],[value_environments_release_name],[value_environments_releaseDefinition_id],[value_environments_releaseDefinition_name],[value_environments_releaseDefinition_name_teamName],[value_environments_releaseDefinition_name_projectName],[value_environments_releaseDefinition_name_repoName],[value_environments_releaseCreatedBy_id],[value_environments_releaseCreatedBy_displayName],[value_environments_triggerReason]) ");
//sbEnv.Append("VALUES (@value_environments_id,@value_environments_releaseId,@value_environments_name,@value_environments_name_LockOrLive,@value_environments_status,@value_environments_rank,@value_environments_definitionEnvironmentId,@value_environments_release_id,@value_environments_release_name,@value_environments_releaseDefinition_id,@value_environments_releaseDefinition_name,@value_environments_releaseDefinition_name_teamName,@value_environments_releaseDefinition_name_projectName,@value_environments_releaseDefinition_name_repoName,@value_environments_releaseCreatedBy_id,@value_environments_releaseCreatedBy_displayName,@value_environments_triggerReason);");
//string sqlEnv = sbEnv.ToString();
//using (SqlCommand commandEnv = new SqlCommand(sqlEnv, connection))
//{
// commandEnv.Parameters.AddWithValue("@value_environments_id", env.id);
// commandEnv.Parameters.AddWithValue("@value_environments_releaseId", env.releaseId);
// commandEnv.Parameters.AddWithValue("@value_environments_name", env.name);
// if (env.name.ToLower().Contains("lock") || env.name.ToLower().Contains("fpnext") || env.name.ToLower().Contains("prelive") || env.name.ToLower().Contains("prod") || env.name.ToLower().Contains("stag") || env.name.ToLower().Contains("ptr") || env.name.ToLower().Contains("automationresourcemanager") || env.name.ToLower().Contains("coa-ss") || env.name.ToLower().Contains("environment 1") || env.name.ToLower().Contains("fdatool") || env.name.ToLower().Contains("fpautomationresourcesservice") || env.name.ToLower().Contains("hotfix") || env.name.ToLower().Contains("ot-ss") || env.name.ToLower().Contains("testmode") || env.name.ToLower().Contains("copy"))
// {
// commandEnv.Parameters.AddWithValue("@value_environments_name_LockOrLive", "Lock");
// }
// else
// {
// commandEnv.Parameters.AddWithValue("@value_environments_name_LockOrLive", "Live");
// }
// commandEnv.Parameters.AddWithValue("@value_environments_status", env.status);
// commandEnv.Parameters.AddWithValue("@value_environments_rank", env.rank);
// commandEnv.Parameters.AddWithValue("@value_environments_definitionEnvironmentId", env.definitionEnvironmentId);
// commandEnv.Parameters.AddWithValue("@value_environments_release_id", env.release.id);
// commandEnv.Parameters.AddWithValue("@value_environments_release_name", env.release.name);
// commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_id", env.releaseDefinition.id);
// commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name", env.releaseDefinition.name);
// // code to fetch the team name start
// string[] teamName = env.releaseDefinition.name.Split('.');
// {
// commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_teamName", ((teamName.Length > 0 && !string.IsNullOrEmpty(teamName[0])) ? teamName[0] : ""));
// commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_projectName", ((teamName.Length > 1 && !string.IsNullOrEmpty(teamName[1])) ? teamName[1] : ""));
// commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_repoName", ((teamName.Length > 2 && !string.IsNullOrEmpty(teamName[2])) ? teamName[2] : ""));
// }
// // code to fetch the team name end
// commandEnv.Parameters.AddWithValue("@value_environments_releaseCreatedBy_id", env.releaseCreatedBy.id);
// commandEnv.Parameters.AddWithValue("@value_environments_releaseCreatedBy_displayName", env.releaseCreatedBy.displayName);
// commandEnv.Parameters.AddWithValue("@value_environments_triggerReason", env.triggerReason);
// int rowsAffectedEnv = commandEnv.ExecuteNonQuery();
// Console.WriteLine(rowsAffectedEnv + " row(s) inserted for environments");
//*****Environment table entry end*****
//}
command.Parameters.AddWithValue("@value_releaseDefinition_id", res.releaseDefinition.id);
command.Parameters.AddWithValue("@value_releaseDefinition_name", res.releaseDefinition.name);
command.Parameters.AddWithValue("@value_releaseDefinition_url", res.releaseDefinition.url);
command.Parameters.AddWithValue("@value_description", res.description);
command.Parameters.AddWithValue("@value_reason", res.reason);
command.Parameters.AddWithValue("@value_releaseNameFormat", res.releaseNameFormat);
command.Parameters.AddWithValue("@value_keepForever", res.keepForever);
command.Parameters.AddWithValue("@value_definitionSnapshotRevision", res.definitionSnapshotRevision);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) inserted for main table");
}
//}
//}
foreach (var env in res.environments)
{
if (env.status == "succeeded" || env.status == "inProgress" || env.status == "failed")
{
StringBuilder sbEnv = new StringBuilder();
sbEnv.Clear();
sbEnv.Append("INSERT INTO [dbo].[ReleaseBIReportsDataEnv] ([value_environments_id],[value_environments_releaseId],[value_environments_name],[value_environments_name_LockOrLive],[value_environments_status],[value_environments_rank],[value_environments_definitionEnvironmentId],[value_environments_release_id],[value_environments_release_name],[value_environments_releaseDefinition_id],[value_environments_releaseDefinition_name],[value_environments_releaseDefinition_name_teamName],[value_environments_releaseDefinition_name_projectName],[value_environments_releaseDefinition_name_repoName],[value_environments_releaseCreatedBy_id],[value_environments_releaseCreatedBy_displayName],[value_environments_triggerReason]) ");
sbEnv.Append("VALUES (@value_environments_id,@value_environments_releaseId,@value_environments_name,@value_environments_name_LockOrLive,@value_environments_status,@value_environments_rank,@value_environments_definitionEnvironmentId,@value_environments_release_id,@value_environments_release_name,@value_environments_releaseDefinition_id,@value_environments_releaseDefinition_name,@value_environments_releaseDefinition_name_teamName,@value_environments_releaseDefinition_name_projectName,@value_environments_releaseDefinition_name_repoName,@value_environments_releaseCreatedBy_id,@value_environments_releaseCreatedBy_displayName,@value_environments_triggerReason);");
string sqlEnv = sbEnv.ToString();
using (SqlCommand commandEnv = new SqlCommand(sqlEnv, connection))
{
commandEnv.Parameters.AddWithValue("@value_environments_id", env.id);
commandEnv.Parameters.AddWithValue("@value_environments_releaseId", env.releaseId);
commandEnv.Parameters.AddWithValue("@value_environments_name", env.name);
if (env.name.ToLower().Contains("lock") || env.name.ToLower().Contains("fpnext") || env.name.ToLower().Contains("prelive") || env.name.ToLower().Contains("prod") || env.name.ToLower().Contains("stag") || env.name.ToLower().Contains("ptr") || env.name.ToLower().Contains("automationresourcemanager") || env.name.ToLower().Contains("coa-ss") || env.name.ToLower().Contains("environment 1") || env.name.ToLower().Contains("fdatool") || env.name.ToLower().Contains("fpautomationresourcesservice") || env.name.ToLower().Contains("hotfix") || env.name.ToLower().Contains("ot-ss") || env.name.ToLower().Contains("testmode") || env.name.ToLower().Contains("copy"))
{
commandEnv.Parameters.AddWithValue("@value_environments_name_LockOrLive", "Lock");
}
else
{
commandEnv.Parameters.AddWithValue("@value_environments_name_LockOrLive", "Live");
}
commandEnv.Parameters.AddWithValue("@value_environments_status", env.status);
commandEnv.Parameters.AddWithValue("@value_environments_rank", env.rank);
commandEnv.Parameters.AddWithValue("@value_environments_definitionEnvironmentId", env.definitionEnvironmentId);
commandEnv.Parameters.AddWithValue("@value_environments_release_id", env.release.id);
commandEnv.Parameters.AddWithValue("@value_environments_release_name", env.release.name);
commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_id", env.releaseDefinition.id);
commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name", env.releaseDefinition.name);
// code to fetch the team name start
string[] teamName = env.releaseDefinition.name.Split('.');
{
commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_teamName", ((teamName.Length > 0 && !string.IsNullOrEmpty(teamName[0])) ? teamName[0] : ""));
commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_projectName", ((teamName.Length > 1 && !string.IsNullOrEmpty(teamName[1])) ? teamName[1] : ""));
commandEnv.Parameters.AddWithValue("@value_environments_releaseDefinition_name_repoName", ((teamName.Length > 2 && !string.IsNullOrEmpty(teamName[2])) ? teamName[2] : ""));
}
// code to fetch the team name end
commandEnv.Parameters.AddWithValue("@value_environments_releaseCreatedBy_id", env.releaseCreatedBy.id);
commandEnv.Parameters.AddWithValue("@value_environments_releaseCreatedBy_displayName", env.releaseCreatedBy.displayName);
commandEnv.Parameters.AddWithValue("@value_environments_triggerReason", env.triggerReason);
int rowsAffectedEnv = commandEnv.ExecuteNonQuery();
Console.WriteLine(rowsAffectedEnv + " row(s) inserted for environments");
}
}
}
}
//enable work item table insertion start
Console.Write("Insertion into table ReleaseBIReportsWorkItemData.....");
foreach (var wk in result.classWorkItemClass)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Clear();
sb.Append("INSERT INTO [dbo].[ReleaseBIReportsWorkItemData] ([workitem_id],[workitem_releaseId],[workitem_rev],[workitem_fields_System_AreaPath],[workitem_fields_System_TeamProject],[workitem_fields_System_IterationPath],[workitem_fields_System_WorkItemType],[workitem_fields_System_State],[workitem_fields_System_Reason],[workitem_fields_System_AssignedTo],[workitem_fields_System_CreatedDate],[workitem_fields_System_CreatedBy],[workitem_fields_System_ChangedDate],[workitem_fields_System_ChangedBy],[workitem_fields_System_Title],[workitem_fields_Microsoft_VSTS_Common_Activity],[workitem_fields_Microsoft_VSTS_Common_StateChangeDate],[workitem_fields_Microsoft_VSTS_Common_ActivatedDate],[workitem_fields_Microsoft_VSTS_Common_ActivatedBy],[workitem_fields_Microsoft_VSTS_Common_ResolvedDate],[workitem_fields_Microsoft_VSTS_Common_ResolvedBy],[workitem_fields_Microsoft_VSTS_Common_ResolvedReason],[workitem_fields_Microsoft_VSTS_Common_Priority],[workitem_fields_Microsoft_VSTS_Common_Severity],[workitem_fields_Microsoft_VSTS_Common_StackRank],[workitem_fields_Microsoft_VSTS_Common_ValueArea],[workitem_fields_Microsoft_VSTS_Scheduling_StoryPoints]) ");
sb.Append("VALUES (@workitem_id,@workitem_releaseId,@workitem_rev,@workitem_fields_System_AreaPath,@workitem_fields_System_TeamProject,@workitem_fields_System_IterationPath,@workitem_fields_System_WorkItemType,@workitem_fields_System_State,@workitem_fields_System_Reason,@workitem_fields_System_AssignedTo,@workitem_fields_System_CreatedDate,@workitem_fields_System_CreatedBy,@workitem_fields_System_ChangedDate,@workitem_fields_System_ChangedBy,@workitem_fields_System_Title,@workitem_fields_Microsoft_VSTS_Common_Activity,@workitem_fields_Microsoft_VSTS_Common_StateChangeDate,@workitem_fields_Microsoft_VSTS_Common_ActivatedDate,@workitem_fields_Microsoft_VSTS_Common_ActivatedBy,@workitem_fields_Microsoft_VSTS_Common_ResolvedDate,@workitem_fields_Microsoft_VSTS_Common_ResolvedBy,@workitem_fields_Microsoft_VSTS_Common_ResolvedReason,@workitem_fields_Microsoft_VSTS_Common_Priority,@workitem_fields_Microsoft_VSTS_Common_Severity,@workitem_fields_Microsoft_VSTS_Common_StackRank,@workitem_fields_Microsoft_VSTS_Common_ValueArea,@workitem_fields_Microsoft_VSTS_Scheduling_StoryPoints);");
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@workitem_id", wk.id);
command.Parameters.AddWithValue("@workitem_releaseId", wk.releaseId);
command.Parameters.AddWithValue("@workitem_rev", wk.rev ?? 0);
command.Parameters.AddWithValue("@workitem_fields_System_AreaPath", String.IsNullOrEmpty(wk.fields.SystemAreaPath) ? "" : wk.fields.SystemAreaPath);
command.Parameters.AddWithValue("@workitem_fields_System_TeamProject", String.IsNullOrEmpty(wk.fields.SystemTeamProject) ? "" : wk.fields.SystemTeamProject);
command.Parameters.AddWithValue("@workitem_fields_System_IterationPath", String.IsNullOrEmpty(wk.fields.SystemIterationPath) ? "" : wk.fields.SystemIterationPath);
command.Parameters.AddWithValue("@workitem_fields_System_WorkItemType", String.IsNullOrEmpty(wk.fields.SystemWorkItemType) ? "" : wk.fields.SystemWorkItemType);
command.Parameters.AddWithValue("@workitem_fields_System_State", String.IsNullOrEmpty(wk.fields.SystemState) ? "" : wk.fields.SystemState);
command.Parameters.AddWithValue("@workitem_fields_System_Reason", String.IsNullOrEmpty(wk.fields.SystemReason) ? "" : wk.fields.SystemReason);
command.Parameters.AddWithValue("@workitem_fields_System_AssignedTo", String.IsNullOrEmpty(wk.fields.SystemAssignedTo) ? "" : wk.fields.SystemAssignedTo);
command.Parameters.AddWithValue("@workitem_fields_System_CreatedDate", ((wk.fields.SystemCreatedDate == DateTime.MinValue) ? Convert.ToDateTime("1/1/1800").Date : (Convert.ToDateTime(wk.fields.SystemCreatedDate).Date)));
command.Parameters.AddWithValue("@workitem_fields_System_CreatedBy", String.IsNullOrEmpty(wk.fields.SystemCreatedBy) ? "" : wk.fields.SystemCreatedBy);
command.Parameters.AddWithValue("@workitem_fields_System_ChangedDate", ((wk.fields.SystemChangedDate == DateTime.MinValue) ? Convert.ToDateTime("1/1/1800").Date : (Convert.ToDateTime(wk.fields.SystemChangedDate).Date)));
command.Parameters.AddWithValue("@workitem_fields_System_ChangedBy", String.IsNullOrEmpty(wk.fields.SystemChangedBy) ? "" : wk.fields.SystemChangedBy);
command.Parameters.AddWithValue("@workitem_fields_System_Title", String.IsNullOrEmpty(wk.fields.SystemTitle) ? "" : wk.id + " - " + wk.fields.SystemTitle);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_Activity", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonActivity) ? "" : wk.fields.MicrosoftVSTSCommonActivity);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_StateChangeDate", ((wk.fields.MicrosoftVSTSCommonStateChangeDate == DateTime.MinValue) ? Convert.ToDateTime("1/1/1800").Date : (Convert.ToDateTime(wk.fields.MicrosoftVSTSCommonStateChangeDate).Date)));
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ActivatedDate", ((wk.fields.MicrosoftVSTSCommonActivatedDate == DateTime.MinValue) ? Convert.ToDateTime("1/1/1800").Date : (Convert.ToDateTime(wk.fields.MicrosoftVSTSCommonActivatedDate).Date)));
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ActivatedBy", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonActivatedBy) ? "" : wk.fields.MicrosoftVSTSCommonActivatedBy);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ResolvedDate", ((wk.fields.MicrosoftVSTSCommonResolvedDate == DateTime.MinValue) ? Convert.ToDateTime("1/1/1800").Date : (Convert.ToDateTime(wk.fields.MicrosoftVSTSCommonResolvedDate).Date)));
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ResolvedBy", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonResolvedBy) ? "" : wk.fields.MicrosoftVSTSCommonResolvedBy);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ResolvedReason", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonResolvedReason) ? "" : wk.fields.MicrosoftVSTSCommonResolvedReason);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_Priority", wk.fields.MicrosoftVSTSCommonPriority ?? 0);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_Severity", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonSeverity) ? "" : wk.fields.MicrosoftVSTSCommonSeverity);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_StackRank", wk.fields.MicrosoftVSTSCommonStackRank ?? 0);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Common_ValueArea", String.IsNullOrEmpty(wk.fields.MicrosoftVSTSCommonValueArea) ? "" : wk.fields.MicrosoftVSTSCommonValueArea);
command.Parameters.AddWithValue("@workitem_fields_Microsoft_VSTS_Scheduling_StoryPoints", wk.fields.MicrosoftVSTSSchedulingStoryPoints ?? 0);
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) inserted in table ReleaseBIReportsWorkItemData");
}
}
catch (Exception ex)
{
Console.WriteLine("Error in workItem data write : " + ex.Message + " and occured at : " + wk.fields);
}
}
//enable work item table insertion end
connection.Close();
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
//Console.WriteLine("All done. Press any key to finish...");
//Console.ReadKey(true);
}
