数据库类

数据库类 [已结贴,结贴人:lzmtw]

lzmtw
水如烟
等级:

发表于：2008-04-01 16:32:09 楼主

利用DbProviderFactory了.

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

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

示例:

VB.NET code

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

问题点数：20 回复次数：66 修改删除举报引用回复

Public Class Form1 Private db As New LzmTW.Data.SqlDatabase(".", "NorthWind") '生成一个Table.如果按了这个了，往后就别按这个，会出错 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim SqlCreate As String = "CREATE TABLE Simple(ID int , Name varchar(50), sex bit)" db.Execute(SqlCreate) End Sub '绑定到DataGridView,并置ID为主键约束.然后在DataGridView上加数据 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim table As New DataTable("Simple") db.Load("Simple", table, New String() {"ID"}) Me.DataGridView1.DataSource = table End Sub '临时指定ID为主键，将DataGridView上的数据更新到数据库.更新了数据后，可以重按Button2看看是不是真的传上去了。 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click db.Update("Simple", CType(Me.DataGridView1.DataSource, DataTable), New String() {"ID"}) End Sub '将这个表的信息包括表头和数据都传到excel去. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click db.ToExcel("Simple", "g:\simple.xls", "Simple") End Sub '删除表 Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click db.Execute("DROP TABLE Simple") End Sub End Class