This class help us to connect DB, show message on browser and export EXCEL file. We can insert, update, delete and query.
' /**********************************************************************/
' /* System : */
' /* Sub System : */
' /* Module Name : */
' /* Description : */
' /* */
' /* Environment : Windows XP + MS Visual Studio 2005 */
' /* Transacion : None */
' /* Modification history */
' /* Date Label Editor Description */
' /* ========== ========== ========== ============================== */
' /* YYYY/MM/DD Author Create */
' /**********************************************************************/
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data
Imports System.IO
Public Class clsDb
Public Shared _ConnStr As String = "Data Source=your server name; Initial Catalog=your catalog; User Id=account;Password=password; Min Pool Size=2; Max Pool Size=40;Pooling=true; Connect Timeout=60"
Private Shared _logPath As String = "logs.log"
Public Sub New(ByVal xmlPath As String)
'constructor
End Sub
'query data from MSSQL
Public Shared Function QueryRunSQL(ByVal sSQL As String) As DataSet
Return QueryRunSQL(sSQL, clsDb._ConnStr)
End Function
'query data from MSSQL
Public Shared Function QueryRunSQL(ByVal sSQL As String, ByVal sConnStr As String) As DataSet
Dim sqlConn As SqlConnection = New SqlConnection(sConnStr)
Dim sqlDapt As SqlDataAdapter
Dim scmd As String = ""
Dim dsSource As New DataSet
Try
sqlDapt = New SqlDataAdapter(sSQL, sqlConn)
sqlDapt.Fill(dsSource)
Catch ex As Exception
dsSource = ErrorData(ex.Message)
Finally
sqlConn.Close()
sqlConn.Dispose()
SqlConnection.ClearAllPools()
End Try
Return dsSource
End Function
'insert, update or delete data from MSSQL
Public Shared Function ExecuteRunSQL(ByVal sql As String) As Integer
Dim list As ArrayList = New ArrayList()
list.Add(sql)
Return ExecuteRunSQL(list)
End Function
'insert, update or delete data from MSSQL
Public Shared Function ExecuteRunSQL(ByVal list As ArrayList) As Integer
Return ExecuteRunSQL(list, clsDb._ConnStr)
End Function
'insert, update or delete data from MSSQL
Public Shared Function ExecuteRunSQL(ByVal list As ArrayList, ByVal sConnStr As String) As Integer
Dim iProcess As Integer = 0
Try
If list Is Nothing Then
Return False
End If
Dim conn As SqlConnection = New SqlConnection(sConnStr)
conn.Open()
Dim trx = conn.BeginTransaction()
Dim command As New SqlCommand
command.Connection = conn
command.Transaction = trx
Try
For Each sSql As String In list
command.CommandText = sSql 'sSql is "delete * from table where condition"
iProcess = command.ExecuteNonQuery()
Next
trx.Commit()
Catch ex As Exception
trx.Rollback()
conn.Close()
Throw ex
Finally
conn.Close()
End Try
Catch ex As Exception
Throw New Exception(ex.Message + Chr(10) + Chr(13) + ex.StackTrace, ex)
Finally
list = Nothing
End Try
Return iProcess
End Function
'sorting hashtable
Public Function SortHashtable(ByVal oHash As Hashtable) As DataView
Dim oTable As New Data.DataTable
oTable.Columns.Add(New Data.DataColumn("key"))
oTable.Columns.Add(New Data.DataColumn("value"))
For Each oEntry As Collections.DictionaryEntry In oHash
Dim oDataRow As DataRow = oTable.NewRow()
oDataRow("key") = oEntry.Key
oDataRow("value") = oEntry.Value
oTable.Rows.Add(oDataRow)
Next
Dim oDataView As DataView = New DataView(oTable)
oDataView.Sort = "value DESC, key ASC"
Return oDataView
End Function
Public Shared Sub MessageBox(ByVal pgMain As Page, ByVal msg As String)
Dim strScript As String = ""
Try
msg = msg.Replace("'", "\'")
msg = msg.Replace(vbCrLf, "\r\n")
msg = msg.Replace(vbLf, "\n")
msg = msg.Replace(vbCr, "\r")
msg = msg.Replace("\""", "\\""")
strScript += ""
If (Not pgMain.ClientScript.IsStartupScriptRegistered(pgMain.GetType(), "MessageBox")) Then
pgMain.ClientScript.RegisterStartupScript(pgMain.GetType(), "MessageBox", strScript)
End If
Catch ex As Exception
pgMain.Response.Write(ex.ToString())
End Try
End Sub
Public Shared Function ShowError(ByVal dsSource As DataSet) As String
Dim sError As String = ""
Try
Dim msg As String = ""
If dsSource.Tables.Count > 0 Then
Dim dtTempTable As DataTable = dsSource.Tables("ErrorMessage")
Dim iJ As Integer
If dtTempTable.Rows.Count > 0 Then
For iJ = 0 To dtTempTable.Rows.Count - 1
msg = dtTempTable.Rows(iJ).Item(0).ToString.Replace("'", "")
msg = msg.Replace("。", "!")
msg = msg.Replace(vbCrLf, "\r\n")
Next
End If
End If
sError = msg
Catch ex As Exception
WriteLog(ex.ToString, "Exception Handler", _logPath)
End Try
Return sError
End Function
Public Shared Function ErrorData(ByVal errorMessage As String) As DataSet
Dim dsSource As New DataSet
Dim dtNewTable As DataTable = New DataTable("ErrorMessage")
dtNewTable.Columns.Add("Message")
Dim dr As DataRow = dtNewTable.NewRow()
dr("Message") = errorMessage
dtNewTable.Rows.Add(dr)
dsSource.Tables.Add(dtNewTable)
Return dsSource
End Function
Public Shared Sub WriteLog(ByVal sErrorMessage As String, ByVal sWho As String, ByVal sFileName As String)
Dim sFile As String
If sFileName Is Nothing Then
sFile = ".\\commonLogs.log"
Else
sFile = sFileName
End If
Dim sLogWriter As IO.StreamWriter = New IO.StreamWriter(sFile, True, System.Text.Encoding.Default)
Try
sLogWriter.WriteLine("[" & Format(DateTime.Now, "yyyy-MM-dd HH:mm:ss") + " user:" + sWho + "] message: " + sErrorMessage)
sLogWriter.Close()
Catch ex As Exception
sLogWriter.WriteLine("[Exception" & Format(DateTime.Now, "yyyy-MM-dd HH:mm:ss") + " user:" + sWho + "] message: " + ex.ToString)
sLogWriter.Close()
Exit Sub
End Try
End Sub
'export to Excel file
Public Shared Function ExportToXls(ByVal dt As System.Data.DataTable, ByVal xlsPath As String) As Boolean
If xlsPath.Trim = "" Then Exit Function
Dim txtPath As String = Replace(xlsPath, ".xls", ".csv")
If File.Exists(txtPath) = True Then File.Delete(txtPath)
If File.Exists(xlsPath) = True Then File.Delete(xlsPath)
Dim oExcel As Object = CreateObject("Excel.Application")
GC.Collect() : GC.WaitForPendingFinalizers()
Try
'---------------------------------
'create header
'---------------------------------
Dim sb As New StringBuilder
Dim icount As Integer
Dim sColumn As String = ""
For icount = 0 To dt.Columns.Count - 1
sColumn += dt.Columns(icount).ToString + ","
Next
sb.Append(sColumn.TrimEnd(","))
sb.Append(Chr(13) + Chr(10))
'---------------------------------
'create data
'---------------------------------
If dt.Rows.Count > 0 Then
For Each dr As DataRow In dt.Rows
Dim en_data As IEnumerator = dt.Columns.GetEnumerator
While en_data.MoveNext
sb.Append(dr.Item(en_data.Current).ToString).Append(",")
End While
sb.Append(Chr(13) + Chr(10))
Next
End If
'out a text file
Dim sw As StreamWriter = New StreamWriter(txtPath, False, System.Text.Encoding.Unicode)
sw.WriteLine(sb.ToString)
sw.Flush()
sw.Close()
'out a excel file
oExcel.Visible = False : oExcel.DisplayAlerts = False
oExcel.Workbooks.OpenText(Filename:=txtPath, StartRow:=1, DataType:=1, _
TextQualifier:=1, ConsecutiveDelimiter:=False, TAB:=False, _
Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, TrailingMinusNumbers:=True)
'oExcel.ActiveWorkbook.SaveAs(Filename:=xlsPath) 'output Excel file
Catch ex As Exception
ExportToXls = False
Throw New Exception(ex.ToString)
Finally
'If File.Exists(txtPath) = True Then File.Delete(txtPath) 'Del temp csv
oExcel.ActiveWorkbook.Close()
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()
ExportToXls = True
End Try
Return ExportToXls
End Function
Public Shared Function Now() As String
Return Format(DateTime.Now, "yyyy-MM-dd HH:mm:ss").Replace("-", "_").Replace(" ", "_").Replace(":", "_")
End Function
End Class
沒有留言:
張貼留言