邏輯等於0與1和ON與OFF,是程式創作的思維. 如何利用創作來支援生活面呢? 唯有.. 一直努力在程式領域上突破及更新自我;更是要有失敗的心情準備!! 聯絡方式 LINE ID : wushig (池龍工作室) 0931507058 巫先生
2014年2月27日 星期四
SQLite Writing/Reading a BLOB Image into the Table
引用來源:SQLite in VB.net - Page 2
--
Imports System.Data.SQLite
Imports System.IO
Public Class sqlitetutorial
'Image BLOB Functions
Private Function BlobToImage(ByVal blob)
Dim mStream As New System.IO.MemoryStream
Dim pData() As Byte = DirectCast(blob, Byte())
mStream.Write(pData, 0, Convert.ToInt32(pData.Length))
Dim bm As Bitmap = New Bitmap(mStream, False)
mStream.Dispose()
Return bm
End Function
Public Overloads Function ImageToBlob(ByVal id As String, ByVal filePath As String)
Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim bm() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
'Create Parm
Dim photo() As Byte = bm
Dim SQLparm As New SQLiteParameter("@image", photo)
SQLparm.DbType = DbType.Binary
SQLparm.Value = photo
Return SQLparm
End Function
'NOT USED IN THE SOURCE.
Public Overloads Function ImageToBlob(ByVal id As String, ByVal image As Image)
Dim ms As New MemoryStream()
image.Save(ms, System.Drawing.Imaging.ImageFormat.Png)
'Create Parm
Dim photo() As Byte = ms.ToArray()
Dim SQLparm As New SQLiteParameter("@image", photo)
SQLparm.DbType = DbType.Binary
SQLparm.Value = photo
Return SQLparm
End Function
Private Sub btn_insertimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insertimage.Click
Dim f As New SaveFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
Dim d As New OpenFileDialog
d.Filter = "Image (*.png)|*.png|All Files|*.*"
If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
'SQL query to Create Table
SQLcommand.CommandText = "CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, image BLOB);"
SQLcommand.ExecuteNonQuery()
'Insert image, DO NOT single quote @image
SQLcommand.CommandText = "INSERT INTO foo (image) VALUES(@image)"
'Define @image
SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
Private Sub btn_viewimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_viewimage.Click
Dim f As New OpenFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
If f.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT image FROM foo WHERE id = '1'"
Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
While SQLreader.Read()
pic_viewimage.Image = BlobToImage(SQLreader("image"))
End While
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
Private Sub btn_updateimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_updateimage.Click
Dim f As New OpenFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
Dim d As New OpenFileDialog
d.Filter = "Image (*.png)|*.png|All Files|*.*"
If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
'Update image, DO NOT single quote @image
SQLcommand.CommandText = "UPDATE foo SET image = @image WHERE id = '1'"
'Define @image
SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
End Class
--
Imports System.Data.SQLite
Imports System.IO
Public Class sqlitetutorial
'Image BLOB Functions
Private Function BlobToImage(ByVal blob)
Dim mStream As New System.IO.MemoryStream
Dim pData() As Byte = DirectCast(blob, Byte())
mStream.Write(pData, 0, Convert.ToInt32(pData.Length))
Dim bm As Bitmap = New Bitmap(mStream, False)
mStream.Dispose()
Return bm
End Function
Public Overloads Function ImageToBlob(ByVal id As String, ByVal filePath As String)
Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim bm() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
'Create Parm
Dim photo() As Byte = bm
Dim SQLparm As New SQLiteParameter("@image", photo)
SQLparm.DbType = DbType.Binary
SQLparm.Value = photo
Return SQLparm
End Function
'NOT USED IN THE SOURCE.
Public Overloads Function ImageToBlob(ByVal id As String, ByVal image As Image)
Dim ms As New MemoryStream()
image.Save(ms, System.Drawing.Imaging.ImageFormat.Png)
'Create Parm
Dim photo() As Byte = ms.ToArray()
Dim SQLparm As New SQLiteParameter("@image", photo)
SQLparm.DbType = DbType.Binary
SQLparm.Value = photo
Return SQLparm
End Function
Private Sub btn_insertimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insertimage.Click
Dim f As New SaveFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
Dim d As New OpenFileDialog
d.Filter = "Image (*.png)|*.png|All Files|*.*"
If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
'SQL query to Create Table
SQLcommand.CommandText = "CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, image BLOB);"
SQLcommand.ExecuteNonQuery()
'Insert image, DO NOT single quote @image
SQLcommand.CommandText = "INSERT INTO foo (image) VALUES(@image)"
'Define @image
SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
Private Sub btn_viewimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_viewimage.Click
Dim f As New OpenFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
If f.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT image FROM foo WHERE id = '1'"
Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
While SQLreader.Read()
pic_viewimage.Image = BlobToImage(SQLreader("image"))
End While
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
Private Sub btn_updateimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_updateimage.Click
Dim f As New OpenFileDialog
f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
Dim d As New OpenFileDialog
d.Filter = "Image (*.png)|*.png|All Files|*.*"
If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
'Update image, DO NOT single quote @image
SQLcommand.CommandText = "UPDATE foo SET image = @image WHERE id = '1'"
'Define @image
SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
SQLcommand.ExecuteNonQuery()
SQLcommand.Dispose()
SQLconnect.Close()
End If
End Sub
End Class
2014年2月26日 星期三
取得螢幕解析度與工作區域大小
以vb.net 2005 為範例
一、取得螢幕解析度
Dim Screen_X
Dim Screen_Y
Screen_X= Screen.PrimaryScreen Screen.PrimarBounds.Width
Screen_Y= Screen.PrimaryScreen Screen.PrimarBounds.Height
MessageBox.Show("螢幕解析度" & Screen_X & "X" & Screen_Y)
二、取得工作區域大小（桌面大小）
Dim workarea_Hight As Integer
Dim workerarea_width As Integer
workarea_Hight = Screen.PrimaryScreen.WorkingArea.Width
workerarea_width = Screen.PrimaryScreen.WorkingArea.Height
MessageBox.Show("工作區域大小" & workerarea_width & "X" & workarea_Hight)
VB.Net 動態生成子控制(Button,TextBox)
引用來源:VB.Net 動態生成子控制(Button,TextBox)
--
動態生成子控制不是問題，重點是怎麼把對應的函式自動加進去。
範例一 - 動態生成 TextBox
Public Class Form
' 動態生成 TextBox
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim myTextBox As TextBox
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
' 動態生成 TextBox
For i = 0 To cnt - 1
myTextBox = New TextBox()
myTextBox.Text = "button" & i
myTextBox.Left = x0
myTextBox.Top = y0 + i * (h + hd)
myTextBox.Width = w
myTextBox.Height = h
Me.Controls.Add(myTextBox)
AddHandler myTextBox.Click, AddressOf myTextBoxClick ' 交附給函式
Next
End Sub
Private Sub myTextBoxClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, TextBox).Text)
End Sub
End Class
範例二 - 動態生成 Button (1)
初版的動態生成 Button 缺點不少，這裡是將 myButton 使用動態陣列，每個陣列名字都長得差不多，另外對應函式是用一個一個 map 起來的。原始碼如下
Public Class Form
' 動態生成按扭
Private myButton() As Button
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
ReDim myButton(cnt)
' 動態生成 button
For i = 0 To cnt - 1
myButton(i) = New Button()
myButton(i).Text = "button" & i
myButton(i).Left = x0
myButton(i).Top = y0 + i * (h + hd)
myButton(i).Width = w
myButton(i).Height = h
Me.Controls.Add(myButton(i))
Next
' 進行函式對應
AddHandler myButton(0).Click, AddressOf Func0
AddHandler myButton(1).Click, AddressOf Func1
AddHandler myButton(2).Click, AddressOf Func2
AddHandler myButton(3).Click, AddressOf Func3
End Sub
Sub Func0()
MsgBox("func0")
End Sub
Sub Func1()
MsgBox("func1")
End Sub
Sub Func2()
MsgBox("func2")
End Sub
Sub Func3()
MsgBox("func3")
End Sub
End Class
範例三 - 動態生成 Button (2)
第二版動態生成按鈕改善了一點點，由於這幾顆按鈕做的事都一樣，所以便用委派方式給同一個函式執行，但實際上效果並不彰。
Public Class Form
' 動態生成按扭
Private myButton() As Button
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
ReDim myButton(cnt)
' 動態生成 button
For i = 0 To cnt - 1
myButton(i) = New Button()
myButton(i).Text = "button" & i
myButton(i).Left = x0
myButton(i).Top = y0 + i * (h + hd)
myButton(i).Width = w
myButton(i).Height = h
Me.Controls.Add(myButton(i))
AddHandler myButton(i).Click, AddressOf myButtonClick
Next
End Sub
' 委派函式
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, Button).Text)
End Sub
End Class
範例四 - 動態生成 Button (3)
第三版動態生成按鈕改善了二個部份，我們可以借由自己寫的函式，把 Button Name 一次全換掉；除此之外，事實上 myButton 可以不用 array 方式建立，同時也可以不用宣告到全域。
注意的是那個 SetButtonName，它的引數記得是放 string，不要直接傳 Button 實體進去，傳 Button 實體進去的話，做法就要等 Button 全都建完之後才可以去呼叫 SetButtonName，效率不彰。
Public Class Form
' 動態生成按扭
' 設定按鈕名稱
Private Sub SetButtonName(ByRef btnName() as String, ByVal ParamArray Name() As String)
For i As Int32 = 0 To UBound(btnName) - 1
btnName(i) = Name(i)
Next i
End Sub
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim btnName() As String
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
Dim myButton As Button
ReDim btnName(cnt)
SetButtonName(btnName, "OK", "ERROR", "WARNNING", "RETRY")
' 動態生成 button
For i = 0 To cnt - 1
myButton = New Button()
myButton.Text = btnName(i)
myButton.Left = x0
myButton.Top = y0 + i * (h + hd)
myButton.Width = w
myButton.Height = h
Me.Controls.Add(myButton)
AddHandler myButton.click, AddressOf myButtonClick
Next
End Sub
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, Button).Text)
End Sub
End Class
總結
動態生成子控制大致上就像上述那樣，這裡提醒的是，如果每個 Button 實際上做的動態差很多，可以考慮把 myButton 宣告成陣列型態(這不是必然，不是用陣列去做時到時 mapping 會比較清楚、簡單)，接著再手動去寫一份 map，至於 Rename 部份仍可以在建立實際 Button 時進行。最後結束前，再給最後一個範例，說明建立四個 button，名稱差很多，處理的東西也差很多，寫出來會是怎樣。
Public Class Form
' 動態生成按扭
Private Sub SetButtonName(ByRef btnName() As String, ByVal ParamArray Name() As String)
For i As Int32 = 0 To UBound(btnName) - 1
btnName(i) = Name(i)
Next i
End Sub
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim BtnName() As String
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
Dim myButton As Button
ReDim BtnName(cnt)
SetButtonName(BtnName, "OK", "ERROR", "WARNNING", "RETRY")
' 動態生成 button
For i = 0 To cnt - 1
myButton = New Button()
myButton.Text = BtnName(i)
myButton.Name = "btn_" & i ' 注意，這裡視為是 button ID, 不是 button text
myButton.Left = x0
myButton.Top = y0 + i * (h + hd)
myButton.Width = w
myButton.Height = h
Me.Controls.Add(myButton)
AddHandler myButton.Click, AddressOf myButtonClick
Next
End Sub
Dim btnIndex As Int32
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
' 取得是由哪個 button ID 發出來的訊息
btnIndex = Val(CType(sender, Button).Name.Split("_")(1))
' 根據發出來的 button ID 做相對應的 function map，這部份比較麻煩
Select Case btnIndex
Case 0
Func0()
Case 1
Func1()
Case 2
Func2()
Case 3
Func3()
End Select
End Sub
Public Sub Func0()
MsgBox("func0")
End Sub
Public Sub Func1()
MsgBox("func1")
End Sub
Public Sub Func2()
MsgBox("func2")
End Sub
Public Sub Func3()
MsgBox("func3")
End Sub
Public Sub Func4()
MsgBox("func4")
End Sub
End Class
--
動態生成子控制不是問題，重點是怎麼把對應的函式自動加進去。
範例一 - 動態生成 TextBox
Public Class Form
' 動態生成 TextBox
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim myTextBox As TextBox
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
' 動態生成 TextBox
For i = 0 To cnt - 1
myTextBox = New TextBox()
myTextBox.Text = "button" & i
myTextBox.Left = x0
myTextBox.Top = y0 + i * (h + hd)
myTextBox.Width = w
myTextBox.Height = h
Me.Controls.Add(myTextBox)
AddHandler myTextBox.Click, AddressOf myTextBoxClick ' 交附給函式
Next
End Sub
Private Sub myTextBoxClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, TextBox).Text)
End Sub
End Class
範例二 - 動態生成 Button (1)
初版的動態生成 Button 缺點不少，這裡是將 myButton 使用動態陣列，每個陣列名字都長得差不多，另外對應函式是用一個一個 map 起來的。原始碼如下
Public Class Form
' 動態生成按扭
Private myButton() As Button
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
ReDim myButton(cnt)
' 動態生成 button
For i = 0 To cnt - 1
myButton(i) = New Button()
myButton(i).Text = "button" & i
myButton(i).Left = x0
myButton(i).Top = y0 + i * (h + hd)
myButton(i).Width = w
myButton(i).Height = h
Me.Controls.Add(myButton(i))
Next
' 進行函式對應
AddHandler myButton(0).Click, AddressOf Func0
AddHandler myButton(1).Click, AddressOf Func1
AddHandler myButton(2).Click, AddressOf Func2
AddHandler myButton(3).Click, AddressOf Func3
End Sub
Sub Func0()
MsgBox("func0")
End Sub
Sub Func1()
MsgBox("func1")
End Sub
Sub Func2()
MsgBox("func2")
End Sub
Sub Func3()
MsgBox("func3")
End Sub
End Class
範例三 - 動態生成 Button (2)
第二版動態生成按鈕改善了一點點，由於這幾顆按鈕做的事都一樣，所以便用委派方式給同一個函式執行，但實際上效果並不彰。
Public Class Form
' 動態生成按扭
Private myButton() As Button
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
ReDim myButton(cnt)
' 動態生成 button
For i = 0 To cnt - 1
myButton(i) = New Button()
myButton(i).Text = "button" & i
myButton(i).Left = x0
myButton(i).Top = y0 + i * (h + hd)
myButton(i).Width = w
myButton(i).Height = h
Me.Controls.Add(myButton(i))
AddHandler myButton(i).Click, AddressOf myButtonClick
Next
End Sub
' 委派函式
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, Button).Text)
End Sub
End Class
範例四 - 動態生成 Button (3)
第三版動態生成按鈕改善了二個部份，我們可以借由自己寫的函式，把 Button Name 一次全換掉；除此之外，事實上 myButton 可以不用 array 方式建立，同時也可以不用宣告到全域。
注意的是那個 SetButtonName，它的引數記得是放 string，不要直接傳 Button 實體進去，傳 Button 實體進去的話，做法就要等 Button 全都建完之後才可以去呼叫 SetButtonName，效率不彰。
Public Class Form
' 動態生成按扭
' 設定按鈕名稱
Private Sub SetButtonName(ByRef btnName() as String, ByVal ParamArray Name() As String)
For i As Int32 = 0 To UBound(btnName) - 1
btnName(i) = Name(i)
Next i
End Sub
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim btnName() As String
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
Dim myButton As Button
ReDim btnName(cnt)
SetButtonName(btnName, "OK", "ERROR", "WARNNING", "RETRY")
' 動態生成 button
For i = 0 To cnt - 1
myButton = New Button()
myButton.Text = btnName(i)
myButton.Left = x0
myButton.Top = y0 + i * (h + hd)
myButton.Width = w
myButton.Height = h
Me.Controls.Add(myButton)
AddHandler myButton.click, AddressOf myButtonClick
Next
End Sub
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
System.Windows.Forms.MessageBox.Show(CType(sender, Button).Text)
End Sub
End Class
總結
動態生成子控制大致上就像上述那樣，這裡提醒的是，如果每個 Button 實際上做的動態差很多，可以考慮把 myButton 宣告成陣列型態(這不是必然，不是用陣列去做時到時 mapping 會比較清楚、簡單)，接著再手動去寫一份 map，至於 Rename 部份仍可以在建立實際 Button 時進行。最後結束前，再給最後一個範例，說明建立四個 button，名稱差很多，處理的東西也差很多，寫出來會是怎樣。
Public Class Form
' 動態生成按扭
Private Sub SetButtonName(ByRef btnName() As String, ByVal ParamArray Name() As String)
For i As Int32 = 0 To UBound(btnName) - 1
btnName(i) = Name(i)
Next i
End Sub
Private Sub Form_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
Dim BtnName() As String
' const declare
Const x0 As Int32 = 20
Const y0 As Int32 = 20
Const w As Int32 = 100
Const h As Int32 = 30
Const hd As Int32 = 10
Const cnt As Int32 = 4
Dim i As Int32 = 0
Dim myButton As Button
ReDim BtnName(cnt)
SetButtonName(BtnName, "OK", "ERROR", "WARNNING", "RETRY")
' 動態生成 button
For i = 0 To cnt - 1
myButton = New Button()
myButton.Text = BtnName(i)
myButton.Name = "btn_" & i ' 注意，這裡視為是 button ID, 不是 button text
myButton.Left = x0
myButton.Top = y0 + i * (h + hd)
myButton.Width = w
myButton.Height = h
Me.Controls.Add(myButton)
AddHandler myButton.Click, AddressOf myButtonClick
Next
End Sub
Dim btnIndex As Int32
Private Sub myButtonClick(ByVal sender As Object, ByVal e As System.EventArgs)
' 取得是由哪個 button ID 發出來的訊息
btnIndex = Val(CType(sender, Button).Name.Split("_")(1))
' 根據發出來的 button ID 做相對應的 function map，這部份比較麻煩
Select Case btnIndex
Case 0
Func0()
Case 1
Func1()
Case 2
Func2()
Case 3
Func3()
End Select
End Sub
Public Sub Func0()
MsgBox("func0")
End Sub
Public Sub Func1()
MsgBox("func1")
End Sub
Public Sub Func2()
MsgBox("func2")
End Sub
Public Sub Func3()
MsgBox("func3")
End Sub
Public Sub Func4()
MsgBox("func4")
End Sub
End Class
SQLite.NET connection strings
引用來源:SQLite.NET connection strings
--
Basic
"Data Source=c:\mydb.db;Version=3;"
Version 2 is not supported by this class library. SQLite
In-Memory Database
An SQLite database is normally stored on disk but the database can also be stored in memory. Read more about SQLite in-memory databases here.
"Data Source=:memory:;Version=3;New=True;"
SQLite
Using UTF16
"Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True;"
SQLite
With password
"Data Source=c:\mydb.db;Version=3;Password=myPassword; "
--
Basic
"Data Source=c:\mydb.db;Version=3;"
Version 2 is not supported by this class library. SQLite
In-Memory Database
An SQLite database is normally stored on disk but the database can also be stored in memory. Read more about SQLite in-memory databases here.
"Data Source=:memory:;Version=3;New=True;"
SQLite
Using UTF16
"Data Source=c:\mydb.db;Version=3;UseUTF16Encoding=True;"
SQLite
With password
"Data Source=c:\mydb.db;Version=3;Password=myPassword; "
2014年2月25日 星期二
SQLite 存/取圖片
google 中文字搜查還真沒有
查: .net SQLite picture <-- p="">---
C# & SQLite - Storing Images
Save and Load image SQLite C#
Blob image from SQLite to .NET property
Read / Write BLOBs from / to SQLite using C# .NET DataReader <==較棒唷!
查: .net SQLite picture <-- p="">---
C# & SQLite - Storing Images
Save and Load image SQLite C#
Blob image from SQLite to .NET property
Read / Write BLOBs from / to SQLite using C# .NET DataReader <==較棒唷!
SQLite 指令操作
參考引用來源
--
概述
SQLite介紹
自幾十年前出現的商業應用程式以來，資料庫就成為軟體應用程式的主要組成部分。正與資料庫管理系統非常關鍵一樣，它們也變得非常龐大，並佔用了相當多的系統資源，增加了管理的複雜性。隨著軟體應用程式逐漸模組模組化，一種新型資料庫會比大型複雜的傳統資料庫管理系統更適應。嵌入式資料庫直接在應用程式進程中運行，提供了零配置（zero-configuration）運行模式，並且資源佔用非常少。
SQLite是一個開源的嵌入式關係資料庫，它在2000年由D. Richard Hipp發佈，它的減少應用程式管理資料的開銷，SQLite可攜性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的應用程式中，它們共用相同的進程空間，而不是單獨的一個進程。從外部看，它並不像一個RDBMS，但在進程內部，它卻是完整的，自包含的資料庫引擎。
嵌入式資料庫的一大好處就是在你的程式內部不需要網路設定，也不需要管理。因為用戶端和伺服器在同一進程空間運行。SQLite 的資料庫許可權只依賴于檔案系統，沒有使用者帳戶的概念。SQLite 有資料庫級鎖定，沒有網路伺服器。它需要的記憶體，其它開銷很小，適合用於嵌入式設備。你需要做的僅僅是把它正確的編譯到你的程式。
架構(architecture)
SQLite採用了模組的設計，它由三個子系統，包括8個獨立的模組構成。
介面(Interface)
介面由SQLite C API組成，也就是說不管是程式、指令碼語言還是庫檔，最終都是通過它與SQLite交互的(我們通常用得較多的ODBC/JDBC最後也會轉化為相應C API的調用)。
編譯器(Compiler)
在編譯器中，分詞器（Tokenizer）和分析器(Parser)對SQL進行語法檢查，然後把它轉化為底層能更方便處理的階層式資料結構---語法樹，然後把語法樹傳給代碼產生器(code generator)進行處理。而代碼產生器根據它生成一種針對SQLite的彙編代碼，最後由虛擬機器(Virtual Machine)執行。
虛擬機器(Virtual Machine)
架構中最核心的部分是虛擬機器，或者叫做虛擬資料庫引擎(Virtual Database Engine,VDBE)。它和JAVA虛擬機器相似，解釋執行位元組代碼。VDBE的位元組代碼由128個操作碼(opcodes)構成，它們主要集中在資料庫操作。它的每一條指令都用來完成特定的資料庫操作(比如打開一個表的游標)或者為這些操作棧空間的準備(比如壓入參數)。總之，所有的這些指令都是為了滿足SQL命令的要求(關於VM，後面會做詳細介紹)。
後端(Back-End)
後端由B-樹(B-tree)，頁緩存(page cache，pager)和作業系統介面(即系統調用)構成。B-tree和page cache共同對資料進行管理。B-tree的主要功能就是索引，它維護著各個頁面之間的複雜的關係，便於快速找到所需資料。而pager的主要作用就是通過OS介面在B-tree和Disk之間傳遞頁面。
SQLite的特點(SQLite’s Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability)：
它是運行在Windows,Linux,BSD,Mac OS X和一些商用Unix系統，比如Sun的Solaris,IBM的AIX，同樣，它也可以工作在許多嵌入式作業系統下，比如QNX,VxWorks,Palm OS, Symbin和Windows CE。
Compactness：
SQLite是被設計成羽量級，自包含的。one header file, one library, and you’re relational, no external database server required
簡單(Simplicity)
靈活(Flexibility)
可靠(Reliability)：
SQLite的核心大約有3萬行標準C代碼，這些代碼都是模組化的，很容易閱讀。
事務(Transaction)
事務的週期(Transaction Lifecycles)
程式與事務之間有兩件事值得注意：
A、哪些物件在事務下運行——這直接與API有關。
B、事務的生命週期，即什麼時候開始，什麼時候結束以及它在什麼時候開始影響別的連接（這點對於併發性很重要）——這涉及到SQLite的具體實現。
一個連接（connection）可以包含多個(statement)，而且每個連接有一個與資料庫關聯的B-tree和一個pager。Pager在連接中起著很重要的作用，因為它管理事務、鎖、記憶體緩存以及負責崩潰恢復(crash recovery)。當你進行資料庫寫操作時，記住最重要的一件事：在任何時候，只在一個事務下執行一個連接。這些回答了第一個問題。
一般來說，一個事務的生命和statement差不多，你也可以手動結束它。預設情況下，事務自動提交，當然你也可以通過BEGIN..COMMIT手動提交。接下來就是鎖的問題。
關於這個圖有以下幾點值得注意：
A、一個事務可以在UNLOCKED，RESERVED或EXCLUSIVE三種狀態下開始。預設情況下在UNLOCKED時開始。
B、白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一個資料庫的同一時存在。
C、從灰色的PENDING開始，事情就變得嚴格起來，意味著事務想得到排斥鎖(EXCLUSIVE)（注意與白色框中的區別）。
雖然鎖有這麼多狀態，但是從體質上來說，只有兩種情況：讀事務和寫事務。
讀者可以從HTTP://www.sqlite.org/下載SQLite 最新的版本
Cmd 進入命令列
創建資料庫檔案:
>SQLite3 d:\test.db 回車
就生成了一個test.db在d盤。
這樣同時也SQLite3掛上了這個test.db
用.help可以看看有什麼命令
>.help 回車即可
看看有創建了多少表
>.tables
看表結構
>.schema 表名
看看目前掛的資料庫
>.database
如果要把查詢輸出到檔
>.output 檔案名
> 查詢語句；
把查詢結果用螢幕輸出
>.output stdout
把表結構輸出，同時索引也會輸出
> .dump 表名
退出
>.exit 或者.quit
從HTTP://sqlite.phxsoftware.com/下載Ado.net驅動。
下載了安裝，在安裝目錄中存在System.Data.SQLite.dll
我們只需要拷貝這個檔到引用目錄，並增加參考即可對SQLite資料庫操作了
所有的Ado.net物件都是以SQLite開頭的，比如SQLiteConnection
連接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--應用在和應用程式或者.net能夠自動找到的目錄
剩下的就很簡單了~~
SQL語法
由於以前用SQLServer或者ISeries，所以DDL的語法很汗顏
創建一個單個Primary Key的table
CREATE TABLE [Admin] (
[UserName] [Nvarchar] (20) PRIMARY KEY NOT Null ,
[Password] [Nvarchar] (50) NOT Null ,
[Rank] [Smallint] NOT Null ,
[MailServer] [Nvarchar] (50) NOT Null ,
[MailUser] [Nvarchar] (50) NOT Null ,
[MailPassword] [Nvarchar] (50) NOT Null ,
[Mail] [Nvarchar] (50) NOT Null
) ;
創建一個多個Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [Nvarchar] (10) NOT Null ,
[CdCode] [Nvarchar] (20) NOT Null ,
[CdString1] [Ntext] NOT Null ,
[CdString2] [Ntext] NOT Null ,
[CdString3] [Ntext] NOT Null,
PRIMARY KEY (CdType,CdCode)
) ;
創建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
還可以視圖等等。
SQLite 分頁查詢
寫法1:
SELECT * FROM TABLE1 LIMIT 20 OFFSET 20 ;
寫法2:
SELECT * FROM TABLE1 LIMIT 20 , 20;
SQLite 檔的壓縮
在多次刪除資料、插入資料、更新資料後，資料庫體積增大，但實際有效資料量很小，則需要對資料庫進行壓縮、整理，把已經刪除的資料從物理檔中移除。調用一下SQL命令即可：
VACUUM
VACUUM的實現
資料插入與更新
使用REPLACE替代INSERT、UPDATE命令。在無滿足條件記錄，則執行Insert，有滿足條件記錄，則執行UPDATE。
1 REPLACE INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
Insert or Replace Into 和Replace Into 的效果是一樣的上面這句話也可以這樣寫
1 Insert or Replace INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
字元編碼轉換
sqlite3的源碼中，提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8個函數進行字元在不同編碼間的轉換，但未在sqlite3.def、sqlite3.h檔中列出，即未對外公開。這些函數中，都使用了MultiByteToWideChar()、WideCharToMultiByte()兩個函數實現字元間轉換。
開發示例
001 using System;
002 using System.Data;
003 using System.Data.SQLite;
004 using System.Collections.Generic;
005 using System.IO;
006
007 namespace DataHelper
008 {
009 public class SqLiteHelper
010 {
011 ///
012 /// ConnectionString樣例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
013 ///
014 public static string ConnectionString
015 {
016 get
017 {
018 return @"Data source= "+DataBasePath+";";
019 }
020 set { throw new NotImplementedException(); }
021 }
022
023 public static string DataBasePath
024 {
025 get { return "SpringYang.db";};
026 }
027
028 private static object lockObject = new object();
029
030 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, List
031 {
032 if (conn.State != ConnectionState.Open)
033 conn.Open();
034 cmd.Parameters.Clear();
035 cmd.Connection = conn;
036 cmd.CommandText = cmdText;
037 cmd.CommandType = CommandType.Text;
038 cmd.CommandTimeout = 30;
039 foreach (var parameter in parameters)
040 {
041 cmd.Parameters.Add(parameter);
042 }
043 }
044
045 public static DataSet ExecuteQuery(string cmdText, List
046 {
047 lock (lockObject)
048 {
049 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
050 {
051 using (SQLiteCommand command = new SQLiteCommand())
052 {
053 DataSet ds = new DataSet();
054 PrepareCommand(command, conn, cmdText, parameters);
055 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
056 da.Fill(ds);
057 return ds;
058 }
059 }
060 }
061 }
062
063 public static int ExecuteNonQuery(string cmdText, List
064 {
065 lock (lockObject)
066 {
067 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
068 {
069 using (SQLiteCommand command = new SQLiteCommand())
070 {
071
072 PrepareCommand(command, conn, cmdText, parameters);
073 return command.ExecuteNonQuery();
074 }
075 }
076 }
077 }
078
079 public static SQLiteDataReader ExecuteReader(string cmdText, List
080 {
081 lock (lockObject)
082 {
083 SQLiteConnection conn = new SQLiteConnection(ConnectionString);
084
085 SQLiteCommand command = new SQLiteCommand();
086
087 PrepareCommand(command, conn, cmdText, parameters);
088 SQLiteDataReader sqLiteDataReader = command.ExecuteReader();
089 return sqLiteDataReader;
090 }
091 }
092
093 public static object ExecuteScalar(string cmdText, List
094 {
095 lock (lockObject)
096 {
097 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
098 {
099 using (SQLiteCommand command = new SQLiteCommand())
100 {
101 PrepareCommand(command, conn, cmdText, parameters);
102 return command.ExecuteScalar();
103 }
104 }
105 }
106 }
107 public static void CreateDataBase()
108 {
109 if (!File.Exists(DataBasePath))
110 SQLiteConnection.CreateFile(DataBasePath);
111 CreateTable();
112 }
113
114
115 public static void CreateTable()
116 {
117 ExecuteNonQuery(CodeDetailTabale, null);
118 }
119
120
121 private static string CodeDetailTabale
122 {
123 get
124 {
125 return @"CREATE TABLE [CodeDetail] (
126 [CdType] [Nvarchar] (10) NOT Null ,
127 [CdCode] [Nvarchar] (20) NOT Null ,
128 [CdString1] [Ntext] NOT Null ,
129 [CdString2] [Ntext] NOT Null ,
130 [CdString3] [Ntext] NOT Null,
131 PRIMARY KEY (CdType,CdCode)
132 ) ;";
133 }
134 }
135 }
136 }
示例講解
A、使用到自己定義的鎖private static object lockObject = new object();
B、使用完連接後都進行關閉操作。使用了using
C、創建資料庫命令：SQLiteConnection.CreateFile(DataBasePath);
最後再講解個Insert or Replace into的經典用法
1 Insert or Replace INTO User(ID, Name,Age) Select old.ID,new.Name,new.Age From
2 (select 'Spring Yang' as Name, '25' as Age) as new left join (Select ID,Name from User where Name = 'Spring Yang' ) as old on old.Name = new.Name
2014年2月24日 星期一
2014年2月22日 星期六
padfone garmin 地圖更新
請參考:Ezmy PadFone2 Garmin 2013.20地圖更新及分享
--
我的是 padfone官網:PadFone
進去,點[支援] 就有地圖可下載了!
更新 方式很簡單,手機插上USB-->to PC 後
將壓縮檔解開並執行 .exe 檔(例:MapUpdater_2013.40_1212.exe)
程式就自動會更新地圖了
--
我的是 padfone官網:PadFone
進去,點[支援] 就有地圖可下載了!
更新 方式很簡單,手機插上USB-->to PC 後
將壓縮檔解開並執行 .exe 檔(例:MapUpdater_2013.40_1212.exe)
程式就自動會更新地圖了
2014年2月17日 星期一
mssql stored procedure call stored procedure
參考引用(1)
--
If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do:
Exec secondSPName @anyparams
Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP. Then you can get these values in first SP as:
Insert into @tep_table
Exec secondSPName @anyparams
Update:
To pass parameter to second sp, do this:
Declare @id ID_Column_datatype
Set @id=(Select id from table_1 Where yourconditions)
Exec secondSPName @id
Update 2:
Suppose your second sp returns Id and Name where type of id is int and name is of varchar(64) type.
now, if you want to select these values in first sp then create a temporary table variable and insert values into it:
Declare @tep_table table
(
Id int,
Name varchar(64)
)
Insert into @tep_table
Exec secondSP
Select * From @tep_table
This will return you the values returned by second SP.
--
Create Proc SP1
(
@ID int,
@Name varchar(40)
-- etc parameter list, If you don't have any parameter then no need to pass.
)
AS
BEGIN
-- Here we have some opereations
-- If there is any Error Before Executing SP2 then SP will stop executing.
Exec SP2 @ID,@Name,@SomeID OUTPUT /*傳入參數*/
--
If you only want to perform some specific operations by your second SP and do not require values back from the SP then simply do:
Exec secondSPName @anyparams
Else, if you need values returned by your second SP inside your first one, then create a temporary table variable with equal numbers of columns and with same definition of column return by second SP. Then you can get these values in first SP as:
Insert into @tep_table
Exec secondSPName @anyparams
Update:
To pass parameter to second sp, do this:
Declare @id ID_Column_datatype
Set @id=(Select id from table_1 Where yourconditions)
Exec secondSPName @id
Update 2:
Suppose your second sp returns Id and Name where type of id is int and name is of varchar(64) type.
now, if you want to select these values in first sp then create a temporary table variable and insert values into it:
Declare @tep_table table
(
Id int,
Name varchar(64)
)
Insert into @tep_table
Exec secondSP
Select * From @tep_table
This will return you the values returned by second SP.
--
Create Proc SP1
(
@ID int,
@Name varchar(40)
-- etc parameter list, If you don't have any parameter then no need to pass.
)
AS
BEGIN
-- Here we have some opereations
-- If there is any Error Before Executing SP2 then SP will stop executing.
Exec SP2 @ID,@Name,@SomeID OUTPUT /*傳入參數*/
2014年2月13日 星期四
mssql 判斷 Table 是否存在
IF EXISTS( SELECT * FROM sys.tables WHERE name = 'Table_Name' )
SELECT * FROM [Table_Name]
-- 暫存表，# 表示本地暫存，## 表示全局暫存
IF OBJECT_ID('tempdb.dbo.#Table_Name') IS NOT NULL
DROP TABLE [#Table_Name]
MS SQL 暫存表 temp table
參考來源:建立#TempTable與Declare @TempTable有何差別
--
MS SQL 暫存表 temp table
1.在tempdb中建立
I.區域-前置詞#-其他連線不可查詢此TABLE
II.全域-前置詞##-其他連線可查詢此TABLE
連線中斷後被清除
Create Table #NOM_tmp (
GPNo varchar(3),
DTNo Nvarchar(10),
GPNa Nvarchar(10),
DTNa Nvarchar(20))
2.在記憶體宣告建立
批次作業結束後清除
DECLARE @NOM_tmp TABLE (
GPNo varchar(3),
DTNo Nvarchar(10),
GPNa Nvarchar(10),
DTNa Nvarchar(20))
3.檢查暫存表是否存在
IF object_id('tempdb..#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp
END
--
MS SQL 暫存表 temp table
1.在tempdb中建立
I.區域-前置詞#-其他連線不可查詢此TABLE
II.全域-前置詞##-其他連線可查詢此TABLE
連線中斷後被清除
Create Table #NOM_tmp (
GPNo varchar(3),
DTNo Nvarchar(10),
GPNa Nvarchar(10),
DTNa Nvarchar(20))
2.在記憶體宣告建立
批次作業結束後清除
DECLARE @NOM_tmp TABLE (
GPNo varchar(3),
DTNo Nvarchar(10),
GPNa Nvarchar(10),
DTNa Nvarchar(20))
3.檢查暫存表是否存在
IF object_id('tempdb..#tmp') IS NOT NULL
BEGIN
DROP TABLE #tmp
END
vb6 由外部往內繞繪線
參考引用來源
--
Option Explicit
Private Sub Form_Load()
Dim i As Integer, j As Integer, k As Integer
Dim x As Integer, y As Integer
Form1.ScaleMode = 3
Form1.AutoRedraw = True
x = Form1.ScaleWidth \ 2
y = Form1.ScaleHeight \ 2
j = 8
k = 8
For i = 0 To 5
Form1.Line (x, y)-(x - j, y)
x = x - j
j = j + k
Form1.Line (x, y)-(x, y + j)
y = y + j
j = j + k
Form1.Line (x, y)-(x + j, y)
x = x + j
j = j + k
Form1.Line (x, y)-(x, y - j)
y = y - j
j = j + k
Next i
End Sub
--
Option Explicit
Private Sub Form_Load()
Dim i As Integer, j As Integer, k As Integer
Dim x As Integer, y As Integer
Form1.ScaleMode = 3
Form1.AutoRedraw = True
x = Form1.ScaleWidth \ 2
y = Form1.ScaleHeight \ 2
j = 8
k = 8
For i = 0 To 5
Form1.Line (x, y)-(x - j, y)
x = x - j
j = j + k
Form1.Line (x, y)-(x, y + j)
y = y + j
j = j + k
Form1.Line (x, y)-(x + j, y)
x = x + j
j = j + k
Form1.Line (x, y)-(x, y - j)
y = y - j
j = j + k
Next i
End Sub
VB.Net打印设置打印预览和预览控制器的实例
參考引用來源:VB.Net打印设置打印预览和预览控制器的实例
--
代码如下：
Public Class PrintForm
Dim pageset As Printing.PageSettings = New Printing.PageSettings()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
PageSetupDialog1.Document = PrintDocument1 ''设置Document属性=文档对象或者与PageSettings类关联就可以使用该控件
PageSetupDialog1.AllowMargins = True
PageSetupDialog1.PageSettings = PrintDocument1.DefaultPageSettings
Dim re As DialogResult = PageSetupDialog1.ShowDialog()
If re = Windows.Forms.DialogResult.OK Then
pageset = PageSetupDialog1.PageSettings
End If
End Sub
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
Dim mypen As Pen = New Pen(Color.Blue, 2)
PrintDocument1.DefaultPageSettings = pageset
e.Graphics.DrawString(RichTextBox1.Text, New Font("黑体", 15), mypen.Brush, 10, 10)
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
PrintDialog1.Document = PrintDocument1
PrintDialog1.PrinterSettings.Copies = 5 ''打印的份数
PrintDialog1.PrinterSettings.FromPage = 1 ''第一页的页码
PrintDialog1.PrinterSettings.ToPage = 5 ''最后一页页码
Dim re As DialogResult = PrintDialog1.ShowDialog()
If re = Windows.Forms.DialogResult.OK Then
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
PrintDocument1.Print()
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
PrintPreviewDialog1.Document = PrintDocument1
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
PrintPreviewDialog1.PrintPreviewControl.Zoom = 1.0
PrintPreviewDialog1.WindowState = FormWindowState.Normal
PrintPreviewDialog1.ShowDialog()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim PrintPreviewControl1 As PrintPreviewControl = New PrintPreviewControl
PrintPreviewControl1.Name = "PrintPreviewControl1"
PrintPreviewControl1.Dock = DockStyle.Fill
PrintPreviewControl1.Document = PrintDocument1
PrintPreviewControl1.UseAntiAlias = True
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
Form1.Controls.Add(PrintPreviewControl1)
PrintPreviewControl1.Show()
Form1.Show()
End Sub
End Class
--
代码如下：
Public Class PrintForm
Dim pageset As Printing.PageSettings = New Printing.PageSettings()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
PageSetupDialog1.Document = PrintDocument1 ''设置Document属性=文档对象或者与PageSettings类关联就可以使用该控件
PageSetupDialog1.AllowMargins = True
PageSetupDialog1.PageSettings = PrintDocument1.DefaultPageSettings
Dim re As DialogResult = PageSetupDialog1.ShowDialog()
If re = Windows.Forms.DialogResult.OK Then
pageset = PageSetupDialog1.PageSettings
End If
End Sub
Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
Dim mypen As Pen = New Pen(Color.Blue, 2)
PrintDocument1.DefaultPageSettings = pageset
e.Graphics.DrawString(RichTextBox1.Text, New Font("黑体", 15), mypen.Brush, 10, 10)
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
PrintDialog1.Document = PrintDocument1
PrintDialog1.PrinterSettings.Copies = 5 ''打印的份数
PrintDialog1.PrinterSettings.FromPage = 1 ''第一页的页码
PrintDialog1.PrinterSettings.ToPage = 5 ''最后一页页码
Dim re As DialogResult = PrintDialog1.ShowDialog()
If re = Windows.Forms.DialogResult.OK Then
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
PrintDocument1.Print()
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
PrintPreviewDialog1.Document = PrintDocument1
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
PrintPreviewDialog1.PrintPreviewControl.Zoom = 1.0
PrintPreviewDialog1.WindowState = FormWindowState.Normal
PrintPreviewDialog1.ShowDialog()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim PrintPreviewControl1 As PrintPreviewControl = New PrintPreviewControl
PrintPreviewControl1.Name = "PrintPreviewControl1"
PrintPreviewControl1.Dock = DockStyle.Fill
PrintPreviewControl1.Document = PrintDocument1
PrintPreviewControl1.UseAntiAlias = True
AddHandler PrintDocument1.PrintPage, AddressOf Me.PrintDocument1_PrintPage
Form1.Controls.Add(PrintPreviewControl1)
PrintPreviewControl1.Show()
Form1.Show()
End Sub
End Class
2014年2月12日 星期三
mssql group by having 運用
mssql group by having 運用
select bk_bar from book group by bk_bar having count(bk_bar )>1
/*相同條碼>1*/
mssql case when else
參考來源:SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported
--
case when tel1='' or tel1 IS NULL then mob else tel1 end as tel
當tel1 空值或null , 使用手機取代值
--
case when tel1='' or tel1 IS NULL then mob else tel1 end as tel
當tel1 空值或null , 使用手機取代值
2014年2月10日 星期一
如何讓 SELECT 查詢結果額外增加自動遞增序號
引用來源:如何讓 SELECT 查詢結果額外增加自動遞增序號
--
如果資料表本身並不內含自動地增編號的欄位時，要怎麼作，才能夠讓 SELECT 查詢結果如圖表 1 所示地額外增加自動遞增序號呢？我們提供下列五種方法供您參考：
USE 北風貿易;
GO
/* 方法一*/
SELECT 序號= (SELECT COUNT(客戶編號) FROM 客戶 AS LiMing
WHERE LiMing.客戶編號<= Chang.客戶編號),
客戶編號, 公司名稱
FROM 客戶 AS Chang ORDER BY 1;
GO
/* 方法二: 使用SQL Server 2005 獨有的RANK() OVER () 語法*/
SELECT RANK() OVER (ORDER BY 客戶編號 DESC) AS 序號,
客戶編號, 公司名稱
FROM 客戶;
GO
/* 方法三*/
SELECT 序號= COUNT(*), LiMing.客戶編號, LiMing.公司名稱
FROM 客戶 AS LiMing, 客戶AS Chang
WHERE LiMing.客戶編號>= Chang.客戶編號
GROUP BY LiMing.客戶編號, LiMing.公司名稱
ORDER BY 序號;
GO
/* 方法四
建立一個「自動編號」的欄位，然後將資料新增至一個區域性暫存資料表，
然後由該區域性暫存資料表中，將資料選取出來，最後刪除該區域性暫存資料表
*/
SELECT 序號= IDENTITY(INT,1,1), 管道, 程式語言, 講師, 資歷
INTO #LiMing
FROM 問券調查一;
GO
SELECT * FROM #LiMing;
GO
DROP TABLE #LiMing;
GO
/*
方法五
使用 SQL Server 2005 獨有的ROW_NUMBER() OVER () 語法
搭配 CTE （一般資料表運算式，就是 WITH 那段語法）選取序號2 ~ 4 的資料
*/
WITH 排序後的圖書 AS
(SELECT ROW_NUMBER() OVER (ORDER BY 客戶編號 DESC) AS 序號,
客戶編號, 公司名稱
FROM 客戶)
SELECT * FROM 排序後的圖書
WHERE 序號 BETWEEN 2 AND 4;
GO
--
如果資料表本身並不內含自動地增編號的欄位時，要怎麼作，才能夠讓 SELECT 查詢結果如圖表 1 所示地額外增加自動遞增序號呢？我們提供下列五種方法供您參考：
USE 北風貿易;
GO
/* 方法一*/
SELECT 序號= (SELECT COUNT(客戶編號) FROM 客戶 AS LiMing
WHERE LiMing.客戶編號<= Chang.客戶編號),
客戶編號, 公司名稱
FROM 客戶 AS Chang ORDER BY 1;
GO
/* 方法二: 使用SQL Server 2005 獨有的RANK() OVER () 語法*/
SELECT RANK() OVER (ORDER BY 客戶編號 DESC) AS 序號,
客戶編號, 公司名稱
FROM 客戶;
GO
/* 方法三*/
SELECT 序號= COUNT(*), LiMing.客戶編號, LiMing.公司名稱
FROM 客戶 AS LiMing, 客戶AS Chang
WHERE LiMing.客戶編號>= Chang.客戶編號
GROUP BY LiMing.客戶編號, LiMing.公司名稱
ORDER BY 序號;
GO
/* 方法四
建立一個「自動編號」的欄位，然後將資料新增至一個區域性暫存資料表，
然後由該區域性暫存資料表中，將資料選取出來，最後刪除該區域性暫存資料表
*/
SELECT 序號= IDENTITY(INT,1,1), 管道, 程式語言, 講師, 資歷
INTO #LiMing
FROM 問券調查一;
GO
SELECT * FROM #LiMing;
GO
DROP TABLE #LiMing;
GO
/*
方法五
使用 SQL Server 2005 獨有的ROW_NUMBER() OVER () 語法
搭配 CTE （一般資料表運算式，就是 WITH 那段語法）選取序號2 ~ 4 的資料
*/
WITH 排序後的圖書 AS
(SELECT ROW_NUMBER() OVER (ORDER BY 客戶編號 DESC) AS 序號,
客戶編號, 公司名稱
FROM 客戶)
SELECT * FROM 排序後的圖書
WHERE 序號 BETWEEN 2 AND 4;
GO
2014年2月6日 星期四
2014年2月4日 星期二
讀取 xsd (XML)
(部分引用)請參考來源:[C#.NET][XML] XSD Convert XML
--
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml("Employee.xsd");
foreach (DataTable table in ds.Tables)
{
Debug.WriteLine(table.TableName);
}
DataTable t = new DataTable("EmpInfo");
t.ReadXml("Employee.xsd");
}
--
private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml("Employee.xsd");
foreach (DataTable table in ds.Tables)
{
Debug.WriteLine(table.TableName);
}
DataTable t = new DataTable("EmpInfo");
t.ReadXml("Employee.xsd");
}
訂閱：
文章 (Atom)