// 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
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 override void Commit() { trx.Commit(); }
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); }