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 async Task<Subdivision> LoadById(int id)
{
Subdivision result = null;
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions where id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result = subdivision;
}
}
return result;
}
public async Task<IEnumerable<Subdivision>> Load100()
{
var result = new List<Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result.Add(subdivision);
}
}
return result;
}
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 static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlDataAdapter v_adapter = null;
System.Data.DataTable v_table;
Console.WriteLine("Exemplo PostgreSQL usando DataAdapter");
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 DataAdapter
v_adapter = new Npgsql.NpgsqlDataAdapter("select * from estados", v_con);
// 4) instanciando DataTable
v_table = new System.Data.DataTable("RESULTADO");
// 5) alimentando DataTable
v_adapter.Fill(v_table);
// 6) 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
{
// 7) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public IActionResult Index()
{
string connectionString = configuration["Data:LinuxPGConnection:ConnectionString"];
try
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connectionString))
{
Trace.WriteLine("NpgsqlConnection created");
conn.Open();
Trace.WriteLine("NpgsqlConnection opened");
using (Npgsql.NpgsqlCommand cmd = conn.CreateCommand())
{
Trace.WriteLine("NpgsqlCommand created");
cmd.CommandText = @"select ""Email"" from ""AspNetUsers"" Limit 1";
var email = cmd.ExecuteScalar();
Trace.WriteLine(email, "Email from npsql");
ViewData.Add("SimpleRequestEmail", email);
}
}
}
catch (Exception ex)
{
ViewData.Add("SimpleRequestException", ex.ToString());
}
Regex re = new Regex(@"Password\s*=\s*([^;]+)\s*;");
ViewData.Add("ConnectionString", re.Replace(connectionString, "Password=*******;") );
bool isConnected = false;
string additionalInfo = "";
try
{
int cnt = context.Users.Count();
isConnected = true;
additionalInfo = string.Format("Users count: {0}", cnt);
}
catch(Exception ex)
{
additionalInfo = "exception: " + ex.ToString();
}
ViewData.Add("ConnectionStatus", isConnected ? "Connected" : "Error");
ViewData.Add("ConnectionInfo", additionalInfo);
return View();
}
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();
}
public async Task<List<LcboStore>> GetLcboStores()
{
var result = new List<LcboStore>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, name, ST_AsGeoJSON(location) as location, beer_volume, wine_volume, spirits_volume from stores limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
public async Task<List<LcboStore>> StoresInArea(string geoJson)
{
var result = new List<LcboStore>();
var query = $@"select s.id, s.name, s.beer_volume, s.wine_volume, s.spirits_volume
from stores s
where ST_Intersects(ST_GeomFromGeoJSON('{geoJson}'), s.location)";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
private void ConnectButtonClick(object sender, RoutedEventArgs e)
{
if (!_isConnected)
{
var pg_con = new Npgsql.NpgsqlConnectionStringBuilder();
pg_con.Host = "127.0.0.1";
pg_con.UserName = "******";
pg_con.Password = "******";
pg_con.Database = "bbrother";
_pgConnection = new Npgsql.NpgsqlConnection(pg_con.ConnectionString);
_pgConnection.Open();
_isConnected = true;
ConnectButton.Content = "Diconnect";
}
else
{
_isConnected = false;
_pgConnection.Close();
ConnectButton.Content = "Connect to DB...";
}
}
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>
/// 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(new SharpMap.Geometries.BoundingBox(
double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS)));
}
else
{
return(null);
}
}
}
}
/// <summary>
/// creating queue
/// </summary>
/// <returns></returns>
private bool CreateQueue()
{
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.CommandText = "select * from pgq.create_queue(@queueName);";
registerID = (int)cmd.ExecuteScalar();
}
}
return(REGISTER_SUCCESS == registerID);
}
catch (Exception e)
{
_logger.LogError($"[PgMQProvider.CreateQueue.Exception]: {e.ToString()}");
return(false);
}
}
private int GetTotalCalls(DateTime startdate, DateTime enddate, int userid)
{
int callscount;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetTotalCallsQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("startdate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Prepare();
command.Parameters[0].Value = userid;
command.Parameters[1].Value = startdate;
command.Parameters[2].Value = enddate;
int dr;
dr = Convert.ToInt32(command.ExecuteScalar());
callscount = dr;
}
}
return(callscount);
}
public static DataTable Npgsql_Data(String sql)
{
//參考資料
//https://www.npgsql.org/doc/index.html
String connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
DataTable dt = new DataTable();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstr))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
//執行不Query的方式
//cmd.ExecuteNonQuery();
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
}
}
return(dt);
}
public void create_server_without_version_suffix()
{
using (var server = new MysticMind.PostgresEmbed.PgServer(
"10.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
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();
}
}
public List <DistanciaViagemDTO> GetDistanciaViagem(int radarInicio, int radarFinal)
{
List <DistanciaViagemDTO> listaRetorno = new List <DistanciaViagemDTO>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText =
"select br0.codigo as RadarInicio, " +
"br1.codigo as RadarFinal, " +
"ST_Distance(ST_Transform(concat('SRID=4326;POINT(', cast(br0.lat as varchar(20)), ' ', cast(br0.lon as varchar(20)), ')')::geometry, 3857), " +
"ST_Transform(concat('SRID=4326;POINT(', cast(br1.lat as varchar(20)), ' ', cast(br1.lon as varchar(20)), ')')::geometry, 3857)) * cosd(42.3521) as distancia " +
"from base_radares_lat_lon br0 inner join base_radares_lat_lon br1 " +
"on br1.codigo = " + radarFinal + " where br0.codigo = " + radarInicio + "; ";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
DistanciaViagemDTO ett = new DistanciaViagemDTO();
ett.codigoRadarInicio = Convert.ToInt32(dr["RadarInicio"]);
ett.codigoRadarFinal = Convert.ToInt32(dr["RadarFinal"]);
ett.distancia = Convert.ToDecimal(dr["distancia"]);
listaRetorno.Add(ett);
}
}
return(listaRetorno);
}
public IQueryable <Domain.Lead> LeadByStatus(string status, int userId)
{
fakeLeads.Clear();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(LeadByStatusSelectQuery, conn))
{
command.Parameters.AddWithValue("status", status);
command.Parameters.AddWithValue("userId", userId);
command.Prepare();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Lead newLead = populateLeadFromDB(dr);
fakeLeads.Add(newLead);
}
}
}
}
return(fakeLeads.AsQueryable());
}
public CalendarEvent GetEventByAppointmentID(string id)
{
CalendarEvent currentEvent = new CalendarEvent();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByAppointmentIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = id;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
currentEvent = populateEventsFromDB(dr);
}
}
}
}
return(currentEvent);
}
public IEnumerable <Equipment> GetEquipmentByType(string equipmenttype)
{
IList <Domain.Equipment> equiptype = new List <Domain.Equipment>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EquipmentTypeQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("equiptype", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = equipmenttype;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Equipment newequip = populateEquipmentFromDB(dr);
equiptype.Add(newequip);
}
}
}
}
return(equiptype);
}
public string GetCalendarColorForUser(int userid)
{
string color = "";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetCalendarColorQuery, 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())
{
color = dr[0].ToString();
}
}
}
}
return(color);
}
public IEnumerable <PracticeHeadCountDto> GetPracticeWiseHeadCountPostgres()
{
List <PracticeHeadCountDto> records = new List <PracticeHeadCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_PRACTICE_WISE_HEAD_COUNT;
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new PracticeHeadCountDto
{
HeadCount = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
Practice = res.IsDBNull(1) == false ? res.GetString(1) : "",
PracticeID = res.IsDBNull(0) == false ? res.GetInt32(0) : -1,
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
/// <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)
{
SharpMap.Geometries.Geometry 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 all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
List<IGeometry> features = new List<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 sharpmap_tempgeometry ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName,col.DataType,col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach(System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <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 bool IsSuperUser()
{
using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
var value = IsSuperUser(conn, trans);
trans.Commit();
return value;
}
}
}
public async Task<Feature> BoundaryGeoJson(int subdivId)
{
var result = new Feature(new Point(new Position()));
var query = @"select ST_AsGeoJSON(boundry, 15, 4) as boundary
from subdivisions
where id = @subdivId";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
cmd.Parameters.AddWithValue("@subdivId", subdivId);
var boundary = await cmd.ExecuteScalarAsync() as string;
if (boundary.Contains("\"type\":\"MultiPolygon\""))
{
var multipolygon = JsonConvert.DeserializeObject<MultiPolygon>(boundary);
result = new Feature(multipolygon);
}
else if (boundary.Contains("\"type\":\"Polygon\""))
{
var polygon = JsonConvert.DeserializeObject<Polygon>(boundary);
result = new Feature(polygon);
}
}
}
return result;
}
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);
}
}
public void SaveLeadAccessRecord(string path)
{
Domain.ImportedLead[] parsedLeads = Helper.ParseCSVFile(path);
string sql = "INSERT INTO lead (companyname, contact1title, contact1firstname, contact2title, contact2firstname, primaryphonenumber, additionalphonenumber, numbertocall, faxnumber, primaryemailaddress, additionalemailaddress, websitelink, streetaddress1, streetaddress2, city, state, zipcode, zonenumber, status, assignedsauserid, callbackdate, ignoreddate, assignedaauserid, suppressed, contact1lastname, contact2lastname, ignored, dateimported, primaryphonechecked) VALUES (:companyname, :contact1title, :contact1firstname, :contact2title, :contact2firstname, :primaryphonenumber, :additionalphonenumber, :numbertocall, :faxnumber, :primaryemailaddress, :additionalemailaddress, :websitelink, :streetaddress1, :streetaddress2, :city, :state, :zipcode, :zonenumber, :status, :assignedsauserid, :callbackdate, :ignoreddate , :assignedaauserid, :suppressed, :contact1lastname, :contact2lastname, :ignored, :dateimported, :primaryphonechecked)";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, conn);
command.Parameters.Add(new Npgsql.NpgsqlParameter("companyname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("numbertocall", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("faxnumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("websitelink", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress1", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress2", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zonenumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("callbackdate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignoreddate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedaauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("suppressed", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignored", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateimported", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonechecked", NpgsqlTypes.NpgsqlDbType.Boolean));
//int leadid = command.Parameters.Count;
// System.Console.WriteLine(leadid);
//System.Console.WriteLine(parsedData.Count);
command.Prepare();
foreach (Domain.ImportedLead parsedLead in parsedLeads)
{
int zone = _ZoneRepository.GetZoneByZipcode(parsedLead.ZipCode);
command.Parameters["@companyname"].Value = parsedLead.CompanyName;
command.Parameters["@contact1firstname"].Value = parsedLead.ContactFirstName;
command.Parameters["@contact1lastname"].Value = parsedLead.ContactLastName;
command.Parameters["@contact1title"].Value = parsedLead.Title;
command.Parameters["@streetaddress1"].Value = parsedLead.Address;
command.Parameters["@city"].Value = parsedLead.City;
command.Parameters["@state"].Value = parsedLead.State;
command.Parameters["@zipcode"].Value = parsedLead.ZipCode;
command.Parameters["@primaryphonenumber"].Value = parsedLead.PrimaryPhoneNumber;
command.Parameters["@additionalphonenumber"].Value = parsedLead.AdditionalPhoneNumber;
command.Parameters["@faxnumber"].Value = parsedLead.FaxNumber;
command.Parameters["@primaryemailaddress"].Value = parsedLead.EmailAddress;
command.Parameters["@status"].Value = "Cold Lead";
command.Parameters["@assignedsauserid"].Value = 0;
command.Parameters["@assignedaauserid"].Value = 0;
command.Parameters["@zonenumber"].Value = zone;
command.Parameters["@ignored"].Value = false;
command.Parameters["@dateimported"].Value = DateTime.Now;
command.Parameters["@primaryphonechecked"].Value = true;
long x = command.ExecuteNonQuery();
}
}
Lead lead = new Lead();
// If it's a new lead, just add it to the list
if (lead.LeadId == 0)
{
lead.LeadId = counter;
counter += 1;
fakeLeads.Add(lead);
}
else if (fakeLeads.Count(row => row.LeadId == lead.LeadId) == 1)
{
//This is an update. Remove old one, insert new one
DeleteLeadAccessRecord(lead);
fakeLeads.Add(lead);
}
}
void connection(String a)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
connection.Open();
String chart_select = "SELECT title FROM movie WHERE id = " + a;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(chart_select, connection);
connection.Close();
IMDb imdb = new IMDb("The Godfather", true);
String tittl = imdb.Id;
Label1.Text = Label1.Text + imdb.Title;
}
}
public void SaveAppointmentSheetFromCalendar(AppointmentSheet appointment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (appointment.AppointmentSheetId > 0)
{
query = CalendarAppointmentUpdateQuery;
isUpdate = true;
}
else
{
query = AppointmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateofappointment", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastupdated", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("street", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdateto", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("event_reference", NpgsqlTypes.NpgsqlDbType.Text));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = appointment.City;
command.Parameters[1].Value = appointment.DayOfAppointment;
command.Parameters[2].Value = appointment.LastUpdated;
command.Parameters[3].Value = appointment.State;
command.Parameters[4].Value = appointment.Street;
command.Parameters[5].Value = appointment.ZipCode;
command.Parameters[6].Value = appointment.AppointmentDateFrom;
command.Parameters[7].Value = appointment.AppointmentDateTo;
command.Parameters[8].Value = appointment.Event_Reference;
//command.Parameters[10].Value = card.Creator;
if (isUpdate)
{
command.Parameters[9].Value = appointment.AppointmentSheetId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public void PgSql()
{
PgSqlCommandExecutor.Configure();
using (var connection = new Npgsql.NpgsqlConnection("host=localhost;username=postgres;password=Norther;database=Test"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var testData = new TestTable() {
Foo = "Foo",
Name = "Hello",
TheDate = DateTime.Now
};
TestTable result;
using (Time(elapsed => Console.WriteLine("Insert : " + elapsed)))
{
result = connection.Insert(testData, transaction);
}
Console.WriteLine("Database generated UID : " + result.TheSecondId);
List<TestTable> bulkResult;
using (Time(elapsed => Console.WriteLine("Bulk insert : " + elapsed)))
{
bulkResult = connection.Insert(Enumerable.Range(0, 100).Select(i => new TestTable() { Name = i.ToString() }), transaction).ToList();
Assert.AreEqual(100, bulkResult.Count());
}
using (Time(elapsed => Console.WriteLine("Bulk update : " + elapsed)))
{
bulkResult = connection.Update(
bulkResult.Select(item => item.SetProperties(new TestTable() { Name = item.Name + " strongly typed mutation" })),
transaction).ToList();
}
using (Time(elapsed => Console.WriteLine("Update : " + elapsed)))
{
var newDate = DateTime.Now.AddYears(-50);
var update = connection.Update(
result.SetProperties(
new { TheDate = newDate }
),
transaction
);
Assert.AreEqual(newDate.Year, update.TheDate.Year);
}
using (Time(elapsed => Console.WriteLine("FindById : " + elapsed)))
{
Assert.IsNotNull(connection.FindById<TestTable>(new { result.Id, result.TheSecondId }, transaction));
}
transaction.Commit();
}
}
}
public List <BillabilityWiseAllocationDetailDto> GetAllocationEntriesByAllocationTypeFromPostgres(int allocationType)
{
List <BillabilityWiseAllocationDetailDto> allocationDetailDtos = new List <BillabilityWiseAllocationDetailDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_VALID_ALLOCATIONS;
if (allocationType == -1)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_ALL_NON_ALLOCATED_DELIVERY_BU_RESOURCES;
}
else if (allocationType == -2)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_ALL_NON_ALLOCATED_OTHER_BU_RESOURCES;
}
else if (allocationType == -5)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_BENCH.Replace("__BENCH_CATEGORY__", "75");
}
else if (allocationType == -6)
{
qry = PostgresSqlQueries.GET_ALLOCATION_DETAIL_FOR_BENCH.Replace("__BENCH_CATEGORY__", "74");
}
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
qry = qry.Replace("__ALLOCATION_TYPE_ID__", allocationType.ToString());
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
DateTime?nullDate = null;
int? nullInt = null;
if (res.HasRows)
{
while (res.Read())
{
allocationDetailDtos.Add(new BillabilityWiseAllocationDetailDto
{
AccountName = res.IsDBNull(15) == false ? res.GetString(15) : "",
AllocationEndDate = res.IsDBNull(12) == false ? res.GetDateTime(12) : nullDate,
AllocationStartDate = res.IsDBNull(11) == false ? res.GetDateTime(11) : nullDate,
AllocationType = res.IsDBNull(6) == false ? res.GetString(6) : "",
AllocationTypeID = res.IsDBNull(5) == false ? res.GetInt32(5) : nullInt,
BusinessUnit = res.IsDBNull(8) == false ? res.GetString(8) : "",
BusinessUnitID = res.IsDBNull(7) == false ? res.GetInt32(7) : nullInt,
Comments = res.IsDBNull(18) == false ? res.GetString(18) : "",
EmployeeEntryID = res.IsDBNull(0) == false ? (int?)res.GetInt64(0) : nullInt,
EmployeeID = res.IsDBNull(1) == false ? res.GetString(1) : "",
EmployeeName = res.IsDBNull(2) == false ? res.GetString(2) : "",
PrimarySkills = res.IsDBNull(3) == false ? res.GetString(3) : "",
ProjectID = res.IsDBNull(13) == false ? (int?)res.GetInt64(13) : nullInt,
ProjectManager = res.IsDBNull(17) == false ? res.GetString(17) : "",
ProjectManagerID = res.IsDBNull(16) == false ? res.GetInt32(16) : nullInt,
ProjectName = res.IsDBNull(14) == false ? res.GetString(14) : "",
ProjectType = "",
ProjectTypeID = 0,
SecondarySkills = res.IsDBNull(4) == false ? res.GetString(4) : ""
});
}
}
}
catch (Exception exp) { }
finally
{
con.Close();
con.Dispose();
}
return(allocationDetailDtos);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers(options =>
{
options.ReturnHttpNotAcceptable = true;
options.Filters.Add(new ConsumesAttribute("application/json"));
options.Filters.Add(new ProducesAttribute("application/json"));
options.Filters.Add(new ProducesResponseTypeAttribute(Status400BadRequest));
options.Filters.Add(new ProducesResponseTypeAttribute(Status500InternalServerError));
});
services.AddSwaggerGen(setupAction =>
{
setupAction.SwaggerDoc(
"LibraryOpenAPISpecification",
new OpenApiInfo
{
Title = "Cricinfo.API - Documentation",
Version = Configuration["APIVersion"]
});
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
setupAction.IncludeXmlComments(xmlPath);
setupAction.UseInlineDefinitionsForEnums();
});
services.AddScoped <ICricInfoCommandService>(sp =>
{
return(new CricInfoCommandService <MatchController>(
Configuration.GetConnectionString("PostgresConnection"),
sp.GetRequiredService <ILogger <MatchController> >()));
});
services.AddScoped <ICricInfoQueryService>(sp =>
{
return(new CricInfoQueryService <MatchController>(
Configuration.GetConnectionString("PostgresConnection"),
sp.GetRequiredService <ILogger <MatchController> >()));
});
services.AddHealthChecks()
.AddCheck("API Healthcheck", () => HealthCheckResult.Healthy())
.AddCheck("SQL Healthcheck", () =>
{
using var conn = new Npgsql.NpgsqlConnection(Configuration.GetConnectionString("PostgresConnection"));
try
{
conn.Open();
return(HealthCheckResult.Healthy());
}
catch (Npgsql.NpgsqlException)
{
return(HealthCheckResult.Degraded());
}
});
services.AddHealthChecksUI(setupSettings =>
{
setupSettings.AddHealthCheckEndpoint("Healthchecks", Configuration["HealthcheckEndpoint"]);
setupSettings.SetEvaluationTimeInSeconds(300);
})
.AddInMemoryStorage();
}
public void UpdateRow(string jsonStr)
{
Data.Clear();
var entity = JsonConvert.DeserializeObject <DataBaseConfig>(jsonStr);
if (string.IsNullOrEmpty(entity.ConnectionStrings))
{
#region 配置数据库连接串
switch (entity.DataType)
{
case FreeSql.DataType.MySql:
entity.ConnectionStrings = $"Data Source={entity.ServerIP};Port={entity.Port};User ID={entity.UserName};Password={entity.UserPass};Initial Catalog={(string.IsNullOrEmpty(entity.DataBaseName) ? "mysql" : entity.DataBaseName)};Charset=utf8;SslMode=none;Max pool size=5";
break;
case FreeSql.DataType.SqlServer:
entity.ConnectionStrings = $"Data Source={entity.ServerIP},{entity.Port};Initial Catalog={entity.DataBaseName};User ID={entity.UserName};Password={entity.UserPass};Pooling=true;Max Pool Size=5";
break;
case FreeSql.DataType.PostgreSQL:
entity.ConnectionStrings = $"Host={entity.ServerIP};Port={entity.Port};Username={entity.UserName};Password={entity.UserPass};Database={(string.IsNullOrEmpty(entity.DataBaseName) ? "postgres" : entity.DataBaseName)};Pooling=true;Maximum Pool Size=5";
break;
case FreeSql.DataType.Oracle:
entity.ConnectionStrings = $"user id={entity.UserName};password={entity.UserPass};data source=//{entity.ServerIP}:{entity.Port}/{entity.DataBaseName};Pooling=true;Max Pool Size=5";
break;
}
#endregion
}
else if (string.IsNullOrEmpty(entity.DataBaseName))
{
switch (entity.DataType)
{
case FreeSql.DataType.Oracle:
entity.DataBaseName = Regex.Match(entity.ConnectionStrings, @"user id=([^;]+)", RegexOptions.IgnoreCase).Groups[1].Value;
break;
case FreeSql.DataType.MySql:
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(entity.ConnectionStrings))
{
conn.Open();
entity.DataBaseName = conn.Database;
conn.Close();
}
break;
case FreeSql.DataType.SqlServer:
using (var conn = new System.Data.SqlClient.SqlConnection(entity.ConnectionStrings))
{
conn.Open();
entity.DataBaseName = conn.Database;
conn.Close();
}
break;
case FreeSql.DataType.PostgreSQL:
using (var conn = new Npgsql.NpgsqlConnection(entity.ConnectionStrings))
{
conn.Open();
entity.DataBaseName = conn.Database;
conn.Close();
}
break;
}
}
Curd.DataBase.InsertOrUpdate(entity);
var _list = Curd.DataBase.Select.ToList();
Data.AddRange(_list);
Data.SaveChanges();
//InvokeJS("page.$confirm('添加完成, 是否继续?', '提示', {confirmButtonText: '确定',cancelButtonText: '取消',type: 'warning'})");
}
public WindowArchiveChart(List <string> colSelect, List <string> colTableName)
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
try
{
conn.Open();
PlotModel plotModel = new PlotModel();
if (colSelect.Count >= 2)
{
int count = 0;
while (true)
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[count], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[count]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
count++;
if (count == colSelect.Count)
{
break;
}
}
}
else
{
DataTable dataTable = new DataTable();
Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(colSelect[0], conn);
adapter.Fill(dataTable);
adapter.Dispose();
OxyPlot.Series.LineSeries line = new OxyPlot.Series.LineSeries()
{
CanTrackerInterpolatePoints = false,
Title = string.Format(colTableName[0]),
Smooth = false,
TrackerFormatString = "{0}" + Environment.NewLine + "{3} {4}" + Environment.NewLine + "{1} {2:dd/MM/yyyy HH:mm:ss}"
};
DataView data = dataTable.DefaultView;
foreach (DataRowView rowView in data)
{
DataRow row = rowView.Row;
DateTime dt = (DateTime)row.ItemArray[1];
double d = Convert.ToDouble(row.ItemArray[0]);
line.Points.Add(new DataPoint(OxyPlot.Axes.DateTimeAxis.ToDouble(dt), d));
}
plotModel.Series.Add(line);
}
plotModel.LegendTitle = "Легенда";
plotModel.LegendOrientation = LegendOrientation.Horizontal;
plotModel.LegendPlacement = LegendPlacement.Outside;
plotModel.LegendPosition = LegendPosition.TopRight;
plotModel.LegendBackground = OxyColor.FromAColor(200, OxyColors.White);
plotModel.LegendBorder = OxyColors.Black;
var dateAxis = new OxyPlot.Axes.DateTimeAxis(OxyPlot.Axes.AxisPosition.Bottom, "Дата", "dd/MM HH:mm")
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, IntervalLength = 65
};
plotModel.Axes.Add(dateAxis);
var valueAxis = new OxyPlot.Axes.LinearAxis(AxisPosition.Left)
{
MajorGridlineStyle = LineStyle.Solid, MinorGridlineStyle = LineStyle.Solid, Title = "Значение"
};
valueAxis.MaximumPadding = 0.3;
valueAxis.MinimumPadding = 0.3;
plotModel.Axes.Add(valueAxis);
Plot = new Plot();
Plot.SetValue(Grid.RowProperty, 1);
Plot.Model = plotModel;
Plot.MinHeight = 100;
Plot.MinWidth = 100;
GridMain.Children.Add(Plot);
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public object GetNewConnection()
{
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connectionString);
conn.Open();
return(conn);
}
static void Main(string[] args)
{
Log.Logger = new LoggerConfiguration()
.WriteTo.Console()
.CreateLogger();
Log.Information("Start testing PostgreConfigurer");
var workDirectory = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).FullName, "postgres-oracle");
string username;
SecureString password;
Console.WriteLine("Username: "******"Enter password: "******"force");
if (isForceInstall || conf.IsInstalled)
{
if (conf.IsRunning)
{
conf.StopPostgres();
}
conf.InstallPostgreSql();
ps.InstallPostgis();
}
var sudoUser = new Npgsql.NpgsqlConnectionStringBuilder
{
Host = "127.0.0.1",
Database = "postgres",
Username = "******",
Password = "******"
};
if (!conf.IsRunning)
{
conf.RunPostgres();
}
using (var contex = new Contex())
{
bool isDbReady = !args.Any(x => x == "full_recreate");
//FIXME: Не работает
/*try {
* isDbReady &= contex.Database.CanConnect ();
* } catch {
* isDbReady = false;
* }*/
if (!isDbReady)
{
Log.Information("Need recreation of database");
using (var connection = new Npgsql.NpgsqlConnection(sudoUser.ToString()))
{
connection.Open();
connection
.DropDatabase("TestDatabase", true)
.CreateUser(new NpgsqlUtils.CreateUserOptions
{
Username = "******",
Password = "******"
})
.CreateDatabase(new NpgsqlUtils.CreateDatabaseOptions
{
Name = "TestDatabase",
Owner = "pilad",
});
contex.Database.EnsureCreated();
connection.ChangeDatabase("testdatabase");
connection.Execute(ps.ActivatePostgisSql());
//TODO: Is nesessary?
connection.Close();
}
Log.Information("Recreating full scheme contex...");
}
contex.Works.Add(new Work {
first = "123", second = "dsa"
});
contex.SaveChanges();
foreach (var work in contex.Works)
{
Log.Information("{first} {second}", work.first, work.second);
}
}
//conf.StopPostgres();
System.Threading.Thread.Sleep(1000);
}
public static void InitializeRetroDRY(bool integrationTestMode = false)
{
//load configuration (for this sample, we are using a separate settings file for retrodry features)
var configBuilder = new ConfigurationBuilder().SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
.AddJsonFile("appsettings_dev.json");
var config = configBuilder.Build();
var dbConnection = config["Database"];
Globals.ConnectionString = dbConnection;
//This will tell RetroDRY how to access your database
DbConnection dbResolver(int databaseNumber)
{
var db = new Npgsql.NpgsqlConnection(dbConnection);
db.Open();
return(db);
}
//build data dictionary from annotations
var ddict = new DataDictionary();
ddict.AddDatonsUsingClassAnnotation(typeof(Startup).Assembly);
//sample to override data dictionary. In a real app the overrides might come from setup tables or be hardcoded, and this process
//could be moved to a separate class
//ddict.DatonDefs["Customer"] = new DatonDef
//{
// ...
//};
//ddict.DatonDefs["Customer"].MainTableDef.Prompt["de"] = "..,";
//sample custom values (dynamic columns that are not declared in the database or at compile time)
ddict.DatonDefs["Sale"].MainTableDef.AddCustomColum("CouponCode", typeof(string), Constants.TYPE_NSTRING);
ddict.DatonDefs["Sale"].MainTableDef.AddCustomColum("IsRushOrder", typeof(bool?), Constants.TYPE_NBOOL).SetPrompt("", "Is Rush Order");
ddict.DatonDefs["Sale"].MainTableDef.AddCustomColum("IsInternalSale", typeof(bool), Constants.TYPE_BOOL);
//sample default values initializer
ddict.DatonDefs["Customer"].Initializer = Initializers.InitializeCustomer;
//start up RetroDRY
ddict.FinalizeInheritance();
Globals.Retroverse?.Dispose();
Globals.Retroverse = new Retroverse(SqlFlavorizer.VendorKind.PostgreSQL, ddict, dbResolver, integrationTestMode: integrationTestMode)
{
ViewonPageSize = 50
};
//error reporting; In a real app you would send this to your logging destinations
Globals.Retroverse.Diagnostics.ReportClientCallError = msg => Console.WriteLine(msg);
//sample SQL overide
Globals.Retroverse.OverrideSql("Customer", new CustomerSql());
//sample exception text rewriter
Globals.Retroverse.CleanUpSaveException = (user, ex) =>
{
if (ex.Message.Contains("violates foreign key constraint"))
{
return("Cannot delete record because other records depend on it.");
}
return(ex.Message);
};
//only for integration testing
if (integrationTestMode)
{
InitializeRetroDRYIntegrationTesting(ddict, dbResolver);
}
}
public async Task<List<Subdivision>> SubdivisionsAndVolumes()
{
var result = new List<Subdivision>();
var query = @"select id, population, name, ST_AsGeoJSON(boundry) as boundary, beer_volume, wine_volume, spirits_volume
from subdivisions
where province = 'Ontario'";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
result.Add(new Subdivision(reader));
}
}
return result;
}
public static DataContext Create(string connectionString)
{
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connectionString);
conn.Open();
return(new DataContext(conn));
}
public WindowArchive()
{
InitializeComponent();
AppWPF app = (AppWPF)Application.Current;
MainWindow window = (MainWindow)Application.Current.MainWindow;
if (app.ConfigProgramBin.UseDatabase)
{
//SqlConnectionStringBuilder Sqlbuilder = new SqlConnectionStringBuilder();
//Sqlbuilder.DataSource = app.ConfigProgramBin.SQLServerName;
//Sqlbuilder.InitialCatalog = app.ConfigProgramBin.SQLDatabaseName;
//if (((AppWPF)Application.Current).ConfigProgramBin.SQLSecuritySSPI)
//{
// Sqlbuilder.IntegratedSecurity = true;
//}
//else
//{
// Sqlbuilder.UserID = app.ConfigProgramBin.SQLUserName;
// Sqlbuilder.Password = app.ConfigProgramBin.SQLPassword;
//}
string connstring = String.Format("Server={0};Port={1};" +
"User Id={2};Password={3};Database={4};",
app.ConfigProgramBin.SQLServerName, 5432, app.ConfigProgramBin.SQLUserName,
app.ConfigProgramBin.SQLPassword, app.ConfigProgramBin.SQLDatabaseName);
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connstring);
string sql = "Select table_name FROM information_schema.tables WHERE table_schema = 'public'";
Npgsql.NpgsqlCommand command = null;
try
{
conn.Open();
command = new Npgsql.NpgsqlCommand(sql, conn);
Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string table = reader.GetString(0);
CollectionTables.Add(table.Replace('_', ' '));
}
reader.Close();
LBTables.ItemsSource = CollectionTables;
LBTables.SelectionMode = SelectionMode.Multiple;
Binding bindingLoadTable = new Binding();
bindingLoadTable.Source = LBTables;
bindingLoadTable.Path = new PropertyPath("SelectedItem");
bindingLoadTable.Converter = new RemoveButtonConverter();
Binding bindingLoadTableChart = new Binding();
bindingLoadTableChart.Source = LBTables;
bindingLoadTableChart.Path = new PropertyPath("SelectedItem");
bindingLoadTableChart.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData = new Binding();
bindingGetOptionalData.Source = LBTables;
bindingGetOptionalData.Path = new PropertyPath("SelectedItem");
bindingGetOptionalData.Converter = new RemoveButtonConverter();
Binding bindingGetOptionalData2 = new Binding();
bindingGetOptionalData2.Source = CHBAverage;
bindingGetOptionalData2.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData3 = new Binding();
bindingGetOptionalData3.Source = CHBSum;
bindingGetOptionalData3.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData4 = new Binding();
bindingGetOptionalData4.Source = CHBMax;
bindingGetOptionalData4.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData5 = new Binding();
bindingGetOptionalData5.Source = CHBMin;
bindingGetOptionalData5.Path = new PropertyPath("IsChecked");
Binding bindingGetOptionalData6 = new Binding();
bindingGetOptionalData6.Source = CHBIntegralSum;
bindingGetOptionalData6.Path = new PropertyPath("IsChecked");
MultiBinding mBindingGetOptionalData = new MultiBinding();
mBindingGetOptionalData.Converter = new GetOptionalData();
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData2);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData3);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData4);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData5);
mBindingGetOptionalData.Bindings.Add(bindingGetOptionalData6);
BLoadTable.SetBinding(Button.IsEnabledProperty, bindingLoadTable);
BLoadTableChart.SetBinding(Button.IsEnabledProperty, bindingLoadTableChart);
BGetOptionalData.SetBinding(Button.IsEnabledProperty, mBindingGetOptionalData);
DataPickerForm.DisplayDateEnd = DateTime.Now;
DataPickerForm.ToolTip = "Диапазон даты от";
DataPickerForm.SelectedDateChanged += DataPickerForm_SelectedDateChanged;
DataPickerForm.SelectedDate = DateTime.Now;
TimePickerForm.ValueChanged += TimePickerForm_ValueChanged;
TimePickerForm.ToolTip = "Диапазон времени от";
TimePickerForm.Format = TimeFormat.Custom;
TimePickerForm.FormatString = "HH:mm:ss";
if (DateTime.Now.Hour == 0)
{
TimePickerForm.Value = DateTime.Now;
}
else
{
TimePickerForm.Value = DateTime.Now.AddHours(-1);
}
DataPickerTo.DisplayDateEnd = DateTime.Now;
DataPickerTo.ToolTip = "Диапазон даты до";
DataPickerTo.SelectedDateChanged += DataPickerTo_SelectedDateChanged;
DataPickerTo.SelectedDate = DateTime.Now;
TimePickerTo.ValueChanged += TimePickerTo_ValueChanged;
TimePickerTo.ToolTip = "Диапазон времени до";
TimePickerTo.Format = TimeFormat.Custom;
TimePickerTo.FormatString = "HH:mm:ss";
TimePickerTo.Value = DateTime.Now;
}
catch (SystemException ex)
{
if (window.CollectionMessage.Count > 300)
{
window.CollectionMessage.RemoveAt(0);
window.CollectionMessage.Insert(298, "Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
else
{
window.CollectionMessage.Add("Сообщение " + " : " + "Ошибка в окне Архива " + ex.Message + " " + DateTime.Now);
}
//if (ex is SqlException)
//{
// SqlException sqlex = ex as SqlException;
// foreach (SqlError er in sqlex.Errors)
// {
// if (window.WindowErrorMessages.LBMessageError.Text.Length > 0)
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text += "\n" + "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// else
// {
// window.CountLineTextMessage++;
// window.WindowErrorMessages.LBMessageError.Text = "Сообщение " + window.CountLineTextMessage.ToString() + " : " + "Ошибка в окне Архива " + er.Message + " " + DateTime.Now;
// }
// }
//}
}
finally
{
conn.Close();
conn.Dispose();
if (command != null)
{
command.Dispose();
}
}
}
}
protected static bool TableExists(string tableName)
{
using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
var value = TableExists(tableName, conn, trans);
trans.Commit();
return value;
}
}
}
public async Task<List<Subdivision>> Density(AlcoholType alcoholType, EndOfDistribution distribution, int count)
{
var volumeField = alcoholTypeVolumeFieldMap[alcoholType];
var sortDirection = endOfDistributionMap[distribution];
var result = new List<Subdivision>();
var query = $@"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
beer_volume / 1000.0 as beer_volume,
wine_volume / 1000.0 as wine_volume,
spirits_volume / 1000.0 as spirits_volume,
{volumeField} / population as density
from subdivisions
where {volumeField} > 0
order by density {sortDirection}
limit {count}";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
/// <summary>
/// 执行存储过程或Sql语句返回DataTable
/// </summary>
/// <param name="sql">存储过程名称或Sql语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="paras">参数</param>
/// <returns></returns>
private DataTable Run(string sql, CommandType commandType, params object[] paras)
{
DataTable table = new DataTable();
switch (this.DBType)
{
case DBTypeEnum.SqlServer:
SqlConnection con = this.Database.GetDbConnection() as SqlConnection;
SqlDataAdapter adapter = new SqlDataAdapter();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
adapter.SelectCommand = cmd;
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
adapter.Fill(table);
adapter.SelectCommand.Parameters.Clear();
}
break;
case DBTypeEnum.MySql:
MySqlConnection mySqlCon = this.Database.GetDbConnection() as MySqlConnection;
using (MySqlCommand cmd = new MySqlCommand(sql, mySqlCon))
{
if (mySqlCon.State == ConnectionState.Closed)
{
mySqlCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
MySqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
mySqlCon.Close();
}
break;
case DBTypeEnum.PgSql:
Npgsql.NpgsqlConnection npgCon = this.Database.GetDbConnection() as Npgsql.NpgsqlConnection;
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, npgCon))
{
if (npgCon.State == ConnectionState.Closed)
{
npgCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
npgCon.Close();
}
break;
}
return(table);
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "select * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry from " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
if(ds.Tables[0].Rows.Count>0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return fdr;
}
else
return null;
}
else
return null;
}
}
}
internal static void ValidateVersion(string connectionStringName, string owner)
{
_ConnectionStringName = connectionStringName;
//open the connection, start the transaction
using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
#region v1.1
//test up to v1.1. This is basicially the entire original schema.
Log.Debug(d => d("Checking for v1.1 schema..."));
if (!TableExists("users", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.1. Updating..."));
RunScript(GetDDLResource("v1._1.InitializeSettings.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Tables.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Types.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_id.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.user_is_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.assign_users_to_roles.sql"), null, conn, trans);
}
#endregion
#region v1.2
//test up to v1.2. This adds the purge activity feature, for cleaning up old data.
Log.Debug(d => d("Checking for v1.2 schema..."));
if (!FunctionExists("purge_activity", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.2. Updating..."));
RunStatement(GetDDLResource("v1._2.purge_activity.sql"), null, conn, trans);
}
#endregion
#region v1.3
//test up to v1.3. This makes users, roles, and applications case-insensitive.
//also adds the version table to check against future versions.
Log.Debug(d => d("Checking for v1.3 schema..."));
if (!TableExists("versions", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.3. Updating..."));
RunScript(GetDDLResource("v1._3.TableChanges.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.assign_users_to_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.user_is_in_role.sql"), null, conn, trans);
}
#endregion
//v1.3 is the first version that includes version tracking in the database.
var currentVersion = GetDBVersion(conn, trans);
#region v1.4
Log.Debug(d => d("Checking for v1.4 schema (no DDL action required)..."));
if (currentVersion == "1.3") currentVersion = "1.4";
#endregion
#region v1.5
Log.Debug(d => d("Checking for v1.5 schema (no DDL action required)..."));
if (currentVersion == "1.4") currentVersion = "1.5";
#endregion
#region v1.6
if (currentVersion == "1.5")
{
//v1.6 fixes an update problem where the last login time was not being persisted.
RunStatement(GetDDLResource("v1._6.record_login_event.sql"), null, conn, trans);
currentVersion = "1.6";
}
#endregion
#region v1.7
if (currentVersion == "1.6")
{
RunStatement(GetDDLResource("v1._7.get_number_of_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._7.get_users_online.sql"), null, conn, trans);
currentVersion = "1.7";
}
#endregion
/*
* Other checks and updates will go here.
*
*
*/
SetDBVersion(conn, trans, currentVersion);
trans.Commit();
}
}
}
public async Task<List<Subdivision>> Top10WineDensity()
{
var result = new List<Subdivision>();
var query = @"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
wine_volume,
wine_volume / population as density
from subdivisions
where wine_volume > 0
order by density desc
limit 10";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
try
{
var loadScript = options.Get("sqlScriptPath", "");
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if ((!String.IsNullOrWhiteSpace(loadScript)) && (!String.IsNullOrWhiteSpace(customCSharpScriptPath)))
{
throw new Exception("No action configured");
}
// prepare paths
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedLoadScript.BucketName, Amazon.RegionEndpoint.USEast1, true);
// load sql script
string sqlScript = null;
if (!String.IsNullOrWhiteSpace(loadScript))
{
sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
}
// generate code
IAWSRedshiftPluginDynamicScript customCode = null;
if (!String.IsNullOrWhiteSpace(customCSharpScriptPath))
{
// load custom code
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
customCode = evaluator.Model;
}
// execute commands
using (var conn = new Npgsql.NpgsqlConnection(RedshiftHelper.GetConnectionString(context)))
{
conn.Open();
if (customCode != null)
{
logger.Debug("Custom csharp code Initialize");
customCode.Initialize(conn, s3, context);
logger.Debug("Custom csharp code BeforeExecution");
customCode.BeforeExecution();
logger.Debug("Custom csharp code PrepareSqlCOPYCommand");
if (!String.IsNullOrEmpty(sqlScript))
{
sqlScript = customCode.PrepareSqlCOPYCommand(sqlScript);
}
}
if (!String.IsNullOrEmpty(sqlScript))
{
logger.Debug("SQL command start");
try
{
conn.Execute(sqlScript);
}
catch (Exception ex)
{
// do nothing in case of timeout... some operations may take a while to complete...
if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) < 0)
{
throw ex;
}
logger.Info("SQL command executed, but is still running (connection timeout)...");
}
logger.Debug("SQL command end");
}
if (customCode != null)
{
logger.Debug("Custom csharp code AfterExecution");
customCode.AfterExecution();
}
}
logger.Success("Done");
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
return(false);
}
return(true);
}
/// <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;
}
public void SaveAppointmentSheet(AppointmentSheet appointment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (appointment.AppointmentSheetId > 0)
{
query = AppointmentUpdateQuery;
isUpdate = true;
}
else
{
query = AppointmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("addingservices", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentlocation", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsalesagent", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("comment", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("createdat", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("currentlyacceptedcards", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("currentprocessor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateofappointment", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("howmanylocations", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("internet", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastupdated", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("moto", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("multilocation", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("newequipment", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("newsetup", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("price", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("score", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("singlelocation", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("street", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("swipe", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("unhappy", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("volume", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("creator", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("parentlead", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("location", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdateto", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("reschedule", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("creatorname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("singleloccheck", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("event_reference", NpgsqlTypes.NpgsqlDbType.Text));
//command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentzone", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = appointment.AddingServices;
command.Parameters[1].Value = appointment.AppointmentLocation;
command.Parameters[2].Value = appointment.AssignedSalesAgent;
command.Parameters[3].Value = appointment.City;
command.Parameters[4].Value = appointment.Comment;
command.Parameters[5].Value = appointment.CreatedAt;
command.Parameters[6].Value = appointment.CurrentlyAcceptingCards;
command.Parameters[7].Value = appointment.CurrentProcessor;
command.Parameters[8].Value = appointment.DayOfAppointment;
command.Parameters[9].Value = appointment.HowManyLocations;
command.Parameters[10].Value = appointment.Internet;
command.Parameters[11].Value = appointment.LastUpdated;
command.Parameters[12].Value = appointment.Moto;
command.Parameters[13].Value = appointment.MultiLocation;
command.Parameters[14].Value = appointment.NewEquipment;
command.Parameters[15].Value = appointment.NewSetUp;
command.Parameters[16].Value = appointment.Price;
command.Parameters[17].Value = appointment.Score;
command.Parameters[18].Value = appointment.SingleLocation;
command.Parameters[19].Value = appointment.State;
command.Parameters[20].Value = appointment.Street;
command.Parameters[21].Value = appointment.Swipe;
command.Parameters[22].Value = appointment.Unhappy;
command.Parameters[23].Value = appointment.Volume;
command.Parameters[24].Value = appointment.ZipCode;
command.Parameters[25].Value = appointment.CreatorId;
command.Parameters[26].Value = appointment.ParentLeadId;
command.Parameters[27].Value = appointment.Location;
command.Parameters[28].Value = appointment.AppointmentDateFrom;
command.Parameters[29].Value = appointment.AppointmentDateTo;
command.Parameters[30].Value = appointment.Reschedule;
command.Parameters[31].Value = appointment.CreatorName;
command.Parameters[32].Value = appointment.SingleLocCheck;
command.Parameters[33].Value = appointment.Event_Reference;
//command.Parameters[34].Value = appointment.AppointmentZone;
//command.Parameters[10].Value = card.Creator;
if (isUpdate)
{
command.Parameters[34].Value = appointment.AppointmentSheetId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
/// <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;
}
internal static void ValidateVersion(string connectionStringName, string owner)
{
_ConnectionStringName = connectionStringName;
//open the connection, start the transaction
using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
#region v1.1
//test up to v1.1. This is basicially the entire original schema.
Log.Debug(d => d("Checking for v1.1 schema..."));
if (!TableExists("users", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.1. Updating..."));
RunScript(GetDDLResource("v1._1.InitializeSettings.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Tables.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Types.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_id.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.user_is_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.assign_users_to_roles.sql"), null, conn, trans);
}
#endregion
#region v1.2
//test up to v1.2. This adds the purge activity feature, for cleaning up old data.
Log.Debug(d => d("Checking for v1.2 schema..."));
if (!FunctionExists("purge_activity", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.2. Updating..."));
RunStatement(GetDDLResource("v1._2.purge_activity.sql"), null, conn, trans);
}
#endregion
#region v1.3
//test up to v1.3. This makes users, roles, and applications case-insensitive.
//also adds the version table to check against future versions.
Log.Debug(d => d("Checking for v1.3 schema..."));
if (!TableExists("versions", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.3. Updating..."));
RunScript(GetDDLResource("v1._3.TableChanges.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.assign_users_to_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.user_is_in_role.sql"), null, conn, trans);
}
#endregion
//v1.3 is the first version that includes version tracking in the database.
var currentVersion = GetDBVersion(conn, trans);
#region v1.4
Log.Debug(d => d("Checking for v1.4 schema (no DDL action required)..."));
if (currentVersion == "1.3")
{
currentVersion = "1.4";
}
#endregion
#region v1.5
Log.Debug(d => d("Checking for v1.5 schema (no DDL action required)..."));
if (currentVersion == "1.4")
{
currentVersion = "1.5";
}
#endregion
#region v1.6
if (currentVersion == "1.5")
{
//v1.6 fixes an update problem where the last login time was not being persisted.
RunStatement(GetDDLResource("v1._6.record_login_event.sql"), null, conn, trans);
currentVersion = "1.6";
}
#endregion
#region v1.7
if (currentVersion == "1.6")
{
RunStatement(GetDDLResource("v1._7.get_number_of_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._7.get_users_online.sql"), null, conn, trans);
currentVersion = "1.7";
}
#endregion
/*
* Other checks and updates will go here.
*
*
*/
SetDBVersion(conn, trans, currentVersion);
trans.Commit();
}
}
}
/// <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;
}
public void SaveLead(Domain.Lead lead)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (lead.LeadId > 0)
{
query = LeadUpdateQuery;
isUpdate = true;
}
else
{
query = LeadInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("companyname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("numbertocall", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("faxnumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("websitelink", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress1", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress2", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zonenumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("callbackdate", NpgsqlTypes.NpgsqlDbType.Date));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignoreddate", NpgsqlTypes.NpgsqlDbType.Date));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedaauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("suppressed", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignored", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateimported", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("reassigned", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonechecked", NpgsqlTypes.NpgsqlDbType.Boolean));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("leadid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = lead.CompanyName;
command.Parameters[1].Value = lead.Contact1Title;
command.Parameters[2].Value = lead.Contact1FirstName;
command.Parameters[3].Value = lead.Contact2Title;
command.Parameters[4].Value = lead.Contact2FirstName;
command.Parameters[5].Value = lead.PrimaryPhoneNumber;
command.Parameters[6].Value = lead.AddtionalPhoneNumber;
command.Parameters[7].Value = lead.NumberToCall;
command.Parameters[8].Value = lead.FaxNumber;
command.Parameters[9].Value = lead.PrimaryEmailAddress;
command.Parameters[10].Value = lead.AdditonalEmailAddress;
command.Parameters[11].Value = lead.WebsiteLink;
command.Parameters[12].Value = lead.StreetAddress1;
command.Parameters[13].Value = lead.StreetAddress2;
command.Parameters[14].Value = lead.City;
command.Parameters[15].Value = lead.State;
command.Parameters[16].Value = lead.ZipCode;
command.Parameters[17].Value = lead.ZoneNumber;
command.Parameters[18].Value = lead.Status;
command.Parameters[19].Value = lead.AssignedSAUserId;
command.Parameters[20].Value = lead.CallbackDate;
command.Parameters[21].Value = lead.IgnoredDate;
command.Parameters[22].Value = lead.AssignedAAUserId;
command.Parameters[23].Value = lead.Contact1LastName;
command.Parameters[24].Value = lead.Contact2LastName;
command.Parameters[25].Value = lead.Suppressed;
command.Parameters[26].Value = lead.Ignored;
command.Parameters[27].Value = lead.DateTimeImported;
command.Parameters[28].Value = lead.Reassigned;
command.Parameters[29].Value = lead.PrimaryPhoneChecked;
if (isUpdate)
{
command.Parameters[30].Value = lead.LeadId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
if (lead.NewFilePath != null && lead.NewFilePath != string.Empty)
{
PGUploadedfileRepository upFile = new PGUploadedfileRepository();
upFile.InsertLeadFile(lead.LeadId, lead.NewFilePath);
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<IGeometry> features = new List<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strGeom + " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
List <string> files = null;
FileTransferDetails parsedErrorLocation = null;
try
{
var inputSearchPath = options.Get("inputSearchPath", "");
if (String.IsNullOrEmpty(inputSearchPath))
{
throw new ArgumentNullException("inputSearchPath");
}
var backupLocation = options.Get("backupLocation", "");
if (String.IsNullOrEmpty(backupLocation))
{
throw new ArgumentNullException("backupLocation");
}
var loadScript = options.Get("sqlScriptPath", "");
if (String.IsNullOrEmpty(loadScript))
{
throw new ArgumentNullException("sqlScriptPath");
}
var errorLocation = options.Get("errorLocation", "");
if (String.IsNullOrEmpty(errorLocation))
{
throw new ArgumentNullException("errorLocation");
}
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if (String.IsNullOrEmpty(customCSharpScriptPath))
{
throw new ArgumentNullException("customCSharpScriptPath");
}
// prepare paths
var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath);
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation);
parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true);
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
// generate code
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
// 1. check if there is any new file
files = GetFilesFromS3(s3, parsedInput).ToList();
if (files.Any())
{
logger.Info("Files found: " + files.Count);
}
else
{
logger.Debug("No file found");
return(false);
}
var connectionString = RedshiftHelper.GetConnectionString(context);
foreach (var f in files)
{
var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
if (String.IsNullOrEmpty(sqlScript))
{
throw new Exception("invalid sql script");
}
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
var fullFilename = System.IO.Path.Combine("s3://", parsedInput.BucketName, f.Trim()).Replace('\\', '/');
options.Set("InputFilename", fullFilename);
evaluator.Model.Initialize(conn, s3, context);
evaluator.Model.BeforeExecution();
sqlScript = evaluator.Model.PrepareSqlCOPYCommand(sqlScript);
// Create a PostgeSQL connection string.
ExecuteRedshiftLoad(connectionString, logger, sqlScript, new List <string> ()
{
f
}, parsedInput);
logger.Debug("Moving files to backup folder");
evaluator.Model.AfterExecution();
// move files
var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
logger.Success("Done");
}
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
try
{
if (files != null && s3 != null)
{
// move files
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
}
}
catch { }
return(false);
}
return(true);
}
/// <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;
}
//To save account(s) to DB
public void SaveAccounts(Domain.Account account)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (account.AccountId > 0)
{
query = AccountUpdateQuery;
isUpdate = true;
}
else
{
query = AccountInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("merchantid", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("aacreator", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsalesrep", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("salesrepnumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("officenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountapprovaldate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("annualfee", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("estimatedmonthlyvolume", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ht", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("hmv", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("platform", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("vendor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("vip", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mbp", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("freesupplies", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("pcirefund", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingstreet", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingcity", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingstate", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingzipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbastreet", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbacity", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbastate", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbazipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphone", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryphone", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("faxnumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryemail", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryemail", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("website", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("credit", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("debit", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("arb", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("cim", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ip", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("giftcardprocessor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secur_chex", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("software", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ecommerce", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminal", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminalowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminalquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminal", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminalowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminalquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipment", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipmentowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipmentquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpad", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpadowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpadquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpad", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpadowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpadquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("printer", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("priterowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("uploadfiles", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("parentlead", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = account.MerchantId;
command.Parameters[1].Value = account.AccountName;
command.Parameters[2].Value = account.AACreator;
command.Parameters[3].Value = account.AssignedSalesRep;
command.Parameters[4].Value = account.SalesRepNumber;
command.Parameters[5].Value = account.OfficeNumber;
command.Parameters[6].Value = account.Status;
command.Parameters[7].Value = account.AccountApprovalDate;
command.Parameters[8].Value = account.AnnualFee;
command.Parameters[9].Value = account.EstimatedMonthlyVolume;
command.Parameters[10].Value = account.HT;
command.Parameters[11].Value = account.HMV;
command.Parameters[12].Value = account.Platform;
command.Parameters[13].Value = account.Vendor;
command.Parameters[14].Value = account.VIP;
command.Parameters[15].Value = account.MBP;
command.Parameters[16].Value = account.FreeSupplies;
command.Parameters[17].Value = account.PCIRefund;
command.Parameters[18].Value = account.MailingStreet;
command.Parameters[19].Value = account.MailingCity;
command.Parameters[20].Value = account.MailingState;
command.Parameters[21].Value = account.MailingZipcode;
command.Parameters[22].Value = account.DBAStreet;
command.Parameters[23].Value = account.DBACity;
command.Parameters[24].Value = account.DBAState;
command.Parameters[25].Value = account.DBAZipcode;
command.Parameters[26].Value = account.PrimaryPhone;
command.Parameters[27].Value = account.SecondaryPhone;
command.Parameters[28].Value = account.FaxNumber;
command.Parameters[29].Value = account.PrimaryEmail;
command.Parameters[30].Value = account.SecondaryEmail;
command.Parameters[31].Value = account.Website;
command.Parameters[32].Value = account.Credit;
command.Parameters[33].Value = account.Debit;
command.Parameters[34].Value = account.ARB;
command.Parameters[35].Value = account.CIM;
command.Parameters[36].Value = account.IP;
command.Parameters[37].Value = account.GiftCardProcessor;
command.Parameters[38].Value = account.Secur_Chex;
command.Parameters[39].Value = account.Software;
command.Parameters[40].Value = account.ECommerace;
command.Parameters[41].Value = account.PrimaryTerminal;
command.Parameters[42].Value = account.PrimaryTerminalOwner;
command.Parameters[43].Value = account.PrimaryTerminalQuantity;
command.Parameters[44].Value = account.SecondaryTerminal;
command.Parameters[45].Value = account.SecondaryTerminalOwner;
command.Parameters[46].Value = account.SecondaryTerminalQuantity;
command.Parameters[47].Value = account.CheckEquipment;
command.Parameters[48].Value = account.CheckEquipmentOwner;
command.Parameters[49].Value = account.CheckEquipmentQuantity;
command.Parameters[50].Value = account.PrimaryPINpad;
command.Parameters[51].Value = account.PrimaryPINpadOwner;
command.Parameters[52].Value = account.PrimaryPINpadQuantity;
command.Parameters[53].Value = account.SecondaryPINpad;
command.Parameters[54].Value = account.SecondaryPINpadOwner;
command.Parameters[55].Value = account.SecondaryPINpadQuantity;
command.Parameters[56].Value = account.Printer;
command.Parameters[57].Value = account.PrinterOwner;
command.Parameters[58].Value = account.Description;
command.Parameters[59].Value = account.UploadedFiles;
command.Parameters[60].Value = account.ParentLead;
if (isUpdate)
{
command.Parameters[61].Value = account.AccountId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
if (account.NewFilePath != null && account.NewFilePath != string.Empty)
{
PGUploadedfileRepository upFile = new PGUploadedfileRepository();
upFile.InsertAccountFile(account.AccountId, account.NewFilePath);
}
}
/// <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;
}
}
