Krishan Kayat

How to find Nth highest salary in ms sql server

2018-07-11T20:40:00.000-07:00

some guys confused how to write a query for getting output in sql server.

there are so many way to get Nth salary .......... i am explaining two ways below find :

select * from (select Dense_Rank() over (order by salary desc) as denserank,salary from SalaryMaster) as r where denserank=Value

Value=on which position rank you want i.e. 2 or 5th

this is a Dense_Rank() function which gives you a sequence without skipping a number order. this is the best way to get Nth salary in a query.

--------------------------------------------------------------------

2nd way.

with CTE

with CTESalary

(

Dense_Rank() over (order by salary desc)as denserank,

,salary from SalaryMaster

)

select * from CTESalary where denserank=2

iphone review

2017-07-27T05:29:00.002-07:00

Unable to add google gmail in iphone

2017-04-14T03:45:00.001-07:00

Hello guys,

i deleted my account and tried to add again but i was facing problem when i was entering my username and password then screen was flashing once then again go to choose option screen .

solution is you'll get micro second to save your setting . when you entered your details then screen will show save button upper right corner. then quickly press save button and you'll see your account has been added. thnx . hope it'll helpful.

Understanding ASP.NET MVC using real world example, for beginners and intermediate

2016-08-01T05:46:00.004-07:00

ASP.NET MVC

First thing is to understand the ASP.NET MVC framework itself, once we’ve gotten enough understanding of the ASP.NET MVC framework, then it would be a lot easy for us to understand why ASP.NET team provided us with this framework. In web development, there are different schemes used to create web sites and web applications, which at a minor age don’t seem to cause a problem. Solving a bug might take hours of headscratching and some other help too. It is harder to find the problem that is scripted inside the HTML web page. In these scenarios, it is always found helpfull to seperate your code from your data and from your HTML markup. This would make it a lot easier to find and solve the problem. Because, now all of the code has been seperated and finding the problem at exact location won’t be a harder task. These three fields are divided and make up the MVC; Model, View and Controller pattern for web development.
Actually this is not a pattern, specific to the web development. If you go to Wikipedia’s web page for this, you will find that it is actually a framework used for software development, meaning that it can be applied anywhere, where you want to distinguish between your application’s data link layer, business logic layer and the rendering code.

The link between these three objects of MVC pattern have been depicted in this image above.

Controller

The Controller in the MVC comes at the last, but is the most used part of the MVC pattern. It is used to work with the HTTP requests, coming from the clients; from the browsers or from any other application that can generate an HttpRequest (not to be confused with the .NET’s HttpRequest object; but a simple HTTP Request). Each request, when comes, is handled by the Controller and then Controller, according to the request makes decisions to load the data, create the response and then sends the data back to the client.
It should also be noted here, that your Controller acts as a bridge between your Model and the View. Because they, as themself, cannot perform any action. Controller triggers their events and makes them do something, like return data from Model, or to render the HTML document from the View etc. All of the resources and errors are also handled by the Controller. Making it the heart of the pattern, because the response is also sent back from a controller. You can think of an example of a Controller to be the entire Business-logic-layer. The code that is used to run the application’s back-end processing, like creating accounts, managing solutions etc, would make up the Controller section of this pattern.

View

Next comes the part of the View, this is the actual web page that is being displayed to the user. It contains the HTML codes that are to be sent back to the data as a response to his request. Correct, Controller sends this response back to the client, View – its self – doesn’t send this response to client whereas Controllers takes this data, and sends back to the client.
View, can also be created dynamically. As already said, all of the requests are handled by Controller, so any parameter (including QueryStrings) can also be handled by Controllers. Using these parameters, we can generate dynamic Views. So dynamic content in our view, change their layouts or show some other error messages if the data sent is not of our own choice. View, generally depends on the Model that is being used to create the View and these dynamic data objects are capture from the Model (Model is discussed in the next section).
Point to be noted here is that while section-in-action is View, still Controller is playing a vital role for passing the values and for retrieving the data to be sent to client.

Model

As the name suggests, it is a model of some object. The object in this case is our application’s data. It can be of any type, like extracted from a database; no matter which one, SQL Server, MySQL or MS Access etc, or it can be a simple data that comes from a Reporting, or from a Spreadsheet etc. Model is never shown to the user (actually, the client) because he is supposed to see the data and the results we want him to see, that is why, it is a good approach to keep a great abstraction layer between Model and the user (the client).
Model doesn’t only store the data, it – at the same time – keeps the View and the Controller updated or any change being made to it. Models are designed, just like Controllers and Views are designed, just so that there is no ambiguity between three of them and it is easy for them to communicate to make the web application fluent. Everytime a change is made, View is update by the Controller, because Controller is informed about the change (this informing event is also raised by Controller; as I already said, Controller handles the events). To store anything in the Model, the user has not been provided with any form that is directly connected to the Model, instead a form is generated in the View by the Controller for the user to fill in. Once the form is filled, the form values are then passed to the model for storing purposes. All kinds of data validations (most special type of which are SQL Injections) can be checked at the Controller level rather than loosing (important) data.

The user must be allowed to interact with Model himself, instead a Controller must be used to connect to the Model to get the data for the user’s View that would be shown to him.

Until now, we’ve been discussing the ASP.NET MVC itself, in the next section we will be discussing a real-world example of an ASP.NET MVC Application, and I will show how to create custom Controllers, Views and Models. In an ASP.NET MVC project a user triggers the Controller, the controller then reads and manipulates the requests, Requests the Model for data, gets the data and then Updates the View to be sent back to the client.

Overview of MVC pattern.

Real world example of ASP.NET MVC Application

In this blog post, I am going to explain creating an MVC application to control your client’s data. For that, I am going to use the Model, View and Controller architecture, so that I can easily distinguish between the data of my application (which would be the data for the clients), code for the requests and responses (which would constitute the controller) and how the data is shown to the users (views of my application). I am going to create everything from scratch, to make every thing understandable for you. For that I am going to start from creating every single object in my application, every controller, every view and model in my project.
One thing you should think about here is that I am going to use JSON files as data source, because database field has already been covered by the ASP.NET team at its best. I don’t think there is any need to re-write the same thing, that is why I am going to explain how to use anything as your model. If you want to read that one, and want to stick to SQL Server database you can go to ASP.NET MVC’s tutorial website to learn that. You’re right, Entity framework won’t be supported here! That is why I am saying I am going to create everything from scratch, to explain every bit of the MVC pattern for you and how you can create actions that respond to your actual processes, not just to the built-in ones.

Real-life example

Let’s have an example of a simple office, where a person gets different clients, whose name, address is stored. Just to distinguish them all, an ID is also stored. Whether the client is trusted or not, is also stored along with him. Office manager wants to be able to, create new clients, modify the existing ones, and once he is done with them he wants to be able to delete the previous ones. To implement this basic example in ASP.NET MVC, we will start off by creating a simple ASP.NET MVC application. To do so, you can either press CTRL+SHIFT+N, or you can click on File, then New and a Project. Like this,

Creating a new project – in Visual Studio 2013

After this, please select the Web and inside select ASP.NET Web Application, then name it as you want. I named it, ASPNET_MVC_Application. You can use any name that you want.

Selecting an ASP.NET web application.

Then you will be required to make the selections for your application in the next tab. You shouldn’t make any change for now, keep the selected options selected and click Next on this tab.

This would then create your application, with the default content for your application settings. You might see this web page inside your Visual Studio.

ASP.NET MVC read me page viewed inside the Visual Studiom 2013 – application’s first run.

This is the tutorial until creation of the application. This web page is also a sign of success, if you still want to make sure that everything runs, just press the green button (with a Browser’s name on it; mine had Google Chrome) to see your application runs in the browser. In the next section, I will be creating the application’s objects (MVC) and then showing you how they can interact and in the end I will give some tips for making your application better.

Customizing the ASP.NET MVC Application

Once all of the things are set, and your application runs. You can continue to make changes to it, so that it would reflect your own application and not the default ASP.NET MVC application that is a sample. First thing to do is to create a controller which we’re going to be using inside our application to control all of the requests and is going to send back the response (view). How to create views and what would be the model will be covered in coming sections. For now, just make up your mind to understand the Controller in MVC.
Required package: Since we’re going to use JSON data, Newtonsoft.Json package is a very usefull package to use, so before you go any further open your NuGet package manager console (inside the Tools) and run the following command to download and include it in your project. It is required.

Install-Package Newtonsoft.Json

Creating a custom Controller

A custom controller is something that you can use to create your custom controllers for your application, that would handle the requests coming for them and they will provide the user with a view filled in with the data from the model. You can name them as you would like to name them, there is no convention; but just one convention which is to append “Controller” to the name of the class. Controller is a class in real which inherits from System.Web.Mvc.Controller class, making it able to inherit all of the functions and members of the Controller (parent) class. Let us create a Controller. Inside your Source code, there is a folder named as Controllers. If you open it, there would be 3 controllers by default, you can create your own.

Default controllers in ASP.NET MVC application

Right click on Controller folder and inside the Add option, click Controller to create a new one.

Creating a new Controller

Give a custom name to your controller; remember to have that Controller at the end of your class’ name. You must create an empty controller, because for the sake of this post we’re not going to use anything built-in but just scratch classes.

We just need an Empty controller, to create our own functions (actions in MVC) for it to handle and perform.

At this stage, your basic Controller has been created and you’re now ready to customize it. Before we move on to create actions that this Controller would perform. I wanted to remind you about (automatic) changes that were made in your Views folder. What Views folder is about, is – yes, well – self-explainatory but we’ll be talking about it too in the coming section. Right now, there is a new Client (or what ever Controller you create’s named) folder inside your View folder. Now it does start to make some sense, doesn’t it? Every controller has its own particular view to display data for, and each time the request would initiate from client (not our class, but the user of course) the Controller would be used (you can think like an instance of that class would be created) and then the Action of Controller would be checked against (inside the URL; which would then trigger the function of the Controller, which we’re going to create next). Like every class, we can create different functions for our Controllers, which are known as Actions. Let us think what we’re supposed to let our manager do with the client, he wants to

Create a new client – Create
Modify current client’s data – Update or Edit
Delete clients when he has finished the work – Delete

We can use above logic to create our functions for the Client Controller. Open your ClientController.cs file and edit it, to write this code in it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Web.Mvc;

using ASP.NET_MVC_Application.Models;
using Newtonsoft.Json;

namespace ASP.NET_MVC_Application.Controllers
{
   public class ClientController : Controller
   {
      // GET: Client
      public ActionResult Index()
      {
         // Load the data for the client
         var clients = Client.GetClients();

         // Return the view.
         return View(clients);
      }

      public ActionResult Create()
      {
         ViewBag.Submitted = false;
         var created = false;
         // Create the Client
         if (HttpContext.Request.RequestType == "POST")
         {
            ViewBag.Submitted = true;
            // If the request is POST, get the values from the form
            var id = Request.Form["id"];
            var name = Request.Form["name"];
            var address = Request.Form["address"];
            var trusted = false;
 
            if(Request.Form["trusted"] == "on") {
               trusted = true;
            }

            // Create a new Client for these details.
            Client client = new Client()
            {
               ID = Convert.ToInt16(id), 
               Name = name,
               Address = address,
               Trusted = Convert.ToBoolean(trusted)
            };

            // Save the client in the ClientList
            var ClientFile = Client.ClientFile;
            var ClientData = System.IO.File.ReadAllText(ClientFile);
            List<Client> ClientList = new List<Client>();
            ClientList = JsonConvert.DeserializeObject<List<Client>>(ClientData);

            if (ClientList == null)
            {
               ClientList = new List<Client>();
            }
            ClientList.Add(client);

            // Now save the list on the disk
            System.IO.File.WriteAllText(ClientFile, JsonConvert.SerializeObject(ClientList));

            // Denote that the client was created
            created = true;
         }

         if (created)
         {
            ViewBag.Message = "Client was created successfully.";
         }
         else
         {
            ViewBag.Message = "There was an error while creating the client.";
         }
       return View();
     }

     public ActionResult Update(int id)
     {
        if (HttpContext.Request.RequestType == "POST")
        {
           // Request is Post type; must be a submit
           var name = Request.Form["name"];
           var address = Request.Form["address"];
           var trusted = Request.Form["trusted"];

           // Get all of the clients
           var clints = Client.GetClients();

           foreach (Client client in clints)
           {
              // Find the client
              if (client.ID == id)
              {
                 // Client found, now update his properties and save it.
                 client.Name = name;
                 client.Address = address;
                 client.Trusted = Convert.ToBoolean(trusted);
                 // Break through the loop
                 break;
              }
           }

           // Update the clients in the disk
           System.IO.File.WriteAllText(Client.ClientFile, JsonConvert.SerializeObject(clints));

           // Add the details to the View
           Response.Redirect("~/Client/Index?Message=Client_Updated");
       }


       // Create a model object.
       var clnt = new Client();
       // Get the list of clients
       var clients = Client.GetClients();
       // Search within the clients
       foreach (Client client in clients)
       {
          // If the client's ID matches
          if (client.ID == id)
          {
              clnt = client;
          }
          // No need to further run the loop
          break;
       }
       if (clnt == null)
       {
           // No client was found
           ViewBag.Message = "No client was found.";
       }
     return View(clnt);
   }

   public ActionResult Delete(int id)
   {
      // Get the clients
      var Clients = Client.GetClients();
      var deleted = false;
      // Delete the specific one.
      foreach (Client client in Clients)
      {
        // Found the client
        if (client.ID == id)
        {
          // delete this client
          var index = Clients.IndexOf(client);
          Clients.RemoveAt(index);

          // Removed now save the data back.
          System.IO.File.WriteAllText(Client.ClientFile, JsonConvert.SerializeObject(Clients));
          deleted = true;
          break;
       }
    }

     // Add the process details to the ViewBag
     if (deleted)
     {
        ViewBag.Message = "Client was deleted successfully.";
     }
     else
     {
        ViewBag.Message = "There was an error while deleting the client.";
     }
     return View();
    }
  }
}

Done! That was our controller, if you have a look at the code above you will find a few functions, a few ViewBags and a few Views etc. I will explain them in the end, because they’re built-in functions of ASP.NET MVC for the application and which we are bound to use them for application to run. Each time you’re going to create a request to your application to load data about clients, this controller would take action, and will provide with the data that user (in case here, you) want from the application. In the above code Create, Update, Delete and Index are the functions of the class, in MVC application they will act as Actions of the application’s Controller. You will use them to tell the application what to do, when user triggers such a particular action in your application. They’re just average functions in a simple class; which inherits from System.Web.Mvc.Controller.

Creating a custom View

Now that back-end code (Controller code) has been done, and you can now create the Views for your application. Views are used to show the content (data) of your application to the client (user) in HTML markup. Because browser can only render HTML content, we will be using that markup, and Views are used for that. Each controller has its own particular view. One thing to note here, is that to return a View, the name of the View must match the Action’s name. You can put it, like each of the Action (function) in the Controller is meant for a particular View (HTML document to be returned) in the application. We, need four Views, for our four Actions; Index, Create, Update, Delete. To create a View you can click on the Views folder, and create a new folder; but remember, here you don’t need to create any new folder, because as I already said when you created the Controller a new folder was already created with the name of the Controller “Client”. Right-click that folder, and inside the Add click on the MVC 5 View with Layout and click Next to go to the next step.

Creating a new View (with Layout)

Now name the View, I named it Index because we need a view for Index page.

Now select the layout, layout is applied across the pages (Views) to design them a like. You can use your own views too, but for this I am going to use the default layout.
This is the step-by-step method to create the View in ASP.NET MVC using Visual Studio. ASP.NET uses Razor syntax to create HTML pages, which shortens the codes for the developers. Update the code inside this page and write this following code,

@model IEnumerable<ASPNET_MVC_Application.Models.Client>

@{
   ViewBag.Title = "All Clients";
   Layout = "~/Views/Shared/_Layout.cshtml";
}

<h3>Following table shows the Clients detail</h3>
<p>You will find their details, as well as other links to other actions that can be performed and operated over the client objects. </p>

<p>You can also create a new client <a href="~/Client/Create">here</a>. </p>

<table class="clients-table">
   <tr>
   <th>ID</th>
   <th>Name</th>
   <th>Address</th>
   <th>Trusted</th>
   <th>Actions</th>
</tr>

 @if(Model != null && Model.Count() > 0)
 {
   foreach (var client in Model)
   {
     // Create the list of these clients
     <tr>
       <td>@client.ID</td      <td>@client.Name</td      <td>@client.Address</td>
       <td>@client.Trusted</td>
       <td>
         <a href="~/Client/Update/@client.ID">Update</a<a href="~/Client/Delete/@client.ID">Delete</atd>
     </tr> 
   }
 }
</table>

In the above page’s source code, you will find one new thing. @model IEnumerable<ASPNET_MVC_Application.Models.Client> that is the type of the data that has to be used inside this View; Yes, it is the model being used here. After that it is just simple razor syntax, to create the HTML document (View) and to return to the client (user). The Model object (the one used in the foreach loop) is having the value of what ever is passed for the @model thing.
You should create next three Views yourself (as a test) and write this content to them.
For Create View:

@{
   ViewBag.Title = "Create";
   Layout = "~/Views/Shared/_Layout.cshtml";
}

<div>
   <h3>Fill in the following form</h3>
</div>
<form method="post" style="margin: 5% 0 0">
   <div class="float-left">
     <p>Client ID</p>
     <p>Name</p>
     <p>Address</p>
     <p>Trusted</p>
   </div>
   <div class="float-right">
     <input type="text" style="margin: 1px 0 2px" name="id" /><br />
     <input type="text" style="margin: 1px 0 2px" name="name" /><br />
     <input type="text" style="margin: 1px 0 2px" name="address" /><br />
     <input type="checkbox" style="margin: 1px 0 2px" name="trusted" /><br />
     <input type="submit" value="Save" />
   </div>
</form>

<p>
   @if (ViewBag.Submitted)
   {
     // If the form was submitted
     @ViewBag.Message
   }
</p>

For Update View:

@model ASP.NET_MVC_Application.Models.Client

@{
   ViewBag.Title = "Update the Client";
   Layout = "~/Views/Shared/_Layout.cshtml";
}


<div>
  <h3>Update the following form</h3>
</div>
<form method="post" style="margin: 5% 0 0">
  <div class="float-left">
     <p>Name</p>
     <p>Address</p>
     <p>Trusted</p>
  </div>
  <div class="float-right">
     <input type="text" style="margin: 1px 0 2px" name="name" value="@Model.Name" /><br />
     <input type="text" style="margin: 1px 0 2px" name="address" value="@Model.Address" /><br />
     <input type="checkbox" style="margin: 1px 0 2px" name="trusted" 
          @if (Model.Trusted) { <text>checked="checked"</text> } 
     />
 <br />
     <input type="submit" value="Save" />
  </div>
</form>

<p>
   @if (ViewBag.Submitted != null && ViewBag.Submitted)
   {
      // If the form was submitted
     @ViewBag.Message
   }
</p>

For Delete View:

@{
   Layout = "~/Views/Shared/_Layout.cshtml";
}

<p>@ViewBag.Message</p>

You will find, that in Create view I am not passing any model. That is because, for a View, to have a model is not obligatory. Model is just to allow dynamic content to it, if you don’t want to use any model, fair enough, you don’t have to use a model in every view to create it an MVC application. These are just simple HTML pages, that are being made update-to-the-content by the razor syntax and then sent back to the browser in an HTML markup, for rendering purposes. _ViewStart.cshtml page is used as the first page to check for any layouts. If you don’t want to set any layout for all of the pages themself and want a unique look across the web app, you can set that in this page and it will be applied to all of the pages.

Creating the custom Model

As far as Model is concerned, ASP.NET MVC allows you to use any kind of data (coming from a data source; server or what so ever) to be used as a Model. You can pass the data from the model to the view from the Controller, which will be then rendered as a simple HTML markup and will be displayed inside the browser to the user.
I have already mentioned, and in-case you missed it above, if you want to use SQL Server database as your Model for application, then ASP.NET team has provided a very good tutorial for that and you might want to join them on their journey here. They used Entity Framework for their Actions. In our project, we will be using JSON files and custom created Actions to perform CRUD operations. This would (try to) remove ambiguity in our minds about this MVC pattern and its extensiveness for other technologies and frameworks.
A Model, like a Controller, is just a class file, that we can instanitiate and get different objects from, when working with a request from a client. A model is a representation of the real-world objects in our own context; which here is the ASP.NET MVC Application. Think of it like there is a Client model. He would have a ClientID, Name, Address and a Trusted as a flag (you should recall our scenario if you have no idea of where these came from). What would you write as a class for this Client? Look at the following class code and have understanding of the Model we’re going to have.

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Newtonsoft.Json;

namespace ASP.NET_MVC_Application.Models
{
   public class Client
   {
     public static string ClientFile = HttpContext.Current.Server.MapPath("~/App_Data/Clients.json");

     public int ID { get; set; }
     public string Name { get; set; }
     public string Address { get; set; }
     public bool Trusted { get; set; }

     public static List<Client> GetClients()
     {
        List<Client> clients = new List<Client>();
        if (File.Exists(ClientFile))
        {
           // File exists..
           string content = File.ReadAllText(ClientFile);
           // Deserialize the objects 
           clients = JsonConvert.DeserializeObject<List<Client>>(content);

           // Returns the clients, either empty list or containing the Client(s).
           return clients;
        }
        else
        {
           // Create the file 
           File.Create(ClientFile).Close();
           // Write data to it; [] means an array, 
           // List<Client> would throw error if [] is not wrapping text
           File.WriteAllText(ClientFile, "[]");

           // Re run the function
           GetClients();
        }

        return clients;
      }
   }
}

A simple C# class file, that would serve our application as a Model. You will find one other major change, which is the namespace this class is location in. The name is having an extra .Models attached to our project’s name. That makes up our different fields. ASP.NET and Visual Studio do these things for us, although they’re not required but just conventions to be followed. Let us create a Model for our application, click on the Models folder in your app, and inside Add click on Class file and name it Client.cs and click Next to continue.

Select a new class file to be created as a Model

Name it what-ever-you-want, but I would be using Client.

These were the three major fields in the MVC that are used to create an application. However, there are a few other minor fields that work and run our application, that I will be covering here for you to atleast know how to kick-start your project.
As far, we have covered what is Controller (controls the HTTP requests coming our going to client), what is a View (it is rendered as HTML output in the browser, the response that is returned to the client in HTML markup can be said to be the View) and finally what is a Model (the representation of the real-world data in our application is called a model). Now, we can go and have a look at the classes and objects which run our application. Like all other apps, that run over .NET framework, ASP.NET also exposes some functions for its developers to use, to perform different actions when the app starts, when the app crashes etc. If you open the folder App_Start in your application, you will find a few classes there. BundleConfig is used to configure your JavaScript libraries, ASP.NET also enables the developers to perform some client-side actions. IdentityConfig configures the Identity service (user authortization servicer), but the main file that I wanted to talk about here, which needs our attention too, is the RouteConfig.cs file.
In ASP.NET, you do not need to have a physical path fixed in order for the client to send a request to its virtual location to read it. Instead, you can now create custom URLs to let your users visit your application, without you having to have a specified file at that location. You create different routing techniques, to allow different data values inside the URL to create dynamic web pages for the clients.
If you open up this file, you will find the following code in it, (I am going to use a partial part of the file)

routes.MapRoute(
   name: "Default",
   url: "{controller}/{action}/{id}",
   defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);

This defines a route, for your application. It has been named “Default” and has the default configurations for the URL, any URL that comes will be matches against this scheme. First value would match for a Controller second to an action (the function inside the Controller class) and then an optional field (see the default values field). You can change this sequence of arragement, to make sure that the URLs are website and user-friendly for your context.
Every ASP.NET can have as many routes enabled in it, every one will be used but the best possible route would be used, but at least one route must be present for the app to run. Let us now run the app and test, for anything that might need our attention.

Running the application (Optional part; if you want to download and test the app, skip this section)

This application was run over the app named “ASP.NET MVC Application” but I created a new app named “ASPNET_MVC_Application”, there is a huge different between these two, usually only difference is the namespace naming one so if you were following the blog post you might find this a bit of confusion; so my apologies for that.

In the beginning, the table is empty.

Now let us, start adding a few objects; at this instance, let us just take one object under consideration.

Fill in this form and click Save, the application would save this client’s information.

Message showing the success of our operation.

If you go back to the main page of the Client’s directory (controller), you will find a record available there.

Let us, now try to update this client’s value, in the Actions attribute of table, you will find two hyperlinks, they actually are just Actions (functions) of our controller, we will pass (optional) ID of our client to it, and then we will update the value. We will update our value as,

Let’s go back and see if our View now reflects any change.

Voila! It does reflect our change, which proves that a Model updates the View for any new change that we make. Try using the second hyperlink, it would delete the object from the collection.

That was up, for the working process. Now our application runs fine enough, our manager can add clients, remove clients or modify their data as per his requirements. As far as now, this application is running and our clients can visit and use it as per their requirement.
This was a basic overview of ASP.NET MVC application, that contains the Model for our data, View for the users to visit and see and a Controller that controls the HTTP data coming or going to the client.

Points of Interest

ASP.NET MVC pattern contains a Controller for controlling all of the HTTP requests coming, and responses going to the clients. A view for generating HTML web pages, Razor view engine is used for creating the HTML markup from C# objects. A Model is a simple C# class that contains the object’s properties. Each time the application is run, Controller gets the data from the Model and populates the View for the user to read the result in his/her browser.
Every real-world object can be used in ASP.NET MVC application as the data source. To create a model, C# files are used (if you’re using C#, otherwise you would be creating a file related to your language). The properties and members act as properties that you would be using inside your HTML markup for rendering purposes.
You can control what kind of URLs are being used in your application by creating routing techniques. These define the structure of your application’s URLs. You must define at least one route in your application for it to run, otherwise you can define as many as you want.
MVC pattern removes the ambiguity between the data and the code-behind of your application. Your code-behind is now a part of the Controller of your application and is written inside the Controllers. The data is stored inside the Model (actually which is just the representation of the data, actual data is stored in somewhere like a file or database) and the what user sees is written in the View. This way, any error can be fixed as soon as possible without having to worry about any other bug, because every field is now controlled and written in a different part.
Model part of this pattern must always not perform any kind of business-logic, because that would entirely kill the actual purpose of this MVC pattern. That is why, all of the logic code is written in the controller; even the creation of data or the manipulation of data should be done in controller through functions, since user has no connection with Model, it provides an extra layer of safety as the user is totally oblivious to the schema of the database.
ViewBag and the View are special objects in ASP.NET used to manipulate the View (HTML markup), you can pass any member (which is dynamic) to this ViewBag from the Controller, to be used inside the View page (.cshtml in these cases) for rendering; like I did for showing some messages like an error or a success message

from Copy here

(nested gridview):How to Get parent gridview rowindex on a child gridview link button click

2015-04-30T00:09:00.002-07:00

GridViewRow Gv2Row = (GridViewRow)((LinkButton)sender).NamingContainer; GridView Childgrid = (GridView)(Gv2Row.Parent.Parent); GridViewRow Gv1Row = (GridViewRow)(Childgrid.NamingContainer); int b = Gv1Row.RowIndex;

how to get list of tables, functions and stored procedure created last month in sql server

2015-04-08T22:20:00.003-07:00

SELECT
[name]
,create_date
,modify_date
FROM
sys.tables where create_date >='03/01/2015'

SELECT * FROM sys.procedures WHERE create_date >='03/01/2015'

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF') -- scalar, inline table-valued, table-valued

how to get files from google drive

2015-02-05T22:10:00.002-08:00

<head runat="server">
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Google drive attachment Example</title>
<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>
<script type="text/javascript">
function onApiLoad() {
gapi.load('auth', { 'callback': onAuthApiLoad });
gapi.load('picker');
}
function onAuthApiLoad() {
window.gapi.auth.authorize({
'client_id': '376407259412-3m7pu8htrdvf2u2tt9flm9o8pftioqj6.apps.googleusercontent.com',
'scope': ['https://www.googleapis.com/auth/drive']
}, handleAuthResult);
}
var oauthToken;
function handleAuthResult(authResult) {
if (authResult && !authResult.error) {
oauthToken = authResult.access_token;
createPicker();
}
}
function createPicker() {
var picker = new google.picker.PickerBuilder()
//.addView(new google.picker.DocsUploadView())
.addView(new google.picker.DocsView())
.setOAuthToken(oauthToken)
//.setDeveloperKey('AIzaSyDPs9U-dgOC9h1jRFNwOwhRtARCph8_3HM')
.setCallback(pickerCallback)
.build();
picker.setVisible(true);
}

function pickerCallback(data) {
var url = 'nothing';
if (data[google.picker.Response.ACTION] == google.picker.Action.PICKED) {
var doc = data[google.picker.Response.DOCUMENTS][0];
url = doc[google.picker.Document.URL];
}
var message = 'You picked: ' + url;
document.getElementById('result').innerHTML = message;
}
</script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>

<form id="form1" runat="server">
<asp:Button ID="btnupload" runat="server" Text="Google Drive" OnClick="btnupload_Click" />
<div id="result">
</div>
<%--<script type="text/javascript" src="https://apis.google.com/js/api.js?onload=onApiLoad"></script>--%>
</form>

in the code behind on button click

protected void btnupload_Click(object sender, EventArgs e)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "https://apis.google.com/js/api.js?onload", "onApiLoad();", true);
}

Error 80043004 solved in windows phone

2014-11-17T02:09:00.001-08:00

Hello Guys,
from last month i was suffering with this error. but today i have solved this error.
month ago i changed my hotmail id password. so i was getting this error.
for solve this go to in your xbolx live. and here login again with your new password and the problem will solve.

Using comma separated value parameter strings in SQL IN clauses

2014-09-22T04:51:00.000-07:00

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id int not null)
AS
BEGIN
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END
GO

This creates a user function that takes a comma separated value string and converts it into a table that SQL does understand - just pass it the sting, and it works it all out. It's pretty obvious how it works, the only complexity is the REPLACE part which ensures the string is terminated with a single comma by appending one, and removing all double commas from the string. Without this, while loop becomes harder to process, as the final number might or might not have a terminating comma and that would have to be dealt with separately.

DECLARE @LIST VARCHAR(200)

SET @LIST = '1,3'

SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

some comman shortcuts

2014-09-08T00:35:00.001-07:00

F1	Display Help
Ctrl+C	Copy the selected item
Ctrl+X	Cut the selected item
Ctrl+V	Paste the selected item
Ctrl+Z	Undo an action
Ctrl+Y	Redo an action
Delete	Delete the selected item and move it to the Recycle Bin
Shift+Delete	Delete the selected item without moving it to the Recycle Bin first
F2	Rename the selected item
Ctrl+Right Arrow	Move the cursor to the beginning of the next word
Ctrl+Left Arrow	Move the cursor to the beginning of the previous word
Ctrl+Down Arrow	Move the cursor to the beginning of the next paragraph
Ctrl+Up Arrow	Move the cursor to the beginning of the previous paragraph
Ctrl+Shift with an arrow key	Select a block of text
Shift with any arrow key	Select more than one item in a window or on the desktop, or select text within a document
Ctrl with any arrow key+Spacebar	Select multiple individual items in a window or on the desktop
Ctrl+A	Select all items in a document or window
F3	Search for a file or folder
Alt+Enter	Display properties for the selected item
Alt+F4	Close the active item, or exit the active program
Alt+Spacebar	Open the shortcut menu for the active window
Ctrl+F4	Close the active document (in programs that allow you to have multiple documents open simultaneously)
Alt+Tab	Switch between open items
Ctrl+Alt+Tab	Use the arrow keys to switch between open items
Ctrl+Mouse scroll wheel	Change the size of icons on the desktop
Windows logo key +Tab	Cycle through programs on the taskbar by using Aero Flip 3-D
Ctrl+Windows logo key+Tab	Use the arrow keys to cycle through programs on the taskbar by using Aero Flip 3-D
Alt+Esc	Cycle through items in the order in which they were opened
F6	Cycle through screen elements in a window or on the desktop
F4	Display the address bar list in Windows Explorer
Shift+F10	Display the shortcut menu for the selected item
Ctrl+Esc	Open the Start menu
Alt+underlined letter	Display the corresponding menu
Alt+underlined letter	Perform the menu command (or other underlined command)
F10	Activate the menu bar in the active program
Right Arrow	Open the next menu to the right, or open a submenu
Left Arrow	Open the next menu to the left, or close a submenu
F5	Refresh the active window
Alt+Up Arrow	View the folder one level up in Windows Explorer
Esc	Cancel the current task
Ctrl+Shift+Esc	Open Task Manager
Shift when you insert a CD	Prevent the CD from automatically playing

nested girdview in datalist cancel update delete c#

2014-08-28T23:47:00.003-07:00

<asp:DataList ID="datalist1" Width="100%" DataKeyField="level2taskno" runat="server"
OnItemDataBound="datalist_ItemDataBound">
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemTemplate>
<table style="width: 100%; color: Black; font-family: Arial; font-size: 12px;" cellspacing="0">
<tr>
<td>
<table style="width: 100%; color: Black; font-family: Arial; font-size: 12px; border: 1px solid #9a9a9a;"
cellpadding="5">
<tr>
<td bgcolor="#8ce8a0" valign="top" width="7%" style="color: #fff; font-size: 12px;
font-weight: bold;">
<b>Initiative(s):</b>
</td>
<td style="font-family: Arial; font-size: 18px;">
<b>
<asp:Label ID="lbltask" runat="server" Text='<%# Eval("TaskName") %>'></asp:Label></b>
</td>
</tr>
<tr>
<td bgcolor="#9bc1d4" valign="top" style="color: #fff; font-size: 12px; font-weight: bold;
padding-top: 10px;">
<b>Outcome(s):</b>
</td>
<td>
<asp:Literal runat="server" ID="outcome" Text='<%# string.Join("<p style=margin-left:0px; >", Eval("outcome").ToString().Split(new []{(char)13},StringSplitOptions.None)) %>'>
</asp:Literal>
</td>
</tr>
<tr>
<td bgcolor="#e8b880" valign="top" style="color: #fff; font-size: 12px; font-weight: bold;">
<b>Indicators:</b>
</td>
<td>
<asp:Literal runat="server" ID="Literal1" Text='<%# string.Join("<p style=margin-left:0px;> ", Eval("indicators").ToString().Split(new []{(char)13},StringSplitOptions.None)) %>'>
</asp:Literal>
</td>
</tr>
<tr>
<td bgcolor="#a6988f" width="7%" valign="top" style="color: #fff; font-size: 12px;
font-weight: bold;">
<b>Activities: </b>
</td>
<td width="100%">
<div style="width: 100%;">
<asp:GridView ID="GridViewlist" DataKeyNames="level2tasklink,level3taskno" runat="server"
ShowHeader="true" Width="100%" BorderColor="#e2e2e2" AutoGenerateColumns="false"
OnRowCancelingEdit="GridView2_RowCancelingEdit" OnRowDeleting="GridView2_RowDeleting"
OnRowEditing="GridView2_RowEditing" OnRowUpdating="GridView2_RowUpdating" OnRowDataBound="grdQuater1_RowDataBound">
<RowStyle VerticalAlign="Middle" HorizontalAlign="Center" />
<Columns>
<asp:TemplateField HeaderText="No.">
<ItemTemplate>
<asp:HiddenField ID="hidCusState" runat="server" Value='<%# Bind("level3taskno") %>' />
<asp:Label ID="lblautoid" runat="server" Text='<%# Container.DataItemIndex+1 %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Activity">
<ItemTemplate>
<asp:Label ID="lblactivity" runat="server" Text='<%# Eval("Activitydescnational") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="30%" VerticalAlign="Middle" HorizontalAlign="Left" />
<EditItemTemplate>
<asp:TextBox Style="width: 100%;" ID="txtactivity" runat="server" Text='<%# Bind("Activitydescnational") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Outputs">
<ItemTemplate>
<asp:Label ID="lbloutput" runat="server" Text='<%# Eval("output") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="30%" />
<EditItemTemplate>
<asp:TextBox Style="width: 100%;" ID="txtoutput" runat="server" Text='<%# Bind("output") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Global / National">
<ItemTemplate>
<asp:Label ID="lblglobal" runat="server" Text='<%# Eval("global1") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="10%" />
<EditItemTemplate>
<asp:TextBox ID="txtglobal" Style='text-transform: uppercase;' runat="server" Text='<%# Bind("global1") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Activity Type">
<ItemTemplate>
<asp:Label ID="lblactivitytype" runat="server" Text='<%# Eval("mov1") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="10%" />
<EditItemTemplate>
<asp:DropDownList ID="drpmov" AutoPostBack="true" runat="server">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Deadline">
<ItemTemplate>
<asp:Label ID="lblmonth" runat="server" Text='<%# Eval("month_due1") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="10%" />
<EditItemTemplate>
<asp:DropDownList ID="drpdeadline" AutoPostBack="true" runat="server">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Status">
<ItemTemplate>
<asp:Image ID="done" runat="server" Width="25" Height="25" ImageUrl='<%# "~/Images/" + (((bool)Eval("status") == false) ? "NotDone.jpg" : "Done.jpg") %>'>
</asp:Image>
<%-- <asp:Image ID="notdone" runat="server" Width="25" Height="25" Visible="false" ImageUrl="~/images/NotDone.jpg">
</asp:Image>--%>
</ItemTemplate>
<ItemStyle Width="5%" />
</asp:TemplateField>
<asp:TemplateField HeaderText="User">
<ItemTemplate>
<asp:Label ID="lbluser" runat="server" Text='<%# Eval("level3name") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="10%" />
</asp:TemplateField>
<%-- <asp:TemplateField HeaderText="File Reports">
<ItemTemplate>
<asp:LinkButton CommandName='<%# Eval("mov1") %>' ID="lnkreport" ForeColor="#2aa9df"
CommandArgument='<%# Eval("level3taskno") %>' OnClick="LinkButton1_Click" runat="server">File Report</asp:LinkButton>
</ItemTemplate>
<ItemStyle Width="8%" />
</asp:TemplateField>--%>
<asp:CommandField HeaderText="Edit" ShowEditButton="True"></asp:CommandField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True">
</asp:CommandField>
</Columns>
<FooterStyle BackColor="#6e6e6e" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="White" VerticalAlign="Middle" HorizontalAlign="Center" />
<SelectedRowStyle HorizontalAlign="Center" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#6e6e6e" Font-Bold="True" ForeColor="White" />
<EditRowStyle />
<AlternatingRowStyle HorizontalAlign="Center" BackColor="White" />
</asp:GridView>
</div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>

first bind the datalist according to your records

void datalist()
{
sql = "select * from tbl_Task_Objective where level2TaskNo in(select distinct level2TaskLink from tbl_Individual_Task where level3name='" + Request.QueryString["id"].ToString() + "'";
if (drpquarter.SelectedItem.Text != "All")
{
sql = sql + " and quarter='" + drpquarter.SelectedItem.Value + "' ";

}

if (drpinitiative.SelectedItem.Text != "All")
{
sql = sql + " and level2tasklink='" + drpinitiative.SelectedItem.Value + "'";
}

sql = sql + ")";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);

datalist1.DataSource = ds.Tables[0];
datalist1.DataBind();

}

2. then itemdataboud of datalist

protected void datalist_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
string id = (datalist1.DataKeys[e.Item.ItemIndex]).ToString();

GridView gv = (GridView)e.Item.FindControl("GridViewlist");

Label lb1 = (Label)e.Item.FindControl("lbltask");
Label lblmonth = (Label)e.Item.FindControl("lblmonth");

bindgrid(gv, id);
}

3. using bindgrid function

private void bindgrid(GridView GridView, string id)
{

sql = "select *, dbo.GetMonthName(month_due) as month_due1 ,dbo.GetDoneStaus(level3TaskNo) Status1,dbo.GetGlobal(global)as global1,dbo.getmov1(mov)as mov1 from tbl_Individual_Task where level2tasklink='" + id + "' and level3name='" + Request.QueryString["id"].ToString() + "' ";
if (drpquarter.SelectedItem.Text != "All")
{
sql = sql + " and quarter='" + drpquarter.SelectedItem.Value + "' ";
}

if (drpinitiative.SelectedItem.Text != "All")
{
sql = sql + " and level2tasklink='" + drpinitiative.SelectedItem.Value + "'";
}

if (drpglobal.SelectedItem.Text != "All")
{
sql = sql + " and global='" + drpglobal.SelectedItem.Value + "'";
}

ds1 = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);

if (ds1.Tables[0].Rows.Count > 0)
{

GridView.DataSource = ds1.Tables[0];
GridView.DataBind();

}

}

4.gridview rowdatabound event accroding to your requirements

protected void grdQuater1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string item = e.Row.Cells[1].Text;

if (e.Row.Cells[9].HasControls())
{
LinkButton lnkbtnDelete = ((LinkButton)e.Row.Cells[9].Controls[0]);
lnkbtnDelete.Attributes.Add("onclick", "return confirm('Do you want to Delete" + item + "?');");
}
}
if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState & DataControlRowState.Edit) > 0)
{
//string id = (datalist1.DataKeys[e.Row.RowIndex]).ToString();
// object objTemp = GridViewlist.DataKeys[e.Row.RowIndex].Value as object;
GridView gv = (GridView)e.Row.FindControl("GridView2");
Label lb = (Label)e.Row.FindControl("lblno");
Label lb1 = (Label)e.Row.FindControl("lbltask");
Image img = (Image)e.Row.FindControl("done");
// Image img1 = (Image)e.Row.FindControl("notdone");
Label lblmonth = (Label)e.Row.FindControl("lblmonth");

Label lbluser = (Label)e.Row.FindControl("lbluser");
HiddenField hidCusState = (HiddenField)e.Row.FindControl("hidCusState");
string level3taskno = hidCusState.Value;
//LinkButton lnk = (LinkButton)e.Row.FindControl("lnkreport");
//string idd = lnk.CommandArgument;

//sql = "select *, dbo.GetMonthName(month_due) as month_due1 ,dbo.GetDoneStaus(level3TaskNo) Status1,dbo.GetGlobal(global)as global1 from tbl_Individual_Task where quarter=1 and level2tasklink='" + id + "' and level3name='" + Session["Name"].ToString() + "'";
//ds1 = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);

sql = "select *, dbo.GetMonthName(month_due) as month_due1 ,dbo.GetDoneStaus(level3TaskNo) Status1,dbo.GetGlobal(global)as global1,dbo.getmov1(mov)as mov1 from tbl_Individual_Task where level3taskno='" + level3taskno + "' ";
if (drpquarter.SelectedItem.Text != "All")
{
sql = sql + " and quarter='" + drpquarter.SelectedItem.Value + "' ";
}

if (drpinitiative.SelectedItem.Text != "All")
{
sql = sql + " and level2tasklink='" + drpinitiative.SelectedItem.Value + "'";
}

if (drpglobal.SelectedItem.Text != "All")
{
sql = sql + " and global='" + drpglobal.SelectedItem.Value + "'";
}

ds1 = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
if (ds1.Tables[0].Rows.Count > 0)
{
if (ds1.Tables[0].Rows[0]["status1"].ToString() == "Done")
{
//img.Visible = true;
//img1.Visible = false;

}
else
{
//img.Visible = false;
//img1.Visible = true;
}
}

DropDownList drpdeadline = (DropDownList)e.Row.FindControl("drpdeadline");
sql = "select Monthno,MonthName from tbl_Month_Master order by Monthno ";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
drpdeadline.DataSource = ds.Tables[0];
drpdeadline.DataTextField = "MonthName";
drpdeadline.DataValueField = "Monthno";
drpdeadline.DataBind();
drpdeadline.SelectedItem.Text = ds1.Tables[0].Rows[0]["month_due1"].ToString();
// drpdeadline.Items.Insert(0, new ListItem("--Select--", "0"));

}
DropDownList drpmov = (DropDownList)e.Row.FindControl("drpmov");
sql = "select * from tbl_verification_master";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
drpmov.DataSource = ds.Tables[0];
drpmov.DataTextField = "description";
drpmov.DataValueField = "id";
drpmov.DataBind();
drpmov.SelectedItem.Text = ds1.Tables[0].Rows[0]["mov1"].ToString();
// drpmov.Items.Insert(0, new ListItem("--Select--", "0"));
}

}

}

5. then using row deleteing updating

protected void GridView2_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView GridView1 = (GridView)sender;
GridView1.EditIndex = e.NewEditIndex;
bindgrid(GridView1, GridView1.DataKeys[e.NewEditIndex][0].ToString());

}
protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridView GridView1 = (GridView)sender;
TextBox txtactivity = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtactivity");
TextBox txtoutput = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtoutput");
TextBox txtglobal = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtglobal");//.Cells[1].Controls[0];

DropDownList drpdeadline = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("drpdeadline");
DropDownList drpmov = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("drpmov");

sql = "select Monthno,MonthName from tbl_Month_Master where monthname='" + drpdeadline.SelectedItem.Text + "' ";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
drpdeadline.DataSource = ds.Tables[0];
drpdeadline.DataTextField = "MonthName";
drpdeadline.DataValueField = "Monthno";
drpdeadline.DataBind();
}
string drp = drpdeadline.SelectedItem.Text;
string drpdeadlinevalue = drpdeadline.SelectedValue;

sql = "select * from tbl_verification_master where description='" + drpmov.SelectedItem.Text + "'";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
drpmov.DataSource = ds.Tables[0];
drpmov.DataTextField = "description";
drpmov.DataValueField = "id";
drpmov.DataBind();

}
string drpmovvalue = drpmov.SelectedValue;
txtglobal.Text = txtglobal.Text.ToUpper();

if (txtglobal.Text == "GLOBAL")
{
txtglobal.Text = "2";
}

else if (txtglobal.Text == "INDIA")
{
txtglobal.Text = "1";
}
else
{
txtglobal.Text = "";
}
sql = "update tbl_Individual_Task set Activitydescnational='" + txtactivity.Text + "', output='" + txtoutput.Text + "', global='" + txtglobal.Text + "',month_due='" + drpdeadlinevalue + "', mov='" + drpmovvalue + "' where level3taskno='" + GridView1.DataKeys[e.RowIndex].Values[1].ToString() + "'";
SqlHelper.ExecuteNonQuery(Cnn, CommandType.Text, sql);

GridView1.EditIndex = -1;
bindgrid(GridView1, GridView1.DataKeys[e.RowIndex][0].ToString());
}
protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridView GridView1 = (GridView)sender;

sql = "delete tbl_Individual_Task where level3taskno='" + GridView1.DataKeys[e.RowIndex].Values[1].ToString() + "'";
// SqlHelper.ExecuteNonQuery(Cnn, CommandType.Text, sql);
bindgrid(GridView1, GridView1.DataKeys[e.RowIndex][0].ToString());
}
protected void GridView2_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView GridView1 = (GridView)sender;
GridView1.EditIndex = -1;
bindgrid(GridView1, GridView1.DataKeys[e.RowIndex][0].ToString());
}

Getting Client Ip address and computer name in c#

2014-08-27T02:32:00.000-07:00

using system .net;

/* host address */

string strHostName = System.Net.Dns.GetHostEntry(Request.UserHostAddress).HostName;

lblip.Text = Request.UserHostAddress.ToString();

when you upload your code on server then in website you'll get your exact value.

Insert new row in gridview when no data in gridview

2014-05-07T23:51:00.001-07:00

sql = "select * from tblbkdetails where formid='" + formid + "'";
ds = SqlHelper.ExecuteDataset(Cnn, CommandType.Text, sql);
//grdshow.DataSource = ds.Tables[0];
//grdshow.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
GridView2.DataSource = ds.Tables[0];
GridView2.DataBind();
}
else
{

ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
GridView2.DataSource = ds;
GridView2.DataBind();
int columncount = GridView2.Rows[0].Cells.Count;
GridView2.Rows[0].Cells.Clear();
GridView2.Rows[0].Cells.Add(new TableCell());
GridView2.Rows[0].Cells[0].ColumnSpan = columncount;
GridView2.Rows[0].Cells[0].Text = "There is no Records ";

}

How can i disable autocorrect function on Windows Phone 7.5

2013-11-07T22:38:00.001-08:00

Hello Guys,

lots of time when i am chatting with my friends, then m using some Hindi words but auto-complete function replace some words with English words. so i was very disappoint with the facilities .

so now m going to telling you how can you solved this problem.

just go into your setting>>Keyboard>>typing settings>>press to change>>uncheck Correct Misspelt words.

i hope it'll be a very userfull for you...

C# - Generics

2013-10-08T23:13:00.001-07:00

Generics allow you to delay the specification of the data type of programming elements in a class or a method, until it is actually used in the program. In other words, generics allow you to write a class or method that can work with any data type.

You write the specifications for the class or the method, with substitute parameters for data types. When the compiler encounters a constructor for the class or a function call for the method, it generates code to handle the specific data type. A simple example would help understanding the concept:

using System;
using System.Collections.Generic;

namespace GenericApplication
{
public class MyGenericArray<T>
{
private T[] array;
public MyGenericArray(int size)
{
array = new T[size + 1];
}
public T getItem(int index)
{
return array[index];
}
public void setItem(int index, T value)
{
array[index] = value;
}
}

class Tester
{
static void Main(string[] args)
{
//declaring an int array
MyGenericArray<int> intArray = new MyGenericArray<int>(5);
//setting values
for (int c = 0; c < 5; c++)
{
intArray.setItem(c, c*5);
}
//retrieving the values
for (int c = 0; c < 5; c++)
{
Console.Write(intArray.getItem(c) + " ");
}
Console.WriteLine();
//declaring a character array
MyGenericArray<char> charArray = new MyGenericArray<char>(5);
//setting values
for (int c = 0; c < 5; c++)
{
charArray.setItem(c, (char)(c+97));
}
//retrieving the values
for (int c = 0; c< 5; c++)
{
Console.Write(charArray.getItem(c) + " ");
}
Console.WriteLine();
Console.ReadKey();
}
}
}
When the above code is compiled and executed, it produces the following result:

0 5 10 15 20
a b c d e
Features of Generics
Using generics is a technique that enriches your programs in the following ways:

It helps you to maximize code reuse, type safety, and performance.

You can create generic collection classes. The .NET Framework class library contains several new generic collection classes in the System.Collections.Generic namespace. You may use these generic collection classes instead of the collection classes in the System.Collections namespace.

You can create your own generic interfaces, classes, methods, events and delegates.

You may create generic classes constrained to enable access to methods on particular data types.

You may get information on the types used in a generic data type at run-time by means of reflection.

Generic Methods
In the previous example, we have used a generic class; we can declare a generic method with a type parameter. The following program illustrates the concept:

using System;
using System.Collections.Generic;

namespace GenericMethodAppl
{
class Program
{
static void Swap<T>(ref T lhs, ref T rhs)
{
T temp;
temp = lhs;
lhs = rhs;
rhs = temp;
}
static void Main(string[] args)
{
int a, b;
char c, d;
a = 10;
b = 20;
c = 'I';
d = 'V';

//display values before swap:
Console.WriteLine("Int values before calling swap:");
Console.WriteLine("a = {0}, b = {1}", a, b);
Console.WriteLine("Char values before calling swap:");
Console.WriteLine("c = {0}, d = {1}", c, d);

//call swap
Swap<int>(ref a, ref b);
Swap<char>(ref c, ref d);

//display values after swap:
Console.WriteLine("Int values after calling swap:");
Console.WriteLine("a = {0}, b = {1}", a, b);
Console.WriteLine("Char values after calling swap:");
Console.WriteLine("c = {0}, d = {1}", c, d);
Console.ReadKey();
}
}
}
When the above code is compiled and executed, it produces the following result:

Int values before calling swap:
a = 10, b = 20
Char values before calling swap:
c = I, d = V
Int values after calling swap:
a = 20, b = 10
Char values after calling swap:
c = V, d = I
Generic Delegates
You can define a generic delegate with type parameters. For example:

delegate T NumberChanger<T>(T n);
The following example shows use of this delegate:

using System;
using System.Collections.Generic;

delegate T NumberChanger<T>(T n);
namespace GenericDelegateAppl
{
class TestDelegate
{
static int num = 10;
public static int AddNum(int p)
{
num += p;
return num;
}

public static int MultNum(int q)
{
num *= q;
return num;
}
public static int getNum()
{
return num;
}

static void Main(string[] args)
{
//create delegate instances
NumberChanger<int> nc1 = new NumberChanger<int>(AddNum);
NumberChanger<int> nc2 = new NumberChanger<int>(MultNum);
//calling the methods using the delegate objects
nc1(25);
Console.WriteLine("Value of Num: {0}", getNum());
nc2(5);
Console.WriteLine("Value of Num: {0}", getNum());
Console.ReadKey();
}
}
}
When the above code is compiled and executed, it produces the following result:

Value of Num: 35
Value of Num: 175

Design Tip #107 Using the SQL MERGE Statement for Slowly Changing Dimension Processing

2013-09-24T02:57:00.001-07:00

Most ETL tools provide some functionality for handling slowly changing dimensions. Every so often, when the tool isn’t performing as needed, the ETL developer will use the database to identify new and changed rows, and apply the appropriate inserts and updates. I’ve shown examples of this code in the Data Warehouse Lifecycle in Depth class using standard INSERT and UPDATE statements. A few months ago, my friend Stuart Ozer suggested the new MERGE command in SQL Server 2008 might be more efficient, both from a code and an execution perspective. His reference to a blog by Chad Boyd on MSSQLTips.com gave me some pointers on how it works. MERGE is a combination INSERT, UPDATE and DELETE that provides significant control over what happens in each clause.

This example handles a simple customer dimension with two attributes: first name and last name. We are going to treat first name as a Type 1 and last name as a Type 2. Remember, Type 1 is where we handle a change in a dimension attribute by overwriting the old value with the new value; Type 2 is where we track history by adding a new row that becomes effective when the new value appears.

Step 1: Overwrite the Type 1 Changes
I tried to get the entire example working in a single MERGE statement, but the function is deterministic and only allows one update statement, so I had to use a separate MERGE for the Type 1 updates. This could also be handled with an update statement since Type 1 is an update by definition.

MERGE INTO dbo.Customer_Master AS CM
USING Customer_Source AS CS
ON (CM.Source_Cust_ID = CS.Source_Cust_ID)
WHEN MATCHED AND — Update all existing rows for Type 1 changes
CM.First_Name <> CS.First_Name
THEN UPDATE SET CM.First_Name = CS.First_Name

This is a simple version of the MERGE syntax that says merge the Customer_Source table into the Customer_Master dimension by joining on the business key, and update all matched rows where First_Name in the master table does not equal the First_Name in the source table.

Step 2: Handle the Type 2 Changes
Now we’ll do a second MERGE statement to handle the Type 2 changes.

This is where things get a little tricky because there are several steps involved in tracking Type 2 changes. Our code will need to:

1. Insert brand new customer rows with the appropriate effective and end dates
2. Expire the old rows for those rows that have a Type 2 attribute change by setting the appropriate end date and current_row flag = ‘n’
3. Insert the changed Type 2 rows with the appropriate effective and end dates and current_row flag = ‘y’

The problem with this is it’s one too many steps for the MERGE syntax to handle. Fortunately, the MERGE can stream its output to a subsequent process. We’ll use this to do the final insert of the changed Type 2 rows by INSERTing into the Customer_Master table using a SELECT from the MERGE results. This sounds like a convoluted way around the problem, but it has the advantage of only needing to find the Type 2 changed rows once, and then using them multiple times.

The code starts with the outer INSERT and SELECT clause to handle the changed row inserts at the end of the MERGE statement. This has to come first because the MERGE is nested inside the INSERT. The code includes several references to getdate; the code presumes the change was effective yesterday (getdate()-1) which means the prior version would be expired the day before (getdate()-2). Finally, following the code, there are comments that refer to the line numbers

1 INSERT INTO Customer_Master
2 SELECT Source_Cust_ID, First_Name, Last_Name, Eff_Date, End_Date, Current_Flag
3 FROM
4 ( MERGE Customer_Master CM
5 USING Customer_Source CS
6 ON (CM.Source_Cust_ID = CS.Source_Cust_ID)
7 WHEN NOT MATCHED THEN
8 INSERT VALUES (CS.Source_Cust_ID, CS.First_Name, CS.Last_Name, convert(char(10), getdate()-1, 101), ’12/31/2199′, ‘y’)
9 WHEN MATCHED AND CM.Current_Flag = ‘y’
10 AND (CM.Last_Name <> CS.Last_Name ) THEN
11 UPDATE SET CM.Current_Flag = ‘n’, CM.End_date = convert(char(10), getdate()- 2, 101)
12 OUTPUT $Action Action_Out, CS.Source_Cust_ID, CS.First_Name, CS.Last_Name, convert(char(10), getdate()-1, 101) Eff_Date, ’12/31/2199′ End_Date, ‘y’Current_Flag
13 ) AS MERGE_OUT
14 WHERE MERGE_OUT.Action_Out = ‘UPDATE’;

Code Comments
Lines 1-3 set up a typical INSERT statement. What we will end up inserting are the new values of the Type 2 rows that have changed.

Line 4 is the beginning of the MERGE statement which ends at line 13. The MERGE statement has an OUTPUT clause that will stream the results of the MERGE out to the calling function. This syntax defines a common table expression, essentially a temporary table in the FROM clause, called MERGE_OUT.

Lines 4-6 instruct the MERGE to load Customer_Source data into the Customer_Master dimension table.

Line 7 says when there is no match on the business key, we must have a new customer, so Line 8 does the INSERT. You could parameterize the effective date instead of assuming yesterday’s date.

Lines 9 and 10 identify a subset of the rows with matching business keys, specifically, where it’s the current row in the Customer_Master AND any one of the Type 2 columns is different.

Line 11 expires the old current row in the Customer_Master by setting the end date and current row flag to ‘n’.

Line 12 is the OUTPUT clause which identifies what attributes will be output from the MERGE, if any.

This is what will feed into the outer INSERT statement. The $Action is a MERGE function that tells us what part of the MERGE each row came from. Note that the OUTPUT can draw from both the source and the master. In this case, we are outputting source attributes because they contain the new Type 2 values.

Line 14 limits the output row set to only the rows that were updated in Customer_Master. These correspond to the expired rows in Line 11, but we output the current values from Customer_Source in Line 12.

Summary
The big advantage of the MERGE statement is being able to handle multiple actions in a single pass of the data sets, rather than requiring multiple passes with separate inserts and updates. A well tuned optimizer could handle this extremely efficiently.

DNS Server is not responding on Windows 7

2013-09-20T00:28:00.000-07:00

When you try to connect to the Internet, the connection might fail when we run the Troubleshooter and you might get this error: The device or resource (DNS server) is not responding.

DNS Server is not responding

In such a scenario, there are few things you could try to resolve it.

1. First backup your Router settings and update the Firmware of your Router. Check the Router manual for more information

2. Next thing is to change the DNS server address manually.

Go to Start and Click on Control Panel

Open up Network and Internet and go to the Network and Sharing Center.

Click on Change adapter settings

Now you’ll see the list of Network adapters select the Network adapter that your using and right click on it and go to properties

Then Click on “Internet Protocol Version 4 (TCP/IPv4)”

You’ll see the Internet Protocol Properties

Select “Use the Following DNS server address:”

Enter the Preferred DNS address: 208.67.222.222

Enter the Alternative DNS address: 208.67.220.220

Then select on Validate settings upon exit.

Click OK, and then Close to finish.

I hope it'll be helpful for you ... give your feedback about your post...

How to find the table name in the database that have triggers

2013-09-17T05:10:00.001-07:00

SELECT DISTINCT o.[name] AS [Table]
FROM [sysobjects] o
JOIN [sysobjects] tr
ON o.[id] = tr.[parent_obj]
WHERE tr.[type] = 'tr'
ORDER BY [Table]

If you want to know all name of the triggers then run the query

select * from sys.triggers

hope it'll helpfull

An example of Arrays,ArrayList,generic collections in an ASP.Net application !!!

2013-04-06T02:27:00.002-07:00

In one of my ASP.Net seminars I have been asked to provide a small sample/example on working with a group of custom objects.
I have created a small example/site with ASP.Net & C# and thought it might be a good idea to share it with you.
More specifically I have been asked to create a small ASP.Net web site where I will demonstrate the use of arrays with objects,ArrayLists with objects and generic collections with objects.
I know some people will find this post's contents pretty basic but for those junior developers out there, I hope it is really helpful.
Let's demonstrate that using our usual methodology, a hands-on step by step example.
We will start with an array of objects first.
1) Launch Visual Studio 2010/2008/2005. Express editions work fine.
2) Create a new empty website and give it an appropriate name.Choose C# as the development language.
3) Add a new item in your site, a web form item. Leave the default name, so it is calledDefault.aspx.
4) Add a new item in your site, a class file.Name the class file Person.cs.
5) The whole code for the Person.cs class follows
public class Person
{

#region define public properties of my person class

public string FirstName { get; set; }

public string LastName { get; set; }

public string Email { get; set; }

public decimal Height { get; set; }

public decimal Weight { get; set; }

#endregion

#region calculatecurrentage

public int CalculateCurrentAge(DateTime dob)
{

int years = DateTime.Now.Year - dob.Year;

if (DateTime.Now.Month < dob.Month ||
(DateTime.Now.Month == dob.Month && DateTime.Now.Day < dob.Day))
years--;

return years;
}

#endregion

public string DisplayFullName()
{
string info;
info = "FullName is: " + FirstName + " " + LastName;
return info;

}

}

6) So as you see this is a fairly easy class with 5 public properties and 2 public methods. Place the Person.cs in the special folder App_Code.If this special folder is not created, you can create it yourself by right-clicking on your site's name and from the submenu click "Add ASP.Net Folder".
I am not using public fields. I want you to make a note of that. I am using C# 3.0 syntax using Auto-implemented properties. The compiler creates the appropriate private fields. In general it is a good practice to have your fields private and make the properties public. I also have created 2 easy to understand public methods.
7) So now we do have our custom object/type. Let's see how to group these objects in an array of objects. In the default.aspx inside the Page_Load event handling routine type,
Person[] people = new Person[3];

Person p1 = new Person();

p1.FirstName = "Nikos";
p1.LastName = "Kantzelis";
p1.Email = "nikolaosk@hotmail.com";
p1.Height = 1.78m;
p1.Weight = 88.5m;

Person p2 = new Person();

p2.FirstName = "James";
p2.LastName = "Rowling";
p2.Email = "jamesr@hotmail.com";
p2.Height = 1.98m;
p2.Weight = 98.25m;

Person p3 = new Person();

p3.FirstName = "George";
p3.LastName = "Graham";
p3.Email = "graham@yahoo.co.uk";
p3.Height = 1.88m;
p3.Weight = 81.5m;

people[0] = p1;
people[1] = p2;
people[2] = p3;

for (int i = 0; i < 3; i++)
{
string peopleinfo;
peopleinfo = people[i].DisplayFullName();
Response.Write(peopleinfo);
Response.Write("<br/>");
}
8) Run your application and see the fullname of our Person object printed in the screen. The code I have written in the Page_Load routine is not difficult to follow.
First I create a new array of Person objects and set the size to 3 elements.Then I create 3 instances of the Person class and fill in the public properties with random values.
Then I place those reference variables (p1-p3) inside our array (people).
Finally I loop inside the array and print the fullname of each object by calling theDisplayFullName() method of the Person class.
This works fine but there are some limitations. One of them is that you have to know beforehand how many items we must place in the array.
An alternative way is to use ArrayLists.
So let's move on to our more efficient way of storing group of custom objects. In this example we will use an ArrayList object.
9) Drag and drop a button in the default.aspx page. Leave the default name. Double click on the button to create the Button1_Click event handling routine.Comment out all of your code in the Page_Load event handling routine.
10) We will use of course the Person class. Inside the Button1_Click event handling routine type,
ArrayList people = new ArrayList();

Person p1 = new Person();

p1.FirstName = "Nikos";
p1.LastName = "Kantzelis";
p1.Email = "nikolaosk@hotmail.com";
p1.Height = 1.78m;
p1.Weight = 88.5m;

Person p2 = new Person();

p2.FirstName = "James";
p2.LastName = "Rowling";
p2.Email = "jamesr@hotmail.com";
p2.Height = 1.98m;
p2.Weight = 98.25m;

Person p3 = new Person();

p3.FirstName = "George";
p3.LastName = "Graham";
p3.Email = "graham@yahoo.co.uk";
p3.Height = 1.88m;
p3.Weight = 81.5m;

people.Add(p1);
people.Add(p2);
people.Add(p3);

foreach (object item in people)
{
Person myperson = (Person)item;

Response.Write(myperson.DisplayFullName());
Response.Write("<br/>");

}
11) Run your application and see the results exactly as before when we used a simple Array object.
Make sure you have added the using System.Collections; in the top of your file.
I am just creating a new instance of the ArrayList object
ArrayList people = new ArrayList();
Then I create 3 intances of the Person custom object and fill in the values as previously.Then I use the Add method to add those objects in my ArrayList object.
The Add method of the Arraylist class accepts a parameter of type Object.So when we add a Person object as we do in our example that object has to be casted to an instance of type Object.
This process is also known as boxing. The reverse process is known as unboxing. This pair of processes has impact on application’s performance.
Then I just loop through the items and print the relevant information.
Now add another object in the ArrayList. We can do that with ArrayLists but not with simple Arrays of objects.You type the following code just after the foreach block of code,
Person p4 = new Person();
p4.FirstName = "Mary";
p4.LastName = "Stewart";
p4.Email = "marystewart@yahoo.co.uk";
p4.Height = 1.71m;
p4.Weight = 61.5m;
people.Insert(2, p4);
12) Now add this code again after you have inserted a 4th object in the array list.
foreach (object item in people)
{
Person myperson = (Person)item;

Response.Write(myperson.DisplayFullName());
Response.Write("<br/>");

}
13) Run your application, click on the button, and see the new object added in the ArrayList in the correct position.So with ArrayLists we do have more power as developers when it comes to grouping objects in arrays.
Now let's see a limitation of ArrayLists. Add another class file to your site and name isAnimal.cs. The code inside the .cs file should be something like this
public class Animal
{

public bool isMammal { get; set; }

public string Species { get; set; }

public double Speed { get; set; }

public decimal Height { get; set; }

public decimal Weight { get; set; }

}
I am not bothered at this time to add any public methods. Switch back to yourdefault.aspx.cs file and in the end of the Button1_Click event handling routine type something like this
Animal myanimal = new Animal();
myanimal.isMammal = true;
myanimal.Species = "Panthera";
myanimal.Speed = 56.8;
myanimal.Height = 0.78m;
myanimal.Weight = 163;
Now add this line of code just below the instantiation of your animal object.
people.Add(myanimal);
Compile your application and see tha it compiles just fine.Now if you add this code just after the previous line

foreach (object item in people)
{
Person myperson = (Person)item;

Response.Write(myperson.DisplayFullName());
Response.Write("<br/>");

}
and build your application, your application will build just fine. Now run your application and you will get an exception (InvalidCastException).It cannot cast something of type Animalto Person.

14) Now let's improve our code with the use of generic collections. With .Net Framework 2.0 generic collections were introduced. A generic collection is basically a container that can be used to store objects of any class. What we do is we simply define a generic version of ArrayList by using the syntax of List<T>. T stands for type parameter, indicating that the type of the member objects in the list has not yet specified. We can replace T with any class or object. T gets replaced by the data type at compile time.
First add another button in the default.aspx page. Leave the default name.Double click on the button to create the Button2_Click event handling routine and type inside it
List<Person> people = new List<Person>();

Person p1 = new Person();

p1.FirstName = "Nikos";
p1.LastName = "Kantzelis";
p1.Email = "nikolaosk@hotmail.com";
p1.Height = 1.78m;
p1.Weight = 88.5m;

Person p2 = new Person();

p2.FirstName = "James";
p2.LastName = "Rowling";
p2.Email = "jamesr@hotmail.com";
p2.Height = 1.98m;
p2.Weight = 98.25m;

Person p3 = new Person();

p3.FirstName = "George";
p3.LastName = "Graham";
p3.Email = "graham@yahoo.co.uk";
p3.Height = 1.88m;
p3.Weight = 81.5m;

people.Add(p1);
people.Add(p2);
people.Add(p3);

foreach (Person item in people)
{

Response.Write(item.DisplayFullName());
Response.Write("<br/>");

}

Run your code and it will work just fine. The only thing we have changed so far is this line of code
ArrayList people = new ArrayList();
with this line of code
List<Person> people = new List<Person>();
and this foreach statement
foreach (object item in people)
{
Person myperson = (Person)item;

Response.Write(myperson.DisplayFullName());
Response.Write("<br/>");

}

with this one
foreach (Person item in people)
{

Response.Write(item.DisplayFullName());
Response.Write("<br/>");

}
We do not need casting anymore. Well that is awesome.
Now let's add an animal object.So just below the code we just typed add,
Animal myanimal = new Animal();
myanimal.isMammal = true;
myanimal.Species = "Panthera";
myanimal.Speed = 56.8;
myanimal.Height = 0.78m;
myanimal.Weight = 163;

people.Add(myanimal);

Try to build this application to see if it compiles. It will fail.It won't compile.Well this is a good thing since now we know (type safety) at compile time that we cannot add our animal object in the people collection.
There are ways to solve this problem but not with generic lists. The point I am trying to make in this post is a simple comparison ways that we group objects, hence a comparison of arrays,ArrayLists and Generic Collections.

Leave a comment ( or email me ) if you need the source code.
Hope it helps!!!

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

2013-03-15T00:21:00.003-07:00

If you run below SQL statement
---------------------------------------------------------
Select * into DBTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

INSERT INTO DBTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

and you get an error message like below
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

Solution
you need to download
"Data Connectivity Components for 2007 office system Driver".
after you download, please restart your server.

you can click link here to download from microsoft site.

sqlhelper.cs source code..

2013-02-20T22:04:00.001-08:00

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;

namespace Microsoft.ApplicationBlocks.Data
{
/// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient.
/// </summary>
public sealed class SqlHelper
{
#region private utility methods & constructors

//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelper()".
private SqlHelper() { }

/// <summary>
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}

command.Parameters.Add(p);
}
}

/// <summary>
/// This method assigns an array of values to an array of SqlParameters.
/// </summary>
/// <param name="commandParameters">array of SqlParameters to be assigned values</param>
/// <param name="parameterValues">array of objects holding the values to be assigned</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
}

// we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}

//iterate through the SqlParameters, assigning the values from the corresponding position in the
//value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}

/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the SqlCommand to be prepared</param>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}

//associate the connection with the command
command.Connection = connection;

//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;

//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}

//set the command type
command.CommandType = commandType;

//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}

return;
}

#endregion private utility methods & constructors

#region ExecuteNonQuery

// Jan 20, 2007 - Sadeer
// Added the ExecuteDeleteQuery method to support the History (Audit Trail) feature

/// <summary>
/// Execute a SQL delete statement against the database specified in the connection string.
/// </summary>
/// <param name="connectionString">The given connection string to connect to the database</param>
/// <param name="deleteText">The SQL DELETE statement to execute</param>
/// <param name="userID">The 24/7 ID of the user invoking the DELETE</param>
/// <returns>An integer representing the number of rows affected by the DELETE command</returns>
public static int ExecuteDeleteQuery(string connectionString, string deleteText, int userID)
{
// This function assumes that the given SQL query is actually a SQL DELETE statement.
string strDeleteText = deleteText.Trim().ToUpper();
if (0 != strDeleteText.IndexOf("DELETE")) return -1;

// DELETE FROM table WHERE c1 = v1 AND ...
int iStartIndex = strDeleteText.IndexOf("FROM") + "FROM ".Length;
int iEndIndex = strDeleteText.IndexOf("WHERE");

string strTableName = strDeleteText.Substring(iStartIndex, iEndIndex - iStartIndex);

// Prepare the UPDATE statement
string strUpdateText = @"UPDATE " + strTableName +
@" SET [TimeStamp] = GetDate() AND [User_ID] = @UserID";

System.Data.SqlClient.SqlParameter oParam = new SqlParameter("@UserID", userID);

// Execute the UPDATE statement
ExecuteNonQuery(connectionString, CommandType.Text, strUpdateText, oParam);

// Finally, execute the DELETE statement
return ExecuteNonQuery(connectionString, CommandType.Text, deleteText, oParam);
}

// End - Jan 20, 2007

// Jan 29, 2007 - Sadeer

/// <summary>
/// Execute a SQL delete statement against the database specified in the transaction.
/// </summary>
/// <param name="transaction">The transaction this DELETE statement will be part of</param>
/// <param name="deleteText">The SQL DELETE statement to execute</param>
/// <param name="userID">The 24/7 ID of the user invoking the DELETE</param>
/// <returns>An integer representing the number of rows affected by the DELETE command</returns>
public static int ExecuteDeleteQuery(SqlTransaction transaction, string deleteText, int userID)
{
// This function assumes that the given SQL query is actually a SQL DELETE statement.
string strDeleteText = deleteText.Trim().ToUpper();
if (0 != strDeleteText.IndexOf("DELETE")) return -1;

// DELETE FROM table WHERE c1 = v1 AND ...
int iStartIndex = strDeleteText.IndexOf("FROM") + "FROM ".Length;
int iEndIndex = strDeleteText.IndexOf("WHERE");

string strTableName = strDeleteText.Substring(iStartIndex, iEndIndex - iStartIndex);

// Prepare the UPDATE statement
string strUpdateText = @"UPDATE " + strTableName +
@" SET [TimeStamp] = GetDate() AND [User_ID] = @UserID";

System.Data.SqlClient.SqlParameter oParam = new SqlParameter("@UserID", userID);

// Finally, execute the DELETE statement
return ExecuteNonQuery(transaction, CommandType.Text, deleteText, oParam);
}

// End - Jan 29, 2007

/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();

//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored prcedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

//finally, execute the command.
int retval = cmd.ExecuteNonQuery();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

//finally, execute the command.
int retval = cmd.ExecuteNonQuery();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion ExecuteNonQuery

#region ExecuteDataSet

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();

//call the overload that takes a connection in place of the connection string
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

//create the DataAdapter & DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
cmd.CommandTimeout = 400;
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();

//return the dataset
return ds;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

//create the DataAdapter & DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();

//return the dataset
return ds;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion ExecuteDataSet

#region ExecuteReader

/// <summary>
/// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
/// we can set the appropriate CommandBehavior when calling ExecuteReader()
/// </summary>
private enum SqlConnectionOwnership
{
/// <summary>Connection is owned and managed by SqlHelper</summary>
Internal,
/// <summary>Connection is owned and managed by the caller</summary>
External
}

/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

//create a reader
SqlDataReader dr;

// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();

return dr;
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();

try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
}
catch
{
//if we fail to return the SqlDatReader, we need to close the connection ourselves
cn.Close();
throw;
}
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

AssignParameterValues(commandParameters, parameterValues);

return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//pass through to private overload, indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a SqlDataReader containing the resultset generated by the command</returns>
public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

AssignParameterValues(commandParameters, parameterValues);

return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion ExecuteReader

#region ExecuteScalar

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();

//call the overload that takes a connection in place of the connection string
return ExecuteScalar(cn, commandType, commandText, commandParameters);
}
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

//execute the command & return the results
object retval = cmd.ExecuteScalar();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;

}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

//execute the command & return the results
object retval = cmd.ExecuteScalar();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion ExecuteScalar

#region ExecuteXmlReader

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

//create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;

}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
/// </remarks>
/// <param name="connection">a valid SqlConnection</param>
/// <param name="spName">the name of the stored procedure using "FOR XML AUTO"</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
}

/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param>
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

//create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();

// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return retval;
}

/// <summary>
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid SqlTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
//if we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);

//call the overload that takes an array of SqlParameters
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}

#endregion ExecuteXmlReader
}

/// <summary>
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
/// </summary>
public sealed class SqlHelperParameterCache
{
#region private methods, variables, and constructors

//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelperParameterCache()".
private SqlHelperParameterCache() { }

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());

/// <summary>
/// resolve at run time the appropriate set of SqlParameters for a stored procedure
/// </summary>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="includeReturnValueParameter">whether or not to include their return value parameter</param>
/// <returns></returns>
private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(spName, cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(cmd);

if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; ;

cmd.Parameters.CopyTo(discoveredParameters, 0);

return discoveredParameters;
}
}

//deep copy of cached SqlParameter array
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

#endregion private methods, variables, and constructors

#region caching functions

/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParamters to be cached</param>
public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
{
string hashKey = connectionString + ":" + commandText;

paramCache[hashKey] = commandParameters;
}

/// <summary>
/// retrieve a parameter array from the cache
/// </summary>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <returns>an array of SqlParamters</returns>
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
string hashKey = connectionString + ":" + commandText;

SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];

if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}

#endregion caching functions

#region Parameter Discovery Functions

/// <summary>
/// Retrieves the set of SqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <returns>an array of SqlParameters</returns>
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}

/// <summary>
/// Retrieves the set of SqlParameters appropriate for the stored procedure
/// </summary>
/// <remarks>
/// This method will query the database for this information, and then store it in a cache for future requests.
/// </remarks>
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>an array of SqlParameters</returns>
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

SqlParameter[] cachedParameters;

cachedParameters = (SqlParameter[])paramCache[hashKey];

if (cachedParameters == null)
{
cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
}

return CloneParameters(cachedParameters);
}

#endregion Parameter Discovery Functions

}
}

You Can DownloadFile From Here.

get the 4 th maximum salary from a table without using any sql keyword (TOP,MAX are mot allowed)

2013-02-16T03:08:00.001-08:00

select ename,Salary from Emp a group by Ename,Salary
having 4=(select COUNT(distinct( salary) )from Emp b where a.salary<=b.Salary)

Hide (this copy of windows is not genuine) notification in windows 7

2013-01-02T21:58:00.001-08:00

with some easy step you can hide this notification form your desktop. so lets start........

Uninstalling Windows Update: KB971033
Go to Windows Update
Hit Installed Updates (link at the bottom left corner). If that link isn’t there, hit “view update history” and hit the “installed updates” link at the top.
Scroll down to the section titled “Microsoft Windows” and look for update KB971033.
Right click it and hit “uninstall”. Hit okay.

Reboot now.

after the reboot

1 Click start and simply type COMMAND PR in the start menu’s search box. Right click COMMAND PROMPT and select RUN AS ADMINISTRATOR.

2. In the command prompt window type:

slmgr.vbs -rearm

and press enter. (Note that there is a space between slmgr.vbs and -rearm.)

3. Now reboot and you're good to go.

This method is tested by me and a lot of other people(see comments) and it is working fine.

If you got stuck anywhere refer to comments. Comments act as a good troubleshooter.

Your queries are appreciated. Feel free to post comments.

How To Change Your Picture Tile Image And Your Picture Hub Background Image

2012-11-26T01:59:00.001-08:00

The following steps will give you the opportunity to change your Picture Tile image and the background image in your Picture Hub.
1 Home screen
2 Select Pictures
3 Under the Title “Pictures” Tap and Hold
4 A menu pops up and select ‘Change background”
5 Select a images in one of your your albums
6 Move the image around within the given frame.
7 Select the “v” check mark
8 Now your Picture Hub background image has been changed as well as your Picture Tile on the home screen.

How To Use Windows Phone 7 Device As Portable USB Flash Drive

2012-11-26T01:55:00.003-08:00

1 From your PC go to the start menu and in the search area type regedit. This will open the registry editor.

2 In the bar located on the left of the editor navigate to the given path

HKEY_LOCAL_MACHINE –> SYSTEM –> CurrentControlSet –> Enum –> USB

3 This will give you a long list in your side bar. Select the USB folder you have just reached but don’t do anything else yet.

4 Hit Ctrl+F, which will open the Find dialog box. Input ZuneDriver and enter. This will automatically bring you to the WUDF folder.

5 Select the folder just above WUDF, named Device Parameters, opening a number of entries in the main screen on the right.

6 Among the many entries look for “ShowInShell”, double click and change the value from 0 to 1.

7 The registry value marked as “PortableDeviceNameSpaceExcludeFromShell” goes to 1 from the default 0

8 Set “EnableLegacySupport” to 1.

9 Exit registry editor.

guys hope it'll help you.......

please comment me if it helped you.......