I have a C# project that Im writing in visual studio on a WPF form.. I need some
ID: 3813077 • 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 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
//Modify this method in clsSQL as below
//this method returns insert sql statement
public static string Insert_SQL(clsItem item)
{
string sSQL;
//construct an insert sql query by using the item object passed in
sSQL = "INSERT INTO ItemDesc(ItemCode, ItemDesc, Cost VALUES( '" + item.ItemCode + "','" + item.ItemDesc + "','" +item.Cost + "'";
return sSQL;
}
Create a new method InsertItemIntoDB in clsitem_Manager as below
//this method takes an clsItem object and constructs a sql statement by calling the above method and //calls ExecuteNonQuery method in clsDataAcess
public int InsertItemIntoDB(clsItem item)
{
string insert_sql = clsSQL.Insert_SQL(item)
int rowEffected = db.ExecuteNonQuery(insert_sql );
if(rowEffected > 0)
{
//record inserted successfully
}
}
//Similarly for delete add the following method in clsSQL
public static string Delete_SQL(string itemCode)
{
string sSQL;
//construct an insert sql query by using the item object passed in
sSQL = "delete from ItemDesc where ItemCode='" + itemCode + "'" ;
return sSQL;
}
//to delete an item from db there should be a unique column in db let us assume that unique column is itemCode
//Add this method in cls_ItemManager class
public int DeleteItemFromDB(string itemCode)
{
string delete_sql = clsSQL.Delete_SQL(itemCode)
int rowEffected = db.ExecuteNonQuery(delete_sql );
if(rowEffected > 0)
{
//record deleted successfully
}
}
//now the clsitem_Manager class looks like below
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 int InsertItemIntoDB(clsItem item)
{
//call Insert_sql method to prepare query
string insert_sql = clsSQL.Insert_SQL(item)
int rowEffected = db.ExecuteNonQuery(insert_sql );
if(rowEffected > 0)
{
//record inserted successfully
}
}
public int DeleteItemFromDB(string itemCode)
{
//call Delete_SQLmethod to prepare query
string delete_sql = clsSQL.Delete_SQL(itemCode)
int rowEffected = db.ExecuteNonQuery(delete_sql );
if(rowEffected > 0)
{
//record deleted successfully
}
}
}
//and clsSQL class looks like below
public class clsSQL
{
public static string GetAllItems_SQL()
{
string sSQL;
sSQL = "Select ItemCode, ItemDesc, Cost FROM ItemDesc";
return sSQL;
}
public static string Insert_SQL(clsItem item)
{
string sSQL;
//construct an insert sql query by using the item object passed in
sSQL = "INSERT INTO ItemDesc(ItemCode, ItemDesc, Cost VALUES( '" + item.ItemCode + "','" + item.ItemDesc + "','" +item.Cost + "'";
return sSQL;
}
public static string Delete_SQL(string itemCode)
{
string sSQL;
//construct an insert sql query by using the item object passed in
sSQL = "delete from ItemDesc where ItemCode='" + itemCode + "'" ;
return sSQL;
}
}
//there will be no changes in the remaining classes
Note:
* for inserting the item we should pass clsItem object.
* for deleting the item we should have a unique column I assumed itemcode as unique in this answer .
* for delete insert or update we use ExecuteNonQuery method only.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.