Visual Studio 2005 Programming with Sql Server 2000 or 2005

Home – http://www.cd3wd.com/

This document attempts to set out the logic behind and the practical details of programming techniques for:

The rapid creation of integrated Sql Server 2000/2005 – Visual Studio 2005 web database systems for use on corporate intranet

The particular implementation described refers to the reverse engineering of an existing Access Database System for HSEC (Health Safety Environment Community) for a mining company in Africa.

The brief was to produce a system within a very short timeframe (10 days) which allowed processing of 8 main parent working tables, their 10 child (detail) tables, and one or 2 lookup tables. There are also 24 lookup tables, whose maintenance can be programmed at a later time, or which are essentially static and can be handled by special request to IT personnel.

The idea is to create a phase I system which is useable and has good functionality, although cosmetics may be somewhat primitive.

There are essentially 19 screens; each is multiplied by 5 to give – view, add, edit, delete and all 4 functionalities.

System will be accessed from corporate intranet.

Access will be restricted and based on windows login

The main tricks and techniques used are as follows:

Menu is done using file web.sitemap. In future there will be a collection of xml files of similar/same construction as web.sitemap. on startup the system will decide which menu to offer the user based on their windows login and user rights. Meantime there may be 2-3 versions of the system on different directories and web addresses, with different restricted accesses.

The screens which handle parent tables are based on a gridview which enables selection of a row in the parent table plus a formview which enables viewing/editing/adding, deletion of the parent table row.

The first gridview should have the options enable paging, enable sorting, enable selection all ticked. Also the second gridview. The second gridview should be linked to the first so that gridview2.parentidentity = gridview1.selectedvalue.

The formview1.iidentity = gridview2.iidentity for the 2+1 parent child screen

the formview1.iidentity = gridview1.selectedvalue for the 1+1 parent screen.

when setting up data control – use where button – where column iidentity = control gridview1.selectedvalue (default value) – add OK.

use also advanced button – generate insert update and delete statements.

The screens which handle child tables are similar but have 2 gridviews plus 1 formview. The first gridview selects parent table row then the 2^nd gridview selects child table row. The formview handles the child table row.

The number of fields per formview varies widely, but is maximum at 52 fields, with 30 of those being lookup fields, and 8 being controlled by a regular expression validation control (regex).

Normal single-line text boxes are length limited by using the maxlength property (e.g. 50, 100 etc); multi-line text boxes are limited if necessary (i.e. if bound to a nvarchar(255) or similar field) by using regex expression ^[\w\W]{0,255}$ (min length 0, max length 255); multi-line text boxes bound to a ntext field or similar do not need to be length-limited (but can if desired be limited by using the same kind of regex expression).

Dates are controlled in 2 ways – to ensure display in screens of British/French date format use the code

protected void Page_Load(object sender, EventArgs e)

{

Session.LCID = 2057;

}

In the .cs subfile.

And then set the textbox data binding (edit data bindings – format) format to short date. This produces a date of format 31/12/2007 or 09/09/2007 in edit mode and 31/12/2007 00:00:00 in view mode. Text='<%# Bind("DateTest", "{0:d}") %>'

Using regex expression in the field validation expression:

^(([0-2]\d|[3][0-1])\/([0]\d|[1][0-2])\/[2][0]\d{2})$|^(([0-2]\d|[3][0-1])\/([0]\d|[1][0-2])\/[2][0]\d{2}\s([0-1]\d|[2][0-3])\:[0-5]\d\:[0-5]\d)$

Handles the dates fairly well, but crashes if 31/09/2007 is entered by mistake. Therefore some improved version which handles the months with 30 days only should be substituted for the above as soon as possible. **

Whole numbers (positive integers) are handled by regex expression:

^\d+$

Email addresses are handled by

^([\w\-\.]+)@((\[([0-9]{1,3}\.){3}[0-9]{1,3}\])|(([\w\-]+\.)+)([a-zA-Z]{2,4}))$

Web addresses have not yet found a useable regex expression **.

A good collection of regex can be found at www.regexlib.com

Pulling the regex strings from database or from web.config file or similar resource should be investigated! ** PS – I am fairly sure that it is not possible to pull those regex strings dynamically – i.e. they may have to be hardcoded, as I have done in the systems already built…

The formview edit template menu option is used. The boxes are moved to be inline. Then each box is worked through.

For lookup fields, the main bound textbox is set to invisible, and a standard dropdownlist is added to the right. A data source is added which pulls data from the lookup table in the correct sequence, and then it is data bound to the correct control (using EDIT DATA BINDINGS option). Both these options are on the smart tag of the dropdownlist.

Before the lookups are processed, it is good to go through the working table and its lookups and set all the relations to ensure data integrity.

The use of nulls in the database is important. There are rules which if followed make the system easier to build and easier to operate:

All lookup fields must be non-nullable

All date fields should be nullable

All numeric fields nullable (apart from iidentity and parentidentity)

All text fields nullable

Additional rules are:

Every table must have an identity field which is also the primary key

All lookups must have a unique index on the lookup value

The main working parent and child tables must store the actual lookup value and not a reference to it (i.e. not an id field)

These actual lookup values must of course be the same length (and datatype) as the corresponding field in the lookup table (typically nvarchar data type and length 30 or 50 characters)

The regular expression validator must have 3 fields filled in – the expression itself, the data control which is being validated, and the error message. I have hard-coded the error messages in the meantime but it should be possible (??**) to use a reference or especially a language-related Resources.Resource…

The prompts at the moment are taken directly from the fieldnames in the database (this is done by the wizard(s)). In future the code will be replaced by Resources.Resource names and will be language-related **

When the edit template has been completed and has been tested and finalised, then 2 options are available:

first option – do the same or similar manual work with the insert template..

second and better option – build system first (very important!). Then go to view markup screen. Shade and copy the markup code inside the edititemtemplate tags. Paste into a notepad/editor window. Modify as follows in 4 steps:

replace DropDownList to DropDownList0

replace asp:DropDownList0 to asp:DropDownList

replace SqlDataSource to SqlDataSource0

replace asp:SqlDataSource0 to asp:SqlDataSource

then shade and copy the markup which is ABOVE the first <asp:linkbutton tag and drop into the InsertItemTemplate tags and delete the existing markup code above the first <asp:linkbutton tag inside those InsertItemTemplate tags.

Now go back to view designer/ edit template / insert item template. Check all the ControlToValidate settings for each of the RegularExpressionValidators – these will be wrong and have to be changed to refer typically to textbox1, textbox 14 etc..

The work of creating insertitem from the data for edit item should now be complete and should now work – and with less time and effort than a manual recreation of the edit item data…

There is a trick for inserting the parentidentity field into the child table on the row insert operation – the following code has to be put in the .cs subfile:

protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)

{

SqlDataSource3.InsertParameters["ParentIdentity"].DefaultValue = GridView1.SelectedValue.ToString();

}

protected void SqlDataSource3_Inserting(object sender, SqlDataSourceCommandEventArgs e)

{

SqlDataSource3.InsertParameters["ParentIdentity"].DefaultValue = GridView1.SelectedValue.ToString();

}

It is necessary to bring up formview1 and click on the iteminserting event; similarly to bring up sqldatasource3 and click on the inserting event…

Note that the tables are linked in that the childtable->parentidentity big integer value = parenttable->iidentity value… I have not set these as relationships in the database, but should probably do so… **

Note also that the parentidentity field in the edit and insert templates should be enabled = false.

When the system is created on the website by using the build, publish website option, then database permissions also have to be set. The user NT AUTHORITY\NETWORK SERVICE has to have select permissions on the lookup tables, and select, edit, add and delete permissions on the working tables (parent and child)

Note that the user NT AUTHORITY\NETWORK SERVICE had to be manually added to the database in question in order for the system to work.

Note that when e.g. Inspections5.aspx and Inspections5.aspx.cs have been finalised, then they can be saved as 4,3,2 and 1. Then the view markup in 1,2,3 and 4 are edited to comment out (using <!-- à tags) the various edit, insert, new menu options as required. 1 comments out all 3 options; 2 leaves new option, 3 leaves edit option, 4 leaves delete option.

WEB SERVER CONFIGURATION - Various problems had been encountered during the initial setup of IIS for asp.net 2.0 to work. One problem had been that the server when configured was set to work only with asp.net 1.1 – ASP.NET version 2.0 has to be deliberately chosen. Other miscellanous problems were encountered which were worked through by googling…. When a good working configuration is found it is essential to save that configuration both on the web server and also externally as a file, so that in the event of someone fiddling one can get back to a working situation!

Web service extensions in particular should be set up in under the IIS Manager, so that the file aspnet_isapi.dll for ASP.NET VERSION 2.0 (and not 1.1!!) is located and locatable.

If the webserver has been stopped for any time, then the file app_offline.htm will probably be present in the root directory of your asp application. Manually delete this to enable the asp application to run (!).

There are also settings in IIS Manager and also in the application web.config file to do with integrated windows authentication.

A master page is used with every web page, and can be tailored at any time to show different page header, informative message(s) etc..

Web config file is edited to give <compilation debug="false"/> before system is published to website. (debugger sets again to true for local testing)

A global.asax file is present and is used for the permissions stuff

Permissions are set by the ppermissions table which lists windows login vs. pages allowed to access. There is also code in the global.asx file for the function permissionsCheck which if the windows login is not blank will allow access to everyone to pages of type 1 and 2 and also of course to default.aspx – the main menu..

To add a new user to the power users group, run the stored procedure from query analyzer against database HSEC – e.g.

PPERMISSIONS_INSERT 'CORP\antonio.lastname'

- that adds Antonio to the system as having access to all 5 levels of menus

Note that the user NT AUTHORITY\NETWORK SERVICE has to be given execute permission on the various stored procedures which are used; also select permission on the various lookup tables, and also select, insert, update, delete permission on the 8+11 working tables (parent + child)

Note that it is planned that we will rework some of the connection strings so as to use a HSECConnectionPassive and an HSECConnectionActive where appropriate – the active will be used for connection to the master database (at The Remote/Upcountry Site), and the passive connection/connection-string will be used for connection to the various local replicated databases (which are used to speed up local querying and reporting). For the The Remote/Upcountry Site installation, both strings will be identical. The other installations will have local webserver and local replicated database, and will point to that local database for passive operations and to the central (The Remote/Upcountry Site) database for active operations (writes/ updates, deletions, insertions). I have not yet worked out how the NT AUTHORITY\NETWORK SERVICE from a remote server will be granted permission to execute stored procedures and do selects etc… but it should be quite simple. – see item (62) below.

Note that there is one workaround in particular which is required with this wizard-driven system! – if not implemented then it becomes impossible to add the first child record to a new parent record, and also impossible to add a parent record to an empty parent table! – the sql server stored procedure CheckCreateBlankRows does the necessary actions to create and then to delete blank or bogus rows or records as and when required…. This SP is run on the server continuously as a job which starts when the server and/or the server agent starts… Note that this is run on the The Remote/Upcountry Site server only and NOT on the replicated copies..

If after a publishing system to website the compilation process takes a long time then use remote desktop to log onto the webserver and start the website by using a web browser locally on that machine.

The end result of all the above is building a system at approx 1-2 hours per screen ( divide by 5 if one counts the view/add/edit/delete/all webpage system), and almost zero source code writing (the wizard(s) are doing it all or most, except for some of the edit->insert coding tricks). Building 19 screens (95 web pages when the 5 view/add/edit/delete/all options are activated) took approx 5 days, including all learning curve processes.

Alex Weir 05/08/2007

Additions Alex Weir 2007-08-29

Shadow tables were created for the 8 parent tables, the 11 child tables, stakeholders and staff tables. Triggers for delete and update were written, the shadow tables record the date and time of delete or update, and whether it was a delete (boolen value – true=deleted, false=updated). It was not possible/easy to include code to indicate the username of the person who effected the delete or update, because of the data binding and codeless programming used for the system. Of course for other systems and for financial applications those kind of necessary data Will be provided and written.. Note that many character fields of types text, ntext and/or image cannot be duplicated by the triggers, therefore trigger protection is only partial. This can be fixed on phase II or III by converting to nvarchar(4000) or varchar(8000) fields… **

Backup procedure was written – takes place daily every night at 0300 hrs; backup is incremental – first run takes place 0300 hrs 30/08/2007. The backup takes place on the THE CAPITAL CITY SITE replicated database copy, and not on the The Remote/Upcountry Site original.

Dual connection string strategy was implemented for HSEC system. The 2 strings are called HSECConnStrPassive and HSECConnStrActive. Passive is used only for operations/pages ending in 1 – the view option. All conn strings inside those pages are passive (this was the easiest way to modify existing code). Active is used for all connection strings inside pages ending 2,3, and 4 (this was the easiest way to modify existing code). All pages type 5 were removed from the menu structures.

Note that the dual string strategy required that the webserver SQL2 at THE CAPITAL CITY SITE had various permissions on the sql server SQL1 at The Remote/Upcountry Site – it was necessary to create the login CORP\RTxxxxxxxSQL2$ on SQL1 and then to allocate the necessary permissions on tables and stored procedures exactly as had been given to NT AUTHORITY\NETWORK SERVICE for the single connection string strategy…The documentation for that was NOT easy to find…

Replication – various tests were done with the 3 standard replication strategies – snapshot, transaction and merge. Problems were encountered with the identity fields. And especially since those fields are used between the parent and child tables for linking. In the end, I used DTS to effect a data copy of all working tables plus the lookups staff and stakeholders every night 0100 hrs. The system works by first of all deleting all the working tables, then the 2 lookups – this must be done in that sequence because of foreign keys. Then the lookups are copied over, and only finally the working tables. This is done by creating 2 DTS packages for the table copying and also one stored procedure for the target table deletions. Then those 3 components are assembled in one parent DTS package, which is then scheduled for running at 0100 hrs daily. The 3 operations are linked – only run 2 if 1 succeeds, only run 3 if 2 succeeds. Email notification if any fails.

** Note that if other lookup tables or any other tables are modified at The Remote/Upcountry Site, the modifications will have to be duplicated manually at THE CAPITAL CITY SITE, and/or a more extensive replication process set up.

Note that the replication process also runs successfully daytime – e.g. could be scheduled to run during lunchtime if managers decided that was a priority.

We can also use the same technique to replicate to UK and/or to Toulouse, so that UK-side managers can get an idea of what is going on – we can even for that institute a 3^rd menu option which gives only view rights – i.e. only passive connection and no add, edit, delete rights.

Almost certainly the replication for the more complex systems (e.g. Inventory and Purchasing) will be done programmatically rather than by DTS or by Sql Server Replication. The simpler systems may well use DTS techniques as above.

Some changes were made to the sequence of presentation of the lookup tables risk and consequence, and the stored procedure checkcreateblankrows was modified also to modify/set the field assessment depending on the content of those 2 fields (in the table smatincident1). Note that the screen should probably be modified in phase II to disenable the field/lookup assessment…

Some very basic webpage based reporting on incidentactions is added to the menus under IncidentsActionsReports..

An activityLogReport is added – http://1.1.1.127/xxxx/ActivityLog_Reports.aspx - this is for restricted high-level (e.g. tony and john) viewing only – and gives an indication of system use or lack of it…

http://1.1.1.127/xxxx/HSEC_GoLive_ToDoStatus.htm has the spreadsheet of outstanding work for phase II and III

This document is under http://1.1.1.127/xxxx/VS2005_Programming.mht

There follows some important stuff on controlling access to asp websites using NTFS – filesystem – instead of using web.config file.

I wasted some 3-4 days trying to get web.config to work properly for protecting web directories against access. This did not work, and in the end, a Microsoft guy contributed to my forum posting by suggesting using NTFS or suffix protection as both being useable techniques. The posting I include immediately below.

We are creating and running some asp.net 2.0 systems in low connectivity environment. We have 4 sites with 4 webservers and 4 sql servers. Sql Server database contents are replicated from the master database to the 3 slaves every night overnight. All applications use 2 connection strings – one to the local database and one to the master database (for the master site the 2 strings are obviously identical). In the applications, pages with passive activity use the passive string only, and pages with active activity use the active string.

All users are inside the corporate intranet and use windows logon and internet explorer. We use windows authentication, and use a database-table-controlled system to decide whether individual users have access to individual pages; refusal redirects the users to an AccessDenied.aspx page.

We have 6000 scan files, to which we need to control access very carefully – these are personal records. Types are pdf, jpg, bmp, tif, png. These files could be bulk converted to web-page-compatible image files – i.e. jpg, gif and/or png if required, and new files to be added could feasibly have that acceptability requirement. There are also 100 word.doc files and 50 excel.xls files which require viewing and require access control; these could maybe be converted to image files if required. Total size is 3 giga, with individual files as large as 34 mega; average size is 500k.

Normally in a good connectivity situation we would go for a database storage solution, but because of the low connectivity, we would prefer to use file system storage, with overnight robocopy.exe incremental copying and synchronization between sites.

Our problem is how to prevent direct access by users to the image files?

Potential solutions seem to be:

- break with the flow and revert to database storage – we would have to do some programmed intelligent replication in that case.

- use web.config file to prohibit direct access to these file suffixes – as per http://aspnet.4guysfromrolla.com/articles/020404-1.aspx - and of course use an .aspx page to display the image in an image control.

- Store the images outside the viewable website directories and stream in as and when required, using a technique similar to http://www.codeguru.com/cpp/i-n/internet/filetransfer/article.php/c12529/

- Store the images inside the website but in a directory and subdirectories which are forbidden to all users except the local asp.net account (usually NT SERVICE) – then they are not directly viewable but can be pulled by asp.net into an aspx webpage to display the image in an image control. Web.config can be used to do this?

- There should also be additional possible solutions??

75. Response from Microsoft - Hi

You can use the NTFS to control access rights on the folders/files.

For those resource(pictures, doc files and so on) which will be accessed through the website, another way to protect it from download by anonymous user is using the HttpHandler like this .

http://www.dotnetspider.com/kb/Article1011.aspx

Best Regards
XiaoYong Dai
Microsoft Online Community Support

I implemented the NTFS solution by right-clicking on the website in IIS Manager on the server, and removing non-essential users – i.e. keeping SYSTEM and administrators only. Then I had to add the user SERVICE in order to make the system work. Then add firstname.lastname users as are required to use the system and grant them read, list and execute rights only. Control Panel, computer management, Services and applications, IIS Manager, websites, default website, HR, and also HR/Scans. Right click on HR and permissions. Read, read and execute and list folder contents should be checked for the users you add.

Provisional End of Document – Alex Weir 3 October 2007.

Note that I did some additional clever things after this, the main one being to tidy up the very messy formview-based editing screens by manually coding <Table><TR><TD></TD></TR></Table> tags around the markup code…. That made a tremendous difference. I very strongly recommend copying the markup out into an editor before doing that, since – if you try it inside the VS editor, it keeps adding stuff for you, and the task becomes messy and/or impossible.

Another clever bit of stuff was concerned with reporting – where I wrote some clever stuff in the Employee Database system which could handle maintenance of 74 different tables using one set of code. I also wrote some stuff which allowed the user to specify which fields he/she wanted to view and also which to sort on.

Provisional End of Document – Alex Weir 28 April 2008

Home – http://www.cd3wd.com/