Исходный код проекта

Класс Gridder:

class Gridder

{

private static DataGridView dGV = null; // data grid view to work with

//=============== A 'Single' pattern ===============//

private static Gridder instance = null;

private Gridder() { }

public static Gridder getInstance() {

if (instance == null)

return new Gridder();

return instance;

}

//===============================================//

/// <summary>

/// DataGridView to display info on

/// </summary>

public DataGridView DataGrid

{

set { Gridder.dGV = value; }

}

/// <summary>

/// Send a query and get info

/// </summary>

/// <param name="query"></param>

/// <param name="returnArray">put data in array. Otherwise it will put it in a DataGridView</param>

/// <param name="select">do we need an answer at all?</param>

/// <returns></returns>

public String[,] queryAndShow(String query, bool returnArray = false, bool select = true)

{

// don't do anything if we need dataGridView but it doesn't exist

if (((dGV == null) &&!returnArray)) return null;

// generate string of connection

string connectionString = "Server=" + Settings.Default.Server + ";Database=" + Settings.Default.Database + ";User Id=" + Settings.Default.Login + ";" + "Password=" + Settings.Default.Password + ";";

SqlConnection con = new SqlConnection(connectionString);

try

{

// open database connection

con.Open();

//MessageBox.Show("Connection is open ");

}

catch { MessageBox.Show("Connection is not open "); }

// здесь перехватываются все исключения

SqlCommand cmd; // contains a command

SqlDataReader dr; // contains a result

String [,] arr = null; // contains answer

cmd = new SqlCommand(query, con);

// метод ExecuteReader выполняет команду, возвращающую множество строк

try

{

dr = cmd.ExecuteReader(); // run query

if (select)

{

int i = 0, k = 0;

// определение числа возвращаемых строк k

while (dr.Read())

{ k += 1; }

dr.Close();// закрытие dr

//повторный вызов метода

dr = cmd.ExecuteReader();

if (!returnArray)

{

dGV.RowCount = k; // число строк в сетке равно k

dGV.ColumnCount = dr.FieldCount;

} else

{

arr = new String[k, dr.FieldCount];

}

while (dr.Read()) // выбор данных

{

if (!returnArray)

{

// for dataGridView

for (int j = 0; j < dr.FieldCount; j++)

dGV[j, i].Value = dr[j].ToString();

}

else

{

// for array

for (int j = 0; j < dr.FieldCount; j++)

arr[i, j] = dr[j].ToString();

}

i += 1;

}

if (!returnArray)

{

// задание имен полей сетки с помощью метода dr.GetName(j)

for (int j = 0; j < dr.FieldCount; j++)

dGV.Columns[j].HeaderText = dr.GetName(j);

for (int km = 0; km < dGV.Columns.Count; km++)

{

// autosize

dGV.Columns[km].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

}

}

}

dr.Close();// закрытие объекта dr

}

catch (Exception e) { MessageBox.Show(e.Message + "\nQUERY = '" + query + "'", "Error"); }

try

{

con.Close();

//MessageBox.Show("Connection is closed ");

}

catch { MessageBox.Show("Connection is not closed"); }

return arr; // anyway return an array

}

}

Класс формы с обработчиками событий:

public partial class Form1: Form

{

public Form1()

{

InitializeComponent();

// initial dataGridView for output

Gridder gr = Gridder.getInstance();

gr.DataGrid = dGV;

// combobox initialization

ComboBox[] cbArr = new ComboBox[] {comboBox1, comboBox2, comboBox3, comboBox4};

String[,] arr = gr.queryAndShow("SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'sysdiagrams';", true);

foreach (var combo in cbArr)

{

foreach (var cur in arr)

{

combo.Items.Add(cur);

}

if (arr.Length > 0)

{

// select the first element

combo.SelectedIndex = 0;

}

}

// Settings information

textBox7.Text = Settings.Default.Server;

textBox8.Text = Settings.Default.Database;

textBox9.Text = Settings.Default.Login;

textBox10.Text = Settings.Default.Password;

}

// SELECT

private void button_select_query_Click(object sender, EventArgs e)

{

Gridder gr = Gridder.getInstance();

String fields = "";

String table = comboBox1.SelectedItem.ToString();

String where = textBox2.Text;

String orderBy = textBox1.Text;

// get all fields

foreach (var item in listBox1.SelectedItems)

{

fields += item + ",";

}

if (fields == "")

{

MessageBox.Show("Choose at least one field");

return;

}

// remove the last comma

fields = fields.Remove(fields.Length - 1, 1);

String query = "SELECT " + fields +

" FROM " + table +

(where.Length>0? " WHERE " + where: "") +

(orderBy.Length > 0? " ORDER BY " + orderBy: "");

toolStripStatusLabel1.Text = query;

// query

gr.queryAndShow(query, false);

}

// SELECT

private void comboBox_select_query_SelectedIndexChanged(object sender, EventArgs e)

{

// get table name

String table = ((ComboBox)sender).SelectedItem.ToString();

Gridder gr = Gridder.getInstance();

String[,] arr = gr.queryAndShow("SELECT [name] AS [Column Name] " +

"FROM syscolumns " +

"WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = '" + table + "')", true);

// clear all old items

listBox1.Items.Clear();

foreach (var item in arr)

{

listBox1.Items.Add(item);

}

listBox1.SelectedIndices.Clear();

// select all

for (int i = 0; i < listBox1.Items.Count; i++)

{

listBox1.SelectedIndices.Add(i);

}

}

// INSERT

private void comboBox_insert_query_SelectedIndexChanged(object sender, EventArgs e)

{

// get table name

String table = ((ComboBox)sender).SelectedItem.ToString();

Gridder gr = Gridder.getInstance();

String[,] arr = gr.queryAndShow("SELECT [name] AS [Column Name] " +

"FROM syscolumns " +

"WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = '" + table + "')", true);

dataGridView2.ColumnCount = arr.Length - 1;

int i = 0;

// fill all column names

foreach (var item in arr)

{

if (item == "Id") continue; // don't paste id, 'cause it's a PK

dataGridView2.Columns[i].HeaderText = item;

i++;

}

// Autosize dataGridView

for (int km = 0; km < dataGridView2.Columns.Count; km++)

{

dataGridView2.Columns[km].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

}

}

// Clear dataGridView. INSERT

private void button_clear_datagrid_Click(object sender, EventArgs e)

{

dataGridView2.Rows.Clear();

}

private void button_connect_Click(object sender, EventArgs e)

{

// gather info

string server = textBox7.Text;

string database = textBox8.Text;

string login = textBox9.Text;

string passwd = textBox10.Text;

Gridder gr = Gridder.getInstance();

// check

String[,] arr = gr.queryAndShow("SELECT name FROM sysobjects WHERE xtype='U' AND name <> 'sysdiagrams';", true);

if (arr!= null)

{

// Save settings

Settings.Default.Server = server;

Settings.Default.Database = database;

Settings.Default.Login = login;

Settings.Default.Password = passwd;

Settings.Default.Save();

MessageBox.Show("Successfully connected");

}

}

// INSERT

private void button_insert_query_Click(object sender, EventArgs e)

{

string values = "";

// form values

for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)

{

values += "(";

for (int j = 0; j < dataGridView2.Rows[i].Cells.Count; j++)

{

values += "'" + (dataGridView2.Rows[i].Cells[j].Value) + "',";

}

if (values.Length > 0)

values = values.Remove(values.Length - 1, 1);

values += "),";

}

// form fields

if (values.Length > 0)

{

string fields = "(";

for (int i = 0; i < dataGridView2.Columns.Count; i++)

{

fields += dataGridView2.Columns[i].HeaderText + ",";

}

fields = fields.Remove(fields.Length - 1, 1);

fields += ")";

values = values.Remove(values.Length - 1, 1);

// query

string query = "INSERT INTO " + comboBox2.SelectedItem.ToString() + " " + fields + " VALUES " + values;

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

gr.queryAndShow(query, true, false);

}

else

{

MessageBox.Show("There is no any data to enter");

}

}

// UPDATE

private void button_update_query_Click(object sender, EventArgs e)

{

string query = "UPDATE " + comboBox3.SelectedItem.ToString() + " SET " + textBox3.Text + " = '" + textBox4.Text + "' WHERE " + textBox5.Text;

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

gr.queryAndShow(query, true, false);

}

// DELETE

private void button_delete_query_Click(object sender, EventArgs e)

{

string query = "DELETE FROM " + comboBox4.SelectedItem.ToString() + " WHERE " + textBox6.Text;

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

gr.queryAndShow(query, true, false);

}

// Count 1

private void button4_Click(object sender, EventArgs e)

{

string query = "SELECT COUNT(Id) FROM dbo.Question";

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

int a, b;

// query

String [,] ans = gr.queryAndShow(query, true, true);

// get a number

string num1 = ans[0, 0];

query = "SELECT COUNT(Id) FROM dbo.Account";

toolStripStatusLabel1.Text = query;

// query

ans = gr.queryAndShow(query, true, true);

// get a number

string num2 = ans[0, 0];

// parse and check

if (Int32.TryParse(num1, out a) && Int32.TryParse(num2, out b))

{

// print the result

label14.Text = (Convert.ToDouble(a) / Convert.ToDouble(b)).ToString();

}

else

{

label14.Text = "Some errors occured...";

}

}

// Count 2

private void button5_Click(object sender, EventArgs e)

{

string query = "SELECT Id FROM dbo.[Type] t WHERE t.Name = 'Student'";

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

int a, b;

// query

String[,] ans = gr.queryAndShow(query, true, true);

// get a number

string num1 = ans[0, 0];

// parse

if (Int32.TryParse(num1, out a))

{

query = "SELECT COUNT(*) FROM dbo.[Session] s JOIN dbo.Account a ON s.Id_Account = a.Id WHERE a.Account_Type = " + a;

toolStripStatusLabel1.Text = query;

// query

ans = gr.queryAndShow(query, true, true);

// get a number

string num2 = ans[0, 0];

// parse

if (Int32.TryParse(num2, out b))

{

// print the result

label15.Text = (Convert.ToInt32(b)).ToString();

return;

}

}

label15.Text = "Some errors occured...";

}

// Count 3

private void button6_Click(object sender, EventArgs e)

{

string query = "SELECT q.Answers FROM dbo.Question q";

toolStripStatusLabel1.Text = query;

Gridder gr = Gridder.getInstance();

// query

String[,] ans = gr.queryAndShow(query, true, true);

int maxLength = 0;

for (int i = 0; i < ans.Length; i++)

{

// find the max

maxLength = Math.Max(ans[i,0].Length, maxLength);

}

// print the result

label17.Text = maxLength.ToString();

}

}