用DataAdapter对数据库增加数据适用于成批量的更改数据,用户在dataset中对数据进行更改后,通过调用dataadapter
对象的update方法批量的把数据提交给数据库,用command对数据库增加数据适合一次只提交一条数据,当用户需要操作数据,通过调用command对
象的executenonquery方法来完成单个数据的操作。
用DataAdapter对数据库增加数据
下面是把课程信息添加到course中,开始吧课程记录逐个添加到数据集的表中,等所有课程添加完毕后,在利用dataadapter对象成批提交到后台数据库中
html代码<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="用DataAdapter对数据库增加数据._Default" %>
<!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>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: left">
课程号: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
课程名称:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="添加" onclick="Button1_Click"
style="text-align: center" />
<br />
<br />
<asp:Panel ID="Panel1" runat="server" Height="185px" Width="286px">
<asp:Label ID="Label1" runat="server" Width="167px"></asp:Label>
<asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="保存到数据库"
Width="93px" />
<br />
<asp:DataGrid ID="DataGrid1" runat="server" Width="272px">
</asp:DataGrid>
</asp:Panel>
</div>
</form>
</body>
</html>
c#代码:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
using System.Data.SqlClient;
namespace 用DataAdapter对数据库增加数据
{
public partial class _Default : System.Web.UI.Page
{
static DataSet ds;//声明静态数据集ds
static SqlDataAdapter da;//声明静态适配器da对象
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string connctionstring = "Data Source=神舟龙-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True;";//定义连接字符串,用windows登陆
SqlConnection conn = new SqlConnection(connctionstring);//创建连接对象
da = new SqlDataAdapter("select * from course",conn);//创建一个da数据适配器
da.InsertCommand = new SqlCommand("insert into course values(@id,@name)",conn);//向course表中id,和name添加
da.InsertCommand.Parameters.Add("@id",SqlDbType.Char,5,"id");//定义id列的数据类型及字符数
da.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar, 20, "name");//定义name列的数据类型及字符数
conn.Open();//打开数据连接对象
ds = new DataSet();//定义一个空的数据集
da.Fill(ds,"course");//把id列和name列添加到数据集中的course表中,映射
conn.Close();//关闭连接对象
this.DataGrid1.DataSource = ds.Tables["course"];//把DataGrid1控件的数据源与数据集中的course表绑定。
this.DataGrid1.DataBind();//绑定
}
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
}
private void InitializeComponent()
{
this.Button1.Click+=new EventHandler(this.Button1_Click);
this.Button2.Click += new EventHandler(this.Button2_Click);
this.Load+=new EventHandler(this.Page_Load);
}
protected void Button1_Click(object sender, EventArgs e)
{
DataRow dr=ds.Tables["course"].NewRow();//创建一个新的行
dr[0] = this.TextBox1.Text;//把用户输入到TextBox1控件中的字符添加到第一列
dr[1] = this.TextBox2.Text;//把用户输入到TextBox2控件中的字符添加到第二列
ds.Tables[0].Rows.Add(dr);//通过add方法添加到新的行中
}
protected void Button2_Click(object sender, EventArgs e)
{
int i = da.Update(ds.Tables["course"]);//通过insertCommand属性对应的insert预计将添加的行数提交到数据库
Label1.Text = "受影响的数据有" + i.ToString() + "条";//用label现实受影响的行数
string connectionstring = "Data Source=神舟龙-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True";//定义连接字符串,用windows登陆
SqlConnection conn = new SqlConnection(connectionstring);//创建连接对象
SqlDataAdapter da1 = new SqlDataAdapter("select * from course", conn);//创建一个da数据适配器
conn.Open();//打开连接对象
DataSet ds1=new DataSet();//创建数据集ds1
da.Fill(ds1,"course");//填充到ds1的course表
conn.Close();//关闭连接对象
this.DataGrid1.DataSource=ds1.Tables["course"];//把数据集中的course表映射到datagrid数据源中
this.DataGrid1.DataBind();//进行绑定
}
}
}
用command对数据库增加数据
html代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="用command增加数据._Default" %><!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>用command增加数据</title>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: center">
课程号:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
课程名:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="添加" onclick="Button1_Click" />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Width="149px"></asp:Label>
<br />
<asp:DataGrid runat="server" ID="DataGrid" Width="216px"></asp:DataGrid>
</div>
</form>
</body>
</html><%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="用command增加数据._Default" %>
<!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>用command增加数据</title>
</head>
<body>
<form id="form1" runat="server">
<div style="text-align: center">
课程号:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
课程名:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="添加" onclick="Button1_Click" />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Width="149px"></asp:Label>
<br />
<asp:DataGrid runat="server" ID="DataGrid" Width="216px"></asp:DataGrid>
</div>
</form>
</body>
</html>
c#代码:using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
using System.Data.SqlClient;
namespace 用command增加数据
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
databindeng();//自定义的方法
}
}
protected override void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Button1.Click+=new EventHandler(this.Button1_Click);
this.Load += new EventHandler(this.Page_Load);
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionstring = "Data Source=神舟龙-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True;";
SqlConnection conn = new SqlConnection(connectionstring);
string insert_sql = "insert into course values('"+TextBox1.Text+"','"+TextBox2.Text+"')";
SqlCommand comm = new SqlCommand(insert_sql,conn);
conn.Open();
int i = comm.ExecuteNonQuery();//通过comm对象的excutenoquery方法,执行SQL语句,并返回受影响的行数
Label1.Text = "受影响的数据有" + i.ToString() + "条";
databindeng();//在触发Button控件时,同时把datagrid表显示出来
}
private void databindeng()
{//自定义方法的功能
string connctionstring = "Data Source=神舟龙-PC\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True;";//windows登陆
SqlConnection conn = new SqlConnection(connctionstring);
SqlDataAdapter da = new SqlDataAdapter("select * from course",conn);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds,"course");
conn.Close();
this.DataGrid .DataSource=ds.Tables["course"];
this.DataGrid.DataBind();
}
}
}