学习记录：一个通用存储过程，用来取出一个表中符合条件的字段或行或多行

在做数据库相关编程时，经常要从一个表中取出符合某一个条件的字段或行
例如：想要从某个表中提取ID=某个特定ID的某个字段或行，为了防止重复编写存储过程，写了一个小工具来完成这项工作。

CREATE PROCEDURE 
prCommSelectSomeByTableNameAndFieldNameAndConditionAndData
@TableName varchar(100),
@FieldName varchar(100),
@Condition varchar(100),
@data varchar(100),
@orderby varchar(100)=null

AS
declare @strSql nvarchar(4000)
set @strSql='select ' + @FieldName  +  ' from ' +  @tablename + '  where '  +  @Condition + ' =  ' + char(39) + @Data + char(39)
if len(@orderby)<>0
    set @[email protected] + ' order by ' + @orderby
exec sp_executesql @strSql
GO

VB.NET中使用此存储过程的函数如下：

	Public Function 
    CommSelectSomeByTableNameAndFieldNameAndConditionAndData(ByVal strTableName As String, ByVal strFieldName As String, ByVal strCondition As String, ByVal strConditionData As String, ByVal strOrderby As String) As SqlDataReader

	Dim cn As New SqlConnection("Server=" & g_DatabaseServerIp & ";Initial Catalog=" & g_DatabaseName & ";UID=" & g_DatabaseUser & ";Pwd=" & g_DatabaseUserPassword)
	cn.Open()

	Dim Cmd As New SqlCommand()
	Cmd.Connection = cn
	Cmd.CommandText = "prCommSelectSomeByTableNameAndFieldNameAndConditionAndData " & Chr(39) & strTableName & Chr(39) & "," & Chr(39) & strFieldName & Chr(39) & "," & Chr(39) & strCondition & Chr(39) & "," & Chr(39) & strConditionData & Chr(39) & "," & Chr(39) & strOrderby & Chr(39)

	CommSelectSomeByTableNameAndFieldNameAndConditionAndData = Cmd.ExecuteReader()

End Function

VB.NET中调用此函数的例子如下：
一 从tbbuybuys表中挑选ID等于送入的ID的一行或多行,不需要排序，填充到ListView控件中

    Dim tmpItem As New ListViewItem
	Dim dr As SqlDataReader
	dr = CommSelectSomeByTableNameAndFieldNameAndConditionAndData("tbbuybuys", "*", "id", FrmBuyInGuarderAddModify.strID, "")
	FrmBuyInGuarderAddModify.Dispose()

	If dr.HasRows() = True Then
		Do While dr.Read() = True
			tmpItem = LVData.Items.Add(dr.Item("id").ToString(), BuyIcoStatu(dr))
			Call FillItem(tmpItem, dr)
			tmpItem.EnsureVisible()
			tmpItem.Selected = True
		Loop
	End If
	dr.Close()

二 从tbbuybuys表中挑选intime字段，条件为id等于传入的id，不需要排序。

    Dim strInTime As String
	Dim dr As SqlDataReader
	dr = CommSelectSomeByTableNameAndFieldNameAndConditionAndData("tbbuybuys", "intime", "id", strID, "")
	If dr.HasRows Then
		dr.Read()
		strInTime = dr.Item("intime").ToString
	Else
		strInTime = ""
	End If