Read and Write Object data from and to NotePad using C#

Read and Write data from and to NotePad

Is this possible by C#? Yes, this is possible by C#. Newtonsoft.Json.dll is needed for this. 

Well a ViewModel is need to be declared and data will be read or written from or to notepad as a type of this ViewModel.

Writing an Object data into a notepad

public static void WrieDataToNotePad(string filePath, Object obj)

        {

            StreamWriter writer = new StreamWriter(filePath);

            string jsonData = JsonConvert.SerializeObject(obj);

            writer.Write(jsonData);

            writer.Close();

           

        }

Example:-

NotePadInteractor.WrieDataToNotePad(AppDomain.CurrentDomain.BaseDirectory + "AppData.txt", new NotePadData { RunApp=true,ModifiedMonth=DateTime.Today.Month});

Here NotePadData is a ViewModel.

If "AppData.txt" is not available in the above location(filePath), it’ll create a file with this name in that location. Data will be stored in JSON format in the .txt file and can be used later in an application as required.

Note:- JsonConvert.SerializeObject(obj) is a part of Newtonsoft.Json.dll 

Reading an Object data from a notepad

public static NotePadData ReadNotePadData(string filePath)

        {

            try

            {

              StreamReader reader = new StreamReader(filePath);

              string jsonData = reader.ReadToEnd();

              reader.Close();

              return JsonConvert.DeserializeObject<NotePadData>(jsonData);

            }

            catch(Exception ex)

            {

                return null;

               throw ex;

            }

           

        }

If location provided in the filePath doesnot contain a file, with the given name then file not found exception will be encountered. So in order to handle this exception, codes need to be written in the catch block.

Type of ViewModel need to be given in this function JsonConvert.DeserializeObject<ViewModelName>(jsonData);

Obviously, this function is a part of Newtonsoft.Json.dll

How to Write Object Data to an XML File using C#

Write Object Data to an XML File using C# 

This example writes the object from a class to an XML file using the XmlSerializer class.This code example defines a class named Book, creates an instance of the class, and uses XML serialization

to write the instance to an XML file.

Example:-

public class XMLWrite

{

         static void Main(string[] args)

           {

                  WriteXML();

            }

               public class Book

               {

                    public String title {get; set; };

                }

                

                 public static void WriteXML()

                {

                    Book overview = new Book();

                    overview.title = "Serialization Overview";

                    System.Xml.Serialization.XmlSerializer writer =

                       new System.Xml.Serialization.XmlSerializer(typeof(Book));

                   System.IO.StreamWriter file = new System.IO.StreamWriter(

                                      @"c:\temp\SerializationOverview.xml");

                   writer.Serialize(file, overview);

                   file.Close();

                }

}

Security

This example creates a new file, if the file does not already exist. If an application needs to create a file, that application needs Create access for the folder. If the file already exists, the application needs only Write access, a lesser privilege. Where possible, it is more secure to create the file during deployment, and only grant Read access to a single file, rather than Create access for a folder.

How to Read Object Data from an XML File using C#

Reading Object Data from an XML File using C#

This example reads object data that was previously written to an XML file using the XmlSerializer class.

public class Book

{

        public String title{get; set;};

}

public void ReadXML()

{

System.Xml.Serialization.XmlSerializer reader =

new System.Xml.Serialization.XmlSerializer(typeof(Book));

System.IO.StreamReader file = new System.IO.StreamReader(

@"c:\temp\SerializationOverview.xml");

Book overview = new Book();

overview = (Book)reader.Deserialize(file);

Console.WriteLine(overview.title);

}

Note:- 

By using this method only simple ViewModel data can be read,

Opening password protected Excel2013 file using Aspose.cells

How to open Password protected Excel file ?

Hey! I am not going to hack or any thing like that, Of course password is needed to open the file. 

For this latest version of Aspose.cells.dll is needed.

This version  Aspose.Cells for .NET v8.5.0.2 supports this feature, you can download the same.

This is the code to do this programmatically.

            LoadOptions loadoptions = new LoadOptions( );            

            loadoptions.Password = "umesh1";    

            string filepath="C:\\Dev\\MyExcel.xlsx";       

            Workbook workbook = new Workbook(filePath, loadoptions);

Use Excel Sheet as DataBase

How to use Excel sheet as DataBase?

Excel sheet can be used as DataBase. Here it goes.

Latest version of Aspose.cells needed.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Aspose.Cells;

namespace BirthDay

{

    public class Decider

    {

        public static string GetRecord()

        {

            string nameList = string.Empty;

            int startColumnDOB = 2;

            int startRowDOB = 1;

            int countExit = 0;

            string filePath = System.AppDomain.CurrentDomain.BaseDirectory;

            string fileName = System.Configuration.ConfigurationSettings.AppSettings["ExcelFileName"];          

            filePath = filePath + fileName;

            Workbook wb = new Workbook(filePath);

            Worksheet ws = wb.Worksheets[0];

            Cells cells = ws.Cells;

            IList<Person> personList = new List<Person>();

            int r = startRowDOB;

            int c = startColumnDOB;

            while (countExit < 5)

            {

                string cellValue = cells[r, c].StringValue;

                if (string.IsNullOrWhiteSpace(cellValue))

                {

                    countExit++;

                    continue;

                }

                string[] dob = cellValue.Split('/');

                int month = 0; 

                int.TryParse(dob[0], out month);

                int day=0;

                int.TryParse(dob[1], out day);

                DateTime dateToday = DateTime.Now.Date;

                if ((month == dateToday.Month) && (day == dateToday.Day))

                {

                    Person person = new Person();

                    person.Id = decimal.Parse(string.IsNullOrWhiteSpace(cells[r, c - 2].StringValue) ? "0" : cells[r, c - 2].StringValue);

                    person.Name = cells[r, c - 1].StringValue;

                    person.DOB = dateToday;

                    nameList += person.Name + Environment.NewLine;

                }

                r++;

            }

            return nameList;

        }

    }

}

For security purpose password protected ExcelSheet can be used.

Download the Sample Project here.

Scheduling Task/Application using C#

How to run a program on a scheduled  basis?

Well this can be done using TaskScheduler of Window.

Microsoft.Win32.TaskScheduler.dll is needed for this

Download the Sample Project here.

The code goes here

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Microsoft.Win32.TaskScheduler;

namespace Event.Schedule

{

    public class Scheduler

    {

        public static void ScheduleTask()

        {

            // Get the service on the local machine

            using (TaskService ts = new TaskService())

            {               

                // Create a new task definition and assign properties

                TaskDefinition td = ts.NewTask();

                td.RegistrationInfo.Description = "Umesh Does something";

                // Create a trigger that will fire the task at this time every other day

                DailyTrigger d = new DailyTrigger();

                d.DaysInterval = 1; // For daily Execution of Task

                d.StartBoundary = DateTime.Now.Date.AddHours(10);//Executes the Task daily at 10AM

                td.Triggers.Add(d);

                // Create an action that will launch another program or Task whenever the trigger fires

                td.Actions.Add(new ExecAction("C:\\Dev\\BirthDay\\BirthDay\\bin\\Debug\\MyApplication.exe"));

                // Register the task in the root folder

                ts.RootFolder.RegisterTaskDefinition("Umesh", td);

                // Remove the task we just created

                 ts.RootFolder.DeleteTask("Umesh");

            }

        }

    }

}

How to check the status of the Task/Task Details?

1. Go to Run, Type taskschd.msc + Enter

2. Now check the image below, you will get it.

SQL Server Books

Books on SQL Server

Book1

Book2

SQL Server IF Exists

CheckIf Column Exists in SQL Server Table

Method 1

IFEXISTS(SELECT*FROMsys.columns

WHEREName=N'columnName'ANDOBJECT_ID=OBJECT_ID(N'tableName'))

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFEXISTS(SELECT*FROMsys.columns

WHERE Name = N'Name' AND OBJECT_ID =OBJECT_ID(N'[HumanResources].

[Department]'))

BEGIN

PRINT'YourColumnExists'

END

Method 2

IFCOL_LENGTH('table_name','column_name')ISNOTNULL

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFCOL_LENGTH('[HumanResources].[Department]','Name')ISNOTNULL

BEGIN

PRINT'YourColumnExists'

END

Method 3

IFEXISTS(

SELECTTOP1*

FROMINFORMATION_SCHEMA.COLUMNS

WHERE[TABLE_NAME]='TableName'

AND[COLUMN_NAME]='ColumnName'

AND[TABLE_SCHEMA]='SchemaName')

BEGIN

PRINT'YourColumnExists'

END

For AdventureWorks sample database

IFEXISTS(

SELECTTOP1*

FROMINFORMATION_SCHEMA.COLUMNS

WHERE[TABLE_NAME]='Department'

AND[COLUMN_NAME]='Name'

AND[TABLE_SCHEMA]='HumanResources')

BEGIN

PRINT'YourColumnExists'

END

SQL Examples

--1st table
create table community1234_832249(ID int IDENTITY (12,1) not null primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249



--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249



--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 



--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'

----Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.

create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345

-- Select the Subject line details & Employee details of those who posted more than once,

  --on behalf of the Learning & Development Community in the sorting order of Date. 
alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.
create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

--search
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694

--modify
create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249

GETDATE() Returns the current date and time
NOW() Returns the current date and time
ALTER TABLE Persons
DROP COLUMN DateOfBirth
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
TRUNCATE TABLE table_name
DROP TABLE table_name
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;


SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;



SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

select distinct m.name from students s
inner join mentor_ralationships mr on mr.student_id=s.student_id
inner join mentors m on m.mentoir_id=mr.mentor_id
where s.teacher_id=1;


SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID   


extra
--1st table
create table community12341_832249(ID int primary key,CName varchar(30) not null);
select * from community1234_832249
create proc comm1234_832249(@Name varchar(30),@ID int out)
as
begin
insert into community1234_832249(CName) values(@Name)
set @ID=@@identity
end
Declare @result int 
Execute comm1234_832249 'Microsoft',@ID=@result output
Execute comm1234_832249 'Java',@ID=@result output
Execute comm1234_832249 'BIPM',@ID=@result output
Execute comm1234_832249 'Learning & Development',@ID=@result output
print @result
select * from community1234_832249

--2nd tble
create table Employee123_832249(EmployeeID bigint primary key,Name varchar(30),Role varchar(30),Password varchar(30));
select * from Employee123_832249
create proc Emp_832249(@EmployeeID bigint,@Name varchar(30),@Role varchar(30),@Password varchar(30))
as
begin
insert into Employee123_832249 values(@EmployeeID,@Name,@Role,@Password)
end
Execute Emp_832249 402510,'Zacharia','HR','tcs@123'
Execute Emp_832249 520114,'Milow Neo J','LF','tcs@123'
Execute Emp_832249 320694,'Salli Ben','LF','tcs@123'
Execute Emp_832249 281354,'Mathew','HR','tcs@123'

select * from Employee123_832249

--3rd table
create table Employee_Community_Subject12_832249 (CommunityID int foreign key references  community123_832249(ID), 
EmployeeID bigint foreign key references Employee123_832249(EmployeeID) , Subject varchar(30), DateOfPosting datetime );
select * from Employee_Community_Subject12_832249 
create proc Emp_comm1_832249(@CommunityID int,@EmployeeID bigint,@Subject varchar(30),@DateOfPosting datetime)
as
begin
insert into Employee_Community_Subject12_832249  values(@CommunityID,@EmployeeID,@Subject,@DateOfPosting)
end
Execute Emp_comm1_832249 12,320694,'Microsoft & VSTO','08/12/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','09/13/13'
Execute Emp_comm1_832249 15,520114,'Self Discipline & Persistance','10/10/13'
Execute Emp_comm1_832249 13,320694,'Comapatibility with Java Swing','11/15/13'
Execute Emp_comm1_832249 15,281354,'Monday Motivator','12/14/13'
select * from Employee_Community_Subject12_832249 

--Display the Employee & community details while validiting the login credentials using employeId & Passoword.
/*create proc create12_swati
as
begin
select E.EmployeeID,E.Name,E.role,E.Password,R.CommunityID from Employee123_832249 as E inner join 
Employee_Community_Subject12_832249 as R on E.EmployeeID=R.EmployeeID
end
Execute create12_swati*/


create proc create1234
(@Empid bigint,@password varchar(30))
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password='tcs@123')as D
on D.CommunityID=C.ID
end
 
 Execute create1234 320694,'tcs@123'



/*select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 
as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=281354 and E.Password='tcs@123'

select * from Employee_Community_Subject12_832249;
select CName,ID from community1234_832249;


create proc create123
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join (select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where E.EmployeeID=@Empid and E.Password=@password)as D
on D.CommunityID=C.ID
end
Execute create123

create proc login1
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,E.Name from  Employee123_832249 as E 
where EmployeeID=@EmployeeID and Password=@Password
end
Execute login1 402510,'tcs@123'
create proc login12
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Role,C.Subject,C.DateOfPosting from  Employee_Community_Subject12_832249  as C inner join Employee123_832249 as E on C.EmployeeID=E.EmployeeID 
where E.EmployeeID=@EmployeeID and Password=@Password
end
Execute login12 402510,'tcs@123'
Execute login12 520114,'tcs@123'
Execute login12 281354,'tcs@123'

create proc login123
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where in (select C.ID from community123_832249 
as C on C.ID = R.CommunityID)
end
create proc login1233
(@EmployeeID bigint,@Password varchar(30))
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where E.EmployeeID=@EmployeeID and Password=@Password 
end
Execute login1233 520114,'tcs@123'
Execute login1233 320694,'tcs@123'*/
--Display the Employee details & Subject details for the posts from 13 sept 2013 to tilldate considering
 --different communities & subject line starting with M & ending with any charecter.
 /*create proc Empp_1

 as
 begin
 select E.Name,E.Role,R.Subject from 
  Employee123_832249 as E inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID 
  where R.Subject like 'M%' and R.DateofPosting>'09/13/13'
 end
 Execute Empp_1*/

 create proc abc12345
 as
 begin
 select D.CommunityID,D.Subject,D.DateofPosting,D.EmployeeID,C.CName from community1234_832249 as C inner join 
 (select S.CommunityID,S.Subject,S.DateofPosting,S.EmployeeID from  Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.Subject like 'M%' and S.DateofPosting between 
'09/13/14' and getDate()) as D on C.ID=D.CommunityID where C.CName like 'M%'
 end
 Execute abc12345


 -- Select the Subject line details & Employee details of those who posted more than once,
  --on behalf of the Learning & Development Community in the sorting order of Date. 
  
  create proc check12
  as 
  begin
  select count(R.EmployeeID),
  E.Name,E.Role,R.Subject from Employee123_832249 as E
  inner join Employee_Community_Subject12_832249 as R on R.EmployeeID=E.EmployeeID inner join 
  community123_832249 as C on C.ID=R.CommunityID
  where C.Name='Learning & Development'
  group by 
  having count(R.EmployeeID)>1
  end
 
  create proc check12
  as 
  begin
  select CommunityID,count(EmployeeID),
  from  Employee_Community_Subject12_832249 as R inner join community123_832249 as C on C.ID=R.CommunityID
  inner join 
  where C.CName='Learning & Development'
  group by CommunityID
  having count(EmployeeID) > 1
  end

  alter proc xyz12
  as
  begin
 select E.EmployeeID,E.Name,D.CommunityID from Employee123_832249 as E 
inner join (select C.ID,C.CName,S.CommunityID, 
 S.EmployeeID  from community1234_832249 as C inner join Employee_Community_Subject12_832249 as S
on C.ID=S.CommunityID  group by S.EmployeeID,S.CommunityID,
C.ID,C.CName having count(S.EmployeeID)>1 ) as D on E.EmployeeID=D.EmployeeID
 where D.CName='Learning_and_Development'
 end
 Execute xyz12

--Display the Community detail with the Employee details who haven't posted anything.

  create proc abc1
  as
  begin
  select C.Name,E.Name,E.Role,R.Subject from Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID inner join  community123_832249 
as C on C.ID = R.CommunityID where R.DateofPosting=null
  end
  Execute abc1

create proc create123456
as
begin
select D.Name,C.CName,D.Role,D.EmployeeID,D.Subject,D.CommunityID from community1234_832249 as C inner join 
(select E.Name,E.Role,E.EmployeeID,S.CommunityID,S.Subject from Employee_Community_Subject12_832249 as S inner join 
Employee123_832249 as E on S.EmployeeID=E.EmployeeID where S.DateofPosting IS NULL)as D
on D.CommunityID=C.ID
end
Execute create123456

 
 

   --Create a console application for the Modification & Search of the Employees given in the above Employee Table. Search the Employee with the EmployeeId & display the details. Then Modify the
--employee record for the Relevant fields. 
create proc search
(@EmployeeID bigint)
as
begin
select E.Name,E.Role,R.Subject,R.DateofPosting from  Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.EmployeeID=@EmployeeID
end
Execute search 281354


create proc modify
(@Name varchar(30),@Role varchar(30),@Subject varchar(30),@DateofPosting DateTime)
as
begin
update Employee_Community_Subject12_832249 as R inner join 
Employee123_832249 as E on R.EmployeeID=E.EmployeeID where E.Name=@NameE.Role=@Role, R.Subject=@Subject, R.DateofPosting=@DateofPosting
end
create proc search1
(@EmployeeID bigint)
as
begin
select D.EmployeeID,E.Name,E.Role,D.DateofPosting,D.Subject from Employee123_832249 as E inner join (select S.EmployeeID,C.CName,S.Subject,S.DateofPosting from  community1234_832249 as C inner
join Employee_Community_Subject12_832249 as S on C.ID=S.CommunityID) as D on D.EmployeeID=E.EmployeeID where D.EmployeeID=@EmployeeID
end
Execute search1 320694



create proc modify1
(@empid bigint, @Name varchar(30),@Role varchar(30),@password varchar(30))
as
begin
update Employee123_832249 set Name=@Name,Role=@Role,Password=@password where EmployeeID=@empid
end

Execute modify1 320694,Swati,Lecturer,swati
select * from Employee123_832249

ADO.NET DISCONNECTED MODEL

Disconnected Architecture

Data Adapter

Dataset

Steps to access data using Disconnected Model

Steps to perform manipulations in Disconnected Model

Important methods of DataRow:

1.  Contains(PKCOLvalue) : it used to search for a record based on the primary key value. If the record exists, it returns true otherwise false.

2.  Find(PKColValue) : it used to search for a record based on primary key column value. If the record exists, it returns row object otherwise -1 (error msg).

 Steps to search for a record in data table

1.   Define a reference variable for the datarow.

   Syntax:   DataRow dr;

2.   Search for a record in the datatable based on the given value and assign it to the DataRow object.

   Syntax: 

    dr= ds.Tables[TableName / TableIndex].Rows.Find(PKColValue);

3.   Retrieve the column values from the DataRow object and display them.

    syntax:                    textBoxname.Text=dr["columnname"/ColumnIndex];

Steps to insert the record into the datatable:

1.  Define the datarow object.

Syntax:   DataRow dr;

2.  Assign the new row of the dataset table to the row object

Syntax:

                        dr= datasetname.tablename.NewRow();

3.  Assign the values to the columns of the datarow.

Syntax:

dr[columnname/colindex]=value;

4.  Add the datarow to the dataset's table.

Syntax:

ds.tablename.Rows.Add(dr);

5.  Update the dataadapter with the dataset table.

Syntax:

daname.Update(datasetname,tablename);

Steps to Update the record

1.          Define the datarow object.

Syntax:   DataRow dr;

2.          Assign the row which has to modified to the datarow object.

    Syntax:   dr= ds.Tables[TableName / TableIndex].Rows.Find(pkcolvalue);

3.          Call the BeginEdit() on the datarow object.

  Syntax:   dr.BeginEdit();

4.          Assign the new values for the columns which have to be modified.

     Syntax:   dr[colname/colindex]=new value;

5.          Call the EndEdit() on the datarow.

      Syntax:   dr.EndEdit()

6.          Call Update() on the dataadpater to update these changes in the database table.

        Syntax:   da.Update(dsname,tablename);

Steps to delete a record

1.  Call the delete() on the record which has to be deleted.

      Syntax:   ds.Tables[TableName / TableIndex].Rows.Find(pkcolvalue).Delete();

2.  Call the Update() on the dataadapter to update the changes in the database table.

      Syntax:   da.Update(dsname,tablename);

Problem Scenario

E-Cognizance Mobile Company is a global mobile manufacturer, whose sales run in tens of thousands handsets every day, globally. They are providing a default warranty on the phone for one year. Faults of these handsets are repaired by the manufacturer's Authorized Service Vendor (ASV) in cities.

The ASV of the E-Cognizance Mobile Company makes the claim for repair and submits for review. He can also check for the status of the claims based on Claim ID and can update / delete the claim details, if the Claim Status is Submitted. 

Following fields are required to work with the Claim Details:

Claim ID - int, 

Model number - String, 

Model Type ('Basic' / 'Smartphone' / 'Business') - String, 

IMEI number (a unique 16-digit number of the mobile phone) - long, 

Part Cost - double,  Labor Cost - double,  and Status (Submitted / Accepted / Rejected ) - String.

Approach to solve the problem

1.     Create a database table to maintain the Claim Details.

2.     Use disconnected model as ASV is the only person who can add, edit, view and delete the claim details from the database.

3.     Retrieve all the Claim Details from database and store it in a Dataset for easy access and to perform maninpulations.

4.     When the Claim ID is entered and click on Find button, the claim details should be displayed in corresponding controls.

5.     When new claim details are submitted, it should generate a unique Claim ID and its status should be updated to Submitted.

6.     Editing and deleting the claim details from database should be done based on the Claim ID.

Solution

using System.Data;

using System.Data.SqlClient;  using System.Configuration; 

      

      SqlDataAdatper da;

      DataSet ds;

Page_Load():

      SqlConnection connection = new

SqlConnection(ConfigurationManager.ConnectionStrings["DBCon"].ConnectionString()); adapter = new SqlDataAdapter("Select * from tblClaims", connection);                 

ds = new DataSet();                 

adapter.Fill(ds);

      ds.Tables[0].Constraints.Add("pk_depid", ds.Tables[0].Columns[0], true);

      SqlCommandBuilder cmb = new SqlCommandBuilder(adapter);

btnFind_Click

            if (ds.Tables[0].Rows.Contains(txtClaimId.Text))

            {

                DataRow row = ds.Tables[0].Rows.Find(txtClaimId.Text);

                txtModelNo.Text = row[1].ToString();

                txtIMEI.Text = row[2].ToString();

                -------------

                -------------

            } 

            else

            {

                txtModelNo.Text = "";

                txtIMEI.Text = "";

          lblStatus.Text = "No Details found";

            }

btnAddNew_Click

                  txtModelNo.Text = "";

                  txtIMEI.Text = "";

                  int lastRowIndex = ds.Tables[0].Rows.Count - 1;      

                  int newClaimId = (int)ds.Tables[0].Rows[lastRowIndex][0] + 1;

                  txtClaimId.Text = newClaimId.ToString();

                  txtClaimId.ReadOnly = true;

                   

btnSave_Click    

               txtClaimId.ReadOnly=false;           

               DataRow row = ds.Tables[0].NewRow();

         row[0] = this.txtClaimId.Text;                                row[1] = this.txtModelNo.Text;                              row[2] = this.txtIMEI.Text;                                    ds.Tables[0].Rows.Add(row);                                adapter.Update(ds);

               lblStatus.Text = "Details are saved successfully";

btnModify_Click

      DataRow row = ds.Tables[0].Rows.Find(this.txtClaimId.Text);                   row.BeginEdit();             

      row[1] = this.txtModelNo.Text;             

      row[2] = this.txtIMEI.Text;            

      row.EndEdit();             

      adapter.Update(ds);

            lblStatus.Text = "Details are modified successfully";

        

btnRemove_Click

            ds.Tables[0].Rows.Find(this.txtClaimId.Text).Delete(); 

            adapter.Update(ds);

      lblStatus.Text = "Details are removed successfully";                     this.txtClaimId.Text = ""; 

      this.txtModelNo.Text = "";

            this.txtIMEI.Text = "";

        }

Explanation about the solution

Step 1: Include all the required namespaces

Step 2: Store the connection string in web.config file because all these actions need to connect to the same database.

Step 3: DataAdapter and DataSet are declared as Class level members as these are required for each CRUD operation.

Step 4: As DataAdapter is defined with only Select Command, we can’t perform manipulations. So, CommandBuilder object is used to generate other commands to do manipulations. Primary key is defined before generating the CommandBuilder object, to generate all Commands.

ADO.NET – CONNECTED MODEL

ADO.Net

ADO.NET is the managed data access API. .Net data provider provides the facility to interact with database. It has the classes Reader, Adapter to fetch data from database. It uses connection class to connect to the database and it uses command class to execute SQL commands

SqlConnection – Used to specify the connection string. Following statement is used to specify

connection string and create an object of SqlConnection

string ConnectionString = "Data Source=ServerName;" + 

"Initial Catalog=DataBaseName;" + 

"User id=UserName;" + 

"Password=Secret;";

SqlConnection connection = new SqlConnection(ConnectionString);

//Connection is opened using Open method

 connection.Open();

 //Connection should be closed after use. Its done using close method

 connection.Close();

SqlCommand:

Used to execute SQL Command. Command object is created . Command type can be text or stored procedure or Table Direct. Command Text is sql query if command type is text. Its stored procedure name if command type is stored procedure and its table name if command type is stored procedure.

//create command object

 SqlCommand command = new SqlCommand();

 //Set command type as stored procedure

 command.CommandType = CommandType.StoredProcedure;

 //Command text is stored procedure name if command type is stored procedure

 command.CommandText = "procedureName";

 //Used to relate command object to connection

 command.Connection = connection;

SQL Parameter: 

Used to required add parameters to stored procedure. Following code is used if the procedure has input parameter. Parameter name is specified within “” and value is passed to the procedure.

 command.Parameters.AddWithValue("@id", id);

Executing command:

Command is executed using following methods

1) ExecuteNonQuery:

Used for insert/update/delete statements. Execute NonQuery returns the number of rows affected

eg.int rowsAffected = command.ExecuteNonQuery();

2) ExecuteReader – Used for select query. Execute Reader returns reader object. We use Read method to read from reader and we can fetch details using reader object. Column name is specified in reader object to fetch the details of the required column eg.reader["id"]. Alternatively we can provide index we can use reader[0],reader[1] and so on.

eg

// Execute procedure with select query

 SqlDataReader reader= command.ExecuteReader();

 List<Customer> customerList = new List<Customer>();

 //Read row by row from reader object

 while(reader.Read())

 {

 int id = Convert.ToInt32(reader["id"]);

 string name = reader["name"].ToString();

 Customer customer = new Customer(id, name);

 customerList.Add(customer);

 }

3) ExecuteScalar – Used when select query returns a single value

eg int maxUser = Convert.ToInt32(command.ExecuteScalar());

Sample

Create a customer table with id, name, contact number and location. And do the following 

1. Add customer and return the autogenerated customer id

2. View all the customers

3. Retrieve contact number for a customer

Following is the sql code for the given scenario

CREATE TABLE tblCustomer(

[customerid] [int] IDENTITY(1,1) primary key,

[name] [varchar](30),

[contactNo] [bigint],

[Location] [varchar](30)

)

create proc sp_viewCustomer

as

select * from tblCustomer

create proc sp_viewCustomerbyId

(@id int)

as

begin

select contactNo from tblCustomer

where customerid=@id

end

create proc sp_insertCustomer

(@name varchar(30),

@contactNo bigint,

@location varchar(30),

@id int out)

as

begin

insert into tblCustomer

values(@name,@contactNo,@location)

set @id=@@identity

end

Following is the customer Class

//Customer Class

 public class Customer

 {

 //Attributes

 int _customerId;

 string _name;

 int _contactNo;

 string _location;

 //properties

 public int CustomerId { get { return _customerId; } set { _customerId = 

value; } }

 public string Name { get { return _name; } set { _name = value; } }

public int ContactNo { get { return _contactNo; } set { _contactNo = 

value; } }

 public string Location { get { return _location; } set { _location = 

value; } }

 //constructor

 public Customer(int id, string name, int contactNo, string location)

 {

 _customerId = id;

 _name = name;

 _contactNo = contactNo;

 _location = location;

 }

 public Customer(string name, int contactNo, string location)

 {

 _name = name;

 _contactNo = contactNo;

 _location = location;

 }

Following is the customer DB class which contains seperate methods for each operation

public class CustomerDB

 {

 public List<Customer> getCustomers()

 {

 string ConnectionString = "Data Source=ServerName;" +

 "Initial Catalog=DataBaseName;" + "User id=UserName;" +"Password=Secret;";

 SqlConnection connection = new SqlConnection(ConnectionString);

 //Connection is opened using Open method

 connection.Open();

 //create command object

 SqlCommand command = new SqlCommand();

 //Set command type as stored procedure

 command.CommandType = CommandType.StoredProcedure;

 //Command text is stored procedure name if command type is stored 

procedure

 command.CommandText = "sp_viewCustomer";

 //Used to relate command object to connection

 command.Connection = connection;

 // Execute procedure with select query

 SqlDataReader reader= command.ExecuteReader();

 List<Customer> customerList = new List<Customer>();

 //Read row by row from reader object

 while(reader.Read())

 {

 int id = Convert.ToInt32(reader["customerid"]);

 string name = reader["name"].ToString();

 Customer customer = new Customer(id, 

name,Convert.ToInt32(reader["contactNo"]),reader["Location"].ToString());

 customerList.Add(customer);

 }

 //Connection should be closed after use. Its done using close method

 connection.Close();

return (customerList);

 public int geCustomersContactNo(int id)

 string ConnectionString = "Data Source=ServerName;" +

 "Initial Catalog=DataBaseName;" +

"User id=UserName;" +

"Password=Secret;";

SqlConnection connection = new SqlConnection(ConnectionString);

 //Connection is opened using Open method

 connection.Open();

 //create command object

 SqlCommand command = new SqlCommand();

 //Set command type as stored procedure

 command.CommandType = CommandType.StoredProcedure;

 //Command text is stored procedure name if command type is stored procedure

 command.CommandText = "sp_viewCustomerbyId";

 //Used to relate command object to connection

 command.Connection = connection;

 //Used to pass parameter to procedure

 command.Parameters.AddWithValue("@id", id);

 // Execute procedure with select query

 int number = Convert.ToInt32(command.ExecuteScalar());

 //Connection should be closed after use. Its done using close method

 connection.Close();

 return number;

 }

public int addCustomers(Customer custObj)

 {

 string ConnectionString = "Data Source=ServerName;" +

 "Initial Catalog=DataBaseName;" +"User id=UserName;" + "Password=Secret;";

 SqlConnection connection = new SqlConnection(ConnectionString);

 //Connection is opened using Open method

 connection.Open();

 //create command object

 SqlCommand command = new SqlCommand();

 //Set command type as stored procedure

 command.CommandType = CommandType.StoredProcedure;

 //Command text is stored procedure name if command type is stored 

procedure

 command.CommandText = "sp_insertCustomer";

 //Used to relate command object to connection

 command.Connection = connection;

 //Used to pass parameter to procedure

 command.Parameters.AddWithValue("@name", custObj.Name);

 command.Parameters.AddWithValue("@contactNo", custObj.ContactNo);

 command.Parameters.AddWithValue("@location", custObj.Location);

 command.Parameters.AddWithValue("@id", 0);

//Denotes that id is a output parameter

 command.Parameters["@id"].Direction=ParameterDirection.Output;

 //Used to execute command

 int rowAffected = command.ExecuteNonQuery();

 //Connection should be closed after use. Its done using close method

 connection.Close();

 // Used to return the value of output parameter

 if (rowAffected > 0)

 return Convert.ToInt32(command.Parameters["@id"].Value);

 else

 return (rowAffected);

 }

Following is the code executed to be written in main method

CustomerDB db=new CustomerDB();

 //Get all customer from getCustomer method in customerDB 

 List<Customer> customerList = db.getCustomers();

 foreach (Customer c in customerList)

 {

 Console.WriteLine("Customer Id: "+c.CustomerId);

 Console.WriteLine("Customer Name: "+c.Name);

 Console.WriteLine("Contact Number: "+c.ContactNo);

 Console.WriteLine("Location: "+c.Location);

 }

 Console.ReadKey();

 //code to insert a data. You can get data from user

 Customer ins=new Customer("Priya",990909090,"Delhi");

 int result=db.addCustomers(ins);

 Console.WriteLine("Customer Added and id is " + result);

 Console.ReadKey();

 //code to fetch contact number of customer inserted now

 int contactNo=db.geCustomersContactNo(result);

 Console.WriteLine(contactNo);

 Console.ReadKey();

Reference

ADO.NET ppt