Connect my project to sql server

[joharasad]

Hi i m new user in vb.net so i want plz u people help mee, i just want to conect my project to sql server database.

[MeSampath]

Hi,

Connecting the database to your project is pretty simple. some basic ideas here;
1) import "System.Data.SqlClient" namespace
2) Create SqlConnection object to establish connection with the database. connectionstring (which contains the server,user,db credentials) is required.
3) Use SqlCommand object to play with your database tables.

Just have a try and post again if you encounter any errors.

Good luck.

[sknake]

Here is a code example what MeSampath suggested:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    'Read an image
    Using conn As New System.Data.SqlClient.SqlConnection("Data Source=apex2006sql;Initial Catalog=Scott;Integrated Security=True;")
      conn.Open()
      Using cmd As New SqlClient.SqlCommand("Select Top 1 Name, CreateDate, Picture From Picture", conn)
        Using dr As SqlClient.SqlDataReader = cmd.ExecuteReader()
          Using dt As New DataTable
            dt.Load(dr)
            Dim row As DataRow = dt.Rows(0)
            Dim sName As String = Convert.ToString(row("Name"))
            Dim dtCreateDate As DateTime = Convert.ToDateTime(row("CreateDate"))
            Using ms As New IO.MemoryStream(CType(row("Picture"), Byte()))
              Dim img As Image = Image.FromStream(ms)
              PictureBox1.Image = img
            End Using
            MessageBox.Show("Loaded image " + sName)
          End Using
        End Using
      End Using
    End Using
  End Sub

[TomW]

MSDN Database Walkthroughs

[joharasad]

thanx to reply me, im new in SQL so i coudnot undersatnd how to creat a tebel in sql then this table conect to my vb.net project.plz help to creat atbel in sql.

[TomW]

There is so much to learn to working with databases and programming that I would suggest starting with a book to get a full overview. Ive been thru many books over the years and there are definitely more recent books published but one book in particular that is my favorite and specific to database programming is "Pro Ado.Net 2.0" there is also an electronic version of this book available for purchase and download on Amazon. I would highly suggest this as a starting point.

[jlego]

if youre using mysql, this is what i do:

1. add adodb reference to your project
(download the mysql connector 5.0 from mysql.com and setup in your system odbc connections)

2. create a module, and put the following

module globdata
private adoconnect as new adodb.connection
public adors as new adodb.recordset

public function db_connect(strQuery as string) as boolean
            GlobData.ADOConnect.Open(My.Settings.DSN, My.Settings.DBUser, My.Settings.DBPass)
            GlobData.ADORS.Open(strQuery$, ADOConnect)
            If GlobData.ADORS.EOF = False Then Return True
end function

    Public Sub DB_Disconnect()
        If GlobData.ADOConnect.State = ConnectionState.Open Then
            GlobData.ADOConnect.Close()
        End If
    End Sub

public sub DB_Update(strQuery as string)
            Dim ADOCmd As New ADODB.Command

            GlobData.ADOConnect.Open(My.Settings.DSN, My.Settings.DBUser, My.Settings.DBPass)
            ADOCmd.ActiveConnection = GlobData.ADOConnect
            ADOCmd.CommandText = strQuery$
            ADOCmd.Execute()
            ADOConnect.Close()
            ADOCmd = Nothing
end sub

end module

then call it in your project like

if globdata.db_connect("SELECT * FROM customer") = true then 
do until globdata.adors.eof = true
me.textbox.text = me.textbox.text & " " & globdata.adors.fields("Name").value
globdata.adors.movenext
loop
end if
call globdata.db_disconnect

or

call globdata.db_update("insert into customer (Name, Phone) VALUES ('name', 'phone')")

[sknake]

call globdata.db_update("insert into customer (Name, Phone) VALUES ('name', 'phone')")

You never want to insert values directly in to the command text like that. Use parameterized queries.

[jlego]

please explain why

[jhpajiri]

'You must

import the System.Data.sqlClient
'Import this in General Declaration
Imports System.Data.sqlClient

'Declare this in your Form Declaration

Dim conn as sqlConnection

'In your Form Load type this.
Try

conn=new sqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=databaseName;Integrated Security=True")
conn.open()
msgbox("Connection Established.")
Catch ex as Exception
msgbox(ex.Message)
End Try

[jhpajiri]

Note:

Google is our Friend.

[sknake]

please explain why

There are a lot of reasons

1) Security. When you manually build queries with values in the command text you open your application up for SQL Injection. If you have a query:

Insert Into tbl (Col1, Col2) Values ('a', 'b')

If the values of "a" or "b" are taken directly from controls then a user could enter a single ' and break the query. They can also enter raw SQL that will be executed on the server and there are a number of things they can do to "hack" your database.

2) Performance. Lets use the same query as above:

Insert Into tbl (Col1, Col2) Values ('a', 'b')

Now lets change it slightly:

Insert Into tbl (Col1, Col2) Values ('a', 'c')

Notice I changed the values from a/b to a/c? That changes the checksum of the INSERT query. Microsoft SQL Server calculates an execution plan for everything you run to determine what indexes etc are optimal for the operation. It then caches the execution plan after you run the query so if it sees the exact same query again before the cache record expires then it doesn't have to recalculate the execution plan, it looks it up from cache. Here is a better example:

Select *
From Customers
Where CustomerId > 12345 and CustomerId > 54321

Lets say the CustomerId has a clustered index and a unique nonclustered index on the "CustomerId" field. SQL Server would look at the two indexes then determine that the clustered index is a better fit. That causes more overhead on the server. If you change either of the customer IDs then the SQL Server has to undergo calculating the execution plan again.

Now when you use parameterized SQL you don't incur the second calculation cost penalty because you would be re-using the same query. Having the values set outside of the query means the checksum for the query stays the same. Here is how the raw parameterized SQL looks like to the server when executed:

exec sp_executesql N'Select * From Customer Where CustNumber = @CustNumber',N'@CustNumber int',@CustNumber=1000

Notice the value is set at the end of the query and the text inside of '' stays the same? Now you benefit from caching.