Saturday, May 30, 2009

Get code in C# at SQL to call a Stored Procedure from C#

you will have to create the procedure written below and run it by passing any Stored Procedure name as a parameter in it .. it will give u code in c# to use that procedure in sql output.

=======================================


CREATE PROCEDURE CS_Code_Generator
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________
Name: CS Code Generator
Version: 1
Date: 13/02/2009
Author: Rajesh Kumar rolen
Description: Call this stored procedue passing the name of your
database object that you wish to insert/update
from .NET (C#) and the code returns code to copy
and paste into your application. This version is
for use with "Microsoft Data Application Block".

*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
--Change the following variable to the name of your connection instance
SET @connName='conn.Connection'
SET @parameterAt=''
SET @outputValues=''
SELECT
dbo.sysobjects.name AS ObjName,
dbo.sysobjects.xtype AS ObjType,
dbo.syscolumns.name AS ColName,
dbo.syscolumns.colorder AS ColOrder,
dbo.syscolumns.length AS ColLen,
dbo.syscolumns.colstat AS ColKey,
dbo.syscolumns.isoutparam AS ColIsOut,
dbo.systypes.xtype
INTO #t_obj
FROM
dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE
(dbo.sysobjects.name = @objName)
AND
(dbo.systypes.status < > 1)
ORDER BY
dbo.sysobjects.name,
dbo.syscolumns.colorder

SET @parameterCount=(SELECT count(*) FROM #t_obj)
IF(@parameterCount < 1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
BEGIN
PRINT 'try'
PRINT ' {'
PRINT ' SqlParameter[] paramsToStore = new SqlParameter[' + cast(@parameterCount as varchar) + '];'
PRINT ''

DECLARE @source_name nvarchar,@source_type varchar,
@col_name nvarchar(100),@col_order int,@col_type varchar(20),
@col_len int,@col_key int,@col_xtype int,@col_redef varchar(20), @col_isout tinyint

DECLARE cur CURSOR FOR
SELECT * FROM #t_obj
OPEN cur
-- Perform the first fetch.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

if(@source_type=N'U') SET @parameterAt='@'
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col_redef=(SELECT CASE @col_xtype
WHEN 34 THEN 'Image'
WHEN 35 THEN 'Text'
WHEN 36 THEN 'UniqueIdentifier'
WHEN 48 THEN 'TinyInt'
WHEN 52 THEN 'SmallInt'
WHEN 56 THEN 'Int'
WHEN 58 THEN 'SmallDateTime'
WHEN 59 THEN 'Real'
WHEN 60 THEN 'Money'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Float'
WHEN 99 THEN 'NText'
WHEN 104 THEN 'Bit'
WHEN 106 THEN 'Decimal'
WHEN 122 THEN 'SmallMoney'
WHEN 127 THEN 'BigInt'
WHEN 165 THEN 'VarBinary'
WHEN 167 THEN 'VarChar'
WHEN 173 THEN 'Binary'
WHEN 175 THEN 'Char'
WHEN 231 THEN 'NVarChar'
WHEN 239 THEN 'NChar'
ELSE '!MISSING'
END AS C)

--Write out the parameter
PRINT ' paramsToStore[' + cast(@col_order-1 as varchar)
+ '] = new SqlParameter("' + @parameterAt + @col_name
+ '", SqlDbType.' + @col_redef
+ ');'

--Write out the parameter direction it is output
IF(@col_isout=1)
BEGIN
PRINT ' paramsToStore['+ cast(@col_order-1 as varchar) +'].Direction=ParameterDirection.Output;'
SET @outputValues=@outputValues+' ?=paramsToStore['+ cast(@col_order-1 as varchar) +'].Value;'
END
ELSE
BEGIN
--Write out the parameter value line
PRINT ' paramsToStore['+ cast(@col_order-1 as varchar) + '].Value = ?;'
END
--If the type is a string then output the size declaration
IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
BEGIN
PRINT ' paramsToStore[' + cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';'
END

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
END
PRINT ''
PRINT ' SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'
PRINT @outputValues
PRINT ' }'
PRINT 'catch(Exception excp)'
PRINT ' {'
PRINT ' }'
PRINT 'finally'
PRINT ' {'
PRINT ' ' + @connName + '.Dispose();'
PRINT ' ' + @connName + '.Close();'
PRINT ' }'
CLOSE cur
DEALLOCATE cur
END
if(LEN(@errMsg) > 0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON
Read More

Monday, May 25, 2009

Primary Keys: IDs versus GUIDs

Primary Keys: IDs versus GUIDs
Personally i love uniqueidentifier in which, every unique item waiting to be born.

Perhaps that's why I read with great interest recent accounts of people switching their database tables from traditional integer primary keys ...

ID Value
-- -----
1 Apple
2 Orange
3 Pear
4 Mango
.. to GUID keys.

ID Value
------------------------------------ -----
C87FC84A-EE47-47EE-842C-29E969AC5131 Apple
2A734AE4-E0EF-4D77-9F84-51A8365AC5A0 Orange
70E2E8DE-500E-4630-B3CB-166131D35C21 Pear
15ED815C-921C-4011-8667-7158982951EA Mango

I know what you're thinking. Using sixteen bytes instead of four bytes for a primary key? Have you lost your mind?
Those additional 12 bytes do come at a cost. But that cost may not be as great as you think:


Using a GUID as a row identity value feels more natural-- and certainly more truly unique-- than a 32-bit integer.
GUID primary keys are a natural fit for many development scenarios, such as replication, or when you need to generate primary keys outside the database.
But it's still a question of balancing the tradeoffs between traditional 4-byte integer IDs and 16-byte GUIDs:

GUID Pros (benefit)
Unique across every table, every database, every server
Allows easy merging of records from different databases
Allows easy distribution of databases across multiple servers
You can generate IDs anywhere, instead of having to roundtrip to the database
Most replication scenarios require GUID columns anyway

GUID Cons (drawbacks)
It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

I'm not proposing that every database switch to GUID primary keys, but I do think it's important to know the option is out there.
GUID provides lots of benefits over traditional integer key.
Read More

Thursday, May 21, 2009

set the session expireation time in web.config file

open web.config file of your web application
you will have tu set sessionstate timeout = value in minuts like below example....



< configuration >
< system.web >
< sessionState mode="InProc"
cookieless="true"
timeout="20"/ >
< /sessionState >
< /system.web >
< /configuration >
Read More

Monday, May 11, 2009

getting Query-String Values From Javascript

Getting Query-String Values From Javascript
script type="text/javascript">
function GetQueryStrValFor(key)
{
var returnval="";
var query = window.location.search.substring(1);
var parms = query.split('&');
for(var i=0 to parms.length)

{ var pos = parms[i].indexOf('=');
if (pos > 0)
{
if(key==parms[i].substring(0,pos))
{
returnval= parms[i].substring(pos+1);
}
}
}
return returnval;
}

script>
Read More

Friday, May 1, 2009

Invalid Operation Exception when u not run program



to remove above error .....go to solution explorer and check resource folder.. exclude it and then include it again...
Read More
Powered By Blogger · Designed By Seo Blogger Templates