My. SQL Visual Basic tutorial. This is a Visual Basic tutorial for the My. In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with. SQL database. It covers the basics of. My. SQL programming with Visual Basic. In this tutorial, we use the. Connector/Net driver. This driver is based on the ADO. NET specification. The examples were created and tested on Ubuntu Linux. ![]() There is a similar. My. SQL C# tutorial. My. SQL Perl tutorial and. SQLite Visual Basic tutorial on Zet. Code. If you need to refresh your knowledge of the Visual Basic language, there is a full. The PHP switch Statement. Use the switch statement to select one of many blocks of code to be executed. Syntax. Prepared Statements and Bound Parameters. A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Visual Basic tutorial on Zet. Code. About My. SQL database. My. SQL is a leading open source database management system. It is a multi user. My. SQL is especially popular on the web. It is one of the parts of the very popular LAMP. Linux, Apache, My. SQL, and PHP. Currently My. SQL is owned by Oracle. My. SQL database is available on most important OS platforms. It runs on BSD Unix. Linux, Windows, or Mac OS. Wikipedia and You. Tube use My. SQL. These sites manage. My. SQL comes in two versions: My. SQL server system and My. SQL. embedded system. Before we start. We need to install several packages to execute the examples in this tutorial. We need to install Visual Basic compiler from the Mono project. Either from a package. The libmysql. 6. 1- cil is the My. SQL database connector for CLI. It is. written in C# and is available for all CLI languages. C#, Visual Basic. Boo and others. $ ls /usr/lib/cli/My. Sql. Data- 6. 1/My. Sql. Data. dll. /usr/lib/cli/My. Sql. Data- 6. 1/My. Sql. Data. dll. From the technical point of view, we need a DLL. On my system (Ubuntu Lucid Lynx), it. We need to know the path to the DLL library. To compile our examples. If you don't already have My. SQL installed, we must install it. This command installs the My. SQL server and various other packages. While installing the package, we are prompted to enter. My. SQL root account. Next, we are going to create a new database user and a new database. We use the mysql client. We check if the My. SQL server is running. If not, we need. to start the server. On Ubuntu Linux, this can be done. Enter password. Welcome to the My. SQL monitor. Commands end with ; or \g. Your My. SQL connection id is 3. Server version: 5. Ubuntu). Type 'help; ' or '\h' for help. Type '\c' to clear the buffer. SHOW DATABASES. +- -- -- -- -- -- -- -- -- -- -+. Database |. +- -- -- -- -- -- -- -- -- -- -+. We use the mysql monitor client application to connect to the. We connect to the database using the root account. We show all available. SHOW DATABASES statement. CREATE DATABASE testdb. Query OK, 1 row affected (0. We create a new testdb database. We will use this database throughout. CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test. Query OK, 0 rows affected (0. USE testdb. Database changed. GRANT ALL ON testdb.* TO 'testuser'@'localhost'. Query OK, 0 rows affected (0. We create a new database user. We grant all privileges to this user. Definitions. ADO. NET is an important part of the . NET framework. It is a specification. XML files and other application data. A My. SQL Connector/Net is an implementation of the ADO. NET specification. My. SQL database. It is a driver written in C# language and is available for. NET languages. The Connection, Command, Data. Reader. Data. Set, and Data. Provider are the core elements. NET data provider model. The Connection creates a connection to. The Command object executes an SQL statement. The Data. Reader reads streams of data from a. The Data. Set object is used for offline work with a mass. It is a disconnected data representation that can hold data from. Both Data. Reader and Data. Set. are used to work with data; they are used under different circumstances. If we only. need to read the results of a query, the Data. Reader is the better choice. If we need more extensive processing of data, or we want to bind a Winforms. Data. Set is preferred. If the following program runs OK, then we have everything. OK. We check the version of the My. SQL server. Option Strict On. Imports My. Sql. Data. My. Sql. Client. Dim cs As String = "Database=testdb; Data Source=localhost; " _. User Id=testuser; Password=test. Dim conn As New My. Sql. Connection(cs). Open(). Console. Write. Line("My. SQL version : {0}", conn. Server. Version). Catch ex As My. Sql. Exception. Console. Write. Line("Error: " & ex. To. String()). Finally. Close(). End Try. We connect to the database and get some info about the My. SQL server. Imports My. Sql. Data. My. Sql. Client. We import the elements of the My. SQL data provider. Dim cs As String = "Database=testdb; Data Source=localhost; " _. User Id=testuser; Password=test. This is the connection string. It is used by the data provider. We specify the database. Dim conn As New My. Sql. Connection(cs). A My. SQLConnection object is created. This object is used to. This line opens the connection. Console. Write. Line("My. SQL version : {0}", conn. Server. Version). Here we print the version of My. SQL using the Server. Version. property of the connection object. Catch ex As My. Sql. Exception. Console. Write. Line("Error: " & ex. To. String()). In case of an exception, we print the error message to the. My. Sql. Data- 6. My. Sql. Data. dll connect. We compile our example. A path to the My. SQL connector DLL. My. SQL version : 5. This is the output of the program on my system. A more complex program follows. Option Strict On. Imports My. Sql. Data. My. Sql. Client. Dim cs As String = "Database=testdb; Data Source=localhost; " _. User Id=testuser; Password=test. Dim stm As String = "SELECT VERSION()". Dim version As String. Dim conn As My. Sql. Connection. conn = New My. Sql. Connection(cs). Open(). Dim cmd As My. Sql. Command = New My. Sql. Command(stm, conn). Convert. To. String(cmd. Execute. Scalar()). Console. Write. Line("My. SQL version: {0}", version). Catch ex As My. Sql. Exception. Console. Write. Line("Error: " & ex. To. String()). Finally. Close(). End Try. We check for the version of the My. SQL database. This time using. Dim stm As String = "SELECT VERSION()". This is the SQL SELECT statement. It returns the version. The VERSION() is a built- in My. SQL function. Dim cmd As My. Sql. Command = New My. Sql. Command(stm, conn). The My. Sql. Command is an object, which is. The parameters are the. SQL statement and the connection object. Convert. To. String(cmd. Execute. Scalar()). There are queries which return only a scalar value. In our. case, we want a simple string specifying the version of the. The Execute. Scalar() is. We avoid the overhead of using more. My. SQL version : 5. Same result as in the previous example. Creating and populating tables. Next we are going to create database tables and fill them. These tables will be used throughout this tutorial. DROP TABLE IF EXISTS Books, Authors. CREATE TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT. Name VARCHAR(2. 5)) ENGINE=INNODB. INSERT INTO Authors(Id, Name) VALUES(1, 'Jack London'). INSERT INTO Authors(Id, Name) VALUES(2, 'Honore de Balzac'). INSERT INTO Authors(Id, Name) VALUES(3, 'Lion Feuchtwanger'). INSERT INTO Authors(Id, Name) VALUES(4, 'Emile Zola'). INSERT INTO Authors(Id, Name) VALUES(5, 'Truman Capote'). CREATE TABLE IF NOT EXISTS Books(Id INT PRIMARY KEY AUTO_INCREMENT. Author. Id INT, Title VARCHAR(1. FOREIGN KEY(Author. Id) REFERENCES Authors(Id) ON DELETE CASCADE). ENGINE=INNODB. INSERT INTO Books(Id, Author. Id, Title) VALUES(1, 1, 'Call of the Wild'). INSERT INTO Books(Id, Author. Id, Title) VALUES(2, 1, 'Martin Eden'). INSERT INTO Books(Id, Author. Id, Title) VALUES(3, 2, 'Old Goriot'). INSERT INTO Books(Id, Author. Id, Title) VALUES(4, 2, 'Cousin Bette'). INSERT INTO Books(Id, Author. Id, Title) VALUES(5, 3, 'Jew Suess'). INSERT INTO Books(Id, Author. Id, Title) VALUES(6, 4, 'Nana'). INSERT INTO Books(Id, Author. Id, Title) VALUES(7, 4, 'The Belly of Paris'). INSERT INTO Books(Id, Author. Id, Title) VALUES(8, 5, 'In Cold blood'). INSERT INTO Books(Id, Author. Id, Title) VALUES(9, 5, 'Breakfast at Tiffany'). We have a books. sql file. It creates two database tables: Authors. Books. The tables are of Inno. DB type. Inno. DB databases support. We place a foreign key constraint on the. Author. Id column of the Books table. We fill the tables. Query OK, 0 rows affected (0. Query OK, 0 rows affected (0. Query OK, 1 row affected (0. We use the source command to execute the books. In the following example, we are going to insert a new author. Authors table. Option Strict On. Imports My. Sql. Data. My. Sql. Client. Dim conn. String As String = "Database=testdb; Data Source=localhost; " _. User Id=testuser; Password=test. Dim conn As New My. Sql. Connection(conn.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |