Data Manger Class

Hello, im trying to get more use out of this DataClass.  I am trying to build it as I go.  Is what I want to do is keep things simple so I understand it because I am new to the data manager, data classes..  

I want to add functionality and this is what im hoping you could help me out with to add inserts, and updates.

Thanks for your help!



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      // Connection String, SQL String, Data Table
      DataClass MMGDataClass = new DataClass();
      DataTable MMGDataTable = MMGDataClass.GetWorkOrders();

      // To get just a single row
      DataRow drNew = MMGDataTable.Rows[0];
      MessageBox.Show(drNew["Number"].ToString());


      // To get a list of all the rows
      foreach (DataRow dr in MMGDataTable.Rows)
      {
        MessageBox.Show(dr["Number"].ToString());
      }

      this.dataGridView1.DataSource = MMGDataTable;

      foreach (DataRow dr in MMGDataTable.Rows)
      {
        ListViewItem lvi = new ListViewItem(dr["PK_MMGWorkOrderID"].ToString());
        lvi.SubItems.Add(dr["Number"].ToString());
        lvi.Tag = dr["PK_MMGWorkOrderID"].ToString();
        this.listView1.Items.Add(lvi);
      }
    }

  }
  class DataClass
  {
    private String _ConnectionString = "Data Source=(local);Initial Catalog=MMGProjectManagement;Integrated Security=True;";

    public DataTable GetWorkOrders()
    {
      String _SQLString = "SELECT TOP(5) PK_MMGWorkOrderID, Number FROM MMGWorkOrder WHERE FK_OwnerID = 'billd';";
      return SQLDataTable(_SQLString, _ConnectionString);
    }

    private DataTable SQLDataTable(String SQLString, String ConnectionString)
    {
      SqlDataAdapter DataAdapter = new SqlDataAdapter(SQLString, ConnectionString);
      DataSet DataSet = new DataSet("Table1");
      DataAdapter.Fill(DataSet, "Table1");
      return DataSet.Tables["Table1"];
    }
  }
}
LVL 1
wd006451Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

keustermansCommented:
Hi,

I had to do something similar not so long a ago and I wrote a class library based on the Microsoft Enterprise library but its only 30k.

It retrieves connection strings from a app.config file.

It has the follwing features:

   You have a choice of encrypting your connections strings or not encypting them.

    You can insert, update, delete or retrieve by making use of inline sql statements or stored procedures.

    It can connect to SQL Server, Oracle or msaaccess.

     It follows the Singleton Pattern for memory efficiency when connecting to one database
     or you can run multiple instance to connect to multiple databases.

     It makes use of the factory pattern.

    It has built in crypto futures

    and more.

bellow is a sample on how would use the dll.  if you are interested I can email you the library.

app.config needs the following to be added:
<appSettings>
    <!--
      Options for Provider are: OleDb, SqlClient, Oracle, and Odbc.
      You must adjust the appropriate connectionstring accordingly.
      -->
    <add key="EncryptedConnectionStrings"  value="true"/>
    <add key="EncryptionKey"  value="4GqgRg2pGwa5Fy6MP6VGTw=="/>
    <add key="Provider" value="SqlClient" />
    <add key="OleDbConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=action.mdb" />
    <add key="SqlClientConnectionString" value="GzLf0SvW2krnVdLvmdwxoAGfGmOMA9AbAEgz2hT+5nb7CNGuvDoQnmEqzGo9aPx5S0/CYJGSbQjh10lkxK7HTgknz/NhvsyxbcwHANUKHGgZ4mUggQXD4yUoE0p+Lf/OQhMRti1znDNSUbd8tlemYA=="/>
    <add key="OracleConnectionString" value="Data Source=MyOracleActionDB;User Id=sa;Password=;Integrated Security=no;" />
    <add key="OdbcConnectionString" value="Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\action.mdb;Uid=sa;Pwd=;" />
  </appSettings>

you need to set a reference to the dll and add a using statement with the correct namespace

internal class EmployeeDataAccessObject
    {
        public EmployeeDataAccessObject()
        {
        }
        public EmployeeDataTransferObject GetEmployee(int employeeId)
        {
            Db db = new Db();
            IDataParameterCollection parameters = ParameterCollectionFactory.ParameterCollection;
            parameters.Add(new SqlParameter("@EmployeeId", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeId));
            DataRow dr = db.GetDataRow("dbo.[usp_GetEmployeeByEmployeeId]", CommandType.StoredProcedure, parameters);
            return new EmployeeDataTransferObject(System.Convert.ToInt32(dr["EmployeeId"]), dr["FirstName"].ToString(), dr["LastName"].ToString(), new JobDataTransferObject(System.Convert.ToInt32(dr["JobId"]), dr["JobDescription"].ToString()), new DepartmentDataTransferObject(System.Convert.ToInt32(dr["DepartmentId"]), dr["Department"].ToString()), System.Convert.ToInt32(dr["ManagerId"]), dr["EmailAddress"].ToString(), System.Convert.ToBoolean(dr["Administrator"].ToString()), System.Convert.ToBoolean(dr["DepartmentHead"].ToString()));

        }

internal int AddEmployee(EmployeeBusinessObject employeeBO)
        {
            //return a 1 if employee has been added and a 0 0if nothing was done.
            try
            {
                //possible employee does exist results
                //0--employee does not exist
                //1--employee does exist
                //2--employee does exist but has been deleted
                int result = employeeBO.EmployeeDoesExist(this);
                int returnvalue = 0;

                if (result == 0)
                {
                    Db db = new Db();
                    IDataParameterCollection parameters = ParameterCollectionFactory.ParameterCollection;
                    parameters.Add(new SqlParameter("@NTLogin", SqlDbType.VarChar, employeeBO.NTLogin.Length, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.NTLogin));
                    parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, employeeBO.FirstName.Length, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.FirstName));
                    parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, employeeBO.LastName.Length, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.LastName));
                    parameters.Add(new SqlParameter("@JobId", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.JobId));
                    parameters.Add(new SqlParameter("@DepartmentId", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.DepartmentId));
                    parameters.Add(new SqlParameter("@EmailAddress", SqlDbType.VarChar, employeeBO.EmailAddress.Length, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.EmailAddress));
                    parameters.Add(new SqlParameter("@ManagerId", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.ManagerId));
                    parameters.Add(new SqlParameter("@DepartmentHead", SqlDbType.Bit, 1, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.IsDepartmentHead));
                    parameters.Add(new SqlParameter("@Administrator", SqlDbType.Bit, 1, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.IsAdministrator));
                    db.Update("dbo.[usp_AddEmployee]", CommandType.StoredProcedure, parameters);

                    returnvalue = 1;
                }
                else if(result==1)
                {
                    MessageBox.Show("Error: The NTLogin your are trying to add is linked to another employee or the employee already exists!", "Personeel Management Sysytem", MessageBoxButtons.OK);
                }
                else
                {
                    if (MessageBox.Show("Error: The NTLogin your are trying to add is linked to another employee or the employee already exists, but has been marked as deleted.  Would you like to undelete this Employee?", "Personeel Management Sysytem", MessageBoxButtons.YesNo).ToString().ToLower() == "yes")
                    {
                        Db db = new Db();
                        IDataParameterCollection parameters = ParameterCollectionFactory.ParameterCollection;
                        parameters.Add(new SqlParameter("@EmployeeId", SqlDbType.Int, 4, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, employeeBO.EmployeeId));
                        parameters.Add(new SqlParameter("@Deleted", SqlDbType.Bit, 1, ParameterDirection.Input, false, ((Byte)(0)), ((Byte)(0)), "", DataRowVersion.Current, 0));

                        db.Update("dbo.[usp_SetEmployeeDeletedStatus]", CommandType.StoredProcedure, parameters);

                        returnvalue = 1;
                    }
                }
                return returnvalue;
            }
            catch(Exception ex)
            {
                throw new Exception("Error Adding Employee. Error: " + ex.Message.ToString());
            }

        }

}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.