/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
/// <summary>
/// 执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
}
}
}
public static void CreateInsertObject(HistoricalQuote historicalQuote)
{
var ds = new DataSet();
string commandString = "insert into quote(";
commandString += HistoricalQuoteTable.Symbol + ',';
commandString += HistoricalQuoteTable.Date + ',';
commandString += HistoricalQuoteTable.Open + ',';
commandString += HistoricalQuoteTable.High + ',';
commandString += HistoricalQuoteTable.Low + ',';
commandString += HistoricalQuoteTable.Close + ',';
commandString += HistoricalQuoteTable.Volume + ',';
commandString += HistoricalQuoteTable.AdjClose;
commandString += ") values (";
commandString += ':' + HistoricalQuoteTable.Symbol + ',';
commandString += ':' + HistoricalQuoteTable.Date + ',';
commandString += ':' + HistoricalQuoteTable.Open + ',';
commandString += ':' + HistoricalQuoteTable.High + ',';
commandString += ':' + HistoricalQuoteTable.Low + ',';
commandString += ':' + HistoricalQuoteTable.Close + ',';
commandString += ':' + HistoricalQuoteTable.Volume + ',';
commandString += ':' + HistoricalQuoteTable.AdjClose;
commandString += ")";
var da = new NpgsqlDataAdapter("select * from historicalquote", Connection)
{
InsertCommand = new NpgsqlCommand(commandString, Connection)
};
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Symbol, DbType.String));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Date, DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Open, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.High, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Low, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Close, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.Volume, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(HistoricalQuoteTable.AdjClose, DbType.Single));
foreach (NpgsqlParameter p in da.InsertCommand.Parameters)
{
p.Direction = ParameterDirection.Input;
}
da.InsertCommand.Parameters[0].SourceColumn = HistoricalQuoteTable.Symbol;
da.InsertCommand.Parameters[1].SourceColumn = HistoricalQuoteTable.Date;
da.InsertCommand.Parameters[2].SourceColumn = HistoricalQuoteTable.Open;
da.InsertCommand.Parameters[3].SourceColumn = HistoricalQuoteTable.High;
da.InsertCommand.Parameters[4].SourceColumn = HistoricalQuoteTable.Low;
da.InsertCommand.Parameters[5].SourceColumn = HistoricalQuoteTable.Close;
da.InsertCommand.Parameters[6].SourceColumn = HistoricalQuoteTable.Volume;
da.InsertCommand.Parameters[7].SourceColumn = HistoricalQuoteTable.AdjClose;
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
MapFields(dr, HistoricalQuoteTable.Symbol, historicalQuote.Symbol);
MapFields(dr, HistoricalQuoteTable.Date, historicalQuote.Date);
MapFields(dr, HistoricalQuoteTable.Open, historicalQuote.Open);
MapFields(dr, HistoricalQuoteTable.High, historicalQuote.High);
MapFields(dr, HistoricalQuoteTable.Low, historicalQuote.Low);
MapFields(dr, HistoricalQuoteTable.Close, historicalQuote.Close);
MapFields(dr, HistoricalQuoteTable.Volume, historicalQuote.Volume);
MapFields(dr, HistoricalQuoteTable.AdjClose, historicalQuote.AdjClose);
dt.Rows.Add(dr);
DataSet ds2 = ds.GetChanges();
if (ds2 != null)
{
da.Update(ds2);
ds.Merge(ds2);
}
ds.AcceptChanges();
}
public async Task GetUpdateCommandInfersParametersWithNpgsqDbType()
{
using var conn = await OpenConnectionAsync();
await using var _ = await GetTempTableName(conn, out var table);
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (
Cod varchar(5) NOT NULL,
Descr varchar(40),
Data date,
DataOra timestamp,
Intero smallInt NOT NULL,
Decimale money,
Singolo float,
Booleano bit,
Nota varchar(255),
BigIntArr bigint[],
VarCharArr character varying(20)[],
PRIMARY KEY (Cod)
);
INSERT INTO {table} VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note')");
var daDataAdapter =
new NpgsqlDataAdapter(
$"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota, BigIntArr, VarCharArr FROM {table}", conn);
var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter);
var dtTable = new DataTable();
daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand();
daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand();
daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand();
Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[10].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[17].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[26].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[28].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[30].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
daDataAdapter.Fill(dtTable);
var row = dtTable.Rows[0];
Assert.That(row[0], Is.EqualTo("key1"));
Assert.That(row[1], Is.EqualTo("description"));
Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3)));
Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0)));
Assert.That(row[4], Is.EqualTo(123));
Assert.That(row[5], Is.EqualTo(123.4));
Assert.That(row[6], Is.EqualTo(1234.5));
Assert.That(row[7], Is.EqualTo(true));
Assert.That(row[8], Is.EqualTo("note"));
dtTable.Rows[0]["Singolo"] = 1.1D;
Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1));
}
/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
// GENERAL command format
const string foreignKeySql = @"SELECT
tc.constraint_name as ForeignKey,
tc.table_name AS FKTable,
kcu.column_name AS FKColumnName,
ccu.table_name AS PKTable,
ccu.column_name AS PKColumnName,
-1 as update_referential_action,
-1 as delete_referential_action
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'" ;
try {
using (var adapter = new NpgsqlDataAdapter(foreignKeySql, _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable()) {
// Just to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKeyLocal = new DbForeignKey {
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne &&
x.ForeignKeyName == manyMultiplicityKeyLocal.ForeignKeyName))
{
continue;
}
manyMultiplicityKeyLocal.UpdateAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
manyMultiplicityKeyLocal.DeleteAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKeyLocal);
// apply local column
var localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKeyLocal.LocalColumnName);
manyMultiplicityKeyLocal.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKeyLocal.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKeyLocal.ForeignColumnName);
manyMultiplicityKeyLocal.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKeyLocal.ForeignTable = null;
manyMultiplicityKeyLocal.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKeyForeign = new DbForeignKey {
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(
x =>
x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany &&
x.ForeignKeyName == oneMultiplicityKeyForeign.ForeignKeyName))
{
continue;
}
oneMultiplicityKeyForeign.UpdateAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
oneMultiplicityKeyForeign.DeleteAction =
ConvertPosgresqlForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKeyForeign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKeyForeign.LocalColumnName);
oneMultiplicityKeyForeign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKeyForeign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKeyForeign.ForeignColumnName);
oneMultiplicityKeyForeign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKeyForeign.ForeignTable = null;
oneMultiplicityKeyForeign.ForeignColumn = null;
}
}
}// all foreign keys
// look for one-to-one situation!
}
}
}
}
catch {
// Seems this version of postgresql doesn't support this query!
// don't stop here!
}
}
internal static IEnumerable <LocalizedResource> GetLocalizationTable(string language)
{
List <LocalizedResource> resources = new List <LocalizedResource>();
string sql = "SELECT * FROM localization.get_localization_table(@Language) WHERE COALESCE(key, '') != '';";
if (ConnectionStringHelper.DBProvider == "sqlclient")
{
sql = "SELECT * FROM localization.get_localization_table(@Language) WHERE COALESCE([key], '') != ''";
using (SqlConnection connection = new SqlConnection(ConnectionStringHelper.GetConnectionString()))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@Language", language);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
using (DataTable dataTable = new DataTable())
{
dataTable.Locale = CultureManager.GetCurrent();
adapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow row in dataTable.Rows)
{
LocalizedResource resource = new LocalizedResource();
resource.Id = long.Parse(row["id"].ToString());
resource.ResourceClass = row["resource_class"].ToString();
resource.Key = row["key"].ToString();
resource.Original = row["original"].ToString();
resource.Translated = row["translated"].ToString();
resources.Add(resource);
}
}
}
}
}
}
return(resources);
}
using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStringHelper.GetConnectionString()))
{
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@Language", language);
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
{
using (DataTable dataTable = new DataTable())
{
dataTable.Locale = CultureManager.GetCurrent();
adapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow row in dataTable.Rows)
{
LocalizedResource resource = new LocalizedResource();
resource.Id = long.Parse(row["id"].ToString());
resource.ResourceClass = row["resource_class"].ToString();
resource.Key = row["key"].ToString();
resource.Original = row["original"].ToString();
resource.Translated = row["translated"].ToString();
resources.Add(resource);
}
}
}
}
}
}
return(resources);
}
public void GetUpdateCommandInfersParametersWithNpgsqDbType()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery(@"
CREATE TABLE pg_temp.test (
Cod varchar(5) NOT NULL,
Descr varchar(40),
Data date,
DataOra timestamp,
Intero smallInt NOT NULL,
Decimale money,
Singolo float,
Booleano bit,
Nota varchar(255),
CONSTRAINT PK_test_Cod PRIMARY KEY (Cod)
);
INSERT INTO test VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note');
");
var daDataAdapter =
new NpgsqlDataAdapter(
"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota FROM test", conn);
var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter);
var dtTable = new DataTable();
daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand();
daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand();
daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand();
Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[16].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
daDataAdapter.Fill(dtTable);
var row = dtTable.Rows[0];
Assert.That(row[0], Is.EqualTo("key1"));
Assert.That(row[1], Is.EqualTo("description"));
Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3)));
Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0)));
Assert.That(row[4], Is.EqualTo(123));
Assert.That(row[5], Is.EqualTo(123.4));
Assert.That(row[6], Is.EqualTo(1234.5));
Assert.That(row[7], Is.EqualTo(true));
Assert.That(row[8], Is.EqualTo("note"));
dtTable.Rows[0]["Singolo"] = 1.1D;
Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1));
}
}
public async Task <ResultObject> GetCustomItemlist(int userId, int roomid)
{
try
{
using (var sqlcon = new NpgsqlConnection("Server = localhost; Username = postgres; Password = sa; Database = elocare;"))
{
sqlcon.Open();
using (NpgsqlDataAdapter dap = new NpgsqlDataAdapter("fn_ro_registeredroomitems", sqlcon))
{
DataSet dsRegisteredroomitems = new DataSet();
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
dap.SelectCommand.Parameters.Add(new NpgsqlParameter("@rid", roomid));
dap.Fill(dsRegisteredroomitems);
List <CustomItemVM> CustomItem = new List <CustomItemVM>();
if (dsRegisteredroomitems.Tables.Count > 0)
{
_result[ResultKey.Success] = true;
_result[ResultKey.Message] = Message.Success;
foreach (DataRow x in dsRegisteredroomitems.Tables[0].Rows)
{
CustomItemVM objCP = new CustomItemVM();
objCP.ItemId = Convert.ToInt32(x["riid"]);
objCP.ItemName = Convert.ToString(x["riname"]);
objCP.IsActive = Convert.ToByte(x["activeObj"]);
CustomItem.Add(objCP);
}
_result[ResultKey.CustomItemList] = CustomItem;
}
else
{
_result[ResultKey.Success] = true;
_result[ResultKey.Message] = Message.Success;
_result[ResultKey.CustomItemList] = CustomItem;
}
}
sqlcon.Close();
}
//using (var sqlConnection = new SqlConnection(ConnectionString))
//{
// await sqlConnection.OpenAsync();
// using (SqlDataAdapter dap = new SqlDataAdapter("sp_GetCustomItemList", sqlConnection))
// {
// DataSet dsCustomItem = new DataSet();
// dap.SelectCommand.CommandType = CommandType.StoredProcedure;
// dap.SelectCommand.Parameters.AddWithValue("@UserId", userId);
// dap.SelectCommand.Parameters.AddWithValue("@deviceId", deviceId);
// dap.Fill(dsCustomItem);
// List<CustomItemVM> CustomItem = new List<CustomItemVM>();
// if (dsCustomItem.Tables[0].Rows.Count > 0)
// {
// _result[ResultKey.Success] = true;
// _result[ResultKey.Message] = Message.Success;
// foreach (DataRow x in dsCustomItem.Tables[0].Rows)
// {
// CustomItemVM objCP = new CustomItemVM();
// objCP.ItemId = Convert.ToInt32(x["ItemId"]);
// objCP.ItemName = Convert.ToString(x["ItemName"]);
// objCP.IsActive = Convert.ToByte(x["IsActive"]);
// //objCP.iscustom = Convert.ToByte(x["iscustom"]);
// //objCP.IsRegistered = Convert.ToByte(x["RegStatus"]);
// //objCP.RegistrationId = x["RegistrationId"] != DBNull.Value ? Convert.ToInt32(x["RegistrationId"]) : (int?)null;
// CustomItem.Add(objCP);
// }
// _result[ResultKey.CustomItemList] = CustomItem;
// }
// else
// {
// _result[ResultKey.Success] = true;
// _result[ResultKey.Message] = Message.Success;
// _result[ResultKey.CustomItemList] = CustomItem;
// }
// }
// sqlConnection.Close();
//}
return(_result);
}
catch (Exception ex)
{
_result[ResultKey.Success] = false;
_result[ResultKey.Message] = ex.Message;
throw ex;
}
}
/// <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(BoundingBox bbox, FeatureDataSet ds)
{
//List<Geometry> features = new List<Geometry>();
using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.Min.X.ToString(Map.NumberFormatEnUs) + " " +
bbox.Min.Y.ToString(Map.NumberFormatEnUs) + "," +
bbox.Max.X.ToString(Map.NumberFormatEnUs) + " " +
bbox.Max.Y.ToString(Map.NumberFormatEnUs) + ")'::box3d)";
if (SRID > 0)
{
strBbox = "setSRID(" + strBbox + "," + SRID.ToString(Map.NumberFormatEnUs) + ")";
}
string strSQL = "SELECT *, AsBinary(\"" + GeometryColumn + "\") AS sharpmap_tempgeometry ";
strSQL += "FROM " + QualifiedTable + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
if (_SupportSTIntersects)
{
strSQL += "ST_Intersects(\"" + GeometryColumn + "\"," + strBbox + ")";
}
else
{
strSQL += "\"" + GeometryColumn + "\" && " + strBbox;
}
#if DEBUG
Debug.WriteLine(string.Format("{0}\n{1}\n", "ExecuteIntersectionQuery: executing sql:", strSQL));
#endif
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
DataSet ds2 = new DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in ds2.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// This method will load the information for the Entrepreneur account that the user has selected by making a query to our database based off of the ID the we have for this investor.
/// </summary>
private void load_EntrepreneurData()
{
/// <SQL>
/// SELECT EM.entrepreneur_profile_name, IM.image_file, EM.entrepreneur_public
/// FROM entrepreneur_master EM
/// LEFT OUTER JOIN entrepreneur_image_xref EXREF
/// ON EXREF.entrepreneur_master_id = EM.entrepreneur_master_i
/// LEFT OUTER JOIN image_master IM
/// ON IM.image_master_id = EXREF.image_master_id
/// WHERE EM.entrepreneur_master_id = @EntrepreneurID
/// </SQL>
string query = "SELECT EM.entrepreneur_profile_name, IM.image_file, EM.entrepreneur_public FROM entrepreneur_master EM LEFT OUTER JOIN entrepreneur_image_xref EXREF ON EXREF.entrepreneur_master_id = EM.entrepreneur_master_id LEFT OUTER JOIN image_master IM ON IM.image_master_id = EXREF.image_master_id WHERE EM.entrepreneur_master_id = @EntrepreneurID";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
da.SelectCommand.Parameters.AddWithValue("@EntrepreneurID", EntrepreneurID);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
// get profile name
AccountName = (string)ds.Tables[0].Rows[0]["entrepreneur_profile_name"];
// get profile picture
{
if (ds.Tables[0].Rows[0]["image_file"].ToString().Length > 0) // check to make sure that the field is not null before we try any fancy byte[] to image business.
{
// This totaly works and is awesome
img_Entrepreneur.ImageUrl = string.Format(
"data:image/jpg;base64,{0}",
Convert.ToBase64String(
(byte[])ds.Tables[0].Rows[0]["image_file"]
)
);
}
else // we need to make the picture to show be our default picture for empty profiles
{
img_Entrepreneur.ImageUrl = "../images/profile_empty_m.jpg";
}
}
// get wether or not it is public
if (ds.Tables[0].Rows[0]["entrepreneur_public"].ToString() == "True")
{
lbl_ProfilePublic.Text = "Yes";
}
else
{
lbl_ProfilePublic.Text = "No";
}
}
public void Start(string requestId, bool viewSQL)
{
try
{
if (Settings.Count == 0)
{
throw new Exception(CommonMessages.Exception_MissingSettings);
}
status.Code = RequestStatus.StatusCode.InProgress;
string server = Settings.GetAsString("Server", "");
string port = Settings.GetAsString("Port", "");
string userId = Settings.GetAsString("UserID", "");
string password = Settings.GetAsString("Password", "");
string database = Settings.GetAsString("Database", "");
string connectionTimeout = Settings.GetAsString("ConnectionTimeout", "15");
string commandTimeout = Settings.GetAsString("CommandTimeout", "120");
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
log.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout);
log.Debug("Query: " + query);
if (!Settings.ContainsKey("DataProvider"))
{
throw new Exception(CommonMessages.Exception_MissingDataProviderType);
}
string connectionString = string.Empty;
switch ((Lpp.Dns.DataMart.Model.Settings.SQLProvider)Enum.Parse(typeof(Lpp.Dns.DataMart.Model.Settings.SQLProvider), Settings.GetAsString("DataProvider", ""), true))
{
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.PostgreSQL:
if (port == null || port == string.Empty)
{
port = "5432";
}
connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};;Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
// Making connection with Npgsql provider
using (NpgsqlConnection connnection = new NpgsqlConnection(connectionString))
{
try
{
connnection.Open();
NpgsqlCommand command = new NpgsqlCommand(query, connnection);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connnection.Close();
}
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.SQLServer:
if (port != null && port != string.Empty)
{
server += ", " + port;
}
connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}", server, userId, password, database, connectionTimeout) :
String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}", server, database, connectionTimeout);
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
try
{
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection);
command.CommandTimeout = int.Parse(commandTimeout);
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.Oracle:
// TODO: Implement this provider
throw new NotImplementedException("Oracle client not implemented yet");
//connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};;Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
//// TODO: Upgrade Oracle client
//using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionString))
//{
// try
// {
// connection.Open();
// System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(query, connection);
// System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(command);
// resultDataset.Reset();
// da.Fill(resultDataset);
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// connection.Close();
// }
//}
//break;
default:
throw new Exception(CommonMessages.Exception_InvalidDataProviderType);
}
status.Code = RequestStatus.StatusCode.Complete;
status.Message = "";
}
catch (Exception e)
{
status.Code = RequestStatus.StatusCode.Error;
status.Message = e.Message;
throw e;
}
}
private void aftersearch()
{
string consultado = Resources.Propiedades.pacienteenconsulta;
ds = new DataSet();
////////////////////////////////////////////////////////////////////////////////////////
/// pacientes dataset ///
////////////////////////////////////////////////////////////////////////////////////////
try
{
dapaciente.SelectCommand.Parameters.AddWithValue("@paciente", consultado);
dapaciente.Fill(ds, "pacientes");
txtNombre.DataBindings.Add("text", ds.Tables["pacientes"], "nombre");
txtEdad.DataBindings.Add("text", ds.Tables["pacientes"], "edad");
txtAltura.DataBindings.Add("text", ds.Tables["pacientes"], "altura");
txtPeso.DataBindings.Add("text", ds.Tables["pacientes"], "peso");
txtTipoS.DataBindings.Add("text", ds.Tables["pacientes"], "tiposangre");
//idpaciente, nombre,(current_date- fechanac)/365 as edad, altura, peso, tiposangre FROM pacientes.paciente
}
catch (InvalidCastException e)
{
MessageBox.Show(e.Message);
}
////////////////////////////////////////////////////////////////////////////////////////
/// medicamentos dataset ///
////////////////////////////////////////////////////////////////////////////////////////
try
{
damedicamentoscombo.Fill(ds, "medicamentos");
cmbMed.DataSource = ds.Tables["medicamentos"];
cmbMed.DisplayMember = "nombre";
cmbMed.ValueMember = "idmedicamentos";
textBox1.DataBindings.Add("text", ds.Tables["medicamentos"], "cantidad");
//idpaciente, paciente,(current_date- fechanac)/365, altura, peso, tiposangre
}
catch (InvalidCastException e)
{
MessageBox.Show(e.Message);
}
////////////////////////////////////////////////////////////////////////////////////////
/// alergias dataset ///
////////////////////////////////////////////////////////////////////////////////////////
try
{
daalergias.SelectCommand.Parameters.AddWithValue("@paciente", consultado);
daalergias.Fill(ds, "alergiass");
lstAler.DataSource = ds.Tables["alergiass"];
lstAler.DisplayMember = "nombre";
lstAler.ValueMember = "nombre";
//paciente.idpaciente, alegrias.nombre
}
catch (InvalidCastException e)
{
MessageBox.Show(e.Message);
}
////////////////////////////////////////////////////////////////////////////////////////
/// patologias dataset ///
////////////////////////////////////////////////////////////////////////////////////////
try
{
dapadecimientos.SelectCommand.Parameters.AddWithValue("@paciente", consultado);
dapadecimientos.Fill(ds, "patologias");
lstPato.DataSource = ds.Tables["patologias"];
lstPato.DisplayMember = "nombre";
lstPato.ValueMember = "nombre";
//select paciente.idpaciente, sscm.nombre
}
catch (InvalidCastException e)
{
MessageBox.Show(e.Message);
}
////////////////////////////////////////////////////////////////////////////////////////
/// procedimientos dataset ///
////////////////////////////////////////////////////////////////////////////////////////
try
{
daprocedimientos.Fill(ds, "procedimientos");
cmbProd.DataSource = ds.Tables["procedimientos"];
cmbProd.DisplayMember = "nombre";
cmbProd.ValueMember = "procid";
//paciente.idpaciente, alergias.descripcionprocid
}
catch (InvalidCastException e)
{
MessageBox.Show(e.Message);
}
////////////////////////////////////////////////////////////////////////////////////////
}
public static DataSet GetDataSet(dbOrigen source, string sSQL, object[] aParam)
{
int i = 1;
DataSet oDs = new DataSet();
switch (source)
{
case dbOrigen.Ninguno:
FillExceptionDataSet(oDs, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
OracleDataAdapter oraAdaptador = new OracleDataAdapter(oraCmd);
oraAdaptador.Fill(oDs);
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
pgCmd.CommandTimeout = DBSettings.PGSQLCmdTimeOut;
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
NpgsqlDataAdapter pgAdaptador = new NpgsqlDataAdapter(pgCmd);
pgAdaptador.Fill(oDs);
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(oDs);
}
private void finishedProductsBtn_Click(object sender, RoutedEventArgs e)
{
Int32 Tabcount = 0;
string connstring = ConfigurationManager.ConnectionStrings["LTCTrace.DBConnectionString"].ConnectionString;
try
{
using (var conn = new NpgsqlConnection(connstring))
{
conn.Open();
string start = "'" + startDate.SelectedDate.Value.Year.ToString() + "-" + startDate.SelectedDate.Value.Month.ToString() + "-" + startDate.SelectedDate.Value.Day.ToString() + "'";
string end = "'" + endDate.SelectedDate.Value.Year.ToString() + "-" + endDate.SelectedDate.Value.Month.ToString() + "-" + endDate.SelectedDate.Value.Day.ToString() + "'";
string filterByComponent = String.Empty;
if (prodCbx.SelectedValue.ToString() == "fb_dm")
{
filterByComponent = "AND housing_fb_assy.fb_dm = '" + prodDmTbx.Text + "' ";
}
else if (prodCbx.SelectedValue.ToString() == "housing_dm")
{
filterByComponent = "AND housing_fb_assy.housing_dm = '" + prodDmTbx.Text + "' ";
}
else if (prodCbx.SelectedValue.ToString() == "mb_dm")
{
filterByComponent = "AND mb_dsp_assy.mb_dm = '" + prodDmTbx.Text + "' ";
}
else if (prodCbx.SelectedValue.ToString() == "gw_dm")
{
filterByComponent = "AND final_assy_two.gw_dm = '" + prodDmTbx.Text + "' ";
}
string Querycmd = @"SELECT
final_assy_two.saved_on as ""Összeszerelve"",
final_assy_two.housing_dm as ""Ház"",
final_assy_one.mb_dm as ""Mainboard"",
final_assy_two.gw_dm as ""Gateway"",
housing_fb_assy.fb_dm as ""Filterboard"",
mb_dsp_assy.dsp_one_one as ""DSP 11"",
mb_dsp_assy.dsp_one_two as ""DSP 12"",
mb_dsp_assy.dsp_one_three as ""DSP 13"",
mb_dsp_assy.dsp_two_one as ""DSP 21"",
mb_dsp_assy.dsp_two_two as ""DSP 22"",
mb_dsp_assy.dsp_two_three as ""DSP 23""
FROM housing_fb_assy
FULL JOIN final_assy_one on final_assy_one.housing_dm = housing_fb_assy.housing_dm
FULL JOIN mb_dsp_assy on mb_dsp_assy.mb_dm = final_assy_one.mb_dm
FULL JOIN final_assy_two on final_assy_two.housing_dm = final_assy_one.housing_dm
WHERE date(final_assy_two.saved_on) >= " + start + " and date(final_assy_two.saved_on) <= " + end +
filterByComponent +
"order by final_assy_two.saved_on desc";
string CountQuerycmd = @"select count(*) from (" + Querycmd + ") as q";
Querycmd += " offset " + dbQueryOffset + " limit 200";
var dataAdapter = new NpgsqlDataAdapter(Querycmd, conn);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
resultDataGrid.ItemsSource = dataTable.AsDataView();
var countcmd = new NpgsqlCommand(CountQuerycmd, conn);
Tabcount = Convert.ToInt32(countcmd.ExecuteScalar());
resultRowCount.Content = Tabcount;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Tabs.Children.Clear();
for (int i = 0; i <= Tabcount / 200; i++)
{
Button newBtn = new Button();
newBtn.Background = Brushes.White;
newBtn.Foreground = Brushes.DarkSlateGray;
newBtn.BorderThickness = new Thickness(0);
newBtn.Focusable = false;
newBtn.Click += getOffsetForFinishedProducts;
newBtn.Content = (i + 1).ToString();
newBtn.Name = "Tab" + (i + 1).ToString();
newBtn.Width = 28;
newBtn.Margin = new Thickness(1, 1, 1, 0);
newBtn.FontSize = 15;
Tabs.Children.Add(newBtn);
//change back and foreground color on active button
if (int.Parse(dbQueryOffset) == i * 200)
{
newBtn.Background = Brushes.DarkSlateGray;
newBtn.Foreground = Brushes.White;
}
}
dbQueryOffset = "0";
}
private void ExportBtn_Click(object sender, EventArgs e)
{
String resultat = "";
String result = "";
var connstring = ConfigurationManager.ConnectionStrings["LTCTrace.DBConnectionString"].ConnectionString;
using (var conn = new NpgsqlConnection(connstring))
{
conn.Open();
for (int i = 0; i < Tabs.Children.Count; i++)
{
//fill datagrid
string sql = getSQLcommand();
var dataAdapter = new NpgsqlDataAdapter(sql, conn);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
resultDataGrid.ItemsSource = dataTable.AsDataView();
dbQueryOffset = ((i + 1) * 200).ToString();
// concatenate the tabs
resultDataGrid.SelectAllCells();
if (i == 0)
{
resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
}
else
{
resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.ExcludeHeader;
}
ApplicationCommands.Copy.Execute(null, resultDataGrid);
resultat += (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
result += (string)Clipboard.GetData(DataFormats.Text);
resultDataGrid.UnselectAllCells();
// set back the default result
if (i == Tabs.Children.Count - 1)
{
foreach (var item in Tabs.Children)
{
if ((item as Button).Background == Brushes.DarkSlateGray)
{
dbQueryOffset = (int.Parse((item as Button).Content.ToString()) * 200 - 200).ToString();
}
}
ListBtn_Click(sender, e as RoutedEventArgs);
}
}
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel |*.xls";
//saveFileDialog.DefaultExt = "xls";
//saveFileDialog.AddExtension = true;
if (saveFileDialog.ShowDialog() == true)
{
System.IO.StreamWriter file1 = new System.IO.StreamWriter(saveFileDialog.FileName);
file1.WriteLine(result.Replace(',', ' '));
file1.Close();
}
}
private void ListBtn_Click(object sender, RoutedEventArgs e)
{
Int32 Tabcount = 0;
using (new WaitCursor())
{
try
{
string connstring = ConfigurationManager.ConnectionStrings["LTCTrace.DBConnectionString"].ConnectionString;
var conn = new NpgsqlConnection(connstring);
conn.Open();
string sql = getSQLcommand();//query;
var dataAdapter = new NpgsqlDataAdapter(sql, conn);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
resultDataGrid.ItemsSource = dataTable.AsDataView();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i].ColumnName.Contains("pic"))
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
byte[] blob = (byte[])dataTable.Rows[j][i];
MemoryStream stream = new MemoryStream();
if (blob.Length > 10)
{
// change the "byte [] array" text to something more readable.
}
else
{
dataTable.Rows[j][i] = null;
}
}
}
}
// get the query result count
var countcmd = new NpgsqlCommand(getSQLcount(), conn);
Tabcount = Convert.ToInt32(countcmd.ExecuteScalar());
resultRowCount.Content = Tabcount;
conn.Close();
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
}
resultDataGrid.Columns[0].Width = 70;
if (resultDataGrid.Columns[1].Header.ToString() == "comments")
{
resultDataGrid.Columns[1].Width = 1500;
}
}
Tabs.Children.Clear();
for (int i = 0; i <= Tabcount / 200; i++)
{
Button newBtn = new Button();
newBtn.Background = Brushes.White;
newBtn.Foreground = Brushes.DarkSlateGray;
newBtn.BorderThickness = new Thickness(0);
newBtn.Focusable = false;
newBtn.Click += getOffset;
newBtn.Content = (i + 1).ToString();
newBtn.Name = "Tab" + (i + 1).ToString();
newBtn.Width = 28;
newBtn.Margin = new Thickness(1, 1, 1, 0);
newBtn.FontSize = 15;
Tabs.Children.Add(newBtn);
//change back and foreground color on active button
if (int.Parse(dbQueryOffset) == i * 200)
{
newBtn.Background = Brushes.DarkSlateGray;
newBtn.Foreground = Brushes.White;
}
}
dbQueryOffset = "0";
}
public void AgregarPrograma(DTOProgram program)
{
string sSel;
string sSelCount;
bool exist;
sSelCount = "SELECT COUNT(*) FROM \"tbl_Program\" WHERE \"idProgram\"=" + program.IdProgram;
NpgsqlDataAdapter daCount;
DataSet dtCount = new DataSet();
try
{
daCount = new NpgsqlDataAdapter(sSelCount, sConexion);
daCount.Fill(dtCount);
if (dtCount.Tables[0].Rows[0][0].ToString() == "0")
{
exist = false;
}
else
{
exist = true;
}
}
catch (Exception)
{
exist = false;
}
if (!exist)
{
sSel = "INSERT INTO \"tbl_Program\" VALUES(" + program.IdProgram;
if (program.Title != "")
{
sSel = sSel + ",'" + program.Title.Replace("'", "") + "'";
}
else
{
sSel = sSel + ",NULL";
}
if (program.RTitle != "")
{
sSel = sSel + ",'" + program.RTitle.Replace("'", "") + "'";
}
else
{
sSel = sSel + ",NULL";
}
if (program.Description != "")
{
sSel = sSel + ",'" + program.Description.Replace("'", "") + "'";
}
else
{
sSel = sSel + ",NULL";
}
if (program.RDescription != "")
{
sSel = sSel + ",'" + program.RDescription.Replace("'", "") + "'";
}
else
{
sSel = sSel + ",NULL";
}
if (program.EpisodeTitle != "")
{
sSel = sSel + ",'" + program.EpisodeTitle.Replace("'", "") + "'";
}
else
{
sSel = sSel + ",NULL";
}
if (program.IdCategory != 0)
{
sSel = sSel + "," + program.IdCategory + ");";
}
else
{
sSel = sSel + ",NULL);";
}
NpgsqlDataAdapter da;
DataSet dt = new DataSet();
try
{
da = new NpgsqlDataAdapter(sSel, sConexion);
da.Fill(dt);
}
catch (Exception)
{
}
}
}
/// <summary>
/// This method will load results for projects that are tied to this entrepreneur account from our DataBase.
/// Projects are modeled in our data base off of the PROCs table.
/// </summary>
private void load_Projects()
{
/// <SQL> Statement that will be used in the future when projects have multiple images associated with them.
/// SELECT *
/// FROM project_master PM
/// LEFT OUTER JOIN project_image_xref PIXREF
/// ON PIXREF.project_master_id = PM.project_master_id
/// LEFT OUTER JOIN image_master IM
/// ON IM.image_master_id = PIXREF.image_master_id
/// WHERE PM.project_master_id = @ProjectID
/// </SQL>
string query = "SELECT project_master_id, project_description, create_date_time, investment_goal FROM project_master WHERE entrepreneur_master_id = @EntrepreneurID";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
da.SelectCommand.Parameters.AddWithValue("@EntrepreneurID", EntrepreneurID);
DataSet ds = new DataSet();
try
{
// open connection
conn.Open();
da.Fill(ds);
}
catch (Exception e)
{
/// <TODO>
/// Handle something going wrong here
/// </TODO>
}
finally
{
// always close conection
conn.Close();
}
// if our query resturned no rows we should probably inform the user instead of making an empty table
try
{
if (ds.Tables[0].Rows.Count < 0)
{
ProjectsResults = "<h3>No Projects were found for your account... perhaps you should make one</h3>";
}
else // start building what we will render onto the page by placing an open table bracket and table head to be shown
{
ProjectsResults += "<h3>Projects</h3><br />";
ProjectsResults += "<table id='tableProjectAccounts' class='table table-hover table-striped table-condensed'>";
ProjectsResults += "<thead><tr><th>Description</th><th>Date Created</th><th>Investment goal</th></tr></thead>";
ProjectsResults += "<tbody>";
foreach (DataRow i in ds.Tables[0].Rows)
{ // loop over results set of our query for each row add a row to our html table
ProjectsResults += "<tr>";
ProjectsResults += "<td><a href='" + "ViewProject/" + i["project_master_id"].ToString() + "'>" + i["project_description"].ToString() + "</a></td>";
ProjectsResults += "<td>" + i["create_date_time"].ToString() + "</td>";
ProjectsResults += "<td>" + i["investment_goal"].ToString() + " </td>";
ProjectsResults += "</tr>";
}
// after loop, now add the closing table tags
ProjectsResults += "</tbody>";
ProjectsResults += "</table>";
}
}
catch (Exception e)
{
ProjectsResults = "<h3>Something went wrong trying to retrive data about your profile!</h3>";
}
}
public FeatureDataTable QueryFeatures(Geometry geom, double distance)
{
//Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>();
using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (SRID > 0)
{
strGeom = "setSRID(" + strGeom + "," + SRID + ")";
}
string strSQL = "SELECT * , AsBinary(\"" + GeometryColumn + "\") As sharpmap_tempgeometry FROM " +
QualifiedTable + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
strSQL += "\"" + GeometryColumn + "\" && " + "buffer(" + strGeom + "," +
distance.ToString(Map.NumberFormatEnUs) + ")";
strSQL += " AND distance(\"" + GeometryColumn + "\", " + strGeom + ")<" +
distance.ToString(Map.NumberFormatEnUs);
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return(fdt);
}
else
{
return(null);
}
}
}
}
static public void Main()
{
// TODO: change to your real values
string connString = "Server=127.0.0.1; Port=9999; Database=LOCALDB; User Id=sa; Password="******"Established connection to PG2LucidDB");
// get list of locations:
Console.WriteLine("LOCATIONS table");
Console.WriteLine("ID NAME MANAGER SQUARE ISFASTFOOD ISCLOSED DATEADDED DATEOPENED");
Console.WriteLine("");
string SQL = "SELECT ID, NAME, MANAGER, SQUARE, ISFASTFOOD, ISCLOSED, DATEADDED, DATEOPENED FROM PG2LUCIDDBTEST.LOCATION ORDER BY ID";
NpgsqlCommand comm = conn.CreateCommand();
comm.CommandText = SQL;
// execute reader:
NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}", dr["ID"], dr["NAME"], dr["MANAGER"], dr["SQUARE"], dr["ISFASTFOOD"], dr["ISCLOSED"], dr["DATEADDED"], dr["DATEOPENED"]);
}
dr.Close();
// fill dataset:
// get list of SKUs:
Console.WriteLine("");
Console.WriteLine("SKU table");
comm.CommandText = "SELECT ID, NAME, PRICE, CURRENCY FROM PG2LUCIDDBTEST.SKU ORDER BY ID";
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comm);
da.Fill(ds);
// write header:
string tmpHeader = "";
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
tmpHeader += ds.Tables[0].Columns[i].ColumnName + "\t";
}
Console.WriteLine(tmpHeader);
Console.WriteLine("");
// write data:
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string tmpLine = "";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
tmpLine += String.Format("{0}\t", ds.Tables[0].Rows[i][j]);
}
Console.WriteLine(tmpLine);
}
// get list of sales_fact:
Console.WriteLine("");
Console.WriteLine("SALES_FACT table");
comm.CommandText = "SET SCHEMA 'PG2LUCIDDBTEST'";
comm.ExecuteNonQuery();
comm.CommandText = "SELECT T1.TRANSACTIONID, T2.NAME, T3.NAME, T1.TRANSACTIONTIME, T1.TRANSACTIONDATE FROM SALES_FACT T1 INNER JOIN LOCATION T2 ON T2.ID = T1.LOCATIONID INNER JOIN SKU T3 ON T3.ID = T1.SKUID WHERE T1.TRANSACTIONDATE = APPLIB.CHAR_TO_DATE('yyyy-M-d', '2009-09-23') ORDER BY T1.TRANSACTIONID";
ds = new DataSet();
da = new NpgsqlDataAdapter(comm);
da.Fill(ds);
// write header:
tmpHeader = "";
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
tmpHeader += ds.Tables[0].Columns[i].ColumnName + "\t";
}
Console.WriteLine(tmpHeader);
Console.WriteLine("");
// write data:
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string tmpLine = "";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
tmpLine += String.Format("{0}\t", ds.Tables[0].Rows[i][j]);
}
Console.WriteLine(tmpLine);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception occured: {0}", ex);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public static void snap_to_network(string dbconnect, string dbnetwork, string epsg, string dbschema, string dbtable, string idcode)
{
String daCmdTxt;
String fullname = dbschema + "." + dbtable;
StringBuilder sb = new StringBuilder();
DataTable dtCoords = new DataTable();
//connect to database
using (NpgsqlConnection dbConnection = new NpgsqlConnection(dbconnect))
{
dbConnection.Open();
//create command object
using (NpgsqlCommand dbCmd = dbConnection.CreateCommand())
{
//create a 'snapid' column
dbCmd.CommandText =
@"Alter Table " + fullname + " Drop Column If Exists snapid;" +
@"Alter Table " + fullname + " Add Column snapid Integer;";
dbCmd.ExecuteNonQuery();
//place all point coordinates into a datatable
daCmdTxt = @"Select " + idcode + " as id, St_X(geom) as x, St_Y(geom) as y From " + fullname + ";";
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(daCmdTxt, dbConnection))
{
da.Fill(dtCoords);
}
//check if integer or character identifier column
dbCmd.CommandText = @"Select data_type From information_schema.columns Where"
+ " table_name = '" + dbtable + "' and column_name = '" + idcode + "';";
string typ = dbCmd.ExecuteScalar().ToString();
if (typ.Substring(0, 3) == "cha")
{
//find nearest network node for each point, placing result in List "snaps"
foreach (DataRow dr in dtCoords.Rows)
{
dbCmd.CommandText =
"Select id::integer From " + dbnetwork
+ " Order By the_geom <-> ST_SetSrid(ST_MakePoint("
+ dr["x"].ToString() + "," + dr["y"].ToString() + "), " + epsg + ") LIMIT 1;";
sb.Append("Update " + fullname + " Set snapid = "
+ dbCmd.ExecuteScalar().ToString()
+ " Where " + idcode + " = '" + dr["id"].ToString() + "';");
}
}
else if (typ.Substring(0, 3) == "int")
{
//find nearest network node for each point, placing result in List "snaps"
foreach (DataRow dr in dtCoords.Rows)
{
dbCmd.CommandText =
"Select id::integer From " + dbnetwork
+ " Order By the_geom <-> ST_SetSrid(ST_MakePoint("
+ dr["x"].ToString() + "," + dr["y"].ToString() + "), " + epsg + ") LIMIT 1;";
sb.Append("Update " + fullname + " Set snapid = "
+ dbCmd.ExecuteScalar().ToString()
+ " Where " + idcode + " = " + dr["id"].ToString() + ";");
}
}
else
{
System.Windows.Forms.MessageBox.Show("Must select an integer or text field for point ids", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
Environment.Exit(1);
}
dbCmd.CommandText = sb.ToString();
dbCmd.ExecuteNonQuery();
}
dbConnection.Close();
}
}
//Funcion en cargada de converir todos los datos que estan en la base de datos
//a objetos que se pueden utilizar en el API
//Esta funcion carga los datos iniciales de populacion que se encuentran en la base de datos.
// Lo hace para la clase tipo, usuario, distribuidor y dispositivo
public void iniciar_Base_Datos()
{
// ------------------------------ Tipo
string queryTipo = "SELECT * FROM TIPO";
NpgsqlCommand conectorTipo = new NpgsqlCommand(queryTipo, coneccion);
NpgsqlDataAdapter datosTipo = new NpgsqlDataAdapter(conectorTipo);
DataTable tabladatosTipo = new DataTable();
datosTipo.Fill(tabladatosTipo);
foreach (DataRow row in tabladatosTipo.Rows)
{
string nombreT = row["Nombre"].ToString();
string DescripcionT = row["Descripcion"].ToString();
int TiempoGarantia = Convert.ToInt32(row["TiempoGarantia"]);
Tipo tip = new Tipo(nombreT, DescripcionT, TiempoGarantia);
Administrador.Lista_tipos.Add(tip);
//Console.WriteLine(nombreT + " , " + DescripcionT + " , " + TiempoGarantia );
}
// ------------------------------ Dispositivos
string queryDispositovos = "SELECT * FROM DISPOSITIVO";
NpgsqlCommand conectorDispositovos = new NpgsqlCommand(queryDispositovos, coneccion);
NpgsqlDataAdapter datosDispositovos = new NpgsqlDataAdapter(conectorDispositovos);
DataTable tabladatosDispositovos = new DataTable();
datosDispositovos.Fill(tabladatosDispositovos);
foreach (DataRow row in tabladatosDispositovos.Rows)
{
int numeroSerie = Convert.ToInt32(row["NumeroSerie"]);
string nombre = row["Nombre"].ToString();
int precio = Convert.ToInt32(row["Precio"]);
string marca = row["Marca"].ToString();
int consumoElectrico = Convert.ToInt32(row["ConsumoElectrico"]);
string nombreTipo = row["NombreTipo"].ToString();
Tipo tipo = Administrador.obtnenerTipo(nombreTipo);
Dispositivo dip = new Dispositivo(nombre, precio, tipo, numeroSerie, marca, consumoElectrico);
Administrador.lista_Dispositivos.Add(dip);
//Console.WriteLine(numeroSerie + " , " + nombre + " , " + precio + " , " + tipo.nombre + " , " + marca + " , " + consumoElectrico );
}
// ------------------------------ DISTRIBUIDOR
string queryDistribuidor = "SELECT * FROM DISTRIBUIDOR";
NpgsqlCommand conectorDistribuidor = new NpgsqlCommand(queryDistribuidor, coneccion);
NpgsqlDataAdapter datosDistribuidor = new NpgsqlDataAdapter(conectorDistribuidor);
DataTable tabladatosDistribuidor = new DataTable();
datosDistribuidor.Fill(tabladatosDistribuidor);
foreach (DataRow row in tabladatosDistribuidor.Rows)
{
int cedulaJuridica = Convert.ToInt32(row["CedulaJuridica"]);
string regionD = row["Region"].ToString();
string nombreD = row["Nombre"].ToString();
Distribuidor distri = new Distribuidor(nombreD, cedulaJuridica, regionD);
Administrador.lista_Distribuidores.Add(distri);
//Console.WriteLine(cedulaJuridica + " , " + regionD + " , " + nombreD );
}
// ------------------------------ usuarios
string queryUsuario = "SELECT * FROM USUARIO";
NpgsqlCommand conectorUsuario = new NpgsqlCommand(queryUsuario, coneccion);
NpgsqlDataAdapter datosUsuario = new NpgsqlDataAdapter(conectorUsuario);
DataTable tabladatosUsuario = new DataTable();
datosUsuario.Fill(tabladatosUsuario);
foreach (DataRow row in tabladatosUsuario.Rows)
{
string correoU = row["Correo"].ToString();
string contrasenaU = row["Contrasena"].ToString();
string nombreU = row["Nombre"].ToString();
string apellidoU = row["Apellido"].ToString();
string regionU = row["Region"].ToString();
string paisU = row["Pais"].ToString();
string direccionU = row["Direccion"].ToString();
Usuario usu = new Usuario(nombreU, apellidoU, paisU, regionU, correoU, contrasenaU, direccionU);
Administrador.lista_Usuarios.Add(usu);
//Console.WriteLine(correoU + " , " + contrasenaU + " , " + nombreU + " , " + apellidoU + " , " + regionU + " , " + paisU + " , " + direccionU);
}
}
public void CargarEnListView(ListView Lista, string consulta)
{
SqlConnection sqlCnx = null;
OleDbConnection accCnx = null;
MySqlConnection mysqlCnx = null;
//SQLiteConnection sqliteCnx = null;
NpgsqlConnection posCnx = null;
switch (BaseDatos)
{
case "SQL":
sqlCnx = (SqlConnection)BDConnection;
break;
case "MySQL":
mysqlCnx = (MySqlConnection)BDConnection;
break;
case "Access":
accCnx = (OleDbConnection)BDConnection;
break;
case "PostgreSQL":
posCnx = (NpgsqlConnection)BDConnection;
break;
}
try
{
SqlDataAdapter daSql = null;
OleDbDataAdapter daAccess = null;
MySqlDataAdapter daMySql = null;
NpgsqlDataAdapter daPos = null;
DataSet ds = new DataSet();
switch (BaseDatos)
{
case "SQL":
daSql = new SqlDataAdapter(consulta, sqlCnx);
daSql.Fill(ds);
break;
case "MySQL":
daMySql = new MySqlDataAdapter(consulta, mysqlCnx);
daMySql.Fill(ds);
break;
case "Access":
daAccess = new OleDbDataAdapter(consulta, accCnx);
daAccess.Fill(ds);
break;
case "SQLite":
daPos = new NpgsqlDataAdapter(consulta, posCnx);
daPos.Fill(ds);
break;
default:
break;
}
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Lista.Columns.Add(ds.Tables[0].Columns[i].ColumnName);
Lista.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
ListViewItem item = new ListViewItem(row[0].ToString());
for (int j = 1; j < ds.Tables[0].Columns.Count; j++)
{
item.SubItems.Add(row[j].ToString());
}
Lista.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show("Error en la conexión: \n" + ex, "Mensaje", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public Orders DoRetrieveUserOrderDetails(int orderId)
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ToString();
connection.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = connection;
cmd.CommandText = "Select * from Orders WHERE orderId=@orderId";
cmd.CommandType = CommandType.Text;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
da.SelectCommand.Parameters.AddWithValue("@orderId", orderId);
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Dispose();
connection.Close();
int cid = Convert.ToInt32(dt.Rows[0]["cId"].ToString());
string transactionId = dt.Rows[0]["transactionId"].ToString();
string paymentType = dt.Rows[0]["paymentType"].ToString();
string cardNum = dt.Rows[0]["cardNum"].ToString();
string deliverAddress = dt.Rows[0]["deliverAddress"].ToString();
string contactNo = dt.Rows[0]["contactNo"].ToString();
double deliveryFee = Convert.ToDouble(dt.Rows[0]["deliveryFee"].ToString());
double totalCost = Convert.ToDouble(dt.Rows[0]["totalCost"].ToString());
string isPaid = dt.Rows[0]["isPaid"].ToString();
string status = dt.Rows[0]["status"].ToString();
DateTime orderCreated = DateTime.Parse(dt.Rows[0]["orderCreated"].ToString());
int rId = 0;
DateTime arriveTime = new DateTime();
DateTime departTime = new DateTime();
DateTime deliverTime = new DateTime();
if (DBNull.Value != dt.Rows[0]["rId"])
{
rId = Convert.ToInt32(dt.Rows[0]["rId"].ToString());
}
if (DBNull.Value != dt.Rows[0]["arriveTime"])
{
arriveTime = DateTime.Parse(dt.Rows[0]["arriveTime"].ToString());
}
if (DBNull.Value != dt.Rows[0]["departTime"])
{
departTime = DateTime.Parse(dt.Rows[0]["departTime"].ToString());
}
if (DBNull.Value != dt.Rows[0]["deliverTime"])
{
deliverTime = DateTime.Parse(dt.Rows[0]["deliverTime"].ToString());
}
Orders od = new Orders(orderId, cid, transactionId, rId, paymentType, cardNum, deliverAddress, contactNo, deliveryFee
, totalCost, orderCreated, arriveTime, departTime, deliverTime, isPaid, status);
return(od);
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(null);
}
/// <summary>
/// Read columns schema from database
/// </summary>
private List <DbColumn> ReadColumns(String tableName, string ownerName)
{
var result = new List <DbColumn>();
using (var adapter = new NpgsqlDataAdapter(String.Format("SELECT * FROM \"{0}\".\"{1}\" LIMIT 1", ownerName, tableName), _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable columnsSchema;
// Jjust to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
using (var columnsList = new DataTable()) {
tempDs.Tables.Add(columnsList);
// Get from db
adapter.Fill(columnsList);
// Get schema
using (var reader = new DataTableReader(columnsList))
columnsSchema = reader.GetSchemaTable();
}
}
// Used to get columns Npgsql DataType
using (DataTable columnsDbTypeTable = _dbConnection.GetSchema("Columns")) {
// Fetch the rows
foreach (DataRow dr in columnsSchema.Rows)
{
var columnName = dr["ColumnName"].ToString();
var column = new DbColumn(columnName, columnName)
{
DataTypeDotNet = dr["DataType"].ToString(),
Length = Convert.ToInt32(dr["ColumnSize"]),
PrimaryKey = Convert.ToBoolean(dr["IsKey"]),
AutoIncrement = Convert.ToBoolean(dr["IsAutoIncrement"]),
AllowNull = Convert.ToBoolean(dr["AllowDBNull"]),
ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]),
};
column.FieldNameSchema = DbSchemaNames.FieldName_RemoveInvalidChars(column.FieldNameSchema);
// Columns which needs additional fetch
var succeed = FillColumnAdditionalInfo(column, columnsDbTypeTable, tableName, columnName);
// if additional info readin is failed, don't add it to the list
if (succeed)
{
// Add to result
result.Add(column);
}
else
{
// TODO: inform the user
}
}
}
}
return(result);
}
private void auto_update_dg()
{
NpgsqlConnection conn = new NpgsqlConnection(log_in_form.prisijungimas.ConnectionString);
string SlctProjects_Str = "Select * from projects where completed != true and (involved like @vartotojas" +
" or creator like @vartotojas) and " +
"(upper(name) like @search or upper(creator) like @search or upper(involved) like @search)";
NpgsqlCommand SlctProjects_Cmd = new NpgsqlCommand(SlctProjects_Str, conn);
string slct_tasks_str =
"Select tasks.*, projects.name as project_name from tasks join projects on tasks.project_id = projects.id " +
"where tasks.completed != true and (tasks.involved like @vartotojas or" +
" responsible like @vartotojas or tasks.creator like @vartotojas) and " +
"(upper(tasks.name) like @search or upper(tasks.creator) like @search or upper(tasks.involved) like @search)";
NpgsqlCommand slct_tasks_cmd = new NpgsqlCommand(slct_tasks_str, conn);
string search_str = "";
string vartotojas = "";
string search_tasks_str = "";
Dispatcher.Invoke(() => { vartotojas = log_in_form.prisijunges_vartotojas; });
conn.Open();
while ((conn.State & ConnectionState.Open) != 0)
{
Dispatcher.Invoke(() => { search_str = ProjectSearchTb.Text.ToUpper(); });
if (search_str == "IEŠKOTI")
{
search_str = "";
}
SlctProjects_Cmd.Parameters.AddWithValue("@search", "%" + search_str + "%");
SlctProjects_Cmd.Parameters.AddWithValue("@vartotojas", "%" + vartotojas + "%");
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(SlctProjects_Cmd))
{
DataTable dt = new DataTable("Projects");
da.Fill(dt);
if (Projects_Dg != null)
{
Dispatcher.Invoke(() => { Projects_Dg.ItemsSource = dt.AsDataView(); });
}
}
Dispatcher.Invoke(() => { search_tasks_str = TaskSearchTb.Text.ToUpper(); });
if (search_tasks_str == "IEŠKOTI")
{
search_tasks_str = "";
}
slct_tasks_cmd.Parameters.AddWithValue("@search", "%" + search_tasks_str + "%");
slct_tasks_cmd.Parameters.AddWithValue("@vartotojas", "%" + vartotojas + "%");
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(slct_tasks_cmd))
{
DataTable dt = new DataTable("Projects");
da.Fill(dt);
if (Tasks_Dg != null)
{
Dispatcher.Invoke(() => { Tasks_Dg.ItemsSource = dt.AsDataView(); });
}
}
Thread.Sleep(5000);
}
auto_update_dg();
}
/// <summary>
/// Reads tables index keys
/// </summary>
private void ApplyTablesConstraintKeys(IEnumerable <DbTable> tables)
{
const string constraintKeySql = @"SELECT
t.relname as TableName
,a.attname as ColumnName
,c.relname as IndexName
,i.indisunique as IsUnique
,i.indisprimary as IsPrimaryKey
,FALSE as IgnoreDuplicateKey
,FALSE as IsUniqueConstraintKey
,FALSE as Disabled
FROM
pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_index i on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
join pg_catalog.pg_attribute a on a.attrelid = c.oid
WHERE
c.relkind = 'i' and n.nspname not in ('pg_catalog', 'pg_toast') and pg_catalog.pg_table_is_visible(c.oid)" ;
using (var adapter = new NpgsqlDataAdapter(constraintKeySql, _dbConnection)) {
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable()) {
// Just to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet()) {
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
// find description if there is any
foreach (var table in tables)
{
// filter row
keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 ";
// fetch findings, if there is any
foreach (DataRowView keysDataRow in keysData.DefaultView)
{
// found table !
DataRow keyRow = keysDataRow.Row;
// constraint Key
var constraintKey = new DbConstraintKey()
{
//IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()),
KeyColumnName = keyRow["ColumnName"].ToString(),
//KeyName = keyRow["IndexName"].ToString()
};
// constraint keys
//table.Indexes.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
}
}
}
private void _LoadAll(string query1, string query2)
{
NpgsqlConnection cn = null;
try
{
cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
DataTable metaData1 = new DataTable();
DataTable metaData2 = new DataTable();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query1, cn);
adapter.Fill(metaData1);
adapter = new NpgsqlDataAdapter(query2, cn);
adapter.Fill(metaData2);
DataRowCollection rows = metaData2.Rows;
int count = rows.Count;
for (int i = 0; i < count; i++)
{
metaData1.ImportRow(rows[i]);
}
PopulateArrayNoHookup(metaData1);
if (metaData1.Rows.Count > 0)
{
string catalog = this.Table.Database.Name;
string schema;
string table;
string[] cols = null;
string q;
string query =
"SELECT a.attname as COLUMN from pg_attribute a, pg_class c, pg_namespace n " +
"WHERE a.attrelid = c.oid AND c.relnamespace = n.oid " +
"AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind = 'r' ";
foreach (ForeignKey key in this)
{
//------------------------------------------------
// Primary
//------------------------------------------------
cols = ParseColumns(key._row["PK_COLS"] as string);
schema = key._row["PK_TABLE_SCHEMA"] as string;
table = key._row["PK_TABLE_NAME"] as string;
q = query;
q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN(";
for (int i = 0; i < cols.GetLength(0); i++)
{
if (i > 0)
{
q += ',';
}
q += cols[i].ToString();
}
q += ") ORDER BY attnum;";
DataTable metaData = new DataTable();
adapter = new NpgsqlDataAdapter(q, cn);
adapter.Fill(metaData);
for (int i = 0; i < cols.GetLength(0); i++)
{
key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, true);
}
//------------------------------------------------
// Foreign
//------------------------------------------------
cols = ParseColumns(key._row["FK_COLS"] as string);
schema = key._row["FK_TABLE_SCHEMA"] as string;
table = key._row["FK_TABLE_NAME"] as string;
q = query;
q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN(";
for (int i = 0; i < cols.GetLength(0); i++)
{
if (i > 0)
{
q += ',';
}
q += cols[i].ToString();
}
q += ") ORDER BY attnum;";
metaData = new DataTable();
adapter = new NpgsqlDataAdapter(q, cn);
adapter.Fill(metaData);
for (int i = 0; i < cols.GetLength(0); i++)
{
key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, false);
}
}
}
}
catch {}
cn.Close();
}
public static void Update(Product product, string nname, string nsource, string nproductLink, string nprice, string ngroup,
string nphotoLink)
{
var updateQuery = "";
updateQuery += Check(product.name, nname, "name");
updateQuery += Check(product.source, nsource, "source");
updateQuery += Check(product.product_link, nproductLink, "product_link");
updateQuery += Check(product.price, nprice, "price");
updateQuery += Check(product.group, ngroup, "group");
updateQuery += Check(product.photo_link, nphotoLink, "photo_link");
if (updateQuery.Length > 0)
{
using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString))
{
con.Open();
updateQuery = updateQuery.TrimEnd(',', ' ');
//
var sql = new NpgsqlCommand(
"UPDATE dbo.\"Products\" SET " + updateQuery + " where name='" + product.name + "' and source='" + product.source + "';", con);
sql.Parameters.AddWithValue("@name", nname);
sql.Parameters.AddWithValue("@source", nsource);
sql.Parameters.AddWithValue("@product_link", nproductLink);
sql.Parameters.AddWithValue("@price", nprice);
sql.Parameters.AddWithValue("@group", ngroup);
sql.Parameters.AddWithValue("@photo_link", nphotoLink);
var dataAdapter = new NpgsqlDataAdapter("select * FROM dbo.\"Products\"", con)
{
UpdateCommand = sql
};
var dataSet = new DataSet();
dataAdapter.AcceptChangesDuringUpdate = true;
dataAdapter.Fill(dataSet, "dbo.\"Products\"");
Debug.WriteLine(sql.CommandText);
var dataTable = dataSet.Tables["dbo.\"Products\""];
//dataTable.Rows.Find(row => row[1].ToString().Equals(product.name));
foreach (DataRow dataRow in dataTable.Rows)
{
if (!dataRow[1].ToString().Equals(product.name))
{
continue;
}
if (ShouldUpdate(product.name, nname))
{
dataRow[1] = nname;
}
if (ShouldUpdate(product.source, nsource))
{
dataRow[2] = nname;
}
if (ShouldUpdate(product.product_link, nproductLink))
{
dataRow[5] = nname;
}
if (ShouldUpdate(product.price, nprice))
{
dataRow[3] = nname;
}
if (ShouldUpdate(product.@group, ngroup))
{
dataRow[6] = nname;
}
if (ShouldUpdate(product.photo_link, nphotoLink))
{
dataRow[4] = nname;
}
break;
}
dataAdapter.Update(dataTable);
dataAdapter.Dispose();
}
}
}
private void btnBuscar_Click(object sender, EventArgs e)
{
//Reiniciar variables al inicio de hacer click en el boton buscar.
where = "";
nombrecompleto = "";
campus = "";
trimestre = "";
especialidad = "";
debeRecortar = false;
strSQL = "SELECT nombrecompleto AS \"Nombre Completo\", campus AS Campus, trimestre as Trimestre, especialidad AS Especialidad FROM administracion.medicos";
//Si algun campo de texto tiene texto adentro, agregar condiciones en consulta SQL.
if (txtnombre.Text.Length > 0 ||
cmbcampus.Text.Length > 0 ||
cbmtrimestre.Text.Length > 0 ||
cmbcarrera.Text.Length > 0
)
{
where = " where ";
debeRecortar = true;
}
//----------------------------------------------------------------------
//Determinar cual subcadena añadir al query y el formato de la misma.
//----------------------------------------------------------------------
if (txtnombre.Text.Length > 0)
{
nombrecompleto = "nombrecompleto LIKE '%" + txtnombre.Text + "%' and ";
}
if (cmbcampus.Text.Length > 0)
{
campus = "campus LIKE '%" + cmbcampus.Text + "%' and ";
}
if (cbmtrimestre.Text.Length > 0)
{
trimestre = "trimestre = " + cbmtrimestre.Text + " and ";
}
if (cmbcarrera.Text.Length > 0)
{
especialidad = "especialidad LIKE '%" + cmbcarrera.Text + "%' and ";
}
//Concatenacion de subcadenas para formar el query.
strSQL = "SELECT nombrecompleto AS \"Nombre Completo\", campus AS Campus, trimestre as Trimestre, especialidad AS Especialidad FROM administracion.medicos" + where + nombrecompleto + campus + trimestre + especialidad;
//Si hay algun texto en alguno de los campos de texto remover la ultima subcadena "and " para evitar errores de sql
if (debeRecortar)
{
strSQL = strSQL.Remove(strSQL.Length - 4);
}
//messagebox para debuggear, descomentar de ser necesario
//MessageBox.Show(strSQL);
//--------------------------------------------------------
//creaer el data adapater para mostrarlo en data grid
//--------------------------------------------------------
NpgsqlDataAdapter da = new NpgsqlDataAdapter(strSQL, conexion);
var ds = new DataSet();
da.Fill(ds);
datagridResultados.ReadOnly = true;
datagridResultados.DataSource = ds.Tables[0];
}
public static void CreateInsertObject(Quote obj)
{
var ds = new DataSet();
string commandString = "insert into quote(";
commandString += QuoteTable.Ask + ',';
commandString += QuoteTable.Averagedailyvolume + ',';
commandString += QuoteTable.Bid + ',';
commandString += QuoteTable.BookValue + ',';
commandString += QuoteTable.Change + ',';
commandString += QuoteTable.ChangeFromTwoHundredDayMovingAverage + ',';
commandString += QuoteTable.ChangeFromYearHigh + ',';
commandString += QuoteTable.ChangeFromYearLow + ',';
commandString += QuoteTable.ChangeInPercent + ',';
commandString += QuoteTable.ChangePercent + ',';
commandString += QuoteTable.DailyHigh + ',';
commandString += QuoteTable.DailyLow + ',';
commandString += QuoteTable.DividendPayDate + ',';
commandString += QuoteTable.DividendShare + ',';
commandString += QuoteTable.DividendYield + ',';
commandString += QuoteTable.EarningsShare + ',';
commandString += QuoteTable.Ebitda + ',';
commandString += QuoteTable.EpsEstimateCurrentYear + ',';
commandString += QuoteTable.EpsEstimateNextQuarter + ',';
commandString += QuoteTable.EpsEstimateNextYear + ',';
commandString += QuoteTable.ExDividendDate + ',';
commandString += QuoteTable.FiftyDayMovingAverage + ',';
commandString += QuoteTable.LastTradeDate + ',';
commandString += QuoteTable.LastTradePrice + ',';
commandString += QuoteTable.LastUpdate + ',';
commandString += QuoteTable.MarketCapitalization + ',';
commandString += QuoteTable.Name + ',';
commandString += QuoteTable.OneYearPriceTarget + ',';
commandString += QuoteTable.Open + ',';
commandString += QuoteTable.PeRatio + ',';
commandString += QuoteTable.PegRatio + ',';
commandString += QuoteTable.PercentChangeFromFiftyDayMovingAverage + ',';
commandString += QuoteTable.PercentChangeFromTwoHundredDayMovingAverage + ',';
commandString += QuoteTable.PercentChangeFromYearHigh + ',';
commandString += QuoteTable.PercentChangeFromYearLow + ',';
commandString += QuoteTable.PreviousClose + ',';
commandString += QuoteTable.PriceBook + ',';
commandString += QuoteTable.PriceEpsEstimateCurrentYear + ',';
commandString += QuoteTable.PriceEpsEstimateNextYear + ',';
commandString += QuoteTable.PriceSales + ',';
commandString += QuoteTable.ShortRatio + ',';
commandString += QuoteTable.StockExchange + ',';
commandString += QuoteTable.Symbol + ',';
commandString += QuoteTable.TwoHunderedDayMovingAverage + ',';
commandString += QuoteTable.Volume + ',';
commandString += QuoteTable.YearlyHigh + ',';
commandString += QuoteTable.YearlyLow;
commandString += ") values (";
commandString += ':' + QuoteTable.Ask + ',';
commandString += ':' + QuoteTable.Averagedailyvolume + ',';
commandString += ':' + QuoteTable.Bid + ',';
commandString += ':' + QuoteTable.BookValue + ',';
commandString += ':' + QuoteTable.Change + ',';
commandString += ':' + QuoteTable.ChangeFromTwoHundredDayMovingAverage + ',';
commandString += ':' + QuoteTable.ChangeFromYearHigh + ',';
commandString += ':' + QuoteTable.ChangeFromYearLow + ',';
commandString += ':' + QuoteTable.ChangeInPercent + ',';
commandString += ':' + QuoteTable.ChangePercent + ',';
commandString += ':' + QuoteTable.DailyHigh + ',';
commandString += ':' + QuoteTable.DailyLow + ',';
commandString += ':' + QuoteTable.DividendPayDate + ',';
commandString += ':' + QuoteTable.DividendShare + ',';
commandString += ':' + QuoteTable.DividendYield + ',';
commandString += ':' + QuoteTable.EarningsShare + ',';
commandString += ':' + QuoteTable.Ebitda + ',';
commandString += ':' + QuoteTable.EpsEstimateCurrentYear + ',';
commandString += ':' + QuoteTable.EpsEstimateNextQuarter + ',';
commandString += ':' + QuoteTable.EpsEstimateNextYear + ',';
commandString += ':' + QuoteTable.ExDividendDate + ',';
commandString += ':' + QuoteTable.FiftyDayMovingAverage + ',';
commandString += ':' + QuoteTable.LastTradeDate + ',';
commandString += ':' + QuoteTable.LastTradePrice + ',';
commandString += ':' + QuoteTable.LastUpdate + ',';
commandString += ':' + QuoteTable.MarketCapitalization + ',';
commandString += ':' + QuoteTable.Name + ',';
commandString += ':' + QuoteTable.OneYearPriceTarget + ',';
commandString += ':' + QuoteTable.Open + ',';
commandString += ':' + QuoteTable.PeRatio + ',';
commandString += ':' + QuoteTable.PegRatio + ',';
commandString += ':' + QuoteTable.PercentChangeFromFiftyDayMovingAverage + ',';
commandString += ':' + QuoteTable.PercentChangeFromTwoHundredDayMovingAverage + ',';
commandString += ':' + QuoteTable.PercentChangeFromYearHigh + ',';
commandString += ':' + QuoteTable.PercentChangeFromYearLow + ',';
commandString += ':' + QuoteTable.PreviousClose + ',';
commandString += ':' + QuoteTable.PriceBook + ',';
commandString += ':' + QuoteTable.PriceEpsEstimateCurrentYear + ',';
commandString += ':' + QuoteTable.PriceEpsEstimateNextYear + ',';
commandString += ':' + QuoteTable.PriceSales + ',';
commandString += ':' + QuoteTable.ShortRatio + ',';
commandString += ':' + QuoteTable.StockExchange + ',';
commandString += ':' + QuoteTable.Symbol + ',';
commandString += ':' + QuoteTable.TwoHunderedDayMovingAverage + ',';
commandString += ':' + QuoteTable.Volume + ',';
commandString += ':' + QuoteTable.YearlyHigh + ',';
commandString += ':' + QuoteTable.YearlyLow;
commandString += ")";
var da = new NpgsqlDataAdapter("select * from quote", Connection)
{
InsertCommand = new NpgsqlCommand(commandString, Connection)
};
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Ask, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Averagedailyvolume, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Bid, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.BookValue, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Change, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ChangeFromTwoHundredDayMovingAverage,
DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ChangeFromYearHigh, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ChangeFromYearLow, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ChangeInPercent, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ChangePercent, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.DailyHigh, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.DailyLow, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.DividendPayDate, DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.DividendShare, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.DividendYield, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.EarningsShare, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Ebitda, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.EpsEstimateCurrentYear, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.EpsEstimateNextQuarter, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.EpsEstimateNextYear, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ExDividendDate, DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.FiftyDayMovingAverage, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.LastTradeDate, DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.LastTradePrice, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.LastUpdate, DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.MarketCapitalization, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Name, DbType.String));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.OneYearPriceTarget, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Open, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PeRatio, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PegRatio, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PercentChangeFromFiftyDayMovingAverage,
DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PercentChangeFromTwoHundredDayMovingAverage,
DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PercentChangeFromYearHigh, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PercentChangeFromYearLow, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PreviousClose, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PriceBook, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PriceEpsEstimateCurrentYear, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PriceEpsEstimateNextYear, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.PriceSales, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.ShortRatio, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.StockExchange, DbType.String));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Symbol, DbType.String));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.TwoHunderedDayMovingAverage, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.Volume, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.YearlyHigh, DbType.Single));
da.InsertCommand.Parameters.Add(new NpgsqlParameter(QuoteTable.YearlyLow, DbType.Single));
foreach (NpgsqlParameter p in da.InsertCommand.Parameters)
{
p.Direction = ParameterDirection.Input;
}
da.InsertCommand.Parameters[0].SourceColumn = QuoteTable.Ask;
da.InsertCommand.Parameters[1].SourceColumn = QuoteTable.Averagedailyvolume;
da.InsertCommand.Parameters[2].SourceColumn = QuoteTable.Bid;
da.InsertCommand.Parameters[3].SourceColumn = QuoteTable.BookValue;
da.InsertCommand.Parameters[4].SourceColumn = QuoteTable.Change;
da.InsertCommand.Parameters[5].SourceColumn = QuoteTable.ChangeFromTwoHundredDayMovingAverage;
da.InsertCommand.Parameters[6].SourceColumn = QuoteTable.ChangeFromYearHigh;
da.InsertCommand.Parameters[7].SourceColumn = QuoteTable.ChangeFromYearLow;
da.InsertCommand.Parameters[8].SourceColumn = QuoteTable.ChangeInPercent;
da.InsertCommand.Parameters[9].SourceColumn = QuoteTable.ChangePercent;
da.InsertCommand.Parameters[10].SourceColumn = QuoteTable.DailyHigh;
da.InsertCommand.Parameters[11].SourceColumn = QuoteTable.DailyLow;
da.InsertCommand.Parameters[12].SourceColumn = QuoteTable.DividendPayDate;
da.InsertCommand.Parameters[13].SourceColumn = QuoteTable.DividendShare;
da.InsertCommand.Parameters[14].SourceColumn = QuoteTable.DividendYield;
da.InsertCommand.Parameters[15].SourceColumn = QuoteTable.EarningsShare;
da.InsertCommand.Parameters[16].SourceColumn = QuoteTable.Ebitda;
da.InsertCommand.Parameters[17].SourceColumn = QuoteTable.EpsEstimateCurrentYear;
da.InsertCommand.Parameters[18].SourceColumn = QuoteTable.EpsEstimateNextQuarter;
da.InsertCommand.Parameters[19].SourceColumn = QuoteTable.EpsEstimateNextYear;
da.InsertCommand.Parameters[20].SourceColumn = QuoteTable.ExDividendDate;
da.InsertCommand.Parameters[21].SourceColumn = QuoteTable.FiftyDayMovingAverage;
da.InsertCommand.Parameters[22].SourceColumn = QuoteTable.LastTradeDate;
da.InsertCommand.Parameters[23].SourceColumn = QuoteTable.LastTradePrice;
da.InsertCommand.Parameters[24].SourceColumn = QuoteTable.LastUpdate;
da.InsertCommand.Parameters[25].SourceColumn = QuoteTable.MarketCapitalization;
da.InsertCommand.Parameters[26].SourceColumn = QuoteTable.Name;
da.InsertCommand.Parameters[27].SourceColumn = QuoteTable.OneYearPriceTarget;
da.InsertCommand.Parameters[28].SourceColumn = QuoteTable.Open;
da.InsertCommand.Parameters[29].SourceColumn = QuoteTable.PeRatio;
da.InsertCommand.Parameters[30].SourceColumn = QuoteTable.PegRatio;
da.InsertCommand.Parameters[31].SourceColumn = QuoteTable.PercentChangeFromFiftyDayMovingAverage;
da.InsertCommand.Parameters[32].SourceColumn = QuoteTable.PercentChangeFromTwoHundredDayMovingAverage;
da.InsertCommand.Parameters[33].SourceColumn = QuoteTable.PercentChangeFromYearHigh;
da.InsertCommand.Parameters[34].SourceColumn = QuoteTable.PercentChangeFromYearLow;
da.InsertCommand.Parameters[35].SourceColumn = QuoteTable.PreviousClose;
da.InsertCommand.Parameters[36].SourceColumn = QuoteTable.PriceBook;
da.InsertCommand.Parameters[37].SourceColumn = QuoteTable.PriceEpsEstimateCurrentYear;
da.InsertCommand.Parameters[38].SourceColumn = QuoteTable.PriceEpsEstimateNextYear;
da.InsertCommand.Parameters[39].SourceColumn = QuoteTable.PriceSales;
da.InsertCommand.Parameters[40].SourceColumn = QuoteTable.ShortRatio;
da.InsertCommand.Parameters[41].SourceColumn = QuoteTable.StockExchange;
da.InsertCommand.Parameters[42].SourceColumn = QuoteTable.Symbol;
da.InsertCommand.Parameters[43].SourceColumn = QuoteTable.TwoHunderedDayMovingAverage;
da.InsertCommand.Parameters[44].SourceColumn = QuoteTable.Volume;
da.InsertCommand.Parameters[45].SourceColumn = QuoteTable.YearlyHigh;
da.InsertCommand.Parameters[46].SourceColumn = QuoteTable.YearlyLow;
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
MapFields(dr, QuoteTable.Ask, obj.Ask);
MapFields(dr, QuoteTable.Averagedailyvolume, obj.AverageDailyVolume);
MapFields(dr, QuoteTable.Bid, obj.Bid);
MapFields(dr, QuoteTable.BookValue, obj.BookValue);
MapFields(dr, QuoteTable.Change, obj.Change);
MapFields(dr, QuoteTable.ChangeFromTwoHundredDayMovingAverage, obj.ChangeFromTwoHundredDayMovingAverage);
MapFields(dr, QuoteTable.ChangeFromYearHigh, obj.ChangeFromYearHigh);
MapFields(dr, QuoteTable.ChangeFromYearLow, obj.ChangeFromYearLow);
MapFields(dr, QuoteTable.ChangeInPercent, obj.ChangeInPercent);
MapFields(dr, QuoteTable.ChangePercent, obj.ChangePercent);
MapFields(dr, QuoteTable.DailyHigh, obj.DailyHigh);
MapFields(dr, QuoteTable.DailyLow, obj.DailyLow);
MapFields(dr, QuoteTable.DividendPayDate, obj.DividendPayDate);
MapFields(dr, QuoteTable.DividendShare, obj.DividendShare);
MapFields(dr, QuoteTable.DividendYield, obj.DividendYield);
MapFields(dr, QuoteTable.EarningsShare, obj.EarningsShare);
MapFields(dr, QuoteTable.Ebitda, obj.Ebitda);
MapFields(dr, QuoteTable.EpsEstimateCurrentYear, obj.EpsEstimateCurrentYear);
MapFields(dr, QuoteTable.EpsEstimateNextQuarter, obj.EpsEstimateNextQuarter);
MapFields(dr, QuoteTable.EpsEstimateNextYear, obj.EpsEstimateNextYear);
MapFields(dr, QuoteTable.ExDividendDate, obj.ExDividendDate);
MapFields(dr, QuoteTable.FiftyDayMovingAverage, obj.FiftyDayMovingAverage);
MapFields(dr, QuoteTable.LastTradeDate, obj.LastTradeDate);
MapFields(dr, QuoteTable.LastTradePrice, obj.LastTradePrice);
MapFields(dr, QuoteTable.LastUpdate, obj.LastUpdate);
MapFields(dr, QuoteTable.MarketCapitalization, obj.MarketCapitalization);
MapFields(dr, QuoteTable.Name, obj.Name);
MapFields(dr, QuoteTable.OneYearPriceTarget, obj.OneYearPriceTarget);
MapFields(dr, QuoteTable.Open, obj.Open);
MapFields(dr, QuoteTable.PeRatio, obj.PeRatio);
MapFields(dr, QuoteTable.PegRatio, obj.PegRatio);
MapFields(dr, QuoteTable.PercentChangeFromFiftyDayMovingAverage, obj.PercentChangeFromFiftyDayMovingAverage);
MapFields(dr, QuoteTable.PercentChangeFromTwoHundredDayMovingAverage, obj.PercentChangeFromTwoHundredDayMovingAverage);
MapFields(dr, QuoteTable.PercentChangeFromYearHigh, obj.PercentChangeFromYearHigh);
MapFields(dr, QuoteTable.PercentChangeFromYearLow, obj.PercentChangeFromYearLow);
MapFields(dr, QuoteTable.PreviousClose, obj.PreviousClose);
MapFields(dr, QuoteTable.PriceBook, obj.PriceBook);
MapFields(dr, QuoteTable.PriceEpsEstimateCurrentYear, obj.PriceEpsEstimateCurrentYear);
MapFields(dr, QuoteTable.PriceEpsEstimateNextYear, obj.PriceEpsEstimateNextYear);
MapFields(dr, QuoteTable.PriceSales, obj.PriceSales);
MapFields(dr, QuoteTable.ShortRatio, obj.ShortRatio);
MapFields(dr, QuoteTable.StockExchange, obj.StockExchange);
MapFields(dr, QuoteTable.Symbol, obj.Symbol);
MapFields(dr, QuoteTable.TwoHunderedDayMovingAverage, obj.TwoHunderedDayMovingAverage);
MapFields(dr, QuoteTable.Volume, obj.Volume);
MapFields(dr, QuoteTable.YearlyHigh, obj.YearlyHigh);
MapFields(dr, QuoteTable.YearlyLow, obj.YearlyLow);
dt.Rows.Add(dr);
DataSet ds2 = ds.GetChanges();
if (ds2 != null)
{
da.Update(ds2);
ds.Merge(ds2);
}
ds.AcceptChanges();
}
static void Main(string[] args)
{
// Dosya yazım alanı
string dosya_yolu = @"C:\Users\Masaüstü\kayitlar.txt";// sizde programın bulunduğu yer
FileStream fs = new FileStream(dosya_yolu, FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
//
//////////////////FONKSİYON YAZIM ALANI/////////
Logger logger = LogManager.GetCurrentClassLogger();//Loglama servisi
void Database()
{
// data base kontrol islemi
string baglantiSatiri = "Server=localhost;User ID=******;password=******;Database=teknokent";
NpgsqlConnection baglanti = new NpgsqlConnection(baglantiSatiri);
NpgsqlCommandBuilder cm = new NpgsqlCommandBuilder();
try
{
baglanti.Open();
Console.Write("Baglantı kuruldu\n");
Console.ReadKey();
}
catch (Exception)
{
Console.Write("Baglantı yok\n");
Console.ReadKey();
}
}//database son
void Firma_bilgisi()
{
NpgsqlCommand commend = new NpgsqlCommand();
Yonetim yonetim1 = new Yonetim();
Console.WriteLine("Çalısan sayısı giriniz:");
yonetim1.calisansayisi = Convert.ToInt32(Console.ReadLine());
sw.WriteLine("Çalışan sayisi: " + yonetim1.calisansayisi);
Console.WriteLine("Firma adi giriniz:");
yonetim1.firma_adi = Console.ReadLine();
sw.Write("Firma adı: " + yonetim1.firma_adi);
Console.WriteLine("Kuruluş tarihi giriniz(Sadece sayı):");
yonetim1.kurulustarihi = Convert.ToInt32(Console.ReadLine());
sw.WriteLine("\nKuruluş tarihi: " + yonetim1.kurulustarihi);
Console.WriteLine("Firmanın çalışacağı sektörü giriniz:");
yonetim1.sektor = Console.ReadLine();
sw.WriteLine("\nSektör: " + yonetim1.sektor);
Random rnd = new Random();
if (yonetim1.sektor == "yazilim")
{
yonetim1.firmaid = rnd.Next(1, 10000);
Console.WriteLine("Firma için hazırlanan ID:" + yonetim1.firmaid);
Console.WriteLine("Konum:C Blok");
sw.WriteLine("ID:" + yonetim1.firmaid + " C Blok");
Console.Read();
}
else if (yonetim1.sektor == "biomedikal")
{
yonetim1.firmaid = rnd.Next(10001, 20000);
Console.WriteLine("Firma için hazırlanan ID:" + yonetim1.firmaid);
Console.WriteLine("Konum:E Blok");
sw.WriteLine("ID:" + yonetim1.firmaid + "E Blok");
Console.Read();
}
else if (yonetim1.sektor == "enerji")
{
yonetim1.firmaid = rnd.Next(20001, 30000);
Console.WriteLine("Firma için hazırlanan ID:" + yonetim1.firmaid);
Console.WriteLine("Konum:A Blok");
sw.WriteLine("ID:" + yonetim1.firmaid + " A Blok");
Console.Read();
}
else if (yonetim1.sektor == "savunma")
{
yonetim1.firmaid = rnd.Next(30001, 40000);
Console.WriteLine("Firma için hazırlanan ID:" + yonetim1.firmaid);
Console.WriteLine("Konum:F Blok");
sw.WriteLine("ID:" + yonetim1.firmaid + " F Blok");
Console.Read();
}
else
{
Console.WriteLine("HATALI İŞLEM");
Console.Read();
logger.Error(kullanici_adi + " Hatali islem yaptı");//log
}
sw.Flush();
sw.Close();
fs.Close();
NpgsqlConnection baglanti = new NpgsqlConnection(baglantiSatiri);
DataSet dataset = new DataSet();
string sql = "INSERT INTO hizmetler(isim,calisansayisi,kurulustarihi,sektor,firmaid) VALUES(@p1,@p2,@p3,@p4,@p5)";
NpgsqlDataAdapter add = new NpgsqlDataAdapter(sql, baglantiSatiri);
add.Fill(dataset);
baglanti.Close();
}//firmabilgisi son
void Hizmetler()
{
Yonetim.Hizmet_giderleri hg = new Yonetim.Hizmet_giderleri();
Console.WriteLine("Personel ucreti giriniz");
hg.personel_ucret = Convert.ToInt32(Console.ReadLine());
sw.WriteLine(hg.personel_ucret);
Console.WriteLine("Çalışan pozisyonunu giriniz:");
hg.personel_pozisyon = Console.ReadLine();
sw.WriteLine(hg.personel_pozisyon);
Console.WriteLine("Personelin hangi blokta çalışacagini giriniz");
hg.blok = Console.ReadLine();
sw.WriteLine(hg.blok);
Console.WriteLine("Personel izin günleri:");
hg.izin = Console.ReadLine();
sw.WriteLine(hg.izin);
sw.Flush();
sw.Close();
fs.Close();
}//hizmetler son
int Giris()
{
Console.WriteLine("Kullanıcı adı:");
kullanici_adi = Console.ReadLine();
Console.WriteLine("Parola:");
kullanici_parola = Convert.ToInt32(Console.ReadLine());
if (kullanici_adi == kullaniciadi && kullanici_parola == parola)
{
Console.WriteLine("Giriş başarılı");
logger.Info("Giris yapan kullanici: " + kullanici_adi);//log
return(1);
}
else//kullanıcı giris ekranı else
{
Console.WriteLine("Kullanıcı adı veya parola yanlış");
logger.Info("Kullanıcı adı:" + kullanici_adi + "parola" + kullanici_parola + " ile kullanıcı yanlıs login oldu");//log
girissay -= 1;
if (girissay == 0)
{
Console.WriteLine("SİSTEME GİRİŞ HAKKINIZ DOLDU..");
logger.Info(kullanici_adi + " - " + kullanici_parola + " 3 kez yanlıs giris yaptı");
return(0);
}
return(2);
}
}
void otopark()
{
Yonetim.Otopark otopark1 = new Yonetim.Otopark();
Console.WriteLine("Araç Sahibinin İsmi:");
otopark1.plakaismi = Console.ReadLine();
Console.WriteLine("Araç Plakası:");
otopark1.plaka = Console.ReadLine();
Console.WriteLine("Sticker Durumu\n[1] VAR [2] YOK");
otopark1.odeme = Convert.ToInt16(Console.ReadLine());
if (otopark1.odeme == 1)
{
Console.WriteLine("Geçiş izni verildi...\n");
Console.Read();
}
else
{
Console.WriteLine("Geçiş rededildi...\n");
Console.Read();
}
}//otopark son
////////////////////////////////////////////////
///////////////////////////////////////////////
Database();
while (true)
{
int donusDegeri = Giris();
if (donusDegeri == 0)
{
Console.Read();
break;
}
if (donusDegeri == 2)
{
continue;
}
if (donusDegeri == 1) //giris calisti
{
Console.Write("\n\nİslem seçiniz\n[1]Yönetim Portalı [2]Hizmetler [3]Otopark [4]Çıkış");
secim = Convert.ToInt32(Console.ReadLine());
if (secim == 1)
{
Firma_bilgisi();
continue;
}
else if (secim == 2)
{
Hizmetler();
continue;
}
else if (secim == 4)
{
Console.Write("\nSistemden çıkış yapılacak...");
logger.Trace("Cikis yapan kullanici: " + kullanici_adi);//log
Console.Read();
break;
}
else if (secim == 3)
{
otopark();
}
else
{
Console.Write("\nHATALI İŞLEM YAPTINIZ..\n");
logger.Error(kullanici_adi + " Hatalı islem yaptı");//log
continue;
}
}
} //while son
} //main son