EchoTex_Payroll/HRM.DA/DA/Common/TaxAttachmentDA.cs

using Ease.Core.DataAccess;
using HRM.BO;
using Microsoft.Data.SqlClient;
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Drawing;

namespace HRM.DA
{
	internal class TaxAttachmentDA
	{
		public static void Insert(TransactionContext tc, TaxAttachment item, string connectionString)
		{
            
            //using (SqlConnection connection = new SqlConnection(connectionString))
            //{
                IDbCommand command = tc.Connection.CreateCommand();
//                connection.Open();
//                SqlCommand command = new SqlCommand( tc.Connection);
                command.Connection = tc.Connection;
                string commandText = @"INSERT INTO TaxAttachment(EmployeeID,ID,ReferenceID,
                        FileData,OriginalFileName,FileType) values 
                    (@EmployeeID,@InvestmentID,@ReferenceID,@FileData,@OriginalFileName,@FileType)";
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                

            SqlParameter one = new SqlParameter("@EmployeeID", SqlDbType.Int);
            one.Value = item.EmployeeID;
            command.Parameters.Add(one);


            SqlParameter two = new SqlParameter("@InvestmentID", SqlDbType.Int);
            two.Value = item.InvestmentID;
            command.Parameters.Add(two);

            SqlParameter trhee = new SqlParameter("@ReferenceID", SqlDbType.Int);
            trhee.Value = item.ReferenceID;
            command.Parameters.Add(trhee);

            SqlParameter four = new SqlParameter("@FileData", SqlDbType.VarBinary);
            four.Value = item.FileAsByteArray;
            command.Parameters.Add(four);

            SqlParameter five = new SqlParameter("@OriginalFileName", SqlDbType.VarChar);
            five.Value = item.OriginalFileName;
            command.Parameters.Add(five);

            SqlParameter six = new SqlParameter("@FileType", SqlDbType.Int);
            six.Value = item.FileType;
            command.Parameters.Add(six);


            //command.Parameters.Add Add("@EmployeeID", SqlDbType.Int);
            //command.Parameters["@EmployeeID"].Value = item.EmployeeID;

            //command.Parameters.Add("@InvestmentID", SqlDbType.Int);
            //command.Parameters["@InvestmentID"].Value = item.InvestmentID;

            //command.Parameters.Add("@ReferenceID", SqlDbType.Int);
            //command.Parameters["@ReferenceID"].Value = item.ReferenceID;

            //command.Parameters.Add("@FileData", SqlDbType.VarBinary);
            //command.Parameters["@FileData"].Value = item.FileAsByteArray;

            //command.Parameters.Add("@OriginalFileName", SqlDbType.VarChar);
            //command.Parameters["@OriginalFileName"].Value = item.OriginalFileName;

            //command.Parameters.Add("@FileType", SqlDbType.Int);
            //command.Parameters["@FileType"].Value = item.FileType;
            command.Transaction = tc.Transaction;
          //  tc.ExecuteNonQuery(CommandType.Text, commandText, command.Parameters);
            command.ExecuteNonQuery();
            //command.Dispose();

            //}
        }

		internal static IDataReader GetByReferenceId(TransactionContext tc, int refId, EnumFileType fileType)
		{
			return tc.ExecuteReader("SELECT * FROM TaxAttachment where ReferenceID= %n and FileType=%n ", refId, (int)fileType);
		}
		internal static IDataReader GetByReferenceandFileId(TransactionContext tc, int refId, EnumFileType fileType, int id)
		{
			return tc.ExecuteReader("SELECT * FROM TaxAttachment where ReferenceID= %n and FileType=%n and TAXATTACHMENTID=%n", refId, (int)fileType, id);
		}

		internal static void Delete(TransactionContext tc, int ID, EnumTaxAttachment type)
		{
			string sql = SQLParser.MakeSQL(@"DELETE FROM TaxAttachment WHERE ID = %n AND FileType = %n", ID, type);
			tc.ExecuteNonQuery(sql);
		}

		internal static IDataReader GetAllAttachmentsByEmpId(TransactionContext tc, int empId, EnumTaxAttachment fileType)
		{
			return tc.ExecuteReader("SELECT * FROM TaxAttachment WHERE EmployeeID = %n and FileType = %n", empId, fileType);
		}
		internal static IDataReader GetAllAttachmentsByID(TransactionContext tc, int ID, int empId, EnumTaxAttachment fileType)
		{
			return tc.ExecuteReader("SELECT * FROM TaxAttachment WHERE ID =%n and EmployeeID = %n and FileType = %n", ID, empId, fileType);
		}
	}
}