一、数据库信息
有一个数据库名为userinfo,该数据库下有一个table1的表。
table1表下有以下几个字段:
username,psw,lgidate,lgitime
username为字符串类型字段
psw为字符串类型字段
lgidate为日期型字段
lgitime为时间型字段
二、有一个WEB登录页面
要求输入用户名和密码,然后点击【登录】按钮开始查询。
如果查询到该用户名在userinfo数据库的table1表中存在,则将该记录的lgidate字段更新为当前日期(年-月-日),lgitime字段更新为当前时间(时:分:秒)。
三、后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
namespace WebApplication1
{
public partial class Login : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string username = TextBox1.Text.ToString().Trim();
string psw = TextBox2.Text.ToString().Trim();
string lgidate = DateTime.Now.ToString("yyyy-MM-dd");
string lgitime = DateTime.Now.ToString("HH:mm:ss");
if (!(string.IsNullOrEmpty(username)))
{
if (!(string.IsNullOrEmpty(psw)))
{
string commandStr = "select *from table1 where username = '" + username + "' and psw = '" + psw + "'";
string ConnectionStr = "server=localhost;port=3306;user Id=root;password=68331; database=userinfo ;Allow User Variables=True"; //连接字符串
MySqlConnection Connection = new MySqlConnection(ConnectionStr);//实例化链接,创建Connection对象
MySqlCommand command = new MySqlCommand();
command.Connection = Connection;
command.CommandText = commandStr;
try
{
Connection.Open();
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
if (Connection != null)
Connection.Close();//关闭连接
MySqlCommand cmd = new MySqlCommand("Update table1 set lgidate='"+lgidate+"' , lgitime='"+lgitime+ "' where username='" + username + "'", Connection);
try
{
Connection.Open();
if (cmd.ExecuteNonQuery() > 0)
{
Response.Write("<script>alert('数据库更新登录时间OK')</script>");
Response.Redirect("Home.aspx");
}
else
{
Response.Write("<script>alert('数据库更新登录时间错误')</script>");
}
}
catch (Exception ex)//创建检查Exception对象
{
Response.Write("<script>alert(ex.Message)</script>");
}
finally
{
if (Connection != null)
Connection.Close();//关闭连接
}
}
else
{
Response.Write("<script>alert('读数据库错误')</script>");
}
}
catch (Exception ex)//创建检查Exception对象
{
Response.Write("<script>alert(ex.Message)</script>");
}
finally
{
if (Connection != null)
Connection.Close();//关闭连接
}
}
else
{
Response.Write("<script>alert('错误:密码为空')</script>");
}
}
else
{
Response.Write("<script>alert('错误:用户名为空')</script>");
}
}
}
}
总结:
在userinfo数据库的table1表中查找符合要求的用户名(要查找的用户名存放在TextBox1文本框中,然后赋值给字符串变量username),数据库中找到符合要求的用户名后,将lgidate字段更新为当前日期(年-月-日),lgitime字段更新为当前时间(时:分:秒)。
string username = TextBox1.Text.ToString().Trim();
string psw = TextBox2.Text.ToString().Trim();
string lgidate = DateTime.Now.ToString("yyyy-MM-dd");
string lgitime = DateTime.Now.ToString("HH:mm:ss");
string ConnectionStr = "server=localhost;port=3306;user Id=root;password=68331; database=userinfo ;Allow User Variables=True"; //连接字符串
MySqlConnection Connection = new MySqlConnection(ConnectionStr);//实例化链接,创建Connection对象
MySqlCommand cmd = new MySqlCommand("Update table1 set lgidate='"+lgidate+"' , lgitime='"+lgitime+ "' where username='" + username + "'", Connection);
try
{
Connection.Open();
if (cmd.ExecuteNonQuery() > 0)
{
Response.Redirect("Home.aspx"); //跳转到主页面
}
else
{
Response.Write("<script>alert('数据库更新登录时间错误')</script>");
}
}
catch (Exception ex)
{
Response.Write("<script>alert(ex.Message)</script>");
}
finally
{
if (Connection != null)
Connection.Close();//关闭连接
}
结果: