webconfig文件:
<connectionStrings>
<add name="pubs" connectionString="Data Source=.;user=sa;password=123456;" providerName="System.Data.SqlClient"/>
</connectionStrings>
数据库连接类库:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
namespace bindData.connection
{
/// <summary>
/// 连接类
/// </summary>
public class Connection
{
/// <summary>
/// 连接数据库的字符
/// </summary>
public static string connStr = ConfigurationManager.ConnectionStrings["pubs"].ConnectionString;
}
}
主页面:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="showGridView.aspx.cs" Inherits="bindData.connection.showGridView" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.hidden
{
display: none;
}
</style>
<script type="text/jscript" language="javascript">
/*http://hi.baidu.com/%BB%B0%C3%B7%CE%B6%B5%C4%CC%C7/blog/item/a0faa7051b44c5121d9583ac.html*/
//添加
function add() {
var result = window.showModalDialog("showGridViewAddDialog.aspx", "", "dialogWidth:800px;DialogHeight=400px;help=0;center=1;status:yes;scroll=1");
if (result == true) {
window.location.href = document.URL;
}
}
function edit(id) {
var result = window.showModalDialog("showGridViewAddDialog.aspx?date=" + new Date() + "&id=" + id, "", "dialogWidth:800px;DialogHeight=400px;help=0;center=1;status:yes;scroll=1");
if (result == true) {
window.location.href = document.URL;
}
}
//全选
function checkAll() {
var checkBox = document.getElementsByTagName("input");
for (var i = 0; i < checkBox.length; i++) {
if (checkBox[i].type == "checkbox") {
checkBox[i].checked = document.all.cb_title.checked;
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<ContentTemplate>
姓名:
<asp:TextBox ID="tb_name" runat="server" />
<asp:Button ID="btn_search" Text="查询" runat="server" OnClick="btn_search_Click" />
<asp:Button ID="btn_delete" Text="勾选删除" runat="server" OnClick="btn_delete_Click" />
<input type="button" value="添加" οnclick="add();" />
<asp:GridView ID="gridView_info" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<input type="checkbox" id="cb_title" οnclick="checkAll();">
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="cb_item" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="id">
<ItemStyle CssClass="hidden" />
<HeaderStyle CssClass="hidden" />
</asp:BoundField>
<asp:BoundField DataField="names" HeaderText="姓名" />
<asp:TemplateField HeaderText="性别">
<ItemTemplate>
<%# showSex(Eval("sex").ToString())%>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="age" HeaderText="年龄" />
<asp:BoundField DataField="birthday" HeaderText="出生日期" />
<asp:BoundField DataField="explain" HeaderText="个人说明" />
<asp:BoundField DataField="email" HeaderText="邮箱" />
<asp:BoundField DataField="telephone" HeaderText="电话" />
<asp:TemplateField HeaderText="性别">
<ItemTemplate>
<asp:DropDownList ID="ddl_sex" runat="server" SelectedValue='<%#Eval("sex") %>' AutoPostBack="true"
OnSelectedIndexChanged="ddl_sex_SelectedIndexChanged">
<asp:ListItem Value="" Text="--请选择--" />
<asp:ListItem Value="0" Text="女" />
<asp:ListItem Value="1" Text="男" />
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<a href='javascript:edit(<%#Eval("id") %>)'>修改</a>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</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 System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace bindData.connection
{
public partial class showGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindData(string.Empty);
}
}
/// <summary>
/// 绑定数据源
/// </summary>
private void bindData(string filter)
{
string sql = string.Format("select * from company where names like '%{0}%' ", filter);
SqlConnection sqlConnection = new SqlConnection(Connection.connStr);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, Connection.connStr);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "company");
DataView dv = ds.Tables["company"].DefaultView;
gridView_info.DataSource = dv;
gridView_info.DataBind();
}
/// <summary>
/// 显示性别:0=女 1=男
/// </summary>
/// <param name="sex"></param>
/// <returns></returns>
protected string showSex(string sex)
{
string sexStr = string.Empty;
if (!string.IsNullOrEmpty(sex.ToString()))
{
if (sex == "0")
{
sexStr = "女";
}
else
{
sexStr = "男";
}
}
return sexStr;
}
//删除
protected void btn_delete_Click(object sender, EventArgs e)
{
using (SqlConnection sqlConnection = new SqlConnection(Connection.connStr))
{
sqlConnection.Open();
for (int i = 0; i < gridView_info.Rows.Count; i++)
{
CheckBox cb_item = gridView_info.Rows[i].Cells[0].FindControl("cb_item") as CheckBox;
if (cb_item.Checked)
{
string sql = "delete from company where id='" + gridView_info.Rows[i].Cells[1].Text + "'";
SqlCommand sqlCommmand = new SqlCommand(sql, sqlConnection);
int deleteRow = sqlCommmand.ExecuteNonQuery();
if (deleteRow > 0)
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('删除成功!');", true);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('删除失败!');", true);
}
}
}
sqlConnection.Close();
}
bindData(string.Empty);
}
//查询
protected void btn_search_Click(object sender, EventArgs e)
{
bindData(tb_name.Text.Trim());
}
//dropDownList选中后直接保存到数据库
protected void ddl_sex_SelectedIndexChanged(object sender, EventArgs e)
{
using (SqlConnection sqlConnection = new SqlConnection(Connection.connStr))
{
sqlConnection.Open();
DropDownList dropdownList = sender as DropDownList;
GridViewRow gridViewRow = dropdownList.NamingContainer as GridViewRow;
if (!string.IsNullOrEmpty(dropdownList.SelectedValue))
{
string sql = string.Format("update company set sex='{0}' where id='{1}'", dropdownList.SelectedValue, gridView_info.Rows[gridViewRow.RowIndex].Cells[1].Text);
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
//执行添加语句
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改成功!');", true);
bindData(string.Empty);
return;
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改失败!');", true);
}
}
}
}
}
}
添加、修改页面:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="showGridViewAddDialog.aspx.cs"
Inherits="bindData.connection.showGridViewAddDialog" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Cache-Control" content="no-cache">
<meta http-equiv="Expires" content="0">
<head runat="server">
<title></title>
<script type="text/javascript" language="javascript">
//刷新
function refresh() {
window.returnValue = true;
window.close();
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:HiddenField ID="hf_id" runat="server" />
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<table style="text-align: center">
<asp:UpdatePanel ID="up_add" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<tr>
<td>
姓名:
</td>
<td>
<asp:TextBox ID="tb_names" runat="server" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btn_submit" runat="server" Text="确定" OnClick="btn_submit_Click" />
</td>
</tr>
</ContentTemplate>
</asp:UpdatePanel>
</table>
<div>
<asp:UpdatePanel ID="up_sex" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:DropDownList ID="ddl_sex" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddl_sex_SelectedIndexChanged">
<asp:ListItem Value="0" Text="女" />
<asp:ListItem Value="1" Text="男" />
</asp:DropDownList>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</div>
</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 System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace bindData.connection
{
public partial class showGridViewAddDialog : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ScriptManager1.RegisterAsyncPostBackControl(btn_submit);
/*
!isPostBack解释:
*1.假设你在page_load()里定义textbox为空,在button_click事件里给写一段往数据库存textbox的值.
*当用户往 textbox里输入值以后,点击button时,就应该可以把往textbox里输入的值存到数据库里.
*可实际上,当点击button的时候,系统会自动先重新执行page_load(),也就是把柄textbox清空,再执行click 事件,
*你往数据库里存的值永远都是空.所以要在page_Load() 里加入if(!IsPostBack).
*2. 第一次显示的时候,IF(!IsPostBack)这个IF里面的语句是执行的,以后页面重新刷新的话
这个IF里面的语句不执行
*2.IsPostBack==ture 当前页面是第一次加载
通常用在page_load中,获取一个值,该值指示该页是否正为响应客户端回发而加载,
* 或者它是否正被首次加载和访问,如果是为响应客户端回发而加载该页,则为true;否则为 false
*/
if (!IsPostBack)
{
hf_id.Value = Server.UrlDecode(Request.QueryString["id"]);
if (!string.IsNullOrEmpty(hf_id.Value))
{
editShow();
}
}
}
protected void btn_submit_Click(object sender, EventArgs e)
{
//插入命令
if (string.IsNullOrEmpty(hf_id.Value))
{
add();
}
else
{
edit();
}
// up_add.Update();
}
/// <summary>
/// 添加
/// </summary>
private void add()
{
try
{
using (SqlConnection connection = new SqlConnection(Connection.connStr))
{
connection.Open();
string sqlstr = "insert into company (names) values(@names)";
SqlCommand sqlCommand = new SqlCommand(sqlstr, connection);
//添加参数
sqlCommand.Parameters.Add(new SqlParameter("@names", SqlDbType.VarChar, 10));
//给参数赋值
sqlCommand.Parameters["@names"].Value = tb_names.Text;
//执行添加语句
int result = sqlCommand.ExecuteNonQuery();
//window.close();window.returnValue = true;
if (result > 0)
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('添加成功!');refresh();", true);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('添加失败!');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
/// <summary>
/// 修改前赋值
/// </summary>
private void editShow()
{
try
{
using (SqlConnection connection = new SqlConnection(Connection.connStr))
{
connection.Open();
string sql = string.Format("select count(*) from company where id='{0}'", hf_id.Value);
SqlCommand sqlCommand = new SqlCommand(sql, connection);
int count = Convert.ToInt32(sqlCommand.ExecuteScalar());
if (count > 0)
{
string sqlStr = string.Format("select * from company where id='{0}'", hf_id.Value);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlStr, connection);
DataSet ds = new DataSet();
sqlDataAdapter.Fill(ds, "company");
tb_names.Text = ds.Tables["company"].Rows[0]["names"].ToString();
ddl_sex.SelectedValue = ds.Tables["company"].Rows[0]["sex"].ToString();
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
/// <summary>
/// 修改
/// </summary>
private void edit()
{
try
{
using (SqlConnection connection = new SqlConnection(Connection.connStr))
{
connection.Open();
string sqlstr = string.Format("update company set names='{0}' where id='{1}'", tb_names.Text, hf_id.Value);
SqlCommand sqlCommand = new SqlCommand(sqlstr, connection);
//执行添加语句
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改成功!');refresh();", true);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改失败!');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
//dropDownList选中后直接保存到数据库
protected void ddl_sex_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
using (SqlConnection connection = new SqlConnection(Connection.connStr))
{
connection.Open();
string sqlstr = string.Format("update company set sex='{0}' where id='{1}'", ddl_sex.SelectedValue, hf_id.Value);
SqlCommand sqlCommand = new SqlCommand(sqlstr, connection);
//执行添加语句
int result = sqlCommand.ExecuteNonQuery();
if (result > 0)
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改成功!');", true);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "", "alert('修改失败!');", true);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
up_sex.Update();
}
}
}