数据库类

水如烟 2008-04-01 04:32:09
加精
利用DbProviderFactory了.


类和源代码都以上传到资源,地址在这.
主要的,
1,方便,简单,扩展(复杂的话,专业的多多,不容自己来考虑)
2,方便无主键表的更新
3,方便输出到Excel(可以利用Select ..From [Sheet1$]来选择Excel数据再传到另一个Excel)

以后在vb.net解答有关数据库的问题,我都会引用这个类.





示例:

Imports System.Data.Common

Public Class Form1

Private db As New LzmTW.Data.SqlDatabase("(local)", "NorthWind")
Private cm As DbCommand
Private ds As New DataSet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
cm = db.CreateCommand("[Sales by Year]", True)
db.AddParameter(cm, "Beginning_Date").DbType = DbType.DateTime
db.AddParameter(cm, "Ending_Date").DbType = DbType.DateTime


db.SetParameterValue(cm, "Beginning_Date", #7/4/1996#)
db.SetParameterValue(cm, "Ending_Date", #9/4/1996#)
db.Load(cm, ds, "1996")

db.SetParameterValue(cm, "Beginning_Date", #7/4/1997#)
db.SetParameterValue(cm, "Ending_Date", #9/4/1997#)
db.Load(cm, ds, "1997")

db.Load("SELECT * FROM ORDERS;SELECT * FROM REGION", ds, New String() {"ORDERS", "REGION"})
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Me.DataGridView1.DataSource = ds.Tables("1996")
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.DataGridView1.DataSource = ds.Tables("1997")
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Me.DataGridView1.DataSource = ds.Tables("ORDERS")
End Sub

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Me.DataGridView1.DataSource = ds.Tables("REGION")
End Sub

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Dim db As New LzmTW.Data.ExcelDatabase("g:\dd.xls")
Console.WriteLine(db.ToExcel(New String() {"[AA$]", "[BB$]"}, "g:\t.xls", New String() {"AA", "BB"}))
End Sub


End Class
...全文
2499 74 打赏 收藏 转发到动态 举报
写回复
用AI写文章
74 条回复
切换为时间正序
请发表友善的回复…
发表回复
hgziy 2012-10-22
  • 打赏
  • 举报
回复
mark
yangengchao 2012-07-13
  • 打赏
  • 举报
回复
很好,,,感谢分享。
ZZ 2011-09-19
  • 打赏
  • 举报
回复
谢谢楼主的分享
lhjsj 2011-08-23
  • 打赏
  • 举报
回复
学习学习
FengZhongXia 2010-10-17
  • 打赏
  • 举报
回复
收藏加学习,你太强了。不顶不停
a13569369465 2010-04-12
  • 打赏
  • 举报
回复
牛逼。。。。
yurengang 2009-04-17
  • 打赏
  • 举报
回复
sqsy_aqsy 2009-04-12
  • 打赏
  • 举报
回复
LZ乃牛人也!
我要好好研究研究!
飞鹰再现 2008-07-27
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 tangweikai 的回复:]
我也做了一个,不过我的是继承了一个DataSet,在些基础上增加数据处理功能,比这个可强大了..哈哈
正用在当前的项目上...
[/Quote]
拿出来与大家分享一下嘛!
cdbqss1 2008-05-31
  • 打赏
  • 举报
回复
高,学习中
水如烟 2008-04-25
  • 打赏
  • 举报
回复

数据库类的扩展:数据库的建立和表的建立
http://topic.csdn.net/u/20080425/11/ea265ef9-1215-405c-a290-489631b00758.html
水如烟 2008-04-15
  • 打赏
  • 举报
回复
我的是用2008编写的.

上面的回贴中,我提供了2005的项目文件.把它覆盖相应文件,应该可以了.
最关键的,是 LzmTW.Data.vbproj
qinboody 2008-04-15
  • 打赏
  • 举报
回复
水水水水水水水水
為什么我下載妳的文件,打不開.
說什么"找不到匯入的專案"C:/MICROSOFT.VISUSALBASIC.TARGETS"請确認<IMPORT>宣告中的路徑是否正确,而且檔案存在于磁碟上"
是不是少了什么文件呀,輕解答!
ping502 2008-04-15
  • 打赏
  • 举报
回复
這個貼希望也能置頂一下。
ping502 2008-04-15
  • 打赏
  • 举报
回复
水哥你太厲害了
水如烟 2008-04-15
  • 打赏
  • 举报
回复
我指的是39楼的内容.
qinboody 2008-04-15
  • 打赏
  • 举报
回复
<ProductVersion>8.0.50727 </ProductVersion>改了還是不行.沒辦法,就看代碼得了. 還有, 我正在看妳在給我的回貼.關于根据變量名稱獲得值的問題.謝謝妳.
水如烟 2008-04-14
  • 打赏
  • 举报
回复
如果前后空格都是必需的,那么用''括起来即可.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString1 As String = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
Dim connectionString2 As String = "Data Source= myServerAddress ;Initial Catalog= myDataBase ;User Id= myUsername ;Password=' myPassword ';"

Dim b1 As New SqlClient.SqlConnectionStringBuilder(connectionString1)
Dim b2 As New SqlClient.SqlConnectionStringBuilder(connectionString2)

Console.WriteLine("equals Data Source? {0} ,value is {1}", b1.DataSource.Equals(b2.DataSource), b2.DataSource)
Console.WriteLine("equals Inital Catalog? {0} ,value is {1}", b1.InitialCatalog.Equals(b2.InitialCatalog), b2.InitialCatalog)
Console.WriteLine("equals User ID? {0} ,value is {1}", b1.UserID.Equals(b2.UserID), b2.UserID)
Console.WriteLine("equals Password? {0} ,value is {1}", b1.Password.Equals(b2.Password), b2.Password)
End Sub

'结果:
'equals Data Source? True ,value is myServerAddress
'equals Inital Catalog? True ,value is myDataBase
'equals User ID? True ,value is myUsername
'equals Password? False ,value is myPassword
水如烟 2008-04-14
  • 打赏
  • 举报
回复
关于为什么用户名、密码前后的空格不给认帐了?

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString1 As String = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
Dim connectionString2 As String = "Data Source= myServerAddress ;Initial Catalog= myDataBase ;User Id= myUsername ;Password= myPassword ;"

Dim b1 As New SqlClient.SqlConnectionStringBuilder(connectionString1)
Dim b2 As New SqlClient.SqlConnectionStringBuilder(connectionString2)

Console.WriteLine("equals Data Source? {0} ,value is {1}", b1.DataSource.Equals(b2.DataSource), b1.DataSource)
Console.WriteLine("equals Inital Catalog? {0} ,value is {1}", b1.InitialCatalog.Equals(b2.InitialCatalog), b1.InitialCatalog)
Console.WriteLine("equals User ID? {0} ,value is {1}", b1.UserID.Equals(b2.UserID), b1.UserID)
Console.WriteLine("equals Password? {0} ,value is {1}", b1.Password.Equals(b2.Password), b1.Password)
End Sub

'结果:
'equals Data Source? True ,value is myServerAddress
'equals Inital Catalog? True ,value is myDataBase
'equals User ID? True ,value is myUsername
'equals Password? True ,value is myPassword
水如烟 2008-04-14
  • 打赏
  • 举报
回复
关于Com的退出.

下面的方法有人提出看法:
这个方法不可行,主要原因是,如果同时有两上人来访问这段代码就会有两个winword进程,一个是你创建,一个是另外一个人的你在用startTime = myProcess.StartTime比较时,如果是另外一个人创建的是不允许你访问Process.StartTime属性,会报出Win32Exception错误.


我想,如果是在本地使用的话,应该没有问题.

示例:
Option Strict Off

Imports System.Runtime.InteropServices

Public Class Form1

Private Function CreateOfficeApplication(ByVal progID As String) As Object
Return CreateObject(progID)
End Function

Dim t1 As New LzmTW.MSOffice.ComBuilder
Dim t2 As New LzmTW.MSOffice.ComBuilder

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
t1.Create(AddressOf CreateOfficeApplication, "Excel.Application", "EXCEL")

t1.ComApplication.Visible = True
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
t2.Create(AddressOf CreateOfficeApplication, "Excel.Application", "EXCEL")

t2.ComApplication.Visible = True
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
t1.ComApplication.Quit()
t1.Release()
End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
t2.ComApplication.Quit()
t2.Release()
End Sub

End Class


代码:
Imports System.Runtime.InteropServices

Namespace LzmTW.MSOffice

Public Delegate Function CreateComHanlder(ByVal progID As String) As Object

Public Class ComBuilder
Private gComProcess As Process
Private gComApplication As Object
Private gValid As Boolean = False

Public Property Valid() As Boolean
Get
Return gValid
End Get
Private Set(ByVal value As Boolean)
gValid = value
End Set
End Property

Public Property ComProcess() As Process
Get
Return gComProcess
End Get
Private Set(ByVal value As Process)
gComProcess = value
End Set
End Property

Public Property ComApplication() As Object
Get
Return gComApplication
End Get
Private Set(ByVal value As Object)
gComApplication = value
End Set
End Property

Public Sub Create(ByVal createCom As CreateComHanlder, ByVal progID As String, ByVal processName As String)
If Me.Valid Then
Throw New Exception("Com 已存在")
End If

InternalBuildInstance(createCom, progID, processName)
End Sub

Private Sub InternalBuildInstance(ByVal d As CreateComHanlder, ByVal progID As String, ByVal processName As String)
Dim beforeTime As Date
Dim afterTime As Date
Dim processTime As Date

beforeTime = Now
Me.ComApplication = d.Invoke(progID)
afterTime = Now

If Me.ComApplication Is Nothing Then
Throw New Exception("无法获取 Com 实例")
End If

If Not Marshal.IsComObject(Me.ComApplication) Then
Me.ComApplication = Nothing

Throw New Exception("非Com")
End If

Dim mProcessList As Process()

mProcessList = Process.GetProcessesByName(processName)

If mProcessList.Length = 0 Then
Me.ReleaseCom()

Throw New Exception("processName 无效")
End If

For Each p As Process In mProcessList
processTime = p.StartTime

If processTime.CompareTo(beforeTime) > 0 AndAlso processTime.CompareTo(afterTime) < 0 Then
Me.ComProcess = p

Exit For
End If
Next

If Me.ComProcess Is Nothing Then
Me.ReleaseCom()

Throw New Exception("无法定位process")
End If

Me.Valid = True
End Sub

Public Sub Release()
If Not Me.Valid Then Return

Me.ReleaseCom()
Me.Kill()

Me.Valid = False
End Sub

Private Sub ReleaseCom()
Marshal.ReleaseComObject(Me.ComApplication)
Me.ComApplication = Nothing

GC.Collect(2)
End Sub

Private Sub Kill()
Me.ComProcess.Kill()
Me.ComProcess.Dispose()
Me.ComProcess = Nothing
End Sub
End Class
End Namespace
加载更多回复(54)

16,549

社区成员

发帖
与我相关
我的任务
社区描述
VB技术相关讨论,主要为经典vb,即VB6.0
社区管理员
  • VB.NET
  • 水哥阿乐
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧