Update using XML part2

ALTER proc [dbo].[sp_Update_ModelPrice]
(
@xmlString varchar(max)
)
as
DECLARE @index int
--set @xmlString='<brand><model id="1"><category id="1"><subCatid>1</subCatid><price>12</price></category></model></brand>'
BEGIN
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
update dbo.tblModelPrice
set Price=xmlprice

FROM OPENXML (@index, 'brand/model/category')
WITH (xmlModelId int '../@id',xmlCatid Varchar(10) '@id',xmlsubCat varchar(100) 'subCatid',xmlprice varchar(100) 'price')
where ModelID=xmlModelId and ModelCategoryID=xmlCatid and ModelSubCategoryID=xmlsubCat

EXEC sp_xml_removedocument @index

END

Update tale using XML date

1. Create this table:

   CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))
   

2. Add sample data:

   INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
   INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')
   

3. Create this stored procedure in the database:

   CREATE PROC sp_update_employee @empdata ntext     
   AS     
   DECLARE @hDoc int       
   exec sp_xml_preparedocument @hDoc OUTPUT,@empdata  
   UPDATE Employee     
   SET     
         Employee.fname = XMLEmployee.fname,    
         Employee.lname = XMLEmployee.lname      
   FROM OPENXML(@hDoc, '/root/Employee')   
                WITH Employee XMLEmployee    
   WHERE  Employee.eid = XMLEmployee.eid    
   EXEC sp_xml_removedocument @hDoc    
   SELECT   *    
   from      Employee 
   FOR XML AUTO    

delete object from database

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'p'
OPEN cur
FETCH NEXT FROM cur INTO @procedureName
WHILE @@fetch_status = 0
BEGIN
EXEC('DROP PROCEDURE ' + @procedureName)
FETCH NEXT FROM cur INTO @procedureName
END
CLOSE cur
DEALLOCATE cur



























#region Enail
                            var MailDetails = (from objemail in objDb.tblEmailSettings where objemail.EmailSettingsID == EmailSettingsID && objemail.IsActive == true select objemail).Single();
                            if (userDetail != null)
                            {
                                if (MailDetails.EmailSettingsID > 0)
                                {
                                    Utility formail = new Utility();
                                    string Tomail = userDetail.Email;
                                    //string FromMail = System.Configuration.ConfigurationManager.AppSettings["MailUid"].ToString();
                                    string FromMail = "kesri.software@gmail.com";
                                    string Subject = MailDetails.EmailSubject;
                                    string Body = MailDetails.EmailBody;
                                    //[First Name]&lt;br /&gt;  [Middle Name]&lt;br /&gt;  [Last Name]&lt;br /&gt;  [Email]&lt;br /&gt;  [Password]&lt;br /&gt;  [ApplicationId]&lt;br /&gt;  [Coupon Code]&lt;br /&gt;  [Message]&lt;br /&gt;  [Site Address]&lt;br /&gt;
                                    Body = Body.Replace("[First Name]", userDetail.First_Name);
                                    Body = Body.Replace("[Middle Name]", "");
                                    Body = Body.Replace("[Last Name]", userDetail.Last_Name);
                                    Body = Body.Replace("[Email]", userDetail.Email);

                                    string pas = ClsDataEncryption.Decrypt(userDetail.Password.Trim(), "passKey");

                                    Body = Body.Replace("[Password]", pas);
                                    Body = Body + "  http://www.globicle.com";
                                    Body = HttpUtility.HtmlDecode(Body);
                             
                                    bool u = formail.SendMail(Tomail, FromMail, Subject, Body);
                                    if (u == true)
                                    {
                                        return Json(new ClsJsonResult { Result = true });
                                    }
                                    else
                                    {
                                        return Json(new ClsJsonResult { Result = false });
                                    }
                                }
                            }
                            #endregion

insert from xml in sql stored procedure

CREATE PROCEDURE [dbo].[InsertSP]
@strXML varchar(1000)
AS
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
Insert into Months
Select * from OpenXml(@intPointer,'/months/month',2)
WITH ( nMonthNo int, cMonth varchar(15))

exec sp_xml_removedocument @intPointer
RETURN
GO

and call the procedure as follows,

exec insertsp '<months><month><nMonthNo>1</nMonthNo><cMonth>January</cMonth></month><month><nMonthNo>2</nMonthNo><cMonth>February</cMonth></month></months>'

Roll back and transaction in sql server procedure

CREATE PROCEDURE DeleteDepartment
(
   @DepartmentID    int
)
AS

-- This sproc performs two DELETEs.  First it deletes all of the
-- department's associated employees.  Next, it deletes the department.

-- STEP 1: Start the transaction
BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
    RETURN
 END


DELETE FROM Departments
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
    RETURN
 END

-- STEP 4: If we reach this point, the commands completed successfully
--         Commit the transaction....
COMMIT

pass array of items in sql server stored procedure and fetch record from it.

First of all,u need to pass a string from ur .net app. like '1,2,3,4,5,6,7' or in any format.
Then use this function to split your comma seprated string and here is the stored procedure to fetch record.



CREATE procedure [dbo].[test](@parameter varchar(5000)) As
begin
declare @val varchar(max);
select * from emp where convert(varchar,Id) in(select * from dbo.fnSplit(@parameter,'.') )

end

Split function in SqlProcedure

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter char(1)  -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

sql query to display parent name as manager if parent id is null

select e2.id,e2.name,isnull(e1.name,'Manager') as parentname
from emp e1
right outer join emp e2 on e2.parentId=e1.Id

sql query

select group_product.*,product.*,(select Sum(user_bid.bid_points) from user_bid where user_bid.product_id=group_product.product_id)
as totbid
from group_product,product
where group_product.product_id=product.product_id

Get driving directions on map.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <title></title>
    <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2">
    </script>
    <script type="text/javascript">
        var map = null;
        function GetMap() {
            map = new VEMap('myMap');
            map.LoadMap(new VELatLong(47.62054, -122.34947), 16, VEMapStyle.Road, false);
            var pinpoint = map.GetCenter();
            shape = new VEShape(VEShapeType.Pushpin, pinpoint);
            shape.SetTitle("Fourth Coffee Company");
            shape.SetDescription("This is where we are located!");
            map.AddShape(shape);
        }

        function GetDirections() {
            var what = document.getElementById('from').value;
            var options = new VERouteOptions();
            options.DrawRoute = true;
            options.SetBestMapView = false;
            options.RouteCallback = onGotRoute;
            map.GetDirections([what, map.GetCenter()], options);
        }

        function onGotRoute(route) {
            var legs = route.RouteLegs;
            var turns = "Total distance: " + route.Distance.toFixed(1) + " mi\n";
            var numTurns = 0;
            var leg = null;
            for (var i = 0; i < legs.length; i++) {
                leg = legs[i];
                var turn = null;
                for (var j = 0; j < leg.Itinerary.Items.length; j++) {
                    turn = leg.Itinerary.Items[j];
                    numTurns++;
                    turns += numTurns + ".\t" + turn.Text + " (" + turn.Distance.toFixed(1) + " mi)\n";
                }
            }
            document.getElementById("directions").value = turns;
        }
    </script>
</head>
<body onload="GetMap()">
    <h5>
        We are located in Downtown Seattle by the Seattle Space Needle</h5>
    <div id='myMap' style="position: relative; width: 600px; height: 400px;">
    </div>
    <br />
    <form name="form" action="" method="post">
    <input id="from" type="text" value="" name="From" />
    <input id="findit" type="button" value="Find It!" name="find" onclick="GetDirections();" />
    <br />
    <br />
    Your directions are:<br />
    <textarea id="directions" cols="50" rows="20" value=""></textarea>
    </form>
</body>
</html>

prompt a user at the time he leaves the current page using javasript.

Sometimes it happens that we have to prompt and ask the user that he is really willing to leave the current page or not. In my example, I have a survey on my site. when the user is in the progress of survey,suddenly he clicks on a link,that causes him to leave the page. At this time, I have to store its survey response to my database. Here is a simple javascript event to handle this situation.

  window.onbeforeunload = function () {

        return doSubmit(); //here doSubmit is my ajax method,that makes an ajax call and save the data.

    }

Or sometimes we have to clear the browser data when user leaves the page. This is a simple block of javascript.

Have fun.

Get Location Info from an IP address

First of all,you need to get an API key to use this service. You will get it free from http://ipinfodb.com/
After generating your API key,you need to call a service url with the IP (that you want to look up). The Response by the requested service returns in XML format.
Here is a simple running code.


 private DataTable GetLocation(string ipaddress)
    {
        DataTable dt = new DataTable();
        WebRequest wreq = WebRequest.Create("http://api.ipinfodb.com/v2/ip_query.php?key=<your key goes here>&timezone=true&ip=" + ipaddress);
        WebProxy proxy = new WebProxy("http://api.ipinfodb.com/v2/ip_query.php?key=<your key goes here>&timezone=true&ip=" + ipaddress, true);


        wreq.Proxy = proxy;
        wreq.Timeout = 5000 ;
        WebResponse wres = wreq.GetResponse();
        XmlTextReader xmlRead = new XmlTextReader(wres.GetResponseStream());
        DataSet ds = new DataSet();
        ds.ReadXml(xmlRead);
        return ds.Tables[0];


    }


To call above method,

 DataTable dt = GetLocation("117.194.12.179");


Enjoy.

paging in sql server stored procedure

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS


DECLARE @FirstId int, @FirstRow int


SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow


-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.


SELECT   @FirstId = [Id]
FROM     dbo.Country
ORDER BY [Id]


SET ROWCOUNT @PageSize


SELECT   *
FROM     dbo.Country
WHERE    [Id] >= @FirstId
ORDER BY [Id]


SET ROWCOUNT 0
GO 


===================================================
Just pass the page number and page size to the procedure.

javascript to select all checkboxes by parent checkbox and uncheck parent one if one of the child is unchecked

<script>
window.onload=function(){
            var cbChild = $("[id^='chkOpportunity_']");
                 cbChild.onchange=function(){
                         checkParent();
                  }  
}
$(document).ready(function () {
            checkParent();

          });
        function checkParent() {
            var cbkParent = document.getElementById('chkAll');
            var cbChild = $("[id^='chkOpportunity_']");
            for (var i = 0; i < cbChild.length; i++) {
                if (cbChild[i].checked) {
                    cbkParent.checked = true;
                }
                else {
                    cbkParent.checked = false;
                    break;
                }
            }
        }
</script>
<input type='checkbox' id='chkAll' />

<input type='checkbox' id=' chkOpportunity_1 ' />
<input type='checkbox' id=' chkOpportunity_2 ' />
<input type='checkbox' id=' chkOpportunity_3 ' />
<input type='checkbox' id=' chkOpportunity_4 ' />
<input type='checkbox' id=' chkOpportunity_5 ' />
<input type='checkbox' id=' chkOpportunity_6 ' />

Extract email ids from a remote web page.

 List<string> result = new List<string>();

        // used to build entire input
        StringBuilder sb = new StringBuilder();

        // used on each read operation
        byte[] buf = new byte[8192];

        try
        {
         
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(url);
            HttpWebResponse response =(HttpWebResponse) request.GetResponse();


            Stream resStream = response.GetResponseStream();

            string tempString = null;
            int count = 0;

            do
            {
                // fill the buffer with data
                count = resStream.Read(buf, 0, buf.Length);

                // make sure we read some data
                if (count != 0)
                {
                    // translate from bytes to ASCII text
                    tempString = Encoding.ASCII.GetString(buf, 0, count);

                    // continue building the string
                    sb.Append(HttpUtility.HtmlDecode( tempString));
                }
            }
            while (count > 0); // any more data to read?
            lblReeult.Text =(sb.ToString());

            var mc = Regex.Matches(sb.ToString(),"([a-zA-Z0-9_\\-\\.]+)@([a-zA-Z0-9_\\-\\.]+)\\.([a-zA-Z]{2,5})");

            foreach (var c in mc)
            {
                result.Add(c.ToString());
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }

        return result;