学习随笔-------数据库/控件-优快云博客

本文介绍了使用ASP.NET进行数据库连接、数据读取、插入、更新及删除等基本操作，并展示了如何利用DataSet、DataView进行数据绑定，实现数据展示。

1.简单数据呈现

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            reader();
        }
    }
    public void reader()
    {

        //从Webconfig读取数据库链接字段，创建SqlConnection。
        SqlConnection my = new SqlConnection(ConfigurationManager.ConnectionStrings["zl"].ConnectionString);
        string commdtext = "select News_Title from News";
        SqlCommand com = new SqlCommand(commdtext, my);
        SqlDataReader dr = null;
        try
        {
            my.Open();
            dr = com.ExecuteReader();

            //从SqlDataReader读取数据
            while (dr.Read())
            {
                ListBox1.Items.Add(dr[0].ToString());
            }
            dr.Close();
            SqlDataReader dr2 = com.ExecuteReader();
            ListBox2.DataSource = dr2;
            ListBox2.DataTextField = "News_Title";
            ListBox2.DataBind();

            dr2.Close();
        }
        catch(Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            my.Close();
        }
    }

2、DataSet数据绑定

     （1）DataSet ds = new DataSet();
            dr.Fill(ds,"News");
            DropDownList1.DataSource = ds;
            DropDownList1.DataTextField = "News_Title";
            DropDownList1.DataBind();

      (2) foreach (DataRow dd in ds.Tables[0].Rows)
            {
                ListBox3.Items.Add(dd["News_PublisherTime"] + "----" + dd["News_Title"]);
            }

           (3)for (int i = 0; i < ds.Tables["News"].Rows.Count; i++)
             {
                     ListBox3.Items.Add(ds.Tables["news"].Rows[i]["News_PublisherTime"].ToString() + "++++" + ds.Tables["news"].Rows[i]

["News_Title"].ToString());
}

3、DataView 视图以最小的数据绑定到控件。同时具有排序、搜索、筛选功能。

筛选例：

        DataView dv = new DataView();
        dv = ds.Tables[0].DefaultView;
        if (DropDownList2.SelectedItem.Text == "All")
        {

            dv.RowFilter = "News_Title like '*'";

        }
        else
        {
            dv.RowFilter = "News_Title like '*d*'";
        }
        ListBox3.DataSource = dv;
        ListBox3.DataTextField = "News_Title" ;
        ListBox3.DataBind();

4、小DEMO

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;


public partial class Updatedata : System.Web.UI.Page
{
    public static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["zl"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LBind();
        }
    }

    public void LBind()
    {
        string sqlText="select*from bm";
        SqlDataAdapter dr = new SqlDataAdapter(sqlText, conn);
        DataSet ds = new DataSet();
        try
        {
            dr.Fill(ds);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                ListBox1.Items.Add(row["bmid"] + "-------" + row["bmname"]);
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    /// <summary>
    /// 增加
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        int youmei;
        //比对现在文本框的内容是否在数据库存在
        string sqltext1 = "select count(*) from bm where bmid='" + TextBox1.Text.Trim() + "' or BMName='" + TextBox2.Text.Trim() + "'";
        SqlCommand cmd1 = new SqlCommand(sqltext1, conn);
        try
        {
            conn.Open();
            //转换为整数
            youmei = Convert.ToInt32(cmd1.ExecuteScalar());
            //判断数据库有没有记录
            if (youmei != 0)
            {
                Response.Write("<script>alert('添加了重复记录,请重新添加')</script>");
            }
            else
            {
               
                    string sqltext = "insert into bm (bmid,bmname) values ('" + TextBox1.Text.Trim() + "','" + TextBox2.Text.Trim() + "')";
                    SqlCommand cmd = new SqlCommand(sqltext, conn);
                    int youmei1= cmd.ExecuteNonQuery();
                    //判断有没有受影响的行
                    if (youmei1 > 0)
                    {
                        Response.Write("<script>alert('添加成功，可以点击查看按钮查看')</script>");
                    }
                    else
                    {
                        Response.Write("<script>alert('添加失败，请重试!')</script>");
                    }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
       

    }

    /// <summary>
    /// 查看
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button4_Click(object sender, EventArgs e)
    {
        ListBox1.Items.Clear();
        LBind();
    }

    /// <summary>
    /// 修改
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button2_Click(object sender, EventArgs e)
    {
        int youmei =0;
        //比对现在文本框的内容是否在数据库存在
        string sqltext1 = "select count(*) from bm where  BMName='" + TextBox2.Text.Trim() + "'";
        SqlCommand cmd1 = new SqlCommand(sqltext1, conn);
        try
        {
            conn.Open();
            //转换为整数
            youmei = Convert.ToInt32(cmd1.ExecuteScalar());
            //判断数据库有没有记录
            if (youmei != 0)
            {
                Response.Write("<script>alert('添加了重复记录,请重新添加')</script>");
            }
            else
            {
                string sqltext = "update bm set bmname='" + TextBox2.Text.Trim() + "'where bmid='"+TextBox1.Text.Trim()+"'";
                SqlCommand cmd = new SqlCommand(sqltext, conn);
                int youmei1 = cmd.ExecuteNonQuery();
                if (youmei1 > 0)
                {
                    Response.Write("<script>alert('修盖成功')</script>");
                }
                else
                {
                    Response.Write("<script>alert('修盖失败，请重新修改')</script>");
                }
            }
            ListBox1.Items.Clear();
            LBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button3_Click(object sender, EventArgs e)
    {
        SqlConnection conn1 =new SqlConnection (ConfigurationManager.ConnectionStrings["zl"].ConnectionString);
        string sqlt = "delete from bm where bmid='" + TextBox1.Text.Trim() + "'";
        SqlCommand cmd = new SqlCommand(sqlt, conn);
        conn.Open();
        cmd.ExecuteNonQuery();
        Response.Write("<script>alert('删除成功')</script>");

        ListBox1.Items.Clear();
        LBind();
    }
}

转载于:https://www.cnblogs.com/loalongblogs/articles/2020136.html