Sunday, November 28, 2010

LINQ - Insert,Update,Delete with LINQ to SQL using Stored Procedure

In this article i will show you how to insert,update and delete data with LINQ to SQL by using Stored procedure

Step 1
Create a student table in database,it is look like this














Step 2
Create a Insert,Update and Delete Stored procedure in Database, it is look like this

Insert

CREATE PROCEDURE dbo.InsertStudent
(
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    
    @Status nvarchar(100) OUTPUT
)
AS

    BEGIN
    
        BEGIN TRY
            
            INSERT INTO Student
            (FirstName,LastName)
            VALUES
            (@FirstName,@LastName) 
            
            SET @Status='Data Insert Sucessfully' 
            
        END TRY
        
        BEGIN CATCH
            SET @Status='Data Insert UnSucessfully'
        END CATCH
    
    END 

GO


Update
CREATE PROCEDURE dbo.UpdateStudent
(
    @StudentID Numeric(18,0),
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    
    @Status nvarchar(100) OUTPUT
)
AS

        BEGIN 
        
            BEGIN TRY
            
                UPDATE Student 
                    SET FirstName=@FirstName,
                          LastName=@LastName
                         
                        WHERE StudentID=@StudentID;
                        
                
                SET @Status='Data Update Sucessfully'        
                
            
            END TRY
            
            BEGIN CATCH
            
                SET @Status='Data Update UnSucessfully'
            
            END CATCH
        
        END 

GO

Delete
CREATE PROCEDURE dbo.DeleteStudent
(

    @StudentID Numeric(18,0),
    
    @Status nvarchar(100) OUTPUT
    
)
AS

    BEGIN
    
        BEGIN TRY
        
            DELETE Student 
                WHERE Student.StudentID=@StudentID
                
                SET @Status='Data Delete Sucessfully'
        
        END TRY
        BEGIN CATCH
                SET @Status='Data Delete UnSucessfully' 
        END CATCH
    
    END 

GO

Step 3
Create a console application and give the solution name as ConLinqToSql_StoredProc.

Step 4
Add a ConnectionString in app.config file,it is look like this

<connectionStrings>
        <add name="ConStr"
            connectionString="Data Source=SHREE\SHREE;Initial Catalog=ABC;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Step 5
Create a new folder inside the solution and give the folder name as ORD,it is look like this

 















Step 6
Create a Student Model Class inside the ORD folder,it is look like this

 public class StudentModel
    {
        #region Property

        public long StudnetID
        {
            get;
            set;
        }

        public String FirstName
        {
            get;
            set;
        }

        public String LastName
        {
            get;
            set;
        }

        #endregion
    }

Step 7
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it StudentDC and click on add button,it is look like this



















Click on image for better view

Step 8
Open a O/R Designer by double click on StudentDC.dbml,it is look like this



















Click on image for better view

















Click on image for better view

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 9
To map stored procedure in DataContext class.go to the server explorer,select a database,go to the Stored Procedure and select InsertStudent,UpdateStudent and DeleteStudent stored procedures ,it is look like this


























Click on image for better view

Drag and drop all three stored procedures from Server explorer onto the design surface of the O/R Designer,it is look like this


















Click on image for better view


Step 10
Create a Student static class in a ORD folder for insert,update and delete data to database,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConLinqToSql_StoredProc.ORD
{
    public static class Student
    {
        #region Methods

        /// <summary>
        /// Insert Student Data
        /// </summary>
        /// <param name="StudentObj">Specify the Student Object</param>
        /// <returns>String</returns>
        public static String InsertStudent(ORD.StudentModel StudentObj)
        {
            String Status = String.Empty; 
            try
            {
                // Create a Student DataContext Object and specify the connectionstring in datacontext constructor.
                ORD.StudentDC DC = new StudentDC(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.Trim());

                // Call maped Insert Stored Procedure
                DC.InsertStudent(StudentObj.FirstName, StudentObj.LastName, ref Status);

                return Status; 

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }
        }

        /// <summary>
        /// Update Student Data
        /// </summary>
        /// <param name="StudentObj">Specify the Student Object</param>
        /// <returns>String</returns>
        public static String UpdateStudent(ORD.StudentModel StudentObj)
        {
            String Status = String.Empty;

            try
            {
                // Create a Student DataContext Object and specify the connectionstring in datacontext constructor.
                ORD.StudentDC DC = new StudentDC(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.Trim());

                // Call maped Update Stored Procedure
                DC.UpdateStudent(StudentObj.StudnetID, StudentObj.FirstName, StudentObj.LastName, ref Status);

                return Status; 
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }
        }

        /// <summary>
        ///  Delete Student Data
        /// </summary>
        /// <param name="StudentObj">Specify the Student Object</param>
        /// <returns>String</returns>
        public static String DeleteStudent(ORD.StudentModel StudentObj)
        {
            String Status = String.Empty;

            try
            {
                // Create a Student DataContext Object and specify the connectionstring in datacontext constructor.
                ORD.StudentDC DC = new StudentDC(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.Trim());

                // Call maped Delete Stored Procedure
                DC.DeleteStudent(StudentObj.StudnetID, ref Status);

                return Status; 
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }
        }


        #endregion
    }
}

Step 11
Call a methods in Main function,it is look like this


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConLinqToSql_StoredProc
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Create a Object of StudentModel Class
                ORD.StudentModel StudentModelObj = new ORD.StudentModel();
                
                
                //#region Insert Section


                //StudentModelObj.FirstName = "Kishor";
                //StudentModelObj.LastName = "Naik";

                //System.Console.WriteLine(ORD.Student.InsertStudent(StudentModelObj));       

                //#endregion



                #region Update Section

                //StudentModelObj.StudnetID = 1;
                //StudentModelObj.FirstName = "Yogesh";
                //StudentModelObj.LastName = "Naik";

                //System.Console.WriteLine(ORD.Student.UpdateStudent(StudentModelObj));   

                #endregion


                #region Delete Section

                StudentModelObj.StudnetID = 1;

                System.Console.WriteLine(ORD.Student.DeleteStudent(StudentModelObj));   

                #endregion


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }
        }
    }
}

Run the Project.

Download
Download Source Code

Wednesday, November 24, 2010

AJAX - Accordion Control

The Accordion is a web control that allows you to provide multiple panes and display them one at a time.The Accordion is implemented as a web control that contains AccordionPane web controls. Each AccordionPane control has a template for its Header and its Content.

Let See how to create Accordion Panel in ASP.net.


Step 1
Create a Web application and give the solution name as SolAccordion.

Step 2
Add a ToolkitScriptManager control from Ajax toolkit inside the Div Tag,it is look like this



 <asp:ToolkitScriptManager ID="ToolkitScriptManger1" runat="server">
 </asp:ToolkitScriptManager>   

This control allows you to replace the default <asp:scriptmanager> control behavior, and supports the ability to dynamically merge multiple client-side Javascript scripts into a single file that is downloaded to the client at runtime. 

Step 3
Create a CSS for accordion control,Add a new folder in the solution and give the folder name as CSS,Select the CSS folder,right click on Add new Item,select Style Sheet from installed Visual Studio templates and click on add button.


/* Accordion Simple CSS */

/* For Header*/    
.AccordionHeader
{
    border: 2px solid #A5A5A5;
    color: white;
    background-color:Gray;
    font-family: Arial, Sans-Serif;
    font-size: 12px;
    font-weight: bold;
    padding: 5px;
    margin-top: 5px;
    cursor: pointer;
}

/* For Content*/
.AccordionContent
{
    background-color:Teal;
    border: 1px solid #2F4F4F;
    border-top: none;
    padding: 5px;
    padding-top: 10px;
}

Step 4
Attach a CSS inside the Head Section,it is look like this


<head runat="server">
    <title></title>

    <link href="CSS/StyleSheet.css" type="text/css" rel="Stylesheet" />
         
</head>

Step 5
Add a Accordion Control from Ajax toolkit,it is look like this

<asp:Accordion ID="Accordian1" runat="server" FadeTransitions="true"  TransitionDuration="400" headercssclass="AccordionHeader"
contentcssclass="AccordionContent" SelectedIndex="-1" RequireOpenedPane="false" FramesPerSecond="50" >


</asp:Accordion>   

There are the following basic properties of Accordion control:

1. FadeTransitions
    True to use the fading transition effect, false for standard transitions.

2. TransitionDuration
     Number of milliseconds to animate the transitions.

3.  HeaderCssClass 
     Name of the CSS class to use for the headers. This can be either applied to the Accordion  as a default for all AccordionPanes, or an individual AccordionPane.

4.  ContentCssClass 
     Name of the CSS class to use for the content. This can be either applied to the Accordion as a default for all AccordionPanes, or an individual AccordionPane.

5.  SelectedIndex
     The AccordionPane to be initially visible.

6.  RequireOpenedPane
     Prevent closing the currently opened pane when its header is clicked (which ensures one pane is always open). The default value is true.

7.  FramesPerSecond
     Number of frames per second used in the transition animations. 
  

Step 6
With the Panes added, you can now add the AccordionPanes with header as well as content inside the accordian control,it is look like this


<asp:Accordion ID="Accordian1" runat="server" FadeTransitions="true"  TransitionDuration="400"
                headercssclass="AccordionHeader"
contentcssclass="AccordionContent" SelectedIndex="-1" RequireOpenedPane="false" FramesPerSecond="50" >

                    <Panes>

                        <asp:AccordionPane  ID="AboutUs" runat="server">
                            <Header>
                                About Us
                            </Header> 

                            <Content>
                               On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.
To change the overall look of your document, choose new Theme elements on the Page Layout tab. To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command. Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template.
On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.

                            </Content> 
                        </asp:AccordionPane>
                        
                        <asp:AccordionPane ID="Service" runat="server">
                            <Header>
                                Service
                            </Header>

                            <Content>
                               On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.
To change the overall look of your document, choose new Theme elements on the Page Layout tab. To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command. Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template.
On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.

                            </Content> 
                        </asp:AccordionPane>     

                        <asp:AccordionPane ID="Gallery" runat="server">
                            <Header>
                                Gallery
                            </Header>

                            <Content>
                                On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.
To change the overall look of your document, choose new Theme elements on the Page Layout tab. To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command. Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template.
On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.

                            </Content> 
                        </asp:AccordionPane> 
                        
                        <asp:AccordionPane ID="ContactUs" runat="server">
                            <Header>
                                Contact Us
                            </Header>

                            <Content>
                                On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.
To change the overall look of your document, choose new Theme elements on the Page Layout tab. To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command. Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template.
On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.
You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.

                            </Content> 
                        </asp:AccordionPane>   
                          
                    </Panes>             
                            
                </asp:Accordion>   

Run the Project.

Download
Download Source Code

DataBinding AJAX Accordion Control

Tuesday, November 23, 2010

WPF - Media Player in WPF

In this article i will show you how to use Media Element in WPF and i will explore some of the functionalities such as drag and drop media file in the media element, Play, Pause, Stop, Back and Forward,Change the volume of the media and fullscreen mode. 

We can see above functionalities step by step.

Step 1
Create a WPF application.

Step 2
Now we will first design the application so that all the functionalities would be clear to us.

Add a Media Element, two slider bar,
and several Template buttons for the functions to be achieved.it is look like this

























Click on image for better view

Download Source Code and view the XAML code, style and Template XAML code in the ResourceDictionary

Step 3
In the constructor of the Application add a DispatcherTimer object and disabled a several Controls,it is look like this



 #region Declaration

        private DispatcherTimer Timer = null;
        private Double CurrentPosition = 0;
        private Boolean IsSeekBarDragging = false;
        private Boolean IsFullscreen = false; 

        #endregion

 public MainWindow()
        {
            this.InitializeComponent();

            #region Timer Section

            try
            {
                Timer = new DispatcherTimer();
                Timer.Interval = TimeSpan.FromMilliseconds(200);
                Timer.Tick += new EventHandler(Timer_Tick);
            }
            catch (Exception)
            { 
            }
            #endregion

            EnabledDisabledControls(false); // disabled controls

            btnPlay.IsEnabled = false; // disabled play button control  
        }

 /// <summary>
        /// Enable and Disabled Controls
        /// </summary>
        /// <param name="Flag">for enable set true and for disable set false</param>
        private void EnabledDisabledControls(Boolean Flag)
        {
            try
            {
                btnPause.IsEnabled = Flag;
                btnStop.IsEnabled = Flag;
                btnForward.IsEnabled = Flag;
                btnBackword.IsEnabled = Flag;
                SeekBar.IsEnabled = Flag;
                VolumeBar.IsEnabled = Flag;
                btnFullScreen.IsEnabled = Flag;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);  
            }
        }

Step 4
Drag and Drop the .wmv file in media element,it is look like this

First Set AllowDrop property as True in the Window element and then add the below code on Window_Drop event.

 /// <summary>
        /// Drop the .wmv file in the media element
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Window_Drop(object sender, DragEventArgs e)
        {
            try
            {

                String[] FileName = (String[])e.Data.GetData(DataFormats.FileDrop, true);

                if (FileName.Length > 0)
                {
                    String VideoPath = FileName[0].ToString();

                    if (CheckWMAExtension(VideoPath))
                    {
                        MediaPlayer.Source = new Uri(VideoPath);
                        DDMessage.Text = "     Click Play button";

                        btnPlay.IsEnabled = true;
                    }
                    else
                    {
                        MessageBox.Show("you are choose wrong file");  
                    }
                }

                e.Handled = true;  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Check .wmv file extension

 /// <summary>
        ///  Check .wma File Extension
        /// </summary>
        /// <param name="FilePath">Specify the file path</param>
        /// <returns>Boolean</returns>
        private Boolean CheckWMAExtension(String FilePath)
        {
            Boolean Flag = false; 
            try
            {
                String Extension=System.IO.Path.GetExtension(FilePath);

                if (Extension != String.Empty)
                {
                    if (Extension == ".wmv")
                    {
                        Flag = true; 
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return Flag; 
        }

Step 5
Add the below code in Timer_Tick event,it is look like this


 /// <summary>
        ///  Timer Tick Event    
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Timer_Tick(object sender, EventArgs e)
        {
            try
            {
                if (!IsSeekBarDragging)
                {
                    SeekBar.Value = MediaPlayer.Position.TotalSeconds;
                    CurrentPosition = SeekBar.Value;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

Step 6
In the MediaElement's MediaOpened event add the following code,it is look like this

 /// <summary>
        /// After media opened start the timer
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MediaPlayer_MediaOpened(object sender, RoutedEventArgs e)
        {
            try
            {
               
                if (MediaPlayer.NaturalDuration.HasTimeSpan)
                {
                    TimeSpan ts = MediaPlayer.NaturalDuration.TimeSpan;
                    SeekBar.Maximum = ts.TotalSeconds;
                }

                Timer.Start();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 7
Play function.
Add the below code in Play Button Click event,it is look like this
  
  /// <summary>
        /// Play the media
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPlay_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                DDMessage.Visibility = System.Windows.Visibility.Collapsed;

                MediaPlayer.Play();

                EnabledDisabledControls(true);

                btnPlay.IsEnabled = false;   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 8
Pause function.
Add the below code in Pause Button Click event,it is look like this

 /// <summary>
        /// Pause the media
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPause_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                MediaPlayer.Pause();

                btnPause.IsEnabled = false;

                btnPlay.IsEnabled = true;   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 9
Stop function.
Add the below code in Stop Button Click event,it is look like this

/// <summary>
        /// Stop the media
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnStop_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                MediaPlayer.Stop();

                EnabledDisabledControls(false);

                btnPlay.IsEnabled = true;   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 10
Move Back function.
Add the below code in Move Back Button Click event,it is look like this

 /// <summary>
        /// Move back
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBackword_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                MediaPlayer.Position -= TimeSpan.FromSeconds(5);     
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 11
Move Forward function.
Add the below code in Move Forward Button Click event,it is look like this

 /// <summary>
        /// move forward
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnForward_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                MediaPlayer.Position += TimeSpan.FromSeconds(5);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Step 12
Seek Bar

Now Add two events to the slider control, events are Thumb.DragStarted and Thumb.DragCompleted in XAML behind.it is look like this

<Slider x:Name="SeekBar" Height="24" Margin="240,0,24,103" VerticalAlignment="Bottom" Style="{DynamicResource SimpleSlider}" FocusVisualStyle="{DynamicResource RadioButtonFocusVisual}" Thumb.DragStarted="SeekBar_DragStarted" Thumb.DragCompleted="SeekBar_DragCompleted" >
                <Slider.Background>
                    <LinearGradientBrush EndPoint="0.5,1" MappingMode="RelativeToBoundingBox" StartPoint="0.5,0">
                        <GradientStop Color="#FF09F5BA" Offset="1"/>
                        <GradientStop Color="#FF07664E"/>
                    </LinearGradientBrush>
                </Slider.Background>
            </Slider>

In the above event handlers add the following code,it is look like this

 #region Drag the Seek Bar
        private void SeekBar_DragStarted(object sender, System.Windows.Controls.Primitives.DragStartedEventArgs e)
        {
            try
            {
                IsSeekBarDragging = true;  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

        private void SeekBar_DragCompleted(object sender, System.Windows.Controls.Primitives.DragCompletedEventArgs e)
        {
            try
            {
                IsSeekBarDragging = false;
                MediaPlayer.Position = TimeSpan.FromSeconds(SeekBar.Value);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }
        #endregion

Step 13
Change the volume of the media,it is look like this

Add the below code in Slider ValueChanged event,it is look like this

   /// <summary>
        ///Change the volume of the media
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Slider_ValueChanged(object sender, RoutedPropertyChangedEventArgs<double> e)
        {
            try
            {
                try
                {
                    MediaPlayer.Volume = (double)VolumeBar.Value;
                }
                catch (Exception)
                { }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 14
FullScreen Mode

Add the below code in Full screen button event,it is used for run the media element in fullscreen mode,it is look like this

  /// <summary>
        /// Switch full screen mode
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFullScreen_Click(object sender, RoutedEventArgs e)
        {
            try
            {

                IsFullscreen = true;  

                LayoutRoot.Children.Remove(MediaPlayer);
               
                this.Background = new SolidColorBrush(Colors.Black);
              
                this.Content = MediaPlayer;
                MediaPlayer.Margin = new Thickness(24, 59, 24, 62);
                
                this.WindowState = WindowState.Maximized;
              


                MediaPlayer.Position = TimeSpan.FromSeconds(CurrentPosition);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Add the below code in window keydown event,it is used for exit fullscreen mode,it is look like this

 /// <summary>
        ///  Exit from full screen mode after press Esc button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Window_KeyDown(object sender, KeyEventArgs e)
        {
            try
            {
                if (IsFullscreen == true)
                {
                    if (e.Key == Key.Escape)
                    {
                        this.Content = LayoutRoot;
                        LayoutRoot.Children.Add(MediaPlayer);
                        MediaPlayer.Margin = new Thickness(24, 59, 24, 189);
                        this.WindowState = WindowState.Normal;

                        MediaPlayer.Position = TimeSpan.FromSeconds(CurrentPosition);

                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 15
Add the below code in MediaEnded event,it is look like this

 /// <summary>
        /// after media ended stop the media player and disabled the controls
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MediaPlayer_MediaEnded(object sender, RoutedEventArgs e)
        {
            try
            {
                MediaPlayer.Stop();

                MediaPlayer.Source = null;

                EnabledDisabledControls(false);

                btnPlay.IsEnabled = false;

                DDMessage.Visibility = System.Windows.Visibility.Visible;
                DDMessage.Text = "Drag and Drop .WMV file";  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Step 16
Close the Application.

Add the below code in Close button event,it is look like this


/// <summary>
        /// Close the application
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnClose_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                System.Windows.Application.Current.Shutdown();    
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

Run the Project.

Download
Download Source Code

Wednesday, November 17, 2010

LINQ - Retrive data from database using stored procedure with LINQ to SQL

In this article i will show you how to call stored procedure to query and retrieve data from the database.

We use stored procedures to encapsulate business logic on the database server side. This gives us the advantage of modularity, performance, and security to name a few. We can use these stored procedures with LINQ to SQL.

LINQ to SQL maps these database-defined abstractions to code-generated client objects, so that you can access them in a strongly typed manner from client code. This gives us the advantage of using Visual Studio's IntelliSense feature to expose them as methods together with the strongly-typed parameters which might be needed to call the stored procedures.

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server

Step 3
Execute the below stored procedure in northwind database.


CREATE PROCEDURE dbo.Sp_CustomerData
AS
    BEGIN
    
        SELECT Customers.CompanyName,Customers.ContactName,
               Customers.City,Customers.Country,Customers.Phone
               
               FROM Customers             
    
    END

Get all customer data from database.

Step 4
Create a Web application and give solution name as SolLinqToSqlStoredProc.

Step 5
Add a gridview on page,it is look like this


 <asp:ScriptManager ID="ScriptManager" runat="server">
        </asp:ScriptManager> 

        <asp:UpdatePanel ID="UpdatePanel" runat="server">
            <ContentTemplate>
                <asp:GridView ID="GvCustomer" runat="server" CellPadding="4" 
                    EnableModelValidation="True" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                </asp:GridView>   
            </ContentTemplate> 
        </asp:UpdatePanel> 

Step 6
Add a ConnectionString in web.config file,it is look like this

<connectionStrings>
        <add name="NorthwindConnectionString" connectionString="Data Source=shree\shree;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Step 7
Add app_code folder in the solution and add a new folder inside the app_code folder and give folder name as ORD,it is look like this



Click on image for better view


Step 8
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this




















Click on image for better view

Step 9
Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

















Click on image for better view


















Click on image for better view

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.


Step 10
To map stored procedure in DataContext class.go to the server explorer,select northwind database,go to the Stored Procedure and select Sp_CustomerData stored procedure ,it is look like this


























Click on image for better view

Drag and drop Sp_CustomerData stored procedure from Server explorer onto the design surface of the O/R Designer,it is look like this


















Click on image for better view

Step 11
Create a Customer static class in app_code folder for retriving a customer data from database,it is look like this


 /// <summary>
    /// Get a Customer Data from Northwind database
    /// </summary>
    /// <returns>List</returns>
    public static List<ORD.Sp_CustomerDataResult> GetCustomerData()
    {
        try
        {
            // Create a object of DataContext and specify the connectionstring in datacontext constructor
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            // Call the mapped Stored procedure
            return DC.Sp_CustomerData().ToList<ORD.Sp_CustomerDataResult>() ;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

Step 12
Bind a customer data in gridview,it is look like this


  protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindData(); 
            }
        }
        catch (Exception)
        { }
    }

    #region Methods
    /// <summary>
    ///  Bind customer data into a gridview
    /// </summary>
    private void BindData()
    {
        try
        {
            List<ORD.Sp_CustomerDataResult> ListCustomer = Customer.GetCustomerData();

            if (ListCustomer != null)
            {
                if (ListCustomer.Count > 0)
                {
                    GvCustomer.DataSource = ListCustomer;
                    GvCustomer.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion


Input Parameter

Stored procedure that returns rows of customers and uses an input parameter to return only those rows that list "London" as the customer city

Execute the below Stored Procedure,it is look like this


CREATE PROCEDURE DBO.Sp_CustomerByCity
(
    @City varchar(50)
)
AS
    BEGIN
            SELECT Customers.CompanyName,Customers.ContactName,
               Customers.City,Customers.Country,Customers.Phone
              
              FROM Customers WHERE Customers.City=@City 
    END
GO

Drag and drop Sp_CustomerByCity stored procedure from Server explorer onto the design surface of the O/R Designer and call map stored procedure in a customer class,it is look like this

/// <summary>
    /// Get a customer Data by city
    /// </summary>
    /// <param name="City">Specify name of the city</param>
    /// <returns>List</returns>
    public static List<ORD.Sp_CustomerByCityResult> GetCustomerByCityData(String City)
    {
        try
        {
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            return DC.Sp_CustomerByCity(City).ToList<ORD.Sp_CustomerByCityResult>();     
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

Bind the data into gridview,it is look like this

 protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
               // BindData(); 
                BindCustomerByCity();
            }
        }
        catch (Exception)
        { }
    }

  /// <summary>
    /// Bind Customer data by city name
    /// </summary>
    private void BindCustomerByCity()
    {
        try
        {
            List<ORD.Sp_CustomerByCityResult> ListCustomer = Customer.GetCustomerByCityData("London");

            if (ListCustomer != null)
            {
                if (ListCustomer.Count > 0)
                {
                    GvCustomer.DataSource = ListCustomer;
                    GvCustomer.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }


Output Parameter
LINQ to SQL maps "out" parameters in stored procedure as reference parameters (ref keyword), and for value types declares the parameter as nullable.

Sp_GetCompanyNameByCustomerID stored procedure which takes a CustomerID as an input parameter, and which returns the company name as an output parameter.

Execute the below stored procedure in database,it is look like this

CREATE PROCEDURE DBO.Sp_GetCompanyNameByCustomerID
(
    @CustomerID nchar(50),
    @CompanyName nvarchar(50) OUTPUT 
)
AS

    BEGIN
            
            SELECT @CompanyName=Customers.CompanyName FROM Customers WHERE Customers.CustomerID=@CustomerID 
            
    END


Drag and drop Sp_GetCompanyNameByCustomerID stored procedure from Server explorer onto the design surface of the O/R Designer and call map stored procedure in a customer class,it is look like this

 /// <summary>
    /// get only company name by customer ID
    /// </summary>
    /// <param name="CustomerID">Specify customer ID</param>
    /// <returns>String</returns>
    public static String GetCompanyName(String CustomerID)
    {
        String CompanyName = String.Empty; 
        try
        {
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            DC.Sp_GetCompanyNameByCustomerID(CustomerID, ref CompanyName);

            return CompanyName; 
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

Write a Company Name

Response.Write("Company Name - "+Customer.GetCompanyName("FRANK")); 


Run the project.

Full Code


1 .aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager" runat="server">
        </asp:ScriptManager> 

        <asp:UpdatePanel ID="UpdatePanel" runat="server">
            <ContentTemplate>
                <asp:GridView ID="GvCustomer" runat="server" CellPadding="4" 
                    EnableModelValidation="True" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                </asp:GridView>   
            </ContentTemplate> 
        </asp:UpdatePanel> 
    </div>
    </form>
</body>
</html>

2. Customer Static Class

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

public static class Customer
{
    #region Methods

    /// <summary>
    /// Get a Customer Data from Northwind database
    /// </summary>
    /// <returns>List</returns>
    public static List<ORD.Sp_CustomerDataResult> GetCustomerData()
    {
        try
        {
            // Create a object of DataContext and specify the connectionstring in datacontext constructor
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            // Call the mapped Stored procedure
            return DC.Sp_CustomerData().ToList<ORD.Sp_CustomerDataResult>() ;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    /// <summary>
    /// Get a customer Data by city
    /// </summary>
    /// <param name="City">Specify name of the city</param>
    /// <returns>List</returns>
    public static List<ORD.Sp_CustomerByCityResult> GetCustomerByCityData(String City)
    {
        try
        {
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            return DC.Sp_CustomerByCity(City).ToList<ORD.Sp_CustomerByCityResult>();     
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    /// <summary>
    /// get only company name by customer ID
    /// </summary>
    /// <param name="CustomerID">Specify customer ID</param>
    /// <returns>String</returns>
    public static String GetCompanyName(String CustomerID)
    {
        String CompanyName = String.Empty; 
        try
        {
            ORD.NorthwindDataContext DC = new ORD.NorthwindDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

            DC.Sp_GetCompanyNameByCustomerID(CustomerID, ref CompanyName);

            return CompanyName; 
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
}

3. .aspx.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
               // BindData(); 
               // BindCustomerByCity();

               Response.Write("Company Name - "+Customer.GetCompanyName("FRANK"));   
            }
        }
        catch (Exception)
        { }
    }

    #region Methods
    /// <summary>
    ///  Bind customer data into a gridview
    /// </summary>
    private void BindData()
    {
        try
        {
            List<ORD.Sp_CustomerDataResult> ListCustomer = Customer.GetCustomerData();

            if (ListCustomer != null)
            {
                if (ListCustomer.Count > 0)
                {
                    GvCustomer.DataSource = ListCustomer;
                    GvCustomer.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    /// <summary>
    /// Bind Customer data by city name
    /// </summary>
    private void BindCustomerByCity()
    {
        try
        {
            List<ORD.Sp_CustomerByCityResult> ListCustomer = Customer.GetCustomerByCityData("London");

            if (ListCustomer != null)
            {
                if (ListCustomer.Count > 0)
                {
                    GvCustomer.DataSource = ListCustomer;
                    GvCustomer.DataBind();  
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }



    #endregion
}


Download
Download Source Code