My experience with SQL Server replication

Requirement: Certain windows clients running in SQL Express Edition, xyz application writing into it. Real time (latency of 10 minutes affordable, i dont know y they say its real time) data must come to a remotely located SQL Server Standard edition, now this is a centralised server.

This task came to me surprisingly (as i am not a DBA :-) )and my initial approach was Merge Replication.

Learned, replication is a bad approach due to obvious reasons as SQL Express cannot be a publisher, no SQL Agent etc.

Merge replication works with Subscriber (here SQL Express) and Publisher (here SQL Standard) where changes will could be synchronized between them, Merge Agent will upload changes and later the Synchronization.

The synchronization process brings the list of GUIDs to the Publisher and compares the list with a list of what has changed on the Publisher since the last synchronization with that Subscriber

Now here came the vilian, conflicts, struggled with this (may be coz i am not experienced in DBA) such as row updation happened to be same, key conflicts...well totally it was bad.

As i was given less time to arrive at a tested , working solution.....seeked help from an expert and he suggested to basically pull data from the clients after linking the clients to server (db links) and a small SP scheduled to run....

This however worked (not sure though this solution is scalable) as when more clients are added, schema if changed etc...

But my replication experience was not good...

RegEx simplified

Came across this site when help was most needed..........very nice tool to test and build your app. Thanks to David Mauri.

User Defined Functions of SQL with .NET CLR

Well, sometimes you might have thought if you had the power of .NET framework in your SQL query's, well here it is. You write your
1. Stored Procedure
2. User-Defined Function
3. Trigger
4. User-Defined Type
5. User-Defined Aggregate

all using C# or VB .net.

I'm going to blog here what i have done recently for my project. I required a webservice which will actually retreive some data using pattern matching.

For this i required to create a UDF that gives me easy regular exp checking using Regex in .NET,

It took me hardly 2 minutes, i opened Visual Studio, choose Visual C# and then Database and choose user defined function for SQL.


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean RegExMatch(String failure_reason,String pattern)
{
Regex argstr = new Regex(pattern);
return argstr.Match(failure_reason).Success;
}
};

and thats it i compiled and deployed in SQL, went ahead and used the UDF as below

Select dbo.RegExMatch (..,..)

I am still completing this task but found this very useful thought this might com handy for you too sometime

CreateUser Wizard, adding roles to users created

With .NET 2.0 its all easy :)

Drag and drop a CreateUserWizard in your page and set the OnCreatedUser=CreateUserWizard1_CreatedUser

Add a Wizard Step as below

asp:WizardStep ID="AddRolesStep" Runat="server" AllowReturn="False" Title="Step 2: Assign User To Roles"
OnActivate="AssignUserToRoles_Activate" OnDeactivate="AssignUserToRoles_Deactivate"

with a Listbox or any control to display the roles

After that in code behind add

public void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
// Create an empty Profile for the newly created user
ProfileCommon p = (ProfileCommon)ProfileCommon.Create(CreateUserWizard1.UserName, true);
// Save the profile - must be done since we explicitly created this profile instance
p.Save();
}
// Activate event fires when the user hits "next" in the CreateUserWizard
public void AssignUserToRoles_Activate(object sender, EventArgs e)
{

// Databind list of roles in the role manager system to a listbox in the wizard
AvailableRoles.DataSource = Roles.GetAllRoles(); ;
AvailableRoles.DataBind();
}

// Deactivate event fires when user hits "next" in the CreateUserWizard
public void AssignUserToRoles_Deactivate(object sender, EventArgs e)
{

// Add user to all selected roles from the roles listbox
for (int i = 0; i < AvailableRoles.Items.Count; i++)
{
if (AvailableRoles.Items[i].Selected == true)
Roles.AddUserToRole(CreateUserWizard1.UserName, AvailableRoles.Items[i].Value);
}
}

Also dont forget to add in web.config file

< profile enabled="true" >
<properties>

<add name="Email" />
</properties>
</profile>

else you will wonder which namespace to add for ProfileCommon, compilation error will show, you are missing reference.

That's it, a Create User with associating to roles are ready!!!

Associating events to dynamically created controls

Continued......

After the controls that are created dynamically now the questions is events of that controls

i came across this microsoft link which looks helpfull and is trying to match with my requirement.

Add rows to asp table web control dynamically

Came across this 'constraint' recently and was amazed to find not much solution exist in .net world for this.

Requirement: Add rows dynamically to an asp table, web control(or say dynamically you want to keep on adding controls on click / some event)

Issue: I was able to add row but previously added row 'vanishes' from UI

Reason: Viewstate of dynamically created control is lost on next post back

Solution:

Added a session to store state of table and to render/recreate the control on post back

code as below in page_load event

if (Session["rowList"] == null)
{
//No row list, Create it.
rowList = new List();
}
else
{
rowList = (List)Session["rowList"];
}
//Recreate the table. (Prevents loosing data already entered into textboxes.)
DisplayTable();

There is an addrow method called on click (/ any event you like )
You have to add our newly formed row to our row List as below.

rowList.Add(tableRow);
Then save our Row List on to the session
Session.Add("rowList", rowList);
Call the DisplayTable();

Finally recreating table as below in DisplayTable function

protected void DisplayTable()
{
//Run through the row list, and add each one to the Table's Row Collection.
foreach (TableRow tr in rowList)
Table1.Rows.Add(tr);
}

Any other suggestion / idea you have please post it here....coz i am still not convinced by this idea :(

New features of .NET 2.0 framework

Came across this link.........lists features of .NET framework 2.0

http://asptoday.com/Content.aspx?id=2368

Nested Gridviews in .NET 2.0 [Master - Detail]

This is my favourite 'feature' and it goes something like this................GridView within GridView

If you are working with a requirement for a detail view for selected item of master view then you must definitely consider this option.

Your aspx look is all your wish, if you are good in UI then you can explore what other options exist.

I choose the following,

simple table with a div section (i'll explain why we need a div later) as below.



grid1-colname will be unique column in your GridView1 (based on data)

RowBound method of your first gridview / master grid will have the following coding

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
GridView grid2 = (GridView)e.Row.FindControl("GridView2");
dbSrc = new SqlDataSource();
//Define your datasource here, connectionstring, selectcommand etc
grid2.DataSource = dbSrc;
grid2.DataBind();
}
}

Now adding a small Javascript, your Nested Gridviews are ready to go live :)

function switchViews(obj)
{
var div = document.getElementById(obj);
var img = document.getElementById('img' + obj);
divobj=div;
if (div.style.display=="none")
{
div.style.display= "inline";
img.src="Images/collapse.jpg"; mce_src="Images/collapse.jpg";
}
else
{
div.style.display = "none";
img.src="Images/expand.jpg"; mce_src="Images/expand.jpg";
}
}

Now i presume you got why we need the div tag, and the ID we gave as unique column of GridView1 for the same.

Just F5....rest you know ;)

ASP.NET Ajax - Ajax extentions framework

Before we take a dip in Ajax Extentions

First download AJAX Extensions 1.0 on the Microsoft site

The download is contained in one Windows installer file; it also includes the Microsoft AJAX Library, which is the core component of using AJAX principles within ASP.NET.

In addition to the Ajax controls now in your developement Studio, you will find a new project type called ASP.NET AJAX-Enabled Web Site

Ajax controls packaged are as below

Timer: Allows you to update portions of a page, run server code, or post the entire page to the server at a defined interval.

ScriptManager: The starting point to provide AJAX functionality in an ASP.NET page.

UpdateProgress: Allows you to post status information pertaining to partial-page updates. You may use it when partial updates are slow, so the user gets feedback on the progress.

UpdatePanel: Allows you to perform partial-page updates.

You may only use one ScriptManager control per page but you may use multiple UpdatePanel controls.

The UpdatePanel control offers so many options to easily incorporate AJAX functionality — most notably, partial-page updates — with ease.

I'll be posting a sample application on these soon!!!

Till then happy programming...........

Invoking a windows service in .net in intervals

I had a requirement recently to run my little windows service every one minute or an interval thats configurable....

And Timers was straight forward solution for me...

Event on which you need to write your code is OnElapsedTime.

private void OnElapsedTime(object source, ElapsedEventArgs e)
{
streamWriter = new StreamWriter(new FileStream(
"C:\\WindowsServiceLogger.txt", System.IO.FileMode.Append));
this.streamWriter.WriteLine(" My Service got invoked again at " +
DateTime.Now.ToString());
this.streamWriter.Flush();
this.streamWriter.Close();
Start();
}
My task for the service is written in Start() method so i can call it directly OnStart and OnElapsedTime

Please remember to enable your timer OnStart and disable OnStop.

Seems all simple and easy....and it is :)

Happy Coding

Export to Excel from pagination enabled Gridview

Recently couple of developers asked me this question, so thought of pinning it down here....

very simple way is to turn of your gridview pagination and bind it. As you must be aware use HTMLWriter to render you control.

................
htmlWrite.RenderBeginTag(HtmlTextWriterTag.Tr);
GridView4.AllowPaging = false;
GridView4.DataBind();
GridView4.RenderControl(htmlWrite);
htmlWrite.Write(" Header of my GridView");
htmlWrite.RenderEndTag();
htmlWrite.Write("
");
...........................

to make your excel look cool add/modify the htmlwriter settings....n e questions shoot me a mail :)

ObjectDataSource - A powerfull datasource control

A middle tier two-way talking control, thats what you can say about this datasource control. Suitable when you have a requirement of encapsulating your buisness logic in object, hence we say this control supports three tier architecture by providing a way to bind your presentation layer and data tier.

The ObjectDataSource control exposes a TypeName property that specifies an object type (class name) to instantiate for performing data operations. You can bind this control to a DAL (data access layer) or BLL (business logic layer) depending on requirement. More secured and organised is to BLL, thus return data will be stateless.

Nice tutorial for begineers is here : http://quickstart.developerfusion.co.uk/QuickStart/aspnet/doc/ctrlref/data/objectdatasource.aspx

Smart ways to add your database connection

Now a small question, where will you add the connection string for your project?

Simple question, right. There are multiple options here, you may place this in your code, config file, etc. Lets explore this in detail.

Its a bad idea to store connection strings in your pages for different reasons.
1. Bad practice from security point of view. In theory, no one should ever be able to view the source code of your ASP.NET pages. In practice, however, hackers have discovered security flaws in the ASP.NET framework. To sleep better at night, you should store your connection strings in a separate file.

2. Difficult managing change requests. Adding a connection string to every page makes it difficult to manage a website. If you ever need to change your password, then you need to change every page that contains it. If, on the other hand, you store the connection string in one file, you can update the password by modifying the single file.

3. Performance, connection pooling which is auto done by ADO.NET checks character by character and strings in multiple places will hence affect performance.
So strings in configuration files is our option here.Now that is a smart option, and an even more smart option if you need multiple application to access the same conneciton string is to place the same in the root config file (C:\WINDOWS\Microsoft.NET\Framework\[version]\CONFIG folder)

Having said that, lets just try to make this a bit more secure by encrypting the connection string. You may use aspnet_regiis to encrypt your config file as below

aspnet_regiis -pef connectionStrings ""

-pef (Protect Encrypt Filepath)

RolesProvider

Authorization settings in ASP 2.0 is simplified with the ASP.NET Web Site Administration Tool feature.
The RoleProvider class is the base class for all role providers. RoleProvider class defines the contract for all ASP.NET 2.0 role providers. The class includes method to create and delete roles, to add and remove users in a role, and to check the role membership for a given user.
If you want to programmatically interact with the role provider, use the Roles class from the System.Web.Security namespace. The static Roles class provides methods and properties that will forward calls to the currently configured role provider
Each role provider retrieves and stores role information from a different data source.
The role providers packaged in System.Web.dll include the SqlRoleProvider, stores roles in a SQL Server database.
The WindowsTokenRoleProvider retrieves role information from a user’s group membership in Windows.
The role manager in ASP.NET is off by default. You can turn on the role manager on the security tab of the web administration tool, which will add the following to the applications’s web.config file.
We can restrict locations of the web application to specific roles using settings in web.config.

Whats in a MembershipProvider

The SqlMembershipProvider supports three formats for storing passwords: Hashed (the default and most secure format), Encrypted, and Clear. The passwordFormat property specifies how the provider will store passwords, and will impact a number of other membership features.
The enablePasswordRetrieval option determines if the provider will return a user’s password with the GetPassword method.
The enablePasswordReset property controls the ResetPassword API. ResetPassword will assign a new, generated password to a user. The PasswordRecovery control can automatically email the new password to a user. It’s a good practise to set the requiresQuestionAndAnswer property to true to prevent a malicious user from resetting someone else’s password, A value of true means the user has to provide the answer to a security question before resetting their password.
The minRequiredPasswordLength and minRequiredNonalphanumericCharacters prevent users from choosing a password like “abc”. If you have additional requirements, you can use the passwordStrengthRegularExpression property to force the password to pass a regular expression test.
The SqlMembershipProvider offers a number of features not shown in the configuration above. I suggest you to explore other properties yourself.
If you want to interact directly with the Membership API, one approach is to use the Membership class from System.Web.Security. The Membership class contains only static members and properties, but these static members map to properties and methods on the MembershipProvider, and the component will forward calls to the configured provider when appropriate. Here is an example using hard coded values for a user's attributes.
An even easier interface to the membership provider is to use the ASP.NET 2.0 Login controls: Login, LoginView, PasswordRecovery, LoginStatus, LoginName, CreateUserWizard, and ChangePassword. The Login control, for example, will ultimately call the ValidateUser method of the current membership provider when a user enter their username and password and clicks the Login button. There is no need to write any code if the built-in controls provide all the functionality you need. All of the controls allow customization various levels of customization through styles and templates. You can find the controls in the Visual Studio toolbox under the “Login” category.

Membership and Roles - An Overview

As .net developers we are familiar writing code to accept user’s name and password, the code to verify passwords, and the code to create and manage users. Thanks to ASP.NET 2.0, web developers will no longer need to write and re-write the code to store and validate credentials. Instead, you have membership and role provider implementations for managing roles and membership in our web applications.
The membership and role providers exist to provide authentication and authorization services to our applications. ASP.NET 2.0 provides login controls we can drop on web forms to perform authentication with no code required. The controls talk directly to the membership provider. ASP.NET 2.0 also offers controls to support the ongoing maintenance of users, including changing passwords and resetting passwords. The role providers in 2.0 allow us to create roles, and map users into the roles. Of course, your application might have special needs. Perhaps your database is not Microsoft SQL Server. Fortunately, Microsoft implemented both membership and role management using an extensible provider model.
The provider model in ASP.NET 2.0 helps developers to plug their own implementation of a feature into the runtime. The provider model in ASP.NET 2.0 includes an infrastructure for the configuration and initialization of providers.
The provider model begins with the abstract class ProviderBase. ProviderBase exists to enforce the contract that all providers need public Name and Description properties, as well as a public Initialize method. Inheriting from ProviderBase are the MembershipProvider and RoleProvider abstract classes. These classes add additional properties and methods to define the interface for their specific areas of functionality.
The beauty of the provider model is this: higher-level application services can build upon a provider and not need to know the details of what happens behind the interface.
Now, let's take the membership controls, which include a Login control, a CreateUser control, a LoginStatus control, and more. All of these controls program against the MembershipProvider contract. At some point, the login control will need to invoke the ValidateUser method on the configured provider. The login control doesn’t care if the call travels to a SQL Server database or an XML file. All the login control cares about is passing in a username and a password and receiving a true or false value in return.