[Перевод] Простой инструмент SQL Server Tool на C#
Публикуем перевод статьи, в которой рассматривается поэтапное создание простого инструмента SQL Server Tool.
Вы также можете посмотреть обучающее видео Easy SQL Tool Using C# на YouTube.
Вступление
Инструмент SQL Server Tool позволяет создавать и удалять базы данных и таблицы, загружать все имена баз из выбранного сервера, наполнять таблицы данными, отображать их в виде сетки, экспортировать данные в формат .csv — и всё это без использования скриптов SQL.
Для программирования баз данных, таблиц и других компонентов SQL Server мы использовали SQL Server Management Objects (SMO) — управляющие объекты SQL Server.
Что такое управляющие объекты SQL Server (SMO)?
Управляющие объекты SQL Server — это набор объектов для программирования всех аспектов SQL Server. Объекты SMO позволяют подключаться к серверу SQL, получать доступ к наборам имен баз данных, создавать или восстанавливать базы данных, их резервные копии и таблицы, просматривать их описания и т. д.
Для получения более подробной информации об управляющих объектах SQL Server смотрите:
• Overview (SMO)
• Create a Visual C# SMO Project in Visual Studio .NET
• Server Class
Основные компоненты Shanu Easy SQL Server Tool (SESST):
1. Подключение к SQL-серверу;
2. Работа с таблицами;
3. Вкладки SQL Script (вкладка Create Table / вкладка Insert and Delete Records / вкладка Select Query);
4. Окно вывода.
Далее мы детально рассмотрим каждый из них.
Шаг 1. Подключение к SQL-серверу
Мы можем подключиться к любому SQL-серверу в нашей сети. Для этого нужно ввести имя сервера или его IP-адрес, идентификатор входа и пароль.
Создание новой базы данных
После ввода имени новой базы данных происходит проверка его доступности. Если имя уже существует, вы получите соответствующее уведомление. Если имя свободно, новая база данных будет создана на сервере.
Загрузка базы данных
Выбор имени существующей базы данных из комбинированного списка.
Удаление базы данных
Выбранную базу данных можно удалить с сервера. Перед удалением появится окно подтверждения. Чтобы продолжить, нажмите «Ок».
Создание резервной копии базы данных
Резервную копию выбранной базы данных можно сохранить в любую папку.
Восстановление базы данных
Для восстановления базы данных необходимо выбрать .bak-файл в соответствующей папке.
Шаг 2. Работа с таблицами
Загрузка таблицы
После того как вы выбрали базу данных и нажали кнопку Load Table Names, имена всех таблиц в этой базе отобразятся в виде комбинированного списка.
Удаление таблицы
Выбранную таблицу можно удалить с сервера. Перед удалением появится окно подтверждения. Чтобы продолжить, нажмите «Ок».
Шаг 3. Вкладки SQL Script
Здесь у нас есть 3 вкладки: Create Table — для создания таблицы, Insert and Delete Records — для добавления и удаления записей, и Select Query — для создания запросов на выборку.
Создание таблицы
В этой вкладке можно создавать таблицы без написания запроса SQL. Для этого нужно выбрать базу данных, в которой будет создана таблица, ввести ее имя в текстовом поле и добавить столбцы. Последний шаг является обязательным.
Добавление столбцов
Первым делом к новой таблице нужно добавить столбцы. Вы можете добавить любое количество столбцов, удалить выбранный столбец или все сразу (с помощью кнопки Clear Columns). На этом этапе любые изменения заносятся в локальную временную таблицу и не сохраняются в базе данных. После добавления столбцов нажмите кнопку Create Table, чтобы создать таблицу с заданными параметрами.
Типы данных:
При добавлении столбцов используются 3 типа данных:
1. Int (числовые данные);
2. varchar (текстовые данные);
3. nVarchar (числовые и текстовые данные).
Для столбцов типа varchar и nVarchar также можно отображать размер данных (для типа integer этот параметр не используется). Кроме того, вы можете задать максимальный размер данных, установив флажок Max Size.
В качестве примера рассмотрим следующую gif-анимацию. Здесь у нас есть 4 столбца, к которым мы добавляем пятый. При этом для каждого из них справа отображаются тип и размер данных. Добавив последний столбец, мы нажимаем кнопку Create Table. Появляется подтверждающее сообщение. Теперь нашу новую таблицу можно выбрать в комбинированном списке Table List.
Добавление и удаление записей
Для демонстрации добавления записей мы продолжим использовать тестовую таблицу, созданную в предыдущем разделе.
Начнем с того, что выберем таблицу TEST из списка. Для добавления записи в выбранную таблицу нажимаем кнопку Load Table Details to insert. Итак, мы видим имена всех столбцов с текстовыми полями, которые нужно заполнить в соответствии с типом и максимальным размером данных. В поля столбцов типа integer можно вводить только числовые данные. Если размер данных для столбцов типа varchar и nVarchar указан как максимальный, для свойства TextBox.MaxLength нужно задать значение 200 — это значит, что пользователь сможет ввести не более 200 символов. Если же размер данных имеет определенное значение, мы просто переносим его в свойство TextBox.MaxLength.
Добавление:
После загрузки описаний всех столбцов можно вводить их значение. После того как вы ввели данные в текстовое поле, нажмите кнопку Insert into Selected Table — и запись будет добавлена в таблицу. Если хотите, можете сами добавить больше функционала с помощью кода, например функцию проверки повторяющихся значений перед отправкой и т. п.
Удаление всех записей:
Все записи можно удалить из выбранной таблицы. Данный инструмент не поддерживает проверку условий перед удалением, но вы можете добавить ее сами.
Создание запроса на выборку
Для выборки записей из базы данных необходимо выбрать саму базу, затем — таблицу из списка и создать SQL-запрос.
Уточнить параметры выборки можно тремя способами:
1) Все столбцы: если установлен флажок All Columns, в окне вывода отображаются все столбцы (обратите внимание: он установлен по умолчанию). Таким образом, нажав кнопку Run Select Query, вы получите записи всех столбцов.
2) Выделенные столбцы: если вы хотите вывести записи определенных столбцов, нажмите кнопку Load Table Columns to Select. Перед вами появится список столбцов, причем все они по умолчанию отмечены галочками. Снимите галочки с ненужных столбцов и не забудьте снять флажок All Columns. В результате вы получите записи только выделенных столбцов.
3) Создание SQL-запроса вручную: вы также можете написать SQL-запрос вручную.
Допустим, вы хотите объединить 3 таблицы с помощью SQL-запроса. Для этого установите флажок SQL Select Query, напишите запрос на объединение вручную и нажмите кнопку Run Select Query для отображения результатов.
Проверка пользовательского запроса на наличие SQL-инъекций:
Перед выполнением нашего запроса давайте проверим его на наличие SQL-инъекций. Мы создали список массивов со всеми строками, чтобы проверить их на совпадение ключевых слов с пользовательским запросом. В качестве примера мы ввели ключевое слово drop. Как вы видите, после нажатия кнопки Run Select Query появляется сообщение, что данное ключевое слово не поддерживается.
Ниже приведен список ключевых слов, которые я использую для проверки на наличие SQL-инъекций. Вы можете дополнить или сократить этот список по своему усмотрению.
string[] sqlInjectionArray = { "create", "drop", "delete", "insert", "update", "truncate","grant ","print","sp_executesql ,"objects","declare","table","into",
"sqlcancel","sqlsetprop","sqlexec","sqlcommit","revoke","rollback","sqlrollback","values","sqldisconnect","sqlconnect","user","system_user","use","schema_name","schemata","information_schema","dbo","guest","db_owner","db_","table","@@","Users","execute","sysname","sp_who","sysobjects","sp_","sysprocesses ","master","sys","db_","is_","exec", "end", "xp_","; --", "/*", "*/", "alter", "begin", "cursor", "kill","--" ,"tabname","or","sys"};
Сохранение выборки: вы также можете сохранить результаты выборки для дальнейшего использования в виде текстового файла в выбранной папке.
Открытие сохраненной выборки: откройте сохраненную выборку, чтобы выполнить скрипт.
Экспорт выборки в формат .csv: процесс экспорта результатов выборки в формат .csv показан ниже.
Предварительные условия
Visual Studio 2015 (можно скачать по ссылке)
Ссылки SQL Server SMO: для работы с управляющими объектами SQL Server нужно добавить 4 важные ссылки:
• Microsoft.SqlServer.ConnectionInfo.dll;
• Microsoft.SqlServer.Smo.dll;
• Microsoft.SqlServer.Management.Sdk.Sfc.dll;
• Microsoft.SqlServer.SqlEnum.dll.
Все эти ссылки можно найти в директории (SQL Server 2014):
C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\
Перед началом работы не забудьте добавить их в проект:
Использование кода
Нашей основной целью было создание простого инструмента SQL. Все функции добавлены в основную форму с несколькими вкладками. Мы сделали 2 класса:
1. smoSQLServerClass: к этому классу относятся функции подключения к SQL-серверу и отключения от него, создания и удаления базы данных или ее резервной копии, занесения исключений в текстовый файл журнала, создания таблиц и т. д.
2. sqlBizClass: этот класс отвечает за реализацию бизнес-логики, а именно — создание динамических запросов, элементов управления и закрепление их на панели, связывание имен всех таблиц, возвращаемых классом smoSQLServerClass, с комбинированным списком, проверку на наличие SQL-инъекций и т. д.
Далее мы рассмотрим некоторые основные фрагменты кода, снабдив их подробными комментариями.
Подключение к SQL-серверу
Как отмечалось выше, для использования управляющих объектов важно добавить 4 ссылки на сборки. Подключение к SQL-серверу осуществляется с помощью объекта ServerConnection. Мы задаем имя сервера ServerInstance, а также прописываем его идентификатор и пароль. После подключения мы проверяем, открыто ли соединение с сервером, и возвращаем логическое значение основной функции для отображения соответствующего уведомления.
public bool SqlServerConnect()
{
try
{
servConn = new ServerConnection();
servConn.ServerInstance = serverName;
servConn.LoginSecure = false;
servConn.Login = LoginID;
servConn.Password = password;
servConn.Connect();
if(servConn.IsOpen)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return false;
}
Занесение сообщений об исключениях в журнал
Все сообщения об исключениях заносятся в файлы журнала с указанием даты и времени.
public void writeLogMessage(String logMessage)
{
string path = Application.StartupPath + @"\LogFile.txt";
logMessage = logMessage + " - on " + DateTime.Now.ToString();
if (!File.Exists(path))
{
using (StreamWriter tw = File.CreateText(path))
{
tw.WriteLine(logMessage);
tw.Close();
}
}
else
{
StreamWriter tr = new StreamWriter(path);
tr.WriteLine(logMessage);
tr.Close();
}
}
Загрузка имени базы данных
При нажатии кнопки Load Database Name параметры комбинированного списка передаются методу класса Biz, который вызывает класс SMO и добавляет в список имена всех баз данных за исключением основной (чтобы пользователь случайно не удалил ее через основную форму). Подобным образом вы можете добавить дополнительные ограничения на отображение баз данных в основной форме.
public void loaddbNames(ComboBox cbo)
{
//return objSQL.loaddbNames();
DatabaseCollection dbnamesCol = objSQL.loaddbNames();
cbo.Items.Clear();
cbo.Items.Add("");
if (dbnamesCol != null)
{
string dbnames = "";
int ival = 0;
foreach (Database db in dbnamesCol)
{
if (db.Name != "master")
{
cbo.Items.Add(db.Name);
} }
}
cbo.SelectedIndex = 0;
}
Класс SMO загружает имена всех баз данных на выбранном сервере и возвращает их в виде объекта DatabaseCollection вызывающему классу.
public DatabaseCollection loaddbNames()
{
DatabaseCollection dbNames = null;
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
dbNames = srv.Databases;
SqlServerDisconnect();
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return dbNames;
}
Создание базы данных
Сначала эта функция проверяет доступность имени, введенного пользователем для базы данных. Если имя свободно, новая база данных будет создана на сервере. Если имя уже существует, пользователь получит соответствующее уведомление.
public string createourDatabase(string DatabaseName)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
if (database == null)
{
database = new Database(srv, DatabaseName);
database.Create();
database.Refresh();
SqlServerDisconnect();
return "Database Created Successfully !";
}
else
{
SqlServerDisconnect();
return "Database Already Exist";
}
}
else
{
return "Enter valid SQL Connection Details";
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return "Sorry Error While creating DB";
}
Создание таблицы
При нажатии кнопки Create все данные (имя выбранной базы данных, заданное имя таблицы и параметры столбцов) передаются в виде объекта DataTable классу SMO. Здесь также происходит проверка доступности имени, введенного пользователем. Если имя свободно, новая таблица будет успешно создана. Если имя уже существует, пользователь получит соответствующее уведомление.
public string createTable(string DatabaseName, string TableName,DataTable dtColumns)
{
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database database = srv.Databases[DatabaseName];
if (database != null)
{
bool tableExists = database.Tables.Contains(TableName);
if (tableExists)
{
SqlServerDisconnect();
return "Table Already Exist.kindly Enter Different Table Name";
}
else
{
Table tbl = new Table(database, TableName);
foreach (DataRow dr in dtColumns.Rows)
{
string columnName = dr["ColumName"].ToString();
string DataType = dr["DataType"].ToString();
string dataSize = dr["Size"].ToString();
Microsoft.SqlServer.Management.Smo.Column columntoAdd =null;
switch (DataType)
{
case "Varchar":
if(dataSize=="max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.VarChar(Convert.ToInt32(dataSize)));
}
break;
case "Int":
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.Int);
break;
case "nVarchar":
if (dataSize == "max")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarCharMax);
}
else if (dataSize != "")
{
columntoAdd = new Column(tbl, columnName, Microsoft.SqlServer.Management.Smo.DataType.NVarChar(Convert.ToInt32(dataSize)));
}
break;
}
if(columntoAdd!=null)
{
tbl.Columns.Add(columntoAdd);
}
}
tbl.Create();
SqlServerDisconnect();
return "Table Created Successfully !";
}
}
}
else
{
return "Enter valid SQL Connection Details";
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return "Sorry Error While Creating Table";
}
Загрузка параметров столбцов
При нажатии кнопки Load Column Details мы передаем параметры панели управления классу Biz, чтобы получить все параметры столбцов выбранной таблицы в виде объекта ColumnCollection из класса SMO. С помощью оператора foreach мы загружаем все параметры и добавляем текстовое поле для ввода информации пользователем. Здесь также происходит проверка типа и размера данных в столбцах. Если тип данных — integer, текстовое поле преобразовывается в числовое. Если тип данных — varchar или nVarchar, для свойства TextBox.MaxLength назначается максимальная длина столбца.
public void loadTableColumnDetails(Panel pnControls, string DataBaseName,string TableName)
{
ColumnCollection tableColumnDetail = objSQL.loadTableColumnDetails(DataBaseName, TableName);
pnControls.Controls.Clear();
if (tableColumnDetail != null)
{
string dbnames = "";
int lableHeight = 20;
int textboxHeight = 20;
int lablewidth = 100;
int lableXVal = 10;
int lableYVal = 10;
foreach (Column colName in tableColumnDetail)
{
string s = colName.Name;
Random rnd = new Random();
int randNumber = rnd.Next(1, 1000);
//to add Column name to display as caption
Label ctrl = new Label();
ctrl.Location = new Point(lableXVal , lableYVal+6);
ctrl.Size = new Size(lablewidth , lableHeight);
ctrl.Name = "lbl_" + randNumber; ;
ctrl.Font = new System.Drawing.Font("NativePrinterFontA", 7F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrl.Text = colName.Name;
pnControls.Controls.Add(ctrl);
//to add textbox for user enter insert text
TextBox ctrltxt = new TextBox();
ctrltxt.Location = new Point(lableXVal+110, lableYVal);
ctrltxt.Size = new Size(lablewidth+40, lableHeight);
ctrltxt.Name = "txt_" + randNumber;
ctrltxt.Font = new System.Drawing.Font("NativePrinterFontA", 8F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrltxt.Text = "";
if (colName.DataType.Name== "int")
{
ctrltxt.MaxLength = 20;
ctrltxt.KeyPress += new KeyPressEventHandler(textBox_KeyPress);
}
else
{
if(colName.DataType.MaximumLength.ToString()!="-1")
{
ctrltxt.MaxLength = Convert.ToInt32(colName.DataType.MaximumLength.ToString());
}
else
{
ctrltxt.MaxLength =100;
}
}
pnControls.Controls.Add(ctrltxt);
//to add Column datatype as hidden field
Label ctrllbl = new Label();
ctrllbl.Location = new Point(lableXVal + 112, lableYVal + 6);
ctrllbl.Size = new Size(1, 1);
ctrllbl.Name = "_lblDT_" + randNumber; ;
ctrllbl.Font = new System.Drawing.Font("NativePrinterFontA", 7F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
ctrllbl.Text = colName.DataType.Name;
ctrllbl.Visible = false;
pnControls.Controls.Add(ctrllbl);
if (lableXVal + 360 < pnControls.Width-110)
{
lableXVal = lableXVal + 270;
}
else
{
lableXVal = 10;
lableYVal = lableYVal + 40;
}
}
}
}
//for numeric textbox validation
private void textBox_KeyPress(object sender, KeyPressEventArgs e)
{
e.Handled = !char.IsDigit(e.KeyChar) && !char.IsControl(e.KeyChar);
}
Метод loadTableColumnDetails класса SMO возвращает все параметры столбцов выбранной таблицы в виде объекта ColumnCollection классу Biz для создания динамических элементов пользовательского ввода.
public ColumnCollection loadTableColumnDetails(string DatabaseName,string TableName)
{
ColumnCollection columnDetails = null;
try
{
if (SqlServerConnect())
{
Server srv = new Server(servConn);
Database db = srv.Databases[DatabaseName];
bool tableExists = db.Tables.Contains(TableName);
if (tableExists)
{
foreach (Table table in db.Tables)
{
if (table.Name == TableName)
{
columnDetails = table.Columns;
break;
}
}
}
SqlServerDisconnect();
}
}
catch (Exception ex)
{
writeLogMessage(ex.Message.ToString());
}
return columnDetails;
}
Добавление записей
При нажатии кнопки Insert данные, введенные пользователем, вместе с именем базы данных и именем таблицы передаются классу Biz, который создает динамический запрос на добавление новых записей и передает команду SqlCommand методу insertQuerymethod класса SMO.
public string saveTableInsertQuery(Panel pnControls, string DataBaseName, string TableName)
{
string result = "";
StringBuilder sqlQuery = new StringBuilder("INSERT INTO " + TableName );
StringBuilder Insert = new StringBuilder(" (");
StringBuilder values = new StringBuilder("VALUES (");
SortedDictionary sd = new SortedDictionary();
string columnName = "";
string colvalue = "";
string dataType = "";
int iCount = 0;
SqlCommand command = new SqlCommand();
foreach (Control p in pnControls.Controls)
{
if (p.Name.ToString().Substring(0, 4) == "lbl_")
{
columnName = p.Text;
}
else if (p.Name.ToString().Substring(0, 4) == "txt_")
{
colvalue = p.Text;
}
else if (p.Name.ToString().Substring(0, 4) == "_lbl")
{
Insert.Append(columnName);
Insert.Append(", ");
sd.Add(columnName, colvalue);
values.Append("@" + columnName);
values.Append(", ");
if (p.Text == "int")
{
command.Parameters.Add("@" + columnName, SqlDbType.Int).Value = colvalue;
}
else if (p.Text == "varchar")
{
command.Parameters.Add("@" + columnName, SqlDbType.VarChar).Value = colvalue;
}
else if (p.Text == "nvarchar")
{
command.Parameters.Add("@" + columnName, SqlDbType.NVarChar).Value = colvalue;
}
}
}
string sqlresult = Insert.ToString().Remove(Insert.Length - 2) + ") ";
sqlQuery.Append(sqlresult);
string valueresult = values.ToString().Remove(values.Length - 2) + ") ";
sqlQuery.Append(valueresult);
sqlQuery.Append(";");
command.CommandText = sqlQuery.ToString();
command.CommandType = CommandType.Text;
return objSQL.insertQuery(DataBaseName, sqlQuery.ToString(), command);
}
Создание запроса на выборку
При нажатии кнопки Select, только если флажок SQL Select Query не установлен, параметры столбцов передаются методу selectRecordsfromTableQuery класса Biz для создания динамического запроса на выборку. Если пользователь выделил все столбцы, используется «select * from tablename». Если же пользователь выделил всего несколько столбцов, выборка выполняется только по ним и возвращает результат в виде объекта DataTable.
public DataTable selectRecordsfromTableQuery(bool isAllColumns, CheckedListBox chkListBoxCols, string DataBaseName, string TableName)
{
string result = "";
StringBuilder sqlQuery = new StringBuilder("Select * FROM " + TableName);
string sqlresult = sqlQuery.ToString();
if (!isAllColumns)
{
sqlQuery = new StringBuilder("Select " );
foreach (object itemChecked in chkListBoxCols.CheckedItems)
{
string colsName = itemChecked.ToString();
sqlQuery.Append(colsName+", ");
}
sqlresult = sqlQuery.ToString().Remove(sqlQuery.Length - 2) + " FROM " + TableName;
}
SqlCommand command = new SqlCommand();
command.CommandText = sqlresult;
command.CommandType = CommandType.Text;
return objSQL.selectRecordsfromTableQuery(DataBaseName, command);
}
Заключение
Надеюсь, эта статья оказалась для вас полезной. Теперь вы можете подключаться к SQL-серверу, создавать базы данных и таблицы, наполнять их данными. В этом инструменте есть некоторые ограничения, например он поддерживает лишь 3 типа данных. Но теперь, когда у вас перед глазами есть код, вы можете сами расширить его функционал.
