.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

send one table data to another table

Posted By: kamlesh     Posted Date: February 28, 2011    Points:2   Category :Sql Server
hi

i want to send one table data to another means suppose my first table has 1000 items and i want to send 100 items to second table when i am sending my 100 items from first table to second table then that 100 items should delete from first table

please help me

Responses
Author: Maverick             
Posted Date: February 28, 2011     Points: 20   

You can create an stored procedure with transaction

Use "Insert into <tablename> as select * from <source table>" and then delete from source table.

Make sure you use transaction.

Hope this helps.


Thanks,
Maverick
Author: Dhiraj Ranka             
Posted Date: February 28, 2011     Points: 20   

Hey Maverick,

This will copy all records and I guess Kamlesh wants only selected(100) records to be copied from source table to destination table.

You can use cursor for the same in which you will loop through all the 100 records and then you can delete the same 100 records from the source_table

For looping through the select query you can follow this
http://sim.plified.com/2008/03/28/t-sql-loop-through-select-statement/

Hope this helps.

Thanks & Regards,
Dhiraj Ranka
http://www.twitter.com/dhirajranka | http://www.dhirajranka.com
Author: kamlesh             
Posted Date: May 03, 2011     Points: 20   

Full Database New

ALTER PROCEDURE [dbo].[DeleteRoutefolder]

@RouteFolderId int

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
delete from dbo.MailMaster where RouteFolderID=@RouteFolderId;
delete from dbo.MailSubFolder where RouteFolderID=@RouteFolderId;
delete from dbo.MailRoutefolder where ID=@RouteFolderId;
delete from dbo.AppointmentMaster where RouteFolderID=@RouteFolderId;
delete from dbo.Attachment where RouteFolderID=@RouteFolderId;
delete from dbo.ContactMaster where RouteFolderID=@RouteFolderId;
delete from dbo.JournalMaster where RouteFolderID=@RouteFolderId;
delete from dbo.NoteMaster where RouteFolderID=@RouteFolderId;
delete from dbo.TaskMaster where RouteFolderID=@RouteFolderId;

END

ALTER PROCEDURE [dbo].[DeleteSubfolder]

@SubFolderId int

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
delete from dbo.MailMaster where SubFolderId=@SubFolderId;
--delete from dbo.MailMaster where =@SubFolderId;
delete from dbo.MailSubFolder where ID =@SubFolderId;
delete from dbo.MailRoutefolder where ID =@SubFolderId;
delete from dbo.ContactMaster where SubFolderId =@SubFolderId;
delete from dbo.JournalMaster where SubFolderId =@SubFolderId;
delete from dbo.NoteMaster where SubFolderId =@SubFolderId;
delete from dbo.TaskMaster where SubFolderId =@SubFolderId;
delete from dbo.AppointmentMaster where SubFolderId =@SubFolderId;
delete from dbo.Attachment where SubFolderId =@SubFolderId;
delete from dbo.MailMaster where SubFolderId in (select ID from dbo.MailSubFolder where ParentId=@SubFolderId);
delete from dbo.MailSubFolder where ParentId = @SubFolderId;


END

ALTER PROCEDURE [dbo].[GetAllNodes]
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder
END

ALTER PROCEDURE [dbo].[GetAllRouteFolder]
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder

END


ALTER PROCEDURE [dbo].[GetAllSubFolders]
@RouteFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailSubFolder
END

ALTER PROCEDURE [dbo].[GetAppointmentDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.AppointmentMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.AppointmentMaster where SubFolderId=@SubFolderId and (Subject like '%'+ @Filter + '%' or Body like '%'+ @Filter + '%')
end
END


ALTER PROCEDURE [dbo].[GetAttachmentListByMailID]
@MailId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

Select * from dbo.Attachment where MailID=@MailId

END


ALTER PROCEDURE [dbo].[GetCalendarNodes]
@SubFolderId int=null
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder where ID IN (select ID from dbo.MailSubFolder where Name='Calendar')

END



ALTER PROCEDURE [dbo].[GetContactDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.ContactMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.ContactMaster where SubFolderId=@SubFolderId and (FullName like '%'+ @Filter + '%' or Companies like '%'+ @Filter + '%' or MobileTelephoneNumber like '%'+ @Filter + '%' or Email1Address like '%'+ @Filter + '%' or Email1Address like '%'+ @Filter + '%')
--Select * from dbo.ContactMaster where SubFolderId=@SubFolderId and (FullName like '%'+ @Filter + '%' or Companies like '%'+ @Filter + '%' or MobileTelephoneNumber LIKE '%(' + LEFT(@Filter, 3) +') ' + SUBSTRING(@Filter, 4, 3) +'-' + RIGHT(@Filter, 4) + '%' or Email1Address like '%'+ @Filter + '%' or Email1Address like '%'+ @Filter + '%')
end
END


ALTER PROCEDURE [dbo].[GetContactNodes]
@SubFolderId int=null
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder where ID IN (select ID from dbo.MailSubFolder where Name='Contacts')
END


ALTER PROCEDURE [dbo].[GetJournalDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.JournalMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.JournalMaster where SubFolderId=@SubFolderId and ( Subject like '%'+ @Filter + '%' or Companies like '%'+ @Filter + '%' or Body like '%'+ @Filter + '%' )
end
END

ALTER PROCEDURE [dbo].[GetJournalNodes]
@SubFolderId int=null
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder where ID IN (select ID from dbo.MailSubFolder where Name='Journal')
END



ALTER PROCEDURE [dbo].[GetMailDetailByRouteFolderID]
@RouteFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.MailMaster where RouteFolderId=@RouteFolderId
end
else
begin
Select * from dbo.MailMaster where RouteFolderId=@RouteFolderId and (MailFrom like '%'+ @Filter + '%' or MailTo like '%'+ @Filter + '%' or MailCc like '%'+ @Filter + '%' or MailSubject like '%' + @Filter + '%' or MailBody like '%'+ @Filter + '%' or MailBodyPlain like '%'+ @Filter + '%')
end
END



ALTER PROCEDURE [dbo].[GetMailDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.MailMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.MailMaster where SubFolderId=@SubFolderId and (MailFrom like '%'+ @Filter + '%' or MailTo like '%'+ @Filter + '%' or MailCc like '%'+ @Filter + '%' or MailSubject like '%' + @Filter + '%' or MailBody like '%'+ @Filter + '%' or MailBodyPlain like '%'+ @Filter + '%' )
end
END



ALTER PROCEDURE [dbo].[GetNotesDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.NoteMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.NoteMaster where SubFolderId=@SubFolderId and (Subject like '%'+ @Filter + '%' or Body like '%'+ @Filter + '%')
end
END



ALTER PROCEDURE [dbo].[GetNotesNodes]
@SubFolderId int=null
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder where ID IN (select ID from dbo.MailSubFolder where Name='Notes')
END



ALTER PROCEDURE [dbo].[GetRootFolderbySubFolderId]
@SubFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select MailSubFolder.*,MailRoutefolder.Name as RouteFolderName from
dbo.MailSubFolder left join dbo.MailRoutefolder on MailSubFolder.RouteFolderID=MailRoutefolder.ID


END

ALTER PROCEDURE [dbo].[GetRouteFolderbyRouteFolderId]
@SubFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select MailRoutefolder.Name as RouteFolderName from
dbo.MailRoutefolder where MailRoutefolder.ID = @SubFolderId

END


ALTER PROCEDURE [dbo].[GetSubFolderbyRouteId]
@RouteFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailSubFolder where RouteFolderID=@RouteFolderId

END


ALTER PROCEDURE [dbo].[GetSubFolderbySubFolderId]
@SubFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select MailSubFolder.*,MailRoutefolder.Name as RouteFolderName from
dbo.MailSubFolder left join dbo.MailRoutefolder on MailSubFolder.RouteFolderID=MailRoutefolder.ID

where MailRoutefolder.ID=@SubFolderId

END



ALTER PROCEDURE [dbo].[GetTaskDetailByRouteFolderID]
@RouteFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.TaskMaster where RouteFolderId=@RouteFolderId
end
else
begin
Select * from dbo.TaskMaster where RouteFolderId=@RouteFolderId and (Subject like '%'+ @Filter + '%' or Body like '%'+ @Filter + '%')
end
END


ALTER PROCEDURE [dbo].[GetTaskDetailBysubFolderID]
@SubFolderId int,
@Filter varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
if @Filter=''
begin
Select * from dbo.TaskMaster where SubFolderId=@SubFolderId
end
else
begin
Select * from dbo.TaskMaster where SubFolderId=@SubFolderId and (Subject like '%'+ @Filter + '%' or Body like '%'+ @Filter + '%')
end
END


ALTER PROCEDURE [dbo].[GetTaskNodes]
@SubFolderId int=null
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder
Select * from dbo.MailSubFolder where ID IN (select ID from dbo.MailSubFolder where Name='Tasks')
END


ALTER PROCEDURE [dbo].[GetTopFolderbySubFolderId]
@SubFolderId int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select MailSubFolder.*,MailRoutefolder.Name as RouteFolderName from
dbo.MailSubFolder left join dbo.MailRoutefolder on MailSubFolder.RouteFolderID=MailRoutefolder.ID where MailSubFolder.ID = @SubFolderId


END



ALTER PROCEDURE [dbo].[IsRouteFolderExists]
@RouteFolderName varchar(200)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailRoutefolder where Name=@RouteFolderName

END

ALTER PROCEDURE [dbo].[IsSubFolderExists]
@SubFolderName varchar(200),
@RouteFolderID int
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Select * from dbo.MailSubFolder where [Name]=@SubFolderName and RouteFolderID=@RouteFolderID

END


ALTER PROCEDURE [dbo].[SaveApppointment]
@RouteFolderID int,
@SubFolderId int,
@Subject varchar(Max),
@Location varchar(MAX),
@StartTimeUTC datetime,
@EndTimeUTC datetime,
@Body varchar(MAX),
@AllDayEvent bit,
@RecurrenceState varchar(50),
@CreationTime datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[AppointmentMaster]
([RouteFolderId]
,[SubFolderId]
,[Subject]
,[Location]
,[StartTimeUTC]
,[EndTimeUTC]
,[Body]
,[AllDayEvent]
,[RecurrenceState]
,[CreationTime])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@Subject,@Location,@StartTimeUTC,@EndTimeUTC,@Body,@AllDayEvent,@RecurrenceState,@creationTime)

END


ALTER PROCEDURE [dbo].[SaveAttechment]
@RouteFolderID int,
@SubFolderId int,
@MailID int,
@FileName varchar(MAX),
@FilePath varchar(MAX)

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[Attachment]
([RouteFolderId]
,[SubFolderId]
,[MailID]
,[FileName]
,[FilePath])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@MailID,@FileName,@FilePath)

END



ALTER PROCEDURE [dbo].[SaveContact]
@RouteFolderID int,
@SubFolderId int,
@FullName varchar(Max),
@Companies varchar(MAX),
@FileAs varchar(100),
@Email1Address varchar(200),
@MobileTelephoneNumber varchar(MAX),
@BusinessAddress varchar(MAX),
@CreationTime datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[ContactMaster]
([RouteFolderId]
,[SubFolderId]
,[FullName]
,[Companies]
,[FileAs]
,[Email1Address]
,[MobileTelephoneNumber]
,[BusinessAddress]
,[CreationTime])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@FullName,@Companies,@FileAs,@Email1Address,@MobileTelephoneNumber,@BusinessAddress,@CreationTime)

END


ALTER PROCEDURE [dbo].[SaveJournal]
@RouteFolderID int,
@SubFolderId int,
@Subject varchar(Max),
@Start varchar(MAX),
@Companies varchar(100),
@Body varchar(Max),
@EntryID varchar(MAX),
@Duration int,
@CreationTime datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[JournalMaster]
([RouteFolderId]
,[SubFolderId]
,[Subject]
,[Start]
,[Companies]
,[Body]
,[EntryID]
,[Duration]
,[CreationTime])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@Subject,@Start,@Companies,@Body,@EntryID,@Duration,@CreationTime)

END


ALTER PROCEDURE [dbo].[SaveMail]
@RouteFolderID int,
@SubFolderId int,
@MailFrom varchar(50),
@MailTo varchar(MAX),
@MailCc varchar(MAX),
@MailBcc varchar(MAX),
@MailSubject varchar(MAX),
@MailBody varchar(MAX),
@MailDate datetime,
@MailSize int,
@HasAttechment bit,
@MailBodyPlain nvarchar(MAX)

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[MailMaster]
([RouteFolderId]
,[SubFolderId]
,[MailFrom]
,[MailTo]
,[MailCc]
,[MailBcc]
,[MailSubject]
,[MailBody]
,[MailDate]
,[MailSize]
,[HasAttechment]
,[MailBodyPlain])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@MailFrom,@MailTo,@MailCc,@MailBcc,@MailSubject,@MailBody,@MailDate,@MailSize,@HasAttechment,@MailBodyPlain)

END


ALTER PROCEDURE [dbo].[SaveMailRouteFolder]
@Name varchar(250)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT into [dbo].[MailRoutefolder]
([Name])OUTPUT INSERTED.ID
VALUES
(@Name)

END


ALTER PROCEDURE [dbo].[SaveMailSubFolder]
@Name varchar(50),
@RouteFolderID int,
@Lavel int,
@ParentId int,
@HasChildNode bit
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[MailSubFolder]
([Name]
,[RouteFolderID]
,[Lavel]
,[ParentId]
,[HasChildNode])
OUTPUT INSERTED.ID
VALUES
(@Name,@RouteFolderID,@Lavel,@ParentId,@HasChildNode)

END


ALTER PROCEDURE [dbo].[SaveNote]
@RouteFolderID int,
@SubFolderId int,
@Subject varchar(Max),
@Body varchar(Max),
@CreationTime datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[NoteMaster]
([RouteFolderId]
,[SubFolderId]
,[Subject]
,[Body]
,[CreationTime])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@Subject,@Body,@CreationTime)

END


ALTER PROCEDURE [dbo].[SaveTask]
@RouteFolderID int,
@SubFolderId int,
@Subject varchar(Max),
@StartDate datetime,
@DueDate datetime,
@Body varchar(Max),
@Status varchar(MAX),
@PercentComplete int,
@ReminderTime datetime,
@CreationTime datetime

AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [dbo].[TaskMaster]
([RouteFolderId]
,[SubFolderId]
,[Subject]
,[StartDate]
,[DueDate]
,[Body]
,[Status]
,[PercentComplete]
,[ReminderTime]
,[CreationTime])
OUTPUT INSERTED.ID
VALUES
(@RouteFolderID,@SubFolderId,@Subject,@StartDate,@DueDate,@Body,@Status,@PercentComplete,@ReminderTime,@CReationTime)

END

Appointment

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
Subject varchar(MAX) Unchecked
Location varchar(MAX) Unchecked
StartTimeUTC datetime Checked
EndTimeUTC datetime Checked
Body varchar(MAX) Checked
AllDayEvent bit Checked
RecurrenceState varchar(100) Checked
CreationTime datetime Checked
Unchecked


Attachment

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
MailID int Unchecked
FileName varchar(MAX) Checked
FilePath varchar(MAX) Checked
Unchecked

Contact

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
FullName varchar(MAX) Unchecked
Companies varchar(MAX) Unchecked
FileAs varchar(MAX) Unchecked
Email1Address varchar(MAX) Checked
MobileTelephoneNumber varchar(MAX) Unchecked
BusinessAddress varchar(MAX) Unchecked
CreationTime datetime Checked
Unchecked

Journal

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
Subject varchar(MAX) Unchecked
Start datetime Unchecked
Companies varchar(MAX) Unchecked
Body varchar(MAX) Checked
EntryID varchar(50) Checked
Duration int Checked
CreationTime datetime Checked
Unchecked


MailMaster

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
MailFrom varchar(50) Unchecked
MailTo varchar(MAX) Unchecked
MailCc varchar(MAX) Checked
MailBcc varchar(MAX) Checked
MailSubject nvarchar(MAX) Checked
MailBody varchar(MAX) Checked
MailDate datetime Checked
MailSize int Checked
HasAttechment bit Unchecked
MailBodyPlain nvarchar(MAX) Checked
Unchecked


MailRouteFolder

ID int Unchecked
Name varchar(255) Unchecked
Unchecked

MailSubFolder

ID int Unchecked
Name varchar(50) Unchecked
RouteFolderID int Unchecked
Lavel int Checked
ParentId int Checked
HasChildNode bit Unchecked
Unchecked

Note

ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
Subject varchar(MAX) Unchecked
Body varchar(MAX) Checked
CreationTime datetime Checked
Unchecked

Task


ID int Unchecked
RouteFolderId int Unchecked
SubFolderId int Unchecked
Subject varchar(MAX) Unchecked
StartDate datetime Checked
DueDate datetime Checked
Body varchar(MAX) Checked
Status varchar(MAX) Checked
PercentComplete int Checked
ReminderTime datetime Checked
CreationTime datetime Checked
Unchecked

Thanks And Regards.
Kamlesh Nikam
Author: kamlesh             
Posted Date: May 03, 2011     Points: 20   

public class IniFile
{

public string path;

[DllImport("kernel32")]
private static extern long WritePrivateProfileString(string section,
string key, string val, string filePath);

[DllImport("kernel32")]
private static extern int GetPrivateProfileString(string section,
string key, string def, StringBuilder retVal,
int size, string filePath);



public IniFile(string INIPath)
{
path = INIPath;
}


public void IniWriteValue(string Section, string Key, string Value)
{
WritePrivateProfileString(Section, Key, Value, this.path);
}


public string IniReadValue(string Section, string Key)
{
StringBuilder temp = new StringBuilder(255);
int i = GetPrivateProfileString(Section, Key, "", temp, 255, this.path);
return temp.ToString();
}

}

------

class MailService
{ public static DialogResult InputBox(string title, string promptText, ref string value)
{
Form form = new Form();
Label label = new Label();
TextBox textBox = new TextBox();
Button buttonOk = new Button();
Button buttonCancel = new Button();

form.Text = title;
label.Text = promptText;
textBox.Text = value;

buttonOk.Text = "OK";
buttonCancel.Text = "Cancel";
buttonOk.DialogResult = DialogResult.OK;
buttonCancel.DialogResult = DialogResult.Cancel;

label.SetBounds(9, 20, 372, 13);
textBox.SetBounds(12, 36, 372, 20);
buttonOk.SetBounds(228, 72, 75, 23);
buttonCancel.SetBounds(309, 72, 75, 23);

label.AutoSize = true;
textBox.Anchor = textBox.Anchor | AnchorStyles.Right;
buttonOk.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;
buttonCancel.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;

form.ClientSize = new Size(396, 107);
form.Controls.AddRange(new Control[] { label, textBox, buttonOk, buttonCancel });
form.ClientSize = new Size(Math.Max(300, label.Right + 10), form.ClientSize.Height);
form.FormBorderStyle = FormBorderStyle.FixedDialog;
form.StartPosition = FormStartPosition.CenterScreen;
form.MinimizeBox = false;
form.MaximizeBox = false;
form.AcceptButton = buttonOk;
form.CancelButton = buttonCancel;

DialogResult dialogResult = form.ShowDialog();
value = textBox.Text;
return dialogResult;
}
public static List<MailNodesDTO> GetCalenderNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetCalendarNodes", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

}public static List<MailNodesDTO> GetContactNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("[GetContactNodes]", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

} public static List<MailNodesDTO> GetTaskNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetTaskNodes", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

}public static List<MailNodesDTO> GetNotesNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetNotesNodes", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

} public static List<MailNodesDTO> GetJournalNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetJournalNodes", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

}public static MailDetailDTO SaveMail(MailDetailDTO MailDetail)
{
SqlConnection Conn;
try
{
Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveMail", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = MailDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = MailDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@MailFrom", SqlDbType.VarChar));
sqlCmd.Parameters["@MailFrom"].Value = Convert.ToString(MailDetail.MailFrom);

sqlCmd.Parameters.Add(new SqlParameter("@MailTo", SqlDbType.VarChar));
sqlCmd.Parameters["@MailTo"].Value = Convert.ToString(MailDetail.MailTo);


sqlCmd.Parameters.Add(new SqlParameter("@MailCc", SqlDbType.VarChar));
sqlCmd.Parameters["@MailCc"].Value = Convert.ToString(MailDetail.MailCc);


sqlCmd.Parameters.Add(new SqlParameter("@MailBcc", SqlDbType.VarChar));
sqlCmd.Parameters["@MailBcc"].Value = Convert.ToString(MailDetail.MailBcc);
//if (sqlCmd.Parameters["@MailBcc"].Value == null)
// sqlCmd.Parameters["@MailBcc"].Value = string.Empty;

sqlCmd.Parameters.Add(new SqlParameter("@MailSubject", SqlDbType.VarChar));
sqlCmd.Parameters["@MailSubject"].Value = Convert.ToString(MailDetail.MailSubject);


sqlCmd.Parameters.Add(new SqlParameter("@MailBody", SqlDbType.VarChar));
sqlCmd.Parameters["@MailBody"].Value = Convert.ToString(MailDetail.MailBody);

sqlCmd.Parameters.Add(new SqlParameter("@MailDate", SqlDbType.DateTime));
sqlCmd.Parameters["@MailDate"].Value = MailDetail.MailDate;


sqlCmd.Parameters.Add(new SqlParameter("@MailSize", SqlDbType.Int));
sqlCmd.Parameters["@MailSize"].Value = MailDetail.MailSize;

sqlCmd.Parameters.Add(new SqlParameter("@HasAttechment", SqlDbType.Bit));
sqlCmd.Parameters["@HasAttechment"].Value = MailDetail.HasAttechment;

sqlCmd.Parameters.Add(new SqlParameter("@MailBodyPlain", SqlDbType.NVarChar));
sqlCmd.Parameters["@MailBodyPlain"].Value = Convert.ToString(MailDetail.MailBodyNew);

//sqlCmd.Parameters.Add(new SqlParameter("@htmlEditor1", SqlDbType.Bit));
//sqlCmd.Parameters["@htmlEditor1"].Value = MailDetail.htmlEditor1;



SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
MailDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{

// MessageBox.Show("Exception caught in SaveMail." + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return MailDetail;

} public static MailRouteFolderDTO SaveRouteFolder(MailRouteFolderDTO RouteFolderDTO)
{
SqlConnection Conn;
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveMailRouteFolder", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar));
sqlCmd.Parameters["@Name"].Value = RouteFolderDTO.Name;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
RouteFolderDTO.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{

// MessageBox.Show("Exception caught in SaveMail." + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return RouteFolderDTO;

} public static MailSubFolderDTO SaveSubFolder(MailSubFolderDTO RouteFolderDTO)
{
SqlConnection Conn;
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveMailSubFolder", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar));
sqlCmd.Parameters["@Name"].Value = RouteFolderDTO.Name;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = RouteFolderDTO.RouteFolderID;

sqlCmd.Parameters.Add(new SqlParameter("@Lavel", SqlDbType.Int));
sqlCmd.Parameters["@Lavel"].Value = RouteFolderDTO.Lavel;

sqlCmd.Parameters.Add(new SqlParameter("@ParentId", SqlDbType.Int));
sqlCmd.Parameters["@ParentId"].Value = RouteFolderDTO.ParentId;

sqlCmd.Parameters.Add(new SqlParameter("@HasChildNode", SqlDbType.Bit));
sqlCmd.Parameters["@HasChildNode"].Value = RouteFolderDTO.HasChildNode;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
RouteFolderDTO.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{

// MessageBox.Show("Exception caught in SaveMail." + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return RouteFolderDTO;

} public static List<MailSubFolderDTO> GetSubNodeList(DataTable dt2, int RouteFolderId, int ParentId)
{
DataRow[] dataRows;
List<MailSubFolderDTO> SubNodeList = new List<MailSubFolderDTO>();
dataRows = dt2.Select("RouteFolderID=" + RouteFolderId + " and ParentId=" + ParentId.ToString());
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, RouteFolderId, SubFolderDTO.ID);
SubNodeList.Add(SubFolderDTO);
}
return SubNodeList;
} public static List<MailNodesDTO> GetNodeList()
{
SqlConnection Conn;
List<MailNodesDTO> NodeList = new List<MailNodesDTO>();

try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetAllNodes", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataRow[] dataRows;
da.Fill(ds);
Conn.Close();
sqlCmd.Dispose();
dt1 = ds.Tables[0];
dt2 = ds.Tables[1];

for (int i = 0; i < dt1.Rows.Count; i++)
{
MailNodesDTO NodeDTO = new MailNodesDTO();

NodeDTO.RouteFolder.ID = Convert.ToInt32(dt1.Rows[i]["ID"]);
NodeDTO.RouteFolder.Name = Convert.ToString(dt1.Rows[i]["Name"]);
dataRows = dt2.Select("RouteFolderID=" + NodeDTO.RouteFolder.ID + " and Lavel=1");
foreach (DataRow dr in dataRows)
{
MailSubFolderDTO SubFolderDTO = new MailSubFolderDTO();
SubFolderDTO.ID = Convert.ToInt32(dr["ID"]);
SubFolderDTO.Name = Convert.ToString(dr["Name"]);
SubFolderDTO.Lavel = Convert.ToInt32(dr["Lavel"]);
SubFolderDTO.ParentId = Convert.ToInt32(dr["ParentId"]);
SubFolderDTO.RouteFolderID = Convert.ToInt32(dr["RouteFolderID"]);
SubFolderDTO.HasChildNode = Convert.ToBoolean(dr["HasChildNode"]);
if (SubFolderDTO.HasChildNode)
SubFolderDTO.ChieldNodeList = GetSubNodeList(dt2, NodeDTO.RouteFolder.ID, SubFolderDTO.ID);
NodeDTO.SubFolderDTOList.Add(SubFolderDTO);
}
NodeList.Add(NodeDTO);
}
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NodeList;

}public static List<MailDetailDTO> GetMailDetailBySubNode(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<MailDetailDTO> MailList = new List<MailDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetMailDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
MailDetailDTO MailDetail = new MailDetailDTO();
MailDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
MailDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
MailDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
MailDetail.MailFrom = Convert.ToString(dt.Rows[i]["MailFrom"]);
MailDetail.MailTo = Convert.ToString(dt.Rows[i]["MailTo"]);
MailDetail.MailCc = Convert.ToString(dt.Rows[i]["MailCc"]);
MailDetail.MailBcc = Convert.ToString(dt.Rows[i]["MailBcc"]);
MailDetail.MailSubject = Convert.ToString(dt.Rows[i]["MailSubject"]);
MailDetail.MailBody = Convert.ToString(dt.Rows[i]["MailBody"]);
MailDetail.MailDate = Convert.ToDateTime(dt.Rows[i]["MailDate"]);
MailDetail.MailSize = Convert.ToInt32(dt.Rows[i]["MailSize"]);
MailDetail.HasAttechment = Convert.ToBoolean(dt.Rows[i]["HasAttechment"]);
MailDetail.MailBodyNew = Convert.ToString(dt.Rows[i]["MailBodyPlain"]);
MailList.Add(MailDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return MailList;

}public static List<MailDetailDTO> GetMailDetailByRouteNode(int RoutFolderId, string Filter)
{
SqlConnection Conn;
List<MailDetailDTO> MailList = new List<MailDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetMailDetailByRouteFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderId", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderId"].Value = RoutFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
MailDetailDTO MailDetail = new MailDetailDTO();
MailDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
MailDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
MailDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
MailDetail.MailFrom = Convert.ToString(dt.Rows[i]["MailFrom"]);
MailDetail.MailTo = Convert.ToString(dt.Rows[i]["MailTo"]);
MailDetail.MailCc = Convert.ToString(dt.Rows[i]["MailCc"]);
MailDetail.MailBcc = Convert.ToString(dt.Rows[i]["MailBcc"]);
MailDetail.MailSubject = Convert.ToString(dt.Rows[i]["MailSubject"]);
MailDetail.MailBody = Convert.ToString(dt.Rows[i]["MailBody"]);
MailDetail.MailDate = Convert.ToDateTime(dt.Rows[i]["MailDate"]);
MailDetail.MailSize = Convert.ToInt32(dt.Rows[i]["MailSize"]);
MailDetail.HasAttechment = Convert.ToBoolean(dt.Rows[i]["HasAttechment"]);
MailDetail.MailBodyNew = Convert.ToString(dt.Rows[i]["MailBodyPlain"]);

MailList.Add(MailDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
// MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return MailList;

}public static List<MailDetailDTO> GetMailDetailByCondition(string StrQuery)
{
SqlConnection Conn;
List<MailDetailDTO> MailList = new List<MailDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand(StrQuery, Conn);
sqlCmd.CommandType = CommandType.Text;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
MailDetailDTO MailDetail = new MailDetailDTO();
MailDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
MailDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
MailDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
MailDetail.MailFrom = Convert.ToString(dt.Rows[i]["MailFrom"]);
MailDetail.MailTo = Convert.ToString(dt.Rows[i]["MailTo"]);
MailDetail.MailCc = Convert.ToString(dt.Rows[i]["MailCc"]);
MailDetail.MailBcc = Convert.ToString(dt.Rows[i]["MailBcc"]);
MailDetail.MailSubject = Convert.ToString(dt.Rows[i]["MailSubject"]);
MailDetail.MailBody = Convert.ToString(dt.Rows[i]["MailBody"]);
MailDetail.MailDate = Convert.ToDateTime(dt.Rows[i]["MailDate"]);
MailDetail.MailSize = Convert.ToInt32(dt.Rows[i]["MailSize"]);
MailDetail.HasAttechment = Convert.ToBoolean(dt.Rows[i]["HasAttechment"]);
MailDetail.MailBodyNew = Convert.ToString(dt.Rows[i]["MailBodyPlain"]);

MailList.Add(MailDetail);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return MailList;

}public static List<ContactDetailDTO> GetContactDetailByCondition(string StrQuery)
{
SqlConnection Conn;
List<ContactDetailDTO> ContactList = new List<ContactDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand(StrQuery, Conn);
sqlCmd.CommandType = CommandType.Text;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
ContactDetailDTO ContactDetail = new ContactDetailDTO();
ContactDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
ContactDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
ContactDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
ContactDetail.FullName = Convert.ToString(dt.Rows[i]["FullName"]);
ContactDetail.Companies = Convert.ToString(dt.Rows[i]["Companies"]);
ContactDetail.FileAs = Convert.ToString(dt.Rows[i]["FileAs"]);
ContactDetail.Email1Address = Convert.ToString(dt.Rows[i]["Email1Address"]);
ContactDetail.MobileTelephoneNumber = Convert.ToString(dt.Rows[i]["MobileTelephoneNumber"]);
ContactDetail.BusinessAddress = Convert.ToString(dt.Rows[i]["BusinessAddress"]);
ContactDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);


ContactList.Add(ContactDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return ContactList;
}
public static ContactDetailDTO SaveContact(ContactDetailDTO ContactDetail)
{
SqlConnection Conn;
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveContact", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = ContactDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = ContactDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@FullName", SqlDbType.VarChar));
sqlCmd.Parameters["@FullName"].Value = Convert.ToString(ContactDetail.FullName);

sqlCmd.Parameters.Add(new SqlParameter("@Companies", SqlDbType.VarChar));
sqlCmd.Parameters["@Companies"].Value = Convert.ToString(ContactDetail.Companies);

sqlCmd.Parameters.Add(new SqlParameter("@FileAs", SqlDbType.VarChar));
sqlCmd.Parameters["@FileAs"].Value = ContactDetail.FileAs;

sqlCmd.Parameters.Add(new SqlParameter("@Email1Address", SqlDbType.VarChar));
sqlCmd.Parameters["@Email1Address"].Value = ContactDetail.Email1Address;

sqlCmd.Parameters.Add(new SqlParameter("@MobileTelephoneNumber", SqlDbType.VarChar));
sqlCmd.Parameters["@MobileTelephoneNumber"].Value = Convert.ToString(ContactDetail.MobileTelephoneNumber);

sqlCmd.Parameters.Add(new SqlParameter("@BusinessAddress", SqlDbType.VarChar));
sqlCmd.Parameters["@BusinessAddress"].Value = Convert.ToString(ContactDetail.BusinessAddress);

sqlCmd.Parameters.Add(new SqlParameter("@CreationTime", SqlDbType.DateTime));
sqlCmd.Parameters["@CreationTime"].Value = ContactDetail.CreationTime;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
ContactDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
// throw new Exception(ex.Message);
}

return ContactDetail;
}public static List<ContactDetailDTO> GetContactDetailBysubFolderID(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<ContactDetailDTO> ContactList = new List<ContactDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetContactDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
ContactDetailDTO ContactDetail = new ContactDetailDTO();
ContactDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
ContactDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
ContactDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
ContactDetail.FullName = Convert.ToString(dt.Rows[i]["FullName"]);
ContactDetail.Companies = Convert.ToString(dt.Rows[i]["Companies"]);
ContactDetail.FileAs = Convert.ToString(dt.Rows[i]["FileAs"]);
ContactDetail.Email1Address = Convert.ToString(dt.Rows[i]["Email1Address"]);
ContactDetail.MobileTelephoneNumber = Convert.ToString(dt.Rows[i]["MobileTelephoneNumber"]);
ContactDetail.BusinessAddress = Convert.ToString(dt.Rows[i]["BusinessAddress"]);
ContactDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);


ContactList.Add(ContactDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return ContactList;

}
public static TaskDetailDTO SaveTask(TaskDetailDTO TaskDetail)
{
SqlConnection Conn;
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveTask", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = TaskDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = TaskDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Subject", SqlDbType.VarChar));
sqlCmd.Parameters["@Subject"].Value = Convert.ToString(TaskDetail.Subject);

sqlCmd.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
sqlCmd.Parameters["@StartDate"].Value = TaskDetail.StartDate;

sqlCmd.Parameters.Add(new SqlParameter("@DueDate", SqlDbType.DateTime));
sqlCmd.Parameters["@DueDate"].Value = TaskDetail.DueDate;

sqlCmd.Parameters.Add(new SqlParameter("@Body", SqlDbType.VarChar));
sqlCmd.Parameters["@Body"].Value = Convert.ToString(TaskDetail.Body);

sqlCmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar));
sqlCmd.Parameters["@Status"].Value = Convert.ToString(TaskDetail.Status);

sqlCmd.Parameters.Add(new SqlParameter("@PercentComplete", SqlDbType.Int));
sqlCmd.Parameters["@PercentComplete"].Value = TaskDetail.PercentComplete;

sqlCmd.Parameters.Add(new SqlParameter("@ReminderTime", SqlDbType.DateTime));
sqlCmd.Parameters["@ReminderTime"].Value = TaskDetail.ReminderTime;

sqlCmd.Parameters.Add(new SqlParameter("@CreationTime", SqlDbType.DateTime));
sqlCmd.Parameters["@CreationTime"].Value = TaskDetail.CreationTime;



SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
TaskDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
// MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return TaskDetail;
}public static List<TaskDetailDTO> GetTaskDetailBysubFolderID(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<TaskDetailDTO> TaskList = new List<TaskDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetTaskDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
TaskDetailDTO TaskDetail = new TaskDetailDTO();
TaskDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
TaskDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
TaskDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
TaskDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
TaskDetail.StartDate = Convert.ToDateTime(dt.Rows[i]["StartDate"]);
TaskDetail.DueDate = Convert.ToDateTime(dt.Rows[i]["DueDate"]);
TaskDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
TaskDetail.Status = Convert.ToString(dt.Rows[i]["Status"]);
TaskDetail.PercentComplete = Convert.ToInt32(dt.Rows[i]["PercentComplete"]);
TaskDetail.ReminderTime = Convert.ToDateTime(dt.Rows[i]["ReminderTime"]);
TaskDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);

TaskList.Add(TaskDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return TaskList;

} public static List<NoteDetailDTO> GetNotesDetailByCondition(string StrQuery)
{
SqlConnection Conn;
List<NoteDetailDTO> NoteList = new List<NoteDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand(StrQuery, Conn);
sqlCmd.CommandType = CommandType.Text;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
NoteDetailDTO NoteDetail = new NoteDetailDTO();
NoteDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
NoteDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
NoteDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
NoteDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
NoteDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
NoteDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);

NoteList.Add(NoteDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return NoteList;
}public static NoteDetailDTO SaveNote(NoteDetailDTO NoteDetail)
{
SqlConnection Conn;
try
{
Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveNote", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = NoteDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = NoteDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Subject", SqlDbType.VarChar));
sqlCmd.Parameters["@Subject"].Value = Convert.ToString(NoteDetail.Subject);

sqlCmd.Parameters.Add(new SqlParameter("@Body", SqlDbType.VarChar));
sqlCmd.Parameters["@Body"].Value = Convert.ToString(NoteDetail.Body);

sqlCmd.Parameters.Add(new SqlParameter("@CreationTime", SqlDbType.DateTime));
sqlCmd.Parameters["@CreationTime"].Value = NoteDetail.CreationTime;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
NoteDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
// throw new Exception(ex.Message);
}

return NoteDetail;
} public static List<NoteDetailDTO> GetNotesDetailBysubFolderID(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<NoteDetailDTO> TaskList = new List<NoteDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetNotesDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
NoteDetailDTO TaskDetail = new NoteDetailDTO();
TaskDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
TaskDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
TaskDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
TaskDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
TaskDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
TaskDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);
TaskList.Add(TaskDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
// throw new Exception(ex.Message);
}

return TaskList;

}public static List<JournalDetailDTO> GetJournalDetailByCondition(string StrQuery)
{
SqlConnection Conn;
List<JournalDetailDTO> journalList = new List<JournalDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand(StrQuery, Conn);
sqlCmd.CommandType = CommandType.Text;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
JournalDetailDTO JournalDetail = new JournalDetailDTO();
JournalDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
JournalDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
JournalDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
JournalDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
JournalDetail.Start = Convert.ToDateTime(dt.Rows[i]["Start"]);
JournalDetail.Companies = Convert.ToString(dt.Rows[i]["Companies"]);
JournalDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
JournalDetail.EntryID = Convert.ToString(dt.Rows[i]["EntryID"]);
JournalDetail.Duration = Convert.ToInt32(dt.Rows[i]["Duration"]);
JournalDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);

journalList.Add(JournalDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return journalList;
}public static List<JournalDetailDTO> GetJournalDetailBysubFolderID(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<JournalDetailDTO> JournalList = new List<JournalDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetJournalDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
JournalDetailDTO JournalDetail = new JournalDetailDTO();
JournalDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
JournalDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
JournalDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
JournalDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
JournalDetail.Start = Convert.ToDateTime(dt.Rows[i]["Start"]);
JournalDetail.Companies = Convert.ToString(dt.Rows[i]["Companies"]);
JournalDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
JournalDetail.EntryID = Convert.ToString(dt.Rows[i]["EntryID"]);
JournalDetail.Duration = Convert.ToInt32(dt.Rows[i]["Duration"]);
JournalDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);

JournalList.Add(JournalDetail);

}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
// MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
// throw new Exception(ex.Message);
}

return JournalList;

}
public static JournalDetailDTO SaveJournal(JournalDetailDTO JournalDetail)
{
SqlConnection Conn;
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveJournal", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = JournalDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = JournalDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Subject", SqlDbType.VarChar));
sqlCmd.Parameters["@Subject"].Value = Convert.ToString(JournalDetail.Subject);

sqlCmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.DateTime));
sqlCmd.Parameters["@Start"].Value = Convert.ToString(JournalDetail.Start);

sqlCmd.Parameters.Add(new SqlParameter("@Companies", SqlDbType.VarChar));
sqlCmd.Parameters["@Companies"].Value = Convert.ToString(JournalDetail.Companies);

sqlCmd.Parameters.Add(new SqlParameter("@Body", SqlDbType.VarChar));
sqlCmd.Parameters["@Body"].Value = Convert.ToString(JournalDetail.Body);

sqlCmd.Parameters.Add(new SqlParameter("@EntryID", SqlDbType.VarChar));
sqlCmd.Parameters["@EntryID"].Value = Convert.ToString(JournalDetail.EntryID);

sqlCmd.Parameters.Add(new SqlParameter("@Duration", SqlDbType.Int));
sqlCmd.Parameters["@Duration"].Value = Convert.ToString(JournalDetail.Duration);

sqlCmd.Parameters.Add(new SqlParameter("@CreationTime", SqlDbType.DateTime));
sqlCmd.Parameters["@CreationTime"].Value = Convert.ToString(JournalDetail.CreationTime);

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
JournalDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return JournalDetail;
}
public static AppointmentDetailDTO SaveAppointment(AppointmentDetailDTO AppointmentDetail)
{
SqlConnection Conn;
try
{
Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("SaveApppointment", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@RouteFolderID", SqlDbType.Int));
sqlCmd.Parameters["@RouteFolderID"].Value = AppointmentDetail.RouteFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = AppointmentDetail.SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Subject", SqlDbType.VarChar));
sqlCmd.Parameters["@Subject"].Value = Convert.ToString(AppointmentDetail.Subject);

sqlCmd.Parameters.Add(new SqlParameter("@Location", SqlDbType.VarChar));
sqlCmd.Parameters["@Location"].Value = Convert.ToString(AppointmentDetail.Location);

sqlCmd.Parameters.Add(new SqlParameter("@StartTimeUTC", SqlDbType.DateTime));
sqlCmd.Parameters["@StartTimeUTC"].Value = AppointmentDetail.StartTimeUTC;

sqlCmd.Parameters.Add(new SqlParameter("@EndTimeUTC", SqlDbType.DateTime));
sqlCmd.Parameters["@EndTimeUTC"].Value = AppointmentDetail.EndTimeUTC;

sqlCmd.Parameters.Add(new SqlParameter("@Body", SqlDbType.VarChar));
sqlCmd.Parameters["@Body"].Value = Convert.ToString(AppointmentDetail.Body);

sqlCmd.Parameters.Add(new SqlParameter("@AllDayEvent", SqlDbType.Bit));
sqlCmd.Parameters["@AllDayEvent"].Value = Convert.ToString(AppointmentDetail.AllDayEvent);

sqlCmd.Parameters.Add(new SqlParameter("@RecurrenceState", SqlDbType.VarChar));
sqlCmd.Parameters["@RecurrenceState"].Value = Convert.ToString(AppointmentDetail.RecurrenceState);

sqlCmd.Parameters.Add(new SqlParameter("@CreationTime", SqlDbType.DateTime));
sqlCmd.Parameters["@CreationTime"].Value = AppointmentDetail.CreationTime;


SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
AppointmentDetail.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
//MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return AppointmentDetail;
}
public static List<AppointmentDetailDTO> GetAppointmentDetailBysubFolderID(int SubFolderId, string Filter)
{
SqlConnection Conn;
List<AppointmentDetailDTO> TaskList = new List<AppointmentDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand("GetAppointmentDetailBysubFolderID", Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@SubFolderId", SqlDbType.Int));
sqlCmd.Parameters["@SubFolderId"].Value = SubFolderId;

sqlCmd.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar));
sqlCmd.Parameters["@Filter"].Value = Filter;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
AppointmentDetailDTO AppointmentDetail = new AppointmentDetailDTO();
AppointmentDetail.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
AppointmentDetail.RouteFolderId = Convert.ToInt32(dt.Rows[i]["RouteFolderId"]);
AppointmentDetail.SubFolderId = Convert.ToInt32(dt.Rows[i]["SubFolderId"]);
AppointmentDetail.Subject = Convert.ToString(dt.Rows[i]["Subject"]);
AppointmentDetail.Location = Convert.ToString(dt.Rows[i]["Location"]);
AppointmentDetail.StartTimeUTC = Convert.ToDateTime(dt.Rows[i]["StartTimeUTC"]);
AppointmentDetail.EndTimeUTC = Convert.ToDateTime(dt.Rows[i]["EndTimeUTC"]);
AppointmentDetail.Body = Convert.ToString(dt.Rows[i]["Body"]);
AppointmentDetail.AllDayEvent = Convert.ToBoolean(dt.Rows[i]["AllDayEvent"]);
//AppointmentDetail.RecurrenceState = Convert.ToString(dt.Rows[i]["RecurrenceState"]);
AppointmentDetail.CreationTime = Convert.ToDateTime(dt.Rows[i]["CreationTime"]);

TaskList.Add(AppointmentDetail);
}
Conn.Close();
sqlCmd.Dispose();
}
catch (Exception ex)
{
// throw new Exception(ex.Message);
// MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clsError.LogError(ex);
}

return TaskList;

}public static List<AppointmentDetailDTO> GetAppointmentDetailByCondition(string StrQuery)
{
SqlConnection Conn;
List<AppointmentDetailDTO> calendarList = new List<AppointmentDetailDTO>();
try
{

Conn = new SqlConnection(Common.ConnectionString);
Conn.Open();
SqlCommand sqlCmd = new SqlCommand(StrQuery, Conn);
sqlCmd.CommandType = CommandType.Text;


SqlDataAdapt

Thanks And Regards.
Kamlesh Nikam
Author: kamlesh             
Posted Date: May 03, 2011     Points: 20   

public class clsError
{
static string ErrorLogPath = AppDomain.CurrentDomain.BaseDirectory + "ErrorLog.txt";
static string ErrorBackupPath=AppDomain.CurrentDomain.BaseDirectory + "ErrorBackup";

public static void LogError(string ErrorMessage, Exception ex , string ClassName , string FunctionName )
{
// Write the string to a file.
StreamWriter file = null;
bool IsBackupTaken = false;
try
{
FileInfo f;
//Checks Backup folder exist if not then creats Folder
if (!Directory.Exists(ErrorBackupPath))
Directory.CreateDirectory(ErrorBackupPath);
//Takes Backup if existing file is not of current day.
if (File.Exists(ErrorLogPath))
{
f = new FileInfo(ErrorLogPath);
if (f.CreationTime.DayOfYear != System.DateTime.Now.DayOfYear)
{
File.Move(ErrorLogPath, ErrorBackupPath + "ErrorLog" + System.DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".txt");
IsBackupTaken = true;
}
}

//Write Error Log in File
string ErrorLog = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " : " + ErrorMessage;
if (ClassName.Trim() != "" || FunctionName.Trim() != "")
ErrorLog = ErrorLog + System.Environment.NewLine + "Source:" + ClassName + "." + FunctionName;
if (ex != null)
ErrorLog = ErrorLog + System.Environment.NewLine + "Error Message:" + ex.Message + " ,Stack Trace:" + ex.StackTrace.ToString();

file = new System.IO.StreamWriter(ErrorLogPath, true);
file.WriteLine(ErrorLog + System.Environment.NewLine);
//If Backup tacken then set file Created time = Now(Current StartTimeUTC Time)
if (IsBackupTaken)
{
f = new FileInfo(ErrorLogPath);
f.CreationTime = System.DateTime.Now;
}
}
catch (Exception ex1)
{

}
finally
{
if (file != null)
file.Close();
}
}


public static void LogError(Exception ex)
{

StreamWriter file = null;
bool IsBackupTaken = false;
try
{
FileInfo f;
//Checks Backup folder exist if not then creats Folder
if (!Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + "ErrorBackup"))
Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + "ErrorBackup");
//Takes Backup if existing file is not of current day.
if (File.Exists(ErrorLogPath))
{
f = new FileInfo(ErrorLogPath);
if (f.CreationTime.DayOfYear != System.DateTime.Now.DayOfYear)
{
File.Move(ErrorLogPath, AppDomain.CurrentDomain.BaseDirectory + "ErrorLog" + System.DateTime.Now.ToString("yyyy-MM-dd_HHmmss") + ".txt");
IsBackupTaken = true;
}
}

//Write Error Log in File
string ErrorLog = "";

if (ex != null)
ErrorLog = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "Error Message:" + ex.Message + " ,Stack Trace:" + ex.StackTrace.ToString();

file = new System.IO.StreamWriter(ErrorLogPath, true);
file.WriteLine(ErrorLog + System.Environment.NewLine);
//If Backup tacken then set file Created time = Now(Current StartTimeUTC Time)
if (IsBackupTaken)
{
f = new FileInfo(ErrorLogPath);
f.CreationTime = System.DateTime.Now;
}

}
catch (Exception ex1)
{
}
finally
{
if (file != null)
file.Close();
}

}
}

Thanks And Regards.
Kamlesh Nikam
Author: kamlesh             
Posted Date: May 04, 2011     Points: 20   

public class AppointmentDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
string _Subject;
string _Location;
DateTime _StartTimeUTC;
DateTime _EndTimeUTC;
string _Body;
bool _AllDayEvent;
string _RecurrenceState;
DateTime _CreationTime;

public AppointmentDetailDTO()
{
_ID = 0;
_RouteFolderId = 0;
_SubFolderId = 0;
_Subject = string.Empty;
_Location = string.Empty;
_Body = string.Empty;
_AllDayEvent = false;
_RecurrenceState = string.Empty;
}
public int ID
{
get { return _ID; }
set { _ID = value; }
}
public string Subject
{
get { return _Subject; }
set { _Subject = value; }
}
}

public class Attachment
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
int _MailId;
string _FileName;
string _FilePath;
}

public static class Common
{
public static string strComputerName = System.Environment.MachineName;
public static string ConnectionString = "Data Source = " + strComputerName + "\\SQLEXPRESS; Initial Catalog = Outlook; Integrated Security = SSPI";
public static string AttachmentPath = AppDomain.CurrentDomain.BaseDirectory + "Attachments";
}

public class ContactDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
string _FullName;
string _Companies;
string _FileAs;
string _Email1Address;
string _MobileTelephoneNumber;
string _BusinessAddress;
DateTime _CreationTime;
}

public class JournalDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
string _Subject;
DateTime _Start;
string _Companies;
string _Body;
string _EntryID;
int _Duration;
DateTime _CreationTime;
}
public class MailDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
String _MailFrom;
String _MailTo;
String _MailCc;
String _MailBcc;
String _MailSubject;
String _MailBody;
DateTime _MailDate;
int _MailSize;
bool _HasAttechment;
String _MailBodyNew;
}

public class MailDTO
{
public MailRouteFolderDTO RouteFolder;
public List<MailSubFolderDTO> SubFolderDTOList;
public MailSubFolderDTO SelectedSubFolder;
public List<MailDetailDTO> MailDetailList;
}

public class MailNodesDTO
{
public MailRouteFolderDTO RouteFolder;
public List<MailSubFolderDTO> SubFolderDTOList;
public MailNodesDTO()
{
RouteFolder = new MailRouteFolderDTO();
SubFolderDTOList = new List<MailSubFolderDTO>();
}
}

public class MailRouteFolderDTO
{
public int ID;
public string Name;
}

public class MailSubFolderDTO
{
public int ID;
public string Name;
public int RouteFolderID;
public int Lavel;
public int ParentId;
public bool HasChildNode;
public List<MailSubFolderDTO> ChieldNodeList;

public MailSubFolderDTO()
{
ChieldNodeList = new List<MailSubFolderDTO>();
}
}

public class NoteDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
string _Subject;
string _Body;
DateTime _CreationTime;
}

public class TaskDetailDTO
{
int _ID;
int _RouteFolderId;
int _SubFolderId;
string _Subject;
string _Body;
DateTime _StartDate;
DateTime _DueDate;
string _Status;
int _PercentComplete;
DateTime _ReminderTime;
DateTime _CreationTime;
}

Thanks And Regards.
Kamlesh Nikam


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend