1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data;
6 using System.Data.SqlClient;
7
8
9
10 class ProcessDL
11 {
12 MyClass Obj = new MyClass();
13
14 string[] UserDetail;
15 SqlConnection con;
16 SqlCommand com;
17 SqlDataReader dr;
18 SqlDataAdapter da;
19 DataSet ds;
20
21 public int InsertProcess(string Processname,string InputPath,string OutputPath, string InputParameter, string FTPparameter, string CSVTriggerName,
22 string ImgTriggerName, string ExceptionFTPpath, string UN, string PWD, string FileFormat, string CreatedBy, string CreatedDate,string status,string ContainsKey)
23 {
24 try
25 {
26 con = new SqlConnection(Obj.ConnectionStr);
27 com = new SqlCommand("InsertNewProcess", Obj.DBConnect());
28 com.CommandType = CommandType.StoredProcedure;
29 com.Parameters.AddWithValue("@ProcessName", Processname);
30 com.Parameters.AddWithValue("@InputPath", InputPath);
31 com.Parameters.AddWithValue("@OutputPath", OutputPath);
32 com.Parameters.AddWithValue("@InputParameter", InputParameter);
33 com.Parameters.AddWithValue("@FTPParameter", FTPparameter);
34 com.Parameters.AddWithValue("@CSVTriggerName", CSVTriggerName);
35 com.Parameters.AddWithValue("@ImgTriggerName", ImgTriggerName);
36 com.Parameters.AddWithValue("@ExceptionFTPpath", ExceptionFTPpath);
37 com.Parameters.AddWithValue("@UserName", UN);
38 com.Parameters.AddWithValue("@Pwd", PWD);
39 com.Parameters.AddWithValue("@FileFormat", FileFormat);
40 com.Parameters.AddWithValue("@CreatedBY", CreatedBy);
41 com.Parameters.AddWithValue("@CreatedDate", CreatedDate);
42 com.Parameters.AddWithValue("@status", status);
43 com.Parameters.AddWithValue("@containsKey", ContainsKey);
44 return com.ExecuteNonQuery();
45 }
46 catch (Exception)
47 {
48
49 throw;
50 }
51
52 }
53
54 public int UpdateProcess(string Processname, string InputPath, string OutputPath, string InputParameter, string FTPparameter, string CSVTriggerName,
55 string ImgTriggerName, string ExceptionFTPpath, string UN, string PWD, string FileFormat,string ContainsKey)
56 {
57 try
58 {
59 con = new SqlConnection(Obj.ConnectionStr);
60 com = new SqlCommand("UpdateProcess", Obj.DBConnect());
61 com.CommandType = CommandType.StoredProcedure;
62 com.Parameters.AddWithValue("@ProcessName", Processname);
63 com.Parameters.AddWithValue("@InputPath", InputPath);
64 com.Parameters.AddWithValue("@OutputPath", OutputPath);
65 com.Parameters.AddWithValue("@InputParameter", InputParameter);
66 com.Parameters.AddWithValue("@FTPParameter", FTPparameter);
67 com.Parameters.AddWithValue("@CSVTriggerName", CSVTriggerName);
68 com.Parameters.AddWithValue("@ImgTriggerName", ImgTriggerName);
69 com.Parameters.AddWithValue("@ExceptionFTPpath", ExceptionFTPpath);
70 com.Parameters.AddWithValue("@UserName", UN);
71 com.Parameters.AddWithValue("@Pwd", PWD);
72 com.Parameters.AddWithValue("@FileFormat", FileFormat);
73 com.Parameters.AddWithValue("@containsKey", ContainsKey);
74 return com.ExecuteNonQuery();
75 }
76 catch (Exception)
77 {
78
79 throw;
80 }
81 }
82
83 public DataSet FetchAllValues(string Status)
84 {
85 try
86 {
87 ds = new DataSet();
88 ds.Clear();
89 con = new SqlConnection(Obj.ConnectionStr);
90 com = new SqlCommand("FetchAllProcess", Obj.DBConnect());
91 com.CommandType = CommandType.StoredProcedure;
92 com.Parameters.AddWithValue("@Status", Status);
93 da = new SqlDataAdapter(com);
94 da.Fill(ds);
95 return ds;
96 }
97 catch (Exception)
98 {
99
100 throw;
101 }
102 }
103 }
104
--------------------------------------------------
Stored Procedures
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[FetchAllProcess] @Status varchar(50)
as begin
SELECT [ProcessName]
,[InputPath]
,[OutputPath]
,[InputParameter]
,[FTPParameter]
,[CSVTriggerName]
,[ImgTriggerName]
,[ExceptionFTPpath]
,[UserName]
,[Pwd]
,[FileFormat]
,[CreatedBY]
,convert(varchar, CreatedDate,103) as 'Created On',
containsKey
FROM [ProcessMaster] order by [ProcessName] asc
end
--------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[UpdateProcess]
@ProcessName varchar(500),
@InputPath varchar(500),
@OutputPath varchar(500),
@InputParameter varchar(500),
@FTPParameter varchar(500),
@CSVTriggerName varchar(500),
@ImgTriggerName varchar(500),
@ExceptionFTPpath varchar(500),
@UserName varchar(100),
@Pwd varchar(100),
@FileFormat varchar(100),
@containsKey varchar(100)
as begin
UPDATE ProcessMaster
SET InputPath = @InputPath,OutputPath = @OutputPath,InputParameter = @InputParameter,FTPParameter = @FTPParameter,
CSVTriggerName = @CSVTriggerName,ImgTriggerName = @ImgTriggerName,ExceptionFTPpath = @ExceptionFTPpath,
UserName = @UserName,Pwd = @Pwd,FileFormat = @FileFormat,containsKey=@containsKey
WHERE ProcessName = @ProcessName
end
--------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[InsertNewProcess]
@ProcessName varchar(500),
@InputPath varchar(500),
@OutputPath varchar(500),
@InputParameter varchar(500),
@FTPParameter varchar(500),
@CSVTriggerName varchar(500),
@ImgTriggerName varchar(500),
@ExceptionFTPpath varchar(500),
@UserName varchar(100),
@Pwd varchar(100),
@FileFormat varchar(100),
@CreatedBY varchar(100),
@CreatedDate datetime,
@status varchar(50),
@containsKey varchar(100)
as begin
INSERT INTO ProcessMaster
(ProcessName,InputPath,OutputPath,InputParameter,FTPParameter,CSVTriggerName,ImgTriggerName,ExceptionFTPpath,UserName,Pwd,FileFormat,
CreatedBY,CreatedDate,status,containsKey)
VALUES
(@ProcessName ,@InputPath,@OutputPath,@InputParameter,@FTPParameter,@CSVTriggerName,@ImgTriggerName,@ExceptionFTPpath,@UserName,@Pwd,@FileFormat,
@CreatedBY,@CreatedDate,@status,@containsKey)
end
--------------------------------------------------
