怎么向数据库插入记录?下面归纳了几种方法。
3.4.1.1通过SQLCommand
插入一条记录到数据库的SQL语句格式为:
Insert Into 表名 (字段1,字段2,。。。) Values (值1,值2,。。。)
我们知道,SQLCommand 可以执行SQL命令,我们把插入记录的SQL语句传递到SQLCommand的CommandText属性,然后执行其ExecuteNonQuery方法,就可以了。
Dim strConn As String
Dim strComm As String
Dim oConn As SQLConnection
Dim oComm As SQLCommand
strConn = "server=localhost;uid=sa;pwd=;database=northwind"
strComm = "INSERT INTO CUSTOMERS (CustomerId, CompanyName, Contactname, ContactTitle, Address) Values ('DarkMan','Sino-ASP.COM', 'Mr. Li', 'CTO','不要找我')"
oConn = new SQLConnection(strConn)
oComm = new SQLCommand(strComm, oConn)
Try
oConn.Open()
‘执行命令
oComm.ExecuteNonQuery()
Catch myException as Exception
//出错处理
Finally
oConn.Close()
End Try
3.4.1.2通过SQLDataSetCommand
如果需要批量插入记录,可以使用SQLDataSetCommand。下面是一个示例:
Dim strConn,strComm As String
//数据库连接字符串
strConn="server=localhost;uid=sa;pwd=;database=northwind"
//查询语句
strComm="select * from region"
Dim oConn As New SQLConnection(strConn)
Dim oDSComm As New SQLDataSetCommand(strComm,oConn)
Dim oParam As SQLParameter
oDSComm.InsertCommand = new SQLCommand("Insert into Region (RegionID,
RegionDescription) VALUES (@RegionID, @RegionDescription)", oConn)
oParam = oDSComm.InsertCommand.Parameters.Add(new SQLParameter("@RegionID", SQLDataType.Int))
oParam.SourceVersion = DataRowVersion.Current
oParam.SourceColumn = "RegionID"
oParam = oDSComm.InsertCommand.Parameters.Add(new
SQLParameter("@RegionDescription", SQLDataType.NChar, 50))
oParam.SourceVersion = DataRowVersion.Current
oParam.SourceColumn = "RegionDescription"
Dim oDS as New DataSet
‘取回RegionID
oDSComm.MissingSchemaAction = MissingSchemaAction.AddWithKey
oDSComm.FillDataSet(oDS, "Region")
Dim index
Dim oRow as DataRow
for index=0 to 20
oRow = oDS.Tables("Region").NewRow()
oRow (0) = CStr(index)
oRow (1) = "地区"+CStr(index)
oDS.Tables("Region").Rows.Add(newRow)
next
Try
oDSComm.Update(oDS,"region")
Catch oException As Exception
//错误处理
Finally
oConn.close
End Try
3.4.2 修改记录
对数据库中的数据进行修改的sql 语句时非常简单的,如用:
UPDATE FORUM SET Notes=’大家好啊!!’ where [ID]=1
这个语句即可把表FORUM 中ID=1的字段Notes的值改为“大加好啊!!”,这些大家应该很熟悉的了。但是不知大家在 .NET中用此语句来操作数据库应用的如何?
下面就是我们具体的应用,我们创建一个aspx文件来具体时间一下。我们这个简单的程序具有编辑、更新、取消更新的功能。下面是我们的文件代码:
(code/database/UpDate.aspx)
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQL" %>
<html>
<script language="VB" runat="server">
'建立数据连接和命令对象
Dim UConn As SQLConnection
'在页面装入时用此方法
Sub Page_Load(Sender As Object, E As EventArgs)
'建立与数据库的连接
UConn = New SQLConnection("server=localhost;uid=NetBBS;pwd=;database=NETBBS")
If Not (IsPostBack)
BindGrid()
End If
End Sub
'在点击Edit连接时用到此方法:
Sub UDG_Edit(Sender As Object, E As DataGridCommandEventArgs)
UDG.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
'在点击Cancle连接时用到此方法:
Sub UDG_Cancel(Sender As Object, E As DataGridCommandEventArgs)
UDG.EditItemIndex = -1
BindGrid()
End Sub
'在点击UpDate连接时用到此方法:
Sub UDG_Update(Sender As Object, E As DataGridCommandEventArgs)
'创建数据集
Dim DS As DataSet
'创建命令对象
Dim UComm As SQLCommand
'定义修改数据的sql语句
Dim UpdateCmd As String = "UPDATE FORUM SET
[ID]=@fid,[Name]=@fname,Notes=@Notes,FatherID=@FatherID,status=@status where [ID]=@fid"
'设置命令对象类型
UComm = New SQLCommand(UpdateCmd, UConn)
'获得更改的数据
UComm.Parameters.Add(New SQLParameter("@fid", SQLDataType.VarChar, 4))
UComm.Parameters.Add(New SQLParameter("@fname", SQLDataType.VarChar, 50))
UComm.Parameters.Add(New SQLParameter("@Notes", SQLDataType.VarChar, 500))
UComm.Parameters.Add(New SQLParameter("@FatherID", SQLDataType.VarChar, 4))
UComm.Parameters.Add(New SQLParameter("@status", SQLDataType.VarChar, 1))
'
Dim Cols As String() = {"@fid","@fname","@Notes","@FatherID","@status"}
'激活数据连接
UComm.ActiveConnection.Open()
Try
'执行命令集
UComm.ExecuteNonQuery()
Message.InnerHtml = "修改成功!!"
'改为Edit连接
UDG.EditItemIndex = -1
'处理异常
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "相同的记录在数据库中"
Else
Message.InnerHtml = "不能更改纪录!!"
End If
Message.Style("color") = "red"
End Try
'关闭数据连接
UComm.ActiveConnection.Close()
'调用BindGrid()方法
BindGrid()
End Sub
'定义BindGrid()方法
Sub BindGrid()
Dim DS As DataSet
Dim UComm As SQLDataSetCommand
'从表forum选出数据
UComm = new SQLDataSetCommand("select * from forum", UConn)
'填充数据集
DS = new DataSet()
UComm.FillDataSet(DS, "forum")
'数据的绑定
UDG.DataSource=DS.Tables("forum").DefaultView
UDG.DataBind()
End Sub
</script>
<title>
Update!
</title>
<body style="font: 10pt verdana">
<BR>
<CENTER>
<form runat="server">
<h3><font face="Verdana">.NET->修改纪录</font></h3>
<span id="Message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p>
<!--响应对数据库操作的模板-->
<ASP:DataGrid id="UDG" runat="server"
Width="800"
BackColor="#ffffff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#ffffff"
OnEditCommand="UDG_Edit"
OnCancelCommand="UDG_Cancel"
OnUpdateCommand="UDG_Update"
>
<property name="Columns">
<asp:EditCommandColumn EditText="编辑" CancelText="取消" UpdateText="修改"
ItemStyle-Wrap="false"/>
</property>
</ASP:DataGrid>
</form>
</CENTER>
</body>
</html>