|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using wcfDuplex.Classes;
using System.Data.SqlClient;
using System.Security.Permissions;
using System.Data;
using wcfDuplex.Model;
namespace wcfDuplex
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Duplex : IDuplexService
{
public void Connect(string id)
{
IDuplexServiceClient client = OperationContext.Current.GetCallbackChannel<IDuplexServiceClient>();
TrainServiceRunner.Register(client);
}
List<Author> authors;
public List<Author> GetEmployeeList(DateTime startDate, DateTime endDate)
{
authors = new List<Author>();
//the connection string to your database
// string connString = "Data Source=DEVELOPER-PC;Initial Catalog=dependencyDB;Integrated Security=True;Timeout=10";
string connString = @"Data Source=SQLSVR2008\SQL2008;Initial Catalog=dependencyDB;Persist Security Info=True;User ID=NotificationsUser;Password=password";
//the name of our stored procedure
string proc = "SelectCredentialsWithinDOBRange";
//first we need to check that the current user has the proper permissions,
//otherwise display the error
return authors;
if (!CheckUserPermissions())
{
Author author = new Author();
author.ID = 0;
author.FirstName = "No";
author.SecondName = "Permission";
author.Address = "To Access DB";
author.DOB = "12/12/2010";
authors.Add(author);
return authors;
}
//clear our ListView so the data isnt doubled up
authors = new List<Author>();
bool success = SqlDependency.Start(connString);
using (SqlConnection sqlConn = new SqlConnection(connString))
{
using (SqlCommand sqlCmd = new SqlCommand())
{
SqlParameter myParm1 = sqlCmd.Parameters.Add("@StartDate", SqlDbType.DateTime, 20);
myParm1.Value = "20/01/1960";
SqlParameter myParm2 = sqlCmd.Parameters.Add("@EndDate", SqlDbType.DateTime, 20);
myParm2.Value = "20/01/2022";
sqlCmd.Connection = sqlConn;
sqlCmd.Connection.Open();
//tell our command object what to execute
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = proc;
sqlCmd.Notification = null;
SqlDependency dependency = new SqlDependency(sqlCmd);
dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);
if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
using (SqlDataReader reader = sqlCmd.ExecuteReader())
{
while (reader.Read())
{
Author author = new Author();
author.ID = reader.GetInt32(0);
author.FirstName = reader.GetString(1);
author.SecondName = reader.GetString(2);
author.Address = reader.GetString(3);
author.DOB = reader.GetDateTime(4).ToString();
authors.Add(author);
}
}
}
return authors;
}
}
private bool CheckUserPermissions()
{
try
{
SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);
//if we cann Demand() it will throw an exception if the current user doesnt have the proper permissions
permissions.Demand();
return true;
}
catch
{
return false;
}
}
private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
{
List<Author> myAuthors = this.GetEmployeeList(DateTime.Now.AddYears(-20), DateTime.Now.AddYears(1));
TrainServiceRunner.watcher_Changed(myAuthors);
//this example is for only a single notification so now we remove the event handler from the dependency object
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);
}
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.