private void buttonShow_Click(object sender, RoutedEventArgs e)
{
StringBuilder str = new StringBuilder();
str.AppendLine("Catalog: " + _tableCatalog);
str.AppendLine("Schema: " + _tableSchema);
str.AppendLine("Table: " + _tableName);
str.AppendLine("--------");
foreach (var item in FieldsClass.GetSelected())
{
str.AppendLine(item.Key + ": " + item.Value);
}
if (binList.SelectedItem != null)
{
str.AppendLine("Бин. поле: " + binList.SelectedItem.ToString());
}
else
{
str.AppendLine("Бин. поле: не выбрано");
}
MessageBox.Show(str.ToString());
}
private void uplBtn_Click(object sender, RoutedEventArgs e)
{
if (binList.SelectedItem != null)
{
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
dlg.Title = "Изменить файл в базе данных";
dlg.Filter = "";
StringBuilder query = new StringBuilder();
byte[] array = null;
//Show open file dialog box
bool?result = dlg.ShowDialog();
if (result == true)
{
string filename = dlg.FileName;
using (FileStream fstream = File.OpenRead(filename))
{
// преобразуем строку в байты
array = new byte[fstream.Length];
//считываем данные
fstream.Read(array, 0, array.Length);
query.Append("UPDATE [" + _tableCatalog + "]." + "[" + _tableSchema + "].[" + _tableName +
"] SET [" + binList.SelectedItem.ToString() + "] = @binaryValue WHERE ");
Dictionary <string, string> qrs = new Dictionary <string, string>();
qrs = FieldsClass.GetSelected();
int counter = 0;
foreach (var item in qrs)
{
counter++;
query.Append(item.Key + " = '" + item.Value + "'");
if (counter != qrs.Count)
{
query.Append(" AND ");
}
}
MessageBox.Show(query.ToString());
}
}
using (SqlConnection conn = new SqlConnection(connectionBox.Text + "Initial Catalog =" + _tableCatalog))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.StatementCompleted += (s, e2) => MessageBox.Show(e2.RecordCount + " строк изменено");
cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, array.Count()).Value = array;
cmd.ExecuteNonQuery();
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
finally
{
conn.Close();
}
}
}
else
{
MessageBox.Show("Не выбрано бинарное поле");
}
}
private void saveBtn_Click(object sender, RoutedEventArgs e)
{
if (binList.SelectedItem != null)
{
byteData = null;
StringBuilder query = new StringBuilder();
query.Append("SELECT " + binList.SelectedItem.ToString() + " FROM [" + _tableCatalog + "]." + "[" + _tableSchema + "].[" + _tableName + "] WHERE ");
Dictionary <string, string> qrs = new Dictionary <string, string>();
qrs = FieldsClass.GetSelected();
int counter = 0;
foreach (var item in qrs)
{
counter++;
query.Append(item.Key + " = '" + item.Value + "'");
if (counter != qrs.Count)
{
query.Append(" AND ");
}
}
MessageBox.Show(query.ToString());
using (SqlConnection sqlConn = new SqlConnection(connectionBox.Text + "Initial Catalog =" + _tableCatalog))
{
try
{
var sqlCmd = new SqlCommand(query.ToString(), sqlConn);
sqlConn.Open();
sqlCmd.StatementCompleted += (s, e2) => MessageBox.Show(e2.RecordCount + " строк выбрано");
var reader = sqlCmd.ExecuteReader();
StringBuilder table = new StringBuilder();
if (reader.HasRows)
{
int fieldCount = reader.FieldCount;
table.Append("| ");
table.AppendLine("|");
table.AppendLine("-----------------");
while (reader.Read())
{
table.Append("| ");
for (int i = 0; i < fieldCount; i++)
{
if (reader.GetValue(i) != null)
{
if (reader.GetValue(i) is byte[])
{
byteData = (byte[])reader.GetValue(i);
}
table.Append(reader.GetValue(i) + " ");
}
else
{
table.Append("null ");
}
table.AppendLine("|");
}
}
}
MessageBox.Show(table.ToString());
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
finally
{
sqlConn.Close();
}
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "";
saveFileDialog1.Title = "Сохранение файла на компьютер";
if (byteData == null)
{
MessageBox.Show("Выбранная запись не содержит бинарных данных");
}
else
if (saveFileDialog1.ShowDialog() == true)
{
File.WriteAllBytes(saveFileDialog1.FileName, byteData);
}
else
{
MessageBox.Show("Не удалось сохранить");
}
}
else
{
MessageBox.Show("Не выбрано бинарное поле");
}
}