private void button_dbQuery_Click(object sender, EventArgs e)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("SELECT id,image FROM imageList WHERE id = {0} ;", textBox_imgId.Text);
cmd.Connection = myConn;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
long len = reader.GetBytes(1, 0, null, 0, 0); // ord=1 是image
byte[] buffer = new byte[len];
len = reader.GetBytes(1, 0, buffer, 0, (int)len);
MemoryStream ms = new MemoryStream(buffer);
Image img = Image.FromStream(ms);
pictureBox1.Image = img;
}
else
{
MessageBox.Show("没有查询到指定结果", "MySQL_Query");
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "MySQL_Query");
}
}
/// <summary>
/// 读取Bolb数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button19_Click(object sender, EventArgs e)
{
string connString = "server=localhost;Database=test;Uid=root;Pwd=jianglin";
MySqlConnection conn = null;
MySqlCommand cmd = null;
MySqlTransaction trans = null;
try
{
conn = new MySqlConnection(connString);
cmd = conn.CreateCommand();
conn.Open();
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select pictureBytes from pictures where Id = 1";
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
byte[] buffer = null;
if (reader.HasRows)
{
reader.Read();
long len = reader.GetBytes(0, 0, null, 0, 0);
buffer = new byte[len];
len = reader.GetBytes(0, 0, buffer, 0, (int)len);
MemoryStream stream = new MemoryStream(buffer);
System.Drawing.Image image = System.Drawing.Image.FromStream(stream);
image.Save(string.Format("../{0}.jpg", Guid.NewGuid().ToString()));
}
}
catch (Exception ex)
{
if (null != trans)
{
trans.Rollback();
}
}
finally
{
if (null != conn)
{
conn.Close();
}
}
}
} // End Sub SaveAssembly
public static void SaveAssembly2(string assemblyName, string destinationPath)
{
string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
using (System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection("context connection=true")) //Create current context connection
{
using (System.Data.Common.DbCommand cmd = new System.Data.SqlClient.SqlCommand(sql, (System.Data.SqlClient.SqlConnection)conn))
{
System.Data.Common.DbParameter param = new System.Data.SqlClient.SqlParameter("@assemblyname", System.Data.SqlDbType.NVarChar);
param.Value = assemblyName;
// param.Size = 128;
cmd.Parameters.Add(param);
using (System.IO.Stream fs = new System.IO.FileStream("logo" + "pub_id" + ".bmp", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write))
{
using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(fs))
{
long startIndex = 0;
var buffer = new byte[1024];
int bufferSize = buffer.Length;
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open(); //Open the context connetion
}
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) //Iterate through assembly files
{
string assemblyFileName = reader.GetString(0); //get assembly file name from the name (first) column
long retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
// Continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
bw.Write(buffer);
bw.Flush();
// Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize;
retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize);
} // Whend
// Write the remaining buffer.
bw.Write(buffer, 0, (int)retval);
bw.Flush();
bw.Close();
} // Whend reader.Read
} // End Using reader
} // End using bw
fs.Flush();
fs.Close();
} // End using fs
} // End using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
} // End Sub SaveAssembly2
private void DumpTable(db mydb, string database, string table, string outdir)
{
if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table))
{
return;
}
if (!Directory.Exists(outdir))
{
Directory.CreateDirectory(outdir);
}
string filename = Path.Combine(outdir, table + ".txt");
if (!overwrite)
{
if (File.Exists(filename))
{
return;
}
}
string tablename = db.GetTableName(dbprovider, database, table);
// Check number of rows
string sqlCount = _sql = $"select count(*) from {tablename}";
int rowcount;
object o = mydb.ExecuteScalarSQL(sqlCount);
if (o.GetType() == typeof(long))
{
rowcount = (int)(long)o;
}
else
{
rowcount = (int)o;
}
if (!exportempty && rowcount == 0)
{
return;
}
string sql = GetTableQuery(mydb, tablename, rowcount);
using (StreamWriter sw = new StreamWriter(filename))
{
using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql))
{
List <int> columns = new List <int>();
for (int i = 0; i < reader.FieldCount; i++)
{
string colname = reader.GetName(i);
if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname))
{
continue;
}
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile)
{
continue;
}
columns.Add(i);
}
if (sortColumns)
{
columns = columns
.OrderBy(c => reader.GetName(c))
.Select(c => c)
.ToList();
}
// Write column names
if (header)
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex)
{
continue;
}
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
sw.Write(reader.GetName(columns[i]));
}
sw.WriteLine();
}
c_c += columns.Count;
if (maxrows != -1 && rowcount > maxrows)
{
sw.WriteLine($"{rowcount} rows.");
}
else
{
// Write data
int rownum = 0;
while (reader.Read())
{
bool isFirstCol = true;
for (int i = 0; i < columns.Count; i++)
{
if (reader.GetFieldType(i) != typeof(byte[]))
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (escapecharacters)
{
sw.Write(FixValue(reader.GetValue(columns[i])));
}
else
{
sw.Write(reader.GetValue(columns[i]));
}
}
else
{
if (binaryhex)
{
if (!isFirstCol)
{
sw.Write(separator);
}
isFirstCol = false;
if (!reader.IsDBNull(i))
{
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
sw.Write(FixBinaryValue(buffer));
}
}
if (binaryfile)
{
if (!reader.IsDBNull(i))
{
string filename_data = Path.Combine(outdir, $"{table}_{reader.GetName(i)}_{rownum}.{extension}");
int length = (int)reader.GetBytes(i, 0, null, 0, 0);
byte[] buffer = new byte[length];
reader.GetBytes(i, 0, buffer, 0, length);
WriteBinaryfile(filename_data, buffer);
}
}
}
}
sw.WriteLine();
rownum++;
}
}
reader.Close();
}
}
}