/*protected override void ValidateConnectionString(string connectionString, SqlProduct sqlProduct)
* {
* ValidateConnectionString(connectionString, sqlProduct) ;
* }*/
protected virtual ConnectionParameter ValidateConnection(string dbConnectionString, string duradosUserId, bool usesSsh = false, bool usesSsl = false, string sshRemoteHost = null, string sshUser = null, string sshPassword = null, string sshPrivateKey = null, int sshPort = 0, bool integratedSecurity = false)
{
/*** dbConnectionString example postgres://qvsrwdpkmcundl:Xq9Rv48RqvPjDJVlSJAXwqUZBB@ec2-54-204-31-13.compute-1.amazonaws.com:5432/d35mshek2k9rg1
* string serverName = "ec2-54-204-31-13.compute-1.amazonaws.com";
* string catalog = "d35mshek2k9rg1";
* string userName = "******";
* string password = "******";
*/
ConnectionParameter oConnectionParameter = new ConnectionParameter();
oConnectionParameter = ConnectionBuilder.GetConnectionParam(dbConnectionString);
string serverName = oConnectionParameter.serverName;
string catalog = oConnectionParameter.catalog;
string dbUserName = oConnectionParameter.dbUsername;
string dbPassword = oConnectionParameter.dbPassword;
int port = oConnectionParameter.port;
SqlProduct productId = oConnectionParameter.productId;
if (productId == SqlProduct.Postgre)
{
usesSsl = true;
}
ValidateConnectionString(integratedSecurity, serverName, catalog, dbUserName, dbPassword, usesSsh, usesSsl, duradosUserId, productId, sshRemoteHost, sshUser, sshPassword, sshPrivateKey, sshPort, port);
return(oConnectionParameter);
}
}// DeleteProduct()
public bool AddProduct(Product displayP)
{
hasError = false;
try
{
SqlProduct p = new SqlProduct(displayP);
int?newProductId = 0;
ContactDBEntities3 db = new ContactDBEntities3();
Contact e = new Contact()
{
FirstName = p.FirstName, LastName = p.LastName, EmailID = p.EmailID, State = p.State, City = p.City, PhNo = p.PhNo
};
// e.Vacations.Add(v);
db.Contacts.Add(e);
db.SaveChanges();
p.ContactID = 1;
displayP.ProductAdded2DB(p); //update corresponding Product ProductId using SqlProduct
}
catch (Exception ex)
{
errorMessage = "Add error, " + ex.Message;
hasError = true;
}
return(!hasError);
} //AddProduct()
protected void btnAdd_Click(object sender, EventArgs e)
{
if (IsValid)
{
SqlProduct.InsertParameters["ProductName"].DefaultValue =
txtProductName.Text;
SqlProduct.InsertParameters["UnitPrice"].DefaultValue =
txtUnitPrice.Text;
SqlProduct.InsertParameters["Package"].DefaultValue =
txtPackage.Text;
SqlProduct.InsertParameters["SupplierID"].DefaultValue =
txtSupplierID.Text;
SqlProduct.InsertParameters["IsDiscontinued"].DefaultValue =
Convert.ToString(cbIsdis.Checked);
try
{
SqlProduct.Insert();
txtProductName.Text = "";
txtUnitPrice.Text = "";
txtPackage.Text = "";
txtSupplierID.Text = "";
cbIsdis.Checked = false;
lblErrorMessage.Text = "Product was added successfully";
}
catch (Exception ex)
{
lblErrorMessage.Text = DatabaseErrorMessage(ex.Message);
}
}
}
protected override string GetSql(SqlProduct sqlProduct)
{
string sql = null;
switch (sqlProduct)
{
case SqlProduct.MySql:
sql = "SELECT Sum(data_length + index_length) FROM information_schema.tables GROUP BY table_schema; ";
break;
case SqlProduct.Postgre:
sql = "SELECT pg_database_size(current_DATABASE());";
break;
case SqlProduct.Oracle:
sql = "";
break;
default:
sql = "SELECT row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. * 1024 AS bigint) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id";
break;
}
return(sql);
}
protected void Load_Category(int catid)
{
SqlProduct.SelectCommand = "SELECT * FROM [dbo].[vw_nthing_CatProducts] where CategoryId = " + catid;
SqlProduct.DataBind();
CategoryItems.DataSource = SqlProduct;
CategoryItems.DataBind();
}
private SqlAccess GetSqlAccess(SqlProduct sqlProduct)
{
switch (sqlProduct)
{
case SqlProduct.MySql:
return(new MySqlAccess());
break;
case SqlProduct.Postgre:
return(new PostgreAccess());
break;
case SqlProduct.Oracle:
return(new OracleAccess());
break;
default:
return(new SqlAccess());
break;
}
}
public override NewDatabaseParameters GetNewParameters(SqlProduct product, string id)
{
NewDatabaseParameters parametes = base.GetNewParameters(product, id);
switch (product)
{
case SqlProduct.SqlServer:
parametes.DbName = null;
parametes.Engine = RDSEngin.sqlserver_ee.ToString().Replace('_', '-');
parametes.EngineVersion = GetSqlServerEnginVersion();
return(parametes);
case SqlProduct.MySql:
parametes.Engine = RDSEngin.MySQL.ToString();
parametes.EngineVersion = GetMySQLEnginVersion();
return(parametes);
case SqlProduct.Postgre:
parametes.Engine = RDSEngin.postgres.ToString();
parametes.EngineVersion = GetPostgreSQLEnginVersion();
return(parametes);
case SqlProduct.Oracle:
parametes.Engine = RDSEngin.oracle_ee.ToString().Replace('_', '-');
parametes.EngineVersion = GetOracleEnginVersion();
parametes.DbName = "ORCL";
return(parametes);
}
return(null);
}
protected void Load_Product(string pguid)
{
SqlProduct.SelectCommand = "SELECT * FROM [dbo].[nthing_Store_Product] where ProductGuid = '" + pguid + "'";
SqlProduct.DataBind();
ProductView.DataSource = SqlProduct;
ProductView.DataBind();
}
public virtual NewDatabaseParameters GetNewParameters(SqlProduct product, string id)
{
AppId = id ?? string.Empty;
NewDatabaseParameters parameters = GetNewBaseParameters();
parameters.Port = GetDefaultProductPort(product);
return(parameters);
}
public CartController(SqlProduct sqlProduct,
SqlCart sqlCart,
UserManager <AppUser> userManager
)
{
_sqlProduct = sqlProduct;
_sqlCart = sqlCart;
_userManager = userManager;
}
private void UpdateProductCache(int id, SqlProduct sqlProduct, IDbCommand command)
{
string[] apps = GetAppsName(id, command);
foreach (string appName in apps)
{
Maps.UpdateSqlProduct(appName, sqlProduct);
}
}
private System.Data.IDbConnection GetExternalAvailableInstanceConnection(SqlProduct product, out string server, out int port)
{
if (connectionStringCache == null || serverCache == null || portCache == -1)
{
connectionStringCache = GetExternalAvailableInstanceConnectionString(product, out serverCache, out portCache);
}
server = serverCache;
port = portCache;
return(GetConnection(product, connectionStringCache));
}
public ProductsController(SqlProduct proRepository, SqlProductLot proLotRepository, SqlColor colorRepository,
SqlBrand brandRepository, SqlTypeproduct typeRepository, SqlTradmark tradRepository, SqlOffer offerRepository)
{
this._proRepository = proRepository;
this._proLotRepository = proLotRepository;
this._colorRepository = colorRepository;
this._brandRepository = brandRepository;
this._typeRepository = typeRepository;
this._tradRepository = tradRepository;
this._offerRepository = offerRepository;
}
public ProductController(SqlProduct sqlProduct,
SqlCart sqlCart,
UserManager <AppUser> userManager,
IWebHostEnvironment webHostEnvironment
)
{
_sqlProduct = sqlProduct;
_sqlCart = sqlCart;
_userManager = userManager;
_webHostEnvironment = webHostEnvironment;
}
public AdminController(
SqlProduct sqlProduct,
SqlCart sqlCart,
SqlDiscount sqlDiscount,
UserManager <AppUser> userManager
)
{
_sqlProduct = sqlProduct;
_sqlCart = sqlCart;
_sqlDiscount = sqlDiscount;
_userManager = userManager;
}
protected void btnSave_Click(object sender, EventArgs e)
{
SqlProduct.UpdateParameters["Price"].DefaultValue = txtPrice.Text;
SqlProduct.UpdateParameters["Product_Id"].DefaultValue = dropProductName.SelectedValue;
SqlProduct.Update();
GridViewProduct.DataBind();
panelAddQuotation.Visible = false;
panelSaveQuotation.Visible = true;
txtPrice.Text = string.Empty;
dropProductName.SelectedIndex = 0;
}
private void UpdateProductCache(EditEventArgs e)
{
int id = Convert.ToInt32(e.PrimaryKey);
string sqlProductColumnName = "SqlProductId";
string sqlProductFieldName = e.View.GetFieldByColumnNames(sqlProductColumnName).Name;
SqlProduct sqlProduct = (SqlProduct)Enum.Parse(typeof(SqlProduct), e.Values.ContainsKey(sqlProductFieldName) ? e.Values[sqlProductFieldName].ToString() : SqlProduct.SqlServer.ToString());
SqlProduct prevSqlProduct = (SqlProduct)Enum.Parse(typeof(SqlProduct), e.PrevRow.Table.Columns.Contains(sqlProductColumnName) ? e.PrevRow[sqlProductColumnName].ToString() : SqlProduct.SqlServer.ToString());
if (prevSqlProduct != sqlProduct)
{
UpdateProductCache(id, sqlProduct, e.Command);
}
}
protected virtual ISqlTextBuilder GetSqlTextBuilder(SqlProduct sqlProduct)
{
switch (sqlProduct)
{
case SqlProduct.MySql:
return(new MySqlTextBuilder());
case SqlProduct.SqlServer:
return(new SqlTextBuilder());
default:
return(null);
}
}
public bool ForceHttps(ActionExecutingContext filterContext)
{
HttpRequestBase req = filterContext.HttpContext.Request;
HttpResponseBase res = filterContext.HttpContext.Response;
const int DefaultPort = 80;
const int SecuredPort = 443;
//Check if we're secure or not and if we're on the local box
//if (!req.IsSecureConnection && !req.IsLocal && Maps.UseSecureConnection)
var builder = new UriBuilder(req.Url);
bool redirect = false;
if (!req.IsSecureConnection && Maps.UseSecureConnection)
{
if (builder.Scheme != Uri.UriSchemeHttps || builder.Port != SecuredPort)
{
builder.Scheme = Uri.UriSchemeHttps;
builder.Port = SecuredPort;
redirect = true;
}
}
SqlProduct sqlProduct = Maps.GetCurrentSqlProduct();
if (Maps.SplitProducts && Maps.ProductsPort.ContainsKey(sqlProduct))
{
int port = Maps.ProductsPort[sqlProduct];
if (builder.Port != port)
{
builder.Port = port;
redirect = true;
}
}
if (redirect)
{
string url = builder.Uri.ToString();
if (req.IsLocal)
{
string appName = Maps.GetCurrentAppName();
url = url.Replace("localhost", appName + "." + Maps.Host, false);
}
res.Redirect(url);
return(true);
}
return(false);
}
} //GetProducts()
/*
* private string GetStringOrNull(SqlDataReader reader, string columnName)
* {
* return reader.IsDBNull(reader.GetOrdinal(columnName)) ? "" : (string)reader[columnName];
* }
*/
/*
* private const int prodStringLen = 50;
* public bool UpdateProduct(Product displayP)
* {
* SqlProduct p = new SqlProduct( displayP);
* hasError = false;
* SqlConnection con = new SqlConnection(conString);
* SqlCommand cmd = new SqlCommand("UpdateProduct", con);
* cmd.CommandType = CommandType.StoredProcedure;
* cmd.Parameters.Add("@ProductId", SqlDbType.Int, 4);
* cmd.Parameters["@ProductId"].Value = p.ProductId;
* cmd.Parameters.Add("@ModelNumber", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@ModelNumber"].Value = p.ModelNumber;
* cmd.Parameters.Add("@ModelName", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@ModelName"].Value = p.ModelName;
* cmd.Parameters.Add("@UnitCost", SqlDbType.Decimal);
* cmd.Parameters["@UnitCost"].Value = p.UnitCost;
* cmd.Parameters.Add("@Description", SqlDbType.VarChar, 200);
* cmd.Parameters["@Description"].Value = p.Description;
* cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@CategoryName"].Value = p.CategoryName;
* int rows = 0;
* try
* {
* con.Open();
* rows = cmd.ExecuteNonQuery();
* }
* catch (SqlException ex)
* {
* errorMessage = "Update SQL error, " + ex.Message;
* hasError = true;
* }
* catch (Exception ex)
* {
* errorMessage = "Update error, " + ex.Message;
* hasError = true;
* }
* finally
* {
* con.Close();
* }
* return (!hasError);
* } //UpdateProduct()
*/
public bool UpdateProduct(Product displayP)
{
try
{
SqlProduct p = new SqlProduct(displayP);
LinqDataContext dc = new LinqDataContext();
dc.UpdateProduct(p.ProductId, p.CategoryName, p.ModelNumber, p.ModelName, p.UnitCost, p.Description);
}
catch (Exception ex)
{
errorMessage = "Update error, " + ex.Message;
hasError = true;
}
return(!hasError);
} //UpdateProduct()
public NewsController
(
SqlProduct sqlProdut,
SqlCart sqlCart,
SqlNews sqlNews,
UserManager <AppUser> userManager,
IWebHostEnvironment webHostEnvironment
)
{
_sqlProduct = sqlProdut;
_sqlCart = sqlCart;
_sqlNews = sqlNews;
_userManager = userManager;
_webHostEnvironment = webHostEnvironment;
}
protected void btnSaveFinishedProduct_Click(object sender, EventArgs e)
{
SqlFinishedProduct.InsertParameters["Product_ID"].DefaultValue = strProductID;
SqlFinishedProduct.InsertParameters["Manufactured_Date"].DefaultValue = ManufacturedDateTextBox.Text.Trim();
SqlFinishedProduct.InsertParameters["Quantity"].DefaultValue = strQty;
SqlFinishedProduct.InsertParameters["Available_Quantity"].DefaultValue = strQty;
SqlFinishedProduct.Insert();
gvFinishedProduct.DataBind();
SqlProduct.UpdateParameters["WorkSchedule_ID"].DefaultValue = dropaddProduct.SelectedValue;
SqlProduct.UpdateParameters["Is_FinishedProduct_Updated"].DefaultValue = "true";
SqlProduct.Update();
PanelgvFinishedProduct.Visible = true;
dropaddProduct.SelectedIndex = -1;
ManufacturedDateTextBox.Text = String.Empty;
QuantityTextBox.Text = String.Empty;
}
protected void Load_Product(string pguid)
{
SqlProduct.SelectCommand = "SELECT * FROM [dbo].[nthing_Store_Product] where ProductGuid = '" + pguid + "'";
SqlProduct.DataBind();
ProductView.DataSource = SqlProduct;
ProductView.DataBind();
if (!Page.IsPostBack)
{
SqlConnection cnn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand("SELECT FORMAT ([AuctEndTime], 'MM dd yyyy HH:mm:ss') as AuctEnd FROM [dbo].[nthing_Store_Product] where ProductGuid = '" + pguid + "'", cnn);
cnn.Open();
string retval = cmd.ExecuteScalar().ToString();
cnn.Close();
HidendDateLab.Value = retval;
}
}
}// DeleteProduct()
/*
* public bool AddProduct(Product displayP)
* {
* SqlProduct p = new SqlProduct(displayP);
* hasError = false;
* SqlConnection con = new SqlConnection(conString);
* SqlCommand cmd = new SqlCommand("AddProduct", con);
* cmd.CommandType = CommandType.StoredProcedure;
* cmd.Parameters.Add("@ModelNumber", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@ModelNumber"].Value = p.ModelNumber;
* cmd.Parameters.Add("@ModelName", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@ModelName"].Value = p.ModelName;
* cmd.Parameters.Add("@UnitCost", SqlDbType.Decimal);
* cmd.Parameters["@UnitCost"].Value = p.UnitCost;
* cmd.Parameters.Add("@Description", SqlDbType.VarChar, 200);
* cmd.Parameters["@Description"].Value = p.Description;
* if (p.Description==null) cmd.Parameters["@Description"].Value = DBNull.Value;
* cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, prodStringLen);
* cmd.Parameters["@CategoryName"].Value = p.CategoryName;
* cmd.Parameters.Add("@ProductId", SqlDbType.Int, 4);
* cmd.Parameters["@ProductId"].Value = p.ProductId;
* cmd.Parameters["@ProductId"].Direction = ParameterDirection.Output;
* try
* {
* con.Open();
* int rows = cmd.ExecuteNonQuery(); //create the new product in DB
* p.ProductId = (int)cmd.Parameters["@ProductId"].Value; //set the returned ProductId in the SqlProduct object
* displayP.ProductAdded2DB(p); //update corresponding Product ProductId using SqlProduct
* }
* catch (SqlException ex)
* {
* errorMessage = "Add SQL error, " + ex.Message;
* hasError = true;
* }
* catch (Exception ex)
* {
* errorMessage = "ADD error, " + ex.Message;
* hasError = true;
* }
* finally
* {
* con.Close();
* }
* return !hasError;
* } //AddProduct()
*/
public bool AddProduct(Product displayP)
{
hasError = false;
try
{
SqlProduct p = new SqlProduct(displayP);
LinqDataContext dc = new LinqDataContext();
int? newProductId = 0;
dc.AddProduct(p.CategoryName, p.ModelNumber, p.ModelName, p.UnitCost, p.Description, ref newProductId);
p.ProductId = (int)newProductId;
displayP.ProductAdded2DB(p); //update corresponding Product ProductId using SqlProduct
}
catch (Exception ex)
{
errorMessage = "Add error, " + ex.Message;
hasError = true;
}
return(!hasError);
} //AddProduct()
protected virtual IDataParameter GetParameter(SqlProduct sqlProduct, string name, object value)
{
if (sqlProduct == SqlProduct.Oracle)
{
return(new Oracle.ManagedDataAccess.Client.OracleParameter(name, value));
}
else if (sqlProduct == SqlProduct.Postgre)
{
return(new Npgsql.NpgsqlParameter(name, value));
}
else if (sqlProduct == SqlProduct.MySql)
{
return(new MySql.Data.MySqlClient.MySqlParameter(name, value));
}
else
{
return(new System.Data.SqlClient.SqlParameter(name, value));
}
}
private SqlSchema GetSqlSchema(SqlProduct sqlProduct)
{
if (sqlProduct == SqlProduct.MySql)
{
return(new MySqlSchema());
}
else if (sqlProduct == SqlProduct.Postgre)
{
return(new PostgreSchema());
}
else if (sqlProduct == SqlProduct.Oracle)
{
return(new OracleSchema());
}
else
{
return(new SqlSchema());
}
}
protected virtual SqlSchema GetSchema(SqlProduct sqlProduct)
{
switch (sqlProduct)
{
case SqlProduct.MySql:
return(new MySqlSchema());
case SqlProduct.SqlServer:
return(new SqlSchema());
case SqlProduct.Postgre:
return(new PostgreSchema());
case SqlProduct.Oracle:
return(new OracleSchema());
default:
return(null);
}
}
protected virtual IDbConnection GetConnection(SqlProduct sqlProduct, string connectionString)
{
if (sqlProduct == SqlProduct.Oracle)
{
return(new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString));
}
else if (sqlProduct == SqlProduct.Postgre)
{
Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.ValidateRemoteCertificateCallback += new Npgsql.ValidateRemoteCertificateCallback(connection_ValidateRemoteCertificateCallback);
return(connection);
}
else if (sqlProduct == SqlProduct.MySql)
{
return(new MySql.Data.MySqlClient.MySqlConnection(connectionString));
}
else
{
return(new System.Data.SqlClient.SqlConnection(connectionString));
}
}
} //GetProducts()
public bool UpdateProduct(Product displayP, int contactId)
{
try
{
SqlProduct p1 = new SqlProduct(displayP);
ContactDBEntities3 db = new ContactDBEntities3();
Contact con = db.Contacts.SingleOrDefault(p => p.ContactID == contactId);
con.FirstName = p1.FirstName;
con.LastName = p1.LastName;
con.EmailID = p1.EmailID;
con.PhNo = p1.PhNo;
con.State = p1.State;
con.City = p1.City;
db.SaveChanges();
}
catch (Exception ex)
{
errorMessage = "Update error, " + ex.Message;
hasError = true;
}
return(!hasError);
} //UpdateProduct()
public static string GetConnectionStringSchema(MapDataSet.durados_SqlConnectionRow sqlConnectionRow)
{
bool usesSsh = !sqlConnectionRow.IsSshUsesNull() && sqlConnectionRow.SshUses;
bool usesSsl = !sqlConnectionRow.IsSslUsesNull() && sqlConnectionRow.SslUses;
string localPort = sqlConnectionRow.ProductPort;
SqlProduct sqlProductId = (SqlProduct)sqlConnectionRow.SqlProductId;
switch ((SqlProduct)sqlProductId)
{
case SqlProduct.MySql:
return(Durados.DataAccess.MySqlAccess.GetConnectionStringSchema(usesSsh));
case SqlProduct.Postgre:
return(Durados.DataAccess.PostgreAccess.GetConnectionStringSchema(usesSsl));
case SqlProduct.Oracle:
return(Durados.DataAccess.OracleAccess.GetConnectionStringSchema());
default:
return("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Integrated Security=False;");
}
}
