protected void Page_Load(object sender, EventArgs e)
{
try
{
//SQL Create Table
//create table customer(id int identity(1, 1) primary key, Cname varchar(max), Caddress varchar(max))
//SQL Create Store procedure
//create proc[dbo].[sp_insertcustomer]
//@Cname varchar(max), @Caddress varchar(max)
//as
//begin
//insert into customer values(@Cname, @Caddress)
//end
Customer entity = new Customer();
entity.Cname = "testname";
entity.Caddress = "testaddress";
using (DbCon dbcon = new DbCon())
{
dbcon._iCustomerRepository.Add(entity, "sp_insertcustomer");
}
}
catch (Exception)
{
throw;
}
}
public void TestAddBook()
{
int res = 0;
try
{
Books b = new Books();
//Add_Books a =null ;
DbCon d = new DbCon(dbpath);
b.AuthorAdd = "Cuttack"; b.AuthorName = "Sudipta"; b.BookID = "15"; b.BookName = "Welcome to C#";
b.BookPafes = 150; b.BookPrice = 45.25; b.CatagoryName = "Computer"; b.PublisherAdd = "Kolkata";
b.PublisherName = "BpB"; b.Stock = 15;
if (b.BookIDExists(b.BookID, dbpath) == false)
{
res = d.SaveBooks(b);
}
else
{
res = 0;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Assert.AreEqual(res > 0, true);
}
// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
services.AddAuthentication("Bearer")
.AddIdentityServerAuthentication(options =>
{
options.Authority = "https://identity.buyingagentapp.com";
options.RequireHttpsMetadata = false;
options.ApiName = "buyingAgentAPI";
});
services.AddMvc()
.AddMvcOptions(o => o.OutputFormatters.Add(
new XmlDataContractSerializerOutputFormatter()));
//var connectionString = Configuration["connectionString:BuyingAgentDBConnectionString"];
var connectionString = DbCon.GetRdsConnectionString(Configuration);
services.AddDbContext <BuyingAgentContext>(o => o.UseSqlServer(connectionString));
services.AddScoped <IBuyingAgentDelete, BuyingAgentDelete>();
services.AddScoped <IBuyingAgentCheckIfSaved, BuyingAgentCheckIfSaved>();
services.AddScoped <IBuyingAgentCheckIfExisted, BuyingAgentCheckIfExisted>();
services.AddScoped <IBuyingAgentSave, BuyingAgentSave>();
services.AddScoped <IBuyingAgentReports, BuyingAgentReports>();
services.AddScoped <IBuyingAgentRead, BuyingAgentRead>();
services.AddScoped <EnumToDicConverter>();
services.AddScoped <DbCon>();
//enabled CORS
services.AddCors();
}
public bool Exists()
{
SqlProvider.FormatExists();
SetSql();
var result = DbCon.QuerySingle <int>(SqlProvider.SqlString, SqlProvider.Params) == 1;
return(result);
}
public async Task <bool> ExistsAsync()
{
SqlProvider.FormatExists();
SetSql();
var result = await DbCon.QuerySingleAsync <int>(SqlProvider.SqlString, SqlProvider.Params) == 1;
return(result);
}
public TResult Sum <TResult>(Expression <Func <T, TResult> > sumExpression)
{
SqlProvider.FormatSum(sumExpression);
SetSql();
var result = DbCon.QuerySingle <TResult>(SqlProvider.SqlString, SqlProvider.Params);
return(result);
}
public async Task <TResult> SumAsync <TResult>(Expression <Func <T, TResult> > sumExpression)
{
SqlProvider.FormatSum(sumExpression);
SetSql();
var result = await DbCon.QuerySingleAsync <TResult>(SqlProvider.SqlString, SqlProvider.Params);
return(result);
}
public static List <UserProfiles> GetProfileForUser(int idUser)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [V_USERS_PROFILES] WHERE [USR_FK]=@ID ");
return(DbCon.Query <UserProfiles>(sql.ToString(), new { ID = idUser }).ToList());
}
public async Task <int> CountAsync()
{
SqlProvider.FormatCount();
SetSql();
var result = await DbCon.QuerySingleAsync <int>(SqlProvider.SqlString, SqlProvider.Params);
return(result);
}
public int Count()
{
SqlProvider.FormatCount();
SetSql();
var result = DbCon.QuerySingle <int>(SqlProvider.SqlString, SqlProvider.Params);
return(result);
}
private void button2_Click(object sender, EventArgs e)
{
MySqlConnection conn = DbCon.GetDBConnection();
conn.Open();
MessageBox.Show(conn.State.ToString());
conn.Close();
MessageBox.Show(conn.State.ToString());
}
public static Functionalities GetSingle(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [FUNCTIONALITIES] ");
sql.Append("WHERE [FUN_PK] = @FUN_PK ");
return(DbCon.Query <Functionalities>(sql.ToString(), new { FUN_PK = id }).SingleOrDefault());
}
public Person Get(int id)
{
string sql = "SELECT * FROM PersonInfo WHERE Id = @Id";
var _params = new DynamicParameters();
_params.Add("@Id", id);
var data = DbCon.QueryFirstOrDefault <Person>(sql, _params);
return(data);
}
public int Delete(int id)
{
string sql = "DELETE FROM PersonInfo WHERE Id = @Id";
var _params = new DynamicParameters();
_params.Add("@Id", id);
int i = DbCon.Execute(sql, _params, commandType: CommandType.Text);
return(i);
}
public static Courses GetSingle(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [COURSES] ");
sql.Append("WHERE [COU_PK] = @COU_PK ");
return(DbCon.Query <Courses>(sql.ToString(), new { COU_PK = id }).SingleOrDefault());
}
public static List <UserProfiles> GetAllUsersForProfile(int idProfile)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [V_USER_PROFILES] WHERE [PRO_FK]=@ID ");
List <UserProfiles> ret = DbCon.Query <UserProfiles>(sql.ToString(), new { ID = idProfile }).ToList();
return(ret);
}
public static Profiles GetSingle(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [PROFILES] ");
sql.Append("WHERE [PRO_PK] = @PRO_PK ");
return(DbCon.Query <Profiles>(sql.ToString(), new { PRO_PK = id }).SingleOrDefault());
}
public static Users GetSingle(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [USERS] ");
sql.Append("WHERE [USR_PK] = @USR_PK ");
return(DbCon.Query <Users>(sql.ToString(), new { USR_PK = id }).SingleOrDefault());
}
public static Parameters GetSingle(int id)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [PARAMETERS] ");
sql.Append("WHERE [ID] = @ID ");
return(DbCon.Query <Parameters>(sql.ToString(), new { ID = id }).SingleOrDefault());
}
public static Int32 Insert(Courses bean)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO [COURSES] ");
sql.Append("([TITLE], [PERIOD]) ");
sql.Append("VALUES(@TITLE, @PERIOD); SELECT CAST(scope_identity() AS int)");
int ret = DbCon.Query <Int32>(sql.ToString(), bean).FirstOrDefault();
return(ret);
}
private void btnTestConnSource_Click(object sender, EventArgs e)
{
try
{
using (DbCon db = CreateDbConn(false))
btnTestConnSource.Text = "Test (ok)";
}
catch (Exception)
{
btnTestConnSource.Text = "Test (error)";
}
}
public Client RecupProduit(int clientId)
{
Client prod = new Client();
//traitement code
//creer l'objet de connexion
MySqlConnection conn = DbCon.GetDBConnection();
conn.Open();
// ouvrir la connection
//preparer la commande
string sql = "SELECT nom, prenom, numero, email FROM client where(id_client=" + clientId + ") ";
// creer un objet command
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
//
try
{
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
//prod.ProduitId = Convert.ToInt32(reader.GetString(1));
prod._Nom = reader.GetString(0);
prod._Prenom = reader.GetString(1);
prod._Numero = reader.GetInt32(2);
//prod.Prix = reader.GetDecimal(2);
//prod.Prix = Convert.ToDecimal(reader.GetString(2));
}
}
}
return(prod);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public Depot RecupProduit(int DepotId)
{
Depot Dep = new Depot();
//traitement code
//creer l'objet de connexion
MySqlConnection conn = DbCon.GetDBConnection();
conn.Open();
// ouvrir la connection
//preparer la commande
string sql = "SELECT Quantite, Lieu FROM depot where(id_depot=" + DepotId + ") ";
// creer un objet command
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
//
try
{
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
//prod.ProduitId = Convert.ToInt32(reader.GetString(1));
Dep._Quantite = reader.GetInt32(0);
Dep._Lieu = reader.GetString(1);
//prod.Prix = reader.GetDecimal(2);
//prod.Prix = Convert.ToDecimal(reader.GetString(2));
}
}
}
return(Dep);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public PageList <T> PageList(int pageIndex, int pageSize)
{
SqlProvider.FormatToPageList <T>(pageIndex, pageSize);
using (var queryResult = DbCon.QueryMultiple(SqlProvider.SqlString, SqlProvider.Params, DbTransaction))
{
var pageTotal = queryResult.ReadFirst <int>();
var itemList = queryResult.Read <T>().ToList();
return(new PageList <T>(pageIndex, pageSize, pageTotal, itemList));
}
}
public static void ErrorSave(string path, string errLog)
{
DbCon db = new DbCon();
List <ErrorLogs> ErrorLogs = new List <ErrorLogs>()
{
new ErrorLogs {
FILE_NAME = path, ERROR_LOG = errLog
}
};
EFBatchOperation.For(db, db.ErrorLogs).InsertAll(ErrorLogs);
ErrorLogs.Clear();
}
public static List <Functionalities> GetAll(string sortString = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [FUNCTIONALITIES] ");
if (sortString != null)
{
sql.Append("ORDER BY " + sortString);
}
List <Functionalities> ret = DbCon.Query <Functionalities>(sql.ToString()).ToList();
return(ret);
}
public static List <Courses> GetAll(string title = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [COURSES] ");
if (title.Equals(""))
{
sql.Append("WHERE UPPER(TITLE) LIKE UPPER(CONCAT('%',@TITLE,'%'))");
}
List <Courses> ret = DbCon.Query <Courses>(sql.ToString(), new { TITLE = title }).ToList();
return(ret);
}
public static List <UserProfiles> GetAll(string sortString = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [USERPROFILES] ");
if (sortString != null)
{
sql.Append("ORDER BY " + sortString);
}
List <UserProfiles> ret = DbCon.Query <UserProfiles>(sql.ToString()).ToList();
return(ret);
}
public static List <Parameters> GetAll(string sortString = null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * ");
sql.Append("FROM [PARAMETERS] ");
if (sortString != null)
{
sql.Append("ORDER BY " + sortString);
}
List <Parameters> ret = DbCon.Query <Parameters>(sql.ToString()).ToList();
return(ret);
}
public static bool Insert(UserProfiles bean)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO [USERPROFILES] ");
sql.Append("([USER_FK], [PRO_FK])");
sql.Append("VALUES( @USER_FK, @PRO_FK)");
int rowsAffected = DbCon.Execute(sql.ToString(), bean);
if (rowsAffected > 0)
{
return(true);
}
return(false);
}
public void Insert()
{
Database.SetInitializer(new DropCreateDatabaseIfModelChanges<DbCon>());
using (var context = new DbCon())
{
var Clients = new List<Client> {
new Client {Client_Id=1 ,Client_Name="Lab021",Client_Ceo="Leesangbong1030", Client_Phone="010-111-1111",Client_Fax="051-111-1111",Client_Address="부산시 중구 조선빌딩 501호",Client_PostNumber="614-051",Client_Remark="remark1",Client_Email="*****@*****.**",Client_HomePage="http://lab021.co.kr" },
new Client {Client_Id=2 ,Client_Name="busan",Client_Ceo="Jungdoyoung1030", Client_Phone="010-222-2222",Client_Fax="051-222-2222",Client_Address="부산시 진구 양정동",Client_PostNumber="614-052",Client_Remark="remark2",Client_Email="*****@*****.**",Client_HomePage="http://wjd7364.co.kr" },
new Client {Client_Id=3 ,Client_Name="unsan",Client_Ceo="haunsan1030", Client_Phone="010-333-3333",Client_Fax="051-333-3333",Client_Address="부산시 서구 팔당동",Client_PostNumber="614-053",Client_Remark="remark3",Client_Email="*****@*****.**",Client_HomePage="http://Unsan.co.kr" },
};
//Clients.ForEach(s => context.Clients.Add(s));
//context.SaveChanges();
}
}
