public string GetProductGroupByProductGroupId(string productGroupId)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand("SELECT ProductGroupId, Title FROM ProductGroups WHERE ProductGroupId = ProductGroupId ", connection);
command.Parameters.Add(new SqlParameter("ProductId", productGroupId));
SqlDataReader reader = command.ExecuteReader();
List <NewProductGroup> products = new List <NewProductGroup>();
while (reader.Read())
{
if (productGroupId == reader.GetGuid(0).ToString())
{
NewProductGroup xx = new NewProductGroup()
{
productGroupId = reader.GetGuid(0),
title = reader.GetString(1),
};
products.Add(xx);
}
}
connection.Close();
return(JsonConvert.SerializeObject(products, Formatting.Indented));
}
public string Save(NewProductGroup x)
{
try {
DB.CreateDataBase(G.db.productGroups);
string sql = null;
if (string.IsNullOrEmpty(x.id))
{
x.id = Guid.NewGuid().ToString();
sql = string.Format(@"INSERT INTO ProductGroups VALUES ('{0}', '{1}')", x.id, x.title);
}
else
{
sql = string.Format(@"UPDATE ProductGroups SET title = '{1}' WHERE id = '{0}'", x.id, x.title);
}
using (var connection = new SQLiteConnection("Data Source=" + DB.GetDataBasePath(G.dataBase))) {
connection.Open();
using (var command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
connection.Close();
}
return(JsonConvert.SerializeObject(LoadData(), Formatting.None));
} catch (Exception e) {
return(JsonConvert.SerializeObject(e.Message, Formatting.None));
}
}
public string GetProductGroups()
{
try {
List <NewProductGroup> xx = new List <NewProductGroup>();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT ProductGroupId, Title FROM ProductGroups", connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewProductGroup x = new NewProductGroup();
x.productGroupId = reader.GetGuid(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
if (!string.IsNullOrEmpty(x.title))
{
xx.Add(x);
}
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(xx, Formatting.Indented));
} catch (Exception e) {
return(e.Message);
}
}
public string Init()
{
try {
NewProductGroup x = new NewProductGroup();
x.id = null;
x.title = null;
return(JsonConvert.SerializeObject(x, Formatting.Indented));
} catch (Exception e) {
return(JsonConvert.SerializeObject(e.Message, Formatting.Indented));
}
}
public string Delete(NewProductGroup x)
{
try {
string sql = string.Format(" DELETE FROM ProductGroups WHERE id = '{0}'", x.id);
using (var connection = new SQLiteConnection("Data Source=" + DB.GetDataBasePath(G.dataBase))) {
connection.Open();
using (var command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
connection.Close();
}
return(JsonConvert.SerializeObject(LoadData(), Formatting.None));
} catch (Exception e) {
return(JsonConvert.SerializeObject(e.Message, Formatting.None));
}
}
public string Save(NewProductGroup productGroup)
{
try {
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
connection.Open();
string sql = @"INSERT INTO ProductGroups VALUES
(@ProductGroupId, @Title)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add(new SqlParameter("ProductGroupId", productGroup.productGroupId));
command.Parameters.Add(new SqlParameter("Title", productGroup.title));
command.ExecuteNonQuery();
connection.Close();
return(string.Format(@"Product saved Successfully!"));
} catch (Exception e) {
return(string.Format(@"Error! Product not saved. ({0})", e.Message));
}
}
public string DeleteProductGroup(NewProductGroup productGroups)
{
try {
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
connection.Open();
string sql = @"
DELETE * FROM ProductGroup WHERE
WHERE [ProductGroupId] = @ProductGroupId";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add(new SqlParameter("ProductGroupId", productGroups.productGroupId));
command.ExecuteNonQuery();
connection.Close();
return(string.Format(@"Product group deleted Successfully!"));
} catch (Exception e) {
return(string.Format(@"Error! Product group not deleted. ({0})", e.Message));
}
}
public string Update(NewProductGroup productGroups)
{
try {
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
connection.Open();
string sql = @"
UPDATE ProductGroups SET
[Title] = @Title
WHERE [ProductGroupId] = @ProductGroupId";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add(new SqlParameter("ProductGroupId", productGroups.productGroupId));
command.Parameters.Add(new SqlParameter("Title", productGroups.title));
command.ExecuteNonQuery();
connection.Close();
return(string.Format(@"Product Group updated Successfully!"));
} catch (Exception e) {
return(string.Format(@"Error! Product Group not updated. ({0})", e.Message));
}
}
public List <NewProductGroup> LoadData()
{
DB.CreateDataBase(G.db.productGroups);
string sql = "SELECT id, title FROM ProductGroups";
List <NewProductGroup> xx = new List <NewProductGroup>();
using (var connection = new SQLiteConnection("Data Source=" + DB.GetDataBasePath(G.dataBase))) {
connection.Open();
using (var command = new SQLiteCommand(sql, connection)) {
using (var reader = command.ExecuteReader()) {
xx = new List <NewProductGroup>();
while (reader.Read())
{
NewProductGroup x = new NewProductGroup();
x.id = G.ReadS(reader, 0);
x.title = G.ReadS(reader, 1);
xx.Add(x);
}
}
}
connection.Close();
}
return(xx);
}
