(Copied from Moses’s Blog for my reference)
Well, I had a small issue regarding writing a script to clean a database we have and reset its identity columns in all tables. Although the database wasn’t huge one (less than 100 tables) I had to trace relations to be able to delete child table’s records before parent’s ones because of the foreign key constraints. The solution is disable the foreign keys and delete records with no fear of any errors then enables the constraints again.
Well, I found a solution to disable all constraints without the need to go on each table and disable it manually. and I was happy to know that I can use this solution in deleting all records from all tables. Not only this I was able to use the same solution to reset identity columns in all tables.
The solution was to use this built in stored procedure sp_MSforeachtable. For help about this proc search for it in Books online or use this sp_helptext sp_MSForeachtable.
Now back to my 6 lines, bellow is how I re-zeroed my Database:
1: /*Disable Constraints & Triggers*/ 2: exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 3: exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 4: 5: /*Perform delete operation on all table for cleanup*/ 6: exec sp_MSforeachtable 'DELETE ?' 7: 8: /*Enable Constraints & Triggers again*/ 9: exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 10: exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 11: 12: /*Reset Identity on tables with identity column*/ 13: exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'
When you create tables in SQLServer (2005) by default its all get created under default schema. (dbo.)
If you have too many tables then the table list could be confusing / clumsy.
Here enters Schema
To organize your tables into meaningful groups (or namespaces)
First create a schema
CREATE SCHEMA Person
Then Alter your existing table
ALTER SCHEMA Lookup
So now that you have grouped your tables into different schemas.
How to use them ? The usual way but with schema name added to it.
SELECT * FROM Person.Contact
What is Schema ? (from MSDN)Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a
database user. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any
user, and schema ownership is transferable.
In previous versions of SQL Server, database users and schemas were conceptually the same object. Beginning in SQL Server 2005, users and schemas
are separate, and schemas serve as containers of objects.
If you are using Update Panel and try to Export gridview contents to EXCEL or Word, your asp.net page will throw nasty error. “Sys.WebForms.PageRequestManagerParserErrorException”
Its because AJAX & Response.write don’t go together. By calling Response.Write() directly you are bypassing the normal rendering mechanism of ASP.NET controls. The bits you write are going straight out to the client without further processing (well, mostly…). This means that UpdatePanel can’t encode the data in its special format.
There are couple of ways to solve this problem.
1. Place the Export Button outside of Update Panel.
2. By Pass the Export Button using <Triggers>
<asp:PostBackTrigger ControlID=”cmdExport” />
if your Export button is part of UserControl then specify usercontrolid:buttonid
<asp:PostBackTrigger ControlID=”uscSelCtl:cmdExport” />
Recently if your webservices started failing / timing out it its worth your time to take a look at this support article.
“HttpException (0x80004005): Request timed out.”
“System.InvalidOperationException: There were not enough free threads in the ThreadPool object to complete the operation”
Contention, poor performance, and deadlocks when you make Web service requests from ASP.NET applications
Related Reading :
Item 5 talks about Asp.Net WS
Filter Datatable by Rows
If you need to get subset of a big dataset into datatable Rowwise …
Dim dtEmp as DataTable Dim dsEmployee as New DataSet dsEmployee = someobject.getdata() 'dataset can be populated in many ways which is not explained here. Dim sExpr as String Dim drRows() as DataRow, drSingleRow as DataRow sExpr = "EmpID > 100" 'Condition drRows = dsEmployee.Tables(0).Select(sExpr) 'If you need to add Sort Order it can be added to sExpr Dim sSortOrder as String sSortOrder = "EmpName DESC" drRows = dsEmployee.Tables(0).Select(sExpr,sSortOrder) For Each drSingleRow in drRows dtEmp.ImportRow(drRows) Next ' Datatable dtEmp has filtered records
Filter Datatable by Column
If you need to get subset of big dataset into datatable columnwise…
Dim dtEmp as DataTable Dim dsEmployee as New DataSet dsEmployoee = someobject.getdata() 'dataset can be populated in many ways which is not explained here. 'This copies the structure and data dtEmp = dsEmployee.Tables(0).Copy dtEmp.Columns.Remove("Unwanted Column 1") dtEmp.Columns.Remove("Unwanted Column 2") dtEmp.Columns.Remove("Unwanted Column 3")
Verifying that an object exists
If the table exists, it is deleted. If the table does not exist, the DROP TABLE statement is not executed.
IF OBJECT_ID (N’dbo.AWBuildVersion’, N’U’) IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
OBJECT_ID (‘object_name’, [‘object_type’])
Object Type is optional, and possible values it can hold are.. (Items in bold are frequently used)
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inlined table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table
Prevent Session Timeout in Asp.net [VB]
ASP.Net 2.0 [VB]
I slightly modified few things to work for VB and VS2005 environment. The code works perfect and I’m very happy with it.First I created a Module file and created this function.
– Subtract 30 seconds from it and assign it to MilliSecondsTimeOut variable
– Create a global variable with max value 6
– Get current hour and Min [just for displaying the last refresh time]
– Verify whether count is less than max value (ie 6)
– If so, change the window status with Text
– Create a dummy image and set reconnect.aspx url as its source [this way a call is made to server and it wont session timeout]Create a timer using window.setInterval and assign the MilliSecondsTimeOut value.
[this way this function is called 30 seconds before session timeout]Tha max value (6) can be set to any number. If its 6 then this session timeout is avoided 5 times.
Default session timeout is 20min. 20 * 5 = 100 mins. Which is good for non-secure page.
On the pages where I want to prevent Session Timeout I entered this line.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Try 'dont forget to add this line Page.ClientScript.RegisterClientScriptBlock(Me.GetType, "reconn key", KeepAlive()) If Not Page.IsPostBack Then --- --- --- End If Catch eX as Exception '---- End Try End Sub
Created reconnect.aspx in Visual Studio and deleted the .vb and .designer.vb files.This is the final version of reconnect.aspx
<%@ Page Language="vb" AutoEventWireup="false"%> <%@ OutputCache Location="None" VaryByParam="None" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "<a href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" mce_href="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</a>"> <html xmlns="<a href="http://www.w3.org/1999/xhtml" mce_href="http://www.w3.org/1999/xhtml">http://www.w3.org/1999/xhtml</a>" > </html>
Note : When you compile the project sometimes, VS2005 complains reconnect.aspx is not in right format.
So I EXCLUDED this file from my project. Now VS 2005 will compile without complaining and the logic will also work.- Happy Programming