Scenario: in ASP.NET application, in run-time force all SqlDataSource instances connecting to a specified data source.
The basic usage of SqlDataSource control assumes assigning values to ConnectionString and ProviderName in HTML part of Web Form or Web User Control.
<asp:SqlDataSource ID="Source1" runat="server" ConnectionString="<%? ConnectionString:MyDataSource %" ProviderName="<%$ ConnectionStrings:MyDataSource.ProviderName %>" SelectCommand="..." />
Both values can also be assigned in the code-behind.
In my case, user selects a data source on start of the web application, and may switch to another available data source during the session. Following a data source selection all SqlDataSource instances must use this and only this data source. The connection string is stored in session variable "ActiveDbConnection".
This scenario can be quickly realized by subclassing the SqlDataSource control.
Apparently you cannot subclass it from Web User Control because the latter must inherit from System.Web.UI.UserControl, and thus cannot inherit from SqlDataSource.
But you can create ASP.NET Server Control and then change its superclass from WebControl to SqlDataSource. Following that replace the default constructor. Зresuming the class name is CustomSqlDataSource the replacement be the following one
public CustomSqlDataSource()
{
ConnectionString =
HttpContext.Current
.Session["ActiveDbConnection"];
ProviderName = "System.Data.OleDb";
}
In HTML part all asp:SqlDataSource tags need to be replaced with asp:CustomSqlDataSource, and the namespace for the class to be registered. And also -- this is very important -- all ConnectionString and ProviderName attributes are to be removed. Otherwise the default constructor we just added will not be called.
<asp:CustomSqlDataSource ID="Source1" runat="server" SelectCommand="..." />
Friday, 15 March 2013
Friday, 1 March 2013
Clearing filter in gridview when adding new record
Otherwise a new record may immediately become hidden from user. And the user will most likely add another one, and then one more, again and again.
The developer is rarely capable of seeing an application with user's eyes. An hour of data entry each month could benefit one.
The developer is rarely capable of seeing an application with user's eyes. An hour of data entry each month could benefit one.
Tuesday, 19 February 2013
Handling shortcuts in WPF application
This is a sketchy post, to be completed or discarded later on. The objectives was having inheritance and minimizing the instantiation code.
This is the base class.
public class KeyShortcutBase
{
private readonly RoutedCommand _command;
private readonly CommandBinding _commandBinding;
protected KeyShortcutBase()
{
this._command = new RoutedCommand();
this._commandBinding = new CommandBinding(
this._command,
OnExecuted,
OnCanExecute);
}
public void ConnectShortcut(
UIElement host, InputGesture gesture)
{
this.ConnectShortcut(host, new[] { gesture });
}
public void ConnectShortcut(
UIElement host, IEnumerable<InputGesture> gestures)
{
foreach (var gesture in gestures)
{
this._command.InputGestures.Add(gesture);
}
if (host != null)
{
host.CommandBindings.Add(this._commandBinding);
}
}
protected virtual void OnCanExecute(
object sender, CanExecuteRoutedEventArgs e)
{
e.CanExecute = false;
}
protected virtual void OnExecuted(
object sender, ExecutedRoutedEventArgs e)
{
}
}
This is the base class.
public class KeyShortcutBase
{
private readonly RoutedCommand _command;
private readonly CommandBinding _commandBinding;
protected KeyShortcutBase()
{
this._command = new RoutedCommand();
this._commandBinding = new CommandBinding(
this._command,
OnExecuted,
OnCanExecute);
}
public void ConnectShortcut(
UIElement host, InputGesture gesture)
{
this.ConnectShortcut(host, new[] { gesture });
}
public void ConnectShortcut(
UIElement host, IEnumerable<InputGesture> gestures)
{
foreach (var gesture in gestures)
{
this._command.InputGestures.Add(gesture);
}
if (host != null)
{
host.CommandBindings.Add(this._commandBinding);
}
}
protected virtual void OnCanExecute(
object sender, CanExecuteRoutedEventArgs e)
{
e.CanExecute = false;
}
protected virtual void OnExecuted(
object sender, ExecutedRoutedEventArgs e)
{
}
}
This is an extension class allowing shorter calls.
public static class KeyShortcutExtender
{
public static void ConnectShortcut(
this UIElement host,
KeyShortcutBase shortcut,
IEnumerable<InputGesture> gestures)
{
shortcut.ConnectShortcut(host, gestures);
}
public static void ConnectShortcut(
this UIElement host,
KeyShortcutBase shortcut,
InputGesture gesture)
{
shortcut.ConnectShortcut(host, gesture);
}
}
This is a subclass.
public class FindItemShortcut : KeyShortcutBase
{
protected override void OnCanExecute(
object sender,
CanExecuteRoutedEventArgs e)
{
e.CanExecute = true;
}
protected override void OnExecuted(
object sender,
ExecutedRoutedEventArgs e)
{
MessageBox.Show(this.GetType().ToString());
}
}
The subclass is instantiated in WPF Window with a single shortcut.
this.ConnectShortcut(
new FindItemShortcut(),
new KeyGesture(Key.F, (ModifierKeys)6));
... and with multiple shortcuts.
this.ConnectShortcut(
new FindItemShortcut(),
new InputGesture[]
{
new KeyGesture(Key.F, (ModifierKeys)6),
new KeyGesture(Key.S, ModifierKeys.Control),
new MouseGesture(MouseAction.RightDoubleClick)
});
So far so good...
Tuesday, 12 February 2013
Allowing only single instance of WPF application per login session
The intention is preventing user from starting multiple instances of the same application. Repeated run should activate (restore) the application's main window.
Having this task already resolved for Visual FoxPro through using semaphores, I chose same approach for WPF. Using direct calls to Semaphore API functions in .NET makes rather little sense, since the Semaphore Class provides all required functionality.
First step is removing StartupUri in App.xaml and overriding Application.OnStartup method. This allows instantiating application's main window only after checking that no other instances are running.
namespace WpfApplicationRunOnce
{
using System.Windows;
public partial class App
{
protected override void OnStartup(StartupEventArgs e)
{
if (!AppLauncher.RunApp())
{
this.Shutdown(0);
}
}
}
}
Static AppLauncher class either creates instance of the main window or activates the one that already exists.
namespace WpfApplicationRunOnce
{
using System;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading;
public static class AppLauncher
{
private const string ApplicationId = "MyAppUniqueId";
private static MainWindow mainWindow;
internal enum RegisterResult
{
Success,
AlreadyRunning,
Failed
}
private enum ShowWindowMode
{
Maximize = 3,
Restore = 9
}
private enum WindowStyles
{
Maximize = 0x01000000
}
public static bool RunApp()
{
var result = RegisterApp(ApplicationId);
switch (result)
{
case RegisterResult.Success:
mainWindow = new MainWindow();
mainWindow.Show();
return true;
case RegisterResult.AlreadyRunning:
ActivateAppMainWindow();
break;
case RegisterResult.Failed:
break;
}
return false;
}
private static RegisterResult RegisterApp(string appId)
{
try
{
Semaphore.OpenExisting(appId);
return RegisterResult.AlreadyRunning;
}
catch (WaitHandleCannotBeOpenedException)
{
try
{
var semaphore = new Semaphore(1, 1, appId);
return RegisterResult.Success;
}
catch
{
return RegisterResult.Failed;
}
}
}
private static void ActivateAppMainWindow()
{
var current = Process.GetCurrentProcess();
var running = Process.GetProcesses()
.FirstOrDefault(p => p.Id != current.Id
&& p.ProcessName.Equals(
current.ProcessName,
StringComparison.CurrentCultureIgnoreCase));
if (running == null)
{
return;
}
var handle = running.MainWindowHandle;
var windowStyle = GetWindowLong(handle, -16);
ShowWindow(
handle,
(windowStyle & (long)WindowStyles.Maximize) != 0
? ShowWindowMode.Maximize : ShowWindowMode.Restore);
SetForegroundWindow(handle);
}
[DllImport("user32.dll", SetLastError = true)]
private static extern int GetWindowLong(
IntPtr hWnd,
int nIndex);
[DllImport("user32.dll")]
private static extern int SetForegroundWindow(
IntPtr window);
[DllImport("user32.dll")]
private static extern int ShowWindow(
IntPtr window,
ShowWindowMode showMode);
}
}
In the code above, replace ApplicationId value with something less generic and more descriptive for your application. This value must be sufficiently unique to be used as the name for a semaphore.
The RegisterApp method attempts to open existing named semaphore. The success indicates that an instance of the application is already running. Otherwise (WaitHandleCannotBeOpenedException), the named semaphore is created.
The ActivateAppMainWindow method uses the name of the current process to find its already running instance. The main window of which gets activated -- either maximized or restored to its original size and position.
There are at least three possible scenarios this approach covers:
The executable files in cases 2) and 3) do not need to be byte to byte identical. What matters is the ApplicationId value shared by the two.
The 3) case behaves differently. While multiple instances are still prevented, the running instance's main window does not get activated since the executable files in this case have different names.
* * *
While adding this functionality to WPF PRISM application, I had to make small changes to the implementation. The testing of the named semaphore existence was moved from the RegisterApp method to a separate method called TestInstance.
internal static RegisterResult TestInstance(string appId)
{
try
{
Semaphore.OpenExisting(appId, SemaphoreRights.Synchronize);
return RegisterResult.AlreadyRunning;
}
catch (WaitHandleCannotBeOpenedException ex)
{
return RegisterResult.NotDetected;
}
}
internal static RegisterResult RegisterApp(string appId)
{
try
{
var semaphore = new Semaphore(1, 1, appId);
return RegisterResult.Success;
}
catch
{
return RegisterResult.Failed;
}
}
Having this task already resolved for Visual FoxPro through using semaphores, I chose same approach for WPF. Using direct calls to Semaphore API functions in .NET makes rather little sense, since the Semaphore Class provides all required functionality.
First step is removing StartupUri in App.xaml and overriding Application.OnStartup method. This allows instantiating application's main window only after checking that no other instances are running.
namespace WpfApplicationRunOnce
{
using System.Windows;
public partial class App
{
protected override void OnStartup(StartupEventArgs e)
{
if (!AppLauncher.RunApp())
{
this.Shutdown(0);
}
}
}
}
Static AppLauncher class either creates instance of the main window or activates the one that already exists.
namespace WpfApplicationRunOnce
{
using System;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Threading;
public static class AppLauncher
{
private const string ApplicationId = "MyAppUniqueId";
private static MainWindow mainWindow;
internal enum RegisterResult
{
Success,
AlreadyRunning,
Failed
}
private enum ShowWindowMode
{
Maximize = 3,
Restore = 9
}
private enum WindowStyles
{
Maximize = 0x01000000
}
public static bool RunApp()
{
var result = RegisterApp(ApplicationId);
switch (result)
{
case RegisterResult.Success:
mainWindow = new MainWindow();
mainWindow.Show();
return true;
case RegisterResult.AlreadyRunning:
ActivateAppMainWindow();
break;
case RegisterResult.Failed:
break;
}
return false;
}
private static RegisterResult RegisterApp(string appId)
{
try
{
Semaphore.OpenExisting(appId);
return RegisterResult.AlreadyRunning;
}
catch (WaitHandleCannotBeOpenedException)
{
try
{
var semaphore = new Semaphore(1, 1, appId);
return RegisterResult.Success;
}
catch
{
return RegisterResult.Failed;
}
}
}
private static void ActivateAppMainWindow()
{
var current = Process.GetCurrentProcess();
var running = Process.GetProcesses()
.FirstOrDefault(p => p.Id != current.Id
&& p.ProcessName.Equals(
current.ProcessName,
StringComparison.CurrentCultureIgnoreCase));
if (running == null)
{
return;
}
var handle = running.MainWindowHandle;
var windowStyle = GetWindowLong(handle, -16);
ShowWindow(
handle,
(windowStyle & (long)WindowStyles.Maximize) != 0
? ShowWindowMode.Maximize : ShowWindowMode.Restore);
SetForegroundWindow(handle);
}
[DllImport("user32.dll", SetLastError = true)]
private static extern int GetWindowLong(
IntPtr hWnd,
int nIndex);
[DllImport("user32.dll")]
private static extern int SetForegroundWindow(
IntPtr window);
[DllImport("user32.dll")]
private static extern int ShowWindow(
IntPtr window,
ShowWindowMode showMode);
}
}
In the code above, replace ApplicationId value with something less generic and more descriptive for your application. This value must be sufficiently unique to be used as the name for a semaphore.
The RegisterApp method attempts to open existing named semaphore. The success indicates that an instance of the application is already running. Otherwise (WaitHandleCannotBeOpenedException), the named semaphore is created.
The ActivateAppMainWindow method uses the name of the current process to find its already running instance. The main window of which gets activated -- either maximized or restored to its original size and position.
There are at least three possible scenarios this approach covers:
- single executable file
- two copies of the executable file having same names
- two copies of the executable file having different names
The executable files in cases 2) and 3) do not need to be byte to byte identical. What matters is the ApplicationId value shared by the two.
The 3) case behaves differently. While multiple instances are still prevented, the running instance's main window does not get activated since the executable files in this case have different names.
* * *
While adding this functionality to WPF PRISM application, I had to make small changes to the implementation. The testing of the named semaphore existence was moved from the RegisterApp method to a separate method called TestInstance.
internal static RegisterResult TestInstance(string appId)
{
try
{
Semaphore.OpenExisting(appId, SemaphoreRights.Synchronize);
return RegisterResult.AlreadyRunning;
}
catch (WaitHandleCannotBeOpenedException ex)
{
return RegisterResult.NotDetected;
}
}
internal static RegisterResult RegisterApp(string appId)
{
try
{
var semaphore = new Semaphore(1, 1, appId);
return RegisterResult.Success;
}
catch
{
return RegisterResult.Failed;
}
}
The OnStartup method in App.xaml.cs was changed as follows.
protected override void OnStartup(StartupEventArgs e)
{
var applicationId = Assembly.GetExecutingAssembly().FullName;
if (AppLauncher.TestInstance(applicationId) == AppLauncher.RegisterResult.AlreadyRunning)
{
AppLauncher.ActivateAppMainWindow();
this.Shutdown(0);
}
base.OnStartup(e);
var bootstrapper = new Bootstrapper();
bootstrapper.Run();
AppLauncher.RegisterApp(applicationId);
}
The named semaphore must be created only after PRISM bootstrapper runs (starts the main process). Otherwise the semaphore has a very short life.
Thursday, 22 November 2012
The number of ordered items cannot be negative
Could I have ordered a negative number of pizzas at a corner store? For that I probably should bake those pizzas myself, bring them over to the store, and ask the proprietor to pay me back. "Hey, there's some pizza for you guys! No delivery charges."
Yesterday a user discovered unusually high number of items on daily back order report. When I looked into the issue I found that all those items had negative ordered counts. I still have not figured out why that happened. But there's one thing I am sure about: had I created a constraint, today I would have one thing less to worry about.
Yesterday a user discovered unusually high number of items on daily back order report. When I looked into the issue I found that all those items had negative ordered counts. I still have not figured out why that happened. But there's one thing I am sure about: had I created a constraint, today I would have one thing less to worry about.
Tuesday, 20 November 2012
Another wheel invented or creating memo columns in cursors
In the SQL SELECT placed below, the last column is populated with the output from a function. Occasionally the length of the output exceeds 255 characters, which requires the column to be of the Memo data type.
SELECT;
product_id,;
cat.category_id,;
category_name,;
GetCategoryFullpath(product_id, cat.category_id);
FROM category cat;
INNER JOIN product_to_category ptc ON ptc.category_id = cat.category_id
Using PADR() for extending GetCategoryFullpath return beyond 255 characters results in "String is too long to fit" error. Same happens when PADR() is applied directly in the SQL SELECT.
In VFP8 this obstacle seems to be impassable (or "unpassable" using modern English). VFP9 added CAST() function that solves the task.
SELECT;
product_id,;
cat.category_id,;
category_name,;
CAST(GetCategoryFullpath(product_id, cat.category_id) as M) as category_fullpath;
FROM category cat;
INNER JOIN product_to_category ptc ON ptc.category_id = cat.category_id
Thanks to Naomi and Sergey on UniversalThread pointing me to the CAST(), I stopped short from wasting more time on reinventing the wheel.
SELECT;
product_id,;
cat.category_id,;
category_name,;
GetCategoryFullpath(product_id, cat.category_id);
FROM category cat;
INNER JOIN product_to_category ptc ON ptc.category_id = cat.category_id
Using PADR() for extending GetCategoryFullpath return beyond 255 characters results in "String is too long to fit" error. Same happens when PADR() is applied directly in the SQL SELECT.
In VFP8 this obstacle seems to be impassable (or "unpassable" using modern English). VFP9 added CAST() function that solves the task.
SELECT;
product_id,;
cat.category_id,;
category_name,;
CAST(GetCategoryFullpath(product_id, cat.category_id) as M) as category_fullpath;
FROM category cat;
INNER JOIN product_to_category ptc ON ptc.category_id = cat.category_id
Thanks to Naomi and Sergey on UniversalThread pointing me to the CAST(), I stopped short from wasting more time on reinventing the wheel.
Tuesday, 13 November 2012
Moving SQL Server database to SQL Azure
The task was simple: a database -- the structure and also the data -- had to be copied from a physical SQL Server up to the Cloud, which was in that case the SQL Azure.
The first tool I tested was SQL Azure Migration Wizard. This is very decent and handy tool I must say. With just a few minor glitches I had 240 Mb database moved up in two successfully completed tries. The first one took 40+ minutes, and 80 minutes was the second. I assume that SQL Azure responsiveness may vary, especially for trial accounts the kind I was using.
The migration tool uploads the structure and the data in a single package, and simplifies the process significantly.
You have to be aware of the differences between SQL Server 2008 R2 and SQL Azure. Naming a few: unsupported DBCC CHECKIDENT should probably make you review some stored procedures in your database. Note that the migration tool moves all stored procedures to the cloud regardless of whether they will continue working properly or not.
A less subtle difference is no FILESTREAM in SQL Azure. My database initially contained some FILESTREAM data. SQL Azure compatibility forced me to review and alter the structure switching from FILESTREAM to varbinary(max).
The second thing I wanted to try was creating empty SQL Azure database by running a script. The script was to be generated in SQL Server Management Studio. As I found, generating and applying such script took minimal time and efforts.
The ability to connect to SQL Azure database in the Management Studio is a really great thing. While online SQL Azure database management (Silverlight) works quite decently, it is not yet quite on par.
You operate in familiar environment, though some features are disabled and some might not work as they used to. For example, each my attempt to open a stored procedure or a trigger for modifications failed. Disabling a trigger also failed while invoked from the context menu, but succeeded through executing a query.
The part of my present project is conversion of data stored in Visual FoxPro database to SQL Server database. To transfer the data up to the cloud I intended to use same routines I used to convert the data to the local SQL Server database.
First I modified the remote connection in VFP database container. For both local and cloud connections I already had SQL Server data sources created. The following command creates connection to my local SQL Server using data source name.
CREATE CONNECTION MyDbConnection DATASOURCE "LocalSqlServer"
Another command creates connection to SQL Azure server.
CREATE CONNECTION MyDbConnection DATASOURCE "SqlAzure" USERID "[my Azure login]" PASSWORD "[my Azure password]"
Since both connections share the name "MyDbConnection", the VFP database really "sees no difference", or let's call it has no awareness (and does not need to) of what kind the target database is. At any time, by launching CREATE CONNECTION command in VFP command line, I can reconnect the VFP database to either local or cloud SQL server.
I also made small changes in my conversion routine giving it means of recognizing the target database whether it SQL Server or SQL Azure. That was accomplished by adding a remote view, the last column of which carried the required information (5 for Azure, 3 for SQL Server 2008 R2).
CREATE SQL VIEW SqlServerProperty REMOTE as SELECT cast(SERVERPROPERTY('productversion') as varchar(50)) as product_version, cast(SERVERPROPERTY('productlevel') as varchar(50)) as product_level, cast(SERVERPROPERTY('edition') as varchar(100)) as edition, cast(SERVERPROPERTY('EngineEdition') as int) as engine_edition
So far so good. I set the connection to SQL Azure and started data conversion routine. Noticeably slower, it was running for some time without glitches, while I occasionally monitored the progress by launching short queries in the Management Studio. Unfortunately the routine failed with fairly non-descriptive message informing of failed data connection -- connectivity error dbnetlib connectionwrite send().
Partially that was caused by designing the conversion routine with local SQL Server in mind. Once the routine hits anything that may result in incompletely converted data, it simply throws an exception, and exits without trying to repeat the failed action.
. . .
Based on my brief tests, now I am more inclined to have VFP to SQL Server data conversion completed locally, sending converted data up to the cloud with SQL Azure Migration Wizard or with a similar tool.
The first tool I tested was SQL Azure Migration Wizard. This is very decent and handy tool I must say. With just a few minor glitches I had 240 Mb database moved up in two successfully completed tries. The first one took 40+ minutes, and 80 minutes was the second. I assume that SQL Azure responsiveness may vary, especially for trial accounts the kind I was using.
The migration tool uploads the structure and the data in a single package, and simplifies the process significantly.
You have to be aware of the differences between SQL Server 2008 R2 and SQL Azure. Naming a few: unsupported DBCC CHECKIDENT should probably make you review some stored procedures in your database. Note that the migration tool moves all stored procedures to the cloud regardless of whether they will continue working properly or not.
A less subtle difference is no FILESTREAM in SQL Azure. My database initially contained some FILESTREAM data. SQL Azure compatibility forced me to review and alter the structure switching from FILESTREAM to varbinary(max).
The second thing I wanted to try was creating empty SQL Azure database by running a script. The script was to be generated in SQL Server Management Studio. As I found, generating and applying such script took minimal time and efforts.
The ability to connect to SQL Azure database in the Management Studio is a really great thing. While online SQL Azure database management (Silverlight) works quite decently, it is not yet quite on par.
You operate in familiar environment, though some features are disabled and some might not work as they used to. For example, each my attempt to open a stored procedure or a trigger for modifications failed. Disabling a trigger also failed while invoked from the context menu, but succeeded through executing a query.
The part of my present project is conversion of data stored in Visual FoxPro database to SQL Server database. To transfer the data up to the cloud I intended to use same routines I used to convert the data to the local SQL Server database.
First I modified the remote connection in VFP database container. For both local and cloud connections I already had SQL Server data sources created. The following command creates connection to my local SQL Server using data source name.
CREATE CONNECTION MyDbConnection DATASOURCE "LocalSqlServer"
Another command creates connection to SQL Azure server.
CREATE CONNECTION MyDbConnection DATASOURCE "SqlAzure" USERID "[my Azure login]" PASSWORD "[my Azure password]"
Since both connections share the name "MyDbConnection", the VFP database really "sees no difference", or let's call it has no awareness (and does not need to) of what kind the target database is. At any time, by launching CREATE CONNECTION command in VFP command line, I can reconnect the VFP database to either local or cloud SQL server.
I also made small changes in my conversion routine giving it means of recognizing the target database whether it SQL Server or SQL Azure. That was accomplished by adding a remote view, the last column of which carried the required information (5 for Azure, 3 for SQL Server 2008 R2).
CREATE SQL VIEW SqlServerProperty REMOTE as SELECT cast(SERVERPROPERTY('productversion') as varchar(50)) as product_version, cast(SERVERPROPERTY('productlevel') as varchar(50)) as product_level, cast(SERVERPROPERTY('edition') as varchar(100)) as edition, cast(SERVERPROPERTY('EngineEdition') as int) as engine_edition
So far so good. I set the connection to SQL Azure and started data conversion routine. Noticeably slower, it was running for some time without glitches, while I occasionally monitored the progress by launching short queries in the Management Studio. Unfortunately the routine failed with fairly non-descriptive message informing of failed data connection -- connectivity error dbnetlib connectionwrite send().
Partially that was caused by designing the conversion routine with local SQL Server in mind. Once the routine hits anything that may result in incompletely converted data, it simply throws an exception, and exits without trying to repeat the failed action.
. . .
Based on my brief tests, now I am more inclined to have VFP to SQL Server data conversion completed locally, sending converted data up to the cloud with SQL Azure Migration Wizard or with a similar tool.
Subscribe to:
Posts (Atom)