I feel like I'm getting really close to getting this application to work but I keep hitting huge roadblocks and I feel like I am missing a few simple things that I can't seem to find on my own. This is a simple C# console application that creates 3 tables and has a few options to add/retrieve/delete rows and data. I was hoping I could get a few pointers on where I went wrong and what I can do to get this working.
Thanks for the help...
populateTables.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;
namespace PopulateDB_SQLite_
{
class populateTables
{
static string strConnectionString = @"Data Source=C:\Programming\C#\Inventory.db;Version=3;";
static SQLiteConnection sqlConn = new SQLiteConnection(strConnectionString);
static SQLiteDataAdapter sql;
static SQLiteCommand comm;
static DataTable dt = new DataTable();
int count = 0;
public void createTables()
{
string inventory = @"CREATE TABLE IF NOT EXISTS [Inventory]([ItemID] integer PRIMARY KEY NOT NULL, [Descrip] varchar(25), [Price] int, [Quanity] int)";
string orders = @"CREATE TABLE IF NOT EXISTS [Orders]([OrderID] integer PRIMARY KEY NOT NULL, [Items] varchar, [ItemCount] int, [TotalCost] int)";
string customers = @"CREATE TABLE IF NOT EXISTS [Customers]([CustomerID] integer PRIMARY KEY NOT NULL, [OrderID] int)";
sqlConn.Open();
comm = new SQLiteCommand(inventory, sqlConn);
comm.ExecuteNonQuery();
comm = new SQLiteCommand(orders, sqlConn);
comm.ExecuteNonQuery();
comm = new SQLiteCommand(customers, sqlConn);
comm.ExecuteNonQuery();
sqlConn.Close();
}
public void inventoryTable(int op)
{
sql = new SQLiteDataAdapter();
string line = "";
switch (op)
{
case 1: //Print descrip, price, quantity
Console.WriteLine("Enter ItemID to print:");
line = Console.ReadLine();
Convert.ToInt32(line);
comm = new SQLiteCommand("SELECT * FROM Inventory WHERE ItemID = " + line + ";", sqlConn);
sqlConn.Open();
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine("ID: " + rdr.GetInt32(0) + "Descrip: " + rdr.GetString(1) + "Price: " + rdr.GetInt32(2) + "Quantity: " + rdr.GetInt32(3));
}
}
}
sqlConn.Close();
break;
case 2: //Insert new items
Console.WriteLine("Enter description, price, quantity seperated by commas(no spaces):");
line = Console.ReadLine();
string[] tokens = line.Split(',');
comm = new SQLiteCommand("INSERT INTO Inventory(ItemID, Descrip, Price, Quantity) VALUES(@ItemID, Descrip, Price, Quantity);", sqlConn);
comm.Parameters.AddWithValue("@ItemID", count);
comm.Parameters.AddWithValue("@Descrip", tokens[0]);
comm.Parameters.AddWithValue("@Price", Convert.ToInt32(tokens[1]));
comm.Parameters.AddWithValue("@Quantity", Convert.ToInt32(tokens[2]));
count++;
break;
case 3: //Update inventory count
Console.WriteLine("Enter ItemID, new quantity seperated by commas(no spaces):");
line = Console.ReadLine();
string[] tokens1 = line.Split(',');
comm = new SQLiteCommand("UPDATE Inventory SET Quantity ='" + Convert.ToInt32(tokens1[1]) + " WHERE ItemID =" + Convert.ToInt32(tokens1[0]) + ";", sqlConn);
break;
case 4: //Update item descrip
Console.WriteLine("Enter ItemID, new description seperated by commas(no spaces):");
line = Console.ReadLine();
string[] tokens2 = line.Split(',');
comm = new SQLiteCommand("UPDATE Inventory SET Descrip =" + tokens2[1] + " WHERE ItemID =" + Convert.ToInt32(tokens2[0]) + ";");
break;
case 5: //Delete an item
Console.WriteLine("Enter ItemID to purge: ");
line = Console.ReadLine();
Convert.ToInt32(line);
comm = new SQLiteCommand("DELETE FROM Inventory WHERE ItemID=" + line + ";");
break;
}
try
{
sqlConn.Open();
object mval = comm.ExecuteScalar();
sqlConn.Close();
}
catch (Exception c)
{
Console.WriteLine(c.Message.ToString(), "Error Message");
}
}
public void orderTable(int op)
{
sql = new SQLiteDataAdapter();
string line = "";
switch (op)
{
case 1: //create new order
Console.WriteLine("Enter number of items, itemID's, customerID, seperated by commas(no spaces):");
line = Console.ReadLine();
string[] tokens = line.Split(',');
line = "";
//grab last number in tokens for customerID
int customerID = Convert.ToInt32(tokens[tokens.Length - 1]);
//grab first number in tokens
int numberOfItems = Convert.ToInt32(tokens[0]);
//get itemIDs and put 'em all in a string
for (int i = 1; i <= numberOfItems; i++)
{
line += tokens[i] + ";";
}
comm = new SQLiteCommand("INSERT INTO Orders(ItemCount, Items) VALUES(@ItemCount, Items);", sqlConn);
//comm.Parameters.AddWithValue("@OrderID", count);
comm.Parameters.AddWithValue("@ItemCount", numberOfItems);
comm.Parameters.AddWithValue("@Items", line);
break;
case 2: //Print price and descrip for order
Console.WriteLine("Enter OrderID: ");
line = Console.ReadLine();
Convert.ToInt32(line);
int cost = 0;
int price = 0;
string items = "";
string[] tokens1;
Console.WriteLine("Order: ");
comm = new SQLiteCommand("SELECT * FROM Orders WHERE OrderID =" + line + ";", sqlConn);
sqlConn.Open();
//get item IDs
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
items = rdr.GetString(1);
}
}
}
sqlConn.Close();
tokens1 = items.Split(',');
for (int i = 0; i < tokens1.Length; i++)
{
comm = new SQLiteCommand("SELECT * FROM Inventory WHERE ItemID=" + tokens1[i] + ";", sqlConn);
sqlConn.Open();
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
cost += rdr.GetInt32(2);
price = rdr.GetInt32(2);
}
}
}
sqlConn.Close();
Console.WriteLine("ItemID: " + tokens1[i] + ", Price: $" + price);
}
Console.WriteLine("Total Cost: " + cost);
break;
case 3: //Print order and description for customer
int orderID = 0;
Console.WriteLine("Enter customer ID: ");
line = Console.ReadLine();
Convert.ToInt32(line);
comm = new SQLiteCommand("SELECT * FROM Customers WHERE CustomerID =" + line + ";", sqlConn);
sqlConn.Open();
//get item IDs
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
orderID = rdr.GetInt32(1);
}
}
}
sqlConn.Close();
comm = new SQLiteCommand("SELECT * FROM Orders WHERE OrderID =" + orderID + ";", sqlConn);
sqlConn.Open();
//get descript and order
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine("ID: " + rdr.GetInt32(0) + ", Items: " + rdr.GetString(1) + ", ItemCount: " + rdr.GetInt32(2) + ", TotalCost: $" + rdr.GetInt32(3));
}
}
}
sqlConn.Close();
break;
case 4: //Print item count and order total for all customers
customerID = 0;
orderID = 0;
comm = new SQLiteCommand("SELECT * FROM Customers;", sqlConn);
sqlConn.Open();
//get item IDs
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
customerID = rdr.GetInt32(0);
orderID = rdr.GetInt32(1);
//Put another command here??
}
}
}
sqlConn.Close();
comm = new SQLiteCommand("SELECT * FROM Orders WHERE OrderID =" + orderID + ";", sqlConn);
sqlConn.Open();
//get descript and order
using (comm)
{
using (SQLiteDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
Console.WriteLine("ID: " + rdr.GetInt32(0) + ", Items: " + rdr.GetString(1) + ", ItemCount: " + rdr.GetInt32(2) + ", TotalCost: $" + rdr.GetInt32(3));
}
}
}
sqlConn.Close();
break;
}
try
{
sqlConn.Open();
object mval = comm.ExecuteScalar();
sqlConn.Close();
}
catch (Exception c)
{
Console.WriteLine(c.Message.ToString(), "Error Message");
}
}
}
}
program.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;
using System.Threading;
namespace PopulateDB_SQLite_
{
class Program
{
static void Main(string[] args)
{
//try
//{
populateTables tb = new populateTables();
tb.createTables();
Console.WriteLine("Database connected successfully...");
menu(tb);
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex);
//}
}
public static void menu(populateTables tb)
{
string line = "";
Console.WriteLine("1) Print description, price, and quanity for an item");
//tb.inventoryTable(1);
Console.WriteLine("2) Create a new order");
//tb.orderTable(1);
Console.WriteLine("3) Print price and description for an order");
//tb.orderTable(2);
Console.WriteLine("4) Print order and description for a customer");
//tb.orderTable(3);
Console.WriteLine("5) Print item count and order total for all customers");
//tb.orderTable(4);
Console.WriteLine("6) Print item count and order total per customer with a total greater than $100");
Console.WriteLine("7) Insert a new item");
//tb.inventoryTable(2);
Console.WriteLine("8) Update inventory count for an item");
//tb.inventoryTable(3);
Console.WriteLine("9) Update item description");
//tb.inventoryTable(4);
Console.WriteLine("10) Delete an item");
//tb.inventoryTable(5);
Console.WriteLine("11) Exit");
line = Console.ReadLine();
int op = Convert.ToInt32(line);
switch (op)
{
case 1:
tb.inventoryTable(1);
menu(tb);
break;
case 2:
tb.orderTable(1);
menu(tb);
break;
case 3:
tb.orderTable(2);
menu(tb);
break;
case 4:
tb.orderTable(3);
menu(tb);
break;
case 5:
tb.orderTable(4);
menu(tb);
break;
case 6: //fix this
tb.orderTable(1);
menu(tb);
break;
case 7:
tb.inventoryTable(2);
menu(tb);
break;
case 8:
tb.inventoryTable(3);
menu(tb);
break;
case 9:
tb.inventoryTable(4);
menu(tb);
break;
case 10:
tb.inventoryTable(5);
menu(tb);
break;
case 11:
//Environment.Exit(0);
break;
default:
Console.WriteLine("Choose an option from the menu");
menu(tb);
break;
}
}
}
}
Right now I'm getting an error when I try to add something to the database "SQL logic error or missing database". But when I run it, I see a file created and I can navigate in my command prompt to it and access the DB with sqlite3 so I'm not sure why this error is popping up.
All the best
Aucun commentaire:
Enregistrer un commentaire