I have a C# project that Im writing in visual studio on a WPF form.. I need some
ID: 3813082 • Letter: I
Question
I have a C# project that Im writing in visual studio on a WPF form.. I need some help setting up the code to insert an item into the database and then delete and item from the database. I currently have it working so that the program reads the information from a database and then puts it into a datagrid.. I just need it so that a user can write and itemcode, description and cost into a textbox and then it will add that information to the database and or delete it from the databse
I will provide the code that i have and you can help me with those two small methods.. thanks.. let me know i you need anything else
------------------------------
namespace MyFinalGroupProject
{
public class clsitem_Manager
{
clsDataAccess db = new clsDataAccess();
DataSet ds = new DataSet();
BindingList<clsItem> lstItems = new BindingList<clsItem>();
public int numberOfRecords;
public BindingList<clsItem> GetAllItemsFromDB(){
string sSQL = clsSQL.GetAllItems_SQL();
int iRet = 0;
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
foreach (DataRow dr in ds.Tables[0].Rows)
{
lstItems.Add(new clsItem { ItemCode = dr[0].ToString(), ItemDesc = dr[1].ToString(), Cost = dr[2].ToString() });
numberOfRecords++;
}
return lstItems;
}
}
public class clsItem
{
public string ItemCode { get; set; }
public string ItemDesc { get; set; }
public string Cost { get; set; }
}
}
----------------------------------------------------------------------
namespace MyFinalGroupProject
{
public class clsSQL
{
public static string GetAllItems_SQL()
{
string sSQL;
sSQL = "Select ItemCode, ItemDesc, Cost FROM ItemDesc";
return sSQL;
}
public static string Insert()
{
string sSQL;
sSQL = "INSERT INTO ItemDesc(ItemCode, ItemDesc, Cost)" + "VALUES( 0 , 1 , 2)";
return sSQL;
}
}
}
----------------------------------------------------------------------------
namespace MyFinalGroupProject
{
public class clsDataAccess
{
/// <summary>
/// Connection string to the database.
/// </summary>
private string sConnectionString;
/// <summary>
/// Constructor that sets the connection string to the database
/// </summary>
public clsDataAccess()
{
sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= " + Directory.GetCurrentDirectory() + "\Invoice.mdb";
}
/// <summary>
/// This method takes an SQL statment that is passed in and executes it. The resulting values
/// are returned in a DataSet. The number of rows returned from the query will be put into
/// the reference parameter iRetVal.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <param name="iRetVal">Reference parameter that returns the number of selected rows.</param>
/// <returns>Returns a DataSet that contains the data from the SQL statement.</returns>
public DataSet ExecuteSQLStatement(string sSQL, ref int iRetVal)
{
try
{
//Create a new DataSet
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
adapter.SelectCommand = new OleDbCommand(sSQL, conn);
adapter.SelectCommand.CommandTimeout = 0;
//Fill up the DataSet with data
adapter.Fill(ds);
}
}
//Set the number of values returned
iRetVal = ds.Tables[0].Rows.Count;
//return the DataSet
return ds;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This method takes an SQL statment that is passed in and executes it. The resulting single
/// value is returned.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <returns>Returns a string from the scalar SQL statement.</returns>
public string ExecuteScalarSQL(string sSQL)
{
try
{
//Holds the return value
object obj;
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
adapter.SelectCommand = new OleDbCommand(sSQL, conn);
adapter.SelectCommand.CommandTimeout = 0;
//Execute the scalar SQL statement
obj = adapter.SelectCommand.ExecuteScalar();
}
}
//See if the object is null
if (obj == null)
{
//Return a blank
return "";
}
else
{
//Return the value
return obj.ToString();
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This method takes an SQL statment that is a non query and executes it.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <returns>Returns the number of rows affected by the SQL statement.</returns>
public int ExecuteNonQuery(string sSQL)
{
try
{
//Number of rows affected
int iNumRows;
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
OleDbCommand cmd = new OleDbCommand(sSQL, conn);
cmd.CommandTimeout = 0;
//Execute the non query SQL statement
iNumRows = cmd.ExecuteNonQuery();
}
//return the number of rows affected
return iNumRows;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
}
}
Explanation / Answer
Assuming the itemCode, desc and cost are coming from textBox, we can call this add method directly, likewise for delete
public static bool Add(string itemCode, string desc, string cost)
{
Insert(itemCode, desc, cost);
return true;
}
public static string Insert(string itemCode = "1", string desc = "0", string cost = "2")
{
string sSQL;
itemCode = "'" + itemCode + "'";
desc = "'" + desc + "'";
cost = "'" + cost + "'";
sSQL = "INSERT INTO ItemDesc(ItemCode, ItemDesc, Cost)" + "VALUES(itemCode, desc, cost)";
return sSQL;
}
public static bool DeleteItem(string itemCode)
{
string sSQL;
sSQL = "DELETE FROM ItemDesc WHERE ItemCode = '" + itemCode + "'";
return sSQL;
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.