private int GetCompanyId(string genre)
{
if (getCompanyId == null)
{
getCompanyId = connection.CreateCommand();
getCompanyId.CommandText = "SELECT id FROM dump_psxdatacenter_companies WHERE name=@name";
getCompanyId.Parameters.Add("@name", NpgsqlDbType.Varchar);
}
getCompanyId.Parameters["@name"].Value = genre;
NpgsqlDataReader dataReader = getCompanyId.ExecuteReader();
bool hasdata = dataReader.Read();
if (hasdata)
{
int result = dataReader.GetInt32(0);
dataReader.Dispose();
return(result);
}
dataReader.Dispose();
if (createCompany == null)
{
createCompany = connection.CreateCommand();
createCompany.CommandText = "INSERT INTO dump_psxdatacenter_companies (name) VALUES (@name) RETURNING id";
createCompany.Parameters.Add("@name", NpgsqlDbType.Varchar);
}
createCompany.Parameters["@name"].Value = genre;
return((int)createCompany.ExecuteScalar());
}
public bool busca_sol_deb_automatico(ref ConnPostNpgsql.Conexao conexao)
{
this.erros.classe = "CSolDebAutomatico";
this.erros.funcao = "busca_sol_deb_automatico()";
this.erros.codErro = "";
NpgsqlCommand npgsqlCommand = new NpgsqlCommand();
NpgsqlDataReader npgsqlDataReader = null;
bool result;
try
{
npgsqlCommand.Connection = conexao.ConexaoBd;
if (conexao.transactionIsOpen())
{
npgsqlCommand.Transaction = conexao.TransacaoBd;
}
string commandText = "SELECT * from tb_solicitacao_deb_aut WHERE seq_solicitacao_deb_aut = :seq_solicitacao_deb_aut";
npgsqlCommand.CommandText = commandText;
openPost.setParameters(npgsqlCommand, ":seq_solicitacao_deb_aut", this.pagamento.seq_solicitacao_deb_aut);
npgsqlDataReader = npgsqlCommand.ExecuteReader();
if (npgsqlDataReader.Read())
{
int ordinal = npgsqlDataReader.GetOrdinal("id_imobiliaria");
this.pagamento.codImobiliaria = npgsqlDataReader.GetValue(ordinal).ToString();
ordinal = npgsqlDataReader.GetOrdinal("id_venda");
this.pagamento.id_venda = npgsqlDataReader.GetValue(ordinal).ToString();
ordinal = npgsqlDataReader.GetOrdinal("id_pagamento");
this.pagamento.id_pagamento = npgsqlDataReader.GetValue(ordinal).ToString();
npgsqlDataReader.Dispose();
result = true;
}
else
{
this.erros.codErro = "9999";
this.erros.descErro = "NAO HA PAGAMENTOS PARA SOLICITACAO DEBITO AUTOMATICO...";
this.erros.rc = 59;
npgsqlCommand.Dispose();
npgsqlDataReader.Dispose();
result = false;
}
}
catch (Exception ex)
{
this.erros.codErro = "9999";
this.erros.descErro = "EXCEPTION:" + ex.Message;
this.erros.rc = 4;
npgsqlCommand.Dispose();
npgsqlDataReader.Dispose();
result = false;
}
finally
{
if (npgsqlCommand != null)
{
npgsqlCommand.Dispose();
npgsqlCommand = null;
}
}
return(result);
}
private int GetScreenshotId(Screenshot screenshot)
{
if (getScreenshotId == null)
{
getScreenshotId = connection.CreateCommand();
getScreenshotId.CommandText = "SELECT id FROM dump_psxdatacenter_screenshots WHERE name=@name";
getScreenshotId.Parameters.Add("@name", NpgsqlDbType.Varchar);
}
getScreenshotId.Parameters["@name"].Value = screenshot.Name;
NpgsqlDataReader dataReader = getScreenshotId.ExecuteReader();
bool hasData = dataReader.Read();
if (hasData)
{
int result = dataReader.GetInt32(0);
dataReader.Dispose();
return(result);
}
dataReader.Dispose();
if (createScreenshot == null)
{
createScreenshot = connection.CreateCommand();
createScreenshot.CommandText = "INSERT INTO dump_psxdatacenter_screenshots (name,buffer) VALUES (@name,@buffer) RETURNING id";
createScreenshot.Parameters.Add("@name", NpgsqlDbType.Varchar);
createScreenshot.Parameters.Add("@buffer", NpgsqlDbType.Bytea);
}
createScreenshot.Parameters["@name"].Value = screenshot.Name;
createScreenshot.Parameters["@buffer"].Value = screenshot.Data;
return((int)createScreenshot.ExecuteScalar());
}
public bool buscaVenda(ref ConnPostNpgsql.Conexao conexao)
{
this.erros.classe = "CVenda";
this.erros.funcao = "buscaVenda()";
this.erros.codErro = "";
NpgsqlCommand npgsqlCommand = new NpgsqlCommand();
NpgsqlDataReader npgsqlDataReader = null;
bool result;
try
{
npgsqlCommand.Connection = conexao.ConexaoBd;
if (conexao.transactionIsOpen())
{
npgsqlCommand.Transaction = conexao.TransacaoBd;
}
string commandText = "SELECT * from tb_vendas WHERE id_imobiliaria = :id_imobiliaria AND id_venda = :id_venda";
npgsqlCommand.CommandText = commandText;
openPost.setParameters(npgsqlCommand, ":id_imobiliaria", this.pagamento.codImobiliaria);
openPost.setParameters(npgsqlCommand, ":id_venda", this.pagamento.id_venda);
npgsqlDataReader = npgsqlCommand.ExecuteReader();
if (npgsqlDataReader.Read())
{
int ordinal = npgsqlDataReader.GetOrdinal("id_venda_imobiliaria");
this.pagamento.id_venda_imobiliaria = npgsqlDataReader.GetValue(ordinal).ToString();
npgsqlDataReader.Dispose();
result = true;
}
else
{
this.erros.codErro = "9999";
this.erros.descErro = "VENDA NAO CADASTRADA...";
this.erros.rc = 8;
npgsqlCommand.Dispose();
npgsqlDataReader.Dispose();
result = false;
}
}
catch (Exception ex)
{
this.erros.codErro = "9999";
this.erros.descErro = "EXCEPTION:" + ex.Message;
this.erros.rc = 4;
npgsqlCommand.Dispose();
npgsqlDataReader.Dispose();
result = false;
}
finally
{
if (npgsqlCommand != null)
{
npgsqlCommand.Dispose();
npgsqlCommand = null;
}
}
return(result);
}
public ActionResult Put([FromBody] SettingsData settingsData)
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.Transaction = usable.Connection.BeginTransaction();
command.CommandText = "select 1 from t_settings;";
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.Read() == false)
{
reader.Dispose();
command.CommandText = $@"
INSERT INTO t_settings (id, enabled, location, location_friendly_name, override_value, weather_enabled)
VALUES (DEFAULT, false, '', '', 100, false);";
command.ExecuteNonQuery();
}
else
{
reader.Dispose();
}
// select where id == 1 if none, then insert
command.CommandText = $@"
UPDATE t_settings
SET enabled = {settingsData.Enabled},
location = '{settingsData.Location}',
location_friendly_name = '{settingsData.LocationFriendlyName}',
override_value = {settingsData.OverrideValue},
weather_enabled = {settingsData.WeatherEnabled}
WHERE id = 1;";
command.ExecuteNonQuery();
command.CommandText = @"
TRUNCATE TABLE ONLY t_humidity_sensor;";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO t_humidity_sensor (settings_id, friendly_name, real_name) VALUES (1, @friendlyName, @realName);";
foreach ((string realSensorName, string friendlySensorName) in settingsData.HumiditySensors)
{
command.Parameters.AddWithValue("@friendlyName", NpgsqlDbType.Text, friendlySensorName);
command.Parameters.AddWithValue("@realName", NpgsqlDbType.Text, realSensorName);
command.ExecuteNonQuery();
}
command.Transaction.Commit();
}
}
return(NoContent());
}
public List <FunctionsDTO> getFunctions(string Moduleid)
{
List <FunctionsDTO> lstFunction = new List <FunctionsDTO>();
try
{
string str = "select functionname,functionid,statusid from tabfunctions where moduleid=1 and statusid=1;";
str = "select functionname,functionid,statusid,moduleid from tabfunctions where moduleid=" + Moduleid + " and statusid=1;";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
FunctionsDTO objFunctionsDTO = new FunctionsDTO();
objFunctionsDTO.FunctionId = Convert.ToInt32(npgdr["functionid"]);
objFunctionsDTO.FunctionName = npgdr["functionname"].ToString();
objFunctionsDTO.Moduleid = Convert.ToInt16(npgdr["moduleid"]);
objFunctionsDTO.FunctionStatus = false;
lstFunction.Add(objFunctionsDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstFunction);
}
public List <ModuleDTO> ShowModuleName()
{
List <ModuleDTO> lstModule = new List <ModuleDTO>();
try
{
string str = "select modulename,moduleid from tabmodules where statusid=1 and moduletype in ('RMS','HRMS');";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
ModuleDTO objModuleDTO = new ModuleDTO();
objModuleDTO.Moduleid = Convert.ToInt32(npgdr["moduleid"]);
objModuleDTO.Modulename = npgdr["modulename"].ToString();
lstModule.Add(objModuleDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstModule);
}
public List <UserInfo> ShowUserName()
{
List <UserInfo> lstemployeename = new List <UserInfo>();
try
{
string str = "SELECT userid, upper(username) as username FROM tabuserinfo where statusid=1 ORDER BY username";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, str);
while (npgdr.Read())
{
UserInfo objUserInfoDTO = new UserInfo();
objUserInfoDTO.UserID = npgdr["userid"].ToString();
objUserInfoDTO.UserName = npgdr["username"].ToString();
lstemployeename.Add(objUserInfoDTO);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstemployeename);
}
private void comboBox6_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
//string connstring = "Server=127.0.0.1;Port=5432; User Id=tomas;Password=tomas;Database=postgres";
NpgsqlConnection con = new NpgsqlConnection(connstring);
con.Open();
string query = "select pizza_size from pizza join product_table using(prod_id) where product_type = '" + comboBox6.Text + "';";
NpgsqlCommand ncmd = new NpgsqlCommand(query, con);
NpgsqlDataReader dr = ncmd.ExecuteReader();
comboBox5.Items.Clear();
while (dr.Read())
{
comboBox5.Items.Add(dr["pizza_size"]);
}
dr.Close();
dr.Dispose();
con.Close();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
MessageBox.Show(msg.ToString());
throw;
}
}
public List <Plant> GetPlantedPlants()
{
List <Plant> plants = new List <Plant>();
NpgsqlCommand cmd = new NpgsqlCommand(
@"SELECT PLANT.plant_id, PLANT.name, PLANT.icon_name, ps.X, ps.Y
from PLANT
inner join PLANT_POSITION on PLANT.plant_id = PLANT_POSITION.plant_id
inner join dblink('controller_con', 'SELECT position_id, X, Y FROM Position')
AS ps(position_id int, X int, Y int)
on PLANT_POSITION.position_id = ps.position_id", DBManager.con);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Plant plant = new Plant();
plant.PlantId = reader.GetInt16(0);
plant.Name = reader.GetString(1);
plant.IconName = reader.GetString(2);
plant.X = reader.GetInt16(3);
plant.Y = reader.GetInt16(4);
plants.Add(plant);
}
reader.Dispose();
cmd.Dispose();
return(plants);
}
public Dictionary <Plant, int> GetPlantFrequency()
{
Dictionary <Plant, int> plantFrequency = new Dictionary <Plant, int>();
NpgsqlCommand cmd = new NpgsqlCommand(
@"SELECT PLANT.plant_id, PLANT.name, count(*) as cnt
from PLANT
inner join PLANT_POSITION on PLANT.plant_id = PLANT_POSITION.plant_id
inner join dblink('controller_con', 'SELECT position_id, X, Y FROM Position')
AS ps(position_id int, X int, Y int)
on PLANT_POSITION.position_id = ps.position_id GROUP BY PLANT.name, PLANT.plant_id", DBManager.con);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Plant plant = new Plant();
plant.PlantId = reader.GetInt16(0);
plant.Name = reader.GetString(1);
plantFrequency.Add(plant, reader.GetInt32(2));
}
reader.Dispose();
cmd.Dispose();
return(plantFrequency);
}
/// <summary>
/// プログラム名称取得
/// </summary>
/// <param name="b2com">共通パラメータ</param>
/// <returns>日本語プログラム名称</returns>
public string getProgramName(B2Com b2com)
{
StringBuilder lstrSQL = new StringBuilder();
string data = b2com.PRGID;
lstrSQL.Append("select ");
lstrSQL.Append(" program_name as data");
lstrSQL.Append(" from menu_data");
lstrSQL.Append(" WHERE filename like '%" + b2com.PRGID + "%'");
/*select bunrui_seq, file_seq, bunrui_name, filename, program_name
* from menu_data;*/
NpgsqlDataReader reader = b2com.PgLib.SelectSql_NoCache(lstrSQL.ToString());
if (reader != null)
{
if (reader.Read())
{
if (DBNull.Value.Equals(reader["data"]))
{
return(data);
}
data = reader["data"].ToString();
}
reader.Close();
reader.Dispose();
reader = null;
}
return(data);
}
public static bool IsCallerValid(string guid, string sharedid, string strWallet)
{
string encusertoken = hashGuid(sharedid);
bool ret = false;
NpgsqlConnection conn = new NpgsqlConnection(strWallet);
conn.Open();
try
{
NpgsqlCommand cmd = new NpgsqlCommand("sp_isCallerValid", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("p_guid", guid));
cmd.Parameters.Add(new NpgsqlParameter("p_sharedid", encusertoken));
NpgsqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
{
ret = true;
}
reader.Dispose();
cmd.Dispose();
}
catch (Exception ex)
{
ret = false;
}
finally
{
conn.Close();
}
return(ret);
}
public NpgsqlConnection connClose()
{
if (connString != null)
{
connString.Close();
}
if (connString != null)
{
connString.Dispose();
}
if (cmdString != null && cmdString.Connection != null)
{
cmdString.Connection.Close();
}
if (cmdString != null && cmdString.Connection != null)
{
cmdString.Connection.Dispose();
}
if (cmdString != null)
{
cmdString.Dispose();
}
if (rdrString != null)
{
rdrString.Close();
}
if (rdrString != null)
{
rdrString.Dispose();
}
return(connString);
}
/// <summary>
/// INI情報取得
/// </summary>
/// <param name="pgName">プログラム名</param>
/// <param name="section">セクション名</param>
/// <param name="key">キー名</param>
/// <param name="def">デフォルト値</param>
/// <returns>INIデータ</returns>
public string GetINIData(string pgName, string section, string key, string def)
{
StringBuilder lstrSQL = new StringBuilder();
string data = def;
/*yakkyoku_code, program, section, keycode, data, biko,
* start_use_date, end_use_date */
lstrSQL.Append("select ");
lstrSQL.Append(" data");
lstrSQL.Append(" from ini_master");
lstrSQL.Append(" WHERE yakkyoku_code = '" + B2_Yakkyoku_Code + "'");
lstrSQL.Append(" AND program = '" + pgName + "'");
lstrSQL.Append(" AND section = '" + section + "'");
lstrSQL.Append(" AND keycode = '" + key + "'");
NpgsqlDataReader reader = PostLib.SelectSQL_NoCache(lstrSQL.ToString());
if (reader != null)
{
if (reader.Read())
{
if (DBNull.Value.Equals(reader["data"]))
{
return(def);
}
data = reader["data"].ToString();
}
reader.Close();
reader.Dispose();
reader = null;
}
return(data);
}
public List <ModuleDTO> ShowRoleFunctions(string ID)
{
NpgsqlDataReader npgdr = null;
List <ModuleDTO> lstJquery = new List <ModuleDTO>();
try
{
string strlocation = "select f.functionid,f.functionname,f.functionurl,f.moduleid,m.modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid order by modulename;";
strlocation = "select x.functionid,functionname,functionurl,x.moduleid,modulename,coalesce(status,false) as status from (select f.functionid,f.functionname,f.functionurl,f.moduleid,m.modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid where moduletype in ('RMS') order by modulename)x left join (SELECT functionid,moduleid,true as status FROM tabrolefunctions where userid=" + ID + ")y on x.moduleid=y.moduleid and x.functionid=y.functionid;";
strlocation = "select x.functionid,functionname,functionurl,x.moduleid, modulename,coalesce(status,false) as status from (select f.functionid,f.functionname,f.functionurl,f.moduleid,m.parentmodulename||'_'||m.modulename as modulename from tabfunctions f left join tabmodules m on m.moduleid=f.moduleid where moduletype in ('RMS') order by modulename)x left join (SELECT functionid,moduleid,true as status FROM tabrolefunctions where userid=" + ID + ")y on x.moduleid=y.moduleid and x.functionid=y.functionid;";
npgdr = NPGSqlHelper.ExecuteReader(NPGSqlHelper.SQLConnString, CommandType.Text, strlocation);
while (npgdr.Read())
{
ModuleDTO obj = new ModuleDTO();
obj.functionid = Convert.ToInt32(npgdr["functionid"].ToString());
obj.functionname = npgdr["functionname"].ToString();
obj.functionurl = npgdr["functionurl"].ToString();
obj.Moduleid = Convert.ToInt32(npgdr["moduleid"].ToString());
obj.modulename = npgdr["modulename"].ToString();
obj.chkStatus = Convert.ToBoolean(npgdr["status"]);
lstJquery.Add(obj);
}
}
catch (Exception ex)
{
EventLogger.WriteToErrorLog(ex, "UserRights");
}
finally
{
npgdr.Dispose();
}
return(lstJquery);
}
public Condition GetConditionByPlant(int plant)
{
NpgsqlCommand cmd = new NpgsqlCommand(
@"SELECT condition_id, soil, minTeperature, maxTeperature, minHumidity, maxHumidity
FROM (SELECT c.condition_id, s.name, c.minTeperature, c.maxTeperature, c.minHumidity, c.maxHumidity
from CONDITION c left join SOIL s on s.soil_id = c.soil_id LEFT JOIN PLANT p on c.condition_id = p.consition_id
where p.plant_id = @plantId')
AS t(condition_id int, soil varchar, minTeperature numeric, maxTeperature numeric, minHumidity numeric, maxHumidity numeric)", DBManager.con);
cmd.Parameters.AddWithValue("@plantId", plant);
NpgsqlDataReader reader = cmd.ExecuteReader();
Condition condition = new Condition();
condition.ConditionId = int.Parse(reader["condition_id"].ToString());
condition.Soil = reader["soil"].ToString();
condition.MinTmp = double.Parse(reader["minTeperature"].ToString());
condition.MaxTmp = double.Parse(reader["maxTeperature"].ToString());
condition.MinHumidity = double.Parse(reader["minHumidity"].ToString());
condition.MaxHumidity = double.Parse(reader["maxHumidity"].ToString());
reader.Dispose();
cmd.Dispose();
return(condition);
}
public void InserirParticipante(Participante participante)
{
try
{
if (conexao.State == ConnectionState.Closed)
{
conexao.Open();
}
NpgsqlCommand comandoSQL = new NpgsqlCommand("inserir_participante", (NpgsqlConnection)conexao);
comandoSQL.CommandType = CommandType.StoredProcedure;
FabricaParametros.CriarParametrosParticipante(comandoSQL, participante, campi);
comandoSQL.Prepare();
NpgsqlDataReader leitor = comandoSQL.ExecuteReader();
int codigo_participante = -1;
while (leitor.Read())
{
codigo_participante = Int32.Parse(leitor[0].ToString());
}
leitor.Close();
leitor.Dispose();
InserirConteudo(participante, codigo_participante);
}
catch (Exception ex)
{
Console.Write("Deu pau jovem: " + ex.Message);
}
}
public ArrayList GetTableNames()
{
string strSQL = this.sql_GetTableNames();
NpgsqlDataReader reader = this.Select(strSQL);
if (reader != null)
{
if (reader.HasRows)
{
ArrayList tables = new ArrayList();
int fc = reader.FieldCount; // 1
while (reader.Read())
{ // oid f1,f2,f3... geom
for (int i = 0; i < fc; i++)
{
string table = reader[0].ToString();
tables.Add(table);
}
}
reader.Close();
reader.Dispose();
return(tables);
}
}
return(null);
}
public void EraseMediaCollection(int CollectionId)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
NpgsqlCommand command = new NpgsqlCommand(@"
SELECT * FROM dbo.""MediaElements"" WHERE CollectionId = :CollectionId;
DELETE FROM dbo.""MediaElements"" WHERE CollectionId = :CollectionId;
", connection);
command.Parameters.Add(new NpgsqlParameter("CollectionId", NpgsqlDbType.Integer));
command.Parameters[0].Value = CollectionId;
NpgsqlCommand command3 = new NpgsqlCommand(@"DELETE FROM dbo.""MediaCollections"" WHERE Id = :CollectionId", connection);
command3.Parameters.Add(new NpgsqlParameter("CollectionId", NpgsqlDbType.Integer));
command3.Parameters[0].Value = CollectionId;
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string mediafile = AppDomain.CurrentDomain.BaseDirectory + "/Content/MembersFiles/" + (string)reader[2];
if (File.Exists(mediafile))
{
File.Delete(mediafile);
}
}
reader.Dispose();
command3.ExecuteNonQuery();
connection.Close();
}
public List <MediaElement> GetMediaCollection(int CollectionId)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
NpgsqlCommand command = new NpgsqlCommand(@"SELECT * FROM dbo.""MediaElements"" WHERE CollectionId = :CollectionId", connection);
command.Parameters.Add(new NpgsqlParameter("CollectionId", NpgsqlDbType.Integer));
command.Parameters[0].Value = CollectionId;
List <MediaElement> mediaelements = new List <MediaElement>();
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
MediaElement mediaelement = new MediaElement();
mediaelement.Id = (int)reader[0];
mediaelement.CollectionId = (int)reader[1];
mediaelement.File = (string)reader[2];
mediaelements.Add(mediaelement);
}
reader.Dispose();
connection.Close();
return(mediaelements);
}
protected MG_MapExtent GetExtent(string layername)
{// SELECT minx, miny, maxx, maxy FROM ext WHERE layername = 'links';
string strSQL = "SELECT minx, miny, maxx, maxy FROM ext WHERE layername = '{0}';";
strSQL = String.Format(strSQL, layername.ToLower());
NpgsqlDataReader reader = this.Select(strSQL);
if (reader != null)
{
if (reader.HasRows)
{
MG_MapExtent mapExt = new MG_MapExtent();
int fc = reader.FieldCount; // 4
while (reader.Read())
{ // xmin ymin xmax ymax
double xmin = Double.Parse(reader["minx"].ToString());
double ymin = Double.Parse(reader["miny"].ToString());
double xmax = Double.Parse(reader["maxx"].ToString());
double ymax = Double.Parse(reader["maxy"].ToString());
mapExt.SetExtent(xmin, ymin, xmax, ymax);
}
reader.Close();
reader.Dispose();
return(mapExt);
}
}
return(null);
}
protected MG_FeatureSet GetFeatureSet(MG_FieldSet fieldSet, string table)
{
NpgsqlDataReader reader = this.SelectAll(table);
if (reader == null || !reader.HasRows)
{
return(null);
}
MG_FeatureSet featureSet = new MG_FeatureSet();
int fc = reader.FieldCount; // 4
while (reader.Read())
{// oid f1,f2,f3... geom
MG_Feature f = new MG_Feature(fieldSet);
MG_ValueSet valueSet = new MG_ValueSet();
string oid = reader["oid"].ToString();
string geom = reader["geomtext"].ToString();
for (int i = 1; i < fc - 1; i++)
{
string str = reader[i].ToString();
MG_Value value = new MG_Value(i - 1, str);
valueSet.Add(value);
}
f.ValueSet = valueSet;
f.Geometry = MG_ShapeFileOper.AsGeometry(geom);
featureSet.Add(f);
}
reader.Close();
reader.Dispose();
return(featureSet);
}
public IActionResult DoesNetworkExist([FromForm] string guid, [FromForm] string sharedid, [FromForm] string username)
{
if (!Helper.IsCallerValid(guid, sharedid, _connstrWallet))
{
ErrorObject ro = new ErrorObject();
ro.message = "ErrInvalid";
ro.error = true;
return(Json(ro));
}
bool networkExists = false;
string errorMessage = "";
bool isError = false;
NpgsqlConnection conn = new NpgsqlConnection(_connstrWallet);
conn.Open();
try
{
NpgsqlCommand com = new NpgsqlCommand("sp_doesnetworkexist", conn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new NpgsqlParameter("p_guid", guid));
com.Parameters.Add(new NpgsqlParameter("p_username", username));
NpgsqlDataReader reader = com.ExecuteReader(CommandBehavior.SingleResult);
if (reader.HasRows)
{
networkExists = true;
}
else
{
networkExists = false;
}
reader.Dispose();
}
catch (Exception ex)
{
isError = true;
errorMessage = "ErrSystem";
}
finally
{
conn.Close();
}
if (isError)
{
ErrorObject ro = new ErrorObject();
ro.message = "ErrInvalid";
ro.error = true;
return(Json(ro));
}
else
{
NetworkExists ne = new NetworkExists();
ne.message = networkExists;
return(Json(ne));
}
}
public override DBDataCollection ExcuteProcedure(string sp_name, bool isReturnDataSet, ref DBOParameterCollection dbp)
{
DBDataCollection rtn = new DBDataCollection();
rtn.IsSuccess = false;
DataSetStd ds = new DataSetStd();
NpgsqlCommand dc = null;//new SqlCommand(p.StoreProcureName, this.sqlconn);
if (this._s == DBStatus.Begin_Trans)
{
dc = new NpgsqlCommand(sp_name, this.conn, this.tran);
}
else
{
dc = new NpgsqlCommand(sp_name, this.conn);
}
//dc.CommandTimeout = 90;
dc.CommandType = CommandType.StoredProcedure;
dc.CommandTimeout = CommandTimeOut;
FillParametersToCommand(dc, dbp);
NpgsqlDataReader ddr = null;
try
{
if (isReturnDataSet)
{
ddr = dc.ExecuteReader();
ds = DataSetStd.FillData(ddr);
rtn.ReturnDataSet = ds;
}
else
{
dc.ExecuteNonQuery();
}
//獲取返回值
foreach (SqlParameter sp in dc.Parameters)
{
if (sp.Direction == ParameterDirection.Output || sp.Direction == ParameterDirection.InputOutput || sp.Direction == ParameterDirection.ReturnValue)
{
rtn.SetValue(sp.ParameterName.Replace(ParameterFlagChar, ""), sp.Value);
}
}
rtn.IsSuccess = true;
}
finally
{
if (ddr != null)
{
ddr.Close();
ddr.Dispose();
}
dc.Dispose();
dc = null;
}
return(rtn);
}
public void Load(NpgsqlDataReader reader)
{
while (reader.Read())
{
Rows.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue));
}
reader.Dispose();
}
public List <MediaCollection> GetMediaCollectionsDescriptions(string UserName, MediaType MediaType)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionString);
NpgsqlCommand command = new NpgsqlCommand(@"SELECT * FROM dbo.""MediaCollections"" WHERE UserName = :UserName AND MediaType = :MediaType", connection);
command.Parameters.Add(new NpgsqlParameter("UserName", NpgsqlDbType.Varchar));
command.Parameters[0].Value = UserName;
command.Parameters.Add(new NpgsqlParameter("MediaType", NpgsqlDbType.Integer));
command.Parameters[1].Value = (int)MediaType;
NpgsqlCommand command2 = new NpgsqlCommand(@"SELECT * FROM dbo.""MediaElements"" WHERE CollectionId = :CollectionId", connection);
connection.Open();
NpgsqlDataReader reader = command.ExecuteReader();
List <MediaCollection> mediacollections = new List <MediaCollection>();
while (reader.Read())
{
MediaCollection mediacollection = new MediaCollection();
mediacollection.Id = (int)reader[0];
mediacollection.UserName = (string)reader[1];
mediacollection.ShortTitle = (string)reader[2];
mediacollection.Title = (string)reader[3];
mediacollection.Description = (string)reader[4];
mediacollection.Location = (string)reader[5];
mediacollection.MediaType = MediaType;
mediacollections.Add(mediacollection);
}
reader.Dispose();
List <MediaElement> mediaelements = new List <MediaElement>();
foreach (MediaCollection mediacollection in mediacollections)
{
command2.Parameters.Add(new NpgsqlParameter("CollectionId", NpgsqlDbType.Integer));
command2.Parameters[0].Value = mediacollection.Id;
NpgsqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
MediaElement mediaelement = new MediaElement();
mediaelement.Id = (int)reader2[0];
mediaelement.CollectionId = (int)reader2[1];
mediaelement.File = (string)reader2[2];
mediaelements.Add(mediaelement);
}
reader2.Dispose();
mediacollection.MediaElements = mediaelements;
}
connection.Close();
return(mediacollections);
}
public IActionResult GetDeviceKey([FromForm] string guid, [FromForm] string devicePIN, [FromForm] string regToken)
{
//sp_getDevice
string errorMessage = "";
bool isError = false;
string deviceKey = "";
NpgsqlConnection conn = new NpgsqlConnection(_connstrWallet);
conn.Open();
try
{
NpgsqlCommand cmd = new NpgsqlCommand("sp_getdevicekey", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("p_guid", guid));
cmd.Parameters.Add(new NpgsqlParameter("p_devicepin", devicePIN));
cmd.Parameters.Add(new NpgsqlParameter("p_regtoken", regToken));
//
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
deviceKey = reader.GetString(0);
}
reader.Dispose();
}
catch (Exception ex)
{
isError = true;
errorMessage = "ErrInvalid";
}
finally
{
conn.Close();
}
if (deviceKey != "")
{
deviceKey = Helper.Decrypt(deviceKey, _csprng);
}
if (isError)
{
return(Json(new ErrorObject(errorMessage)));
}
else
{
dynamic ret = new System.Dynamic.ExpandoObject();
ret.DeviceKey = deviceKey;
return(Json(ret));
}
}
public IActionResult GetFriendRequestPacket([FromForm] string guid, [FromForm] string sharedid, [FromForm] string username)
{
if (!Helper.IsCallerValid(guid, sharedid, _connstrWallet))
{
ErrorObject ro = new ErrorObject();
ro.message = "ErrInvalid";
ro.error = true;
return(Json(ro));
}
//here i am getting a friend request packet that username left for me
string packet = "";;
bool isError = false;
string errorMessage = "";
NpgsqlConnection conn = new NpgsqlConnection(_connstrWallet);
conn.Open();
try
{
NpgsqlCommand com = new NpgsqlCommand("sp_getfriendrequestpacket", conn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new NpgsqlParameter("p_guid", guid));
com.Parameters.Add(new NpgsqlParameter("p_username", username));
NpgsqlDataReader reader = com.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
packet = reader.GetString(0);
}
reader.Dispose();
}
catch (Exception ex)
{
isError = true;
errorMessage = "ErrSystem";
}
finally
{
conn.Close();
}
if (isError)
{
ErrorObject ro = new ErrorObject();
ro.message = errorMessage;
ro.error = true;
return(Json(ro));
}
else
{
ReturnObject ret = new ReturnObject();
ret.message = packet;
return(Json(ret));
}
}
public void getFriendlyAccounts(FriendSystem system)
{
if (system == null)
{
return;
}
if (system._friends.Count == 0)
{
return;
}
try
{
using (NpgsqlConnection npgsqlConnection = SQLjec.getInstance().conn())
{
NpgsqlCommand command = npgsqlConnection.CreateCommand();
npgsqlConnection.Open();
List <string> stringList = new List <string>();
for (int index = 0; index < system._friends.Count; ++index)
{
Friend friend = system._friends[index];
string parameterName = "@valor" + (object)index;
command.Parameters.AddWithValue(parameterName, (object)friend.player_id);
stringList.Add(parameterName);
}
string str = string.Join(",", stringList.ToArray());
command.CommandText = "SELECT player_name,player_id,rank,online,status FROM accounts WHERE player_id in (" + str + ") ORDER BY player_id";
NpgsqlDataReader npgsqlDataReader = command.ExecuteReader();
while (npgsqlDataReader.Read())
{
Friend friend = system.GetFriend(npgsqlDataReader.GetInt64(1));
if (friend != null)
{
friend.player.player_name = npgsqlDataReader.GetString(0);
friend.player._rank = npgsqlDataReader.GetInt32(2);
friend.player._isOnline = npgsqlDataReader.GetBoolean(3);
friend.player._status.SetData((uint)npgsqlDataReader.GetInt64(4), friend.player_id);
if (friend.player._isOnline && !this._contas.ContainsKey(friend.player_id))
{
friend.player.setOnlineStatus(false);
friend.player._status.ResetData(friend.player_id);
}
}
}
command.Dispose();
npgsqlDataReader.Dispose();
npgsqlDataReader.Close();
npgsqlConnection.Dispose();
npgsqlConnection.Close();
}
}
catch (Exception ex)
{
Logger.error("Ocorreu um problema ao carregar (FriendlyAccounts)!\r\n" + ex.ToString());
}
}
