Friday, December 30, 2011

Show the msg before deleting data from the grid view in asp.net


i wrote the code in source window,

<asp:button  id="btn1" OnClientClick="javascript:return confirm('Are you sure you want to Delete this Record!');"   ></asp:button>

(or)

function confirm_Delete() {
                var msg = confirm("Are you sure you want to Delete this Record!");

                if (msg == true) {
                    alert("Record Deleted");
                    return true;
                }
                else {
                    return false;
                }
            }

Thursday, December 29, 2011

How to get the ID from db using Datakeys in ASP.NET



source page:
========
<gridview   ........ DataKeyNames="IdeaID"   ></gridview>
Code Behind:
=========

 protected void grdMyIdea_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            try
            {
             
                if (e.CommandName == "Edit")
                {
                    Response.Redirect("Idea.aspx?Ideaid=" +e.CommandArgument );
                }
                if(e.CommandName == "Delete")
                {
                    int intValue = 0;
                    DataAccessLayer _objDAL = new DataAccessLayer();
                    GridViewRow row = (GridViewRow)(((ImageButton)e.CommandSource).NamingContainer);
                    int IdeaID = Convert.ToInt32(((System.Web.UI.WebControls.GridView)   (sender)).DataKeys[row.RowIndex].Value);
                    SqlParameter[] _sqlparam = new SqlParameter[1];
                    _sqlparam[0] = new SqlParameter("@Ideaid", IdeaID);
                    intValue = _objDAL.Manipulation("THRSP_MyideaDelete", _sqlparam);
                    GetdataMyidea();
                   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Thursday, December 22, 2011

Send Email Functionality using ASP.NET with c sharp


Web.config file:
===========
<configuration>
  <appSettings>
    <add key="Email" value="chitharan89@gmail.com"/>
  </appSettings>
  <system.net>
    <mailSettings>
      <smtp >
        <network host="127.0.0.1" port="25" userName ="chitharan89@gmail.com" password ="9715929121" />
      </smtp>
    </mailSettings>
  </system.net>
<connectionStrings>
<add name="connection" connectionString="Data Source=SPTEST;database=db1;Integrated Security=True;  "/>
</connectionStrings>
</configuration>

protected void imgBtnshare_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                string FromEmail = ConfigurationManager.AppSettings["Email"].ToString();
                string ToEmail = "chitharan89@gmail.com";
                MailMessage Message = new MailMessage(FromEmail, ToEmail);
                Message.Subject = "To Share the Idea to Friends";
                Message.Body = "The main Purpose sharing ideas to friend and get reply ";                              
                Message.IsBodyHtml = true;
                SmtpClient SMTP = new SmtpClient();
                SMTP.Send(Message);
               
               
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);

            }

        }

Fetch the gridview data and display next page textboxes throw Query string using ASP.NET


 protected void grdMyIdea_RowCommand(object sender, GridViewCommandEventArgs e)
        { 
            try
            {
              
                if (e.CommandName == "Edit")
                {
                    Response.Redirect("sampleidea.aspx?Ideaid=" +e.CommandArgument );
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

ANOTHER PAGE: Ideaid is primarykey is also used in this table..fetch by another table Title and Description how? 

 protected void Page_Load(object sender, EventArgs e)
 {
           int Ideaid = 0;
           Ideaid = Convert.ToInt32(Request.QueryString["Ideaid"].ToString());
          
            if (!IsPostBack)
            {
                Getdata();
            }

public void Getdata()
        {
            SqlConnection con = new SqlConnection(connection);
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter("select Title,Description from Idea where Ideaid='" + Request.QueryString["Ideaid"].ToString() + "'", con);
            con.Close();
            DataSet ds = new DataSet();
            da.Fill(ds);
            TextBox1.Text = ds.Tables[0].Rows[0]["Title"].ToString();
            TextBox2.Text = ds.Tables[0].Rows[0]["Description"].ToString();
           
           
        }
 

Wednesday, December 21, 2011

Show msg onclick client side only using source file code.

OnClientClick="return confirm('Edit the file permanently ?');"

Grid view Serial No count will be generated automatically using this Code


protected void grid1_RowDataBound(object sender, gridviewRowDataBound e)
{
 if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label lblno = (Label)e.Row.FindControl("lblSerialNo");
                lblno.Text = ((grdMyIdea.PageIndex * grdMyIdea.PageSize) + e.Row.RowIndex + 1).ToString();
            }
}

Tuesday, December 20, 2011

Display current year, month and date


--Display name of the current month and Year--
Select DateName( Month, getDate() ) + ' ' + DateName( Year, getDate() )
--Display current year, month and date--
 SELECT 'Year: ' + CONVERT(VarChar(4), YEAR(GETDATE()))    + ', Month: ' + CONVERT(VarChar(2), MONTH(GETDATE()))
    + ', Day: ' + CONVERT(VarChar(2), DAY(GETDATE()))

Creating procedure used for Diplaying current year of quater months data's should be Visible.



CREATE PROC [dbo].[THRSP_CurrentQuaterIdea]
AS
BEGIN
DECLARE @month INT;
SET @month=MONTH (GETDATE())
IF(@month =1 or @month=2 or @month =3)
BEGIN
SELECT *FROM Idea WHERE YEAR(CreatedOn)=YEAR(GETDATE()) and MONTH(CreatedOn) in (1,2,3)
END
ELSE IF(@month =4 or @month=5 or @month =6)
BEGIN
SELECT *FROM Idea WHERE YEAR(CreatedOn)=YEAR(GETDATE()) and MONTH(CreatedOn) in (4,5,6)
END
ELSE IF(@month =7 or @month=8 or @month =9)
BEGIN
SELECT *FROM Idea WHERE YEAR(CreatedOn)=YEAR(GETDATE()) and MONTH(CreatedOn) in (7,8,9)
END
ELSE IF(@month =10 or @month=11 or @month =12)
BEGIN
SELECT *FROM Idea WHERE YEAR(CreatedOn)=YEAR(GETDATE()) and MONTH(CreatedOn) in (10,11,12)
END

END


GO

Thursday, December 15, 2011

Show the lastweek, Today, Mostvotes, Leastvotes,Noofvotes using a sp in Sql Server


USE [db1]
GO

/****** Object:  StoredProcedure [dbo].[THRSP_ShowListOfIdea]    Script Date: 12/16/2011 10:34:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[THRSP_ShowListOfIdea]
(
@Showme varchar(20)
)
AS
IF(@Showme='Last Week')
BEGIN
declare @fromdate datetime;
declare @enddate datetime;
set @fromdate=(select convert(datetime, (convert (varchar(10), getdate(),101))))
set @enddate=(select convert(datetime, (convert (varchar(10), getdate()-7,101))))
select Title,Description from Idea where CreatedOn between  @enddate and @fromdate
END

ELSE IF(@Showme='Today')
BEGIN
Declare @today datetime;
set @today =(select convert(datetime, (convert (varchar(10), getdate(),101))))
SELECT i.Title, i.Description from idea i INNER JOIN votes v on i.ideaid=v.ideaid where
(year (v.VotedOn)=year(@today)and  month (v.VotedOn)=month(@today)and  day (v.VotedOn)=day(@today))
END

ELSE IF(@Showme='Most votes')
BEGIN
select * from (
SELECT i.Title, i.Description
,(select COUNT(ideaid) from Votes where ideaid=v.ideaid)as ideacount
from idea i INNER JOIN votes v on i.ideaid=v.ideaid ) as tempTable
order by ideacount desc
END

ELSE IF(@Showme='Least Votes')
BEGIN
select * from (
SELECT i.Title, i.Description
,(select COUNT(ideaid) from Votes where ideaid=v.ideaid)as ideacount
from idea i INNER JOIN votes v on i.ideaid=v.ideaid ) as tempTable
order by ideacount ASC
END
ELSE IF(@Showme='Number Of Votes')
BEGIN
SELECT i.Title, i.Description from idea i INNER JOIN votes v on i.ideaid=v.ideaid
END

GO


Wednesday, December 14, 2011

Displaying current date last week date using sql server


declare @fromdate datetime;
declare @enddate datetime;
set @fromdate=(select convert(datetime, (convert (varchar(10), getdate(),101))))
set @enddate=(select convert(datetime, (convert (varchar(10), getdate()-2,101))))

select *from Votes where VotedOn between  @enddate and @fromdate

Tuesday, December 13, 2011

Show the date ,hour and day with message in sql server.


select top(3) IdeaID,Comments ,
Case when (CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))>1439)
then CONVERT(varchar(20), CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))/1440 ) +' days'
 when (CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))>=60)
then CONVERT(varchar(20),CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))/60) + ' hours'
else
CONVERT(varchar(20),CONVERT (INT, DATEDIFF(minute,CommentedOn,GETDATE()))) + 'minutes'
End   from comments order by CommentedOn desc

Monday, December 12, 2011

Display the how many day's ,hours,minites and seconds using sql server


select
DATEDIFF(day,2007-11-30,2007-11-20) AS NumberOfDays,
DATEDIFF(hour,2007-11-30,2007-11-20) AS NumberOfHours,
DATEDIFF(minute,'2011-12-12 05:43:06.007','2011-12-12 05:45:12.100') AS NumberOfMinutes,
DATEDIFF(second,'2011-12-12 05:45:06.007','2011-12-12 05:45:06.007') AS NumberOfSecond

Friday, December 2, 2011

Mouse over Event (image), when u move over the image display the another image using Javascript

<
<
html xmlns="http://www.w3.org/1999/xhtml">head runat="server">
</
<
<title></title>head>body><form id="form1" runat="server"><div><asp:Image ID="Image1" runat="server" Width="60px" Height="80px" ImageUrl="~/Lighthouse.jpg" onmouseover="ShowBiggerImage(this);" onmouseout="ShowDefaultImage(this);"/></div><div id="LargeImageContainerDiv" style="position: absolute; z-index:2"></div></form><script type="text/javascript" language="ecmascript">
document.getElementById(
}

document.getElementById(
}

event = event || window.event;
LargeImageContainerDiv.style.left = event.clientX + document.body.scrollLeft + 10;
LargeImageContainerDiv.style.top = event.clientY + document.body.scrollTop + 10;
}
document.onmousemove = move_Area;


</
</
function ShowBiggerImage(obj) {"LargeImageContainerDiv").innerHTML = "<img src='" + obj.src + "'+'width=150 height=200' >";function ShowDefaultImage(obj) {"LargeImageContainerDiv").innerHTML = "";function move_Area(event) {</script>body>html>

Thursday, December 1, 2011

Not allowed for redirect to page(Im using Quality Functional Deployment-QFD)

ScriptManager.RegisterStartupScript(this, this.GetType(),
 "Javascript", "javascript:collapseAll(4);", true);

Disable textbox and not placed cursor in textbox.

function SetCheckBoxStatus() {

if (document.getElementById("<%=chkShow.ClientID%>").checked) {
document.getElementById("<%=txtArticleBody.ClientID%>").disabled = true;
document.getElementById("<%=fuThumbnailImage.ClientID%>").disabled = true;

}
else {
document.getElementById("<%=txtArticleBody.ClientID%>").disabled = false;
document.getElementById("<%=fuThumbnailImage.ClientID%>").disabled = false;

}
}

Friday, November 25, 2011

How To validate Email ID a Form in ASP. Net.

<script language="javascript" type="text/javascript">
function validate()
{
if (document.getElementById("<%=TextBox1.ClientID%>").value=="")
      {
                 alert("Name can not be blank");
                 document.getElementById("<%=TextBox1.ClientID%>").focus();
                 return false;
      }
      
      if(document.getElementById("<%=TextBox2.ClientID %>").value=="")
      {
                 alert("Email id can not be blank");
                document.getElementById("<%=TextBox2.ClientID %>").focus();
                return false;
      }
     var emailPat= /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;
     var emailid=document.getElementById("<%=TextBox2.ClientID %>").value;
     var matchArray = emailid.match(emailPat);
     if (matchArray == null)
    {
               alert("Your email address seems incorrect. Please try again.");
               document.getElementById("<%=TextBox2.ClientID %>").focus();
               return false;
    }
    if (document.getElementById("<%=TextBox3.ClientID%>").value=="")
      {
                 alert("Contact Number can not be blank");
                 document.getElementById("<%=TextBox3.ClientID%>").focus();
                 return false;
      }
     var digits="0123456789";
     var temp;
     for (var i=0;i<document.getElementById("<%=TextBox3.ClientID %>").value.length;i++)
     {
     temp=document.getElementById("<%=TextBox3.ClientID%>").value.substring(i,i+1);
     if (digits.indexOf(temp)==-1)
      {
      alert("Please enter correct Contact Number");
      document.getElementById("<%=TextBox3.ClientID%>").focus();
      return false;
      }
     }
      if(document.getElementById("<%=TextBox5.ClientID %>").value=="")
    {
      alert("City can not be blank");
     document.getElementById("<%=TextBox5.ClientID %>").value;
      document.getElementById("<%=TextBox5.ClientID %>").focus();
      return false;
    }
      if(document.getElementById("<%=TextBox6.ClientID %>").value=="")
    {
     alert("Password can not be blank");
     document.getElementById("<%=TextBox6.ClientID %>").value;
     document.getElementById("<%=TextBox6.ClientID %>").focus();
      return false;
    }
    if(document.getElementById("<%=TextBox7.ClientID %>").value=="")
    {
     alert("Please Re-Enter Password ");
   document.getElementById("<%=TextBox7.ClientID %>").value;
   document.getElementById("<%=TextBox7.ClientID %>").focus();
    return false;
    }
     return true;
}
</script>

Thursday, November 24, 2011

Limit no of character in textbox with count in Asp.net using javascript

<script type="text/javascript">
 {if (field.value.length > maxlimit)


{return false;</script>

}
}
function textCounter(field,maxlimit)


protected void Page_Load(object sender, EventArgs e)"onkeydown", "return textCounter(this, 5)");"onkeyup", "return textCounter(this, 5)");"onmousedown","return textCounter(this, 5)");"onmouseup","return textCounter(this, 5)");"onblur", "return textCounter(this, 5)");
{
txtmaxlen.Attributes.Add(
txtmaxlen.Attributes.Add(
txtmaxlen.Attributes.Add(
txtmaxlen.Attributes.Add(
txtmaxlen.Attributes.Add(

}

Wednesday, November 16, 2011

Spaces are not allowed!

<asp:RegularExpressionValidator ID="rev" runat="server" ControlToValidate="txtBox" 
   
ErrorMessage="Spaces are not allowed!" ValidationExpression="[^\s]+" /> 
<asp:RequiredFieldValidator ID="rfv" runat="server" ControlToValidate="txtBox"  
   
ErrorMessage="Value can't be empty" /> 

Monday, September 26, 2011

Adding more Interactive features to our Video Player.


I promised in the previous topics that I will be adding more “stuff” to each topic that we discussed prior and in this article I want to revisit the previous article where we created a cool Video Player using a button. For those who didn’t read that article please see it here.  Now, remember that the only interactive feature we added was a button click event to play the video but we didn’t include any visuals. So this time around we will add Play, Stop, and Pause buttons.

In future topics we will add a volume slider, and a Seek to Slider buttons, among other things and I will explain these later on, but for now we want to add interactive buttons to our video player, and make it look like the following or whatever cool video player you created last time:
Note: Before I go deeper into this Media API discussion, I want to make sure that the reader understands that this is an introductory topic to beginners of Expression Blend and WPF.
Now that we have taken care of the disclaimerJ, let’s discuss a little bit about the Media API and how it works in Expression Blend.  Expression Blend supports audio and video file types that Microsoft Windows Media Player 10 and above supports.  This means in order to work with media in Expression Blend, you must have Windows Media Player 10 OCX or above installed on your computer. As you may have noticed in the previous article when you add a MediaElement in Expression Blend the Media pane is added:

We did touch a little bit about the MediaElement and MediaPlayer in the previous topic, and these two can be used to present video, video with audio, and audio. There are however, used to target different scenarios although both rely on the Microsoft Windows Media Player 10 OCX and above for media playback. Since MediaElement is a UIElement that it supported by the Layout System, it can be consumes as content by many controls. On the other hand MediaPlayer is designed for Drawing object and lacks layout support, therefore media loaded using MediaPlayer can only be presented using a VideoDrawing or directly interacting with DrawingContent. As you have already noticed in the previous topic when you insert a MediaElement in Expression Blend, the Insert feature adds an audio or video file to the artboard wrapped in a MediaElement control as long as you’re using one of the supported media types.
Enough about the conceptual stuffJ, let’s jump in and do some designing and add more controls to our previous video player.
  1. Open the previous project just before we added a video to the “cool” button we designed. We are not making any changes to the video this time but you should have a video linked to the Video Player.
  2. Add a rectangle to the artboard with a solid white Fill and no Stroke. This will be our Play button.
  3. Convert the rectangle to a path (right click on element):

4.     Using the Direct Selection tool drag one corner of the converted path halfway towards the other corner:
Make sure you have a straight line.
5.    Use  the Transform panel to rotate the path (rectangle) 45%, and also reduce the size of the path to 13×14 (W x H):

6.    Next, let’s create a Stop button. Add another rectangle with whatever Fill you want and no Stroke.  Also reduce the size to 10×12 (WxH).
7.   Let’s move on to the Pause button, add two rectangles (2×12) in size. Group Into a Grid the two rectangles and name the grid “Pause”. Now you should have all three buttons as follows: Play, Stop, Pause:

Now, we could simply add a few lines of code to these three buttons and make them interactive with our video but we are not going to do that yetJ! Remember, we are supposed to be creating cool things in Expression Blend!
8.   So the next step we want to do is to duplicate or copy the cool button we created in step 15 and reduce the size to say 85×47 (WxH). Position this smaller button on the center of the video player or wherever you like.
9.  Next, edit the ButtonStyle of that button (Video Player) you copied above and add the “Play” element you created above.  Mine looks like this:

10.  Repeat the above step for the Stop and Pause buttons. Tip: simply copy the button style and edit the Template if you want to apply the same style to all buttons. If on the other hand you want to apply a different style, simply create a new style from the base class:


Tip: Remember when aligning your elements, the Layout and the Transform panels are your best friends!
11.   Once you’re done with the layout your Play, Stop and Pause buttons should look like the following or something similar:

Now, positioning this on my video player:

12.  Next, let’s add a few simply lines of code to make our buttons interactive. In my LayoutRoot, I have the following:

Now, we want to move the button click play event we created last time to the Play button we just created:


13.  Next wire the Stop and Pause buttons using simple click events:

Note: There are better ways of wiring this other than what we just did but that will be for another topic in the future. I want to keep it as simple as possible to none coders.
14.  Build and test you application, you should be able to Play, Stop and Pause the video:

Conclusion
That’s it folks for nowJ! We have seen how Play, Pause and Stop methods can be used to control media. In the next few topics we will cover other ways of interacting with media, for instance, using triggers.  Remember again, this is an introductory topic to new Expression Blend/WPF users, and people who are interested in learning how to use Expression Blend for designing UserInterfaces (UIs).  Hope this helps someone!

Friday, September 23, 2011

LINQ WITH DELEGATES CONCEPTS

Normal query string:
===================

static void Main(string[] args)
        {
            string[] names = { "chitharans", "naga", "ravi", "praveen", "chith", "hello", "welcome" };
            IEnumerable<string> query123 = from sss in names where sss.Length >= 4 orderby sss descending  select sss.ToUpper();
            foreach (string item in query123)
                Console.WriteLine(item);
            Console.ReadKey();
        }


Funtion  using query string:
=========================================

  static void Main(string[] args)
        {
            string[] names = { "chitharans", "naga", "ravi", "praveen", "chith", "hello", "welcome" };
            Func<string, bool> one = sss => sss.Length >= 4;
            Func<string, string> two = sss => sss;
            Func<string, string> three = sss => sss.ToUpper();
            IEnumerable<string> query123 = names.Where(one).OrderBy(two).Select(three);
          
            foreach (string item in query123)
                Console.WriteLine(item);
            Console.ReadKey();
        }

Delegates using query string:
============================

 static void Main(string[] args)
        {
            string[] names = { "chitharans", "naga", "ravi", "praveen", "chith", "hello", "welcome" };
            Func<string, bool> one = delegate(string sss)
            {
                return sss.Length>=4;
            };
            Func<string, string> two = delegate(string sss)
            {
                return sss;
            };
            Func<string, string> three = delegate(string sss)
            {
                return sss.ToUpper();
            };



           
            IEnumerable<string> query123 = names.Where(one).OrderBy(two).Select(three);
         
            foreach (string item in query123)
                Console.WriteLine(item);
            Console.ReadKey();
        }

Thursday, September 15, 2011

Creating Visual State Manager(Media Player)

using System;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

namespace SLVSMMediaPlayer
{
    public partial class VSMPlayer1 : UserControl
    {
        public VSMPlayer1()
        {
            // Required to initialize variables
            InitializeComponent();
            Loaded +=new RoutedEventHandler(VSMPlayer1_Loaded);
        }
      
        void VSMPlayer1_Loaded(object sender, RoutedEventArgs e)
        {
          
            LayoutRoot.MouseEnter +=new MouseEventHandler(LayoutRoot_MouseEnter);
            LayoutRoot.MouseLeave += new MouseEventHandler(LayoutRoot_MouseLeave);
            button.Click += new RoutedEventHandler(button_Click);
            button1.Click += new RoutedEventHandler(button1_Click);
           
           
        }

        void button1_Click(object sender, RoutedEventArgs e)
        {
            //throw new NotImplementedException();
            mymedia.Pause();
        }

        void button_Click(object sender, RoutedEventArgs e)
        {
           // throw new NotImplementedException();
            mymedia.Play();
        }

        void LayoutRoot_MouseLeave(object sender, MouseEventArgs e)
        {
            //throw new NotImplementedException();
            VisualStateManager.GoToState(this,"MouseLeave", true);
        }

        void LayoutRoot_MouseEnter(object sender, MouseEventArgs e)
        {
            //throw new NotImplementedException();
            VisualStateManager.GoToState(this, "MouseEnter", true);
        }
    }
}

Thursday, September 8, 2011

Silverlight and the layout management. (Grid, Canvas, StackPanel and dock panel)






If you have done any Silverlight development, then you are familiar with the Canvas, the Grid and the StackPanel.
All 3 containers are unique in their own way. But they all have some limitations/drawbacks. Let's see:
1: The Canvas:
<Canvas Background="Blue">
   <TextBlock Height="50" Width="50" Canvas.Left="15" Canvas.Top="15" />
</Canvas>
I don't want to pixelate my items.
2: The StackPanel: 
<StackPanel>
   <TextBlock Height="50" Width="50" />
   <TextBlock Height="50" Width="50" />  
</StackPanel>
Just not rich enough to do any complex layouts.
3: The Grid:
<Grid Background="Silver">
   <Grid.ColumnDefinitions>
       <ColumnDefinition Width="*2" />
       <ColumnDefinition Width="*1" />
   </Grid.ColumnDefinitions>
   <Grid.RowDefinitions>
       <RowDefinition Height="50" />
       <RowDefinition Height="100" />
   </Grid.RowDefinitions>
   <TextBlock Height="50" Width="50" Grid.Row="1" Grid.Column="1"/>
</Grid>
Yes, I can define a very complex layout using the Grid. But I am still required to place my items inside "a" cell by specifying
the Grid.Row and Grid.Column attributes on each individual item. I also need to decide, how may rows and columns my page has.
This just does not feel natural.
To address this, Developer Express is working on a special LayoutControl container that will eliminate these hassles. All you have to do is specify where you want
your items placed. Left, Right, Top or Bottom.
<DockedLayoutControl x:Name="layoutItems" DockedLayoutControl.Dock="Client">
            <TextBlock Text="My Top Text" DockedLayoutControl.Dock="Left"/>
            <TextBlock Text="My Bottom Text" DockedLayoutControl.Dock="Bottom"/>
</DockedLayoutControl>
Here is a screen shot of a layout done using a DockedLayoutContro

Wednesday, September 7, 2011

Creating Stored Procedure?,Calling Stored Procedure,Inserting Data to Stored Procedure

Sample image

Introduction

There are several advantages of using stored procedures instead of standard SQL. First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements. Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.

Creating Stored Procedure?

Enterprise Manager provides an easy way to create stored procedures. First, select the database to create the stored procedure on. Expand the database node, right-click on "Stored Procedures" and select "New Stored Procedure...". You should see the following:
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
Substitute OWNER with "dbo" (database owner) and PROCEDURE NAME with the name of the procedure. For example:
CREATE PROCEDURE [dbo].[GetProducts] AS So far, we are telling SQL Server to create a new stored procedure with the name GetProducts. We specify the body of the procedure after the AS clause:
CREATE PROCEDURE [dbo].[GetProducts] AS SELECT ProductID, ProductName FROM Products Click on the Check Syntax button in order to confirm that the stored procedure is syntactically correct. Please note that the GetProducts example above will work on the Northwind sample database that comes with SQL Server. Modify it as necessary to suite the database you are using.
Now that we have created a stored procedure, we will examine how to call it from within a C# application

Calling Stored Procedure

A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement.

1. Create a new C# Windows Application project.

2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.

3. Double-click on the Form to generate the Form_Load event handler. Before entering any code, add "using System.Data.SqlClient" at the top of the file.

Enter the following code:
private void Form1_Load(object sender, System.EventArgs e) {
SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=db_first;Integrated Security=SSPI"
);
SqlCommand command = new SqlCommand("GetProducts", conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dg_Data.DataSource = ds;

this.dg_Data.DataMember = "Products";

Inserting Data to Stored Procedure

Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure. First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.
Sql Server Code
CREATE PROCEDURE [dbo].[InsertUser] (
@Username varchar(50), @Password varchar(50)
)
AS INSERT INTO Users VALUES(@Username, @Password)


C# code
string username = Username.Text // get username from user string //
password = Password.Text // get password from user

SqlConnection conn = new SqlConnection"
DataSource=localhost;Database=db_First;Integrated Security=SSPI"
);
SqlCommand command = new SqlCommand("InsertUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
conn.Open(); int rows = command.ExecuteNonQuery();
conn.Close();
First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.
<code>if (rows == 1) {
MessageBox.Show("Create new user SUCCESS!");
}
else {
MessageBox.Show("Create new user FAILED!"); }
We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.

Conclusion

Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The combination of these two allows us to create very powerful appliations rapidly.
enjoyyyyyyyyyyyyyyyyyyyyyyyyyy