CODEWITHSUNDEEP
c#mysqlasp.net
Zephyr
Zephyr

Reputation: 5

How to cast datetime of mysql to datetime of asp.net

I am fetching date from a table called lot in mysql in a string and want to insert that same datetime value in another table called soldlot in mysql. How to do that? Please help.

Thank you

String q6 = "select date from lot LIMIT " + m + "," + n + "";
MySqlCommand c6 = new MySqlCommand(q6, cn);
String d = (String)(c6.ExecuteScalar());
String q7 = "insert into sold values(" + lotno + "," + c + "," + rcno + ",'" + d.ToString("yyyy-mm-dd") + "')";
MySqlCommand c7 = new MySqlCommand(q7, cn);
c7.ExecuteScalar();

Upvotes: 0

Views: 1357

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

First of all, let's fix this to use parameters:

//I'm less worried about this query, as m and are likely integers. 
// I still don't approve of the string concatenation, but I'll leave it alone for now
String q6 = "select date from lot LIMIT " + m + "," + n + "";
MySqlCommand c6 = new MySqlCommand(q6, cn);
DateTime d = (DateTime)c6.ExecuteScalar(); //This should work just fine. 


//but parameters are VERY IMPORTANT for this query
String q7 = "insert into sold values( @LotNo, @c, @rcno, @date );";
MySqlCommand c7 = new MySqlCommand(q7, cn);
//I'm guessing at column types. Use actual DB column types and lengths here
c7.Parameters.Add("@LotNo", MySqlDbType.VarChar, 6).Value = lotno;
c7.Parameters.Add("@c", MySqlDbType.VarChar, 20).Value = c;
c7.Parameters.Add("@rcno", MySqlDbType.VarChar, 6).Value = rcno;
c7.Parameters.Add("@date", MySQlDbType.Date).Value = d;

c7.ExecuteNonQuery();

This change will fix a very serious security issue in your code, and as a nice side effect also avoids any need to ever think about date formats, thus answering your question at the same time.

But while I'm here, let's also make one other big improvement to this. Let's save an entire round-trip to the database, and consolidate your two queries into one:

String q6 = "insert into sold select @LotNo, @c, @rcno, date from lot LIMIT @m , @n;";
MySqlCommand c6 = new MySqlCommand(q6, cn);
//I'm guessing at column types. Use actual DB column types and lengths here
c6.Parameters.Add("@LotNo", MySqlDbType.VarChar, 6).Value = lotno;
c6.Parameters.Add("@c", MySqlDbType.VarChar, 20).Value = c;
c6.Parameters.Add("@rcno", MySqlDbType.VarChar, 6).Value = rcno;
c6.Parameters.Add("@m", MySqlDbType.Int).Value = m;
c6.Parameters.Add("@n", MySqlDbType.Int).Value = n;

c6.ExecuteNonQuery();

If the first attempt didn't solve your date casting issue, this will, as now the date never need come over to ASP.Net at all, and this will be much faster as a bonus.

Finally, I'm concerned that you were already up to #7 on your q and c variables. That's a major code smell for me. Always try to reduce the number of round-trips to the database. Mostly likely, everything in this method can be written as a single SQL statement, or at least reduced to a two-step process.

Upvotes: 1

Related Questions