VB 2010 Express & SQL Express
Появилась необходимость работы с SQL. До сих пор с этим не сталкивался. В майкрософтовских примерах есть пример работы с SQL. Есть подключение, создание БД, создание таблиц... А вот дальше почему-то ошибки. Не могу понять что ему не хватает. Может у кого есть примеры как выполняются недостающие мне операции, а именно: Добавление, изменение, удаление записей (вероятно для удаления ещё и их поиск по определённому значению). И было бы СОВСЕМ ЧУДЕСНО, если найдётся пример сохранения изображений в SQL с сопоставлением этих изображений определённым записям в БД.
Заранее благодарен!
Ну и остаётся вопрос по изменению и удалению записей.
Разобрался и со считыванием изображения. Осталось немного :)
Выкладываю для тех, кто также как я безуспешно ищет простой ответ на конкретный вопрос!
Исходник не могу выложить, тут какие-то ограничения по объёму сделали. Выложу код.
В примере можно увидеть (правда ни одного комментария внутри) как производится подключение к SQL Server, как создаётся БД, таблица, как добавляются/изменяются записи. Как записать/считать изображение.
Чтобы код такой какой есть работал, нужно создать:
- 4 PictureBox с именами "P1", "P2", "P3", "P4"
- 3 Button с именами "bWrite", "bFind", "bChange"
- 6 TextBox с именами "TID", "TFIO", "TNUM", "T", "T1", "T2"
С именами не заморачивался)
Думаю кому надо, разберётся.
Здравствуйте, мне бы очень помог ваш код. Выложите, плиз))
Код:
Imports System.Data.SqlClient
Imports System.Data
Public Class frmMain
Protected Const SqlConnectionString As String = _
"Server=COMPNAME\SQLEXPRESS;" & _
"DataBase=;" & _
"User ID=sa;Password=PASS"
'Integrated Security=SSPI
Protected Const ConnectionErrorMessage As String = _
"To run this sample, you must have SQL " & _
"installed. For " & _
"instructions on installing SQL, view the documentation file."
Protected didPreviouslyConnect As Boolean = False
Protected didCreateTable As Boolean = False
Protected connectionString As String = SqlConnectionString
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
CreateDatabase()
connectionString = "Server=COMPNAME\SQLEXPRESS;" & "DataBase=Test;" & "User ID=sa;Password=PASS"
CreateTable()
End Sub
#Region "Создание БД и таблицы"
Private Sub CreateDatabase()
Dim sqlStatement As String = _
"IF EXISTS (" & _
"SELECT * " & _
"FROM master..sysdatabases " & _
"WHERE Name = 'Test')" & vbCrLf & _
"DROP DATABASE Test" & vbCrLf & _
"CREATE DATABASE Test"
Try
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
Private Sub CreateTable()
Dim strSQL As String = _
"USE Test" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM Test.dbo.sysobjects " & _
"WHERE Name = 'MyTable' " & _
"AND TYPE = 'u')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP TABLE Test.dbo.MyTable" & vbCrLf & _
"END" & vbCrLf & _
"CREATE TABLE MyTable (" & _
"ID NVarChar(40) NOT NULL," & _
"Image1 Image NOT NULL," & _
"Image2 Image NOT NULL," & _
"Image3 Image NOT NULL," & _
"Image4 Image NOT NULL," & _
"FIO NVarChar(40) NOT NULL," & _
"Number NVarChar(40) NOT NULL" & ")"
Try
Dim dbConnection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(strSQL, dbConnection)
dbConnection.Open()
cmd.ExecuteNonQuery()
dbConnection.Close()
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
#End Region
#Region "Считывание информации"
Private Sub bFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bFind.Click
P1.Image = Nothing : P2.Image = Nothing : P3.Image = Nothing : P4.Image = Nothing
Try
Dim Connection As New SqlConnection(connectionString)
Dim cmd = New SqlCommand("SELECT * FROM MyTable WHERE ID='" & T.Text & "'", Connection)
Connection.Open()
Dim datareader As SqlDataReader = cmd.ExecuteReader
datareader.Read()
TID.Text = datareader.GetString(0)
TFIO.Text = datareader.GetString(5)
TNUM.Text = datareader.GetString(6)
Dim bLength As Long
Dim mStream As System.IO.Stream
bLength = datareader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue)
Dim bBuffer(bLength) As Byte
datareader.GetBytes(1, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P1.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(2, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(2, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P2.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(3, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(3, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P3.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(4, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(4, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P4.Image = Image.FromStream(mStream)
Connection.Dispose()
Catch sqlExc As InvalidOperationException
MsgBox("Запись не найдена", MsgBoxStyle.OkOnly, "SQL Find")
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
End Try
End Sub
#End Region
Private Sub bChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bChange.Click
Try
Dim sqlStatement As String = "UPDATE MyTable SET ID = '" & T2.Text & "' WHERE ID = '" & T1.Text & "'"
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
connection.Open()
cmd.ExecuteNonQuery()
connection.Dispose()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
Private Sub bWrite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bWrite.Click
Try
Dim sqlStatement As String = "INSERT INTO MyTable (ID, Image1, Image2, Image3, Image4, FIO, Number) VALUES (@ID, @Image1, @Image2, @Image3, @Image4, @FIO, @Number)"
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
Dim par As New SqlParameter("@ID", SqlDbType.Int)
par.Value = TID.Text
cmd.Parameters.Add(par)
par = New SqlParameter("@Image1", SqlDbType.Image)
Dim fStream As New System.IO.FileStream("Путь к изображению\1.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
Dim imageBytes(fStream.Length) As Byte
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image2", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\2.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image3", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\3.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image4", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\4.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@FIO", SqlDbType.NVarChar)
par.Value = TFIO.Text
cmd.Parameters.Add(par)
par = New SqlParameter("@Number", SqlDbType.NVarChar)
par.Value = TNUM.Text
cmd.Parameters.Add(par)
connection.Open()
cmd.ExecuteNonQuery()
connection.Dispose()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
End Class
Imports System.Data
Public Class frmMain
Protected Const SqlConnectionString As String = _
"Server=COMPNAME\SQLEXPRESS;" & _
"DataBase=;" & _
"User ID=sa;Password=PASS"
'Integrated Security=SSPI
Protected Const ConnectionErrorMessage As String = _
"To run this sample, you must have SQL " & _
"installed. For " & _
"instructions on installing SQL, view the documentation file."
Protected didPreviouslyConnect As Boolean = False
Protected didCreateTable As Boolean = False
Protected connectionString As String = SqlConnectionString
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
CreateDatabase()
connectionString = "Server=COMPNAME\SQLEXPRESS;" & "DataBase=Test;" & "User ID=sa;Password=PASS"
CreateTable()
End Sub
#Region "Создание БД и таблицы"
Private Sub CreateDatabase()
Dim sqlStatement As String = _
"IF EXISTS (" & _
"SELECT * " & _
"FROM master..sysdatabases " & _
"WHERE Name = 'Test')" & vbCrLf & _
"DROP DATABASE Test" & vbCrLf & _
"CREATE DATABASE Test"
Try
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
Private Sub CreateTable()
Dim strSQL As String = _
"USE Test" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM Test.dbo.sysobjects " & _
"WHERE Name = 'MyTable' " & _
"AND TYPE = 'u')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP TABLE Test.dbo.MyTable" & vbCrLf & _
"END" & vbCrLf & _
"CREATE TABLE MyTable (" & _
"ID NVarChar(40) NOT NULL," & _
"Image1 Image NOT NULL," & _
"Image2 Image NOT NULL," & _
"Image3 Image NOT NULL," & _
"Image4 Image NOT NULL," & _
"FIO NVarChar(40) NOT NULL," & _
"Number NVarChar(40) NOT NULL" & ")"
Try
Dim dbConnection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(strSQL, dbConnection)
dbConnection.Open()
cmd.ExecuteNonQuery()
dbConnection.Close()
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
#End Region
#Region "Считывание информации"
Private Sub bFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bFind.Click
P1.Image = Nothing : P2.Image = Nothing : P3.Image = Nothing : P4.Image = Nothing
Try
Dim Connection As New SqlConnection(connectionString)
Dim cmd = New SqlCommand("SELECT * FROM MyTable WHERE ID='" & T.Text & "'", Connection)
Connection.Open()
Dim datareader As SqlDataReader = cmd.ExecuteReader
datareader.Read()
TID.Text = datareader.GetString(0)
TFIO.Text = datareader.GetString(5)
TNUM.Text = datareader.GetString(6)
Dim bLength As Long
Dim mStream As System.IO.Stream
bLength = datareader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue)
Dim bBuffer(bLength) As Byte
datareader.GetBytes(1, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P1.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(2, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(2, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P2.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(3, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(3, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P3.Image = Image.FromStream(mStream)
bLength = datareader.GetBytes(4, 0, Nothing, 0, Integer.MaxValue)
ReDim bBuffer(bLength)
datareader.GetBytes(4, 0, bBuffer, 0, bLength)
mStream = New System.IO.MemoryStream(bBuffer)
P4.Image = Image.FromStream(mStream)
Connection.Dispose()
Catch sqlExc As InvalidOperationException
MsgBox("Запись не найдена", MsgBoxStyle.OkOnly, "SQL Find")
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
End Try
End Sub
#End Region
Private Sub bChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bChange.Click
Try
Dim sqlStatement As String = "UPDATE MyTable SET ID = '" & T2.Text & "' WHERE ID = '" & T1.Text & "'"
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
connection.Open()
cmd.ExecuteNonQuery()
connection.Dispose()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
Private Sub bWrite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bWrite.Click
Try
Dim sqlStatement As String = "INSERT INTO MyTable (ID, Image1, Image2, Image3, Image4, FIO, Number) VALUES (@ID, @Image1, @Image2, @Image3, @Image4, @FIO, @Number)"
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sqlStatement, connection)
Dim par As New SqlParameter("@ID", SqlDbType.Int)
par.Value = TID.Text
cmd.Parameters.Add(par)
par = New SqlParameter("@Image1", SqlDbType.Image)
Dim fStream As New System.IO.FileStream("Путь к изображению\1.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
Dim imageBytes(fStream.Length) As Byte
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image2", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\2.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image3", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\3.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@Image4", SqlDbType.Image)
fStream = New System.IO.FileStream("Путь к изображению\4.jpg", System.IO.FileMode.Open, System.IO.FileAccess.Read)
ReDim imageBytes(fStream.Length)
fStream.Read(imageBytes, 0, imageBytes.Length)
par.Value = imageBytes
cmd.Parameters.Add(par)
par = New SqlParameter("@FIO", SqlDbType.NVarChar)
par.Value = TFIO.Text
cmd.Parameters.Add(par)
par = New SqlParameter("@Number", SqlDbType.NVarChar)
par.Value = TNUM.Text
cmd.Parameters.Add(par)
connection.Open()
cmd.ExecuteNonQuery()
connection.Dispose()
Catch sqlExc As SqlException
MsgBox(sqlExc.Message, MsgBoxStyle.OkOnly, "SQL Exception Error")
Catch exc As Exception
MsgBox(exc.Message, MsgBoxStyle.OkOnly, "Connection failed.")
End Try
End Sub
End Class
Обратите внимание, что при каждом запуске будет создаваться/очищаться БД. Можно вынести код на отдельный button.
Также обратите внимание на "COMPNAME" и "PASS" в параметрах подключения. Если замут с безопасностью нет и подключение производится к локальной БД, можно вообще без имени и пароля.