MySql with ASP.NET Core

  1. Create the project: dotnet new console
  2. Add mysql package: dotnet add package MySql.Data
  3.  Optional: Add an ORM: dotnet add package Dapper
  4. Start vscode: code .
  5. Connect and and query
  6. Run the code: dotnet run

     static void Main(string[] args)
        {
            MySqlConnectionStringBuilder conn_string = new MySqlConnectionStringBuilder();
            conn_string.Server = "SERVERAdress";
            conn_string.UserID = "USERid";
            conn_string.Password = "Pws";
            conn_string.Database = "Db";

            MySqlConnection connection = new MySqlConnection
            {
                ConnectionString = conn_string.ToString()
            };
            connection.Open();

            MySqlCommand command = new MySqlCommand("SELECT * FROM Notes;", connection);

            using (MySqlDataReader reader = command.ExecuteReader())
            {

                while (reader.Read())
                {
                    string row = $"{reader["NoteId"]}\t\t{reader["NoteTitle"]}\t\t{reader["NoteText"]}";
                    System.Console.WriteLine(row);
                }
            }

            connection.Close();

            System.Console.ReadKey();
        }

Advertisements

#asp-net-core, #mysql, #mysql-with-net-core

Reports for Redmine

After some requests I have received, I will share some of the SQL commands that I used for creating reports for Redmine. You need to change projectIDs, journelIDs etc.  Here they are:

1 – Approximate Solution Time


a.istipi IsTipi,
COUNT(a.isnumarasi) ToplamIsSayisi,
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id IN (45,
64,
65) OR
projects.parent_id IN (64,
45,
65)) AND
journal_details.value = 3 AND
issues.status_id =12

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
GROUP BY
a.tracker_id

2. Current Project Analysis


(select name from projects where projects.id = issues.project_id) ProjeAdi,
count(*) ToplamIsSayisi,
(SELECT count(*) from issues where issues.status_id=2 and issues.project_id= $P{PROJECT_ID} ) IslemdeIsSayisi,
(SELECT count(*) from issues where issues.status_id=12 and issues.project_id=$P{PROJECT_ID}) CozumOnaylandiIsSayisi,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id = 1

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuHata,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id =2

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuIstek,

(select
SUM(DATEDIFF(issues.due_date,issues.start_date)+1) IsGunuSayisi
FROM issues
where issues.tracker_id =16
and issues.project_id = $P{PROJECT_ID}) ToplamZiyaretSayisi,
(select max(due_date) FROM issues where issues.tracker_id =16 and issues.project_id = $P{PROJECT_ID}) SonZiyaretTarihi
FROM issues where issues.project_id = $P{PROJECT_ID}
and issues.tracker_id != 16

3. Status of an Assignee

Change assignee id for your project.


SELECT id,
(SELECT name
FROM issue_statuses
WHERE issue_statuses.id = issues.status_id)
issueStatus,
(SELECT name
FROM projects
WHERE projects.id =issues.project_id)
ProjeAdi,
(select concat(firstname,' ',lastname) from users where users.id=issues.assigned_to_id) as AssigneeName
FROM issues
WHERE issues.assigned_to_id = $P{ASSIGNEE__ID}

Thanks a lot to Taner Yurdunkulu

#business-intelligence, #redmine, #redmine-reports

Floor, Ceiling and Rounding with MySQL

I was trying to get the average solution time of issues in Redmine per tracker type. In order to get the average time, I first selected creation time of issue and extracted it from the date of solution of the issue (this doesn’t mean it is closed, it is to say status is updated with the id =X),  after that I listed the average of each day.

ROUND(AVG(DATEDIFF(journals.created_on,issues.created_on))) AverageSolutionTimeRounded,

Some mathematical functions of MySQL could be a useful. Here they are: Continue reading

#ceiling, #floor, #mysql, #rounding