|
Imports System End Namespace
------------------------------------------------------------------------------------
Imports System.Data.SqlClient
Public Class ControlBind
Dim myconndb As New TeamWork.Modules.CoreCode.Connection Dim myfunctiondb As New OperateFunction ‘本类用来绑定各种控件 ‘返回sqldatareader Public Function ReturnReader(ByVal myp As PublicPara) As SqlDataReader Dim Cmd As SqlCommand = myfunctiondb.ReturnCmd(myp) Dim MyDtr As SqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection) Return MyDtr End Function ‘数据控件的数据绑定（通用于Dropdownlist,datagrid,datalist）（数据源是Datareader）不分页 Public Sub DataControlDataReaderBinding(ByVal myp As PublicPara) myp.DataControl.DataSource = ReturnReader(myp) If TypeOf (myp.DataControl) Is DataList Or TypeOf (myp.DataControl) Is DataGrid Then If myp.DataKeyField <> "" Then myp.DataControl.DataKeyField = myp.DataKeyField End If myp.DataControl.DataBind() ElseIf TypeOf (myp.DataControl) Is DropDownList Then myp.DataControl.DataTextField = myp.DataTextField myp.DataControl.DataValueField = myp.DataValueField myp.DataControl.DataBind() End If myconndb.Conn.Close() End Sub ‘数据绑定分页效果
Public Function DataBindAspNetPager(ByVal myp As PublicPara) myp.pager.RecordCount = myfunctiondb.ReturnCount(myp.strCountItem) ‘Dim cmd As New SqlCommand(myp.ProcedureName, myconndb.Conn) Dim cmd As SqlCommand = myfunctiondb.ReturnCmd(myp) Dim adp As New SqlDataAdapter(cmd) Dim ds As New DataSet adp.Fill(ds, myp.pager.PageSize * (myp.pager.CurrentPageIndex - 1), myp.pager.PageSize, "data") myp.DataControl.DataSource = ds.Tables("data") If myp.DataKeyField <> "" Then
myp.DataControl.DataKeyField = myp.DataKeyField End If myconndb.Conn.Close() If ds.Tables("data").Rows.Count > 0 Then myp.DataControl.DataBind() End If End Function End Class
------------------------------------------------------------------------------------ Imports System.Data Imports System.Data.SqlClient Imports Microsoft.ApplicationBlocks.Data Public Class OperateFunction
Dim myconndb As New TeamWork.Modules.CoreCode.Connection
#Region "给存储过程备用的命令对象"
Public Function ReturnCmd(ByVal myp As PublicPara) As SqlCommand
myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(myp.ProcedureName, myconndb.Conn) Cmd.CommandType = CommandType.StoredProcedure If Not myp.UserAccount Is Nothing Then Cmd.Parameters.Add("@UserAccount", myp.UserAccount) End If If Not myp.UserPassword Is Nothing Then Cmd.Parameters.Add("@UserPassword", myp.UserPassword) End If If Not myp.UserID Is Nothing Then Cmd.Parameters.Add("@UserId", myp.UserID) End If If Not myp.UserEmail Is Nothing Then Cmd.Parameters.Add("@UserEmail", myp.UserEmail) End If If Not myp.UserAddDate Is Nothing Then Cmd.Parameters.Add("@UserAddDate", myp.UserAddDate) End If If myp.UserPath <> "~\UserFaces\myface.png" Then Cmd.Parameters.Add("@UserPath", myp.UserPath) End If If Not myp.UserSign Is Nothing Then Cmd.Parameters.Add("@UserSign", myp.UserSign) End If If Not myp.UserInstruction Is Nothing Then Cmd.Parameters.Add("@UserInstruction", myp.UserInstruction) End If If Not myp.UserGoldCoin Is Nothing Then Cmd.Parameters.Add("@UserGoldCoin", myp.UserGoldCoin) End If If Not myp.UserCopperCoin Is Nothing Then Cmd.Parameters.Add("@UserCopperCoin", myp.UserCopperCoin) End If If Not myp.UserGrade Is Nothing Then Cmd.Parameters.Add("@UserGrade", myp.UserGrade) End If If Not myp.UserRoleID Is Nothing Then Cmd.Parameters.Add("@UserRoleID", myp.UserRoleID) End If If myp.IsUpload <> -1 Then Cmd.Parameters.Add("@IsUpload", myp.IsUpload) End If If Not myp.UserRoleName Is Nothing Then Cmd.Parameters.Add("@UserRoleName", myp.UserRoleName) End If If Not myp.TagID Is Nothing Then Cmd.Parameters.Add("@TagID", myp.TagID) End If If Not myp.TagName Is Nothing Then Cmd.Parameters.Add("@TagName", myp.TagName) End If If Not myp.TagClickTimes Is Nothing Then Cmd.Parameters.Add("@TagClickTimes", myp.TagClickTimes) End If If myp.AddDate <> "" Then Cmd.Parameters.Add("@AddDate", myp.AddDate) End If If myp.TeamID <> "" Then Cmd.Parameters.Add("@TeamID", myp.TeamID) End If If myp.TeamName <> "" Then Cmd.Parameters.Add("@TeamName", myp.TeamName) End If If myp.TeamLeader <> "" Then Cmd.Parameters.Add("@TeamLeader", myp.TeamLeader) End If If myp.TeamFacePath <> "" Then Cmd.Parameters.Add("@TeamFacePath", myp.TeamFacePath) End If If myp.TeamAffiche <> "" Then Cmd.Parameters.Add("@TeamAffiche", myp.TeamAffiche) End If If myp.TeamIntro <> "" Then Cmd.Parameters.Add("@TeamIntro", myp.TeamIntro) End If If myp.TeamAddDate <> "" Then Cmd.Parameters.Add("@TeamAddDate", myp.TeamAddDate) End If If myp.TeamClickTimes <> "" Then Cmd.Parameters.Add("@TeamClickTimes", myp.TeamClickTimes) End If If myp.TopicID <> "" Then Cmd.Parameters.Add("@TopicID", myp.TopicID) End If If myp.TopicTitle <> "" Then Cmd.Parameters.Add("@TopicTitle", myp.TopicTitle) End If If myp.TopicAddDate <> "" Then Cmd.Parameters.Add("@TopicAddDate", myp.TopicAddDate) End If If myp.TopicContent <> "" Then Cmd.Parameters.Add("@TopicContent", myp.TopicContent) End If If myp.TopicReadTimes <> "" Then Cmd.Parameters.Add("@TopicReadTimes", myp.TopicReadTimes) End If If myp.TopicAuthorID <> "" Then Cmd.Parameters.Add("@TopicAuthorID", myp.TopicAuthorID) End If If myp.IsGlobal <> 0 Then Cmd.Parameters.Add("@IsGlobal", myp.IsGlobal) End If If myp.TopicStyle <> 2 Then Cmd.Parameters.Add("@TopicStyle", myp.TopicStyle) End If If myp.IsFinished <> 0 Then Cmd.Parameters.Add("@IsFinished", myp.IsFinished) End If If myp.IsElite <> 0 Then Cmd.Parameters.Add("@IsElite", myp.IsElite) End If If myp.ReplyID <> 0 Then Cmd.Parameters.Add("@ReplyID", myp.ReplyID) End If If Not myp.ReplyContent Is Nothing Then Cmd.Parameters.Add("@ReplyContent", myp.ReplyContent) End If If Not myp.ReplyDate Is Nothing Then Cmd.Parameters.Add("@ReplyDate", myp.ReplyDate) End If If Not myp.ReplyerID Is Nothing Then Cmd.Parameters.Add("@ReplyerID", myp.ReplyerID) End If If myp.MessageID <> "" Then Cmd.Parameters.Add("@MessageID", myp.MessageID) End If If Not myp.MessageTitle Is Nothing Then Cmd.Parameters.Add("@MessageTitle", myp.MessageTitle) End If If myp.MessageContent <> "" Then Cmd.Parameters.Add("@MessageContent", myp.MessageContent) End If If Not myp.MessageAddDate Is Nothing Then Cmd.Parameters.Add("@MessageAddDate", myp.MessageAddDate) End If If myp.FromUserID <> "" Then Cmd.Parameters.Add("@FromUserID", myp.FromUserID) End If If myp.ToUserID <> "" Then Cmd.Parameters.Add("@ToUserID", myp.ToUserID) End If If myp.IsReaded <> "" Then Cmd.Parameters.Add("@IsReaded", myp.IsReaded) End If If myp.LinkID <> "" Then Cmd.Parameters.Add("@LinkID", myp.LinkID) End If If myp.LinkText <> "" Then Cmd.Parameters.Add("@LinkText", myp.LinkText) End If If myp.LinkAddDate <> "" Then Cmd.Parameters.Add("@LinkAddDate", myp.LinkAddDate) End If If myp.AttachmentSize <> "" Then Cmd.Parameters.Add("@AttachmentSize", myp.AttachmentSize) End If If myp.WebAffiche <> "" Then Cmd.Parameters.Add("@WebAffiche", myp.WebAffiche) End If If myp.UpFileStyle <> "" Then Cmd.Parameters.Add("@UpFileStyle", myp.UpFileStyle) End If If myp.IntroOfWeb <> "" Then Cmd.Parameters.Add("@IntroOfWeb", myp.IntroOfWeb) End If If myp.ProtocolOfWeb <> "" Then Cmd.Parameters.Add("@ProtocolOfWeb", myp.ProtocolOfWeb) End If If myp.MaxUserNumPerTeam <> "" Then Cmd.Parameters.Add("@MaxUserNumPerTeam", myp.MaxUserNumPerTeam) End If If myp.AttchmentID <> "" Then Cmd.Parameters.Add("@AttchmentID", myp.AttchmentID) End If If myp.AttchmentName <> "" Then Cmd.Parameters.Add("@AttchmentName", myp.AttchmentName) End If If myp.AttchmentStyle <> "" Then Cmd.Parameters.Add("@AttchmentStyle", myp.AttchmentStyle) End If If myp.AttachmentAddDate <> "" Then Cmd.Parameters.Add("@AttchmentAddDate", myp.AttachmentAddDate) End If If myp.AttachmentPath <> "" Then Cmd.Parameters.Add("@AttachmentPath", myp.AttachmentPath) End If If myp.MyFavorID <> "" Then Cmd.Parameters.Add("@MyFavorID", myp.MyFavorID) End If If myp.BelongToWhoID <> "" Then Cmd.Parameters.Add("@BelongToWhoID", myp.BelongToWhoID) End If If myp.MyFavorText <> "" Then Cmd.Parameters.Add("@MyFavorText", myp.MyFavorText) End If If myp.MyFavorUserID <> "" Then Cmd.Parameters.Add("@MyFavorUserID", myp.MyFavorUserID) End If If myp.MyFavorAddDate <> "" Then Cmd.Parameters.Add("@MyFavorAddDate", myp.MyFavorAddDate) End If If myp.User_TeamID <> "" Then Cmd.Parameters.Add("@User_TeamID", myp.User_TeamID) End If ‘If Not myp.ReturnValue Is Nothing Then ‘ Cmd.Parameters.Add("@ReturnValue", myp.ReturnValue) ‘End If If myp.AlarmContent <> "" Then Cmd.Parameters.Add("@AlarmContent", myp.AlarmContent) End If If myp.CommentID <> "" Then Cmd.Parameters.Add("@CommentID", myp.CommentID) End If If Not myp.TabType Is Nothing Then
Cmd.Parameters.Add("@TabType", myp.TabType) End If If Not myp.LinkUrl Is Nothing Then Cmd.Parameters.Add("@LinkUrl", myp.LinkUrl) End If Return Cmd End Function #End Region #Region "各种数据操作函数"
‘更新数据（不返回数据）（Update,insert,delete 三种Sql语句共同使用） Public Function UpdateData(ByVal myp As PublicPara) Dim Cmd As SqlCommand = ReturnCmd(myp) Cmd.ExecuteNonQuery() myconndb.Conn.Close() End Function ‘不返回数据 Public Function DataChange(ByVal Str As String) myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(Str, myconndb.Conn) Cmd.ExecuteNonQuery() myconndb.Conn.Close() End Function ‘返回datareader Public Function SelectQuery(ByVal Str As String) As SqlDataReader myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(Str, myconndb.Conn) Cmd.CommandType = CommandType.StoredProcedure Dim MyDtr As SqlDataReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection) Return MyDtr End Function ‘返回数据集 Public Function SelectData(ByVal Str As String) As DataSet myconndb.Conn.Open() Dim MyDad As SqlDataAdapter = New SqlDataAdapter(Str, myconndb.Conn) Dim MyDst As New DataSet MyDad.Fill(MyDst, "table1") Return MyDst End Function ‘返回数据表 Public Function ReturnDataTable(ByVal myp As PublicPara) As DataTable Dim MyDad As New SqlDataAdapter MyDad.SelectCommand = ReturnCmd(myp) Dim MyDst As New DataSet MyDad.Fill(MyDst) Return MyDst.Tables(0) End Function ‘返回数据行数
Public Function ReturnCount(ByVal Str As String) As Integer myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(Str, myconndb.Conn) Dim i As Integer i = Convert.ToInt32(Cmd.ExecuteScalar()) myconndb.Conn.Close() Return i End Function Public Function ReturnOneValue(ByVal myp As PublicPara) As String myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(myp.ProcedureName, myconndb.Conn) Dim i As String = Convert.ToString(Cmd.ExecuteScalar()) myconndb.Conn.Close() Return i End Function ‘返回一个数据对象
Public Function ReturnOneData(ByVal Myp As PublicPara) As Object myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(Myp.Str1, myconndb.Conn) Dim Data As Object Data = Cmd.ExecuteScalar() Return Data End Function ‘使用存储过程返回一条命令,字符串形式 Public Function ReturnCmd(ByVal Str As String) As SqlCommand myconndb.Conn.Open() Dim Cmd As SqlCommand = New SqlCommand(Str, myconndb.Conn) Cmd.CommandType = CommandType.StoredProcedure Return Cmd End Function ‘返回一个过程参数 Public Function ReturnOneNvarchDataFromProduce(ByVal Myp As PublicPara) As String Dim Cmd As SqlCommand = ReturnCmd(Myp) Dim OutputValue As SqlParameter = Cmd.Parameters.Add("@OutPutValue", SqlDbType.NVarChar, 255) OutputValue.Direction = ParameterDirection.Output Cmd.ExecuteNonQuery() myconndb.Conn.Close() If TypeOf (OutputValue.Value) Is DBNull Then Return 0 Else Return OutputValue.Value End If End Function ‘返回一个存储过程返回来的数据Integer Public Function ReturnOneDataFromProduce(ByVal myp As PublicPara) As Integer Dim Cmd As SqlCommand = ReturnCmd(myp) Dim ReturnValue As SqlParameter = Cmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 4) ReturnValue.Direction = ParameterDirection.ReturnValue Cmd.ExecuteNonQuery() myconndb.Conn.Close() Return ReturnValue.Value End Function #End Region
#Region "各种字符串操作函数" ‘字符截取函数 Public Function GetTopWords(ByVal str As String, ByVal strlen As Integer) Dim l, t, c, i As Integer l = Len(str.Trim()) t = 0 For i = 1 To l c = Math.Abs(Asc(Mid(str, i, 1))) t = t + 1 If t >= strlen Then GetTopWords = Left(str, i) & "..." Exit For Else GetTopWords = str & " " End If Next End Function #End Region #Region "从DAAB里绑定数据用sqlhelper,呵呵,这个好"
‘从用DAAB绑定数据 ‘使用 SqlHelper 类执行命令 ‘SqlHelper 类提供了五种 Shared (Visual Basic) 或 static (C#) 方法，它们是：ExecuteNonQuery、ExecuteDataset、 ‘ExecuteReader、ExecuteScalar 和 ExecuteXmlReader。 ‘实现的每种方法都提供一组一致的重载。这提供了一种很好的使用 SqlHelper 类来执行命令的模式，同时为开发人员选择 ‘访问数据的方式提供了必要的灵活性。每种方法的重载都支持不同的方法参数，因此开发人员可以确定传递连接、事务和参数信 ‘息的方式。 ‘Function ReturnDataSetBySqlHelper(byval strProcedureName as String,byval ) ‘ Dim myconndb As TeamWork.Modules.CoreCode.Connection ‘ Dim ds As DataSet = SqlHelper.ExecuteDataset( _ ‘ myconndb.Conn, _ ‘ CommandType.StoredProcedure, _ ‘ "strProcedureName", _ ‘ New SqlParameter("@CategoryID", categoryID)) ‘End Function #End Region
#Region "datagrid分页代码"
‘********************datagrid等数据控件分页操作开始********************** Dim ds As DataSet Dim dad As SqlDataAdapter ‘专门给datagrid等数据控件分页返回的Adapter Private Function ReturnBindingAdapter(ByVal Myp As PublicPara) As SqlDataAdapter Dim Cmd As SqlCommand = ReturnCmd(Myp) Dim MyDapter As New SqlDataAdapter MyDapter.SelectCommand = Cmd Return MyDapter End Function ‘分页Myp的初始化（重新赋值）Myp.int1表示数据记录总数，Myp.int2表示Pagesize，Myp.int3表示总页数，Myp.int4表示当前页，Myp.str1表示查询语句 Public Sub PageLoad(ByVal Myp As PublicPara) dad = ReturnBindingAdapter(Myp) ds = New DataSet dad.Fill(ds, "data") myconndb.Conn.Close() Myp.Int1 = ds.Tables("data").Rows.Count If Myp.Int1 <> 0 Then If Myp.Int1 Mod Myp.Int2 <> 0 Then Myp.Int3 = Myp.Int1 \ Myp.Int2 + 1 Else Myp.Int3 = Myp.Int1 \ Myp.Int2 End If If Myp.Int4 > Myp.Int3 Then Myp.Int4 = Myp.Int3 End If If Not Myp.MyDrop Is Nothing Then Myp.MyDrop.Items.Clear() For i As Integer = 1 To Myp.Int3 Myp.MyDrop.Items.Add("第" + CStr(i) + "页") Next Myp.MyDrop.Items(0).Selected = True End If End If If Myp.Int4 = 1 Or Myp.Int4 = 0 Then Myp.Int4 = 1 Else Myp.Int4 = Myp.Int4 End If DataBind(Myp) End Sub ‘控件数据绑定（每页）
Public Sub DataBind(ByVal myp As PublicPara) dad = ReturnBindingAdapter(myp) ds = New DataSet ds.Clear() dad.Fill(ds, (myp.Int4 - 1) * myp.Int2, myp.Int2, "data") myp.DataControl.DataSource = ds.Tables("data") myp.DataControl.DataKeyField = myp.DataKeyField myp.DataControl.DataBind() myconndb.Conn.Close() myp.lbl1.Text = myp.Int1 myp.lbl2.Text = myp.Int4 myp.lbl3.Text = myp.Int3 myp.lbl4.Text = myp.Int2 If myp.Int3 < 2 Then myp.lbtn1.Enabled = False myp.lbtn3.Enabled = False myp.lbtn2.Enabled = False myp.lbtn4.Enabled = False Else If myp.Int4 > 1 Then If myp.Int4 < myp.Int3 Then myp.lbtn1.Enabled = True myp.lbtn3.Enabled = True myp.lbtn2.Enabled = True myp.lbtn4.Enabled = True Else myp.lbtn1.Enabled = True myp.lbtn3.Enabled = True myp.lbtn2.Enabled = False myp.lbtn4.Enabled = False End If Else myp.lbtn1.Enabled = False myp.lbtn3.Enabled = False myp.lbtn2.Enabled = True myp.lbtn4.Enabled = True End If End If End Sub ‘首页数据绑定
Public Sub lbtn1(ByVal myp As PublicPara) myp.Int4 = 1 DataBind(myp) End Sub ‘下一页数据绑定
Public Sub lbtn2(ByVal myp As PublicPara) myp.Int4 = myp.Int4 + 1 DataBind(myp) End Sub ‘上一页数据绑定
Public Sub lbtn3(ByVal myp As PublicPara) myp.Int4 = myp.Int4 - 1 DataBind(myp) End Sub ‘尾页数据绑定
Public Sub lbtn4(ByVal myp As PublicPara) myp.Int4 = myp.Int3 DataBind(myp) End Sub ‘跳转页操作
Public Sub GoToPage(ByVal myp As PublicPara) DataBind(myp) End Sub ‘********************datagrid 分页操作结束**********************
#End Region
End Class --------------------------------------------------------------------------------- Public Class PublicPara
‘定义字符串 Public ProcedureName As String ‘存储过程名 Public Str1 As String Public StrDataKeyField As String Public StrDataSource As String Public StrDataValueField As String Public StrDataTextField As String Public strCountItem As String ‘定义数据库字段名 Public UserID As String Public UserAccount As String Public UserPassword As String Public UserEmail As String Public UserAddDate As String Public UserPath As String = "~\UserFaces\myface.png" Public UserGoldCoin As String Public UserCopperCoin As String Public UserGrade As String Public UserSign As String Public UserInstruction As String Public UserRoleID As String Public IsUpload As Integer = -1 Public UserRoleName As String
Public TagID As String
Public TagName As String Public TagClickTimes As String Public AddDate As String Public TeamID As String
Public TeamName As String Public TeamLeader As String Public TeamFacePath As String Public TeamAffiche As String Public TeamIntro As String Public TeamAddDate As String Public TeamClickTimes As String Public TopicID As String
Public TopicTitle As String Public TopicAddDate As String Public TopicContent As String Public TopicReadTimes As String Public TopicAuthorID As String Public IsGlobal As Integer Public TopicStyle As Integer = 2 Public IsFinished As Integer Public IsElite As Integer Public ReplyID As Integer
Public ReplyContent As String Public ReplyDate As String Public ReplyerID As String Public MessageID As String
Public MessageTitle As String Public MessageContent As String Public MessageAddDate As String Public ToUserID As String Public FromUserID As String Public IsReaded As String Public LinkID As String
Public LinkText As String Public LinkAddDate As String Public LinkUrl As String Public AttachmentSize As String Public WebAffiche As String Public UpFileStyle As String Public IntroOfWeb As String Public ProtocolOfWeb As String Public MaxUserNumPerTeam As String Public AttchmentID As String
Public AttchmentName As String Public AttchmentStyle As String Public AttachmentAddDate As String Public AttachmentPath As String Public MyFavorID As String
Public BelongToWhoID As String Public MyFavorText As String Public MyFavorUserID As String Public MyFavorAddDate As String Public User_TeamID As String
Public AlarmID As String
Public AlarmContent As String Public CommentID As String Public ReturnValue As String
‘定义控件及相关参数
Public lbl As Label Public MyDgrd As DataGrid Public MyDlst As DataList Public MyDrop As DropDownList Public DataValueField As String Public DataKeyField As String Public DataTextField As String ‘Public myDataSource As DataSet Public TabType As String
‘对象
Public DataControl As Object Public pager As Wuqi.Webdiyer.AspNetPager Public Int1 As Integer
Public Int2 As Integer Public Int3 As Integer Public Int4 As Integer Public Int5 As Integer Public Int6 As Integer Public Int7 As Integer Public Int8 As Integer Public Int9 As Integer Public lbl1 As Label Public lbl2 As Label Public lbl3 As Label Public lbl4 As Label Public lbl5 As Label Public lbl6 As Label Public lbl7 As Label Public lbl8 As Label Public lbtn1 As LinkButton Public lbtn2 As LinkButton Public lbtn3 As LinkButton Public lbtn4 As LinkButton End Class |
|
|
