Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Archive for July, 2010

Absolute Beginners Guide to Entity Framework

Posted by Viral Sarvaiya on July 22, 2010


The Entity Framework bridges the gap between how developers commonly manipulate conceptual objects (customers, orders, products; posts, tags, members; wall posts, private messages, friend connections) and the way data is actually stored (records in database tables).  The technical term for a tool that provides this abstraction is object relational mapper (ORM).  ORMs help developers be more efficient and focused, since they don’t need to spend brain cycles thinking about how to communicate with the database.  It also means that the code is more portable – switching database software requires changing a setting in the ORM, not a rewrite of the whole codebase to match the new database’s dialect.  As someone who has programmed using the ORM in Django, I can tell you how such a tool makes development less tedious and more enjoyable when you don’t have to consider SELECTS and INSERTS.  In fact, I’ve never before written a line of SQL, yet I was able to build a rich web application thanks to an ORM

for more details :

http://blogs.msdn.com/b/adonet/archive/2010/07/19/absolue-beginners-guide-to-entity-framework.aspx

Posted in ASP.NET | Tagged: , , | Leave a Comment »

Using Google API showing search Result with asp.net

Posted by Viral Sarvaiya on July 20, 2010


Google allows external applications to access their search engine through their search webservice. This webservice, and many others, was implemented via the Simple Object Access Protocol, or SOAP. SOAP is a ‘lightweight’ protocol that is ideal for exchanging information in web environments, since it provides a standard way for passing data between different types of systems. It is based on XML, and allows for the definition of simple objects and Remote Procedure Calls, or RPC (calling a method from your program that ‘lives’ on another server) and the RPC responses.

In order to take advantage of this web service using ASP.NET, we’re going to have to build a proxy class which will serve as an intermediary between our program and the web service. That sounds like a whole lot of work, but fear not, Microsoft has a utility (wsdl.exe) that automates this process for us.

For More Details : http://www.communitymx.com/content/article.cfm?page=2&cid=E8E8CE970C6AB073

Posted in ASP.NET | Tagged: , , , , | 2 Comments »

Combine Multiple Rows in One

Posted by Viral Sarvaiya on July 9, 2010


I have a table as:

itemID        tagID
100                111
200                222
200                223
300                333
300                334
300                335

I want to have a select statement to group records by itemID and one itemID will have only one row, multiple tagIDs will be combined in one field.

Step1: create a scalar-valued function (UDF)

ALTER FUNCTION sUDF_itemTagIds(@aItemId int)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @tagids varchar(max)
SELECT @tagids = COALESCE(@tagids + ‘,’,”) + convert(varchar(20),tagID)
FROM myTable
WHERE itemID = @aItemId
RETURN @tagids
END
GO

Step2: call the UDF from the SQL script:

SELECT itemID,dbo.sUDF_itemTagIds(itemID) as tagIDs
FROM myTable
GROUP BY itemID

Here is the result:

itemID         tagIDs
100                111
200                222,223
300                333,334,335

Intro to User Defined Functions

The ability to create a user-defined function (UDF) is a new feature added to SQL Server 2000.

A user-defined function is a database object that encapsulates one or more Transact-SQL statements for reuse. This definition is similar to the one for stored procedures, but there are many important differences between user-defined functions and stored procedures—the most pronounced being what types of data they can return.

Scalar Functions

A scalar function returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp.

Inline Table-Valued Functions

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement.

Multi-Statement Table-Valued Functions

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined.

COALESCE

Returns the first nonnull expression among its arguments.

Syntax

COALESCE ( expression [ ,...n ] )

http://bimawen.wordpress.com/2009/09/03/combine-multiple-rows-in-one/

Posted in Sql Server | Tagged: | Leave a Comment »

Restore SQL server database with structure and data from bak file

Posted by Viral Sarvaiya on July 9, 2010


A bak file is generated for backup a database structure and data. If you want to restore a bak file from SQL Server database, you can do it with SQL Server management studio express as followed:

  1. start SQL Server Management Studio Express ( I am using SQL Server 2005 express);
  2. create a new empty database “aNewDb”;
  3. right click on Databases in Object Explorer;
  4. select  “Restore Database”, and then Restore Database window pops up;
  5. select the new-created database “aNewDb” to “To database:” box;
  6. check “From device:” and go find the bak file, and then one record should show up in backup sets;
  7. check the checkBox in the sets list for restore;
  8. click “Options” on the left, and MUST check “Overwrite the existing database”;
  9. MUST make sure that the files for “Restore As” are of the “aNewDb”, but sysft_aNewDb’s path could not be same as the path of the mdf and ldf files.
  10. click “OK”, and done!

You would get the new database with the backup data.

http://bimawen.wordpress.com/2009/06/12/how-to-restore-a-database-structure-and-data-from-a-bak-file/

Posted in Sql Server | Tagged: , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: