public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlCommand v_cmd = null;
Npgsql.NpgsqlDataReader v_reader = null;
System.Data.DataTable v_table;
System.Data.DataRow v_row;
Console.WriteLine("Exemplo PostgreSQL usando DataReader");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando Command
v_cmd = new Npgsql.NpgsqlCommand("select * from estados", v_con);
// 4) executando DataReader
v_reader = v_cmd.ExecuteReader();
// 5) criando DataTable
v_table = new System.Data.DataTable("RESULTADO");
for (int i = 0; i < v_reader.FieldCount; i++)
v_table.Columns.Add(v_reader.GetName(i), typeof(string));
// 6) alimentando DataTable
while (v_reader.Read())
{
v_row = v_table.NewRow();
for (int i = 0; i < v_reader.FieldCount; i++)
v_row[i] = v_reader[i].ToString();
v_table.Rows.Add(v_row);
}
// 7) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 8) liberando Command
if (v_cmd != null)
{
v_cmd.Cancel();
v_cmd.Dispose();
v_cmd = null;
}
// 9) liberando DataReader
if (v_reader != null)
{
v_reader.Close();
v_reader = null;
}
// 10) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public IEnumerable <CalendarEvent> GetEventsByUserId(int userid)
{
IList <CalendarEvent> userEvents = new List <CalendarEvent>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByUserIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = userid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
CalendarEvent newEvent = populateEventsFromDB(dr);
userEvents.Add(newEvent);
}
}
}
}
return(userEvents);
}
private void CreateMatrixMdb()
{
using (
var connPoints =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connPoints.Open();
using (
var pointsReader =
new Npgsql.NpgsqlCommand(
"WITH t as (SELECT zellnr, st_pointonsurface(wkb_geometry) as g FROM \"DFN2950\".\"fuerimport\") " +
"SELECT zellnr, st_X(g), st_Y(g) FROM t ORDER BY zellnr;",
connPoints).ExecuteReader())
{
using (
var connMatrix =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connMatrix.Open();
using (
var matrixReader =
new Npgsql.NpgsqlCommand(
"SELECT origin, destin, val FROM \"DFN2950\".\"iv-kfz\" " +
"WHERE val > 10 " +
"ORDER BY origin, destin;",
connMatrix).ExecuteReader())
_matrix = new ODMatrix(pointsReader, matrixReader);
}
}
}
}
public async Task create_server_async_and_table_test()
{
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
clearInstanceDirOnStop: true))
{
await server.StartAsync();
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
conn.Close();
}
}
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 + ";";
_logger.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];
_logger.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
_logger.Debug(d => d("Script complete."));
}
protected static bool IsSuperUser(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
using (var cmd = new Npgsql.NpgsqlCommand(IS_SUPERUSER, conn, trans))
{
return(HasRows(cmd));
}
}
public bool Login(string user, string password)
{
using (var connection = GetConnection())
{
Console.Write(user, password);
var sql = "SELECT * FROM users where username="******"'" + user + "' and password= '******'";
connection.Open();
using var cmd = new Npgsql.NpgsqlCommand(sql, connection);
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
UserLoginCache.idUser = dr.GetInt32(0);
UserLoginCache.Username = dr.GetString(1);
UserLoginCache.Email = dr.GetString(3);
UserLoginCache.F_name = dr.GetString(4);
UserLoginCache.L_name = dr.GetString(5);
UserLoginCache.Role = dr.GetString(6);
}
return(true);
}
else
{
return(false);
}
}
}
public IList <DdvDoctor> GetByGroupName(string groupName)
{
IList <DdvDoctor> list = new List <DdvDoctor>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT d.r_object_id, d.dss_full_name, d.dss_middle_name, d.dss_first_name, d.r_modify_date," +
" d.dss_short_name, d.r_creation_date, d.dss_last_name FROM ddv_doctor d, dm_group_users g WHERE g.dss_group_name = '{0}'" +
" AND d.r_object_id = g.dsid_doctor_id", groupName);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdvDoctor obj = new DdvDoctor();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.FullName = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.MiddleName = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.FirstName = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.ModifyDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
obj.ShortName = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
obj.LastName = reader.IsDBNull(7) ? null : reader.GetString(7);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdvDoctor> GetByConsiliumGroupId(string consiliumGroupId)
{
IList <DdvDoctor> list = new List <DdvDoctor>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT d.r_object_id, dss_full_name, dss_middle_name, dss_first_name, d.r_modify_date, dss_short_name, d.r_creation_date, dss_last_name, gm.dss_name " +
"FROM ddt_consilium_group cg, ddt_consilium_group_member gm, ddv_doctor d " +
"WHERE cg.r_object_id = gm.dsid_group and gm.dsid_doctor = d.r_object_id and cg.r_object_id = '{0}'", consiliumGroupId);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdvDoctor obj = new DdvDoctor();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.FullName = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.MiddleName = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.FirstName = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.ModifyDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
obj.ShortName = (reader.IsDBNull(8) ? null : reader.GetString(8)) + " " + (reader.IsDBNull(5) ? null : reader.GetString(5));
obj.CreationDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
obj.LastName = reader.IsDBNull(7) ? null : reader.GetString(7);
list.Add(obj);
}
}
}
return(list);
}
public DdtEkg GetByHospitalSessionAndAdmission(string hospitalSession)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format(CultureInfo.CurrentCulture, "SELECT dsid_hospitality_session, r_object_id, dsdt_analysis_date, r_modify_date, dss_parent_type, r_creation_date, dsid_parent, dsb_admission_analysis, dss_ekg, dsid_doctor, dsid_patient " +
"FROM ddt_ekg WHERE dsid_hospitality_session = '{0}'", hospitalSession);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtEkg obj = new DdtEkg();
obj.HospitalitySession = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ObjectId = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.AnalysisDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.ParentType = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.CreationDate = reader.IsDBNull(5) ? DateTime.MinValue : reader.GetDateTime(5);
obj.Parent = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.AdmissionAnalysis = reader.GetBoolean(7);
obj.Ekg = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.Doctor = reader.IsDBNull(9) ? null : reader.GetString(9);
obj.Patient = reader.IsDBNull(10) ? null : reader.GetString(10);
return(obj);
}
}
}
return(null);
}
public DdtConsiliumGroupMember GetById(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_doctor, dsid_group FROM ddt_consilium_group_member WHERE r_object_id = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtConsiliumGroupMember obj = new DdtConsiliumGroupMember();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.Doctor = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.Group = reader.IsDBNull(5) ? null : reader.GetString(5);
return(obj);
}
}
}
return(null);
}
/// <summary>
/// Get the ID of the next batch to be processed.
/// </summary>
/// <returns>The next batch ID to process, or null if there are no more events available.</returns>
private long?GetNextBatchId()
{
long?batchID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@queueName", _queueName);
cmd.Parameters.AddWithValue("@consumeName", _consumerName);
cmd.CommandText = "select * from pgq.next_batch(@queueName,@consumeName);";
if (long.TryParse(cmd.ExecuteScalar().ToString(), out long result))
{
batchID = result;
}
else
{
batchID = null;
}
}
}
return(batchID);
}
public IList <DdtDoctor> GetAll()
{
IList <DdtDoctor> list = new List <DdtDoctor>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, dss_middle_name, dss_first_name, r_modify_date, r_creation_date, dss_last_name FROM ddt_doctor";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtDoctor obj = new DdtDoctor();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.MiddleName = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.FirstName = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.CreationDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
obj.LastName = reader.IsDBNull(5) ? null : reader.GetString(5);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtValues> GetListByNameLike(string name)
{
IList <DdtValues> list = new List <DdtValues>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dss_value FROM ddt_values WHERE dss_name LIKE '{0}%'", name);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtValues obj = new DdtValues();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.Value = reader.IsDBNull(4) ? null : reader.GetString(4);
list.Add(obj);
}
}
}
return(list);
}
/// <summary>
/// get DataProtectionKey by FriendlyName
/// </summary>
/// <param name="friendlyName">Friendly Name</param>
/// <returns></returns>
public DataProtectionKey GetElement(string friendlyName)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
var sql = @"select ""FriendlyName"",""XmlData"" from public.""DataProtectionKeys"" where ""FriendlyName""=@FriendlyName;";
using (var cmd = new Npgsql.NpgsqlCommand(sql, con))
{
DataProtectionKey dataProtectionKey = null;
cmd.Parameters.Add(new Npgsql.NpgsqlParameter("@FriendlyName", friendlyName));
con.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataProtectionKey = new DataProtectionKey();
dataProtectionKey.FriendlyName = reader.GetString(0);
dataProtectionKey.XmlData = reader.GetString(1);
}
reader.Close();
}
con.Close();
return(dataProtectionKey);
}
}
}
public string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carbrandid_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
public void create_server_with_user_defined_instance_id_and_table_test()
{
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
instanceId: Guid.NewGuid(),
clearInstanceDirOnStop: true))
{
server.Start();
// assert if instance id drectory exists
Assert.True(Directory.Exists(server.InstanceDir));
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public bool Update()
{
string sQuery = "UPDATE tbm_carbrand SET " +
"brandname = @bname,dlt=@dlt,opedit=@opedit,pcedit=@pcedit,luedit=now()" +
"WHERE carbrandid = @bid";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
if (m_brandid != null)
{
cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = brandid;
}
else
{
cmd.Parameters.Add("@bid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (m_brandname != null)
{
cmd.Parameters.Add("@bname", NpgsqlTypes.NpgsqlDbType.Varchar).Value = brandname;
}
else
{
cmd.Parameters.Add("@bname", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (opedit != null)
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit;
}
else
{
cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (pcedit != null)
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit;
}
else
{
cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value;
}
if (luedit != null && luedit != DateTime.MinValue)
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit;
}
else
{
cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value;
}
cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt;
cmd.CommandText = sQuery;
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
}
public DdtKag GetById(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, dsdt_analysis_date, dsdt_end_time, r_creation_date, dsid_parent, dss_kag_manipulation, dsid_doctor, dsid_patient, dsid_hospitality_session, dsdt_start_time, r_modify_date, dss_parent_type, dss_results, dss_kag_action FROM ddt_kag WHERE r_object_id = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtKag obj = new DdtKag();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.EndTime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.Parent = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.KagManipulation = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.Doctor = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Patient = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.HospitalitySession = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.StartTime = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
obj.ModifyDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
obj.ParentType = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.Results = reader.IsDBNull(12) ? null : reader.GetString(12);
obj.KagAction = reader.IsDBNull(13) ? null : reader.GetString(13);
return(obj);
}
}
}
return(null);
}
public IList <DdtKag> GetByQuery(string sql)
{
IList <DdtKag> list = new List <DdtKag>();
using (dynamic connection = connectionFactory.GetConnection())
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtKag obj = new DdtKag();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.AnalysisDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.EndTime = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.CreationDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.Parent = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.KagManipulation = reader.IsDBNull(5) ? null : reader.GetString(5);
obj.Doctor = reader.IsDBNull(6) ? null : reader.GetString(6);
obj.Patient = reader.IsDBNull(7) ? null : reader.GetString(7);
obj.HospitalitySession = reader.IsDBNull(8) ? null : reader.GetString(8);
obj.StartTime = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
obj.ModifyDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
obj.ParentType = reader.IsDBNull(11) ? null : reader.GetString(11);
obj.Results = reader.IsDBNull(12) ? null : reader.GetString(12);
obj.KagAction = reader.IsDBNull(13) ? null : reader.GetString(13);
list.Add(obj);
}
}
}
return(list);
}
public DdtDoctor GetById(string id)
{
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT r_object_id, dss_middle_name, dss_first_name, r_modify_date, r_creation_date, dss_last_name FROM ddt_doctor WHERE r_object_id = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DdtDoctor obj = new DdtDoctor();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.MiddleName = reader.IsDBNull(1) ? null : reader.GetString(1);
obj.FirstName = reader.IsDBNull(2) ? null : reader.GetString(2);
obj.ModifyDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3);
obj.CreationDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4);
obj.LastName = reader.IsDBNull(5) ? null : reader.GetString(5);
return(obj);
}
}
}
return(null);
}
static void Main(string[] args)
{
const string PG_USER = "******";
const string CONN_STR = "Server=localhost;Port={0};User Id={1};Password=test;Database=postgres;Pooling=false";
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: true,
clearInstanceDirOnStop: true))
{
server.Start();
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
} // End Function GetConnectionString
public static void CreateUser(string userName, string password)
{
string role = "CREATE ROLE \"" + userName.Replace("\"", "\"\"") + "\" "
+ "WITH PASSWORD '" + password.Replace("'", "''") + "' "
+ "LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity'; ";
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
// https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
// https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
cmd.CommandText = "SELECT COUNT(*) FROM pg_roles WHERE rolname = '" + userName.Replace("'", "''") + "'; ";
// cmd.CommandText = "SELECT COUNT(*) FROM pg_catalog.pg_user WHERE usename = '" + userName.Replace("'", "''") + "'";
long countOfExistingUsersWithThisName = (long)cmd.ExecuteScalar();
if (countOfExistingUsersWithThisName == 0)
{
cmd.CommandText = role;
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub CreateUser
public void create_server_without_using_block()
{
var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION);
try
{
server.Start();
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
finally
{
server.Stop();
}
}
private void CreateMatrixMdb()
{
using (
var connPoints =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connPoints.Open();
using (
var pointsReader =
new Npgsql.NpgsqlCommand(
"WITH t as (SELECT zellnr, st_pointonsurface(wkb_geometry) as g FROM \"DFN2950\".\"fuerimport\") " +
"SELECT zellnr, st_X(g), st_Y(g) FROM t ORDER BY zellnr;",
connPoints).ExecuteReader())
{
using (
var connMatrix =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connMatrix.Open();
using (
var matrixReader =
new Npgsql.NpgsqlCommand(
"SELECT origin, destin, val FROM \"DFN2950\".\"iv-kfz\" " +
"WHERE val > 10 " +
"ORDER BY origin, destin;",
connMatrix).ExecuteReader())
_matrix = new ODMatrix(pointsReader, matrixReader);
}
}
}
}
/// <summary>
/// The result of the registration. 1 means success, 0 means already registered.
/// </summary>
/// <returns></returns>
private bool Register()
{
try
{
int registerID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@queueName", _queueName);
cmd.Parameters.AddWithValue("@consumeName", _consumerName);
cmd.CommandText = "select * from pgq.register_consumer(@queueName,@consumeName);";
registerID = (int)cmd.ExecuteScalar();
}
}
return(REGISTER_SUCCESS == registerID);
}
catch (Exception e)
{
_logger.LogError($"[PgMQProvider.Register.Exception]: {e.ToString()}");
return(false);
}
}
public IList <DdtConsiliumGroupMember> GetDefault()
{
IList <DdtConsiliumGroupMember> list = new List <DdtConsiliumGroupMember>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_doctor, dsid_group FROM ddt_consilium_group_member WHERE dsb_default = TRUE";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtConsiliumGroupMember obj = new DdtConsiliumGroupMember();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.Doctor = reader.IsDBNull(4) ? null : reader.GetString(4);
obj.Group = reader.IsDBNull(5) ? null : reader.GetString(5);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtCure> GetListByMedicineListId(string id)
{
IList <DdtCure> list = new List <DdtCure>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("SELECT c.r_object_id, c.r_modify_date, c.r_creation_date, c.dss_name, c.dsid_cure_type FROM ddt_cure c, ddt_issued_medicine m WHERE m.dsid_cure = c.r_object_id AND m.dsid_med_list = '{0}'", id);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtCure obj = new DdtCure();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtCure> GetAll()
{
IList <DdtCure> list = new List <DdtCure>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = "SELECT r_object_id, r_modify_date, r_creation_date, dss_name, dsid_cure_type FROM ddt_cure";
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtCure obj = new DdtCure();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4);
list.Add(obj);
}
}
}
return(list);
}
public List <DdtCure> GetByQuery(string sql)
{
List <DdtCure> list = new List <DdtCure>();
using (dynamic connection = connectionFactory.GetConnection())
{
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtCure obj = new DdtCure();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4);
list.Add(obj);
}
}
}
return(list);
}
public IList <DdtCure> GetListByTemplate(string templateName)
{
IList <DdtCure> list = new List <DdtCure>();
using (dynamic connection = connectionFactory.GetConnection())
{
String sql = String.Format("Select cure.* from ddt_values vv, ddt_cure cure where vv.dss_name like '{0}%' AND vv.dss_value = cure.dss_name", templateName);
Logger.Debug(CultureInfo.CurrentCulture, "SQL: {0}", sql);
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, connection);
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DdtCure obj = new DdtCure();
obj.ObjectId = reader.IsDBNull(0) ? null : reader.GetString(0);
obj.ModifyDate = reader.IsDBNull(1) ? DateTime.MinValue : reader.GetDateTime(1);
obj.CreationDate = reader.IsDBNull(2) ? DateTime.MinValue : reader.GetDateTime(2);
obj.Name = reader.IsDBNull(3) ? null : reader.GetString(3);
obj.CureType = reader.IsDBNull(4) ? null : reader.GetString(4);
list.Add(obj);
}
}
}
return(list);
}
private void RefreshButtonClick(object sender, RoutedEventArgs e)
{
var pgQuery = new Npgsql.NpgsqlCommand("SELECT * FROM info_log");
pgQuery.Connection = _pgConnection;
var reader = pgQuery.ExecuteReader();
ServerLogGrid.ItemsSource = reader;
ServerLogGrid.Items.Refresh();
}
public static void Analyze()
{
string sql = "ANALYZE;";
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql))
{
command.CommandTimeout = 3600;
MixERP.Net.DBFactory.DBOperations.ExecuteNonQuery(command);
}
}
public static void VacuumFull()
{
string sql = "VACUUM FULL;";
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql))
{
command.CommandTimeout = 3600;
MixERP.Net.DBFactory.DBOperations.ExecuteNonQuery(command);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.richTextBox1.Text.Trim().Length > 2)
{
if (InsInBase == true)
{
Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("update error_log set user_descr = '" + this.richTextBox1.Text.Trim() + "' where err_id = '"+errorLogId+"' ", DBExchange.Inst.connectDb);
nci.ExecuteNonQuery();
}
else
{
if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\")))
{
System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\");
}
FileStream fs = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter s = new StreamWriter(fs);
s.Close();
fs.Close();
FileStream fs1 = new FileStream(Application.StartupPath + "\\Errors\\errlog.txt", FileMode.Append, FileAccess.Write);
StreamWriter s1 = new StreamWriter(fs1);
s1.Write("Date/Time: " + DateTime.Now.ToString());
s1.Write("\nКомментарий: " + this.richTextBox1.Text.Trim());
s1.Write("\n=========================================================================================== \n\n");
s1.Close();
fs1.Close();
}
}
FormRegisterError.ActiveForm.Close();
}
private void button3_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr);
connection.Open();
Npgsql.NpgsqlTransaction et = connection.BeginTransaction();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
et.Save("apa");
et.Commit();
connection.Close();
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
Npgsql.NpgsqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (Npgsql.NpgsqlCommand) CommandsCollection[storedProcedure];
}
else
{
Npgsql.NpgsqlConnection Conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new Npgsql.NpgsqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
Npgsql.NpgsqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (Npgsql.NpgsqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT " + this.ObjectIdColumn + " ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<SharpMap.Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
System.Collections.Generic.List<SharpMap.Geometries.Geometry> features = new Collection<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
IGeometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if(geom!=null)
features.Add(geom);
}
}
}
conn.Close();
}
}
return features;
}
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."));
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(
DateTime? start = null,
DateTime? stop = null,
IDbConnection conn = null,
bool closeConnection = true)
{
string sql;
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
DataHelper.Close(conn, closeConnection);
return list;
}
public void SetupDBProviderFromConfig()
{
SetConnectionStringFromConfig();
connection = new Npgsql.NpgsqlConnection(connstring);
comm = new Npgsql.NpgsqlCommand("", connection);
}
public void SetupDBProvider(string server, string port, string user, string password, string database)
{
SetConnectionString(server, port, user, password, database);
connection = new Npgsql.NpgsqlConnection(connstring);
comm = new Npgsql.NpgsqlCommand("", connection);
}
public void Open(bool transaction)
{
connection = new Npgsql.NpgsqlConnection(connstring);
connection.Open();
comm = new Npgsql.NpgsqlCommand("", connection);
if (transaction)
{
comm.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
}
}
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 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 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();
}
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public SharpMap.Geometries.BoundingBox GetExtents()
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT EXTENT(" + this.GeometryColumn + ") FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object result = command.ExecuteScalar();
conn.Close();
if (result == System.DBNull.Value)
return null;
string strBox = (string)result;
if (strBox.StartsWith("BOX("))
{
string[] vals = strBox.Substring(4, strBox.IndexOf(")")-4).Split(new char[2] { ',', ' ' });
return SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(
double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS));
}
else
return null;
}
}
}
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();
}
}
public override int Delete(string table, string query)
{
int result = 0;
string sql = "DELETE FROM " + table;
if (!String.IsNullOrEmpty(query))
sql += " " + query;
SystemHooks.OnSQL(LocalName, sql);
lock (DatabaseLock)
{
try
{
Npgsql.NpgsqlCommand SqlCommand = new Npgsql.NpgsqlCommand(sql, this.connection);
result = SqlCommand.ExecuteNonQuery();
}
catch (Npgsql.NpgsqlException me)
{
ErrorBuffer = me.Message;
HandleError(sql);
}
}
return result;
}
public static List<Common.Models.Tasks.Task> ListChildren(
long? parentId,
List<Tuple<string, string>> filter = null,
IDbConnection conn = null,
bool closeConnection = true)
{
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
IEnumerable<DBOs.Tasks.Task> ie = null;
//filter = new List<Tuple<string, string>>();
//filter.Add(new Tuple<string, string>("status", "pending"));
if (filter != null)
{
string filterStr = null;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
filter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Item1))
cats.Add(x.Item1.ToLower());
if (!string.IsNullOrWhiteSpace(x.Item2))
tags.Add(x.Item2.ToLower());
});
filterStr = "SELECT * FROM \"task\" WHERE \"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
filterStr += ":" + parmName + ",";
});
filterStr = filterStr.TrimEnd(',');
filterStr += ")) AND \"parent_id\"";
if (parentId.HasValue && parentId.Value > 0)
{
filterStr += "=:parentid ";
parms.Add(new Npgsql.NpgsqlParameter("parentid", DbType.Int64) { Value = parentId.Value.ToString() });
}
else
filterStr += " is null ";
filterStr += "AND \"utc_disabled\" is null";
conn = DataHelper.OpenIfNeeded(conn);
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(filterStr, (Npgsql.NpgsqlConnection)conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
else
{
conn = DataHelper.OpenIfNeeded(conn);
if (parentId.HasValue)
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\"=@ParentId AND \"utc_disabled\" is null",
new { ParentId = parentId.Value });
else
ie = conn.Query<DBOs.Tasks.Task>(
"SELECT * FROM \"task\" WHERE \"parent_id\" is null AND \"utc_disabled\" is null");
foreach (DBOs.Tasks.Task dbo in ie)
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
DataHelper.Close(conn, closeConnection);
return list;
}
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;
}
}
}
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;
}
}
}
public static List<Common.Models.Tasks.Task> GetTodoListForAll(List<Common.Models.Settings.TagFilter> tagFilter, DateTime? start = null, DateTime? stop = null)
{
string sql;
List<string> cats = new List<string>();
List<string> tags = new List<string>();
List<Npgsql.NpgsqlParameter> parms = new List<Npgsql.NpgsqlParameter>();
List<Common.Models.Tasks.Task> list = new List<Common.Models.Tasks.Task>();
tagFilter.ForEach(x =>
{
if (!string.IsNullOrWhiteSpace(x.Category))
cats.Add(x.Category.ToLower());
if (!string.IsNullOrWhiteSpace(x.Tag))
tags.Add(x.Tag.ToLower());
});
sql = "SELECT * FROM \"task\" WHERE \"active\"=true AND " +
"\"id\" IN (SELECT \"task_id\" FROM \"task_tag\" WHERE \"tag_category_id\" " +
"IN (SELECT \"id\" FROM \"tag_category\" WHERE LOWER(\"name\") IN (";
cats.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND LOWER(\"tag\") IN (";
tags.ForEach(x =>
{
string parmName = parms.Count.ToString();
parms.Add(new Npgsql.NpgsqlParameter(parmName, NpgsqlTypes.NpgsqlDbType.Text) { Value = x });
sql += ":" + parmName + ",";
});
sql = sql.TrimEnd(',');
sql += ")) AND \"utc_disabled\" is null ";
if (start.HasValue)
{
parms.Add(new Npgsql.NpgsqlParameter("Start", DbType.DateTime) { Value = start.Value });
if (stop.HasValue)
{
sql += "AND \"due_date\" BETWEEN @Start AND @Stop ";
parms.Add(new Npgsql.NpgsqlParameter("Stop", DbType.DateTime) { Value = stop.Value });
}
else
{
sql += "AND \"due_date\">=@Start ";
}
}
sql += "ORDER BY \"due_date\" ASC";
using (Npgsql.NpgsqlConnection conn = (Npgsql.NpgsqlConnection)Database.Instance.GetConnection())
{
conn.Open();
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, conn))
{
parms.ForEach(x => cmd.Parameters.Add(x));
using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DBOs.Tasks.Task dbo = new DBOs.Tasks.Task();
dbo.Id = Database.GetDbColumnValue<long>("id", reader);
dbo.Title = Database.GetDbColumnValue<string>("title", reader);
dbo.Description = Database.GetDbColumnValue<string>("description", reader);
dbo.ProjectedStart = Database.GetDbColumnValue<DateTime?>("projected_start", reader);
dbo.DueDate = Database.GetDbColumnValue<DateTime?>("due_date", reader);
dbo.ProjectedEnd = Database.GetDbColumnValue<DateTime?>("projected_end", reader);
dbo.ActualEnd = Database.GetDbColumnValue<DateTime?>("actual_end", reader);
dbo.ParentId = Database.GetDbColumnValue<long?>("parent_id", reader);
dbo.IsGroupingTask = Database.GetDbColumnValue<bool>("is_grouping_task", reader);
dbo.SequentialPredecessorId = Database.GetDbColumnValue<long?>("sequential_predecessor_id", reader);
list.Add(Mapper.Map<Common.Models.Tasks.Task>(dbo));
}
}
}
}
return list;
}
public void writeLog(string _thisFunc, string _thisException, string _thisLog)
{
//if (!(System.IO.Directory.Exists(Application.StartupPath + "\\Errors\\")))
//{
// System.IO.Directory.CreateDirectory(Application.StartupPath + "\\Errors\\");
// System.IO.File.Create(Application.StartupPath + "\\Errors\\errlog.txt");
//}
if (!(System.IO.Directory.Exists(di.FullName + "\\Medex\\Errors\\")))
{
System.IO.Directory.CreateDirectory(di.FullName + "\\Medex\\Errors\\");
if (!(System.IO.File.Exists(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt")))
{
System.IO.File.Create(di.FullName + "\\Medex\\Errors\\errlog_"+DateTime.Now.ToShortDateString()+".txt");
}
}
if (_thisException == "There is already an open DataReader associated with this Command which must be closed first.")
{
DBExchange.Inst.connectDb.Close();
DBExchange.Inst.connectDb.Dispose();
DBExchange.Inst.connectDb.Open();
}
else
{
try
{
string s3 = "";
s3+= "Program version: " + DBExchange.Inst.versionNumber.ToString();
s3+="\nFunction: " + _thisFunc;
s3+="\nMessage: " + _thisException;
s3+="\nStackTrace: " + _thisLog;
s3+= "\n=========================================================================================== \n\n";
Npgsql.NpgsqlCommand nci = new Npgsql.NpgsqlCommand("insert into error_log (descr, user_id) values ('"+s3+"','"+DBExchange.Inst.dbUsrId+"') returning err_id ", DBExchange.Inst.connectDb);
int error_id = (int)nci.ExecuteScalar();
System.Windows.Forms.MessageBox.Show("ВНИМАНИЕ ВОЗНИКЛА НЕПРЕДВИДЕННАЯ ОШИБКА \nОшибка зарегистрирована в базе данных \nВы можете сообщить о своих действиях на сайте http://medx.spb.ru/issues \nили описать свои деействия в следующей форме" );
Templates.FormRegisterError fre = new Templates.FormRegisterError(true,error_id);
fre.ShowDialog();
}
catch
{
FileStream fs = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter s = new StreamWriter(fs);
s.Close();
fs.Close();
FileStream fs1 = new FileStream(di.FullName + "\\Medex\\Errors\\errlog_" + DateTime.Now.ToShortDateString() + ".txt", FileMode.Append, FileAccess.Write);
StreamWriter s1 = new StreamWriter(fs1);
s1.Write("Date/Time: " + DateTime.Now.ToString());
s1.Write("\nProgram version: " + DBExchange.Inst.versionNumber.ToString());
s1.Write("\nFunction: " + _thisFunc);
s1.Write("\nMessage: " + _thisException);
s1.Write("\nStackTrace: " + _thisLog);
s1.Write("\n=========================================================================================== \n\n");
s1.Close();
fs1.Close();
Templates.FormRegisterError fre = new Templates.FormRegisterError(false,0);
System.Windows.Forms.MessageBox.Show(_thisFunc + " \n" + _thisException + " \n" + _thisLog);
fre.ShowDialog();
}
}
}
public override void Commit()
{
SystemHooks.OnSQL(LocalName, "commit;");
Npgsql.NpgsqlCommand c = new Npgsql.NpgsqlCommand("commit;", this.connection);
c.ExecuteNonQuery();
}
