Ma'lumotlar bazasini yaratish

Ma'lumotlar bazasini yaratish:
Ma'lumotlar bazasini va keyinchalik dasturda foydalanadigan
jadval yaratamiz.
Buyruq satridan biz ma'lumotlar bazasini yaratishni boshlaymiz:
ConnectCsharpToMysql ma'lumotlar bazasini yaratish ;
Keyin jadval yaratilishidan oldin foydalanish uchun ma'lumotlar
bazasini tanlaymiz:
ConnectCsharpToMysqldan foydalanish;
Biz dasturimizdan so'rashimiz mumkin bo'lgan jadvalni yaratamiz:
Create Table TableInfo
(
id AVTO AVTOMOZALARNI NULL EMAS,
nomi VARCHAR (30),
yosh INT
PRIMARY KEY ( id)
);
Kodlardan foydalanish.
Ma'lumot qo'shish va loyihadan MySQL ulagichi DLL yaratish.
Kodni
yozishni
boshlashdan
oldin ,
bizning
loyihamizga mysql Reference- ni qo'shishimiz kerak . Buning uchun biz
loyihamiz nomini o'ng tugmasini bosing va “Add Reference” ni tanlang
14.2 rasm Reference qo‟shish
Keyin biz ro'yxatdan MySql.Data- ni tanlaymiz : AddReference 2
102
Ilovani ulagich o'rnatilmagan boshqa kompyuterlarda ishlatish uchun biz
ma'lumotnomadan DLL
yaratishimiz
kerak. Buning
uchun biz
loyihamizdagi mos yozuvlar nomini sichqonchaning o'ng tugmasi bilan
bosamiz va uning nusxasida lokal ravishda haqiqiyligini o'rnatamiz:
Dll qo'shiladi.
Sinfni yaratiladi.
Ma'lumotlar bazasiga ulanish uchun yangi sinf yaratish va ma'lumotlar
bazasiga kiradigan koddan ajratish har doim yaxshiroq. Bu bizning
kodimizni toza, o'qishi oson va samaraliroq bo'lishiga yordam beradi.
Biz MySql Connector kutubxonasini qo'shishdan boshlaymiz :
// MySql kutubxonasini qo'shish
MySql.Data.MySqlClient-dan foydalanish;
Keyin biz foydalanadigan o'zgaruvchini e'lon qilish va ishga tushirish:
ulanish :
ma'lumotlar
bazasiga
ulanishni
ochishda
foydalaniladi.
server : bizning serverimiz joylashgan joyni ko'rsatadi,
bizning holimizda , bu localhost .
Ma'lumotlar bazasi : bu biz foydalanadigan ma'lumotlar
bazasining nomi, bizning holimizda biz oldinroq yaratgan
ma'lumotlar bazasi, bu connectcsharptomysql .
uid : bu bizning MySQL foydalanuvchi nomimiz.
parol : bu bizning MySQL parolimiz.
connectionString : ma'lumotlar bazasiga ulanish uchun ulanish
satrini o'z ichiga oladi va ulanish o'zgaruvchisiga tayinlanadi.
class DBConnect
{
private MySqlConnection connection;
private string server;
private string database;
private string uid;
private string password;
//Constructor
public DBConnect()
{
Initialize();
}
//Initialize values
103
private void Initialize()
{
server = "localhost";
database = "connectcsharptomysql";
uid = "username";
password = "password";
string connectionString;
connectionString = "SERVER=" + server + ";" +
"DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" +
password + ";";
connection = new MySqlConnection(connectionString);
}
//open connection to database
private bool OpenConnection()
{
}
//Close connection
private bool CloseConnection()
{
}
//Insert statement
public void Insert()
{
}
public void Update()
{
}
public void Delete()
{
}
//Select statement
public List [] Select()
{
}
//Count statement
public int Count()
104
{
}
//Backup
public void Backup()
{
}
//Restore
public void Restore()
{
}
}
Ulanishni ochish va yopish
Biz doimo jadvallarimizga murojaat qilishdan oldin ulanishni
ochishimiz va uni tugatgandan so'ng darhol o'chirib qo'yishimiz kerak,
bu resurslarni bo'shatish va bu ulanish endi kerak emasligini bildiradi.
Ma'lumotlar bazasiga ulanishni ochish va yopish juda oson, ammo har
doim ulanishni ochmasdan yoki yopmasdan oldin istisnolardan
foydalanish, xatolarni ko'rish va ular bilan shug'ullanish yaxshidir.
// ma'lumotlar bazasiga ochiq ulanish
private bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server. Contact
administrator");
break;
case 1045:
MessageBox.Show("Invalid username/password, please try
again");
105
break;
}
return false;
}
}
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
DML bilan ishlash (qo'shish, yangilash, tanlash, o'chirish).
Odatda, kiritish, yangilash va o'chirish ma'lumotlar bazasida
ma'lumotlarni yozish yoki o'zgartirish uchun ishlatiladi, Select esa
ma'lumotlarni o'qish uchun ishlatiladi.
Shu sababli, biz ushbu so'rovlarni bajarish uchun har xil usullarga
egamiz.
Usullari quyidagilar:
ExecuteNonQuery : misol uchun, har qanday ma'lumotlarni
qaytarib bo'lmaydi buyruqni bajarishi uchun ishlatiladi Insert , yangilash
yoki o'chirish.
ExecuteReader : 0 yoki undan ortiq yozuvlarni qaytaradigan buyruqni
bajarish uchun foydalaniladi, masalan Select .
ExecuteScalar : Faqat 1 qiymatni qaytaradigan buyruqni bajarish uchun
foydalaniladi, masalan, Hisoblashni tanlang ( *).
Ma'lumotlar bazasiga ochiq ulanish.
MySQL buyrug'ini yarating.
Buyruqqa ulanish va so'rovni tayinlang. Buni konstruktor
yordamida
yoki MySqlCommand sinfidagi Connection
va CommandText usullaridan foydalanib bajarish mumkin .
106
public void Insert()
{
string query = "INSERT INTO tableinfo (name, age) VALUES('John
Smith', '33')";
if (this.OpenConnection() == true)
{
//create command and assign the query and connection from the
constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
}
}
public void Update()
{
string query = "UPDATE tableinfo SET name='Joe', age='22'
WHERE name='John Smith'";
if (this.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
//Execute query
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
//Delete statement
public void Delete()
{
string query = "DELETE FROM tableinfo WHERE name='John
Smith'";
if (this.OpenConnection() == true)
107
{
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
}
}
Endi MySQLni C ++ ga ulashni ko‟rib chiqamiz. Ma'lumotlar bazasiga
yordamida C / C ++ ulanish.
SQL (Structured Query Language) - bu to'rtinchi avlod tili (4GL) bo'lib,
u MBBTni aniqlash, boshqarish va boshqarish uchun ishlatiladi
(ma'lumotlar bazasini boshqarish tizimi).
Kod:
C: \ SQLAPI \ lib \ libsqlapiddll.a
C: \ Program Files \ CodeBlocks \ MinGW \ lib \ libuser32.a
C: \ Program Files \ CodeBlocks \ MinGW \ lib \ libversion.a
C: \ Program Files \ CodeBlocks \ MinGW \ lib \ liboleaut32.a
C: \ Program Files \ CodeBlocks \ MinGW \ lib \ libole32.a
Ular sizning SQLAPI ++ da topiladi (Agar siz C: drayverida ajratib
olmagan bo'lsangiz, tegishli joyni tanlang va bog'langan sozlamalarga
ushbu fayllarni qo'shing).
Yuqoridagi kod C / C ++ dasturini SQLAPI bilan ulash uchun
kutubxona fayllarini qo'shish uchun ishlatiladi.
Asosan, ikkita bosqich mavjud:
1. Ma'lumotlar bazasiga ulanish (va xatolar bilan ishlov berish)
// Ma'lumotlar bazasiga ulanish uchun C ++ pgroram (va xatolar
bilan ishlash)
#include
#include
// main SQLAPI++ header
int main(int argc, char* argv[])
{
// create connection object to connect to database
SAConnection con;
try
{
con.Connect ("test", // database name
"tester", // user name
"tester", // password
108
SA_Oracle_Client); //Oracle Client
printf("Ulandi!\n");
con.Disconnect();
printf("Uzildi!\n");
}
catch(SAException & x)
{
try
{
con.Rollback ();
}
catch(SAException &)
{
}
printf("%s\n", (const char*)x.ErrText());
}
return 0;
}
Ulandi !
Uzildi !
SQL oddiy buyrug'ini bajarish.
Endi biz sodda SQL so'rovini bajaramiz. Dastlab ma'lumotlar bazasi
uchun jadval yaratamiz :
create table tb1(id number, name varchar(20);
Endi, so'rovni
ma'lumotlar
bazasiga
yuborish
uchun
cmd.setCommandText usulidan foydalanish kerak, u quyidagicha
ko'rsatiladi:
con.Connect("test", "tester", "tester", SA_Oracle_Client);
cmd.setCommandText("create table tb1(id number, name
varchar(20));”);
and now, to execute the query we have to use the following command:
cmd.Execute();
Full Code:
#include
#include // main SQLAPI++ header
int main(int argc, char* argv[])
{
109
SAConnection con; // connection object to connect to database
SACommandcmd; // create command object
try
{
// connect to database (Oracle in our example)
con.Connect("test", "tester", "tester", SA_Oracle_Client);
// associate a command with connection
// connection can also be specified in SACommand constructor
cmd.setConnection(&con);
// create table
cmd.setCommandText("create table tbl(id number, name
varchar(20));");
cmd.Execute();
// insert value
cmd.setCommandText("Insert into tbl(id, name) values
(1,”Vinay”)");
cmd.setCommandText("Insert into tbl(id, name) values
(2,”Kushal”)");
cmd.setCommandText("Insert into tbl(id, name) values
(3,”Saransh”)");
cmd.Execute();
// commit changes on success
con.Commit();
printf("Table created, row inserted!\n");
}
catch(SAException &x)
{
try
{
// on error rollback changes
con.Rollback();
}
catch(SAException &)
{
}
printf("%s\n", (const char*)x.ErrText());
}
110
return 0;
}
con.Commit ( );
Yuqorida biz ma‟lumotlar bazasiga murojaatni tashkil etishda C++,C#
dasturlariga bog‟lash va ularni kod jixatdan taxlilini keltirib o‟tdik.
|