GridView的增删改查

合集下载

数据控件DataGridView添加、删除和修改数据库中的内容

数据控件DataGridView添加、删除和修改数据库中的内容作者：天涯来源：中国自学编程网发布日期：1214063638介绍一个数据控件DataGridView，它是 3.5中新增加的的重要控件。

它是一种网格形式的控件，能以表格的形式显示数据，它的优势是能多行显示数据，在数据库的操作中会经常用到。

（1）打开VS2008，在D:\C#\ch14目录下建立名为DataGridViewTest的Windows应用程序，打开工程，为当前窗体添加控件，如表14-2所示。

表14-2 添加控件列表控件名NameTextDataGridViewdataGridView1ButtonbtnRef更新设置ButtonbtnDelete删除（2）接下来需要设置DadaAdapter和DataSet，方法同上一节一样。

选中DataGridView的DataSource属性。

（3）单击“下一步”按钮，选择“数据连接”图标。

（4）最后一步需要选择数据库对象，本例是要操作StudentInf数据库中的表，所以选择“表”复选框。

（5）设置完毕后，整个程序界面就设置完了。

程序界面设计完毕后，接下来要做的工作就是通过修改dataGridView1中的数据来更新数据库中的内容。

它实现的原理很简单，通过studentInfDataSet把dataGridView1绑定到Class1表，studentInfDataSet处于中间位置，所以在dataGridView1中修改的数据必须要传递到studentInfDataSet后才能改变数据库中的内容。

（1）双击“更新设置”按钮，添加如下代码。

this.sqlDataAdapter1.Update(this.studentInfDataSet);该代码的功能是调用sqlDataAdapter1的Update()方法实现对studentInfDataSet的更新。

（2）按F5键，程序运行以后，对dataGridView1添加一行新的数据，然后单击“更新设置”按钮，完成后关闭程序再打开。

GridView增删改三层完整版

：1)DiaryModel（业务实体层），新建一个User类代码如下：using System;using System.Collections.Generic;using System.Text;namespace DiaryModel{[Serializable] //序列化public class Users{int _UserID;string _UserName;string _Password;public int UserID{get { return _UserID; }set { _UserID = value; }}///<summary>///用户名///</summary>public string UserName{get { return _UserName; }set { _UserName = value; }}///<summary>///密码///</summary>public string Password{get { return _Password; }set { _Password = value; }}}该类可以获得User类的各个字段。

<appSettings><add key="DbHelperProvider" value="System.Data.SqlClient"/></appSettings><connectionStrings><add name="DbHelperConnectionString" connectionString="Data Source=FAN;InitialCatalog=test;User ID=sa;Password=20;Connect Timeout=18000"/></connectionStrings>（将DBHelper.cs类纺织DiaryDAL目录下，要注意的是DBHelper类下的命名空间要改为DiaryDAL）新建一个UserService.cs类，实现增删改查等相关操作，具体代码如下所示：using System;using System.Collections.Generic;using System.Text;using System.Data;using DiaryModel; //记得要添加引用namespace DiaryDAL{public class UserService{//添加public static bool Add(Users user){string sql = "insert into Users(username,password) values(@username,@password)";Dictionary<string,object> dic=new Dictionary<string,object>();dic.Add("@username", erName);dic.Add("@password", user.Password);return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false ;}//删除public static bool Delete(int UserID){string sql = "delete from Users where ID=@UserID";Dictionary<string, object> dic = new Dictionary<string, object>();dic.Add("@UserID", UserID);return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false;}//修改public static bool Modify(Users user){string sql = "update Users set username=@username,password=@password where ID=@UserID";Dictionary<string, object> dic = new Dictionary<string, object>();dic.Add("@username", erName);dic.Add("@password", user.Password);dic.Add("@UserID", erID);return DBHelper.ExecuteNonQuery(sql, dic) > 0 ? true : false;}//显示public static List<Users> GetAllUsers(){string sql = "select * from Users";DataTable dt = DBHelper.GetDataSet(sql);List<Users> list = new List<Users>();foreach (DataRow dr in dt.Rows){Users user = new Users();erID = (int)dr["ID"];erName = dr["username"].ToString();user.Password = dr["password"].ToString();list.Add(user);}return list;}}}2)DiaryBLL（业务逻辑层）新建一个UserManage.cs类，具体代码如下：using System;using System.Collections.Generic;using System.Text;using System.Data;using DiaryModel;namespace DiaryBLL{public class UserManage{public static bool Add(Users user){return erService.Add(user);}public static bool Delete(int UserID){return erService.Delete(UserID);}public static bool Modify(Users user){return erService.Modify(user);}public static List<Users> GetAllUsers(){return erService.GetAllUsers();}}}<%@Page Language="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/1999/xhtml"><head runat="server"><title>无标题页</title></head><body><form id="form1"runat="server"><div>用户列表：<br/><asp:GridView ID="GridView1"runat="server"AutoGenerateColumns="False"BackColor="White"BorderColor="#CCCCCC"BorderStyle="None"BorderWidth="1px"CellPadding="3"PageSize="4"Width="778px"OnRowDeleting="GridView1_RowDeleting" OnRowDataBound="GridView1_RowDataBound"OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="GridView1_RowCancelingEdit"OnRowUpdating="GridView1_RowUpdating"> <FooterStyle BackColor="White"ForeColor="#000066"/><RowStyle ForeColor="#000066"/><Columns><asp:TemplateField HeaderText="用户ID"><ItemTemplate><asp:Label ID="Label1"runat="server"Text='<%# Bind("UserID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="用户名"><EditItemTemplate><asp:TextBox ID="TextBox2"runat="server"Text='<%# Bind("UserName") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2"runat="server"Text='<%# Bind("UserName") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="密码"><EditItemTemplate><asp:TextBox ID="TextBox3"runat="server"Text='<%# Bind("Password") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label3"runat="server"Text='<%# Bind("Password") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="操作"ShowHeader="False"><EditItemTemplate><asp:LinkButton ID="LinkButton1"runat="server"CausesValidation="True" CommandName="Update"Text="更新"></asp:LinkButton><asp:LinkButton ID="LinkButton2"runat="server"CausesValidation="False" CommandName="Cancel"Text="取消"></asp:LinkButton></EditItemTemplate><ItemTemplate><asp:LinkButton ID="LinkButton1"runat="server"CausesValidation="False" CommandName="Edit"Text="编辑"></asp:LinkButton><asp:LinkButton ID="LinkButton2"runat="server"CausesValidation="False" CommandName="Delete"OnClientClick="javascript:return confirm('确认要删除么?');"Text="删除"></asp:LinkButton></ItemTemplate></asp:TemplateField></Columns><PagerStyle BackColor="White"ForeColor="#000066"HorizontalAlign="Left"/><SelectedRowStyle BackColor="#669999"Font-Bold="True"ForeColor="White"/><HeaderStyle BackColor="#006699"Font-Bold="True"ForeColor="White"/></asp:GridView></div><br/><br/><br/>添加用户<br/>用户名：<asp:TextBox ID="tb_username"runat="server"></asp:TextBox><br/>密码：  <asp:TextBox ID="tb_pwd"runat="server"></asp:TextBox><br/><asp:Button ID="Button1"runat="server"Text="添加"OnClick="Button1_Click"/> </form></body></html>后台代码如下所示：using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using DiaryModel;public partial class_Default : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){Bind();}}protected void Bind(){GridView1.DataSource = erManage.GetAllUsers();GridView1.DataBind();}protected void Button1_Click(object sender, EventArgs e)Users user = new Users();erName = this.tb_username.Text.ToString().Trim();user.Password = this.tb_pwd.Text.ToString().Trim();bool bol=erManage.Add(user);if (bol){Response.Redirect("Default.aspx");}}protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e){int UserID = Convert.ToInt32((GridView1.Rows[e.RowIndex].FindControl("Label1") as Label).Text);bool bol = erManage.Delete(UserID);if (bol){Bind();}else{Response.Write("<script>alert('删除失败');location.href=Default.aspx;</script>");}}protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e){if (e.Row.RowType == DataControlRowType.DataRow){LinkButton lb = e.Row.FindControl("LinkButton2") as LinkButton;if (lb.Text == "删除"){lb.Attributes.Add("onclick", "return confirm('确认要删除么?');");}}}///<summary>///让当前处于修改状态///</summary>///<param name="sender"></param>///<param name="e"></param>protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e){GridView1.EditIndex = e.NewEditIndex;Bind();}///<summary>///让当前行处于绑定状态///</summary>///<param name="sender"></param>///<param name="e"></param>protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e){GridView1.EditIndex = -1;Bind();}///<summary>///更新至数据库///</summary>///<param name="sender"></param>///<param name="e"></param>protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e){Users user = new Users();erID = Convert.ToInt32((GridView1.Rows[e.RowIndex].FindControl("Label1") as Label).Text);erName = (GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox).Text.ToString();user.Password = (GridView1.Rows[e.RowIndex].FindControl("TextBox3") as TextBox).Text.ToString();bool bol = erManage.Modify(user);if (bol){Response.Write("<script>alert('修改成功');</script>");GridView1.EditIndex = -1;Bind();}else{Response.Write("<script>alert('修改失败');</script>");}}。

使用DataGridView进行增删改查,并同步到数据库

使用DataGridView进行增删改查，并同步到数据库DataGridView控件具有极高的可配置性和可扩展性。

它提供有大量的属性、方法和事件，可以用来对该控件的外观和行为进行自定义。

下面通过一个小例子来展示DataGridView进行增删改查，并同步到数据库的功能。

窗体展示：用户需求：1.当窗体显示时，将数据库中用户表中的数据显示出来。

2.选中一行，执行删除操作，同时在数据库中相应数据被删除。

3.双击某个数据，进行编辑，或者在空白行添加新的数据，然后点击更新，数据库随之更新。

代码展示：[vb] view plaincopyPublic Class Form1'代码较简单，没有使用三层架构。

Public DT As DataTable Public SDA As SqlDataAdapter Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.LoadDim conn = NewSqlConnection("Server=***;DataBase=userinfo;UserID=***;Password=***") SDA = New SqlDataAdapter("select * from Users", conn) DT = New DataTable SDA.Fill(DT) '将查到的数据传到DataTable中DataGridView1.DataSource = DT '将DataTable中的数据传给DataGridView1显示End Sub '更新操作Private SubbtnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click Dim SCB = New SqlCommandBuilder(SDA) SDA.Update(DT) MsgBox("更新成功") End Sub '删除操作Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click '删除选中行DataGridView1.Rows.RemoveAt(DataGridView1.CurrentCell. RowIndex) '数据库中进行删除Dim SCB = New SqlCommandBuilder(SDA)SDA.Update(DT) MsgBox("删除成功")End Sub End Class注意：1.数据库中相应的表中一定要有主键。

c#gridview控件中添、删、改、查数据

c#gridview控件中添、删、改、查数据主要是实现类似于jQGrid那种页⾯效果⼤佬整理的，借鉴⼀下1）前台代码如下<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="GridViewDemo._Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/1999/xhtml" ><head runat="server"><title></title><style type="text/css">body{ font-size:12px;}</style><script language="javascript" type="text/javascript">function deleteStudent() {if(!confirm('are you sure to delete this student?')){return false;}}</script></head><body><form id="form1" runat="server"><div><asp:GridView ID="gvwStudent" runat="server" AutoGenerateColumns="False"ShowFooter="true" onrowcommand="gvwStudent_RowCommand"><Columns><%--编号--%><asp:TemplateField HeaderText="id"><ItemTemplate><%#Eval("studentID") %></ItemTemplate><EditItemTemplate><%#Eval("studentID") %></EditItemTemplate></asp:TemplateField><%--姓名--%><asp:TemplateField HeaderText="name"><ItemTemplate><%#Eval("studentName") %></ItemTemplate><EditItemTemplate><asp:TextBox ID="txtStudentName" runat="server" Text='<%#Eval("studentName") %>'></asp:TextBox></EditItemTemplate><FooterTemplate><asp:TextBox ID="txtStudentName" runat="server"></asp:TextBox></FooterTemplate></asp:TemplateField><%--性别--%><asp:TemplateField HeaderText="sex"><ItemTemplate><%#Eval("studentSex") %></ItemTemplate><EditItemTemplate><asp:RadioButton ID="rbtnMale" Text="male" runat="server" GroupName="1" Checked='<%#Eval("studentSex").ToString()=="male"?true:false %>'/><asp:RadioButton ID="rbtnFemale" Text="female" runat="server" GroupName="1" Checked='<%#Eval("studentSex").ToString()=="female"?true:false %>' /></EditItemTemplate><FooterTemplate><asp:RadioButton ID="rbtnMale" Text="male" runat="server" GroupName="1" Checked="true"/><asp:RadioButton ID="rbtnFemale" Text="female" runat="server" GroupName="1" /></FooterTemplate></asp:TemplateField><%--年龄--%><asp:TemplateField HeaderText="age"><ItemTemplate><%#Eval("studentAge") %></ItemTemplate><EditItemTemplate><asp:TextBox ID="txtAge" runat="server" Text='<%#Eval("studentAge") %>'></asp:TextBox></EditItemTemplate><FooterTemplate><asp:TextBox ID="txtAge" runat="server"></asp:TextBox></FooterTemplate></asp:TemplateField><%--修改--%><asp:TemplateField HeaderText="edit"><ItemTemplate><asp:LinkButton ID="lbtnEdit" runat="server" CommandName="studentEdit" CommandArgument='<%#Eval("studentID") %>'>Edit</asp:LinkButton></ItemTemplate><EditItemTemplate><asp:LinkButton ID="lbtnUpdate" runat="server" CommandName="studentUpdate" CommandArgument='<%#Eval("studentID") %>'>Update</asp:LinkButton></EditItemTemplate><FooterTemplate><asp:LinkButton ID="lbtnSave" runat="server" CommandName="studentAdd">Add</asp:LinkButton></FooterTemplate></asp:TemplateField><%--删除--%><asp:TemplateField HeaderText="delete"><ItemTemplate><asp:LinkButton ID="lbtnDelete" runat="server" CommandName="studentDelete" CommandArgument='<%#Eval("studentID") %>' OnClientClick="return deleteStudent();">Delete</asp:LinkButton> </ItemTemplate></asp:TemplateField></Columns></asp:GridView><asp:Label ID="lblMessage" runat="server" Text="" style=" color:Red;"></asp:Label></div></form></body></html>2）后台代码如下using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.Linq;using System.Data;namespace WebApplication7{public partial class _Default : System.Web.UI.Page{string nodata = "no data!";StudentClassesDataContext scdc = new StudentClassesDataContext(@"server=.\sqlexpress;database=School;uid=sa;pwd=1");protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){BindStudent();}else//防⽌PostBack时页⾯显⽰变化{if (gvwStudent.Rows.Count == 1 && gvwStudent.Rows[0].Cells[0].Text == nodata){int columnCount = gvwStudent.Columns.Count;gvwStudent.Rows[0].Cells.Clear();gvwStudent.Rows[0].Cells.Add(new TableCell());gvwStudent.Rows[0].Cells[0].ColumnSpan = columnCount;gvwStudent.Rows[0].Cells[0].Text = nodata;gvwStudent.Rows[0].Cells[0].Style.Add("text-align", "center");}}}///<summary>/// bind student///</summary>private void BindStudent(){Table<Student> students = scdc.GetTable<Student>();//gvwStudent.DataSource = from student in students where student.studentSex == "male" select student;if (students.Count() > 0){gvwStudent.DataSource = students;gvwStudent.DataBind();}else//增加空⾏来显⽰GridView的结构{DataTable dtStudent = new DataTable();dtStudent.Columns.Add(new DataColumn("studentID"));dtStudent.Columns.Add(new DataColumn("studentName"));dtStudent.Columns.Add(new DataColumn("studentSex"));dtStudent.Columns.Add(new DataColumn("studentAge"));if (dtStudent.Rows.Count == 0){dtStudent.Rows.Add(dtStudent.NewRow());}gvwStudent.DataSource = dtStudent;gvwStudent.DataBind();int columnCount = gvwStudent.Columns.Count;gvwStudent.Rows[0].Cells.Clear();gvwStudent.Rows[0].Cells.Add(new TableCell());gvwStudent.Rows[0].Cells[0].ColumnSpan = columnCount;gvwStudent.Rows[0].Cells[0].Text = nodata;gvwStudent.Rows[0].Cells[0].Style.Add("text-align", "center");}}protected void gvwStudent_RowCommand(object sender, GridViewCommandEventArgs e){switch (mandName){case"studentAdd"://添加{//获取选中⾏GridViewRow gridViewRow = (GridViewRow)((LinkButton)mandSource).NamingContainer;//姓名TextBox txtStudentName = (TextBox)gridViewRow.FindControl("txtStudentName");string studentName = txtStudentName.Text.Trim();if (studentName == "") { lblMessage.Text = "please input student name!"; return; }//性别RadioButton rbtnMale = (RadioButton)gridViewRow.FindControl("rbtnMale");string studentSex = rbtnMale.Checked ? rbtnMale.Text : ((RadioButton)gridViewRow.FindControl("rbtnFemale")).Text;//年龄TextBox txtAge = (TextBox)gridViewRow.FindControl("txtAge");string age = txtAge.Text.Trim();int studentAge = 0;if (!Int32.TryParse(age, out studentAge)) { lblMessage.Text = "please input currect student age!"; return; }Student item = new Student();item.studentName = studentName;item.studentSex = studentSex;item.studentAge = studentAge;if (AddStudent(item)){lblMessage.Text = "add student success!";BindStudent();}else{lblMessage.Text = "add student failure!";}}break;case"studentDelete"://删除{int studentID = 0;if (!Int32.TryParse(mandArgument.ToString(), out studentID)) { lblMessage.Text = "student's id is error"; }if (DeleteStudent(studentID)){lblMessage.Text = "delete student success!";BindStudent();}else{lblMessage.Text = "delete student failure!";}}break;case"studentEdit"://修改{GridViewRow gridViewRow = (GridViewRow)((LinkButton)mandSource).NamingContainer;int index = gridViewRow.RowIndex;gvwStudent.EditIndex = index;BindStudent();break;}case"studentUpdate"://更新{int studentID = 0;if (!Int32.TryParse(mandArgument.ToString(), out studentID)) { lblMessage.Text = "student's id is error"; }Student item = GetStudent(studentID);//获取选中⾏GridViewRow gridViewRow = (GridViewRow)((LinkButton)mandSource).NamingContainer;//姓名TextBox txtStudentName = (TextBox)gridViewRow.FindControl("txtStudentName");string studentName = txtStudentName.Text.Trim();if (studentName == "") { lblMessage.Text = "please input student name!"; return; }//性别RadioButton rbtnMale = (RadioButton)gridViewRow.FindControl("rbtnMale");string studentSex = rbtnMale.Checked ? rbtnMale.Text : ((RadioButton)gridViewRow.FindControl("rbtnFemale")).Text;//年龄TextBox txtAge = (TextBox)gridViewRow.FindControl("txtAge");string age = txtAge.Text.Trim();int studentAge = 0;if (!Int32.TryParse(age, out studentAge)) { lblMessage.Text = "please input currect student age!"; return; }item.studentName = studentName;item.studentSex = studentSex;item.studentAge = studentAge;if (UpdateStudent(item)){lblMessage.Text = "update student success!";gvwStudent.EditIndex = -1;BindStudent();}else{lblMessage.Text = "update student failure!";}}break;default:break;}}///<summary>///更新///</summary>///<param name="item"></param>///<returns></returns>private bool UpdateStudent(Student item){bool flag = false;try{scdc.SubmitChanges();flag = true;}catch (Exception ex){flag = false;}return flag;}///<summary>///获取学⽣///</summary>///<param name="studentID"></param>///<returns></returns>private Student GetStudent(int studentID){Student item = new Student();item = scdc.Student.SingleOrDefault(s => s.studentID == studentID);return item;}///<summary>///删除///</summary>///<param name="studentID"></param>///<returns></returns>private bool DeleteStudent(int studentID){bool flag = false;try{Student item = scdc.Student.SingleOrDefault(s => s.studentID == studentID);scdc.Student.DeleteOnSubmit(item);scdc.SubmitChanges();flag = true;}catch (Exception ex){flag = false;}return flag;}///<summary>///添加///</summary>///<param name="item"></param>///<returns></returns>private bool AddStudent(Student item){bool flag = false;try{scdc.Student.InsertOnSubmit(item); scdc.SubmitChanges();flag = true;}catch (Exception ex){flag = false;}return flag;}}}。

WPF+DataGrid+MySQL实现增删改查、Excel文件导出

WPF+DataGrid+MySQL实现增删改查、Excel⽂件导出1、前台⽂件代码<Window x:Class="MySql_Demo.MainWindow"xmlns="/winfx/2006/xaml/presentation"xmlns:x="/winfx/2006/xaml"xmlns:d="/expression/blend/2008"xmlns:mc="/markup-compatibility/2006"xmlns:local="clr-namespace:MySql_Demo"mc:Ignorable="d"Title="MainWindow" Height="450" Width="720"><Grid><DataGrid x:Name="MySqlDataGrid" HorizontalAlignment="Left" Height="400" Margin="10,10,0,10" SelectionMode="Extended" HorizontalScrollBarVisibility="Auto" VerticalAlignment="Top" Width="600" AutoGenerateColumns= <DataGrid.Columns><DataGridTextColumn Header="编号" Width="50" IsReadOnly="True" Binding="{Binding Path=id}"/><DataGridTextColumn Header="姓名" Width="100" Binding="{Binding Path=name}"/><DataGridTextColumn Header="年龄" Width="50" Binding="{Binding Path=age}"/><DataGridTextColumn Header="⾝⾼" Width="50" Binding="{Binding Path=high}"/><DataGridTextColumn Header="性别" Width="50" Binding="{Binding Path=gender}"/><DataGridTextColumn Header="出⽣⽇期" Width="*" Binding="{Binding Path=birthday, StringFormat='yyyy-MM-dd'}"/></DataGrid.Columns></DataGrid><Button x:Name="DeleteButton" Content="删除" Margin="0,10,10,0" VerticalAlignment="Top" Click="DeleteButton_Click" HorizontalAlignment="Right" Width="75"/><Button x:Name="UpdateButton" Content="修改" Margin="0,40,10,0" VerticalAlignment="Top" Click="ModifyButton_Click" HorizontalAlignment="Right" Width="75"/><Button x:Name="InsertButton" Content="插⼊" Margin="0,70,10,0" VerticalAlignment="Top" Click="InsertButton_Click" HorizontalAlignment="Right" Width="75"/><Button x:Name="ExportSelectButton" Content="复制所有项" Margin="0,0,10,55" Click="ExportCopytExcel_Click" HorizontalAlignment="Right" VerticalAlignment="Bottom" Width="75"/><Button x:Name="ExportAllButton" Content="导出所有项" Margin="0,0,10,25" Click="ExportAllExcel_Click" HorizontalAlignment="Right" VerticalAlignment="Bottom" Width="75"/></Grid></Window>2、后台⽂件代码　using System;　using System.Data;　using System.Drawing;　using System.IO;　using System.Linq;　using System.Text;　using System.Windows;　using System.Windows.Controls;　using System.Windows.Input;　using MySql.Data.MySqlClient;　using OfficeOpenXml;　using OfficeOpenXml.Style;namespace MySql_Demo{///<summary>/// Interaction logic for MainWindow.xaml///</summary>public partial class MainWindow : Window{string mysqlstr = "Data Source=127.0.0.1;User ID=root;Password=root;DataBase=grafaninfo;Charset=utf8;";MySqlConnection mysqlcon;MySqlDataAdapter mysqladapter;DataTable mysqldataTable;MySqlCommand mysqlcmd;public MainWindow(){InitializeComponent();UpdateMySqlData();}private void UpdateMySqlData(){mysqlcon = new MySqlConnection(mysqlstr);try{string upsql = "select * from students";mysqlcon.Open();mysqlcmd = new MySqlCommand(upsql, mysqlcon);mysqldataTable = new DataTable();mysqladapter = new MySqlDataAdapter(mysqlcmd);mysqladapter.Fill(mysqldataTable);MySqlDataGrid.ItemsSource = mysqldataTable.DefaultView;}catch(MySqlException ex){mysqldataTable.RejectChanges();MessageBox.Show(ex.Message);}finally{if(mysqlcon.State == ConnectionState.Open){mysqlcon.Close();}}}private void DeleteButton_Click(object sender, RoutedEventArgs e){int selectIndex = MySqlDataGrid.SelectedIndex;if (selectIndex == -1){return;}mysqlcon = new MySqlConnection(mysqlstr);try{mysqlcon.Open();mysqlcmd = mysqlcon.CreateCommand();mandText = "delete from students where id = @stuid";mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);mysqlcmd.ExecuteNonQuery();}catch (MySqlException ex){mysqldataTable.RejectChanges();MessageBox.Show(ex.Message);}finally{if (mysqlcon.State == ConnectionState.Open){mysqlcon.Close();UpdateMySqlData();}}}private void ModifyButton_Click(object sender, RoutedEventArgs e){int selectIndex = MySqlDataGrid.SelectedIndex;if (selectIndex == -1){return;}mitEdit();mysqlcon = new MySqlConnection(mysqlstr);try{mysqlcon.Open();mysqlcmd = mysqlcon.CreateCommand();mandText = "update students set name = @stuname, age = @stuage, high = @stuhigh, gender = @studgender, birthday = @stubirthday where id = @stuid"; mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);_ = mysqlcmd.Parameters.AddWithValue("@studgender", mysqldataTable.Rows[selectIndex]["gender"]);_ = mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);mysqlcmd.Parameters.AddWithValue("@stuid", mysqldataTable.Rows[selectIndex]["id"]);mysqlcmd.ExecuteNonQuery();}catch (MySqlException ex){mysqldataTable.RejectChanges();MessageBox.Show(ex.Message);}finally{if (mysqlcon.State == ConnectionState.Open){mysqlcon.Close();UpdateMySqlData();}}}private void InsertButton_Click(object sender, RoutedEventArgs e){int selectIndex = MySqlDataGrid.SelectedIndex;if (selectIndex == -1){return;}mitEdit();mysqlcon = new MySqlConnection(mysqlstr);try{mysqlcon.Open();mysqlcmd = mysqlcon.CreateCommand();mandText = "insert into students(id, name, age, high, gender, birthday) values(@stuid, @stuname, @stuage, @stuhigh, @stugender, @stubirthday)";mysqlcmd.Parameters.AddWithValue("@stuname", mysqldataTable.Rows[selectIndex]["name"]);mysqlcmd.Parameters.AddWithValue("@stuage", mysqldataTable.Rows[selectIndex]["age"]);mysqlcmd.Parameters.AddWithValue("@stuhigh", mysqldataTable.Rows[selectIndex]["high"]);mysqlcmd.Parameters.AddWithValue("@stugender", mysqldataTable.Rows[selectIndex]["gender"]);mysqlcmd.Parameters.AddWithValue("@stubirthday", mysqldataTable.Rows[selectIndex]["birthday"]);mysqlcmd.Parameters.AddWithValue("@stuid", null);mysqlcmd.ExecuteNonQuery();}catch (MySqlException ex){mysqldataTable.RejectChanges();MessageBox.Show(ex.Message);}finally{if (mysqlcon.State == ConnectionState.Open){mysqlcon.Close();UpdateMySqlData();}}}private void DataGrid_LoadingRow(object sender, DataGridRowEventArgs e){e.Row.Header = e.Row.GetIndex() + 1; //}private void ExportAllExcel_Click(object sender, RoutedEventArgs e){#region导出选择的⾏//DataRowView selectRow = MySqlDataGrid.SelectedItem as DataRowView;//string tempStr1 = "";// 写内容//for (int j = 0; j < MySqlDataGrid.Items.Count; j++)//{//if (j > 0)//{//tempStr1 += "\t";//}//tempStr1 += selectRow.Row[j].ToString();//}//sw.WriteLine(tempStr1);#endregionExport(MySqlDataGrid, "学⽣信息表");}private void ExportCopytExcel_Click(object sender, RoutedEventArgs e){string fileName = AppDomain.CurrentDomain.BaseDirectory + "学⽣信息表" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".csv";string strFormat = fileName;MySqlDataGrid.SelectAllCells();MySqlDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;ApplicationCommands.Copy.Execute(null, MySqlDataGrid);MySqlDataGrid.UnselectAllCells();string result = (string)Clipboard.GetData(maSeparatedValue);File.AppendAllText(strFormat, result, Encoding.UTF8);Clipboard.Clear();//清空粘贴板MessageBox.Show("导出成功");}public bool Export(DataGrid dataGrid, string excelTitle){DataTable dt = new DataTable();for (int i = 0; i < dataGrid.Columns.Count; i++){if (dataGrid.Columns[i].Visibility == Visibility.Visible)//只导出可见列{dt.Columns.Add(dataGrid.Columns[i].Header.ToString());//构建表头}}for (int i = 0; i < dataGrid.Items.Count; i++){int columnsIndex = 0;DataRow row = dt.NewRow();for (int j = 0; j < dataGrid.Columns.Count; j++){if (dataGrid.Columns[j].Visibility == Visibility.Visible){if (dataGrid.Items[i] != null && (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i]) as TextBlock) != null)//填充可见列数据{row[columnsIndex] = (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i]) as TextBlock).Text.ToString();}else row[columnsIndex] = "";columnsIndex++;}}dt.Rows.Add(row);}if (ExcelExport(dt, excelTitle) != null){return true;}else{return false;}}public string ExcelExport(DataTable DT, string title){try{//创建ExcelMicrosoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(Type.Missing);//创建⼯作表（即Excel⾥的⼦表sheet） 1表⽰在⼦表sheet1⾥进⾏数据导出Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];//如果数据中存在数字类型可以让它变⽂本格式显⽰ExcelSheet.Cells.NumberFormat = "@";//设置⼯作表名 = title;//设置Sheet标题string start = "A1";string end = ChangeASC(DT.Columns.Count) + "1";Microsoft.Office.Interop.Excel.Range _Range = ExcelSheet.get_Range(start, end);_Range.Merge(0); //单元格合并动作(要配合上⾯的get_Range()进⾏设计)_Range = ExcelSheet.get_Range(start, end);_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;_Range.Font.Size = 22; //设置字体⼤⼩_ = "宋体"; //设置字体的种类ExcelSheet.Cells[1, 1] = title; //Excel单元格赋值_Range.EntireColumn.AutoFit(); //⾃动调整列宽//写表头for (int m = 1; m <= DT.Columns.Count; m++){ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();start = "A2";end = ChangeASC(DT.Columns.Count) + "2";_Range = ExcelSheet.get_Range(start, end);_Range.Font.Size = 15; //设置字体⼤⼩_Range.Font.Bold = true;//加粗_ = "宋体"; //设置字体的种类_Range.EntireColumn.AutoFit(); //⾃动调整列宽_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;}//写数据for (int i = 0; i < DT.Rows.Count; i++){for (int j = 1; j <= DT.Columns.Count; j++){//Excel单元格第⼀个从索引1开始// if (j == 0) j = 1;ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();}}//表格属性设置for (int n = 0; n < DT.Rows.Count + 1; n++){start = "A" + (n + 3).ToString();end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();//获取Excel多个单元格区域_Range = ExcelSheet.get_Range(start, end);_Range.Font.Size = 12; //设置字体⼤⼩_ = "宋体"; //设置字体的种类_Range.EntireColumn.AutoFit(); //⾃动调整列宽_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其⽅式 _Range.EntireColumn.AutoFit(); //⾃动调整列宽 }ExcelApp.DisplayAlerts = false; //保存Excel的时候，不弹出是否保存的窗⼝直接进⾏保存//弹出保存对话框,并保存⽂件SaveFileDialog sfd = new SaveFileDialog();sfd.DefaultExt = ".xlsx";sfd.Filter = "导出Excel⽂件(*.xlsx)|*.xlsx";if (sfd.ShowDialog() == true){if (sfd.FileName != ""){ExcelBook.SaveAs(sfd.FileName); //将其进⾏保存到指定的路径//MessageBox.Show("导出⽂件已存储为: " + sfd.FileName, "温馨提⽰");}}//释放可能还没释放的进程ExcelBook.Close();ExcelApp.Quit();// PubHelper.Instance.KillAllExcel(ExcelApp);return sfd.FileName;}catch{//MessageBox.Show("导出⽂件保存失败！", "警告！");return null;}}///<summary>///获取当前列列名,并得到EXCEL中对应的列///</summary>///<param name="count"></param>///<returns></returns>private string ChangeASC(int count){string ascstr = "";switch (count){case1:ascstr = "A";break;case2:ascstr = "B";break;case3:ascstr = "C";break;case4:ascstr = "D";break;case5:ascstr = "E";break;case6:ascstr = "F";break;case7:ascstr = "G";break;case8:ascstr = "H";break;case9:ascstr = "I";break;case10:ascstr = "J";break;case11:ascstr = "K";break;case12:ascstr = "L";break;case13:ascstr = "M";break;case14:ascstr = "N";break;case15:ascstr = "O";break;case16:ascstr = "P";break;case17:ascstr = "Q";break;case18:ascstr = "R";break;case19:ascstr = "S";break;case20:ascstr = "Y";break;default:ascstr = "U";break;}return ascstr;}}}3、利⽤EEPLUS导出、导⼊EXCEL，只需求更改实现即可，头⽂件就不提供啦private void ExportAllExcel_Click(object sender, RoutedEventArgs e){#region导出选择的⾏//DataRowView selectRow = MySqlDataGrid.SelectedItem as DataRowView;//string tempStr1 = "";// 写内容//for (int j = 0; j < MySqlDataGrid.Items.Count; j++)//{//if (j > 0)//{//tempStr1 += "\t";//}//tempStr1 += selectRow.Row[j].ToString();//}//sw.WriteLine(tempStr1);#endregionExcelPackage.LicenseContext = LicenseContext.NonCommercial;using (var excle = new ExcelPackage()){//ExcelWorksheet sheet = excle.Workbook.Worksheets.Add();//向新建的Excel中添加⼀个sheetvar sheet = excle.Workbook.Worksheets.Add("学⽣信息表");//注：Excel中⾏的索引从1开始，DataTable的索引从0开始int rowIndex = 1; //起始⾏为第⼆⾏int columnIndex = 0;//起始列为第⼀列//绑定列头并设置样式foreach (DataColumn dc in mysqldataTable.Columns){columnIndex++;ExcelRange cell = sheet.Cells[rowIndex, columnIndex];cell.Value = dc.ColumnName;cell.Style.Font.Bold = true; //字体为粗体cell.Style.Font.Color.SetColor(Color.Red); //字体颜⾊ = "微软雅⿊"; //字体样式cell.Style.Font.Size = 14; //字体⼤⼩cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//⽔平居中cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中}//绑定数据for (int i = 0; i < mysqldataTable.Rows.Count; i++){for (int j = 0; j < mysqldataTable.Columns.Count; j++){sheet.Cells[i + 2, j + 1].Value = mysqldataTable.Rows[i][j].ToString();//从第⼆⾏开始绑定数据//修改性别显⽰⽅式//if (j == 2)//{//sheet.Cells[i + 2, j + 1].Value = int.Parse(mysqldataTable.Rows[i][j].ToString()) == 0 ? "男" : "⼥";//}//else//{//sheet.Cells[i + 2, j + 1].Value = mysqldataTable.Rows[i][j];//}}}excle.SaveAs(new FileInfo(@"D:\InstanceProject\VisualStudio\ProjectFile\ComeCapture-master\MySql_Demo\MyWorkbook.xlsx")); MessageBox.Show("导出成功");}}private void ExportCopytExcel_Click(object sender, RoutedEventArgs e){string fileName = AppDomain.CurrentDomain.BaseDirectory + "学⽣信息表" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".csv";string strFormat = fileName;MySqlDataGrid.SelectAllCells();MySqlDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;ApplicationCommands.Copy.Execute(null, MySqlDataGrid);MySqlDataGrid.UnselectAllCells();string result = (string)Clipboard.GetData(maSeparatedValue);File.AppendAllText(strFormat, result, Encoding.UTF8);Clipboard.Clear();//清空粘贴板MessageBox.Show("导出成功");}private void ImportExcel_Click(object sender, RoutedEventArgs e){ExcelPackage.LicenseContext = LicenseContext.NonCommercial;FileInfo file = new FileInfo(@"D:\InstanceProject\VisualStudio\ProjectFile\ComeCapture-master\MySql_Demo\学⽣信息表.xlsx");if ( file!=null){using (ExcelPackage excelPackage = new ExcelPackage(file)){int vSheetCount = excelPackage.Workbook.Worksheets.Count; //获取总Sheet页ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.First();mysqldataTable = new DataTable();int maxRowNum = worksheet.Dimension.End.Row; //获取worksheet的⾏数int maxColumnNum = worksheet.Dimension.End.Column; //获取worksheet的列数if (maxRowNum > 10){maxRowNum = 10;}DataTable vTable = new DataTable();DataColumn vC;for (int j = 1; j <= maxColumnNum; j++){vC = new DataColumn(worksheet.Cells[1,j].Value.ToString());vTable.Columns.Add(vC);}for (int n = 2; n <= maxRowNum; n++){DataRow vRow = vTable.NewRow();for (int m = 1; m <= maxColumnNum; m++){vRow[m - 1] = worksheet.Cells[n, m].Value;}vTable.Rows.Add(vRow);}MySqlDataGrid.ItemsSource = vTable.DefaultView;}}。

Gridview删除、更新语句设置

使用SqlDataSource插入、更新以及删除数据分类：DOTNET 2009-05-19 15:34 2126人阅读评论(1) 收藏举报在概述插入、更新和删除数据中我们讨论过，GridView控件提供了内建的更新与删除功能，而DetailsView和FormView控件除了这些之外还拥有插入功能。

这些数据修改功能可以直接接入到数据源控件中而不需要编写任何代码。

概述插入、更新和删除数据讲解了如何使用ObjectDataSource来帮助GridView、DetailsView以及FormView控件完成插入、更新以及删除操作。

ObjectDataSource能工作的地方，SqlDataSource也行。

回忆一下，要使ObjectDataSource支持插入、更新和删除功能，我们需要定义一些用以执行插入、更新和删除动作的对象层方法。

而在SqlDataSource中，我们则需要提供INSERT、UPDATE以及DELETE语句（或存储过程）。

正如我们将要在本节教程中看到的那样，这些语句可以手工创建，也可以通过SqlDataSource的“配置数据源”向导自动生成。

注意：由于我们已经讨论过了GridView、DetailsView以及FormView控件的插入、编辑和删除功能，本教程中我们将重点讨论如何配置SqlDataSource以使其支持这些操作。

如果你需要温习一下如何在GridView、DetailsView以及FormView中实现这个功能，请回到“编辑插入和删除数据”的章节，从概述插入、更新和删除数据开始。

第一步：指定INSERT、UPDATE以及DELETE语句就像我们在上两节教程中看到的那样，要从SqlDataSource控件中获取数据，我们需要设置两个属性：1. ConnectionString，它指定了查询应该发送到的那个数据库；2. SelectCommand，它指定了用于返回记录的SQL语句或存储过程。

Winform（DataGridView）控件及通过此控件中实现增删改查

Winform（DataGridView）控件及通过此控件中实现增删改查：显⽰数据表，通过此控件中可以实现连接数据库，实现数据的增删改查⼀、后台数据绑定：List<xxx> list = new List<xxx>();dataGridView1.DataSource = list;//设置不⾃动⽣成列，此属性在属性⾯板中没有dataGridView1.AutoGenerateColumns = false;//取消加载默认选中第⼀⾏dataGridView1.ClearSelection();⼆、前台：⼩三⾓箭头，取消可编辑，添加，删除功能；Columns集合属性中，添加列HeaderText中设置显⽰的⽂本DataPropertyName设置绑定的字段名或数据库列名SelectionMode --设置选择⽅式，FullRowSelect只能选中⾏MultiSelect --是否可以选中多⾏内容三、取值：取出选中的单元格的值：dataGridView1.SelectedCells中放着全部选中的单元格if(dataGridView1.SelectedCells.Count > 0){MessageBox.Show(dataGridView1.SelectedCells[0].Value.ToString());}取出选中的⾏内容：if(dataGridView1.SelectedRows.Count > 0){MessageBox.Show(dataGridView1.SelectedRows[0].Cells[0].ToString());}获取⽤于填充⾏绑定的对象：//⾏对象使⽤属性：DataBoundItemstudent sss = dataGridView1.SelectedRows[0].DataBoundItem as student;四、删除加确认MessageBoxButtons btn = MessageBoxButtons.YesNoCancel;if (MessageBox.Show("确定要删除么？", "删除数据", btn) == DialogResult.Yes){}五、多条件查询如果⽤户什么都不输⼊，或者⽂本框是空，这时候是查询所有//做两个恒成⽴的条件string tj1 = " 1=1 ";string tj2 = " 1=1 ";//根据⽤户输⼊来改变条件//如果⽤户输⼊了姓名if (name != ""){tj1 = " Name like @name ";}//如果⽤户输⼊了民族if (nation != ""){tj2 = " Nation = @nation ";}//拼接成完整条件string ztj = " where "+tj1+" and "+tj2;。

GridView、DetailsView 显示控件增删改

使用GridView、DetailsView 服务器控件在网页上编辑和插入数据通过使用封装数据访问的数据源控件与以可编辑格式显示记录的DetailsView 和GridView 控件的组合，可以创建一个允许用户编辑现有记录或插入新记录的数据输入页，所有操作均无需代码。

1、创建网站和网页创建解决方案文件夹和一个名为Default.aspx 的新页。

2、配置SqlDataSource连接到SQL Server拖一SqlDataSource控件至页面中，然后，并单击“显示智能标记”。

出现“数据源配置向导”对话框。

在“选择数据源类型”下单击“数据库”。

保留默认名称“SqlDataSource1”，然后单击“确定”。

“配置数据源”向导显示“选择连接”页。

在“应用程序连接数据库应使用哪个数据连接？”框中输入在“创建与SQL Server 的连接”中创建的连接，然后单击“下一步”。

该向导显示一页，从该页中您可以选择将连接字符串存储到配置文件中。

将连接字符串存储在配置文件中有两个优点：(1)比将它存储在页面中更安全。

(2)可以在多个页面中使用相同的连接字符串。

选择“是，将此连接另存为”复选框，然后单击“下一步”。

该向导显示一页，从该页中您可以指定要从数据库中检索的数据。

在“配置Select 语句”页上选择“指定来自表或视图的列”，然后在“名称”框中单击“雇员”。

在“列”下，选中“EmployeeID”、“Lastname”、“Firstname”和“HireDate”复选框，然后在“配置Select 语句”页上单击“高级”，选中“生成INSERT、UPDATE 和DELETE 语句”复选框，然后单击“确定”。

注意可以通过选择“指定自定义SQL 语句或存储过程”并输入SQL 查询来手动创建语句。

您可以选择SqlDataSource 控件并查看DeleteQuery、InsertQuery 和UpdateQuery 属性，以便检查由向导生成的语句。

C#DataGridView绑定List对象时，利用BindingList来实现增删查改

C#DataGridView绑定List对象时，利⽤BindingList来实现增删查改当DataGridView的DataSource是DataTable的时候，DataTable的数据改变时，DataGridView的数据会随之改变，⽆需重新绑定到DataGridView。

当DataGridView的DataSource是泛型List，当List的数据改变时，则需要先将DataGridView的DataSource设置为new List<T>()，再将改变后的List<T>赋给DataGridView的DataSource。

绑定List时，注意：切莫将DataGridView的DataSource设置为Null，否则会破坏DataGridView的列结构。

如果要对绑定在DataGridView中的List<T>进⾏数据的添加删除，先要把List<T>转换成BindingList<T>，再进⾏绑定：DataGridView.DataSource=new BindingList<T>(new List<T>)。

否则的话会产⽣许多意想不到的错误。

如：初始绑定空数据后再添加数据绑定后，却取不到DataGridView.CurrentCell属性。

IList<T> list= new List<T>();DataGridView.DataSource=list;//DataGridView的⾏不能添加删除DataGridView.DataSource=new BindingList<T>(list);//DataGridView的⾏可以添加删除（只有允许添加⾏、删除⾏）⽰例代码：public partial class ucServer : UserControl{private List<ServerInfo> serverList;private BindingList<ServerInfo> dataBindings;public ucServer(List<ServerInfo> serverList){InitializeComponent();if (serverList == null)serverList = new List<ServerInfo>();this.serverList = serverList;dataBindings = new BindingList<ServerInfo>(this.serverList);}private void ucChecker_Load(object sender, EventArgs e){this.dgParams.DataSource = dataBindings;}private void llDownloadUrl_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e){MessageBox.Show("请设置下载地址。

关于C# -WINFORM-DataGridView的更新、删除

关于C# -WINFORM-DataGridView的更新、删除一、DataGridView绑定数据库之后直接对其进行操作：1.在按钮添加更新操作代码（只需一行）：this.tbTableAdapter.Update(this.abcDataSet2.tb);其中abc为数据库名，tb为表名2.在按钮添加删除操作代码：DialogResult dlResult = MessageBox.Show(this, "要删除这些记录吗？", "请确认",MessageBoxButtons.YesNo,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1,MessageBoxOptions.RightAlign);if (dlResult == DialogResult.Yes){int j = dataGridView1.SelectedRows.Count;int[] l = new int[j];int i;for (i = 0; i < j; i++){l[i] = dataGridView1.SelectedRows[i].Index;}int k = 0;while (k < j){this.abcDataSet2.tb.Rows[l[k]].Delete();k++;}二、DataGridView绑定数据库，把查询结果进行更新、删除操作后保存到数据库：1.在按钮添加更新操作代码：if (MessageBox.Show("确实要修改数据库吗？", "决策提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) ==DialogResult.OK){DataTable dt = dataGridView1.DataSource as DataTable; if (dt != null){try{using (SqlConnection con = newSqlConnection("data source=ksig;initial catalog=AMDMS;userid=sa;pwd=123;")){SqlDataAdapter da = newSqlDataAdapter("select * from account", con);SqlCommandBuilder scb = new SqlCommandBuilder(da);DataSet ds = new DataSet();da.Fill(ds, "account");da.Update(dt);}}catch (DataException de){//}}MessageBox.Show("成功修改数据库！", "恭喜");}else{MessageBox.Show("放弃修改数据库！", "系统提示");}2.在按钮添加删除操作代码：//在DataGridView1界面上进行可视化删除dataGridView1.Rows.Remove(dataGridView1.Rows[dataGridView 1.CurrentCell.RowIndex]);//把删除后的DataGridView1的结果更新到数据库中DataTable dt = dataGridView1.DataSource as DataTable;if (dt != null){try{using (SqlConnection con = new SqlConnection("datasource=ksig;initial catalog=AMDMS;user id=sa;pwd=123;")){SqlDataAdapter da = new SqlDataAdapter("select * from account", con);SqlCommandBuilder scb = new SqlCommandBuilder(da);DataSet ds = new DataSet();da.Fill(ds, "account");da.Update(dt);}}catch (DataException de){//}}。

GridView增删查改

GridView增删查改<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/1999/xhtml" ><head runat="server"><title>GridView_Demo</title></head><body><form id="form1" runat="server"><div><asp:GridView ID="myGrid" runat="server" ></asp:GridView></div></form></body></html>====================================using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;//myselfusing System.Drawing;public partial class _Default : System.Web.UI.Page{private Employees.Employees_BLL bll = new Employees.Employees_BLL();protected void Page_Load(object sender, EventArgs e){if (!IsPostBack)//设置GridView外观样式setGridViewStyle();//创建及设置Fields字段setFields();//设置GridView 数据源绑定GridBind();}//以后台的方式添加GridView 的各类事件myGrid.RowEditing += new GridViewEditEventHandler(myGrid_RowEditing);myGrid.RowUpdating += new GridViewUpdateEventHandler(myGrid_RowUpdating);myGrid.RowCancelingEdit += new GridViewCancelEditEventHandler(myGrid_RowCancelingEdit);myGrid.RowDeleting += new GridViewDeleteEventHandler(myGrid_RowDeleting);}方法#region 方法//设置GridView外观样式private void setGridViewStyle(){myGrid.AutoGenerateColumns = false;//设置Row的键值组成，具有唯一性string[] KeyNames = new string[] { "EmployeeID" };myGrid.DataKeyNames = KeyNames;//设置GridView属性myGrid.AllowPaging = true; //设置分页myGrid.AllowSorting = true; //设置排序myGrid.Font.Size = 10; //设置字号大小myGrid.GridLines = GridLines.Both; //设置网格线myGrid.PageSize = 15; //分页大小myGrid.PagerSettings.Position = PagerPosition.TopAndBottom; //分页位置myGrid.PagerStyle.HorizontalAlign = HorizontalAlign.Center; //分页对齐myGrid.HeaderStyle.BackColor = Color.Tan;myGrid.RowStyle.BackColor = Color.LightGoldenrodYellow;myGrid.AlternatingRowStyle.BackColor = Color.PaleGoldenrod;myGrid.HeaderStyle.ForeColor = Color.Black;myGrid.PagerStyle.BackColor = Color.Goldenrod;myGrid.SelectedRowStyle.BackColor = Color.LightBlue; //设置选择行背景颜色//myGrid.ShowFooter = true;//创建及设置Fields字段private void setFields(){//创建"编辑"命令字段CommandField editField = new CommandField();editField.ButtonType = ButtonType.Button;editField.ShowEditButton = true; //显示"编辑"按钮editField.ShowCancelButton = true; //显示"取消"按钮editField.EditText = "编辑";editField.UpdateText = "更新";editField.CancelText = "取消";editField.ControlStyle.BackColor = Color.LightPink; editField.ItemStyle.Wrap = false;//创建"删除"命令字段CommandField deleteField = new CommandField(); deleteField.ButtonType = ButtonType.Button;deleteField.ShowDeleteButton = true; //显示"删除"按钮deleteField.DeleteText = "删除";deleteField.ControlStyle.BackColor = Color.LightPink; deleteField.ItemStyle.Wrap = false;//创建数据绑定字段BoundField employeeidField = new BoundField(); BoundField lastnameField = new BoundField();BoundField firstnameField = new BoundField();BoundField titleField = new BoundField();BoundField addressField = new BoundField();BoundField cityField = new BoundField();employeeidField.DataField = "EmployeeID";//指定数据源字段employeeidField.HeaderText = "员工代号"; //设置字段头名称employeeidField.ItemStyle.Wrap = false; //设置字段不换行employeeidField.ReadOnly = true; //只读,编辑模式不能修改lastnameField.DataField = "LastName";lastnameField.HeaderText = "名字";lastnameField.ItemStyle.Wrap = false;lastnameField.ReadOnly = true; //只读,编辑模式不能修改firstnameField.DataField = "FirstName"; firstnameField.HeaderText = "姓氏";firstnameField.ItemStyle.Wrap = false;//firstnameField.ReadOnly = true;titleField.DataField = "Title";titleField.HeaderText = "职称";titleField.ItemStyle.Wrap = false;addressField.DataField = "Address";addressField.HeaderText = "地址";addressField.ItemStyle.Wrap = false;cityField.DataField = "City";cityField.HeaderText = "城市";cityField.ItemStyle.Wrap = false;//将字段添加到GridViewmyGrid.Columns.Add(editField); //编辑myGrid.Columns.Add(deleteField);//删除myGrid.Columns.Add(employeeidField);myGrid.Columns.Add(lastnameField);myGrid.Columns.Add(firstnameField);myGrid.Columns.Add(titleField);myGrid.Columns.Add(addressField);myGrid.Columns.Add(cityField);}//设置GridView 数据源绑定public void GridBind(){//bll = new Employees.Employees_BLL();myGrid.DataSource = bll.GetAllList();myGrid.DataBind();}#endregion 方法// GridView 编辑操作protected void myGrid_RowEditing(object sender, GridViewEditEventArgs e) {//设置编辑行的索引myGrid.EditIndex = e.NewEditIndex;//设置更新与取消按钮之背景颜色myGrid.Columns[0].ControlStyle.BackColor = Color.LightSteelBlue;myGrid.Columns[1].ControlStyle.BackColor = Color.LightSteelBlue;myGrid.ShowFooter = true;//设置GridView在编辑模式时，TextBox字段宽度及背景颜色//EmployeeID字段myGrid.Columns[2].ControlStyle.Width = 80;myGrid.Columns[2].ControlStyle.BackColor = Color.LightBlue;myGrid.Columns[2].FooterText = "不可编辑";myGrid.Columns[2].FooterStyle.BackColor = Color.Red;//LastName字段myGrid.Columns[3].ControlStyle.Width = 80;myGrid.Columns[3].ControlStyle.BackColor = Color.LightBlue;myGrid.Columns[3].FooterText = "不可编辑";myGrid.Columns[3].FooterStyle.BackColor = Color.Red;//FirstName字段myGrid.Columns[4].ControlStyle.Width = 80;myGrid.Columns[4].ControlStyle.BackColor = Color.LightBlue;myGrid.Columns[4].FooterText = "可编辑";myGrid.Columns[4].FooterStyle.BackColor = Color.Red;//Title字段myGrid.Columns[5].ControlStyle.Width = 100;myGrid.Columns[5].ControlStyle.BackColor = Color.LightPink;myGrid.Columns[5].FooterText = "可编辑";myGrid.Columns[5].FooterStyle.BackColor = Color.Red;//Address字段myGrid.Columns[6].ControlStyle.Width = 120;myGrid.Columns[6].ControlStyle.BackColor = Color.LightPink;myGrid.Columns[6].FooterText = "可编辑";myGrid.Columns[6].FooterStyle.BackColor = Color.Red;//City字段myGrid.Columns[7].ControlStyle.Width = 80;myGrid.Columns[7].ControlStyle.BackColor = Color.LightGreen;myGrid.Columns[7].FooterText = "可编辑";myGrid.Columns[7].FooterStyle.BackColor = Color.Red;GridBind();}// GridView 更新操作protected void myGrid_RowUpdating(object sender, GridViewUpdateEventArgs e){if (e.NewValues != e.OldValues){Employees.Employees_Model model = new Employees.Employees_Model();model.EmployeeID = Convert.ToInt32(myGrid.Rows[e.RowIndex].Cells[2].Text.Trim());//Employees_DAL 中的where 条件为EmployeeID and LastName, 所以LastName不能更改stName = myGrid.Rows[e.RowIndex].Cells[3].Text.ToString();model.FirstName = ((TextBox)myGrid.Rows[e.RowIndex].Cells[4].Controls[0]).Text.ToString();model.Title = ((TextBox)myGrid.Rows[e.RowIndex].Cells[5].Controls[0]).Text.ToString();model.Address = ((TextBox)myGrid.Rows[e.RowIndex].Cells[6].Controls[0]).Text.ToString();model.City = ((TextBox)myGrid.Rows[e.RowIndex].Cells[7].Controls[0]).Text.ToString();//Employees.BLL.Employees_Model bll = new Employees.BLL.Employees_Model();bll.Update(model);//取消编辑时隐藏FootermyGrid.ShowFooter = false;//设置"编辑"和"删除"按钮还原为系统定义的颜色myGrid.Columns[0].ControlStyle.BackColor = Color.LightPink;myGrid.Columns[1].ControlStyle.BackColor = Color.LightPink;myGrid.EditIndex = -1;GridBind();}}// GridView 取消事件protected void myGrid_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {//取消编辑时隐藏FootermyGrid.ShowFooter = false;//设置"编辑"和"删除"按钮还原为系统定义的颜色myGrid.Columns[0].ControlStyle.BackColor = Color.LightPink;myGrid.Columns[1].ControlStyle.BackColor = Color.LightPink;myGrid.EditIndex = -1; //取消编辑状态GridBind();}// GridView 删除操作protected void myGrid_RowDeleting(object sender, GridViewDeleteEventArgs e){//设置更新与取消按钮之背景颜色myGrid.Columns[0].ControlStyle.BackColor = Color.LightSteelBlue;myGrid.Columns[1].ControlStyle.BackColor = Color.LightSteelBlue;// 获取Employees_DAL 中的where 条件: EmployeeID and LastNameint strEmployeeID = Convert.ToInt32(myGrid.DataKeys[e.RowIndex].Values[0]);string strLastName = myGrid.Rows[e.RowIndex].Cells[3].Text.ToString();bll.Delete(strEmployeeID, strLastName); //删除GridBind();}。

.NET三层架构与三层架构下GridView控件增删改操作详解

.NET三层架构与三层架构下GridView控件增删改操作详解(一)所谓三层架构（3-tier application）就是将整个业务应用划分为：表现层（UI）、业务逻辑层（BLL）、数据访问层（DAL）。

区分层次的目的即为了“高内聚、低耦合”的思想。

1、表现层（UI）：主要是指与用户交互的界面，用于显示数据和接受用户输入的数据，将用户输入的数据传递给业务逻辑层，一般不包含任何实际的业务处理，当业务逻辑层的数据发生变化时，表示层就会显示出更新的结果。

表示层提供应用程序的用户界面，通常为Windows 应用程序或Web应用程序。

2、业务逻辑层（BLL）：是表示层和数据访问层之间的桥梁，它代表应用程序的核心功能，负责处理数据层的数据，实现业务逻辑。

业务逻辑层通常为类库。

3、数据访问层（DAL）：主要实现对数据的保存和读取操作，将存储在数据库中的数据提交给业务层，同时将业务层处理的数据保存到数据库中。

数据访问层可以访问关系数据库、文本文件或者XML文档，通常为类库。

三层架构对应的图如下图所示:为了更好地让初学者轻松入门，这里仍然采用趣味性的方式聊一些常用技术点，致力于.NET 新手们的快速提高!知识都是普通的，关键是学习的思路。

技术源于生活，技术原来可以这样学。

抛砖引玉而已。

层次结构在现实社会里随处可见。

记得有个笑话讲有个村长得意地向他老婆吹牛：“全中国只有四个人比我官大，乡长、县长、省长和国务院总理”。

这个笑话也体现了真实社会中分层的现象。

社会人群会分层，公司人员结构也会分层，楼房是分层的，甚至做包子的笼屉都是分层的。

虽然分层的目的各有不同，但都是为解决某一问题而产生的。

所以，分层架构其实是为了解决某一问题而产生的一种解决方案。

1、常用的三层架构设计软件系统最常用的一般会讲到三层架构，其实就是将整个业务应用划分为表示层、业务逻辑层、数据访问层等，有的还要细一些，通过分解业务细节，将不同的功能代码分散开来，更利于系统的设计和开发，同时为可能的变更提供了更小的单元，十分有利于系统的维护和扩展。

【精品】datagridview的增删改查

【关键字】精品using System;using ;using ponentModel;using System.Data;using System.Drawing;using System.Text;using ;using ;namespace dataGridViewUse{public partial class Form1 : Form{private OleDbConnection con = null;private OleDbCommand com = null;private OleDbDataAdapter adapter = null;private OleDbDataReader dr = null;private string s;private string sqlstring = "Provider=;Data Source=E:/My Documents/Visual Studio 2005/Projects/dataGridViewUse/dataGridViewUse/表.mdb";private dataGrid1;private DataSet ds = null;public Form1(){InitializeComponent();}private void Form1_Load(object sender, EventArgs e){dataGridView1.Refresh();con = new OleDbConnection(sqlstring);getData();//水晶报表DataSet1 d = new DataSet1();;CrystalReport1 c = new CrystalReport1();c.SetDataSource(d);crystalReportViewer1.ReportSource = c;}//得到表Area里所有的数据private void getData(){trycon.Open();adapter = new OleDbDataAdapter("select * from Area", con);ds = new DataSet();adapter.Fill(ds, "Area");dataGridView1.DataSource = ds.Tables["Area"];}catch (Exception ex){MessageBox.Show(ex.Message);}finally{con.Close();}}private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e){}//增加的方法public void methodAdd(){// TODO: 这行代码将数据加载到表“表DataSet.Area”中。

GridView1_删除、修改(字段类型全而且用GridView集成处理)

详细学习内容：TemplatedField模板列技术、使用绑定列技术、鼠标所在行颜色提示、绑定列中使用下拉列表框、设置字段的ControlStyle属性运行态1. 在VS2008中建立Web项目。

2. 在Windows环境下将数据库jyxystu.mdf(命名含义为教育学院学生)和jyxystu.ldf拷贝到项目的App_Data目录下,并在VS中添加的项目中。

3. 在SQL Server 2000中附加数据库jyxystu.mdf。

4. 在数据库中建立一个表:tb_zg（命名含义为职工表，以tb开头意思是本对象是一个表），表结构如下：5. 录入若干条记录(为了看出效果，记录数至少20条)6. 修改web.config文件，(1) 删除</configSections>下方的<appSettings/>(2) 在</configSections>位置下增加：<appSettings><add key="ConnStr" value="Server=localhost;uid=sa;pwd=;database=jyxystu"></add></appSettings>7. 在项目中添加Web窗体mygridview.aspx（文件名含义为按类查询）8. 布局控件如下：一个GridView，在GridView下方有两个label，准备显示第x页共y页。

ID属性分别是Label1和Label2。

GridView1自动套用格式为"雪松"。

AutoGenerateColumns="False"AllowSorting="True"AllowPaging="True"PageSize="10"因为要采用分页技术所以需要再设置PagerSettings属性FirstPageText="首页"LastPageText="尾页"NextPageText="下一页"PreviousPageText="上一页"Mode="NextPreviousFirstLast"把GridView中的FontSize设为small。

C#对DataGridView进行添加、修改、删除数据操作

C#对DataGridView进⾏添加、修改、删除数据操作C#对DataGridView进⾏添加、修改、删除数据操作数据库⽤的是本地服务器（MySql）：设定全局变量：MySqlConnection conn;MySqlDataAdapter adapter;MySqlTransaction trans;1. // 数据库联接private System.Data.DataTable dbconn(string strSql){string strconn = "host=localhost;database=test;user id=root;password=";conn = new MySqlConnection();conn.ConnectionString = strconn;conn.Open();this.adapter = new MySqlDataAdapter(strSql, conn);System.Data.DataTable dtSelect = new System.Data.DataTable();int rnt=this.adapter.Fill(dtSelect);conn.Close();return dtSelect;}2. //设定DataGridView的样式private void setDgStyle(){this.dgselect.Columns.Clear();DataGridViewCheckBoxColumn colDel = new DataGridViewCheckBoxColumn();colDel.DataPropertyName = "Del"; = "Del";colDel.Selected = false;colDel.FalseValue = "0";colDel.TrueValue = "1";colDel.Width = 40;colDel.SortMode = DataGridViewColumnSortMode.NotSortable;colDel.HeaderText = "删除";colDel.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colDel.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;this.dgselect.Columns.Insert(0, colDel);DataGridViewTextBoxColumn colID = new DataGridViewTextBoxColumn();colID.DataPropertyName = "ProductsSpecID"; = "ProductsSpecID";colID.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colID.HeaderText = "产品规格ＩＤ";colID.Width = 160;this.dgselect.Columns.Insert(1, colID);DataGridViewTextBoxColumn colNM = new DataGridViewTextBoxColumn();colNM.DataPropertyName = "ProductsSpec"; = "ProductsSpec";colNM.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colNM.HeaderText = "产品规格名称";colNM.Width = 160;this.dgselect.Columns.Insert(2, colNM);DataGridViewTextBoxColumn colUnit = new DataGridViewTextBoxColumn();colUnit.DataPropertyName = "ProductsSpecUnit"; = "ProductsSpecUnit";colUnit.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colUnit.HeaderText = "产品规格单位";this.dgselect.Columns.Insert(3, colUnit);DataGridViewTextBoxColumn colPID = new DataGridViewTextBoxColumn();colPID.DataPropertyName = "ProductsID"; = "ProductsID";colPID.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colPID.HeaderText = "产品ID";colPID.Width = 140;this.dgselect.Columns.Insert(4, colPID);DataGridViewButtonColumn colButton = new DataGridViewButtonColumn();colButton.DataPropertyName = "colSearch"; = "colSearch";colButton.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;colButton.HeaderText = "Button";colButton.Width = 80;this.dgselect.Columns.Insert(5, colButton);this.dgselect.RowHeadersWidth = 15;this.dgselect.ColumnHeadersDefaultCellStyle.Font=new System.Drawing.Font("宋体",14);}3. //修改数据,并将数据提交到数据库private Boolean dbUpdate(){string strSql = "select ProductsSpecID,ProductsSpec,ProductsSpecUnit,ProductsID from tbl_product_detail_master"; System.Data.DataTable dtUpdate = new System.Data.DataTable();dtUpdate = this.dbconn(strSql);dtUpdate.Rows.Clear();System.Data.DataTable dtShow = new System.Data.DataTable();//dtShow = (DataTable)this.bindSource.DataSource;dtShow = (System.Data.DataTable)this.dgselect.DataSource;int p1 = dtShow.Rows.Count;// try// {for (int i = 0; i < dtShow.Rows.Count; i++){DataRowState rowState=new DataRowState();rowState=dtShow.Rows[i].RowState;if (rowState==DataRowState.Added || rowState==DataRowState.Detached || rowState==DataRowState.Modified){if (this.dgselect["Del", i].Value.ToString() == "1"){dtShow.Rows[i].Delete();}}}for (int i = 0; i < dtShow.Rows.Count; i++){dtUpdate.ImportRow(dtShow.Rows[i]);}int num = dtUpdate.Rows.Count;try{this.conn.Open();trans = this.conn.BeginTransaction();MySqlCommandBuilder CommandBuiler;CommandBuiler = new MySqlCommandBuilder(this.adapter);this.adapter.Update(dtUpdate);this.conn.Close();}catch ( Exception ex){MessageBox.Show(ex.Message.ToString()); trans.Rollback();return false;}dtUpdate.AcceptChanges();return true;}。

DataGridView的增删改及复制粘贴

DataGridView中直接增删改查的方法—追加了复制粘贴功能在或C#中对于直接增删改DataGridView中的记录，用户更易接受，虽然DataGridView本身提供Update的方法（该仅需有Select语句即可）可将添加或删除或更新的的记录同时更新至底层数据库，但该方法模拟Excel的操作，如用户未点击保存按钮，用户此前所做的添加修改或更新的操作无法反映到底层数据库，若此时有别的用户也在访问该表格，系统所显示的还是旧的数据而不是最新的；另外系统所提供的Update方法仅适用于单表查询结果的操作，而对于关联表查询的操作则无能为力。

下面的提供的方法所有的操作必须要手动完成，select,update,delete,insert的语句全部有编程人员手动加入。

在DataGridView中可通过其本身直接增加记录，修改记录，以及删除记录。

为了保证修改及删除的功能正常运行，数据库在设计表时必须要添加自动编号ID。

添加记录：一般添加时，用Insert方法可模拟向数据库添加空白记录，以获取数据库的自动编号，随后利用修改功能输入其他字段的数据。

修改记录：修改记录时利用DataGridiView的CellEndEdit事件直接修改，每修改完一个单元格即触发该事件，该事件再调用Update方法更新到数据库中的记录。

避免数据库表中添加了新的字段而必须修改Update 的sql语句。

删除记录：有了ID后，删除记录也变得更简单，只需将所有欲删除的记录选中，最后由数据库的Delete方法删除选择的记录。

以上方法每一次动作均直接与数据库关联，保证数据库中的数据都是最新的，一般对于局域网中的操作比较合适。

直接上项目：将DataGridView控件拉入窗体中，打开其属性，修改AllowUserToAddRows 与AllowUserToDeleteRows 均为False，如下图。

.同时必须将下面的删除和添加按钮删除，加入自己的删除按钮和添加按钮。