Solved

Data Manger Class

Posted on 2007-03-25
1
456 Views
Last Modified: 2013-12-17
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"];
    }
  }
}
0
Comment
Question by:wd006451
1 Comment
 
LVL 7

Accepted Solution

by:
keustermans earned 500 total points
ID: 18806755
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now