public async Task<IEnumerable<Subdivision>> Load100()
{
var result = new List<Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result.Add(subdivision);
}
}
return result;
}
public async Task<Subdivision> LoadById(int id)
{
Subdivision result = null;
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions where id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result = subdivision;
}
}
return result;
}
private void CreateMatrixMdb()
{
using (
var connPoints =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connPoints.Open();
using (
var pointsReader =
new Npgsql.NpgsqlCommand(
"WITH t as (SELECT zellnr, st_pointonsurface(wkb_geometry) as g FROM \"DFN2950\".\"fuerimport\") " +
"SELECT zellnr, st_X(g), st_Y(g) FROM t ORDER BY zellnr;",
connPoints).ExecuteReader())
{
using (
var connMatrix =
new Npgsql.NpgsqlConnection(
"server=127.0.0.1;port=5432;database=ivv-projekte;uid=postgres;password=1.Kennwort"))
{
connMatrix.Open();
using (
var matrixReader =
new Npgsql.NpgsqlCommand(
"SELECT origin, destin, val FROM \"DFN2950\".\"iv-kfz\" " +
"WHERE val > 10 " +
"ORDER BY origin, destin;",
connMatrix).ExecuteReader())
_matrix = new ODMatrix(pointsReader, matrixReader);
}
}
}
}
public static void Main(string[] args)
{
Npgsql.NpgsqlConnection v_con = null;
Npgsql.NpgsqlDataAdapter v_adapter = null;
System.Data.DataTable v_table;
Console.WriteLine("Exemplo PostgreSQL usando DataAdapter");
Console.WriteLine();
try
{
// 1) instanciando Connection
v_con = new Npgsql.NpgsqlConnection(
"Server=127.0.0.1;Port=5432;Database=lugares;User ID=postgres;Password=knightnote"
);
// 2) abrindo Connection
v_con.Open();
// 3) instanciando DataAdapter
v_adapter = new Npgsql.NpgsqlDataAdapter("select * from estados", v_con);
// 4) instanciando DataTable
v_table = new System.Data.DataTable("RESULTADO");
// 5) alimentando DataTable
v_adapter.Fill(v_table);
// 6) usando DataTable (imprimindo na tela)
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", c.ColumnName);
Console.WriteLine();
foreach (System.Data.DataRow r in v_table.Rows)
{
foreach (System.Data.DataColumn c in v_table.Columns)
Console.Write("{0} ", r[c].ToString());
Console.WriteLine();
}
}
catch (Npgsql.NpgsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
// 7) fechando e liberando Connection
if (v_con != null)
{
v_con.Close();
v_con = null;
}
}
Console.ReadKey();
}
public PostgreSQL()
{
Npgsql.NpgsqlConnectionStringBuilder conn = new Npgsql.NpgsqlConnectionStringBuilder();
conn.Database = Configuration.Postgres.DBNM;
conn.Host = Configuration.Postgres.Host;
conn.UserName = Configuration.Postgres.User;
conn.Port = Configuration.Postgres.Port;
conn.Password = Configuration.Postgres.Pass;
this.connection = new Npgsql.NpgsqlConnection(conn.ConnectionString);
//this.connection.Open();
}
public void RunTableList()
{
//couldn't get package to restore.
using (var connection = new Npgsql.NpgsqlConnection(PostgreSql))
{
var dr = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = dr.ReadAll();
var tableList = dr.TableList();
var tables = dr.AllTables();
var views = dr.AllViews();
Assert.NotEmpty(tableList);
}
}
public IActionResult Index()
{
string connectionString = configuration["Data:LinuxPGConnection:ConnectionString"];
try
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connectionString))
{
Trace.WriteLine("NpgsqlConnection created");
conn.Open();
Trace.WriteLine("NpgsqlConnection opened");
using (Npgsql.NpgsqlCommand cmd = conn.CreateCommand())
{
Trace.WriteLine("NpgsqlCommand created");
cmd.CommandText = @"select ""Email"" from ""AspNetUsers"" Limit 1";
var email = cmd.ExecuteScalar();
Trace.WriteLine(email, "Email from npsql");
ViewData.Add("SimpleRequestEmail", email);
}
}
}
catch (Exception ex)
{
ViewData.Add("SimpleRequestException", ex.ToString());
}
Regex re = new Regex(@"Password\s*=\s*([^;]+)\s*;");
ViewData.Add("ConnectionString", re.Replace(connectionString, "Password=*******;") );
bool isConnected = false;
string additionalInfo = "";
try
{
int cnt = context.Users.Count();
isConnected = true;
additionalInfo = string.Format("Users count: {0}", cnt);
}
catch(Exception ex)
{
additionalInfo = "exception: " + ex.ToString();
}
ViewData.Add("ConnectionStatus", isConnected ? "Connected" : "Error");
ViewData.Add("ConnectionInfo", additionalInfo);
return View();
}
private void button3_Click(object sender, EventArgs e)
{
string _conStr = ConfigurationManager.AppSettings["DB_SK"];
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr);
connection.Open();
Npgsql.NpgsqlTransaction et = connection.BeginTransaction();
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_personnr", "196402230319");
cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561");
cmd.Prepare();
cmd.ExecuteNonQuery();
et.Save("apa");
et.Commit();
connection.Close();
}
public async Task<List<LcboStore>> GetLcboStores()
{
var result = new List<LcboStore>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, name, ST_AsGeoJSON(location) as location, beer_volume, wine_volume, spirits_volume from stores limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
public async Task<List<LcboStore>> StoresInArea(string geoJson)
{
var result = new List<LcboStore>();
var query = $@"select s.id, s.name, s.beer_volume, s.wine_volume, s.spirits_volume
from stores s
where ST_Intersects(ST_GeomFromGeoJSON('{geoJson}'), s.location)";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
protected override System.Data.IDbCommand getCommand(string storedProcedure)
{
Npgsql.NpgsqlCommand mCommand;
if(CommandsCollection.Contains(storedProcedure))
{
mCommand = (Npgsql.NpgsqlCommand) CommandsCollection[storedProcedure];
}
else
{
Npgsql.NpgsqlConnection Conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
Conn.Open();
mCommand = new Npgsql.NpgsqlCommand(storedProcedure,Conn);
mCommand.CommandType = System.Data.CommandType.StoredProcedure;
Npgsql.NpgsqlCommandBuilder.DeriveParameters(mCommand);
Conn.Close();
Conn.Dispose();
CommandsCollection.Add(storedProcedure, mCommand);
}
mCommand.Connection = (Npgsql.NpgsqlConnection) this.Connection;
return (System.Data.IDbCommand) mCommand;
}
private void ConnectButtonClick(object sender, RoutedEventArgs e)
{
if (!_isConnected)
{
var pg_con = new Npgsql.NpgsqlConnectionStringBuilder();
pg_con.Host = "127.0.0.1";
pg_con.UserName = "******";
pg_con.Password = "******";
pg_con.Database = "bbrother";
_pgConnection = new Npgsql.NpgsqlConnection(pg_con.ConnectionString);
_pgConnection.Open();
_isConnected = true;
ConnectButton.Content = "Diconnect";
}
else
{
_isConnected = false;
_pgConnection.Close();
ConnectButton.Content = "Connect to DB...";
}
}
public CalendarEvent GetEventByAppointmentID(string id)
{
CalendarEvent currentEvent = new CalendarEvent();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(EventByAppointmentIdQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = id;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
currentEvent = populateEventsFromDB(dr);
}
}
}
}
return(currentEvent);
}
/// <summary>
/// Replaces a single VenueDocument with a new full set of values
/// </summary>
/// <param name="replacedVenueDocument">The new data for the VenueDocument you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceVenueDocument(VenueDocument replacedVenueDocument)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""VenueDocuments""
SET
venueId = @VenueId,
documentId = @DocumentId
WHERE venueId = @VenueDocumentId",
replacedVenueDocument
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
public List <DistanciaViagemDTO> GetDistanciaViagem(int radarInicio, int radarFinal)
{
List <DistanciaViagemDTO> listaRetorno = new List <DistanciaViagemDTO>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText =
"select br0.codigo as RadarInicio, " +
"br1.codigo as RadarFinal, " +
"ST_Distance(ST_Transform(concat('SRID=4326;POINT(', cast(br0.lat as varchar(20)), ' ', cast(br0.lon as varchar(20)), ')')::geometry, 3857), " +
"ST_Transform(concat('SRID=4326;POINT(', cast(br1.lat as varchar(20)), ' ', cast(br1.lon as varchar(20)), ')')::geometry, 3857)) * cosd(42.3521) as distancia " +
"from base_radares_lat_lon br0 inner join base_radares_lat_lon br1 " +
"on br1.codigo = " + radarFinal + " where br0.codigo = " + radarInicio + "; ";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
DistanciaViagemDTO ett = new DistanciaViagemDTO();
ett.codigoRadarInicio = Convert.ToInt32(dr["RadarInicio"]);
ett.codigoRadarFinal = Convert.ToInt32(dr["RadarFinal"]);
ett.distancia = Convert.ToDecimal(dr["distancia"]);
listaRetorno.Add(ett);
}
}
return(listaRetorno);
}
public async Task AddMedia(Guid mediaId, string fileName, string userId, string campaignId, DateTime createdOn, Uri storageUri)
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
Guid id = Guid.NewGuid();
string insertQuery = "INSERT INTO campaign.\"media\" (\"id\" ,filename ,createdby ,id_ref_campaign_fk ,isdeleted ,createdon ,blob_url) VALUES ( @mediaId, @fileName, @userId, (SELECT id from campaign.\"campaign\" WHERE id=@guidCampaignId) , B'0', @createdOn, @absoluteUri)";
var absoluteUri = storageUri.AbsoluteUri;
var guidCampaignId = new Guid(campaignId);
var result = await conn.ExecuteAsync(insertQuery,
new
{
mediaId,
fileName,
userId,
guidCampaignId,
createdOn,
absoluteUri
}
);
}
}
public string GetCalendarColorForUser(int userid)
{
string color = "";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetCalendarColorQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Prepare();
command.Parameters[0].Value = userid;
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
color = dr[0].ToString();
}
}
}
}
return(color);
}
public void create_server_and_table_test()
{
using (var server = new MysticMind.PostgresEmbed.PgServer(
"9.5.5.1",
PG_USER,
addLocalUserAccessPermission: ADD_LOCAL_USER_ACCESS_PERMISSION,
clearInstanceDirOnStop: true))
{
server.Start();
// Note: set pooling to false to prevent connecting issues
// https://github.com/npgsql/npgsql/issues/939
string connStr = string.Format(CONN_STR, server.PgPort, PG_USER);
var conn = new Npgsql.NpgsqlConnection(connStr);
var cmd =
new Npgsql.NpgsqlCommand(
"CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)",
conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public static System.Data.Common.DbConnection OpenConnection(IDictionary <string, object> settings, log4net.ILog logger, bool openConnection = true)
{
string connectionString = BuildConnectionString(settings, logger);
System.Data.Common.DbConnection conn = null;
var dataProvider = (Model.Settings.SQLProvider)Enum.Parse(typeof(Model.Settings.SQLProvider), settings.GetAsString("DataProvider", ""), true);
switch (dataProvider)
{
case SQLProvider.ODBC:
conn = new System.Data.Odbc.OdbcConnection(connectionString);
break;
case SQLProvider.SQLServer:
conn = new System.Data.SqlClient.SqlConnection(connectionString);
break;
case SQLProvider.PostgreSQL:
conn = new Npgsql.NpgsqlConnection(connectionString);
break;
case SQLProvider.Oracle:
conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
break;
default:
throw new NotSupportedException("Only ODBC, SqlServer, PostgreSQL, and MySQL currently supported.");
}
if (openConnection)
{
conn.Open();
}
return(conn);
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
SharpMap.Geometries.Geometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
public void Commit(MetadataSchema schema, SystemInfo systemInfo)
{
var con = new Npgsql.NpgsqlConnection(_connectionString);
using (DbConnection db = con)
{
db.Open();
var dbName = schema.DatabaseSchema.Title;
var com = db.CreateCommand();
com.CommandText = $"CREATE DATABASE {dbName}";
try
{
var result = com.ExecuteNonQuery();
var dbText = DatabaseSqlString(schema.DatabaseSchema);
com = db.CreateCommand();
com.CommandText = dbText;
var reader = com.ExecuteReader();
}
catch (Exception e)
{
// 42P04 - database already exist
if (!e.Data["SqlState"].Equals("42P04"))
{
throw e;
}
}
WriteSystemInfo(systemInfo);
//var tables = string.Join(",", schema.DatabaseSchema.TableList.Select(i => i.Title));
db.Close();
}
}
public IEnumerable <PracticeHeadCountDto> GetPracticeWiseHeadCountPostgres()
{
List <PracticeHeadCountDto> records = new List <PracticeHeadCountDto>();
Npgsql.NpgsqlConnection con = null;
try
{
con = new Npgsql.NpgsqlConnection(PostgresSqlQueries.CONNECTION_STRING);
con.Open();
string qry = PostgresSqlQueries.GET_PRACTICE_WISE_HEAD_COUNT;
qry = qry.Replace("__CURRENT_DATE__", $"{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}");
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(qry, con);
Npgsql.NpgsqlDataReader res = cmd.ExecuteReader();
if (res.HasRows)
{
while (res.Read())
{
records.Add(new PracticeHeadCountDto
{
HeadCount = res.IsDBNull(2) == false ? (int)res.GetInt64(2) : 0,
Practice = res.IsDBNull(1) == false ? res.GetString(1) : "",
PracticeID = res.IsDBNull(0) == false ? res.GetInt32(0) : -1,
});
}
}
}
catch (Exception) { }
finally
{
con.Close();
con.Dispose();
}
return(records);
}
/// <summary>
/// Fetches multiple Documents
/// </summary>
/// <param name="companyId">The company id to query against</param>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <Document> > > > FetchCountedCompanyDocuments(int companyId, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
using var obj = await con.QueryMultipleAsync("SELECT COUNT(*) FROM \"Document\" WHERE companyId = @CompanyId; SELECT * FROM \"Document\" WHERE companyId = @CompanyId LIMIT @Limit OFFSET @Offset", new { CompanyId = companyId, Limit = count, Offset = page * count }).ConfigureAwait(false);
var totalCount = obj.Read <int>().Single();
var data = obj.Read <Document>().ToList();
var paginatedData = new PaginatedResult <Document>
{
Data = data ?? new List <Document>(),
Count = totalCount
};
return(Result.Ok(Maybe <PaginatedResult <Document> > .From(paginatedData)));
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail <Maybe <PaginatedResult <Document> > >(ex.ToString()));
}
}
/// <summary>
/// Inserts a new UserSession
/// </summary>
/// <param name="newUserSession">The UserSession to be inserted</param>
/// <returns>Returns a result containing the created resource id</returns>
public async Task <Result <Maybe <int> > > CreateUserSession(UserSession newUserSession)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
var data = (await con.QueryAsync <int>(
@"INSERT INTO ""UserSession""(
userId,
authToken,
type,
authTokenExpiry
) VALUES (
@UserId,
@AuthToken,
@TokenType,
@AuthTokenExpiry
) RETURNING sessionId",
newUserSession
).ConfigureAwait(false)).FirstOrDefault();
if (data < 1)
{
return(Result.Ok(Maybe <int> .None));
}
return(Result.Ok(Maybe <int> .From(data)));
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail <Maybe <int> >(ex.ToString()));
}
}
/// <summary>
/// creating queue
/// </summary>
/// <returns></returns>
private bool CreateQueue()
{
try
{
int registerID;
using (var cnt = new Npgsql.NpgsqlConnection(_pgOptions.ConnectionString))
{
cnt.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = cnt;
cmd.Parameters.AddWithValue("@queueName", _queueName);
cmd.CommandText = "select * from pgq.create_queue(@queueName);";
registerID = (int)cmd.ExecuteScalar();
}
}
return(REGISTER_SUCCESS == registerID);
}
catch (Exception e)
{
_logger.LogError($"[PgMQProvider.CreateQueue.Exception]: {e.ToString()}");
return(false);
}
}
private int GetTotalAppointments(DateTime startdate, DateTime enddate, int userid)
{
int appointmentscount;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(GetTotalAppointmentsQuery, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("userid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("startdate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("enddate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Prepare();
command.Parameters[0].Value = userid;
command.Parameters[1].Value = startdate;
command.Parameters[2].Value = enddate;
int dr;
dr = Convert.ToInt32(command.ExecuteScalar());
appointmentscount = dr;
}
}
return(appointmentscount);
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
List <string> files = null;
FileTransferDetails parsedErrorLocation = null;
try
{
var inputSearchPath = options.Get("inputSearchPath", "");
if (String.IsNullOrEmpty(inputSearchPath))
{
throw new ArgumentNullException("inputSearchPath");
}
var backupLocation = options.Get("backupLocation", "");
if (String.IsNullOrEmpty(backupLocation))
{
throw new ArgumentNullException("backupLocation");
}
var loadScript = options.Get("sqlScriptPath", "");
if (String.IsNullOrEmpty(loadScript))
{
throw new ArgumentNullException("sqlScriptPath");
}
var errorLocation = options.Get("errorLocation", "");
if (String.IsNullOrEmpty(errorLocation))
{
throw new ArgumentNullException("errorLocation");
}
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if (String.IsNullOrEmpty(customCSharpScriptPath))
{
throw new ArgumentNullException("customCSharpScriptPath");
}
// prepare paths
var parsedInput = FileTransferDetails.ParseSearchPath(inputSearchPath);
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedBackupLocation = FileTransferDetails.ParseSearchPath(backupLocation);
parsedErrorLocation = FileTransferDetails.ParseSearchPath(errorLocation);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedInput.BucketName, Amazon.RegionEndpoint.USEast1, true);
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
// generate code
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
// 1. check if there is any new file
files = GetFilesFromS3(s3, parsedInput).ToList();
if (files.Any())
{
logger.Info("Files found: " + files.Count);
}
else
{
logger.Debug("No file found");
return(false);
}
var connectionString = RedshiftHelper.GetConnectionString(context);
foreach (var f in files)
{
var sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
if (String.IsNullOrEmpty(sqlScript))
{
throw new Exception("invalid sql script");
}
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
var fullFilename = System.IO.Path.Combine("s3://", parsedInput.BucketName, f.Trim()).Replace('\\', '/');
options.Set("InputFilename", fullFilename);
evaluator.Model.Initialize(conn, s3, context);
evaluator.Model.BeforeExecution();
sqlScript = evaluator.Model.PrepareSqlCOPYCommand(sqlScript);
// Create a PostgeSQL connection string.
ExecuteRedshiftLoad(connectionString, logger, sqlScript, new List <string> ()
{
f
}, parsedInput);
logger.Debug("Moving files to backup folder");
evaluator.Model.AfterExecution();
// move files
var destName = System.IO.Path.Combine(parsedBackupLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
logger.Success("Done");
}
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
try
{
if (files != null && s3 != null)
{
// move files
foreach (var f in files)
{
var destName = System.IO.Path.Combine(parsedErrorLocation.FilePath, System.IO.Path.GetFileName(f));
s3.MoveFile(f, destName, false);
}
}
}
catch { }
return(false);
}
return(true);
}
public void SetupDBProvider(string server, string port, string user, string password, string database)
{
SetConnectionString(server, port, user, password, database);
connection = new Npgsql.NpgsqlConnection(connstring);
comm = new Npgsql.NpgsqlCommand("", connection);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent() {
System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(ConnectionStringEditorForm));
this.tc_main = new System.Windows.Forms.TabControl();
this.tp_connection = new System.Windows.Forms.TabPage();
this.btn_refresh = new System.Windows.Forms.Button();
this.tb_server = new System.Windows.Forms.TextBox();
this.btn_check_connection = new System.Windows.Forms.Button();
this.gb_add_parms = new System.Windows.Forms.GroupBox();
this.tb_port = new System.Windows.Forms.TextBox();
this.lab_port = new System.Windows.Forms.Label();
this.tb_timeout = new System.Windows.Forms.TextBox();
this.lab_timeout = new System.Windows.Forms.Label();
this.cb_select_db = new System.Windows.Forms.ComboBox();
this.lab_select_db = new System.Windows.Forms.Label();
this.tb_password = new System.Windows.Forms.TextBox();
this.lab_password = new System.Windows.Forms.Label();
this.tb_username = new System.Windows.Forms.TextBox();
this.lab_username = new System.Windows.Forms.Label();
this.lab_login = new System.Windows.Forms.Label();
this.lab_server = new System.Windows.Forms.Label();
this.lab_advise = new System.Windows.Forms.Label();
this.btn_ok = new System.Windows.Forms.Button();
this.btn_cancel = new System.Windows.Forms.Button();
this.btn_help = new System.Windows.Forms.Button();
this.pgconn = new Npgsql.NpgsqlConnection();
this.tc_main.SuspendLayout();
this.tp_connection.SuspendLayout();
this.gb_add_parms.SuspendLayout();
this.SuspendLayout();
//
// tc_main
//
this.tc_main.AccessibleDescription = ((string)(resources.GetObject("tc_main.AccessibleDescription")));
this.tc_main.AccessibleName = ((string)(resources.GetObject("tc_main.AccessibleName")));
this.tc_main.Alignment = ((System.Windows.Forms.TabAlignment)(resources.GetObject("tc_main.Alignment")));
this.tc_main.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tc_main.Anchor")));
this.tc_main.Appearance = ((System.Windows.Forms.TabAppearance)(resources.GetObject("tc_main.Appearance")));
this.tc_main.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tc_main.BackgroundImage")));
this.tc_main.Controls.AddRange(new System.Windows.Forms.Control[] {
this.tp_connection});
this.tc_main.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tc_main.Dock")));
this.tc_main.Enabled = ((bool)(resources.GetObject("tc_main.Enabled")));
this.tc_main.Font = ((System.Drawing.Font)(resources.GetObject("tc_main.Font")));
this.tc_main.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tc_main.ImeMode")));
this.tc_main.ItemSize = ((System.Drawing.Size)(resources.GetObject("tc_main.ItemSize")));
this.tc_main.Location = ((System.Drawing.Point)(resources.GetObject("tc_main.Location")));
this.tc_main.Name = "tc_main";
this.tc_main.Padding = ((System.Drawing.Point)(resources.GetObject("tc_main.Padding")));
this.tc_main.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tc_main.RightToLeft")));
this.tc_main.SelectedIndex = 0;
this.tc_main.ShowToolTips = ((bool)(resources.GetObject("tc_main.ShowToolTips")));
this.tc_main.Size = ((System.Drawing.Size)(resources.GetObject("tc_main.Size")));
this.tc_main.TabIndex = ((int)(resources.GetObject("tc_main.TabIndex")));
this.tc_main.Text = resources.GetString("tc_main.Text");
this.tc_main.Visible = ((bool)(resources.GetObject("tc_main.Visible")));
//
// tp_connection
//
this.tp_connection.AccessibleDescription = ((string)(resources.GetObject("tp_connection.AccessibleDescription")));
this.tp_connection.AccessibleName = ((string)(resources.GetObject("tp_connection.AccessibleName")));
this.tp_connection.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tp_connection.Anchor")));
this.tp_connection.AutoScroll = ((bool)(resources.GetObject("tp_connection.AutoScroll")));
this.tp_connection.AutoScrollMargin = ((System.Drawing.Size)(resources.GetObject("tp_connection.AutoScrollMargin")));
this.tp_connection.AutoScrollMinSize = ((System.Drawing.Size)(resources.GetObject("tp_connection.AutoScrollMinSize")));
this.tp_connection.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tp_connection.BackgroundImage")));
this.tp_connection.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btn_refresh,
this.tb_server,
this.btn_check_connection,
this.gb_add_parms,
this.cb_select_db,
this.lab_select_db,
this.tb_password,
this.lab_password,
this.tb_username,
this.lab_username,
this.lab_login,
this.lab_server,
this.lab_advise});
this.tp_connection.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tp_connection.Dock")));
this.tp_connection.Enabled = ((bool)(resources.GetObject("tp_connection.Enabled")));
this.tp_connection.Font = ((System.Drawing.Font)(resources.GetObject("tp_connection.Font")));
this.tp_connection.ImageIndex = ((int)(resources.GetObject("tp_connection.ImageIndex")));
this.tp_connection.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tp_connection.ImeMode")));
this.tp_connection.Location = ((System.Drawing.Point)(resources.GetObject("tp_connection.Location")));
this.tp_connection.Name = "tp_connection";
this.tp_connection.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tp_connection.RightToLeft")));
this.tp_connection.Size = ((System.Drawing.Size)(resources.GetObject("tp_connection.Size")));
this.tp_connection.TabIndex = ((int)(resources.GetObject("tp_connection.TabIndex")));
this.tp_connection.Text = resources.GetString("tp_connection.Text");
this.tp_connection.ToolTipText = resources.GetString("tp_connection.ToolTipText");
this.tp_connection.Visible = ((bool)(resources.GetObject("tp_connection.Visible")));
//
// btn_refresh
//
this.btn_refresh.AccessibleDescription = ((string)(resources.GetObject("btn_refresh.AccessibleDescription")));
this.btn_refresh.AccessibleName = ((string)(resources.GetObject("btn_refresh.AccessibleName")));
this.btn_refresh.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("btn_refresh.Anchor")));
this.btn_refresh.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btn_refresh.BackgroundImage")));
this.btn_refresh.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("btn_refresh.Dock")));
this.btn_refresh.Enabled = ((bool)(resources.GetObject("btn_refresh.Enabled")));
this.btn_refresh.FlatStyle = ((System.Windows.Forms.FlatStyle)(resources.GetObject("btn_refresh.FlatStyle")));
this.btn_refresh.Font = ((System.Drawing.Font)(resources.GetObject("btn_refresh.Font")));
this.btn_refresh.Image = ((System.Drawing.Image)(resources.GetObject("btn_refresh.Image")));
this.btn_refresh.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_refresh.ImageAlign")));
this.btn_refresh.ImageIndex = ((int)(resources.GetObject("btn_refresh.ImageIndex")));
this.btn_refresh.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("btn_refresh.ImeMode")));
this.btn_refresh.Location = ((System.Drawing.Point)(resources.GetObject("btn_refresh.Location")));
this.btn_refresh.Name = "btn_refresh";
this.btn_refresh.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("btn_refresh.RightToLeft")));
this.btn_refresh.Size = ((System.Drawing.Size)(resources.GetObject("btn_refresh.Size")));
this.btn_refresh.TabIndex = ((int)(resources.GetObject("btn_refresh.TabIndex")));
this.btn_refresh.Text = resources.GetString("btn_refresh.Text");
this.btn_refresh.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_refresh.TextAlign")));
this.btn_refresh.Visible = ((bool)(resources.GetObject("btn_refresh.Visible")));
this.btn_refresh.Click += new System.EventHandler(this.btn_refresh_Click);
//
// tb_server
//
this.tb_server.AccessibleDescription = ((string)(resources.GetObject("tb_server.AccessibleDescription")));
this.tb_server.AccessibleName = ((string)(resources.GetObject("tb_server.AccessibleName")));
this.tb_server.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tb_server.Anchor")));
this.tb_server.AutoSize = ((bool)(resources.GetObject("tb_server.AutoSize")));
this.tb_server.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tb_server.BackgroundImage")));
this.tb_server.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tb_server.Dock")));
this.tb_server.Enabled = ((bool)(resources.GetObject("tb_server.Enabled")));
this.tb_server.Font = ((System.Drawing.Font)(resources.GetObject("tb_server.Font")));
this.tb_server.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tb_server.ImeMode")));
this.tb_server.Location = ((System.Drawing.Point)(resources.GetObject("tb_server.Location")));
this.tb_server.MaxLength = ((int)(resources.GetObject("tb_server.MaxLength")));
this.tb_server.Multiline = ((bool)(resources.GetObject("tb_server.Multiline")));
this.tb_server.Name = "tb_server";
this.tb_server.PasswordChar = ((char)(resources.GetObject("tb_server.PasswordChar")));
this.tb_server.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tb_server.RightToLeft")));
this.tb_server.ScrollBars = ((System.Windows.Forms.ScrollBars)(resources.GetObject("tb_server.ScrollBars")));
this.tb_server.Size = ((System.Drawing.Size)(resources.GetObject("tb_server.Size")));
this.tb_server.TabIndex = ((int)(resources.GetObject("tb_server.TabIndex")));
this.tb_server.Text = resources.GetString("tb_server.Text");
this.tb_server.TextAlign = ((System.Windows.Forms.HorizontalAlignment)(resources.GetObject("tb_server.TextAlign")));
this.tb_server.Visible = ((bool)(resources.GetObject("tb_server.Visible")));
this.tb_server.WordWrap = ((bool)(resources.GetObject("tb_server.WordWrap")));
//
// btn_check_connection
//
this.btn_check_connection.AccessibleDescription = ((string)(resources.GetObject("btn_check_connection.AccessibleDescription")));
this.btn_check_connection.AccessibleName = ((string)(resources.GetObject("btn_check_connection.AccessibleName")));
this.btn_check_connection.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("btn_check_connection.Anchor")));
this.btn_check_connection.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btn_check_connection.BackgroundImage")));
this.btn_check_connection.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("btn_check_connection.Dock")));
this.btn_check_connection.Enabled = ((bool)(resources.GetObject("btn_check_connection.Enabled")));
this.btn_check_connection.FlatStyle = ((System.Windows.Forms.FlatStyle)(resources.GetObject("btn_check_connection.FlatStyle")));
this.btn_check_connection.Font = ((System.Drawing.Font)(resources.GetObject("btn_check_connection.Font")));
this.btn_check_connection.Image = ((System.Drawing.Image)(resources.GetObject("btn_check_connection.Image")));
this.btn_check_connection.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_check_connection.ImageAlign")));
this.btn_check_connection.ImageIndex = ((int)(resources.GetObject("btn_check_connection.ImageIndex")));
this.btn_check_connection.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("btn_check_connection.ImeMode")));
this.btn_check_connection.Location = ((System.Drawing.Point)(resources.GetObject("btn_check_connection.Location")));
this.btn_check_connection.Name = "btn_check_connection";
this.btn_check_connection.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("btn_check_connection.RightToLeft")));
this.btn_check_connection.Size = ((System.Drawing.Size)(resources.GetObject("btn_check_connection.Size")));
this.btn_check_connection.TabIndex = ((int)(resources.GetObject("btn_check_connection.TabIndex")));
this.btn_check_connection.Text = resources.GetString("btn_check_connection.Text");
this.btn_check_connection.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_check_connection.TextAlign")));
this.btn_check_connection.Visible = ((bool)(resources.GetObject("btn_check_connection.Visible")));
this.btn_check_connection.Click += new System.EventHandler(this.btn_check_connection_Click);
//
// gb_add_parms
//
this.gb_add_parms.AccessibleDescription = ((string)(resources.GetObject("gb_add_parms.AccessibleDescription")));
this.gb_add_parms.AccessibleName = ((string)(resources.GetObject("gb_add_parms.AccessibleName")));
this.gb_add_parms.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("gb_add_parms.Anchor")));
this.gb_add_parms.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("gb_add_parms.BackgroundImage")));
this.gb_add_parms.Controls.AddRange(new System.Windows.Forms.Control[] {
this.tb_port,
this.lab_port,
this.tb_timeout,
this.lab_timeout});
this.gb_add_parms.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("gb_add_parms.Dock")));
this.gb_add_parms.Enabled = ((bool)(resources.GetObject("gb_add_parms.Enabled")));
this.gb_add_parms.Font = ((System.Drawing.Font)(resources.GetObject("gb_add_parms.Font")));
this.gb_add_parms.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("gb_add_parms.ImeMode")));
this.gb_add_parms.Location = ((System.Drawing.Point)(resources.GetObject("gb_add_parms.Location")));
this.gb_add_parms.Name = "gb_add_parms";
this.gb_add_parms.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("gb_add_parms.RightToLeft")));
this.gb_add_parms.Size = ((System.Drawing.Size)(resources.GetObject("gb_add_parms.Size")));
this.gb_add_parms.TabIndex = ((int)(resources.GetObject("gb_add_parms.TabIndex")));
this.gb_add_parms.TabStop = false;
this.gb_add_parms.Text = resources.GetString("gb_add_parms.Text");
this.gb_add_parms.Visible = ((bool)(resources.GetObject("gb_add_parms.Visible")));
//
// tb_port
//
this.tb_port.AccessibleDescription = ((string)(resources.GetObject("tb_port.AccessibleDescription")));
this.tb_port.AccessibleName = ((string)(resources.GetObject("tb_port.AccessibleName")));
this.tb_port.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tb_port.Anchor")));
this.tb_port.AutoSize = ((bool)(resources.GetObject("tb_port.AutoSize")));
this.tb_port.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tb_port.BackgroundImage")));
this.tb_port.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tb_port.Dock")));
this.tb_port.Enabled = ((bool)(resources.GetObject("tb_port.Enabled")));
this.tb_port.Font = ((System.Drawing.Font)(resources.GetObject("tb_port.Font")));
this.tb_port.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tb_port.ImeMode")));
this.tb_port.Location = ((System.Drawing.Point)(resources.GetObject("tb_port.Location")));
this.tb_port.MaxLength = ((int)(resources.GetObject("tb_port.MaxLength")));
this.tb_port.Multiline = ((bool)(resources.GetObject("tb_port.Multiline")));
this.tb_port.Name = "tb_port";
this.tb_port.PasswordChar = ((char)(resources.GetObject("tb_port.PasswordChar")));
this.tb_port.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tb_port.RightToLeft")));
this.tb_port.ScrollBars = ((System.Windows.Forms.ScrollBars)(resources.GetObject("tb_port.ScrollBars")));
this.tb_port.Size = ((System.Drawing.Size)(resources.GetObject("tb_port.Size")));
this.tb_port.TabIndex = ((int)(resources.GetObject("tb_port.TabIndex")));
this.tb_port.Text = resources.GetString("tb_port.Text");
this.tb_port.TextAlign = ((System.Windows.Forms.HorizontalAlignment)(resources.GetObject("tb_port.TextAlign")));
this.tb_port.Visible = ((bool)(resources.GetObject("tb_port.Visible")));
this.tb_port.WordWrap = ((bool)(resources.GetObject("tb_port.WordWrap")));
//
// lab_port
//
this.lab_port.AccessibleDescription = ((string)(resources.GetObject("lab_port.AccessibleDescription")));
this.lab_port.AccessibleName = ((string)(resources.GetObject("lab_port.AccessibleName")));
this.lab_port.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_port.Anchor")));
this.lab_port.AutoSize = ((bool)(resources.GetObject("lab_port.AutoSize")));
this.lab_port.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_port.Dock")));
this.lab_port.Enabled = ((bool)(resources.GetObject("lab_port.Enabled")));
this.lab_port.Font = ((System.Drawing.Font)(resources.GetObject("lab_port.Font")));
this.lab_port.Image = ((System.Drawing.Image)(resources.GetObject("lab_port.Image")));
this.lab_port.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_port.ImageAlign")));
this.lab_port.ImageIndex = ((int)(resources.GetObject("lab_port.ImageIndex")));
this.lab_port.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_port.ImeMode")));
this.lab_port.Location = ((System.Drawing.Point)(resources.GetObject("lab_port.Location")));
this.lab_port.Name = "lab_port";
this.lab_port.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_port.RightToLeft")));
this.lab_port.Size = ((System.Drawing.Size)(resources.GetObject("lab_port.Size")));
this.lab_port.TabIndex = ((int)(resources.GetObject("lab_port.TabIndex")));
this.lab_port.Text = resources.GetString("lab_port.Text");
this.lab_port.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_port.TextAlign")));
this.lab_port.Visible = ((bool)(resources.GetObject("lab_port.Visible")));
//
// tb_timeout
//
this.tb_timeout.AccessibleDescription = ((string)(resources.GetObject("tb_timeout.AccessibleDescription")));
this.tb_timeout.AccessibleName = ((string)(resources.GetObject("tb_timeout.AccessibleName")));
this.tb_timeout.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tb_timeout.Anchor")));
this.tb_timeout.AutoSize = ((bool)(resources.GetObject("tb_timeout.AutoSize")));
this.tb_timeout.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tb_timeout.BackgroundImage")));
this.tb_timeout.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tb_timeout.Dock")));
this.tb_timeout.Enabled = ((bool)(resources.GetObject("tb_timeout.Enabled")));
this.tb_timeout.Font = ((System.Drawing.Font)(resources.GetObject("tb_timeout.Font")));
this.tb_timeout.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tb_timeout.ImeMode")));
this.tb_timeout.Location = ((System.Drawing.Point)(resources.GetObject("tb_timeout.Location")));
this.tb_timeout.MaxLength = ((int)(resources.GetObject("tb_timeout.MaxLength")));
this.tb_timeout.Multiline = ((bool)(resources.GetObject("tb_timeout.Multiline")));
this.tb_timeout.Name = "tb_timeout";
this.tb_timeout.PasswordChar = ((char)(resources.GetObject("tb_timeout.PasswordChar")));
this.tb_timeout.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tb_timeout.RightToLeft")));
this.tb_timeout.ScrollBars = ((System.Windows.Forms.ScrollBars)(resources.GetObject("tb_timeout.ScrollBars")));
this.tb_timeout.Size = ((System.Drawing.Size)(resources.GetObject("tb_timeout.Size")));
this.tb_timeout.TabIndex = ((int)(resources.GetObject("tb_timeout.TabIndex")));
this.tb_timeout.Text = resources.GetString("tb_timeout.Text");
this.tb_timeout.TextAlign = ((System.Windows.Forms.HorizontalAlignment)(resources.GetObject("tb_timeout.TextAlign")));
this.tb_timeout.Visible = ((bool)(resources.GetObject("tb_timeout.Visible")));
this.tb_timeout.WordWrap = ((bool)(resources.GetObject("tb_timeout.WordWrap")));
//
// lab_timeout
//
this.lab_timeout.AccessibleDescription = ((string)(resources.GetObject("lab_timeout.AccessibleDescription")));
this.lab_timeout.AccessibleName = ((string)(resources.GetObject("lab_timeout.AccessibleName")));
this.lab_timeout.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_timeout.Anchor")));
this.lab_timeout.AutoSize = ((bool)(resources.GetObject("lab_timeout.AutoSize")));
this.lab_timeout.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_timeout.Dock")));
this.lab_timeout.Enabled = ((bool)(resources.GetObject("lab_timeout.Enabled")));
this.lab_timeout.Font = ((System.Drawing.Font)(resources.GetObject("lab_timeout.Font")));
this.lab_timeout.Image = ((System.Drawing.Image)(resources.GetObject("lab_timeout.Image")));
this.lab_timeout.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_timeout.ImageAlign")));
this.lab_timeout.ImageIndex = ((int)(resources.GetObject("lab_timeout.ImageIndex")));
this.lab_timeout.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_timeout.ImeMode")));
this.lab_timeout.Location = ((System.Drawing.Point)(resources.GetObject("lab_timeout.Location")));
this.lab_timeout.Name = "lab_timeout";
this.lab_timeout.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_timeout.RightToLeft")));
this.lab_timeout.Size = ((System.Drawing.Size)(resources.GetObject("lab_timeout.Size")));
this.lab_timeout.TabIndex = ((int)(resources.GetObject("lab_timeout.TabIndex")));
this.lab_timeout.Text = resources.GetString("lab_timeout.Text");
this.lab_timeout.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_timeout.TextAlign")));
this.lab_timeout.Visible = ((bool)(resources.GetObject("lab_timeout.Visible")));
//
// cb_select_db
//
this.cb_select_db.AccessibleDescription = ((string)(resources.GetObject("cb_select_db.AccessibleDescription")));
this.cb_select_db.AccessibleName = ((string)(resources.GetObject("cb_select_db.AccessibleName")));
this.cb_select_db.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("cb_select_db.Anchor")));
this.cb_select_db.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("cb_select_db.BackgroundImage")));
this.cb_select_db.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("cb_select_db.Dock")));
this.cb_select_db.Enabled = ((bool)(resources.GetObject("cb_select_db.Enabled")));
this.cb_select_db.Font = ((System.Drawing.Font)(resources.GetObject("cb_select_db.Font")));
this.cb_select_db.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("cb_select_db.ImeMode")));
this.cb_select_db.IntegralHeight = ((bool)(resources.GetObject("cb_select_db.IntegralHeight")));
this.cb_select_db.ItemHeight = ((int)(resources.GetObject("cb_select_db.ItemHeight")));
this.cb_select_db.Location = ((System.Drawing.Point)(resources.GetObject("cb_select_db.Location")));
this.cb_select_db.MaxDropDownItems = ((int)(resources.GetObject("cb_select_db.MaxDropDownItems")));
this.cb_select_db.MaxLength = ((int)(resources.GetObject("cb_select_db.MaxLength")));
this.cb_select_db.Name = "cb_select_db";
this.cb_select_db.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("cb_select_db.RightToLeft")));
this.cb_select_db.Size = ((System.Drawing.Size)(resources.GetObject("cb_select_db.Size")));
this.cb_select_db.TabIndex = ((int)(resources.GetObject("cb_select_db.TabIndex")));
this.cb_select_db.Text = resources.GetString("cb_select_db.Text");
this.cb_select_db.Visible = ((bool)(resources.GetObject("cb_select_db.Visible")));
this.cb_select_db.DropDown += new System.EventHandler(this.cb_select_db_DropDown);
//
// lab_select_db
//
this.lab_select_db.AccessibleDescription = ((string)(resources.GetObject("lab_select_db.AccessibleDescription")));
this.lab_select_db.AccessibleName = ((string)(resources.GetObject("lab_select_db.AccessibleName")));
this.lab_select_db.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_select_db.Anchor")));
this.lab_select_db.AutoSize = ((bool)(resources.GetObject("lab_select_db.AutoSize")));
this.lab_select_db.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_select_db.Dock")));
this.lab_select_db.Enabled = ((bool)(resources.GetObject("lab_select_db.Enabled")));
this.lab_select_db.Font = ((System.Drawing.Font)(resources.GetObject("lab_select_db.Font")));
this.lab_select_db.Image = ((System.Drawing.Image)(resources.GetObject("lab_select_db.Image")));
this.lab_select_db.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_select_db.ImageAlign")));
this.lab_select_db.ImageIndex = ((int)(resources.GetObject("lab_select_db.ImageIndex")));
this.lab_select_db.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_select_db.ImeMode")));
this.lab_select_db.Location = ((System.Drawing.Point)(resources.GetObject("lab_select_db.Location")));
this.lab_select_db.Name = "lab_select_db";
this.lab_select_db.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_select_db.RightToLeft")));
this.lab_select_db.Size = ((System.Drawing.Size)(resources.GetObject("lab_select_db.Size")));
this.lab_select_db.TabIndex = ((int)(resources.GetObject("lab_select_db.TabIndex")));
this.lab_select_db.Text = resources.GetString("lab_select_db.Text");
this.lab_select_db.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_select_db.TextAlign")));
this.lab_select_db.Visible = ((bool)(resources.GetObject("lab_select_db.Visible")));
//
// tb_password
//
this.tb_password.AccessibleDescription = ((string)(resources.GetObject("tb_password.AccessibleDescription")));
this.tb_password.AccessibleName = ((string)(resources.GetObject("tb_password.AccessibleName")));
this.tb_password.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tb_password.Anchor")));
this.tb_password.AutoSize = ((bool)(resources.GetObject("tb_password.AutoSize")));
this.tb_password.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tb_password.BackgroundImage")));
this.tb_password.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tb_password.Dock")));
this.tb_password.Enabled = ((bool)(resources.GetObject("tb_password.Enabled")));
this.tb_password.Font = ((System.Drawing.Font)(resources.GetObject("tb_password.Font")));
this.tb_password.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tb_password.ImeMode")));
this.tb_password.Location = ((System.Drawing.Point)(resources.GetObject("tb_password.Location")));
this.tb_password.MaxLength = ((int)(resources.GetObject("tb_password.MaxLength")));
this.tb_password.Multiline = ((bool)(resources.GetObject("tb_password.Multiline")));
this.tb_password.Name = "tb_password";
this.tb_password.PasswordChar = ((char)(resources.GetObject("tb_password.PasswordChar")));
this.tb_password.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tb_password.RightToLeft")));
this.tb_password.ScrollBars = ((System.Windows.Forms.ScrollBars)(resources.GetObject("tb_password.ScrollBars")));
this.tb_password.Size = ((System.Drawing.Size)(resources.GetObject("tb_password.Size")));
this.tb_password.TabIndex = ((int)(resources.GetObject("tb_password.TabIndex")));
this.tb_password.Text = resources.GetString("tb_password.Text");
this.tb_password.TextAlign = ((System.Windows.Forms.HorizontalAlignment)(resources.GetObject("tb_password.TextAlign")));
this.tb_password.Visible = ((bool)(resources.GetObject("tb_password.Visible")));
this.tb_password.WordWrap = ((bool)(resources.GetObject("tb_password.WordWrap")));
//
// lab_password
//
this.lab_password.AccessibleDescription = ((string)(resources.GetObject("lab_password.AccessibleDescription")));
this.lab_password.AccessibleName = ((string)(resources.GetObject("lab_password.AccessibleName")));
this.lab_password.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_password.Anchor")));
this.lab_password.AutoSize = ((bool)(resources.GetObject("lab_password.AutoSize")));
this.lab_password.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_password.Dock")));
this.lab_password.Enabled = ((bool)(resources.GetObject("lab_password.Enabled")));
this.lab_password.Font = ((System.Drawing.Font)(resources.GetObject("lab_password.Font")));
this.lab_password.Image = ((System.Drawing.Image)(resources.GetObject("lab_password.Image")));
this.lab_password.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_password.ImageAlign")));
this.lab_password.ImageIndex = ((int)(resources.GetObject("lab_password.ImageIndex")));
this.lab_password.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_password.ImeMode")));
this.lab_password.Location = ((System.Drawing.Point)(resources.GetObject("lab_password.Location")));
this.lab_password.Name = "lab_password";
this.lab_password.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_password.RightToLeft")));
this.lab_password.Size = ((System.Drawing.Size)(resources.GetObject("lab_password.Size")));
this.lab_password.TabIndex = ((int)(resources.GetObject("lab_password.TabIndex")));
this.lab_password.Text = resources.GetString("lab_password.Text");
this.lab_password.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_password.TextAlign")));
this.lab_password.Visible = ((bool)(resources.GetObject("lab_password.Visible")));
//
// tb_username
//
this.tb_username.AccessibleDescription = ((string)(resources.GetObject("tb_username.AccessibleDescription")));
this.tb_username.AccessibleName = ((string)(resources.GetObject("tb_username.AccessibleName")));
this.tb_username.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("tb_username.Anchor")));
this.tb_username.AutoSize = ((bool)(resources.GetObject("tb_username.AutoSize")));
this.tb_username.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("tb_username.BackgroundImage")));
this.tb_username.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("tb_username.Dock")));
this.tb_username.Enabled = ((bool)(resources.GetObject("tb_username.Enabled")));
this.tb_username.Font = ((System.Drawing.Font)(resources.GetObject("tb_username.Font")));
this.tb_username.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("tb_username.ImeMode")));
this.tb_username.Location = ((System.Drawing.Point)(resources.GetObject("tb_username.Location")));
this.tb_username.MaxLength = ((int)(resources.GetObject("tb_username.MaxLength")));
this.tb_username.Multiline = ((bool)(resources.GetObject("tb_username.Multiline")));
this.tb_username.Name = "tb_username";
this.tb_username.PasswordChar = ((char)(resources.GetObject("tb_username.PasswordChar")));
this.tb_username.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("tb_username.RightToLeft")));
this.tb_username.ScrollBars = ((System.Windows.Forms.ScrollBars)(resources.GetObject("tb_username.ScrollBars")));
this.tb_username.Size = ((System.Drawing.Size)(resources.GetObject("tb_username.Size")));
this.tb_username.TabIndex = ((int)(resources.GetObject("tb_username.TabIndex")));
this.tb_username.Text = resources.GetString("tb_username.Text");
this.tb_username.TextAlign = ((System.Windows.Forms.HorizontalAlignment)(resources.GetObject("tb_username.TextAlign")));
this.tb_username.Visible = ((bool)(resources.GetObject("tb_username.Visible")));
this.tb_username.WordWrap = ((bool)(resources.GetObject("tb_username.WordWrap")));
//
// lab_username
//
this.lab_username.AccessibleDescription = ((string)(resources.GetObject("lab_username.AccessibleDescription")));
this.lab_username.AccessibleName = ((string)(resources.GetObject("lab_username.AccessibleName")));
this.lab_username.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_username.Anchor")));
this.lab_username.AutoSize = ((bool)(resources.GetObject("lab_username.AutoSize")));
this.lab_username.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_username.Dock")));
this.lab_username.Enabled = ((bool)(resources.GetObject("lab_username.Enabled")));
this.lab_username.Font = ((System.Drawing.Font)(resources.GetObject("lab_username.Font")));
this.lab_username.Image = ((System.Drawing.Image)(resources.GetObject("lab_username.Image")));
this.lab_username.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_username.ImageAlign")));
this.lab_username.ImageIndex = ((int)(resources.GetObject("lab_username.ImageIndex")));
this.lab_username.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_username.ImeMode")));
this.lab_username.Location = ((System.Drawing.Point)(resources.GetObject("lab_username.Location")));
this.lab_username.Name = "lab_username";
this.lab_username.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_username.RightToLeft")));
this.lab_username.Size = ((System.Drawing.Size)(resources.GetObject("lab_username.Size")));
this.lab_username.TabIndex = ((int)(resources.GetObject("lab_username.TabIndex")));
this.lab_username.Text = resources.GetString("lab_username.Text");
this.lab_username.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_username.TextAlign")));
this.lab_username.Visible = ((bool)(resources.GetObject("lab_username.Visible")));
//
// lab_login
//
this.lab_login.AccessibleDescription = ((string)(resources.GetObject("lab_login.AccessibleDescription")));
this.lab_login.AccessibleName = ((string)(resources.GetObject("lab_login.AccessibleName")));
this.lab_login.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_login.Anchor")));
this.lab_login.AutoSize = ((bool)(resources.GetObject("lab_login.AutoSize")));
this.lab_login.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_login.Dock")));
this.lab_login.Enabled = ((bool)(resources.GetObject("lab_login.Enabled")));
this.lab_login.Font = ((System.Drawing.Font)(resources.GetObject("lab_login.Font")));
this.lab_login.Image = ((System.Drawing.Image)(resources.GetObject("lab_login.Image")));
this.lab_login.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_login.ImageAlign")));
this.lab_login.ImageIndex = ((int)(resources.GetObject("lab_login.ImageIndex")));
this.lab_login.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_login.ImeMode")));
this.lab_login.Location = ((System.Drawing.Point)(resources.GetObject("lab_login.Location")));
this.lab_login.Name = "lab_login";
this.lab_login.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_login.RightToLeft")));
this.lab_login.Size = ((System.Drawing.Size)(resources.GetObject("lab_login.Size")));
this.lab_login.TabIndex = ((int)(resources.GetObject("lab_login.TabIndex")));
this.lab_login.Text = resources.GetString("lab_login.Text");
this.lab_login.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_login.TextAlign")));
this.lab_login.Visible = ((bool)(resources.GetObject("lab_login.Visible")));
//
// lab_server
//
this.lab_server.AccessibleDescription = ((string)(resources.GetObject("lab_server.AccessibleDescription")));
this.lab_server.AccessibleName = ((string)(resources.GetObject("lab_server.AccessibleName")));
this.lab_server.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_server.Anchor")));
this.lab_server.AutoSize = ((bool)(resources.GetObject("lab_server.AutoSize")));
this.lab_server.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_server.Dock")));
this.lab_server.Enabled = ((bool)(resources.GetObject("lab_server.Enabled")));
this.lab_server.Font = ((System.Drawing.Font)(resources.GetObject("lab_server.Font")));
this.lab_server.Image = ((System.Drawing.Image)(resources.GetObject("lab_server.Image")));
this.lab_server.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_server.ImageAlign")));
this.lab_server.ImageIndex = ((int)(resources.GetObject("lab_server.ImageIndex")));
this.lab_server.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_server.ImeMode")));
this.lab_server.Location = ((System.Drawing.Point)(resources.GetObject("lab_server.Location")));
this.lab_server.Name = "lab_server";
this.lab_server.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_server.RightToLeft")));
this.lab_server.Size = ((System.Drawing.Size)(resources.GetObject("lab_server.Size")));
this.lab_server.TabIndex = ((int)(resources.GetObject("lab_server.TabIndex")));
this.lab_server.Text = resources.GetString("lab_server.Text");
this.lab_server.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_server.TextAlign")));
this.lab_server.Visible = ((bool)(resources.GetObject("lab_server.Visible")));
//
// lab_advise
//
this.lab_advise.AccessibleDescription = ((string)(resources.GetObject("lab_advise.AccessibleDescription")));
this.lab_advise.AccessibleName = ((string)(resources.GetObject("lab_advise.AccessibleName")));
this.lab_advise.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("lab_advise.Anchor")));
this.lab_advise.AutoSize = ((bool)(resources.GetObject("lab_advise.AutoSize")));
this.lab_advise.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("lab_advise.Dock")));
this.lab_advise.Enabled = ((bool)(resources.GetObject("lab_advise.Enabled")));
this.lab_advise.Font = ((System.Drawing.Font)(resources.GetObject("lab_advise.Font")));
this.lab_advise.Image = ((System.Drawing.Image)(resources.GetObject("lab_advise.Image")));
this.lab_advise.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_advise.ImageAlign")));
this.lab_advise.ImageIndex = ((int)(resources.GetObject("lab_advise.ImageIndex")));
this.lab_advise.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("lab_advise.ImeMode")));
this.lab_advise.Location = ((System.Drawing.Point)(resources.GetObject("lab_advise.Location")));
this.lab_advise.Name = "lab_advise";
this.lab_advise.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("lab_advise.RightToLeft")));
this.lab_advise.Size = ((System.Drawing.Size)(resources.GetObject("lab_advise.Size")));
this.lab_advise.TabIndex = ((int)(resources.GetObject("lab_advise.TabIndex")));
this.lab_advise.Text = resources.GetString("lab_advise.Text");
this.lab_advise.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("lab_advise.TextAlign")));
this.lab_advise.Visible = ((bool)(resources.GetObject("lab_advise.Visible")));
//
// btn_ok
//
this.btn_ok.AccessibleDescription = ((string)(resources.GetObject("btn_ok.AccessibleDescription")));
this.btn_ok.AccessibleName = ((string)(resources.GetObject("btn_ok.AccessibleName")));
this.btn_ok.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("btn_ok.Anchor")));
this.btn_ok.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btn_ok.BackgroundImage")));
this.btn_ok.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("btn_ok.Dock")));
this.btn_ok.Enabled = ((bool)(resources.GetObject("btn_ok.Enabled")));
this.btn_ok.FlatStyle = ((System.Windows.Forms.FlatStyle)(resources.GetObject("btn_ok.FlatStyle")));
this.btn_ok.Font = ((System.Drawing.Font)(resources.GetObject("btn_ok.Font")));
this.btn_ok.Image = ((System.Drawing.Image)(resources.GetObject("btn_ok.Image")));
this.btn_ok.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_ok.ImageAlign")));
this.btn_ok.ImageIndex = ((int)(resources.GetObject("btn_ok.ImageIndex")));
this.btn_ok.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("btn_ok.ImeMode")));
this.btn_ok.Location = ((System.Drawing.Point)(resources.GetObject("btn_ok.Location")));
this.btn_ok.Name = "btn_ok";
this.btn_ok.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("btn_ok.RightToLeft")));
this.btn_ok.Size = ((System.Drawing.Size)(resources.GetObject("btn_ok.Size")));
this.btn_ok.TabIndex = ((int)(resources.GetObject("btn_ok.TabIndex")));
this.btn_ok.Text = resources.GetString("btn_ok.Text");
this.btn_ok.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_ok.TextAlign")));
this.btn_ok.Visible = ((bool)(resources.GetObject("btn_ok.Visible")));
this.btn_ok.Click += new System.EventHandler(this.btn_ok_Click);
//
// btn_cancel
//
this.btn_cancel.AccessibleDescription = ((string)(resources.GetObject("btn_cancel.AccessibleDescription")));
this.btn_cancel.AccessibleName = ((string)(resources.GetObject("btn_cancel.AccessibleName")));
this.btn_cancel.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("btn_cancel.Anchor")));
this.btn_cancel.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btn_cancel.BackgroundImage")));
this.btn_cancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btn_cancel.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("btn_cancel.Dock")));
this.btn_cancel.Enabled = ((bool)(resources.GetObject("btn_cancel.Enabled")));
this.btn_cancel.FlatStyle = ((System.Windows.Forms.FlatStyle)(resources.GetObject("btn_cancel.FlatStyle")));
this.btn_cancel.Font = ((System.Drawing.Font)(resources.GetObject("btn_cancel.Font")));
this.btn_cancel.Image = ((System.Drawing.Image)(resources.GetObject("btn_cancel.Image")));
this.btn_cancel.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_cancel.ImageAlign")));
this.btn_cancel.ImageIndex = ((int)(resources.GetObject("btn_cancel.ImageIndex")));
this.btn_cancel.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("btn_cancel.ImeMode")));
this.btn_cancel.Location = ((System.Drawing.Point)(resources.GetObject("btn_cancel.Location")));
this.btn_cancel.Name = "btn_cancel";
this.btn_cancel.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("btn_cancel.RightToLeft")));
this.btn_cancel.Size = ((System.Drawing.Size)(resources.GetObject("btn_cancel.Size")));
this.btn_cancel.TabIndex = ((int)(resources.GetObject("btn_cancel.TabIndex")));
this.btn_cancel.Text = resources.GetString("btn_cancel.Text");
this.btn_cancel.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_cancel.TextAlign")));
this.btn_cancel.Visible = ((bool)(resources.GetObject("btn_cancel.Visible")));
this.btn_cancel.Click += new System.EventHandler(this.btn_cancel_Click);
//
// btn_help
//
this.btn_help.AccessibleDescription = ((string)(resources.GetObject("btn_help.AccessibleDescription")));
this.btn_help.AccessibleName = ((string)(resources.GetObject("btn_help.AccessibleName")));
this.btn_help.Anchor = ((System.Windows.Forms.AnchorStyles)(resources.GetObject("btn_help.Anchor")));
this.btn_help.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btn_help.BackgroundImage")));
this.btn_help.Dock = ((System.Windows.Forms.DockStyle)(resources.GetObject("btn_help.Dock")));
this.btn_help.Enabled = ((bool)(resources.GetObject("btn_help.Enabled")));
this.btn_help.FlatStyle = ((System.Windows.Forms.FlatStyle)(resources.GetObject("btn_help.FlatStyle")));
this.btn_help.Font = ((System.Drawing.Font)(resources.GetObject("btn_help.Font")));
this.btn_help.Image = ((System.Drawing.Image)(resources.GetObject("btn_help.Image")));
this.btn_help.ImageAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_help.ImageAlign")));
this.btn_help.ImageIndex = ((int)(resources.GetObject("btn_help.ImageIndex")));
this.btn_help.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("btn_help.ImeMode")));
this.btn_help.Location = ((System.Drawing.Point)(resources.GetObject("btn_help.Location")));
this.btn_help.Name = "btn_help";
this.btn_help.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("btn_help.RightToLeft")));
this.btn_help.Size = ((System.Drawing.Size)(resources.GetObject("btn_help.Size")));
this.btn_help.TabIndex = ((int)(resources.GetObject("btn_help.TabIndex")));
this.btn_help.Text = resources.GetString("btn_help.Text");
this.btn_help.TextAlign = ((System.Drawing.ContentAlignment)(resources.GetObject("btn_help.TextAlign")));
this.btn_help.Visible = ((bool)(resources.GetObject("btn_help.Visible")));
this.btn_help.Click += new System.EventHandler(this.btn_help_Click);
//
// ConnectionStringEditorForm
//
this.AcceptButton = this.btn_ok;
this.AccessibleDescription = ((string)(resources.GetObject("$this.AccessibleDescription")));
this.AccessibleName = ((string)(resources.GetObject("$this.AccessibleName")));
this.AutoScaleBaseSize = ((System.Drawing.Size)(resources.GetObject("$this.AutoScaleBaseSize")));
this.AutoScroll = ((bool)(resources.GetObject("$this.AutoScroll")));
this.AutoScrollMargin = ((System.Drawing.Size)(resources.GetObject("$this.AutoScrollMargin")));
this.AutoScrollMinSize = ((System.Drawing.Size)(resources.GetObject("$this.AutoScrollMinSize")));
this.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("$this.BackgroundImage")));
this.CancelButton = this.btn_cancel;
this.ClientSize = ((System.Drawing.Size)(resources.GetObject("$this.ClientSize")));
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btn_help,
this.btn_cancel,
this.btn_ok,
this.tc_main});
this.Enabled = ((bool)(resources.GetObject("$this.Enabled")));
this.Font = ((System.Drawing.Font)(resources.GetObject("$this.Font")));
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
this.ImeMode = ((System.Windows.Forms.ImeMode)(resources.GetObject("$this.ImeMode")));
this.Location = ((System.Drawing.Point)(resources.GetObject("$this.Location")));
this.MaximizeBox = false;
this.MaximumSize = ((System.Drawing.Size)(resources.GetObject("$this.MaximumSize")));
this.MinimizeBox = false;
this.MinimumSize = ((System.Drawing.Size)(resources.GetObject("$this.MinimumSize")));
this.Name = "ConnectionStringEditorForm";
this.RightToLeft = ((System.Windows.Forms.RightToLeft)(resources.GetObject("$this.RightToLeft")));
this.ShowInTaskbar = false;
this.StartPosition = ((System.Windows.Forms.FormStartPosition)(resources.GetObject("$this.StartPosition")));
this.Text = resources.GetString("$this.Text");
this.tc_main.ResumeLayout(false);
this.tp_connection.ResumeLayout(false);
this.gb_add_parms.ResumeLayout(false);
this.ResumeLayout(false);
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
List<IGeometry> features = new List<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT *, AsBinary(" + this.GeometryColumn + ") AS sharpmap_tempgeometry ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName,col.DataType,col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach(System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public SharpMap.Geometries.BoundingBox GetExtents()
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT EXTENT(" + this.GeometryColumn + ") FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object result = command.ExecuteScalar();
conn.Close();
if (result == System.DBNull.Value)
return null;
string strBox = (string)result;
if (strBox.StartsWith("BOX("))
{
string[] vals = strBox.Substring(4, strBox.IndexOf(")")-4).Split(new char[2] { ',', ' ' });
return SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(
double.Parse(vals[0], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[2], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[1], SharpMap.Map.numberFormat_EnUS),
double.Parse(vals[3], SharpMap.Map.numberFormat_EnUS));
}
else
return null;
}
}
}
/// <summary>
/// Updates a single Company with one or more values
/// </summary>
/// <param name="updatedCompany">The new data for the Company you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> UpdateCompany(CompanyPatch updatedCompany)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
var sqlPatchOperations = new StringBuilder();
var obj = updatedCompany;
var operationCount = 0;
if (obj.CompanyName != null)
{
sqlPatchOperations.AppendLine(obj.CompanyName.Operation == OperationKind.Remove
? "companyName = NULL,"
: "companyName = @CompanyName,"
);
operationCount++;
}
if (obj.CompanyAddress != null)
{
sqlPatchOperations.AppendLine(obj.CompanyAddress.Operation == OperationKind.Remove
? "companyAddress = NULL,"
: "companyAddress = @CompanyAddress,"
);
operationCount++;
}
if (obj.CompanyPostcode != null)
{
sqlPatchOperations.AppendLine(obj.CompanyPostcode.Operation == OperationKind.Remove
? "companyPostcode = NULL,"
: "companyPostcode = @CompanyPostcode,"
);
operationCount++;
}
if (obj.CompanyContact != null)
{
sqlPatchOperations.AppendLine(obj.CompanyContact.Operation == OperationKind.Remove
? "companyContact = NULL,"
: "companyContact = @CompanyContact,"
);
operationCount++;
}
if (obj.CompanyPhone != null)
{
sqlPatchOperations.AppendLine(obj.CompanyPhone.Operation == OperationKind.Remove
? "companyPhone = NULL,"
: "companyPhone = @CompanyPhone,"
);
operationCount++;
}
if (obj.CompanyEmail != null)
{
sqlPatchOperations.AppendLine(obj.CompanyEmail.Operation == OperationKind.Remove
? "companyEmail = NULL,"
: "companyEmail = @CompanyEmail,"
);
operationCount++;
}
if (obj.ExternalAccountId != null)
{
sqlPatchOperations.AppendLine(obj.ExternalAccountId.Operation == OperationKind.Remove
? "externalAccountId = NULL,"
: "externalAccountId = @ExternalAccountId,"
);
operationCount++;
}
if (obj.ExternalCustomerId != null)
{
sqlPatchOperations.AppendLine(obj.ExternalCustomerId.Operation == OperationKind.Remove
? "externalCustomerId = NULL,"
: "externalCustomerId = @ExternalCustomerId,"
);
operationCount++;
}
if (obj.ExternalAccessToken != null)
{
sqlPatchOperations.AppendLine(obj.ExternalAccessToken.Operation == OperationKind.Remove
? "externalAccessToken = NULL,"
: "externalAccessToken = @ExternalAccessToken,"
);
operationCount++;
}
if (obj.ExternalRefreshToken != null)
{
sqlPatchOperations.AppendLine(obj.ExternalRefreshToken.Operation == OperationKind.Remove
? "externalRefreshToken = NULL,"
: "externalRefreshToken = @ExternalRefreshToken,"
);
operationCount++;
}
if (obj.ExternalTokenExpiry != null)
{
sqlPatchOperations.AppendLine(obj.ExternalTokenExpiry.Operation == OperationKind.Remove
? "externalTokenExpiry = NULL,"
: "externalTokenExpiry = @ExternalTokenExpiry,"
);
operationCount++;
}
if (obj.ReferenceCode != null)
{
sqlPatchOperations.AppendLine(obj.ReferenceCode.Operation == OperationKind.Remove
? "referenceCode = NULL,"
: "referenceCode = @ReferenceCode,"
);
operationCount++;
}
var patchOperations = sqlPatchOperations.ToString();
if (operationCount > 0)
{
// Remove final ", " from StringBuilder to ensure query is valid
patchOperations = patchOperations.TrimEnd(System.Environment.NewLine.ToCharArray());
patchOperations = patchOperations.TrimEnd(',');
}
await con.ExecuteAsync($"UPDATE \"Company\" SET {patchOperations} WHERE companyId = @ResourceId", new {
ResourceId = obj.ResourceId,
CompanyName = (string)(obj.CompanyName == default ? default : obj.CompanyName.Value),
CompanyAddress = (string)(obj.CompanyAddress == default ? default : obj.CompanyAddress.Value),
CompanyPostcode = (string)(obj.CompanyPostcode == default ? default : obj.CompanyPostcode.Value),
CompanyContact = (string)(obj.CompanyContact == default ? default : obj.CompanyContact.Value),
CompanyPhone = (string)(obj.CompanyPhone == default ? default : obj.CompanyPhone.Value),
CompanyEmail = (string)(obj.CompanyEmail == default ? default : obj.CompanyEmail.Value),
ExternalAccountId = (string)(obj.ExternalAccountId == default ? default : obj.ExternalAccountId.Value),
ExternalCustomerId = (string)(obj.ExternalCustomerId == default ? default : obj.ExternalCustomerId.Value),
ExternalAccessToken = (string)(obj.ExternalAccessToken == default ? default : obj.ExternalAccessToken.Value),
ExternalRefreshToken = (string)(obj.ExternalRefreshToken == default ? default : obj.ExternalRefreshToken.Value),
ExternalTokenExpiry = (DateTime)(obj.ExternalTokenExpiry == default ? default : obj.ExternalTokenExpiry.Value),
ReferenceCode = (string)(obj.ReferenceCode == default ? default : obj.ReferenceCode.Value)
}).ConfigureAwait(false);
return(Result.Ok());
}
public async Task<List<Subdivision>> SubdivisionsAndVolumes()
{
var result = new List<Subdivision>();
var query = @"select id, population, name, ST_AsGeoJSON(boundry) as boundary, beer_volume, wine_volume, spirits_volume
from subdivisions
where province = 'Ontario'";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
result.Add(new Subdivision(reader));
}
}
return result;
}
public void Open(bool transaction)
{
connection = new Npgsql.NpgsqlConnection(connstring);
connection.Open();
comm = new Npgsql.NpgsqlCommand("", connection);
if (transaction)
{
comm.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers(options =>
{
options.ReturnHttpNotAcceptable = true;
options.Filters.Add(new ConsumesAttribute("application/json"));
options.Filters.Add(new ProducesAttribute("application/json"));
options.Filters.Add(new ProducesResponseTypeAttribute(Status400BadRequest));
options.Filters.Add(new ProducesResponseTypeAttribute(Status500InternalServerError));
});
services.AddSwaggerGen(setupAction =>
{
setupAction.SwaggerDoc(
"LibraryOpenAPISpecification",
new OpenApiInfo
{
Title = "Cricinfo.API - Documentation",
Version = Configuration["APIVersion"]
});
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
setupAction.IncludeXmlComments(xmlPath);
setupAction.UseInlineDefinitionsForEnums();
});
services.AddScoped <ICricInfoCommandService>(sp =>
{
return(new CricInfoCommandService <MatchController>(
Configuration.GetConnectionString("PostgresConnection"),
sp.GetRequiredService <ILogger <MatchController> >()));
});
services.AddScoped <ICricInfoQueryService>(sp =>
{
return(new CricInfoQueryService <MatchController>(
Configuration.GetConnectionString("PostgresConnection"),
sp.GetRequiredService <ILogger <MatchController> >()));
});
services.AddHealthChecks()
.AddCheck("API Healthcheck", () => HealthCheckResult.Healthy())
.AddCheck("SQL Healthcheck", () =>
{
using var conn = new Npgsql.NpgsqlConnection(Configuration.GetConnectionString("PostgresConnection"));
try
{
conn.Open();
return(HealthCheckResult.Healthy());
}
catch (Npgsql.NpgsqlException)
{
return(HealthCheckResult.Degraded());
}
});
services.AddHealthChecksUI(setupSettings =>
{
setupSettings.AddHealthCheckEndpoint("Healthchecks", Configuration["HealthcheckEndpoint"]);
setupSettings.SetEvaluationTimeInSeconds(300);
})
.AddInMemoryStorage();
}
public void SaveAppointmentSheetFromCalendar(AppointmentSheet appointment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (appointment.AppointmentSheetId > 0)
{
query = CalendarAppointmentUpdateQuery;
isUpdate = true;
}
else
{
query = AppointmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateofappointment", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastupdated", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("street", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdateto", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("event_reference", NpgsqlTypes.NpgsqlDbType.Text));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = appointment.City;
command.Parameters[1].Value = appointment.DayOfAppointment;
command.Parameters[2].Value = appointment.LastUpdated;
command.Parameters[3].Value = appointment.State;
command.Parameters[4].Value = appointment.Street;
command.Parameters[5].Value = appointment.ZipCode;
command.Parameters[6].Value = appointment.AppointmentDateFrom;
command.Parameters[7].Value = appointment.AppointmentDateTo;
command.Parameters[8].Value = appointment.Event_Reference;
//command.Parameters[10].Value = card.Creator;
if (isUpdate)
{
command.Parameters[9].Value = appointment.AppointmentSheetId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
public static IDbConnection GetConnection(string provider, string cstring)
{
IDbConnection cn = null;
switch (provider.ToLower())
{
case "sqlite":
if (cstring.Length > 0)
{
cn = new SqliteConnection(cstring);
}
break;
case "oracle":
if (cstring.Length > 0)
{
cn = new System.Data.OracleClient.OracleConnection(cstring);
}
break;
case "postgresql":
if (cstring.Length > 0)
{
cn = new Npgsql.NpgsqlConnection(cstring);
}
break;
case "firebird.net":
if (cstring.Length > 0)
{
cn = new FirebirdSql.Data.FirebirdClient.FbConnection(cstring);
}
break;
case "mysql.net":
// can't connect unless information provided;
// when user wants to set the connection programmatically this they should do this
if (cstring.Length > 0)
{
cn = new MySql.Data.MySqlClient.MySqlConnection(cstring);
}
break;
case "sql":
// can't connect unless information provided;
// when user wants to set the connection programmatically this they should do this
if (cstring.Length > 0)
{
cn = new SqlConnection(cstring);
}
break;
case "odbc":
cn = new OdbcConnection(cstring);
break;
//case "oledb":
// cn = new OleDbConnection(cstring);
// break;
case "filedirectory":
cn = new fyiReporting.Data.FileDirConnection(cstring);
break;
case "xml":
cn = new fyiReporting.Data.XmlConnection(cstring);
break;
//case "webservice":
// cn = new fyiReporting.Data.WebServiceConnection(cstring);
// break;
case "weblog":
cn = new fyiReporting.Data.LogConnection(cstring);
break;
case "text":
cn = new fyiReporting.Data.TxtConnection(cstring);
break;
case "itunes":
cn = new fyiReporting.Data.iTunesConnection(cstring);
break;
#if EnableBundleLinux
// See properties -> Build -> Conditional compilation symbols
case "sqlite":
cn = new Mono.Data.Sqlite.SqliteConnection(cstring);
break;
case "postgresql":
cn = new Npgsql.NpgsqlConnection(cstring);
break;
#endif
default:
if (SqlEntries == null)
{ // if never initialized; we should init
RdlEngineConfigInit();
}
System.Console.WriteLine("Attempt to find provider");
SqlConfigEntry sce = SqlEntries[provider] as SqlConfigEntry;
if (sce == null || sce.CodeModule == null)
{
if (sce != null && sce.ErrorMsg != null)
{ // error during initialization??
throw new Exception(sce.ErrorMsg);
}
break;
}
System.Console.WriteLine("Provider Create Instance");
object[] args = new object[] { cstring };
Assembly asm = sce.CodeModule;
object o = asm.CreateInstance(sce.ClassName, false,
BindingFlags.CreateInstance, null, args, null, null);
if (o == null)
{
throw new Exception(string.Format(Strings.RdlEngineConfig_Error_UnableCreateInstance, sce.ClassName, provider));
}
cn = o as IDbConnection;
break;
}
return(cn);
}
public IDbConnection GetOpenConnection()
{
IDbConnection cnn;
if (ConnectionProvider != null)
{
if (ConnectionProvider == "Npgsql.NpgsqlConnection")
{
cnn = new Npgsql.NpgsqlConnection(ConnectionString);
}
else
{
throw new Exception("Unknown connection provider");
}
}
else
{
cnn = new SqlConnection(ConnectionString);
}
cnn.Open();
if (AppSettings.FetchDataInReadUncommitted && cnn is SqlConnection) { cnn.Execute("set transaction isolation level read uncommitted"); }
return cnn;
}
/// <summary>
/// 执行存储过程或Sql语句返回DataTable
/// </summary>
/// <param name="sql">存储过程名称或Sql语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="paras">参数</param>
/// <returns></returns>
private DataTable Run(string sql, CommandType commandType, params object[] paras)
{
DataTable table = new DataTable();
switch (this.DBType)
{
case DBTypeEnum.SqlServer:
SqlConnection con = this.Database.GetDbConnection() as SqlConnection;
SqlDataAdapter adapter = new SqlDataAdapter();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
adapter.SelectCommand = cmd;
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
adapter.Fill(table);
adapter.SelectCommand.Parameters.Clear();
}
break;
case DBTypeEnum.MySql:
MySqlConnection mySqlCon = this.Database.GetDbConnection() as MySqlConnection;
using (MySqlCommand cmd = new MySqlCommand(sql, mySqlCon))
{
if (mySqlCon.State == ConnectionState.Closed)
{
mySqlCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
MySqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
mySqlCon.Close();
}
break;
case DBTypeEnum.PgSql:
Npgsql.NpgsqlConnection npgCon = this.Database.GetDbConnection() as Npgsql.NpgsqlConnection;
using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, npgCon))
{
if (npgCon.State == ConnectionState.Closed)
{
npgCon.Open();
}
cmd.CommandTimeout = 2400;
cmd.CommandType = commandType;
if (paras != null)
{
foreach (var param in paras)
{
cmd.Parameters.Add(param);
}
}
Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
npgCon.Close();
}
break;
}
return(table);
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name='" + this.Table + "'";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
public async Task<Feature> BoundaryGeoJson(int subdivId)
{
var result = new Feature(new Point(new Position()));
var query = @"select ST_AsGeoJSON(boundry, 15, 4) as boundary
from subdivisions
where id = @subdivId";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
cmd.Parameters.AddWithValue("@subdivId", subdivId);
var boundary = await cmd.ExecuteScalarAsync() as string;
if (boundary.Contains("\"type\":\"MultiPolygon\""))
{
var multipolygon = JsonConvert.DeserializeObject<MultiPolygon>(boundary);
result = new Feature(multipolygon);
}
else if (boundary.Contains("\"type\":\"Polygon\""))
{
var polygon = JsonConvert.DeserializeObject<Polygon>(boundary);
result = new Feature(polygon);
}
}
}
return result;
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "select * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry from " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + RowID.ToString() + "'";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
if(ds.Tables[0].Rows.Count>0)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return fdr;
}
else
return null;
}
else
return null;
}
}
}
/// <summary>
/// Updates a single EmployeeSession with one or more values
/// </summary>
/// <param name="updatedEmployeeSession">The new data for the EmployeeSession you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> UpdateEmployeeSession(EmployeeSessionPatch updatedEmployeeSession)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
var sqlPatchOperations = new StringBuilder();
var obj = updatedEmployeeSession;
var operationCount = 0;
if (obj.SessionId != null)
{
sqlPatchOperations.AppendLine(obj.SessionId.Operation == OperationKind.Remove
? "sessionId = NULL,"
: "sessionId = @SessionId,"
);
operationCount++;
}
if (obj.EmployeeId != null)
{
sqlPatchOperations.AppendLine(obj.EmployeeId.Operation == OperationKind.Remove
? "employeeId = NULL,"
: "employeeId = @EmployeeId,"
);
operationCount++;
}
if (obj.AuthToken != null)
{
sqlPatchOperations.AppendLine(obj.AuthToken.Operation == OperationKind.Remove
? "authToken = NULL,"
: "authToken = @AuthToken,"
);
operationCount++;
}
if (obj.RefreshToken != null)
{
sqlPatchOperations.AppendLine(obj.RefreshToken.Operation == OperationKind.Remove
? "refreshToken = NULL,"
: "refreshToken = @RefreshToken,"
);
operationCount++;
}
if (obj.Expiry != null)
{
sqlPatchOperations.AppendLine(obj.Expiry.Operation == OperationKind.Remove
? "expiry = NULL,"
: "expiry = @Expiry,"
);
operationCount++;
}
if (obj.RefreshExpiry != null)
{
sqlPatchOperations.AppendLine(obj.RefreshExpiry.Operation == OperationKind.Remove
? "refreshExpiry = NULL,"
: "refreshExpiry = @RefreshExpiry,"
);
operationCount++;
}
var patchOperations = sqlPatchOperations.ToString();
if (operationCount > 0)
{
// Remove final ", " from StringBuilder to ensure query is valid
patchOperations = patchOperations.TrimEnd(System.Environment.NewLine.ToCharArray());
patchOperations = patchOperations.TrimEnd(',');
}
await con.ExecuteAsync($"UPDATE \"EmployeeSession\" SET {patchOperations} WHERE sessionId = @ResourceId", new {
ResourceId = obj.ResourceId,
SessionId = (int)(obj.SessionId == default ? default : obj.SessionId.Value),
EmployeeId = (int)(obj.EmployeeId == default ? default : obj.EmployeeId.Value),
AuthToken = (string)(obj.AuthToken == default ? default : obj.AuthToken.Value),
RefreshToken = (string)(obj.RefreshToken == default ? default : obj.RefreshToken.Value),
Expiry = (DateTime)(obj.Expiry == default ? default : obj.Expiry.Value),
RefreshExpiry = (DateTime)(obj.RefreshExpiry == default ? default : obj.RefreshExpiry.Value)
}).ConfigureAwait(false);
return(Result.Ok());
}
public void SetupDBProviderFromConfig()
{
SetConnectionStringFromConfig();
connection = new Npgsql.NpgsqlConnection(connstring);
comm = new Npgsql.NpgsqlCommand("", connection);
}
public void PgSql()
{
PgSqlCommandExecutor.Configure();
using (var connection = new Npgsql.NpgsqlConnection("host=localhost;username=postgres;password=Norther;database=Test"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var testData = new TestTable() {
Foo = "Foo",
Name = "Hello",
TheDate = DateTime.Now
};
TestTable result;
using (Time(elapsed => Console.WriteLine("Insert : " + elapsed)))
{
result = connection.Insert(testData, transaction);
}
Console.WriteLine("Database generated UID : " + result.TheSecondId);
List<TestTable> bulkResult;
using (Time(elapsed => Console.WriteLine("Bulk insert : " + elapsed)))
{
bulkResult = connection.Insert(Enumerable.Range(0, 100).Select(i => new TestTable() { Name = i.ToString() }), transaction).ToList();
Assert.AreEqual(100, bulkResult.Count());
}
using (Time(elapsed => Console.WriteLine("Bulk update : " + elapsed)))
{
bulkResult = connection.Update(
bulkResult.Select(item => item.SetProperties(new TestTable() { Name = item.Name + " strongly typed mutation" })),
transaction).ToList();
}
using (Time(elapsed => Console.WriteLine("Update : " + elapsed)))
{
var newDate = DateTime.Now.AddYears(-50);
var update = connection.Update(
result.SetProperties(
new { TheDate = newDate }
),
transaction
);
Assert.AreEqual(newDate.Year, update.TheDate.Year);
}
using (Time(elapsed => Console.WriteLine("FindById : " + elapsed)))
{
Assert.IsNotNull(connection.FindById<TestTable>(new { result.Id, result.TheSecondId }, transaction));
}
transaction.Commit();
}
}
}
internal static void ValidateVersion(string connectionStringName, string owner)
{
_ConnectionStringName = connectionStringName;
//open the connection, start the transaction
using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
#region v1.1
//test up to v1.1. This is basicially the entire original schema.
Log.Debug(d => d("Checking for v1.1 schema..."));
if (!TableExists("users", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.1. Updating..."));
RunScript(GetDDLResource("v1._1.InitializeSettings.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Tables.sql"), null, conn, trans);
RunScript(GetDDLResource("v1._1.Types.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_by_id.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.user_is_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._1.assign_users_to_roles.sql"), null, conn, trans);
}
#endregion
#region v1.2
//test up to v1.2. This adds the purge activity feature, for cleaning up old data.
Log.Debug(d => d("Checking for v1.2 schema..."));
if (!FunctionExists("purge_activity", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.2. Updating..."));
RunStatement(GetDDLResource("v1._2.purge_activity.sql"), null, conn, trans);
}
#endregion
#region v1.3
//test up to v1.3. This makes users, roles, and applications case-insensitive.
//also adds the version table to check against future versions.
Log.Debug(d => d("Checking for v1.3 schema..."));
if (!TableExists("versions", conn, trans))
{
Log.Info(i => i("The database does not seem to be compatible with v1.3. Updating..."));
RunScript(GetDDLResource("v1._3.TableChanges.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.assign_users_to_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.create_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.delete_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_all_users.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_online_count.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_roles_for_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_credentials.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_user_name_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_email.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_by_username.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_in_role.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.get_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.record_login_event.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.role_exists.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.remove_users_from_roles.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.unlock_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_password.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.update_user_q_and_a.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._3.user_is_in_role.sql"), null, conn, trans);
}
#endregion
//v1.3 is the first version that includes version tracking in the database.
var currentVersion = GetDBVersion(conn, trans);
#region v1.4
Log.Debug(d => d("Checking for v1.4 schema (no DDL action required)..."));
if (currentVersion == "1.3")
{
currentVersion = "1.4";
}
#endregion
#region v1.5
Log.Debug(d => d("Checking for v1.5 schema (no DDL action required)..."));
if (currentVersion == "1.4")
{
currentVersion = "1.5";
}
#endregion
#region v1.6
if (currentVersion == "1.5")
{
//v1.6 fixes an update problem where the last login time was not being persisted.
RunStatement(GetDDLResource("v1._6.record_login_event.sql"), null, conn, trans);
currentVersion = "1.6";
}
#endregion
#region v1.7
if (currentVersion == "1.6")
{
RunStatement(GetDDLResource("v1._7.get_number_of_users_online.sql"), null, conn, trans);
RunStatement(GetDDLResource("v1._7.get_users_online.sql"), null, conn, trans);
currentVersion = "1.7";
}
#endregion
/*
* Other checks and updates will go here.
*
*
*/
SetDBVersion(conn, trans, currentVersion);
trans.Commit();
}
}
}
public bool Execute(ISessionContext context)
{
var logger = context.GetLogger();
var options = context.Options;
AWSS3Helper s3 = null;
try
{
var loadScript = options.Get("sqlScriptPath", "");
var customCSharpScriptPath = options.Get("customCSharpScriptPath", "");
if ((!String.IsNullOrWhiteSpace(loadScript)) && (!String.IsNullOrWhiteSpace(customCSharpScriptPath)))
{
throw new Exception("No action configured");
}
// prepare paths
var parsedLoadScript = FileTransferDetails.ParseSearchPath(loadScript);
var parsedCustomCSharpScriptPath = FileTransferDetails.ParseSearchPath(customCSharpScriptPath);
// open s3 connection
s3 = new AWSS3Helper(options.Get("awsAccessKey", ""), options.Get("awsSecretAccessKey", ""), parsedLoadScript.BucketName, Amazon.RegionEndpoint.USEast1, true);
// load sql script
string sqlScript = null;
if (!String.IsNullOrWhiteSpace(loadScript))
{
sqlScript = s3.ReadFileAsText(parsedLoadScript.FilePath, true);
}
// generate code
IAWSRedshiftPluginDynamicScript customCode = null;
if (!String.IsNullOrWhiteSpace(customCSharpScriptPath))
{
// load custom code
var csharpScript = s3.ReadFileAsText(parsedCustomCSharpScriptPath.FilePath, true);
var evaluator = ScriptEvaluator.CompileAndCreateModel(csharpScript);
if (evaluator.HasError || evaluator.Model == null)
{
throw new Exception("Script compilation error. " + (evaluator.Message ?? "<empty>"));
}
customCode = evaluator.Model;
}
// execute commands
using (var conn = new Npgsql.NpgsqlConnection(RedshiftHelper.GetConnectionString(context)))
{
conn.Open();
if (customCode != null)
{
logger.Debug("Custom csharp code Initialize");
customCode.Initialize(conn, s3, context);
logger.Debug("Custom csharp code BeforeExecution");
customCode.BeforeExecution();
logger.Debug("Custom csharp code PrepareSqlCOPYCommand");
if (!String.IsNullOrEmpty(sqlScript))
{
sqlScript = customCode.PrepareSqlCOPYCommand(sqlScript);
}
}
if (!String.IsNullOrEmpty(sqlScript))
{
logger.Debug("SQL command start");
try
{
conn.Execute(sqlScript);
}
catch (Exception ex)
{
// do nothing in case of timeout... some operations may take a while to complete...
if (ex.Message.IndexOf("timeout", StringComparison.OrdinalIgnoreCase) < 0)
{
throw ex;
}
logger.Info("SQL command executed, but is still running (connection timeout)...");
}
logger.Debug("SQL command end");
}
if (customCode != null)
{
logger.Debug("Custom csharp code AfterExecution");
customCode.AfterExecution();
}
}
logger.Success("Done");
}
catch (Exception ex)
{
context.Error = ex.Message;
logger.Error(ex);
return(false);
}
return(true);
}
protected static void RunStatement(string statement, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null)
{
parameters = new Dictionary <string, Npgsql.NpgsqlParameter>();
}
using (var cmd = new Npgsql.NpgsqlCommand(statement, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (statement.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
public void SaveAppointmentSheet(AppointmentSheet appointment)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (appointment.AppointmentSheetId > 0)
{
query = AppointmentUpdateQuery;
isUpdate = true;
}
else
{
query = AppointmentInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("addingservices", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentlocation", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsalesagent", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("comment", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("createdat", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("currentlyacceptedcards", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("currentprocessor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateofappointment", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("howmanylocations", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("internet", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("lastupdated", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("moto", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("multilocation", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("newequipment", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("newsetup", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("price", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("score", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("singlelocation", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("street", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("swipe", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("unhappy", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("volume", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("creator", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("parentlead", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("location", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentdateto", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("reschedule", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("creatorname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("singleloccheck", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("event_reference", NpgsqlTypes.NpgsqlDbType.Text));
//command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentzone", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("appointmentid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = appointment.AddingServices;
command.Parameters[1].Value = appointment.AppointmentLocation;
command.Parameters[2].Value = appointment.AssignedSalesAgent;
command.Parameters[3].Value = appointment.City;
command.Parameters[4].Value = appointment.Comment;
command.Parameters[5].Value = appointment.CreatedAt;
command.Parameters[6].Value = appointment.CurrentlyAcceptingCards;
command.Parameters[7].Value = appointment.CurrentProcessor;
command.Parameters[8].Value = appointment.DayOfAppointment;
command.Parameters[9].Value = appointment.HowManyLocations;
command.Parameters[10].Value = appointment.Internet;
command.Parameters[11].Value = appointment.LastUpdated;
command.Parameters[12].Value = appointment.Moto;
command.Parameters[13].Value = appointment.MultiLocation;
command.Parameters[14].Value = appointment.NewEquipment;
command.Parameters[15].Value = appointment.NewSetUp;
command.Parameters[16].Value = appointment.Price;
command.Parameters[17].Value = appointment.Score;
command.Parameters[18].Value = appointment.SingleLocation;
command.Parameters[19].Value = appointment.State;
command.Parameters[20].Value = appointment.Street;
command.Parameters[21].Value = appointment.Swipe;
command.Parameters[22].Value = appointment.Unhappy;
command.Parameters[23].Value = appointment.Volume;
command.Parameters[24].Value = appointment.ZipCode;
command.Parameters[25].Value = appointment.CreatorId;
command.Parameters[26].Value = appointment.ParentLeadId;
command.Parameters[27].Value = appointment.Location;
command.Parameters[28].Value = appointment.AppointmentDateFrom;
command.Parameters[29].Value = appointment.AppointmentDateTo;
command.Parameters[30].Value = appointment.Reschedule;
command.Parameters[31].Value = appointment.CreatorName;
command.Parameters[32].Value = appointment.SingleLocCheck;
command.Parameters[33].Value = appointment.Event_Reference;
//command.Parameters[34].Value = appointment.AppointmentZone;
//command.Parameters[10].Value = card.Creator;
if (isUpdate)
{
command.Parameters[34].Value = appointment.AppointmentSheetId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
}
private void btnMigration_Click1(object sender, EventArgs e)
{
var tablenames = new List <string>();
foreach (ListViewItem item in lvTable.CheckedItems)
{
tablenames.Add(item.Text);
}
var frmMessage = new frmMessage();
var thread = new Thread(delegate()
{
foreach (var item in tablenames)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在查询{item}表……";
}));
List <dynamic> list = null;
string pgsql = null;
using (var con = new SqlConnection(_sqlconnectionstring))
{
list = con.Query <dynamic>($@"select * from {item}").ToList();
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，总共{list.Count()}条记录……";
}));
var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')");
var pgsqlField = new StringBuilder();
var pgsqlPar = new StringBuilder();
foreach (var field in fields)
{
pgsqlField.Append($"{field},");
pgsqlPar.Append($"@{field},");
}
pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})";
}
var index = 0;
var count = 1000;
using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring))
{
while (index * count < list.Count)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，{index * count}/{list.Count()}……";
}));
var newlist = new List <dynamic>();
newlist.AddRange(list.Skip(index * count).Take(count));
pgcon.Execute(pgsql, newlist);
index++;
}
}
}
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.picLoad.Image = Resources.migration1;
frmMessage.labMessage.Text = $"全部搬运完成！";
frmMessage.tmrCount.Stop();
}));
});
frmMessage.MigrationThread = thread;
frmMessage.tmrCount.Start();
thread.Start();
frmMessage.ShowDialog();
}
protected static void RunScript(string script, IDictionary <string, Npgsql.NpgsqlParameter> parameters, Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlTransaction trans)
{
if (parameters == null)
{
parameters = new Dictionary <string, Npgsql.NpgsqlParameter>();
}
foreach (var commandText in script.Split(';'))
{
var sql = commandText + ";";
Log.Debug(d => d("Script command: {0}", sql));
using (var cmd = new Npgsql.NpgsqlCommand(sql, conn, trans))
{
foreach (var parameter in parameters.Keys)
{
if (sql.Contains(parameter))
{
var value = parameters[parameter];
Log.Debug(d => d("The command contains the parameter '{0}', setting value to '{1}'...", parameter, value.Value));
cmd.Parameters.Add(value);
}
}
cmd.ExecuteNonQuery();
}
}
Log.Debug(d => d("Script complete."));
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<SharpMap.Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
System.Collections.Generic.List<SharpMap.Geometries.Geometry> features = new Collection<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
IGeometry geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
if(geom!=null)
features.Add(geom);
}
}
}
conn.Close();
}
}
return features;
}
private void btnMigration_Click(object sender, EventArgs e)
{
var tablenames = new List <string>();
foreach (ListViewItem item in lvTable.CheckedItems)
{
tablenames.Add(item.Text);
}
var frmMessage = new frmMessage();
var thread = new Thread(delegate()
{
foreach (var item in tablenames)
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在查询{item}表……";
}));
var pagesize = 5000;
var pageindex = 1;
while (true)
{
List <dynamic> list = null;
string pgsql = null;
List <string> keyfields = null;
int recordCount = 0;
using (var con = new SqlConnection(_sqlconnectionstring))
{
keyfields = con.Query <string>($@"SELECT c.name Cname FROM sys.objects T INNER JOIN sys.objects P ON t.object_id=p.parent_object_id AND t.type='U' AND p.type='PK' INNER JOIN sys.SysColumns C ON c.id=t.object_id INNER JOIN sysindexes i ON i.name=p.name INNER JOIN sysindexkeys k ON k.id=c.id AND k.colid=c.colid AND k.indid=i.indid where t.name='{item}'").ToList();
if (keyfields.Count() > 0)
{
list = con.Query <dynamic>($@"SELECT TOP {pagesize} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {keyfields[0]}) AS RowNo,* FROM {item})querytable WHERE RowNo > {(pageindex - 1) * pagesize} ").ToList();
if (list.Count == 0)
{
break;
}
}
else
{
list = con.Query <dynamic>($@"SELECT * from {item}").ToList();
}
recordCount = con.ExecuteScalar <int>($@"SELECT count(1) as sl from {item}");
//生成pg的insert into 语句
var fields = con.Query <string>($@"select sc.name from syscolumns sc,systypes st where sc.xtype=st.xtype and st.status=0 and sc.id in(select id from sysobjects where xtype='U' and name='{item}')");
var pgsqlField = new StringBuilder();
var pgsqlPar = new StringBuilder();
foreach (var field in fields)
{
pgsqlField.Append($"{field},");
pgsqlPar.Append($"@{field},");
}
pgsql = $"insert into {item}({pgsqlField.ToString().TrimEnd(',')}) values({pgsqlPar.ToString().TrimEnd(',')})";
}
using (var pgcon = new Npgsql.NpgsqlConnection(_pgconnectionstring))
{
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.labMessage.Text = $"正在搬运{item}表，{pageindex * pagesize}/{recordCount}条记录……";
}));
pgcon.Execute(pgsql, list);
}
//没有关键了，就退出
if (keyfields.Count() == 0)
{
break;
}
pageindex++;
}
}
this.BeginInvoke(new ThreadStart(delegate()
{
frmMessage.picLoad.Image = Resources.migration1;
frmMessage.labMessage.Text = $"全部搬运完成！";
frmMessage.tmrCount.Stop();
}));
});
frmMessage.MigrationThread = thread;
frmMessage.tmrCount.Start();
thread.Start();
frmMessage.ShowDialog();
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom FROM " + this.Table + " WHERE " + this.ObjectIdColumn + "='" + oid.ToString() + "'";
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
public static IDbConnection GetConnection(string provider, string cstring)
{
IDbConnection cn = null;
switch (provider.ToLower())
{
case "sql":
// can't connect unless information provided;
// when user wants to set the connection programmatically this they should do this
if (cstring.Length > 0)
cn = new SqlConnection(cstring);
break;
case "odbc":
cn = new OdbcConnection(cstring);
break;
case "oledb":
cn = new OleDbConnection(cstring);
break;
case "filedirectory":
cn = new fyiReporting.Data.FileDirConnection(cstring);
break;
case "xml":
cn = new fyiReporting.Data.XmlConnection(cstring);
break;
case "webservice":
cn = new fyiReporting.Data.WebServiceConnection(cstring);
break;
case "weblog":
cn = new fyiReporting.Data.LogConnection(cstring);
break;
case "text":
cn = new fyiReporting.Data.TxtConnection(cstring);
break;
case "itunes":
cn = new fyiReporting.Data.iTunesConnection(cstring);
break;
#if EnableBundleLinux
// See properties -> Build -> Conditional compilation symbols
case "sqlite":
cn = new Mono.Data.Sqlite.SqliteConnection(cstring);
break;
case "postgresql":
cn = new Npgsql.NpgsqlConnection(cstring);
break;
#endif
default:
if (SqlEntries == null){ // if never initialized; we should init
RdlEngineConfigInit();
}
System.Console.WriteLine("Attempt to find provider");
SqlConfigEntry sce = SqlEntries[provider] as SqlConfigEntry;
if (sce == null || sce.CodeModule == null)
{
if (sce != null && sce.ErrorMsg != null){ // error during initialization??
throw new Exception(sce.ErrorMsg);
}
break;
}
System.Console.WriteLine("Provider Create Instance");
object[] args = new object[] { cstring };
Assembly asm = sce.CodeModule;
object o = asm.CreateInstance(sce.ClassName, false,
BindingFlags.CreateInstance, null, args, null, null);
if (o == null)
throw new Exception(string.Format(Strings.RdlEngineConfig_Error_UnableCreateInstance, sce.ClassName, provider));
cn = o as IDbConnection;
break;
}
return cn;
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public System.Collections.Generic.List<uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strBbox = "box2d('BOX3D(" +
bbox.MinX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MinY.ToString(SharpMap.Map.numberFormat_EnUS) + "," +
bbox.MaxX.ToString(SharpMap.Map.numberFormat_EnUS) + " " +
bbox.MaxY.ToString(SharpMap.Map.numberFormat_EnUS) + ")'::box3d)";
if (this.SRID > 0)
strBbox = "setSRID(" + strBbox + "," + this.SRID.ToString(Map.numberFormat_EnUS) + ")";
string strSQL = "SELECT " + this.ObjectIdColumn + " ";
strSQL += "FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}
public void SaveLead(Domain.Lead lead)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (lead.LeadId > 0)
{
query = LeadUpdateQuery;
isUpdate = true;
}
else
{
query = LeadInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("companyname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2title", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2firstname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalphonenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("numbertocall", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("faxnumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("additionalemailaddress", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("websitelink", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress1", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("streetaddress2", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("state", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("zonenumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("callbackdate", NpgsqlTypes.NpgsqlDbType.Date));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignoreddate", NpgsqlTypes.NpgsqlDbType.Date));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedaauserid", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact1lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("contact2lastname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("suppressed", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ignored", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dateimported", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("reassigned", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphonechecked", NpgsqlTypes.NpgsqlDbType.Boolean));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("leadid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = lead.CompanyName;
command.Parameters[1].Value = lead.Contact1Title;
command.Parameters[2].Value = lead.Contact1FirstName;
command.Parameters[3].Value = lead.Contact2Title;
command.Parameters[4].Value = lead.Contact2FirstName;
command.Parameters[5].Value = lead.PrimaryPhoneNumber;
command.Parameters[6].Value = lead.AddtionalPhoneNumber;
command.Parameters[7].Value = lead.NumberToCall;
command.Parameters[8].Value = lead.FaxNumber;
command.Parameters[9].Value = lead.PrimaryEmailAddress;
command.Parameters[10].Value = lead.AdditonalEmailAddress;
command.Parameters[11].Value = lead.WebsiteLink;
command.Parameters[12].Value = lead.StreetAddress1;
command.Parameters[13].Value = lead.StreetAddress2;
command.Parameters[14].Value = lead.City;
command.Parameters[15].Value = lead.State;
command.Parameters[16].Value = lead.ZipCode;
command.Parameters[17].Value = lead.ZoneNumber;
command.Parameters[18].Value = lead.Status;
command.Parameters[19].Value = lead.AssignedSAUserId;
command.Parameters[20].Value = lead.CallbackDate;
command.Parameters[21].Value = lead.IgnoredDate;
command.Parameters[22].Value = lead.AssignedAAUserId;
command.Parameters[23].Value = lead.Contact1LastName;
command.Parameters[24].Value = lead.Contact2LastName;
command.Parameters[25].Value = lead.Suppressed;
command.Parameters[26].Value = lead.Ignored;
command.Parameters[27].Value = lead.DateTimeImported;
command.Parameters[28].Value = lead.Reassigned;
command.Parameters[29].Value = lead.PrimaryPhoneChecked;
if (isUpdate)
{
command.Parameters[30].Value = lead.LeadId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
if (lead.NewFilePath != null && lead.NewFilePath != string.Empty)
{
PGUploadedfileRepository upFile = new PGUploadedfileRepository();
upFile.InsertLeadFile(lead.LeadId, lead.NewFilePath);
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<IGeometry> features = new List<IGeometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strGeom + " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
//To save account(s) to DB
public void SaveAccounts(Domain.Account account)
{
string query;
bool isUpdate = false;
// Want to know right off the bat if we're doing a insert or update
if (account.AccountId > 0)
{
query = AccountUpdateQuery;
isUpdate = true;
}
else
{
query = AccountInsertQuery;
}
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString()))
{
conn.Open();
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, conn))
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("merchantid", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountname", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("aacreator", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("assignedsalesrep", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("salesrepnumber", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("officenumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("status", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountapprovaldate", NpgsqlTypes.NpgsqlDbType.Timestamp));
command.Parameters.Add(new Npgsql.NpgsqlParameter("annualfee", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("estimatedmonthlyvolume", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ht", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("hmv", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("platform", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("vendor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("vip", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mbp", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("freesupplies", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("pcirefund", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingstreet", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingcity", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingstate", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("mailingzipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbastreet", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbacity", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbastate", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("dbazipcode", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryphone", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryphone", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("faxnumber", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryemail", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryemail", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("website", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("credit", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("debit", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("arb", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("cim", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ip", NpgsqlTypes.NpgsqlDbType.Boolean));
command.Parameters.Add(new Npgsql.NpgsqlParameter("giftcardprocessor", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secur_chex", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("software", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("ecommerce", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminal", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminalowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primaryterminalquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminal", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminalowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondaryterminalquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipment", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipmentowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("checkequipmentquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpad", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpadowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("primarypinpadquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpad", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpadowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("secondarypinpadquantity", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("printer", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("priterowner", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new Npgsql.NpgsqlParameter("uploadfiles", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters.Add(new Npgsql.NpgsqlParameter("parentlead", NpgsqlTypes.NpgsqlDbType.Integer));
if (isUpdate)
{
command.Parameters.Add(new Npgsql.NpgsqlParameter("accountid", NpgsqlTypes.NpgsqlDbType.Integer));
}
command.Prepare();
command.Parameters[0].Value = account.MerchantId;
command.Parameters[1].Value = account.AccountName;
command.Parameters[2].Value = account.AACreator;
command.Parameters[3].Value = account.AssignedSalesRep;
command.Parameters[4].Value = account.SalesRepNumber;
command.Parameters[5].Value = account.OfficeNumber;
command.Parameters[6].Value = account.Status;
command.Parameters[7].Value = account.AccountApprovalDate;
command.Parameters[8].Value = account.AnnualFee;
command.Parameters[9].Value = account.EstimatedMonthlyVolume;
command.Parameters[10].Value = account.HT;
command.Parameters[11].Value = account.HMV;
command.Parameters[12].Value = account.Platform;
command.Parameters[13].Value = account.Vendor;
command.Parameters[14].Value = account.VIP;
command.Parameters[15].Value = account.MBP;
command.Parameters[16].Value = account.FreeSupplies;
command.Parameters[17].Value = account.PCIRefund;
command.Parameters[18].Value = account.MailingStreet;
command.Parameters[19].Value = account.MailingCity;
command.Parameters[20].Value = account.MailingState;
command.Parameters[21].Value = account.MailingZipcode;
command.Parameters[22].Value = account.DBAStreet;
command.Parameters[23].Value = account.DBACity;
command.Parameters[24].Value = account.DBAState;
command.Parameters[25].Value = account.DBAZipcode;
command.Parameters[26].Value = account.PrimaryPhone;
command.Parameters[27].Value = account.SecondaryPhone;
command.Parameters[28].Value = account.FaxNumber;
command.Parameters[29].Value = account.PrimaryEmail;
command.Parameters[30].Value = account.SecondaryEmail;
command.Parameters[31].Value = account.Website;
command.Parameters[32].Value = account.Credit;
command.Parameters[33].Value = account.Debit;
command.Parameters[34].Value = account.ARB;
command.Parameters[35].Value = account.CIM;
command.Parameters[36].Value = account.IP;
command.Parameters[37].Value = account.GiftCardProcessor;
command.Parameters[38].Value = account.Secur_Chex;
command.Parameters[39].Value = account.Software;
command.Parameters[40].Value = account.ECommerace;
command.Parameters[41].Value = account.PrimaryTerminal;
command.Parameters[42].Value = account.PrimaryTerminalOwner;
command.Parameters[43].Value = account.PrimaryTerminalQuantity;
command.Parameters[44].Value = account.SecondaryTerminal;
command.Parameters[45].Value = account.SecondaryTerminalOwner;
command.Parameters[46].Value = account.SecondaryTerminalQuantity;
command.Parameters[47].Value = account.CheckEquipment;
command.Parameters[48].Value = account.CheckEquipmentOwner;
command.Parameters[49].Value = account.CheckEquipmentQuantity;
command.Parameters[50].Value = account.PrimaryPINpad;
command.Parameters[51].Value = account.PrimaryPINpadOwner;
command.Parameters[52].Value = account.PrimaryPINpadQuantity;
command.Parameters[53].Value = account.SecondaryPINpad;
command.Parameters[54].Value = account.SecondaryPINpadOwner;
command.Parameters[55].Value = account.SecondaryPINpadQuantity;
command.Parameters[56].Value = account.Printer;
command.Parameters[57].Value = account.PrinterOwner;
command.Parameters[58].Value = account.Description;
command.Parameters[59].Value = account.UploadedFiles;
command.Parameters[60].Value = account.ParentLead;
if (isUpdate)
{
command.Parameters[61].Value = account.AccountId;
}
int rowsAffected = command.ExecuteNonQuery();
}
}
if (account.NewFilePath != null && account.NewFilePath != string.Empty)
{
PGUploadedfileRepository upFile = new PGUploadedfileRepository();
upFile.InsertAccountFile(account.AccountId, account.NewFilePath);
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
