Tips and tricks
Code snippet will loop through all tables of Database SOURCEDB and compare record counts with tables in TARGETDB database
USE SOURCEDB
EXEC SP_MSforeachtable ‘DECLARE @OriCount INT
DECLARE @Count INT
DECLARE @Name VARCHAR(400)
SET @Count = (SELECT COUNT(*) FROM TARGETDB.?)
SET @OriCount = (SELECT COUNT(*) FROM SOURCEDB.?)
SET @Name=”?”
IF(@Count <> @OriCount)
BEGIN
SELECT @Name,@Count as target,@OriCount as source
END‘
Parameterized constructors are not allowed in WCF RIA domain service, however your object model may requires to have constructor with arguments. For example EmployeeService (in this example) is WCF RIA Domain Service which requires Authentication and have constructor with authenticated User instance as parameter. The activate the service in such scenario you need to provide your own custom domain factory calls and plug the factory in Application_Start event of Global.aspx.
Sample Employee Service class
[EnableClientAccess()]
[RequiresAuthentication()]
public class EmployeeService : DomainService
{
public EmployeeService(User user)
{
}
//......
}
Custom Domain service factory
internal sealed class DomainServiceFactory : IDomainServiceFactory
{
private IDomainServiceFactory _defaultFactory;
public DomainServiceFactory(IDomainServiceFactory defaultFactory)
{
_defaultFactory = defaultFactory;
}
public static void Setup()
{
if (!(DomainService.Factory is DomainServiceFactory))
{
DomainService.Factory = new DomainServiceFactory(DomainService.Factory);
}
}
public DomainService CreateDomainService(Type domainServiceType, DomainServiceContext context)
{
if (domainServiceType == typeof(EmployeeService))
{
DomainServiceContext authServiceContext =
new DomainServiceContext(context, DomainOperationType.Query);
AuthenticationService authService =
(AuthenticationService)_defaultFactory.CreateDomainService(typeof(AuthenticationService), authServiceContext);
User user = authService.GetUser();
DomainService domainService = (DomainService)Activator.CreateInstance(domainServiceType, user);
domainService.Initialize(context);
return domainService;
}
else
{
return _defaultFactory.CreateDomainService(domainServiceType, context);
}
}
public void ReleaseDomainService(DomainService domainService)
{
if (domainService is EmployeeService)
{
domainService.Dispose();
}
else
{
_defaultFactory.ReleaseDomainService(domainService);
}
}
}
Setup Domain service factory
protected void Application_Start(object sender, EventArgs e)
{
DomainServiceFactory.Setup();
}
The objective of this article is to discover the presence of Microsoft SQL Server across subnet. There are API’s to enumerate SQL Server instances in single subnet (Win32 API : NetServerEnum ) and Microsoft enterprise manger uses this API to populate the list of SQL server available in current subnet. The API NetServerEnum broadcast UDP packets in the network and SQL server respond the message by sending their details. Since UDP packets can’t cross subnets and hence it will only return the partial list in particular domain.
I was working on project where I have to find if on given IP or IP range , any sql server exists or not and if sql server exists I need to find the instance names. etc.
The SQL server discovery module is hosted on web server and will be accessed by Silverlight application via WCF service. The code can be used to determine if SQL server is running or not (Before trying to connect) to build more responsive applications.
The code sends the UDP packet (point to point access) directly to provide ip address on port 1434 and revived the data back from machines.
If server is there the another function connects on TCP channel on TCP IP port to extract Sql server Netlib version.
public class SqlServerInfo
{
public string ServerName { get; private set; }
public string IpAddress { get; private set; }
public string InstanceName { get; private set; }
public bool IsClustered { get; private set; }
public string Version { get; private set; }
public int tcpPort { get; private set; }
public string NamedPipe { get; private set; }
public string Rpc { get; private set; }
public bool IsActive { get; private set; }
static public List<SqlServerInfo> DiscoverSQLServer(string[] possibleIPs, bool requiredDeepCheck)
{
List<SqlServerInfo> servers = new List<SqlServerInfo>();
foreach (string ip in possibleIPs)
{
servers.AddRange(SqlServerInfo.DiscoverSQLServer(ip));
}
return servers;
}
static public List<SqlServerInfo> DiscoverSQLServer(string ip)
{
Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
socket.EnableBroadcast = true;
socket.ReceiveTimeout = 1000;
List<SqlServerInfo> servers = new List<SqlServerInfo>();
try
{
byte[] msg = new byte[] { 3 };
IPEndPoint ep = new IPEndPoint(IPAddress.Parse(ip), 1434);
socket.SendTo(msg, ep);
int cnt = 0;
byte[] bytBuffer = new byte[64000];
do
{
cnt = socket.Receive(bytBuffer);
string s = System.Text.ASCIIEncoding.ASCII.GetString(bytBuffer, 3, BitConverter.ToInt16(bytBuffer, 1));
string[] parts = s.Split(new string[] { ";;" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s1 in parts)
{
SqlServerInfo sInfo = new SqlServerInfo(s1);
sInfo.IpAddress = ip;
if (sInfo.CheckIsActive())
{
servers.Add(sInfo);
}
}
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
} while (cnt != 0);
}
catch
{
}
finally
{
socket.Close();
}
return servers;
}
private SqlServerInfo()
{
}
public string SSNetlibVersion(string remoteIP, int port)
{
string str = "";
try
{
TcpClient client = new TcpClient();
client.SendTimeout = 300;
client.ReceiveTimeout = 300;
client.Connect(remoteIP, port);
NetworkStream stream = client.GetStream();
byte[] buffer = new byte[] {
0x12, 1, 0, 0x34, 0, 0, 0, 0, 0, 0, 0x15, 0, 6, 1, 0, 0x1b,
0, 1, 2, 0, 0x1c, 0, 12, 3, 0, 40, 0, 4, 0xff, 8, 0, 1,
0x55, 0, 0, 0, 0x4d, 0x53, 0x53, 0x51, 0x4c, 0x53, 0x65, 0x72, 0x76, 0x65, 0x72, 0,
4, 8, 0, 0};
stream.Write(buffer, 0, buffer.Length);
byte[] buffer2 = new byte[0xff];
string str2 = string.Empty;
int count = stream.Read(buffer2, 0, buffer2.Length);
str2 = Encoding.ASCII.GetString(buffer2, 0, count);
string[] strArray = new string[] { buffer2[0x1d].ToString(), ".", buffer2[30].ToString(), ".", ((buffer2[0x1f] * 0x100) + buffer2[0x20]).ToString() };
str = string.Concat(strArray);
if (str.Substring(0, 1) == "0")
{
str = "";
}
}
catch
{
}
return str;
}
private bool CheckIsActive()
{
string version= SSNetlibVersion(this.IpAddress, this.tcpPort);
if (version.Length == 0)
{
this.IsActive = false;
return false;
}
else
{
this.IsActive = true;
this.Version = version;
return true;
}
}
private SqlServerInfo(string info)
{
string[] nvs = info.Split(';');
for (int i = 0; i < nvs.Length; i += 2)
{
switch (nvs[i].ToLower())
{
case "servername":
this.ServerName = nvs[i + 1];
break;
case "instancename":
this.InstanceName = nvs[i + 1];
break;
case "isclustered":
this.IsClustered = (nvs[i + 1].ToLower() == "yes"); //bool.Parse(nvs[i+1]);
break;
case "version":
this.Version = nvs[i + 1];
break;
case "tcp":
this.tcpPort = int.Parse(nvs[i + 1]);
break;
case "np":
this.NamedPipe = nvs[i + 1];
break;
case "rpc":
this.Rpc = nvs[i + 1];
break;
}
}
}
}
using the code
List<SqlServerInfo> sqlSrv = null;
sqlSrv = SqlServerInfo.DiscoverSQLServer(address);
//Or
sqlSrv = SqlServerInfo.DiscoverSQLServer(
new string[]{ address+ ".01",
address+ ".02",
address+ ".03",
address+ ".04",
address+ ".05",
address+ ".06",
address+ ".07",
address+ ".08",
address+ ".09",
address+ ".10",
address+ ".21",
address+ ".51",
address+ ".100"},true);
Data Grid Ex Support two new events
Also supports single row double click.
Event Commit will be raise if user double click on row or press enter while row is selected where as event Cancel is fired if user click on Esc button.
Class DataGridEx :
using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.Windows.Automation.Peers;
using System.Linq;
namespace XXX.YYY.Controls
{
public class DataGridEx : DataGrid
{
private DataGridRow _LastDataGridRow = null;
private DataGridColumn _LastDataGridColumn = null;
private DataGridCell _LastDataGridCell = null;
private object _LastObject = null;
private DateTime _LastClick = DateTime.MinValue;
private double _DoubleClickTime = 1500;
///
/// An event that indicates that a selection is complete and has been
/// made, effectively a commit action.
///
public event RoutedEventHandler Commit;
///
/// An event that indicates that the selection operation has been
/// canceled.
///
public event RoutedEventHandler Cancel;
///
/// Initializes a new instance of the DataGridEx class.
///
public DataGridEx()
{
MouseLeftButtonUp += OnGridMouseLeftButtonUp;
}
private void OnGridMouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
DateTime clickTime = DateTime.Now;
DataGridRow currentRowClicked;
DataGridColumn currentColumnClicked;
DataGridCell currentCellClicked;
object currentObject;
//If we've found at least the row,
if (GetDataGridCellByPosition(e.GetPosition(null), out currentRowClicked, out currentColumnClicked, out currentCellClicked, out currentObject))
{
//And the current row is the same as the last row, and is within the timespan, consider it a double-click
bool isDoubleClick = (currentRowClicked == _LastDataGridRow && clickTime.Subtract(_LastClick) <= TimeSpan.FromMilliseconds(_DoubleClickTime));
_LastDataGridRow = currentRowClicked;
_LastDataGridColumn = currentColumnClicked;
_LastDataGridCell = currentCellClicked;
_LastObject = currentObject;
if (isDoubleClick)
{
OnItemDoubleClick(this,null);
}
}
else
{
_LastDataGridRow = null;
_LastDataGridCell = null;
_LastDataGridColumn = null;
_LastObject = null;
}
_LastClick = clickTime;
}
private bool GetDataGridCellByPosition(Point pt, out DataGridRow dataGridRow, out DataGridColumn dataGridColumn, out DataGridCell dataGridCell, out object dataGridObject)
{
var elements = VisualTreeHelper.FindElementsInHostCoordinates(pt, this);
dataGridRow = null;
dataGridCell = null;
dataGridColumn = null;
dataGridObject = null;
if (null == elements || elements.Count() == 0)
{
return false;
}
var rowQuery = from gridRow in elements where gridRow is DataGridRow select gridRow as DataGridRow;
dataGridRow = rowQuery.FirstOrDefault();
if (dataGridRow == null)
{
return false;
}
dataGridObject = dataGridRow.DataContext;
var cellQuery = from gridCell in elements where gridCell is DataGridCell select gridCell as DataGridCell;
dataGridCell = cellQuery.FirstOrDefault();
if (dataGridCell != null)
{
dataGridColumn = DataGridColumn.GetColumnContainingElement(dataGridCell);
}
//If we've got the row, return true - sometimes the Column, DataContext could be null
return dataGridRow != null;
}
private void OnItemDoubleClick(object sender,RoutedEventArgs e)
{
OnCommit(this, e);
}
protected override void OnKeyDown(KeyEventArgs e)
{
HandleKeyDown(e);
if (!e.Handled)
base.OnKeyDown(e);
}
///
/// Process a key down event.
///
/// The key event arguments object.
public void HandleKeyDown(KeyEventArgs e)
{
switch (e.Key)
{
case Key.Enter:
OnCommit(this, e);
e.Handled = true;
break;
case Key.Escape:
OnCancel(this, e);
e.Handled = true;
break;
default:
break;
}
}
///
/// Fires the Commit event.
///
/// The source object.
/// The event data.
private void OnCommit(object sender, RoutedEventArgs e)
{
RoutedEventHandler handler = Commit;
if (handler != null)
{
handler(sender, e);
}
}
///
/// Fires the Cancel event.
///
/// The source object.
/// The event data.
private void OnCancel(object sender, RoutedEventArgs e)
{
RoutedEventHandler handler = Cancel;
if (handler != null)
{
handler(sender, e);
}
}
///
/// Initializes a new instance of a DataGridAutomationPeer.
///
/// Returns a new DataGridAutomationPeer.
public AutomationPeer CreateAutomationPeer()
{
return new DataGridAutomationPeer(this);
}
}
}
LINQ dynamic query code
//Input : str=> string to search some text (First Name,Last Name,Date of Birth(dd/mm/yyyy) or post code) in any order;
//output : result set
str = str.ToLower().Replace(" ", " ").Trim();
IList<vw_patient> patients = App.Patients;
string[] parts = str.Split(" ".ToCharArray(),StringSplitOptions.RemoveEmptyEntries);
Func<vw_patient, bool> predicate = null;
foreach (string part in parts)
{
long retCode = 0;
predicate = delegate(vw_patient p)
{
return p.Pati_FirstName.ToLower().StartsWith(part) ||
p.Pati_SurName.ToLower().StartsWith(part) ||
(long.TryParse(part.Replace("/", ""), out retCode) ? ((p.Pati_DOB.HasValue ? p.Pati_DOB.Value.ToShortDateString().Replace("/", "") : "").Contains(part.Replace("/", ""))) : false) ||
p.Pati_PostCode.Replace(" ","").ToLower().StartsWith(part);
};
patients = patients.Where<vw_patient>(predicate).ToList();
}
this.dgPatients.ItemsSource = new PagedCollectionView(patients);
//PagedCollectionView is used for paging with data grid
Pixytech 