public List <MessageMediaStructure> loadMessagesFromDataBase(int ConceptID, int Base, int count, int type)
{
List <MessageMediaStructure> TempTable = new List <MessageMediaStructure>();
if (ConceptID <= 0)
{
return(TempTable);
}
////order by Id OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY
//string select_query = @"select id,message,tagged " +
// "from(select * from Messages ) a " +
// "inner join " +
// "(select MessageId,tagged from MessageConcept where ConceptId = {2} ) b " +
// "on a.Id= b.messageid";
string select_query = "select ID, Message,[InConcept-00] " +
"from Relations b where ConceptID = {0} " +
"and (b.[InConcept-00] = {3} or b.[InConcept-00] = {4})" +
" order by Id OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY";
if (type != -1)
{
select_query = string.Format(select_query, Base, count, ConceptID, type, type);
}
else
{
select_query = string.Format(select_query, ConceptID, Base, count, 2, 1);
}
SqlConnection select_Conn = new SqlConnection(Utilities.UtilitiesClass.Conncection.ConnectionString_MrGhasempour_ConseptMediaDB);
try
{
SqlCommand cmd2 = new SqlCommand(select_query, select_Conn);
select_Conn.Open();
SqlDataReader dr = cmd2.ExecuteReader();
DataTable result = new DataTable();
result.Load(dr);
for (int k = 0; k < result.Rows.Count; k++)
{
MessageMediaStructure ms = new MessageMediaStructure();
ms.Id = int.Parse(result.Rows[k]["ID"].ToString());
ms.message = result.Rows[k]["Message"].ToString().Trim();
if (result.Rows[k]["InConcept-00"] != null)
{
string t = result.Rows[k]["InConcept-00"].ToString();
if (t.ToLower() == "1")
{
ms.tagged = 1;
}
else
{
ms.tagged = 0;
}
ms.ConceptID = ConceptID;
TempTable.Add(ms);
}
}
select_Conn.Close();
}
catch (Exception)
{
select_Conn.Close();
}
return(TempTable);
}
public List <MessageMediaStructure> loadMessagesFromDataBase(int ConceptID, int type, string where)
{
List <MessageMediaStructure> TempTable = new List <MessageMediaStructure>();
if (ConceptID <= 0)
{
return(TempTable);
}
where = " and (" + where + ") ";
string select_query = "select ID, Message,[InConcept-00] " +
"from Relations b where ConceptID = {0} " +
"and (b.[InConcept-00] = {1} or b.[InConcept-00] = {2})";
if (type != -1)
{
select_query = string.Format(select_query, ConceptID, type, type);
}
else
{
select_query = string.Format(select_query, ConceptID, 2, 1);
}
SqlConnection select_Conn = new SqlConnection(Utilities.UtilitiesClass.Conncection.ConnectionString_MrGhasempour_ConseptMediaDB);
try
{
SqlCommand cmd2 = new SqlCommand(select_query, select_Conn);
select_Conn.Open();
SqlDataReader dr = cmd2.ExecuteReader();
DataTable result = new DataTable();
result.Load(dr);
for (int k = 0; k < result.Rows.Count; k++)
{
MessageMediaStructure ms = new MessageMediaStructure();
ms.Id = int.Parse(result.Rows[k]["ID"].ToString());
ms.message = result.Rows[k]["Message"].ToString().Trim();
if (result.Rows[k]["InConcept-00"] != null)
{
string t = result.Rows[k]["InConcept-00"].ToString();
if (t.ToLower() == "1")
{
ms.tagged = 1;
}
else
{
ms.tagged = 0;
}
ms.ConceptID = ConceptID;
TempTable.Add(ms);
}
else
{
ms.tagged = -1;
}
}
select_Conn.Close();
}
catch (Exception)
{
select_Conn.Close();
}
return(TempTable);
}
private List <MessageMediaStructure> readExcel(string filePath)
{
List <MessageMediaStructure> ListOfexcelRows = new List <MessageMediaStructure>();
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
//xlWorkBook = xlApp.Workbooks.Open(Environment.CurrentDirectory + "\\" + filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkBook = xlApp.Workbooks.Open(filePath);//, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
}
catch
{
MessageBox.Show("فایل ورودی وجود ندارد", "خطا");
return(ListOfexcelRows);
}
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in xlWorkBook.Worksheets)
{
int i = 1;
foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.UsedRange.Rows)
{
MessageMediaStructure ex = new MessageMediaStructure();
try
{
//ex.lable = int.Parse(worksheet.Cells[row.Row, 2].Value.ToString());
ex.tagged = int.Parse(worksheet.Cells[row.Row, 2].Value.ToString());
}
catch
{
//NuOfEmptyTags++;
//continue;
}
try
{
//ex.text = worksheet.Cells[row.Row, 1].Value.ToString().Trim();
ex.message = worksheet.Cells[row.Row, 1].Value.ToString().Trim();
}
catch
{
//NuOfWrongRows++;
}
if (ex.message != null)
{
ex.Id = i;
ListOfexcelRows.Add(ex);
i++;
}
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
xlWorkBook.Close();
xlApp.Quit();
return(ListOfexcelRows);
}