C# ASP.NET MySQL数据库根据DATE字段和TIME字段按照时间段查询，然后绑定到GridView控件显示

最新推荐文章于 2024-04-15 00:06:41 发布

ba_wang_mao

最新推荐文章于 2024-04-15 00:06:41 发布

阅读量902

点赞数 1

分类专栏： ASP.NET MySQL

本文链接：https://blog.csdn.net/ba_wang_mao/article/details/108149346

版权

ASP.NET 同时被 2 个专栏收录

79 篇文章 64 订阅

订阅专栏

MySQL

27 篇文章 3 订阅

订阅专栏

userreco用户数据库内有一个table1表，该表内有2个字段：

lgidate 为DATE型字段

lgitime 为TIME型字段

userreco用户数据库table1表的数据如下图所示：

一、设计前台代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DownLoad.aspx.cs" Inherits="WebApplication1.DownLoad" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        </div>
        <asp:Label ID="Label1" runat="server" style="z-index: 1; left: 62px; top: 179px; position: absolute; width: 158px" Text="起始时间"></asp:Label>
        <asp:Label ID="Label2" runat="server" style="z-index: 1; left: 62px; top: 266px; position: absolute; width: 158px" Text="结束时间"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server" style="z-index: 1; left: 185px; top: 179px; position: absolute; height: 49px; width: 260px; bottom: 231px;"></asp:TextBox>
        <asp:TextBox ID="TextBox2" runat="server" style="z-index: 1; left: 185px; top: 266px; position: absolute; height: 49px; width: 260px"></asp:TextBox>
        <p>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" style="z-index: 1; left: 70px; top: 380px; position: absolute; width: 230px; height: 40px; right: 1172px;" Text="下载" />
        </p>
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" style="z-index: 1; left: 324px; top: 380px; position: absolute; width: 230px; height: 40px" Text="浏览"  />
        <asp:GridView ID="GridView1" runat="server" style="z-index: 1; left: 608px; top: 62px; position: absolute; height: 428px; width: 821px">
        </asp:GridView>
    </form>
</body>
</html>

二、设计后台代码

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;

using System.Data;
using System.Text;
using System.IO;


namespace WebApplication1
{
    public partial class DownLoad : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

     protected void Button2_Click(object sender, EventArgs e)
        {
            string startTime = TextBox1.Text.ToString().Trim();
            string endTime = TextBox2.Text.ToString().Trim();
            //创建字符串连接
            string constr = "Server=localhost;UserId=root;Password=68331;Database=userreco;pooling=false;CharSet=utf8;port=3306";//声明一个字符串用来存放连接数据库的信息
            MySqlConnection sqlconn = new MySqlConnection(constr);//创建一个SqlConnection对象

            //MySqlDataAdapter sqlda = new MySqlDataAdapter("select * from table1 where CONCAT(lgidate,' ',lgitime)>='" + startTime + "' ", sqlconn);
            MySqlDataAdapter sqlda = new MySqlDataAdapter("select * from table1 where CONCAT(lgidate,' ',lgitime)>='" + startTime + "' and CONCAT(lgidate,' ',lgitime)<='" + endTime + "'   ", sqlconn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds);
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
        }
   }

}

三、运行效果

按时间段查询DATE和TIME字段的核心是将DATE和TIME转换成字符串，然后进行比较，参见下图：

startTime为起始时间段

endTime为结束时间段

  string startTime = TextBox1.Text.ToString().Trim();  //起始时间，格式=（年-月-日 时:分:秒）
  string endTime = TextBox2.Text.ToString().Trim();    //结束时间，格式=（年-月-日 时:分:秒）

  string constr = "Server=localhost;UserId=root;Password=68331;Database=userreco;pooling=false;CharSet=utf8;port=3306";//声明一个字符串用来存放连接数据库的信息
  MySqlConnection sqlconn = new MySqlConnection(constr);//创建一个SqlConnection对象         
  MySqlDataAdapter sqlda = new MySqlDataAdapter("select * from table1 where CONCAT(lgidate,' ',lgitime)>='" + startTime + "' and CONCAT(lgidate,' ',lgitime)<='" + endTime + "'   ", sqlconn);
  DataSet ds = new DataSet();
  sqlda.Fill(ds);
  this.GridView1.DataSource = ds;
  this.GridView1.DataBind();

四、按时间段删除数据库中的记录

startTime为起始时间段

endTime为结束时间段

     protected void Button1_Click(object sender, EventArgs e)
        {
            string startTime = TextBox1.Text.ToString().Trim();
            string endTime = TextBox2.Text.ToString().Trim();
            string constr = "Server=localhost;UserId=root;Password=68331;Database=userreco;pooling=false;CharSet=utf8;port=3306";//声明一个字符串用来存放连接数据库的信息
            MySqlConnection con = new MySqlConnection(constr);//创建一个SqlConnection对象

            try
            {
                con.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "Delete from table1 where CONCAT(lgidate,' ',lgitime)>='" + startTime + "' and CONCAT(lgidate,' ',lgitime)<='" + endTime + "'   ";
                if (cmd.ExecuteNonQuery() > 0)
                {
                    Response.Write("<script>alert('清理数据库成功')</script>");
                }
                else
                {
                    Response.Write("<script>alert('清理数据库失败')</script>");
                }
            }

            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

            finally
            {
                con.Close();//关闭数据库连接
            }
        }

ba_wang_mao

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
C# ASP.NET MySQL数据库根据DATE字段和TIME字段按照时间段查询，然后绑定到GridView控件显示

userreco用户数据库内有一个table1表，该表内有2个字段： lgidate 为DATE型字段 lgitime 为TIME型字段userreco用户数据库table1表的数据如下图所示：一、设计前台代码<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DownLoad.aspx.cs" Inherits="WebA...
复制链接

扫一扫