Solved

Data Manger Class

Posted on 2007-03-25
1
464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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