using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace Exzamen
{
public partial class Form1 : Form
{
DataSet ds = new DataSet();
OleDbConnection connection = new OleDbConnection("Provider= Microsoft.Jet.OleDb.4.0;Data Source= dbnew.mdb;");
OleDbDataAdapter adapter;
DataView dataview;
OleDbCommand cmd = new OleDbCommand();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
//создание таблицы
private void button1_Click_1(object sender, EventArgs e)
{
try
{
string s = "Create Table " + textBox1.Text + "( ID_T Counter(1,10) Primary Key,"
+ "City varchar(10) Not Null," + "SS INT," + "integ INT)";
OleDbCommand cmd = new OleDbCommand(s);
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//удаление таблицы
private void button2_Click_1(object sender, EventArgs e)
{
try
{
string s = "Drop Table " + textBox1.Text;
OleDbCommand cmd = new OleDbCommand(s);
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open) connection.Close();
}
}
//вывод таблицы
private void button3_Click_1(object sender, EventArgs e)
{
//очистка dataGridView1
{
while (dataGridView1.Rows.Count > 1)
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
dataGridView1.Rows.Remove(dataGridView1.Rows[i]);
}
try
{
adapter = new OleDbDataAdapter("Select * FROM[" + textBox1.Text + "]", connection);
adapter.Fill(ds, textBox1.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dataGridView1.DataSource = ds.Tables[textBox1.Text];
dataview = new DataView(ds.Tables[textBox1.Text]);
dataGridView1.DataSource = dataview;
}
//создание столбца
private void button4_Click_1(object sender, EventArgs e)
{
try
{
string s = "ALTER TABLE [" + textBox1.Text + "] ADD COLUMN [" + textBox2.Text + "] char (5)";
OleDbCommand cmd = new OleDbCommand(s);
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
private void button5_Click(object sender, EventArgs e)
{
//удаление столбца
try
{
string s = "ALTER TABLE [" + textBox1.Text + "] Drop [" + textBox2.Text + "]";
OleDbCommand cmd = new OleDbCommand(s);
cmd.Connection = connection;
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//вычесляемое поле
private void button6_Click(object sender, EventArgs e)
{
ds.Tables["НоваяТаблица"].Columns.Add("[integ+SS]", typeof(int));
ds.Tables["НоваяТаблица"].Columns["[integ+SS]"].Expression = "[integ] + [SS]";
}
//добавить строку в таблицу
private void button7_Click(object sender, EventArgs e)
{
try
{
string cmdText = "INSERT INTO " + textBox1.Text + "(ID_T,City,SS,integ) Values('" + textBox3.Text + "'," + (string)textBox4.Text + "," + textBox5.Text + "," + textBox6.Text + ")";
cmd = new OleDbCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if(connection.State==ConnectionState.Open)
{
connection.Close();
}
}
}
//обновление строки
private void button8_Click(object sender, EventArgs e)
{
try
{
string cmdText = "UPDATE " + textBox1.Text + " Set City=" + textBox4.Text + ",SS=" + textBox5.Text + ",integ=" + textBox6.Text + " Where ID_T=" + textBox3.Text;
cmd = new OleDbCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//удаление строки
private void button9_Click(object sender, EventArgs e)
{
try
{
string cmdText = "DELETE FROM " + textBox1.Text + " Where ID_T=" + textBox3.Text;
cmd = new OleDbCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
private void button10_Click(object sender, EventArgs e)
{
DataView dv = new DataView(ds.Tables[textBox1.Text]);
dv.RowFilter = "ID_T=1";
dataGridView1.DataSource = dv;
}
//создание связи
private void button11_Click(object sender, EventArgs e)
{
DataRelation dr=new DataRelation("relation",
ds.Tables["НоваяТаблица"].Columns["ID_T"],
ds.Tables["NOVA"].Columns["ID"]);
if (!ds.Relations.Contains("relation"))
ds.Relations.Add(dr);
}
//создание запроса
private void button12_Click(object sender, EventArgs e)
{
string cmdText = "CREATE PROCEDURE SelectFromНоваяТаблица AS SELECT * FROM НоваяТаблица WHERE SS=50;";
try
{
cmd = new OleDbCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
//удаление запроса
private void button13_Click(object sender, EventArgs e)
{
string cmdText = "DROP PROCEDURE SelectFromНоваяТаблица";
try
{
cmd = new OleDbCommand(cmdText, connection);
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
//вывод запроса
private void button14_Click(object sender, EventArgs e)
{
if (ds.Tables[textBox1.Text] != null)
ds.Tables[textBox1.Text].Clear();
try
{
OleDbCommand stProc = new OleDbCommand(textBox1.Text, connection);
stProc.CommandType = CommandType.StoredProcedure;
OleDbDataAdapter ad = new OleDbDataAdapter(stProc);
OleDbParameter param = new OleDbParameter();
param.ParameterName = "SelectFromНоваяТаблица";
param.OleDbType = OleDbType.Integer;
param.Value = "50";
stProc.Parameters.Add(param);
connection.Open();
ad.Fill(ds, "НоваяТаблица");
dataGridView1.DataSource = ds.Tables["НоваяТаблица"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
}
}