public static int InsertData(string name, string address, string employee, string total_repository_count, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[repository] ([name] ,[address] ,[employee] ,[total_repository_count] ,[description]) VALUES ({0} ,{1} ,{2},{3} ,{4})";
return(db.Database.ExecuteSqlCommand(query, name, address, employee, total_repository_count, description));
}
public static int UpdateData(int id, string name, string address, string employee, string total_repository_count, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "UPDATE [dbo].[repository] SET [name]={1} ,[address]={2} ,[employee]={3} ,[total_repository_count]={4} ,[description]={5} where id={0}";
return(db.Database.ExecuteSqlCommand(query, id, name, address, employee, total_repository_count, description));
}
public static List <repository> SelectData(int?id)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM [dbo].[repository] WHERE Id = {0}";
return(db.Database.SqlQuery <repository>(query, id).ToList <repository>());
}
public static int UpdateData(int id, int repository_code, int item_category_id, string name, int count, string buy_price, string sell_price, string buy_date, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "UPDATE [dbo].[item] SET [repository_code]={1} ,[item_category_id]={2} ,[name]={3} ,[count]={4} ,[buy_price]={5},[sell_price]={6},[buy_date]={7},[description]={8} where id={0}";
return(db.Database.ExecuteSqlCommand(query, id, repository_code, item_category_id, name, count, buy_price, sell_price, buy_date, description));
}
public static int InsertData(string name, string phone, string address, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[provider] ([name] ,[phone] ,[address] , [description]) VALUES ({0} ,{1}, {2},{3})";
return(db.Database.ExecuteSqlCommand(query, name, phone, address, description));
}
public static int InsertData(string name, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[shop_category] ([name] ,[description]) VALUES ({0} ,{1})";
return(db.Database.ExecuteSqlCommand(query, name, description));
}
public static List <shop_category> SelectAllData()
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM [dbo].[shop_category]";
return(db.Database.SqlQuery <shop_category>(query).ToList <shop_category>());
}
public static int UpdateData(int id, string name, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "UPDATE [dbo].[shop_category] SET [name]={1} ,[description]={2} where id={0}";
return(db.Database.ExecuteSqlCommand(query, id, name, description));
}
public static int UpdateData(int id, int customer_id, string item_list, string item_count, string date, string price, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "UPDATE [dbo].[order] SET [customer_id]={1} ,[item_list]={2} ,[item_count]={3},[date]={4},[price]={5},[description]={6} where id={0}";
return(db.Database.ExecuteSqlCommand(query, id, customer_id, item_list, item_count, date, price, description));
}
public static int UpdateData(int id, string name, string phone, string address, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "UPDATE [dbo].[provider] SET [name]={1} ,[phone]={2} ,[address]={3},[description]={4} where id={0}";
return(db.Database.ExecuteSqlCommand(query, id, name, phone, address, description));
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
SqlHandler.Dispose();
}
public static List <customer> SelectAllData()
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM customer";
return(db.Database.SqlQuery <customer>(query).ToList <customer>());
}
public static int InsertData(string fname, string lname, string age, string sex, string mobile, string address, string email, string credit, string created_date, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[customer] ([query] ,[fname] ,[lname] ,[age] ,[sex], [mobile], [address], [email],[credit],[created_date],[description] ) VALUES (@query ,@fname ,'@lname', @age ,@sex , '@mobile', '@address' ,'@email','@credit','@created_date','@description')";
return(db.Database.ExecuteSqlCommand(query, fname, lname, age, sex, mobile, address, email, credit, created_date, description));
}
public ApplicationState()
{
//
// TODO: Add constructor logic here
//
var connectionString = ConfigurationManager.ConnectionStrings["msAccessConnectionString"];
Handler = _handler = new SqlHandler(connectionString.ProviderName, connectionString.ConnectionString);
}
public static List <customer> SelectData(int?id)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM customer WHERE Id = @id";
return(db.Database.SqlQuery <customer>(query, id).ToList <customer>());
}
public static int DeleteData(int id)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "DELETE FROM [dbo].[item] where id={0}";
return(db.Database.ExecuteSqlCommand(query, id));
}
public static int InsertData(int customer_id, string item_list, string item_count, string date, string price, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[order] ([customer_id] ,[item_list] ,[item_count] , [date] ,[price],[description] ) VALUES ({0} ,{1}, {2},{3},{4},{5})";
return(db.Database.ExecuteSqlCommand(query, customer_id, item_list, item_count, date, price, description));
}
public static int InsertData(int repository_code, int item_category_id, string name, int count, string buy_price, string sell_price, string buy_date, string description)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "INSERT INTO [dbo].[item] ([repository_code] ,[item_category_id] ,[name] ,[count] ,[buy_price], [sell_price], [buy_date], [description] ) VALUES ({0},{1},{2},{3},{4},{5},{6},{7})";
return(db.Database.ExecuteSqlCommand(query, repository_code, item_category_id, name, count, buy_price, sell_price, buy_date, description));
}
public static List <item> SelectAllData(int repository_code, int item_category_id)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM item WHERE repository_code = {0} and item_category_id ={1}";
return(db.Database.SqlQuery <item>(query, repository_code, item_category_id).ToList <item>());
}
public static List <item> SelectData(int id)
{
SqlHandler sq = new SqlHandler();
DbContext db = new DbContext(sq.ConnectionString());
string query = "SELECT * FROM item WHERE Id = {0}";
return(db.Database.SqlQuery <item>(query, id).ToList <item>());
}
/// <summary>
/// Loads all the npc informations and spawns.
/// </summary>
/// <returns>Returns true if the the infos/spawns were loaded.</returns>
public static bool LoadNPCInfo()
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("\tLoading NPCs...");
using (var sql = new SqlHandler(Program.Config.ReadString("GameConnectionString")))
{
using (var cmd = new SqlCommandBuilder(sql, SqlCommandType.SELECT, false))
{
cmd.Finish("DB_NPCInfo");
}
while (sql.Read())
{
Entities.NPC npc = new ProjectX_V3_Game.Entities.NPC();
npc.EntityUID = sql.ReadUInt32("NPCID");
ushort mapid = sql.ReadUInt16("MapID");
if (mapid == 0)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Failed to load npcs. [MAPID]");
Console.ResetColor();
return(false);
}
Maps.Map map;
Core.Kernel.Maps.TrySelect(mapid, out map);
npc.Map = map;
if (!npc.Map.EnterMap(npc))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Failed to load npcs. [MAP]");
Console.ResetColor();
return(false);
}
npc.Mesh = sql.ReadUInt16("Mesh");
npc.Flag = sql.ReadUInt32("Flag");
npc.Name = sql.ReadString("NPCName");
npc.X = sql.ReadUInt16("X");
npc.Y = sql.ReadUInt16("Y");
npc.NPCType = (Enums.NPCType)Enum.Parse(typeof(Enums.NPCType), sql.ReadString("Type"));
npc.Avatar = sql.ReadByte("Avatar");
if (!Core.Kernel.NPCs.TryAdd(npc.EntityUID, npc))
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Failed to load npcs. [ADD]");
Console.ResetColor();
return(false);
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("\tLoaded {0} NPCs...", Core.Kernel.NPCs.Count);
return(true);
}
private static async Task <int> upfileInsert(SqlHandler db, int upfilecat_r, string ext, string originname, string upfile)
{
var rs = await db.execAsync("upfile/add", "upfilecat_rowid", upfilecat_r + "", "ext", ext, "originname", originname, "upfile", upfile);
if (!rs.noRecord && !rs.valiError && rs.result == 1)
{
return(rs.insertId);
}
return(0);
}
public static int deletePaymentVoucher(int id)
{
SqlHandler sql = new SqlHandler();
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("@accountId", id));
int i = sql.ExecuteNonQueryI("Usp_DeletePaymentVoucher", parameter);
return(i);
}
public Property GetByPropertyName(string propertyName, string typeName, int entityId)
{
var property = new SqlHandler(_context)
.GetTypePropertiesByName(typeName)
.FirstOrDefault(p => p.EntityId == entityId &&
p.Key == propertyName) ?? throw new ArgumentException();
property.DisplayKey = GetAttributeValue(typeName);
return(property);
}
public VocabularyBuilder()
{
vocabulary = new Vocabulary.Vocabulary();
sqlHandler = new SqlHandler();
var query = "SELECT Word,Importance FROM [Vocabulary]";
var words = sqlHandler.Select(query);
vocabulary.Words.AddRange(words);
}
private void populateListOfCompanies()
{
SQLiteDataReader sQLiteDataReader = fetchCompaniesFromDB();
while (sQLiteDataReader.Read() && sQLiteDataReader != null)
{
ListOfCompanies.Add(createCompanyObjectFromRowInDB(sQLiteDataReader));
}
SqlHandler.closeConnectionToDataBase();
}
private int deleteCompanyFromDB(int companyId)
{
string sql = "UPDATE COMPANY SET Deleted=1 WHERE CompanyId=@companyId";
SqlParameter[] sqlParameters = new SqlParameter[] {
new SqlParameter("companyId", companyId.ToString(), DbType.Int32)
};
return(SqlHandler.executionQuery(sql, sqlParameters));
}
public static int addAcountDetails(AccountDetails account)
{
using (var Conn = new SqlConnection(_connectionString))
{
SqlHandler sqlH = new SqlHandler();
SqlTransaction trans = null;
try
{
Conn.Open();
trans = Conn.BeginTransaction();
List <KeyValuePair <string, object> > parameter = new List <KeyValuePair <string, object> >();
parameter.Add(new KeyValuePair <string, object>("@accountTypeId", account.accountTypeId)); //1
parameter.Add(new KeyValuePair <string, object>("@accountGroupId", account.accountGroupId)); //2
parameter.Add(new KeyValuePair <string, object>("@accountSubGroupId", account.accountSubGroupId)); //2
parameter.Add(new KeyValuePair <string, object>("@accountName", account.accountName)); //3
parameter.Add(new KeyValuePair <string, object>("@isCustomerSupplier", account.isCustomerSupplier)); //4
parameter.Add(new KeyValuePair <string, object>("@isbank", account.isActive)); //5
parameter.Add(new KeyValuePair <string, object>("@accountNo", account.accountNo)); //6
parameter.Add(new KeyValuePair <string, object>("@openigBalanace", account.openigBalanace)); //7
parameter.Add(new KeyValuePair <string, object>("@isCreditDebit", account.isCreditDebit)); //8
if (account.isCreditDebit == "जमा रक्कम")
{
account.crAmount = 0;
account.drAmount = account.openigBalanace;
}
if (account.isCreditDebit == "नावे रक्कम")
{
account.crAmount = account.openigBalanace;
account.drAmount = 0;
}
parameter.Add(new KeyValuePair <string, object>("@crAmount", account.crAmount)); //9
parameter.Add(new KeyValuePair <string, object>("@drAmount", account.drAmount)); //10
parameter.Add(new KeyValuePair <string, object>("@addedBy", 1)); //11
parameter.Add(new KeyValuePair <string, object>("@addedOn", account.addedOn)); //12
parameter.Add(new KeyValuePair <string, object>("@accountGSTNO", account.accountGSTNO)); //12
parameter.Add(new KeyValuePair <string, object>("@MobileNo", account.MobileNo)); //12
parameter.Add(new KeyValuePair <string, object>("@FinancialYearID", account.FinancialYearID)); //12
// parameter.Add(new KeyValuePair<string, object>("@accountGSTNO", account.accountGSTNO));//13
int i = sqlH.ExecuteNonQueryTM("[dbo].[Usp_addAccountDetails]", parameter, Conn, trans);
trans.Commit();
return(i);
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally { Conn.Close(); }
}
}
/// <summary>
/// Updates computer from the DB
/// </summary>
/// <param name="computer"></param>
private static void UpdateDB(object computer)
{
try
{
SqlHandler.UpdateComputer((Computer)computer);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Deletes computer from the DB
/// </summary>
/// <param name="computer"></param>
private static void DeleteDB(object computer)
{
try
{
SqlHandler.DeleteComputer(((Computer)computer).ID);
}
catch (Exception)
{
throw;
}
}
