Пример использования колекци из запроса к mssql server

C Sharp > Пример использования колекци из запроса к mssql server
28.10.2016 15:46:13


Наиболее часто встречающиеся слова в статье:

[ToString] [substring] [''00000000''] [VARCHAR] [last_run_duration] [Substring] [m_workSheet] [run_duration] [GetString] [last_run_date]


Статья:

Пример использования  колекци из запроса к mssql server. например вывод задач в exel

 

List<object[]> AllZnaniaPod = new List<object[]>(); // список всех задач

 AllZnaniaPod = (List<object[]>)dal.Select_SQL();

 foreach (var value in AllZnaniaPod)
 {
i = i + 1;
vrem = value[3].ToString();
if (vrem.Length == 5)
vrem = "0" + value[3].ToString().Substring(0, 1) + ":" + value[3].ToString().Substring(1, 2);
if (vrem.Length == 6)
vrem = value[3].ToString().Substring(0, 2) + ":" + value[3].ToString().Substring(2, 2);
dats = value[2].ToString().Substring(6, 2) + "." + value[2].ToString().Substring(4, 2)+ "."+value[2].ToString().Substring(0,4)+" "+ vrem;
mess= value[4].ToString().Substring(0, 27);
zadacha = value[0].ToString();
flag_zad = 0; // задачи пока нет
// начало обсёта в ехел с 50 по 72 ячейку в 1 столбце
for (int j = s1; j <= s1_1; j++)
{

Excel.Range range = (Excel.Range)m_workSheet.Cells[j, 1];
if ((object)range.Value != null)
rez = range.Value.ToString();
else rez = "";

// m_workSheet.Cells[j, 8] = rez;
if (rez == zadacha)
{
m_workSheet.Cells[j, 7] = "Есть совп, " + mess;
m_workSheet.Cells[j, 5] = value[1].ToString();
m_workSheet.Cells[j, 3] = dats;
flag_zad = 1; // флаг что есть задача установлен
if (mess.IndexOf("fail") > 0)
{
// пометим красным цветом , там где ошибка
Excel.Range range0_001 = (Excel.Range)m_workSheet.Cells[j, 7];
range0_001.Font.Color = Color.Red;
}

}

}

if (flag_zad == 0)
{
// добавим новое заание , которого нету в анализируемой таблице ехел
var arr = new object[2];
arr[0] = zadacha;
arr[1] = value[1].ToString();
No_List.Add(arr);
}


}

 

 

Сам класс получения  значений dal.Select_SQL();

 internal object Select_SQL()
{
List<object[]> Col = new List<object[]>();
string zapros;
zapros = @"
use msdb;

SELECT
h.[name]

,h2.last_run_date as last_run_date
, last_run_time as [last_date ччммсс]

,round( (cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS [last_run_duration]
,round( (cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MIN(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MIN_run_duration
,round( (cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(MAX(h3.run_duration ) AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2) AS MAX_run_duration
,h.job_id

INTO #status
FROM [sysjobs] h (NOLOCK)
join sysjobsteps h2 (NOLOCK) ON h2.job_id=h.job_id
JOIN sysjobhistory h3 (NOLOCK) ON h3.job_id=h.job_id AND h3.step_id = h2.step_id
JOIN sysjobschedules h4 (NOLOCK) ON h4.job_id=h.job_id
WHERE h.[enabled]=1 --and h.[name] not like '%backup%'
GROUP BY
h.[name]

,h2.last_run_date
,round( (cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 1, 4) as int) * 60)+cast(substring(right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 5, 2) as int) + cast(substring( right('00000000'+ cast(h2.last_run_duration AS VARCHAR(16)), 8), 7, 2) as float) / 60, 2)
,last_run_time
,h.job_id;


select h3.name,cast(sum(h3.last_run_duration) as varchar(50)) last_run_duration , sum(h3.MIN_run_duration) MIN_run_duration ,sum(h3.MAX_run_duration) MAX_run_duration ,cast(min(h3.last_run_date) as varchar(50)) [RUN_DATE]
,cast(min(h3.[last_date ччммсс]) as varchar(50)) [RUN_ччммсс]
,(select top 1 h00.message from sysjobhistory h00 (NOLOCK)where h00.job_id=h3.job_id order by h00.instance_id DESC ) as [STATUS_JOB]
from #status h3
group by h3.name,h3.job_id
order by h3.name;
drop table #status;

";
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand com = new SqlCommand(zapros, con);
try
{
con.Open();
SqlDataReader dr = com.ExecuteReader();


if (dr.HasRows)
foreach (DbDataRecord result in dr)
{
// формируем массив и запихиваем в колекцию объектов Col
var arr = new object[5];
arr[0] = dr.GetString(0);
arr[1] = dr.GetString(1);
arr[2] = dr.GetString(4);
arr[3] = dr.GetString(5);
arr[4] = dr.GetString(6);
// добавляем так сказать строку из массива в колекцию
Col.Add(arr);
}
else
return null;


}
catch
{

}
return Col;
}
}