Thursday, December 15, 2011

ASMX service in MVC 3 Razor

We at Anthem Infotech pvt. ltd. recently came across an issue with using ASP.net asmx service with ASP.net MVC 3 Razor project, so we set out on a quest to resolve the issue with a number of options in mind 


  1. Using Javascript to call and handle the service calls
  2. consuming the service by simply adding a web reference in the page controllers (naah too easy)
so we decided to go the Javascript way as it best suited our needs and was a challenge for us in technical terms.

before moving to describing the solution, here are the 2 urls which where very helpful in working out a solution



Step 1 is a copy of step 3 from dotnet by example blog, to enable the service to be callable from javascript you need to add the [ScriptService] tag on the top of the web service class.

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[ScriptService]
public class DemoService : System.Web.Services.WebService
{
 [WebMethod]
 public string HelloWorld( string ToSomeone )
 {
   return "Hello World" + ToSomeone;
 }
}

Step 2: Is adding web reference of the service as we normally do in any case


Step 3:


add 2 script tags like below
 src="@Url.Content("~/Scripts/MicrosoftAjax.js")"


 src="@Url.Content("service.asmx/js")"


first tag refers to the microsoftajax.js already present in the script folder of the MVC 3 project and second tag refers to the service asmx page with a trailing "/js" tag this gives the scripted version of service which can then be directly called in javascript methods.


function Regscan() {
 var   ip = hdIP.value; // hdIP is a hidden variable that has the IP value

 demoproject.service.Registeruser(ip, RegscanCallback); 
 // Service call with call back method that is pass the result.

      }

 function RegscanCallback(result) {
          var id = result;
          somemethod(id);
      }







for seeing the asmx java script simply call the web service url with trailing /js you will see the methods that you can call. 


Hold Up

Wednesday, November 2, 2011

ASP.net Base Page


The auto-generated ASP.NET page class must extend the Page class (as shown in the code below), You can optionally create a base class from which all pages will derive from, When creating a base class, you'll typically have this base class extend the Page class. If you are using the code-behind model your code-behind classes will need to be modified to inherit from the new base class as opposed to the Page class. This gives you greater flexibility in controlling your application and reduce code significantly if done correctly for example if you where to place a simple check of user roles and check access on any number of pages by just Inheriting the page from the custom base class you created. 


Standard ASP.NET page code

public class WebForm1 : System.Web.UI.Page
{
 private void Page_Load(object sender, System.EventArgs e)
 {
  // Put user code to initialize the page here
 }

 #region Web Form Designer generated code
    ... code removed for brevity ...
 #endregion
}



Base Page class inheriting System.Web.UI.Page

public class MyBaseClass : System.Web.UI.Page
{
    protected override void OnLoad(EventArgs e)
    {
       // ... add custom logic here ...
       
       // Be sure to call the base class's OnLoad method!
       base.OnLoad(e);
    }
}



Now Page is inheriting from MyBaseClass and not directly from the Page Class

public class WebForm1 : MyBaseClass
{
 private void Page_Load(object sender, System.EventArgs e)
 {
  // Put user code to initialize the page here
 }

}



Now in the  above code we see  that in the MyBaseClass we are overriding the page load method and can add custom code here, I posted a example of base page implementation in my earlier blog about Multilingual website where we had easily implemented the base page across the website and checked the database for user language preference and updated the page culture accordingly with minimal code.


You can get tips on good usage of Base page from this article http://dotnetslackers.com/articles/aspnet/Four-Helpful-Features-to-Add-to-Your-Base-Page-Class.aspx


In the end I would like to state that with Base page you can handle a complex task or a very simple task within the page execution cycle and not having to re-write the code at several locations. 



Hold Up

Thursday, October 20, 2011

calling Javascript methods on updatepanel events

Recently came up with a scenario where I need to call a Javascript function to update a label with client side time on auto save, while auto saving was working fine, the only issue was capturing the time, since the label was updated at server we would end up with server time, so the do get client side time the option is to call a JS function at end request of update panel now the problem, there are multiple update panels on the page how do we know which panel is called, after digging a lot, I fond this discussion on stack overflow http://stackoverflow.com/questions/338702/how-to-call-a-client-side-javascript-function-after-a-specific-updatepanel-has-b 


Code on stack overflow


$(document).ready(function() {
 panelsLoaded = 1;   Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(PageLoaded) 
 }); 


 function PageLoaded(sender, args) { 
   console.log("I have occured " + panelsLoaded++ + " times!"); 
   var panelsCreated = args.get_panelsCreated(); 


   for (var i = 0; i < panelsCreated.length; i++) { 
     console.log("Panels Updating: " + panelsCreated[i].id); 
   } 
  var panelsUpdated = args.get_panelsUpdated(); 


 for (var i = 0; i < panelsUpdated.length; i++) { 
 console.log("Panels Updating: " + panelsUpdated[i].id); 
 } 
 } 


My Modified version 


$(document).ready(function () {        Sys.WebForms.PageRequestManager.getInstance().add_pageLoaded(PageLoaded) 
 }); 


function PageLoaded(sender, args) { 
  var panelsUpdated = args.get_panelsUpdated(); 


  for (var i = 0; i < panelsUpdated.length; i++) { 
     if(panelsUpdated[i].id == "<%=UpdatePanel2.ClientID%>") { 
                 alert('timer called');                     
         } 
     } 
 } 


This enabled us to call timer string update after isolating the update panel, we thanks james for sharing this code, in the first place. Hope this is useful to you, please give your comments and share this article if you do.




Hold Up


kick it on DotNetKicks.com

Monday, September 19, 2011

Multilingual website in asp.net

While its neither new nor overly complex to implement multiple languages in ASP.net, it can be at times confusing and at times bit over the top in certain scenarios. ASP.net provides resource files which can be used to update the content based on browser language choice. The other option is to have a button or user choice saved in the database which is picked up as soon as user logs in. 

Before moving further let me explain a bit what need to done at the html level, first this is key as if you have not planned for this from the start doing this at the last step can get very irritating, while defining the labels/ literals that need to switched over to the other language in addition to ID and Runat tag you need to define resource key like meta:resourceKey ="FirstName" the same needs to be define in the corresponding resource file. with this out of the way, we need to set the page UICulture and Culture the same can be set at Page directive level and in web.config level if you want it to static at global level in the application. 

now we need to override the page Initialize Culture method as shown below, here we are fetching user selected language from class user as a property. and rest is just setting of values no rocket science here.

protected override void InitializeCulture()

 {

        clsUsers obj = new cls.Users();
                                String selectedLanguage = obj.Language;

        UICulture = selectedLanguage;

        Culture = selectedLanguage;

        Thread.CurrentThread.CurrentCulture =
                CultureInfo.CreateSpecificCulture(selectedLanguage);
       Thread.CurrentThread.CurrentUICulture = new
       CultureInfo(selectedLanguage);
      base.InitializeCulture()

}

an alternative to doing this at every page is using a base page class, here a class is inherited by System.Web.UI.Page and above method is written once and same is applicable in all the pages that inherit from base page instead of System.Web.UI.Page. More on base page is updated on the blog.

This is how the class will look like 
public class BasePage : System.Web.UI.Page

This how pages.cs will look like
public partial class UserInfo : SomeProject_BLL.BasePage

Looking forward to your comments 


Hold Up

Tuesday, September 6, 2011

Jquery Graphs

Recently I had to come up with a low cost graphing solution for one of my clients, little did I know that Jquery Graphs would not only be a fringe tools, but something that can be used professionally as well. That said there are both paid and free tools which meet professional standards and are as good as it gets features available. some of the key pro and cons of using jquery graphs are listed below 

PROs
  1. Light weight and very small foot print 
  2. Client side control, so resources are not consumed on the server, and jquery calls can be made to get real time data or read of a XML
  3. Interactive UI, clicks on graphs and specific bars or section can be handled in most tools available.
  4. Easy to implement
  5. Most Important fact is that most of these are actually HTML 5 compliant and render well on all modern browsers


CONs
  1. Lacks support in older browsers support, though they us excanvas.js it fails at times
  2. If you opt for a free tool, you may struggle to find support on the tool
  3. can be complex to use with AJAX or other scripting libraries.
Some of the tools i reviewed are listed below in particular order

  1. JQPlot - Free but an advanced library 
  2. HighCharts - Paid but really good.... you pay $80 for a single site without any login or user based data, but if you have a login and user based data then the price moves to $360.
  3. Flot - Good but the library lacks good examples and documentation and is sightly harder to work on. 




Hold Up

Tuesday, May 17, 2011

NopCommerce - Opensource and effective

This would be the first off the shelf product i am promoting, but i just cant help it, the cart is so dam good, you think that there has to be catch to it but there is none. I have been reviewing this for a while and now i am implementing it for one of my clients. I find it to be very flexible and the forum is great it help out on almost anything. The code is very well structured and does take some getting used to but in the end, it follows all the good practices and have a flexibility of any paid shopping carts. It comes highly recommended from Micosoft as well. 


It is expected that there will be a MVC 3 version out somtime in july


nopCommerce offers unprecedented flexibility and control. Since it is open-source, nopCommerce’s source code is available free for download. Just click here to download your copy today.

Sunday, April 17, 2011

SQL server order of precedence


Anyone who has been writing queries must have come across a situation where somehow the query looks fine but the result is not matching to the expectation. Well then one major problem here is that you are not aware of the logical or arithmetic operator sequence. I have tried to simplify, present to you the order that you should have in mind.



1~ (Bitwise NOT)
2* (Multiply), / (Division), % (Modulo)
3+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
4=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5NOT
6AND
7ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8= (Assignment)



for everyone’s reference if any query has 2 operators with the sample level of precedence then the operator is evaluated from left 2 right as rule of thumb, to override this you can use brackets to increase precedence. 
Hold Up

Wednesday, December 22, 2010

ASP.Net Event Sequence

I have often seen developers struggle with coding due to the simple fact that they don't realize what event follows what, here is quick list of event sequence. some of the events are very rarely used and might not be so important, something like load event sequences is important and is often confused see below.

Load event Sequence
1. Content page Load event.
2. Master page Load event.   
3. Master page controls Load event.
4. Content page controls Load event.


The following is the sequence in which every events occur when a master page is merged with a content page:
  1. Content page PreInit event.
  2. Master page controls Init event.
  3. Content controls Init event.
  4. Master page Init event.
  5. Content page Init event.
  6. Content page Load event.
  7. Master page Load event.
  8. Master page controls Load event.
  9. Content page controls Load event.
  10. Content page PreRender event.
  11. Master page PreRender event.
  12. Master page controls PreRender event.
  13. Content page controls PreRender event.
  14. Master page controls Unload event.
  15. Content page controls Unload event.
  16. Master page Unload event.
  17. Content page Unload event.

kick it on DotNetKicks.com

Wednesday, August 11, 2010

Novice with mobile apps

When i started with VB6 someone told me there is nothing better than application (desktop) development, web is just a fragment of what you can do with application development, then with a shift in technology i moved to web development I did some really good work but most of it was web applications and not websites. now with almost 7 years of web development experience, i feel like a novice when i comes to mobile apps and top that off  they differ based on platform OS, there is that pioneering technology call iPhone apps and then we have Android apps and of course there are those windows mobile apps, blackberry apps, and Nokia symbian apps....


my interests lie in Android and to some extent iPhone apps..... looking forward to exploring and understand the technology and frameworks....   i will update the blogs with tit-bits i learn over a period of time. 

Monday, December 14, 2009

Data Types in SQL Server 2008


I Read a great article on database journal lately about data types in 2008
, i am sharing some basic info.

Numeric data types 


An Integer is a counting number with no decimal point or fractional piece. All negative numbers, positive numbers, and zero are integers. SQL Server breaks integers into four sizes:

BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Int: -2,147,483,648 to 2,147,483,648

SmallInt: -32,768 to 32,767

TinyInt: 0 to 255


Here as a part of software design you need to clear what to use in which cases, it may be a simple choice to pick the largest range, but looking deep you need to understand first Bigint uses double the space as int, and the other being the application consuming the database gets what it expects and there are not overflow errors.

for decimal value numbers, there are  Decimal, Numeric, Money, and SmallMoney. The types Decimal and Numeric are functionally the same thing. Meaning, they will work, calculate and behave identically, the only difference is in the mathematical definition and not in the way SQL Server utilizes them.

Money and SmallMoney are really Decimals with a fixed amount of four decimal places to the right. SmallMoney can be valued from - 214,748.3648 to 214,748.3647 while the range of Money is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. One of the reasons for using Money rather than a Decimal includes the display option of dollar signs and commas after three digits.


Strings  


Here we will discuss Char, VarChar, and Text. As most of you might me aware of difference between char and varchar, where when a char(50) and varchar(50) columns are defined, char takes up 50 Bytes in respective of size of content saved in the column where varchar is flexible and takes up bytes required to save the length of the content. 


Char must be used in case of where it is know or understood that length of the text would be fixed like a char(1) column would make much more sense in case of a Sex column where only M/F is expected to saved instead of varchar, where as something like name should be varchar. 


Varchar has a max limit of 8000, though a new introduction of Varchar(max) has made it more flexible as the size can be unlimited in this case, and the main benefit of this is that as in case of Text column string operations cannot be done, while here all the string operations can be handled easily. Also the thing to note here is that Varchar(max) is all set to replace text in the future versions of SQL server as Text is supported in 2008 only as a part of backward compatibility. 


I will adding more to this, hope you like it.... please Digg, Kick or share this if you like this.     


kick it on DotNetKicks.com

Friday, November 27, 2009

Resetting DNN passwords



Yesterday me and my team came across a strange problem, we had a client DNN deployment on our server for demo, and for long the client was not interested or was too busy and suddenly he needs the see the site the problem the original developer was not available and no one else know the host or admin password 


BIG PROBLEM.....


Luckily one of my team member were able to find a post with solution, i am re- posting the procedure.




CREATE procedure [dbo].[uap_ResetPassword]
@UserName NVarChar(255),
@NewPassword NVarChar(255)
as
begin
Declare @PasswordSalt NVarChar(128)
Declare @ApplicationID NVarChar(255)
Declare @ApplicationName NVarChar(255)


Set @ApplicationID = (SELECT [ApplicationID] FROM aspnet_Users WHERE UserName=@UserName)
Set @ApplicationName = (SELECT [ApplicationName] FROM aspnet_Applications WHERE ApplicationID=@ApplicationID)
Set @PasswordSalt = (SELECT PasswordSalt FROM aspnet_Membership WHERE UserID IN (SELECT UserID FROM aspnet_Users WHERE UserName=@UserName))


–select @ApplicationID, @ApplicationName
declare @RetVal as int
Exec @RetVal = dbo.aspnet_Membership_ResetPassword @ApplicationName, @UserName, @NewPassword, 10, 10, @PasswordSalt, -5
return @RetVal

end


Orignal post: http://www.devprise.com/2006/09/26/how-to-reset-a-dnn-password-at-the-database/


Thanks Tony...





kick it on DotNetKicks.com

Thursday, October 29, 2009

SQL Server Database analysis scripts







While browsing the net my team came across couple of very good analytical scripts, i would like to share this with everyone. often there is a scenario where you wonder how to find out what script or procedure is consuming most memory or have the most reads, this help to identify the possible bottle necks in the website.

The query below is really useful

1) Following Query will return top 10 queries having maximum order by number of executions:




SELECT TOP 10 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = 'DatabaseName')
ORDER BY qs.total_physical_reads DESC
Though this is not as useful as query one, but can be handy in case of really large databases, where you need to identify what table is consuming how much memory, and may be the the data in some tables is not all that relevant now and can be deleted conditionally, help improve the performance.

2) Query to check the size of each table:



EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
One major point, you need admin/sa access to run these scripts
Look forward to your comments on the same



kick it on DotNetKicks.com

Tuesday, July 21, 2009

Performance optimizing the SQL Store Procedure

I have noted below some of the key features that can be easily implemented and made a habit during the course of deveopement that can in future boost your performance.

NO COUNT
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.
To turn this feature off on at the stored procedure level, you can include the statement:
SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
If you want this feature turned off for your entire server, you can do this by running these statements at your server:
SP_CONFIGURE 'user options', 512 RECONFIGURE
but i don't recommend this as in certain cases you might need that all important row count.

Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.

Three ways to help reduce the length of a transaction are to:
1) break up the entire job into smaller steps (or multiple stored procedures or user defined functions) so each step can be committed as soon as possible
2) take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server.
3) if there is a certain set of statement or calculations that is being done repeatedly in procedures use user defined functions to reduce code size and improve performance
Procedure Naming
If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_"
Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.

Executing Strings
When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql offers two major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.
Sp_executesql executes a string of Transact-SQL in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.


kick it on DotNetKicks.com

Thursday, July 2, 2009

Benefits of Stored Procedures - Explained (Part 1)

Network Latency


One of the biggest advantages of using stored procedures over not using stored procedures is the ability to significantly reduce network traffic. And the more network traffic that can be reduced, the better the overall performance of your SQL Server-based applications.


case 1.
when we call a stored procedure from your application we just make a small RPC call while image if the stored procedure encapsulates 200~500 lines of SQL code, and if the same was to be moved to application code all the SQL in form of string will first have to sent across the network creating number of network packets and now imagine if 100 users are calling the same code at once you have one very busy network.


case 2.
when we are executing a number of statements based on some business logic, it is best all the business processing is done at stored procedure level. eg. lets say we have master detail relashionship where master data and detail data is to updated or inserted in one go. now if we were to use client side or application to handle all this we will first have to createa a master insert statement (T-SQL) , that would be sent across the network like explained above and then we will need to retrieve the master id just created with the insert statement. Then we proceed to creating a update or insert statement for the details table and now following the same process of sending the T-SQL to database and then having the needful done. while it would be just one call to a stored procedure with all the required parameter to handle all the above defined.


It is recommend to move as much of business logic to the stored procedure in that you would be moving all the processing to the sql server reducing the round trip network traffic.



In case you have any questions or comments please post them i will try and respond.






kick it on DotNetKicks.com

Monday, June 29, 2009

Benefits of Stored Procedures

Before I go on to explain the benefits, let me explain why I am writing this. We use stored procedures as a standard practice, not thinking twice of the very reason behind this.

I realized that we follow standard practice without questioning it, not understanding its perks and pit falls, so I decided to list out the benefits I understand and share it with my team via this blog. Benefits are listed below I will explain the points one by one in the future.

Stored procedures offer many benefits, including:

  • Reduced network traffic and latency, boosting application performance.

  • Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.

  • Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.

  • Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.

  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don't remove any result sets columns). This saves developer time.

  • Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access.
Warning: using stored procedure does not garuntee fast speed, there has to some level of code design, to make the procedure fast and reusable.

Please add comments to blog, i would like to hear what you this of this.

Wednesday, June 24, 2009

Use view to improve query performance


Recently I came across two such scenarios where a large dataset (1,000,000 + rows) was being retrieved using a complex set of joins in stored procedure, we did all the regular stuff
1. Checked Indexes - made sure they are on the right columns and verified the fill factor
2. Re indexed the database for better performance
3. Checked and removed the non essential joins
4. Verified join types

we did have some improvement in performance, but nothing like we had with using views instead of complex select query, we were able to get performance levels increased by 10 t0 40 times in specific cases.

Now the question is why?
I read the following while searching on another topic and it struck me, a view is an optimized query object, SQL server itself chooses the best execution plan and then when we do a filter on the view the nested loops are decreased considerably as only one select is being filtered rather that all the tables in the join..... It really helps

From the Database Management System (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for the executing query. The query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

Friday, June 19, 2009

FTP without FTP client on windows XP

I am sure everyone has used FTP and FTP Clients one time or the other. what happens when you want to connect to a ftp and dont have a ftp client, i just got to know of connecting to FTP with DOS, so i am sharing the info

1) Open a command windows (start->run_.cmd)
2) Type ftp
3) Type open ftp.ftp.somesite.com
4) Type username: ftpuser
5) Type the password: ftppassword
6) Type “dir” to see the content
7) Type “binary” Or "ASCII" to change to download Type
8) Type: “get filename” to download

also note that the filenames are case sensitive here so be careful.

Thursday, April 30, 2009

UPDATE script with Joins


I know this is basic but we tend to forget the basic stuff, today when a client asked me to update the date from one table into another table with 100's of records i for a moment thought that’s manual data entry and then it struck me a simple update statement will do it. Guess this is bound to happen if you work more at client end and not development.

well here is a sample of what i did and i was amazed that if i had opted for manual data entry it would have taken me hours. and the script took me 5~6 minutes.

here is what i did

update Table1 set Table1.col1= Table2.col2 from Table2 where Table1.Id= Table2.id


of course I could have used Inner join or any other standard join as and when required.

Thursday, November 20, 2008

Insert Script for MS SQL Server

Browsing the net i found this great script it creates a procedure with takes table name as input and returns a insert script for table works for both SQL Server 2000 and 2005. Great to use and no additional server requirement or installs required.

/***** Object: StoredProcedure [dbo].[sp_CreateDataLoadScript] Script
******/

SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ON

GO

Create Procedure[dbo].[sp_CreateDataLoadScript]@TblName
varchar(128)

as

/*execsp_CreateDataLoadScript 'MyTable'*/

create table #a (id intidentity (1,1), ColType int, ColName varchar(128))

insert #a
(ColType,ColName) select case when DATA_TYPE like '%char%' then 1 else 0 end
,COLUMN_NAME from information_schema.columns where TABLE_NAME =@TblNameorder by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,@maxid int,@cmd1 varchar(7000) ,@cmd2 varchar(7000)

select @id = 0 ,@maxid =max(id)from
#a

select @cmd1 = 'select '' insert ' + @TblName + ' ('select @cmd2 = ' + ''
select '' + 'while @id < @maxid begin select@id = min(id) from #a where id > @idselect

@cmd1 = @cmd1 + ColName +',' from #a where id = @idselect

@cmd2 = @cmd2+ ' casewhen ' + ColName + ' is null '+ ' then ''null'' '+ ' else '+
case when ColType = 1 then ''''''''' + ' + ColName + ' + '''''''''
else 'convert(varchar(20),' + ColName + ')' end+ ' end + '','' + 'from #a
where id = @idendselect

@cmd1 = left(@cmd1,len(@cmd1)-1) +' ) '' '

select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblNameselect '

/*' + @cmd1 + @cmd2 +'*/'

exec (@cmd1 + @cmd2)

droptable #a



Note: you will have to turn of identity column if you want to insert the primary key as well.

Tuesday, November 18, 2008

Back button with Ajax in ASP.Net

I am sure anyone who is worked on AJAX grids has come accross a QA or client who wants the grid pager to go to previous page when they hit browser back button. well asp.net 3.5 Sp1 has the perfect answer, it has a history control which allows you to add history on your ajax enabled pages. it is defined in five simple steps on the blog link mentioned below.

http://geekswithblogs.net/ranganh/archive/2008/11/17/enabling-the-browserrsquos-back-button-for-grid-view-asp.net-ajax.aspx

basic logic here is that you are able to create history points to you browser on AJAX postback.