?
Solved

Data Manger Class

Posted on 2007-03-25
1
Medium Priority
?
465 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 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