学习随笔-------数据库/控件
1.简单数据呈现
<connectionStrings>
<add name="zl" connectionString="Data Source=.;Initial Catalog=zlTest;User ID=sa;password=111111"/>
</connectionStrings>
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();
}
}
