请帮我查看代码n获取正确的代码 [英] Please help me to review the code n get the correct code
问题描述
我想将当前日期与数据库日期（即过期日期）进行比较...如果当前日期大于数据库中的过期日期，则数据库中的状态列应使用C＃更改为已过期...我没有获取代码实现这个请帮我一个窗口应用程序...
我尝试过：
string constring =Data Source = LENOVO-PC\\ SUMIT; Initial Catalog = Ngo_Mgmt; User ID = sa; Password = 123;
SqlConnection con = new SqlConnection （constring）;
试试
{
con.Open（）;
SqlCommand cmd = new SqlCommand（ ）;
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText =select Subscription_Expire，Status，来自AddMember的currentdate;
SqlDataReader dr = cmd.ExecuteReader（）;
if（dr.HasRows）
{
while（dr.Read（））
{
if（ dr [Subscription_Expire]。ToString（）。Trim（）== dr [currentdate]。ToString（）。Trim（））
{
cmd.CommandText =更新AddMember设置Status = @ Status;
string ans =Expired;
cmd.Parameters.AddWithValue（@ Status，ans）;
cmd.ExecuteNonQuery（）;
}
}
}
else
{
// MessageBox.Show（No Such record Exists，Alert！）;
}
cmd.Dispose（）;
dr.Close（）;
}
catch（exception ex）
{
//MessageBox.Show（不能打开连接！ ）;
}
终于
{
con.Close（）;
}
i want to compare current date with database date which is expired date...if current date greater than expired date in database then status column in database should changed to "Expired" using C#...i m not getting the code to implement this pls help me its a window application...
What I have tried:
string constring = "Data Source=LENOVO-PC\\SUMIT;Initial Catalog=Ngo_Mgmt;User ID=sa;Password=123";
SqlConnection con = new SqlConnection(constring);
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Subscription_Expire,Status,currentdate from AddMember";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
if (dr["Subscription_Expire"].ToString().Trim() == dr["currentdate"].ToString().Trim())
{
cmd.CommandText = "update AddMember set Status=@Status ";
string ans="Expired";
cmd.Parameters.AddWithValue("@Status",ans);
cmd.ExecuteNonQuery();
}
}
}
else
{
//MessageBox.Show("No Such record Exists", "Alert!");
}
cmd.Dispose();
dr.Close();
}
catch (Exception ex)
{
//MessageBox.Show("Can not open connection ! ");
}
finally
{
con.Close();
}
推荐答案
不要这样做！永远不要将日期转换为字符串并尝试转换它们 - 始终将它们保留为DateTime对象并进行比较。
Don't do that! Never convert dates to strings and try to convert them - always leave them as DateTime objects and compare those.
DateTime expireDate = (DateTime) dr["Subscription_Expire"];
if (expireDate <= DateTime.Now.Date)
{
// Expired.
...这假设您的数据库将日期保留为DATE或DATETIME列 - 如果不存在，则应该基于字符串的日期非常非常快地引起巨大问题！
This assumes that your DB holds dates as DATE or DATETIME columns - and if it doesn't it should as string based dates cause enormous problems very, very quickly!
您可以执行此操作使用单个查询：
You can do that with a single query:
UPDATE
AddMember
SET
Status = 'Expired'
WHERE
Subscription_Expire < GetDate()
;
您 将日期存储为其中一种日期类型 [ ^ ]，而不是字符串，对吧？如果没有，请先解决问题，然后再遇到存储为字符串的日期所带来的重大问题。
注意：您的应用程序从不以 sa 连接到数据库。这是一个无限制的用户，可以用来接管你的服务器，也可能是你的网络。创建一个只具有应用程序所需权限的特定用户，或使用Windows身份验证。
You are storing your dates as one of the date types[^], and not as strings, right? If not, fix that first, before you run into the significant problems that come with dates stored as strings.
NB: Your application should never connect to the database as sa. That is an unlimited user which could be used to take over your server, and possibly your network. Create a specific user which has only the privileges your application needs, or use Windows authentication.
// Using Windows authentication:
const string constring = "Data Source=LENOVO-PC\\SUMIT;Initial Catalog=Ngo_Mgmt;Trusted_Connection=True;";
const string query = @"UPDATE AddMember SET Status = 'Expired' WHERE Subscription_Expire < GetDate();";
using (var connection = new SqlConnection(constring))
using (var command = new SqlCommand(query, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
或许更好的解决方案是数据库中的计算列。
create table test1
（
column1 int identity（1,1）not null primary key，
column2 datetime2 not null default（sysdatetime （）），
column3作为column2< sysdatetime（）的情况然后'this'
else'那''结束
）
插入test1（column2）选择'2010年1月1日'
插入test1（column2）选择'1/1/2050'
select * from test1
perhaps a better solution would be a computed column in the db.
create table test1
(
column1 int identity(1,1) not null primary key,
column2 datetime2 not null default(sysdatetime()),
column3 as case when column2 < sysdatetime() then 'this'
else 'that' end
)
insert into test1(column2) select '1/1/2010'
insert into test1(column2) select '1/1/2050'
select * from test1
这篇关于请帮我查看代码n获取正确的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
