Click here to Skip to main content
15,885,940 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i am Using SQL Database with 2 tables Employee and Department
trying to use DAL to get and set data with database Easy and more security
here what i did :
i made 2 classes reflect the tables in database EX: Emp_object.cs << this is the class
inside this class i defined public properties with the columns inside the table like this
C#
public class Emp_object
{
    public int ID { set; get; }
    public string EmpName { set; get; }
    public string EmpDept { set; get; }
    public float EmpSalary { set; get; }
    public float EmpBounce { set; get; }
    public float EmpCut { set; get; }
    public int EmpCell { set; get; }
    
    public Emp_object()
	{
		//
		// TODO: Add constructor logic here
		//
	}
// i use this method to select * from the table employee
public List<Emp_object> SelectAll()
    {
       List<Emp_object> itm_lst = new List<Emp_object>();
       Emp_object itm;
       DataTable dt= new BaseLayer().GetDataTable("GetAllEmp", CommandType.StoredProcedure);
       foreach (DataRow drow in dt.Rows)
       {
           itm = new Emp_object();
           itm.EmpName = drow["EmpName"].ToString();
           itm.EmpDept = drow["EmpDept"].ToString();
           itm.EmpSalary = (float)drow["EmpSalary"];
           itm.EmpBounce = (float)drow["EmpBounce"];
           itm.EmpCut =(float)drow["Emp_Cut"];
           itm.EmpCell = (int)drow["Emp_Cell"];

           itm_lst.Add(itm);
       }
       return itm_lst;
    }

}

and i have anther class BaseLayer that is get the connection string and it has the methods that interact with the emp_object class
C#
public class BaseLayer
{
    SqlConnection con;
    #region constarctor 
    public BaseLayer()
    {
        con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\HrEmpDatabase.mdf;Integrated Security=True;User Instance=True");
    }
   
    #endregion

public int ModifyTable(string CommandText, CommandType comType,List<Emp_object> EmpList)
    {
        SqlCommand com = new SqlCommand(CommandText, con);
        com.CommandType = comType;
        for (int i = 0; i < EmpList.Count; i++)
        {
            com.Parameters.Add(EmpList[i]);
        }
        con.Open();
        int x = com.ExecuteNonQuery();
        con.Close();
        return x;
    }

now i am trying to use now i need to do method the insert data into database or update , delete using the emp_object class can i pass the object to such method and use the list of emp object to pass the paramters? thats what i need help with
Posted

1 solution

Yes, you can use the list as a parameter to the ModifyTable as you have already defined. The problem you would have is, what is the state if a single Emp? Is it added, modified, deleted or unchanged. You have to know this in order to decide the SQL statement.

I noticed that you have used a data table from which you pull out the list. Data table has a mechanism to track the state of a row (well actually each data row has this). So why not use the data table and make the modifications directly to it and use a SqlDataAdapter[^] to actually carry out the modifications.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900