Click here to Skip to main content
14,303,648 members
Rate this:
Please Sign up or sign in to vote.
I'm trying to get specific rows between 2 date 1 and 2 from MySQL table and convert the Result table to excel file .
I tried the mysqlConnection methode it works fine , but i want to do the same thing using entity model methode .

MysqlCoonection methode :

MySqlConnection connection = new MySqlConnection("datasource=;port=;username=;password=");
connection.Open();

string sQuery = "select * from  DB.table1 WHERE date = '" + d1 + "' and date <='" + d2+ "' ";

MySqlDataAdapter cmd= new MySqlDataAdapter(sQuery, connection);
DataSet ds = new DataSet();
cmd.Fill(ds);

//Conversion part code  
            Excel.Application app;
            Excel.Workbook wb;
            Excel.Worksheet ws;

            object misValue = System.Reflection.Missing.Value;

            app = new Excel.Application();
            app.Visible = true;
            wb = app.Workbooks.Add(misValue);

            ws = (Excel.Worksheet)wb.ActiveSheet;


            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                ws.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
            }
         

            for (int k = 1; k < ds.Tables[0].Rows.Count; k++)
            {

                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    ws.Cells[k + 1, j + 1] = ds.Tables[0].Rows[k][j].ToString();
                            
                }


            }


What I have tried:

What i have tried in Entity methode :

table_Entity result = new table_Entity();

 var  EntityQuery=from t in DB.Table1 where t.date >= d1 && t.date <=  d2  select t;
 var result = EntityQuery.ToList(); 


Is it possible to use var result and copy it in dataset to use it in the same Conversion excel code part ?


Thank you for your help and attention .
Posted
Updated 13-Sep-19 0:07am
v4
Comments
Gerry Schmitz 9-Sep-19 12:37pm
   
Yes, you just have to figure out what is in "var result".
EM_Y 10-Sep-19 10:40am
   
Hi Gerry
I almost losing hope posting my problem . I'm trying my best to figure out ,Thank you for your reply !
Richard Deeming 13-Sep-19 11:46am
   
string sQuery = "select * from  DB.table1 WHERE date = '" + d1 + "' and date <='" + d2+ "' ";


Don't do it like that! Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
EM_Y yesterday
   
Hi Richard I'm using here the entity framework query not oledb connection . I don't need the parameterized query .
Richard Deeming yesterday
   
Irrelevant. If you're executing a raw SQL query, you need to use parameters.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If you want to save data to Excel file, you can connect to the excel file via OleDbConnection[^] and INSERT data through OleDbCommand[^].

More:
OLE DB Tutorial (C#)[^]
Microsoft ACE OLEDB 12.0 Connection Strings - ConnectionStrings.com[^]

[EDIT]
Quote:
My question is to do the same operation using entity framework model query .

Yes, you can connect to MySQL database using EF. See: MySQL :: MySQL Connector/NET Developer Guide :: 9.1 Entity Framework 6 Support[^]
   
v2
Comments
EM_Y 13-Sep-19 6:14am
   
I did succeed converting my MySQL table to excel using MySQL connection /command !
My question is to do the same operation using entity framework model query .

table_Entity result = new table_Entity();
var  EntityQuery=from t in DB.Table1 where t.date >= d1 && t.date <=  d2  select t;
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100