通过程序获得SQL Server自增型字段的函数:GetKey
通过程序获得SQL Server自增型字段的函数:GetKey概述:
通过程序来产生自增型字段,可以避免多用户操作的读取脏数据,操作也很简便.可以更好的在程序中控制这些关键字段的数值.
关键步骤:
1. 创建用于存放需要自增的数据表.(systemkey)
SQL Script 如下:
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table .
GO
CREATE TABLE . (
NOT NULL ,
(50)NOT NULL ,
NOT NULL ,
(50)NOT NULL ,
NULL
) ON
GO
KeyName:关键字的字段名(我们需要的字段名称,手工添加到这个表中) KeyValue:对应字段名的值.
SourceID:字段的来源,如果没有可以填””
LockTime:锁定的时间,在程序内部使用.
2. GetKeys方程,通过调用GetKeys得到关键字的值.
函数描述如下:
Imports Microsoft.ApplicationBlocks.Data
Imports Microsoft.VisualBasic.CompilerServices
Imports System.Threading
Imports System.Data.SqlClient
Public Class ClassTestClass ClassTest
Public Function GetKeys()Function GetKeys(ByVal KeyName As String, ByVal Source As String, ByVal CNString As String) As Integer
Dim connection As New SqlConnection(CNString)
Dim NewNum As Integer
Dim obj2 As Object
Dim sFlage As String = "Flag"
Try
Dim sql As String
Dim time As DateTime = DateAndTime.Now.AddSeconds(1)
connection.Open()
Do While (StringType.StrCmp(sFlage, "", False)0)
sql = (("Update Set ='" & Source & "', =GetDate()Where ='" & KeyName) & "' AND ((DATEADD(millisecond, 1000, LockTime)0) Then
sFlage = ""
Exit Do
End If
sFlage = "Err"
connection.Close()
If (DateTime.Compare(time, DateAndTime.Now) < 0) Then
Return -1
End If
Thread.Sleep(10)
Loop
sql = "Select KeyValueFrom Where ='" & KeyName & "' AND SourceID='" & Source & "'"
Dim OldNum As Object = SqlHelper.ExecuteScalar(connection, CommandType.Text, sql)
Dim num As Integer = (IntegerType.FromObject(OldNum) + 1)
sql = "Update Set =" & StringType.FromInteger(num) & ", ='' Where ='" & KeyName & "'"
SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sql)
NewNum = num
Catch exception As Exception
NewNum = -1
Finally
If Not connection Is Nothing Then
CType(connection, IDisposable).Dispose()
End If
End Try
Return NewNum
End Function
End Class
页:
[1]