Solved

Data Manger Class

Posted on 2007-03-25
1
458 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with converting data format in xml file using VB.NET 11 52
How can I use SQL Stored Procedure to return FedEx Zone 17 41
Replace &lt; with < 14 53
ASP.NET 5 Templates 2 65
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…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 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

16 Experts available now in Live!

Get 1:1 Help Now