// pobierz: download
// Rysunek: drawing
//take a picture from database and convert to Image type
public System.Drawing.Image GetDrawing(int idOfOID)
{
System.Drawing.Image img;
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID
byte[] buf = new byte[lo.Size()];
buf = lo.Read(lo.Size());
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
ms.Write(buf, 0, lo.Size());
img = System.Drawing.Image.FromStream(ms);
} // End Using ms
lo.Close();
trans.Commit();
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
} // End Using trans
} // End Using connection
return(img);
} // End Function GetDrawing
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans))
{
return(HasRows(cmd));
}
}
protected static string GetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand("select version from versions where name='application' limit 1;", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
return((string)cmd.ExecuteScalar());
}
}
protected static void SetDBVersion(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans, string versionNumber)
{
using (var cmd = new Npgsql.NpgsqlCommand("update versions set version = @version where name = 'application';", conn, trans))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@version", NpgsqlTypes.NpgsqlDbType.Varchar, 15).Value = versionNumber;
cmd.ExecuteNonQuery();
}
}
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans))
{
using (var r = cmd.ExecuteReader())
{
return(r.HasRows);
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
try
{
LibDuoc.AccessData ac = new LibDuoc.AccessData();
backgroundWorker1.ReportProgress(0, "Bắt đầu xử lý....");
using (Npgsql.NpgsqlCommand cmm = new Npgsql.NpgsqlCommand("select * from medibv.d_menuitem where id = '000' and id_goc ='901'", new Npgsql.NpgsqlConnection(ac.ConStr)))
{
try
{
cmm.Connection.Open();
if (cmm.ExecuteReader().HasRows)
{
cmm.CommandText = "UPDATE medibv.d_menuitem SET id='23400', id_menu='23400' WHERE id = '000' and id_goc ='901'";
cmm.ExecuteNonQuery();
cmm.CommandText = "select id,userid,right_ from medibv.d_dlogin";
Npgsql.NpgsqlDataAdapter apd = new Npgsql.NpgsqlDataAdapter(cmm);
DataTable ls_user = new DataTable();
apd.Fill(ls_user);
Npgsql.NpgsqlTransaction T = cmm.Connection.BeginTransaction();
foreach (DataRow dtr in ls_user.Rows)
{
string right = dtr["right_"].ToString();
if (right.Contains("+000"))
{
right = right.Replace("+000", "+23400");
string upd = "UPDATE medibv.d_dlogin set right_='" + right + "' where id='" + dtr["id"].ToString() + "'";
cmm.CommandText = upd;
cmm.ExecuteNonQuery();
}
}
T.Commit();
backgroundWorker1.ReportProgress(100, "Hoàn thành.");
e.Result = true;
}
}
catch
{
}
finally
{
cmm.Connection.Close();
}
}
}
catch
{
MessageBox.Show("Không kết nối được với cơ sở dữ liệu!. \nHãy thử copy tool và các file dll vào thư mục chứa chương trình Dược và chạy lại.", "Lỗi");
}
}
} // End Function GetLargeDrawing
// http://stackoverflow.com/questions/14509747/inserting-large-object-into-postgresql-returns-53200-out-of-memory-error
// https://github.com/npgsql/Npgsql/wiki/User-Manual
public int InsertLargeObject()
{
int noid;
byte[] BinaryData = new byte[123];
// Npgsql.NpgsqlCommand cmd ;
// long lng = cmd.LastInsertedOID;
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
NpgsqlTypes.LargeObjectManager manager = new NpgsqlTypes.LargeObjectManager(connection);
noid = manager.Create(NpgsqlTypes.LargeObjectManager.READWRITE);
NpgsqlTypes.LargeObject lo = manager.Open(noid, NpgsqlTypes.LargeObjectManager.READWRITE);
// lo.Write(BinaryData);
int i = 0;
do
{
int length = 1000;
if (i + length > BinaryData.Length)
{
length = BinaryData.Length - i;
}
byte[] chunk = new byte[length];
System.Array.Copy(BinaryData, i, chunk, 0, length);
lo.Write(chunk, 0, length);
i += length;
} while (i < BinaryData.Length);
lo.Close();
transaction.Commit();
} // End Try
catch
{
transaction.Rollback();
throw;
} // End Catch
return(noid);
} // End Using transaction
} // End using connection
} // End Function InsertLargeObject
public bool UpdateObject(IDBObject dbObject)
{
Npgsql.NpgsqlConnection conn = null;
Npgsql.NpgsqlTransaction trans = null;
Npgsql.NpgsqlConnection readerConn = null;
try
{
if (dbObject != null)
{
conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
object result = this.GetDataReader(dbObject.GetObjectByIdQuery(), readerConn);
if (result != null)
{
string strSql = dbObject.GetUpdateStatement();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSql);
command.Connection = conn;
trans = conn.BeginTransaction();
command.ExecuteNonQuery();
trans.Commit();
return(true);
}
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
if (readerConn != null)
{
readerConn.Close();
}
}
return(false);
}
} // End Function GetDrawing
public System.Drawing.Image GetLargeDrawing(int idOfOID)
{
System.Drawing.Image img;
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
lock (connection)
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID
byte[] buffer = new byte[32768];
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
int read;
while ((read = lo.Read(buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, read);
} // Whend
img = System.Drawing.Image.FromStream(ms);
} // End Using ms
lo.Close();
trans.Commit();
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
} // End Using trans
} // End lock connection
} // End Using connection
return(img);
} // End Function GetLargeDrawing
public void DeleteLargeObject(int noid)
{
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction())
{
NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection);
lbm.Delete(noid);
trans.Commit();
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
} // End Using trans
} // End Using connection
} // End Sub DeleteLargeObject
public bool DeleteObject(IDBObject dbObject)
{
Npgsql.NpgsqlTransaction trans = null;
Npgsql.NpgsqlConnection conn = null;
try
{
if (dbObject != null)
{
conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(dbObject.GetDeleteStatement());
command.Connection = conn;
trans = conn.BeginTransaction();
int count = command.ExecuteNonQuery();
trans.Commit();
if (count > 0)
{
return(true);
}
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(false);
}
public bool AddObject(IDBObject dbObject)
{
Npgsql.NpgsqlConnection conn = null;
Npgsql.NpgsqlConnection readerConn = null;
Npgsql.NpgsqlTransaction trans = null;
try
{
if (dbObject != null)
{
bool result = this.UpdateObject(dbObject);
if (!result)
{
//Add Object
conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection();
string strSql = dbObject.GetAddStatement();
Npgsql.NpgsqlCommand insertCommand = new Npgsql.NpgsqlCommand(strSql);
trans = conn.BeginTransaction();
insertCommand.Connection = conn;
insertCommand.ExecuteNonQuery();
trans.Commit();
object savedObject = this.GetDataReader(dbObject.GetUniqueObjectQuery(), readerConn);
if (savedObject != null)
{
Npgsql.NpgsqlDataReader reader = (Npgsql.NpgsqlDataReader)savedObject;
if (reader.HasRows)
{
while (reader.Read())
{
dbObject.SetId(reader.GetInt32(reader.GetOrdinal("id")));
}
return(true);
}
}
return(false);
}
return(result);
}
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
}
if (readerConn != null)
{
readerConn.Close();
}
}
return(false);
}
protected static void RunStatement(string statement, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null)
{
parameters = new Dictionary <string, Npgsql.NpgsqlParameter>();
}
using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (statement.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
protected static void RunScript(string script, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null)
{
parameters = new Dictionary <string, Npgsql.NpgsqlParameter>();
}
foreach (var commandText in script.Split(';'))
{
var sql = commandText + ";";
Log.Debug(d => d("Script command: {0}", sql));
using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (sql.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
Log.Debug(d => d("Script complete."));
}
protected static bool TableExists(string tableName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(TABLE_EXISTS, conn, trans))
{
cmd.Parameters.Add("@tableName", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = tableName;
using (var r = cmd.ExecuteReader())
{
return(r.HasRows);
}
}
}
protected static bool FunctionExists(string functionName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(FUNCTION_EXISTS, conn, trans))
{
cmd.Parameters.Add("@procname", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = functionName;
using (var r = cmd.ExecuteReader())
{
return(r.HasRows);
}
}
}
protected static bool TableExists(string tableName, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(TABLE_EXISTS, conn, trans))
{
cmd.Parameters.Add("@tableName", NpgsqlTypes.NpgsqlDbType.Varchar, 255).Value = tableName;
var exists = (long)cmd.ExecuteScalar();
return(exists > 0);
}
}
public PostgresTransaction(Npgsql.NpgsqlTransaction trx, PostgresConnection con)
{
this.trx = trx;
this.con = con;
}
