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));
            //        }
            //    }
            //}
        }
示例#3
0
 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;
 }
示例#4
0
 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;
 }
示例#5
0
        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);
 }