关于DataGrid里对数据增删改问题!;
我的目的:通过传入数据库和表,把数据库中的表的数据显示在DataGrid上,并能直接在DataGrid 上进行增加,删除,修改操作!(假设数据库中只有一个单表)
下面是我自己编写的用户控件:不过有大问题,通过指定数据库和表后,只能在DataGrid上显示,不能进行增加,删除,修改操作。(个人感觉就是:增删修操作的SQL语句不好写)
//WebForm1.aspx.cs的代码
//它使用后面的控件
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace webkj
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected WebUserControl1 ucBook ;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
ucBook.DBName = "bookdb";
ucBook.SQLCmd = "select * from book ";
ucBook.TabName = "book";
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN:该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
//用户控件 WebUserControl1.ascx.cs 的代码
namespace webkj
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// WebUserControl1 的摘要说明。
/// </summary>
public abstract class WebUserControl1 : System.Web.UI.UserControl
{
public string DBName;//数据库
public string SQLCmd;//查询
public string TabName;//表
//public string key = null;
protected System.Web.UI.WebControls.Label lblTitle;
public DataTable dt = null;
public DataSet ds = null;
public SqlDataAdapter myDataAdapter=null;
public SqlCommandBuilder objAutoGen = null;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public bool shouldRebind = false;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if (Page != null)
{
string connstr = "Server=(local);Integrated
Security=SSPI;database="+DBName;
ds = new DataSet();
SqlConnection conn = new SqlConnection(connstr);
myDataAdapter =new SqlDataAdapter(SQLCmd,conn);
objAutoGen = new SqlCommandBuilder(myDataAdapter);
myDataAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
myDataAdapter.Fill(ds,TabName);
if (TabName != String.Empty)
dt = ds.Tables[TabName];
else
dt = ds.Tables[0];
}
if (!Page.IsPostBack)
{
shouldRebind = true;
BindDataGrid();
}
//this.DataGrid1.DataKeyField = dt.Columns[0];
//this.TextBox1.Text = key.ToString();
}
//绑定DataGrid
private void BindDataGrid()
{
if (shouldRebind)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
//保存操作
public void SaveData()
{
myDataAdapter.Update(ds,TabName);
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN:该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// 设计器支持所需的方法 - 不要使用
/// 代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.DataGrid1.CancelCommand += new
System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand);
this.DataGrid1.EditCommand += new
System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand);
this.DataGrid1.DeleteCommand += new
System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_DeleteCommand);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
//DataGrid里用“属性生成器”建的 “编辑”按钮。
private void DataGrid1_EditCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex =(int)e.Item.ItemIndex;
shouldRebind = true;
}
//DataGrid里用“属性生成器”建的 “取消”按钮。
private void DataGrid1_CancelCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
shouldRebind = true;
}
//DataGrid里用“属性生成器”建的 “删除”按钮。
private void DataGrid1_DeleteCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
/*
// 删除引发命令的行
dt.Rows.RemoveAt(e.Item.DataSetIndex);
// 保存更新的数据
Console.WriteLine("Delete: "+objAutoGen.GetDeleteCommand().CommandText);
SaveData();
this.DataGrid1.EditItemIndex = -1;
shouldRebind = true;
*/
//int number =(int)DataGrid1.DataKeys[e.Item.ItemIndex];
//string str= DataGrid1.DataKeyField ;
//dt.Rows.RemoveAt((int)e.Item.DataSetIndex);
//怎么找到 关键字段 ->DataGrid里的关键值 -》执行SQL删除
string str = DataGrid1.DataKeys[(int)e.Item.ItemIndex].ToString();
string delCmd = "delete from "+TabName+"where"+key+"="+str;
string connstr = "Server=(local);Integrated
Security=SSPI;database="+DBName;
//ds = new DataSet();
SqlConnection conn = new SqlConnection(connstr);
//myDataAdapter =new SqlDataAdapter(SQLCmd,conn);
conn.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = delCmd;
sqlCmd.Connection = conn;
sqlCmd.ExecuteNonQuery();
/*
conn.Close();
SaveData();
DataGrid1.EditItemIndex = -1;
shouldRebind = true;
*/
}
}
}