public bool Insert(Category category)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM Category c WHERE c.Code='" + category.Code + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string updateQuery = @"UPDATE Category SET Name='" + category.Name
+ "' WHERE Category.Code='" + category.Code + "'";
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string insertQuery = @"INSERT INTO Category (Code,Name)
VALUES ('" + category.Code + "','" + category.Name + "')";
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
return true;
}
return false;
}
}
static void Main(string[] args)
{
// TODO : finish when ADO.NET gets useful if ever does
string connectionString = ConfigurationManager.ConnectionStrings["HackCompany"].ConnectionString;
DbConnection dbConnection = new DbConnection(connectionString);
Category category = new Category("TST","NETEST");
dbConnection.Insert(category);
//var c1 = new Product(1,"prod 1",100,"SFT");
//dbConnection.Insert(c1);
//string query = @"SELECT TOP 10 *
// FROM HackCompany.dbo.Customer c
// WHERE c.Id=2";
//using (var connection = new SqlConnection(connectionString))
//{
// connection.Open();
// var command = new SqlCommand(query, connection);
// using (var reader = command.ExecuteReader())
// {
// while (reader.Read())
// {
// string name = StringOrNull(reader["Name"]);
// string email = StringOrNull(reader["Email"]);
// Console.WriteLine(string.Format(name + email));
// }
// }
//}
}
public Category GetCategoryByID(int id)
{
Category categ = new Category();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = @"SELECT*
FROM CATEGORY
WHERE CATEGORYID=" + id;
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
categ.Name = (string)reader["NAME"];
categ.LETTERCODE = (string)reader["LETTERCODE"];
categ.CategoryID = (int)reader["CATEGORYID"];
}
}
return categ;
}
public List<Category> ListCategories()
{
List<Category> categories = new List<Category>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT*
FROM CATEGORY";
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Category temp = new Category();
temp.Name = (string)reader["NAME"];
temp.LETTERCODE = (string)reader["LETTERCODE"];
temp.CategoryID = (int)reader["CATEGORYID"];
categories.Add(temp);
}
}
return categories;
}
public void CreateOrUpdateCategory(Category category)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
int counter = 0;
string query = @"SELECT COUNT(*)
FROM CATEGORY
WHERE CATEGORYID" + category.CategoryID;
var command = new SqlCommand(query, connection);
counter = (int)command.ExecuteScalar();
if (counter == 1)
{
string updateQuery = @"UPDATE CATEGORY
SET NAME=@categoryName,LETTERCODE=@categoryLETTERCODE,CATEGORYID=@categoryCategoryID
WHERE CATEGORYID=" + category.CategoryID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@categoryName", category.Name);
updateCommand.Parameters.AddWithValue("@categoryLETTERCODE", category.LETTERCODE);
updateCommand.Parameters.AddWithValue("@CATEGORYID", category.CategoryID);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO CATEGORY(NAME,LETTERCODE,CATEGORYID)
VALUES (@categoryName,@categoryLETTERCODE,@CATEGORYID)";
var addCommand = new SqlCommand(addQuery, connection);
addCommand.Parameters.AddWithValue("@categoryName", category.Name);
addCommand.Parameters.AddWithValue("@categoryLETTERCODE", category.LETTERCODE);
addCommand.Parameters.AddWithValue("@CATEGORYID", category.CategoryID);
addCommand.ExecuteNonQuery();
}
}
}
public void UpdateOrInsert(Category category)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Categories
WHERE CategoryID = '{category.Id}')
BEGIN
UPDATE Categories
SET Name = '{category.Name}'
WHERE CategoryID = '{category.Id}'
END
ELSE
BEGIN
INSERT INTO Categories
VALUES ('{category.Id}', '{category.Name}');
END";
ExecuteNonQuery(query);
}