Wednesday, December 26, 2012

Selecting Record to using cursor in stored procedure

Following is an example using cursor to fetch record from table and performing action on each row using while loop, also used try catch in this stored procedure


ALTER PROCEDURE [dbo].[Test_Procedure_By_Khalid]
(
      -- Add the parameters for the stored procedure here
      @TableName nvarchar(255),
      @AreaName varchar(50),
      @SysDate DateTime,
      @ReturnCode INT   OUTPUT
)
AS
     
      DECLARE @GraphID NVARCHAR(50)
      DECLARE @PERIOD INT
      DECLARE @MODELKEY NVARCHAR(50)
      DECLARE @ORIGNALDATE NVARCHAR(50)
      DECLARE @DATE INT
      DECLARE @QTY INT
      DECLARE @TOTALQTY INT
      DECLARE @MODELKEYTYPE INT
      DECLARE @SCREENID NVARCHAR(50)
      DECLARE @RetailModelClassificationName NVARCHAR(50)
      DECLARE @SELECTEDAREA varchar(50)
      DECLARE @INF_Cursor INT
      DECLARE @THEMONTH NVARCHAR(6)
      DECLARE @LASTMONTH NVARCHAR(6)
      DECLARE @LASTYEARMONTH NVARCHAR(6)

      --Cursor Definition
      DECLARE     @Cur_TableParm    CURSOR
     
BEGIN TRY
      ----------------------------
      -- Start Transaction --
      ----------------------------
      BEGIN TRANSACTION
-- Set Cursor to select data from Table CPT_TableParm
      SET @Cur_TableParm = CURSOR FAST_FORWARD FOR
SELECT GraphID,ModelKey,AreaName,ModelClassificationName,ScreenID FROM CPT_TableParm
      WHERE TableName = @TableName
      AND ActFcType = 1
     
      -- Executing Cursor to select data from Table CPT_TableParm
      OPEN  @Cur_TableParm
      FETCH NEXT FROM @Cur_TableParm  INTO
             @GraphID, @MODELKEY,@SELECTEDAREA,
 @RetailModelClassificationName,@SCREENID
     
      -- Looping through cursor result   
      WHILE  @@FETCH_STATUS = 0
        BEGIN
           
Print  @SELECTEDAREA+','+cast(@PERIOD as varchar)+'~'+@MODELKEY+'~'+@ORIGNALDATE+'~'+cast(@DATE as varchar)+'~'+cast(@QTY as varchar)+'~'+
                                          cast(@TOTALQTY as varchar)+'~'+@GraphID+'~''3''~'+@SCREENID
                              SET @ReturnCode = 2

        FETCH NEXT FROM @Cur_TableParm  INTO
                                    @GraphID, @MODELKEY,@SELECTEDAREA,
@RetailModelClassificationName,@SCREENID
       END
      -- Closing @Cur_TableParm to select data from CPT_TableParm
      CLOSE       @Cur_TableParm
      DEALLOCATE  @Cur_TableParm

END TRY

BEGIN CATCH
-- Rollback Transaction --
      ROLLBACK TRANSACTION
      -- Reset Cursor State
      BEGIN
            SET @INF_Cursor = CURSOR_STATUS('global','@Cur_TableParm')
            IF @INF_Cursor = -1
            BEGIN
                  DEALLOCATE  @Cur_TableParm    -- Delete Cursor
            END
            ELSE IF @INF_Cursor <> -3
                  BEGIN
                        CLOSE       @Cur_TableParm    -- Close Cursor
                        DEALLOCATE  @Cur_TableParm    -- Delete Cursor
                  END

            PRINT       ERROR_MESSAGE()
      END
      RETURN 999
END CATCH

Tuesday, December 4, 2012

Very Simple Data Access Layer for Three Tire Application

Below code is for very simple Data Access Layer that can be use in three tier application, it also includes the Method used in with Transaction




using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLayer
{
    public static class DAcess
    {
        static string constr = "";
        static SqlCommand sqlcom = new SqlCommand();
        static SqlConnection sqlcon = new SqlConnection();
        static SqlDataAdapter sqlda;
        static SqlTransaction SqlTran;
       
        public static void ConnectToDatabase()
        {
            if (sqlcon.State == ConnectionState.Closed)
            {
                constr = ConfigurationManager.ConnectionStrings["ZaaSConString"].ToString();
                sqlcon = new SqlConnection(constr);
                sqlcon.Open();
            }
        }
        public static void DisconnectToDataBase()
        {
            if(sqlcon.State == ConnectionState.Open)
            sqlcon.Close();
        }

        public static DataTable returnDataTable(string Query)
        {
            DataTable dtResult = new DataTable("ResultTable");
            try
            {
                ConnectToDatabase();
                sqlda = new SqlDataAdapter(Query, sqlcon);
                sqlda.Fill(dtResult);
            }
            catch (Exception ex)
            {
            }
            finally
            {
            }
            return dtResult;
        }
        public static DataTable returnDataTableSpecial(string Query)
        {
            DataTable dtResult = new DataTable("ResultTable");
            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon, SqlTran);
                sqlda = new SqlDataAdapter(sqlcom);
                sqlda.Fill(dtResult);
            }
            catch (Exception ex)
            {
            }
            finally
            {
            }
            return dtResult;
        }

        public static int ExecuteQuery(string Query)
        {
            int Result = 0;

            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon);
                Result = sqlcom.ExecuteNonQuery();
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                sqlcon.Close();
                throw ex;
            }
            finally
            {
                sqlcon.Close();
            }

           
            return Result;
        }
        public static int NewExecuteQuery(string Query)
        {
            int Result = 0;
            sqlcom = new SqlCommand(Query, sqlcon, SqlTran);
            Result = sqlcom.ExecuteNonQuery();
            return Result;
        }
        public static void StartTransaction()
        {
            SqlTran = sqlcon.BeginTransaction(IsolationLevel.ReadUncommitted);
        }
        public static void CommitTransaction()
        {
            SqlTran.Commit();
        }
        public static void RollBackTransaction()
        {
            SqlTran.Rollback();
        }
        public static void CloseTransaction()
        {
            SqlTran.Dispose();
        }
       
       
        public static string returnScalar(string Query)
        {
            string ResultString = "";

            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon);
                ResultString = Convert.ToString(sqlcom.ExecuteScalar());
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                sqlcon.Close();
                throw ex;
            }
            finally
            {
                sqlcon.Close();
            }


            return ResultString;
        }
        public static string NewreturnScalar(string Query)
        {
            string ResultString = "";
            sqlcom = new SqlCommand(Query, sqlcon,SqlTran);
            ResultString = Convert.ToString(sqlcom.ExecuteScalar());
            return ResultString;
        }


    }
}


Wednesday, November 21, 2012

File Attachment and saving on serving in Silverlight, Using Relay Command in MVVM Structure

Add Relay Command in View Model


///
/// Rlc Command for Attach Command
///
public RelayCommand rlcAttach
{
  get
  {
     return new RelayCommand(AttachCommand);

   }
}

Property for FilesList to upload
///
/// Property for List of Files To upload
///
public List<FileInfo> FilesToUpload
{
     get { return lstFilesToUpload; }
     set
     {
                lstFilesToUpload = value;
                RaisePropertyChanged("FilesToUpload");
     }
}


Method getting file information in List FilesToUpload
///
/// Method for go to Attach Command
///
protected void AttachCommand()
{
try
      {
                  OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Multiselect = true;
            //.doc, .xls, .csv, .txt
            fileDialog.Filter = "Word 2003 Format (*.doc)|*.doc|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|Excel Files (*.xlsx)|*.xlsx|Excel Files 2003 Format(*.xls)|*.xls|Word Files(*.docx)|*.docx|All Files(*.*)|*.*";
            if (fileDialog.ShowDialog() == true)
            {
                    FilesToUpload = fileDialog.Files.ToList();
            }
      }
      catch (Exception ex)
      {
         //MessageBox.Show(ex.Message);
      }

          
}


Finally Method To send Files over the server using file handler in Web Project.
///
/// Method for go to Save Command
///
protected void SaveCommand()
{
    try
    {
      foreach (FileInfo file in FilesToUpload)
      {
          //Define the Url object for the Handler
          String ApplicationURL =    System.Windows.Browser.HtmlPage.Document.DocumentUri.ToString();
          String UploderHandlerURI = ApplicationURL.Substring(0, ApplicationURL.LastIndexOf('/') + 1) + "UploadFileHandler.ashx";
          UriBuilder handlerUrl = new UriBuilder(UploderHandlerURI);
          //Set the QueryString
          handlerUrl.Query = "InputFile=" + file.Name;
          FileStream FsInputFile = file.OpenRead();
          //Define the WebClient for Uploading the Data
          WebClient webClient = new WebClient();
          //An async class for writing the file to the server
          webClient.OpenWriteCompleted += (s, evt) =>
          {
               UploadFileData(FsInputFile, evt.Result);
               evt.Result.Close();
               FsInputFile.Close();
          };
          webClient.OpenWriteAsync(handlerUrl.Uri);
          Attachments.Add(file.Name);
         }
       }
       catch (Exception)
      {
                //MessageBox.Show(ex.Message);
      }
}

Supporting Method UploadFileData in above lines....
///
/// The Below Method read the data from the input file stream
/// and write into the out stream
///
///
///
private void UploadFileData(Stream inputFile, Stream resultFile)
{
    byte[] fileData = new byte[4096];
    int fileDataToRead;
while ((fileDataToRead =inputFile.Read(fileData, 0, fileData.Length)) != 0)
   {
                resultFile.Write(fileData, 0, fileDataToRead);
   }
}



File Handler in Web Project...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;

namespace DistributionExpress.Web
{
    ///
    /// Summary description for UploadFileHandler
    ///
    public class UploadFileHandler : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            string filename = context.Request.QueryString["InputFile"].ToString();

            using (FileStream fileStream = File.Create(context.Server.MapPath("~/FilesServer/" + filename)))
            {
                byte[] bufferData = new byte[4096];
                int bytesToBeRead;
                while ((bytesToBeRead = context.Request.InputStream.Read(bufferData, 0, bufferData.Length)) != 0)
                {
                    fileStream.Write(bufferData, 0, bytesToBeRead);
                }
                fileStream.Close();
            }
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}




Allowing only Decimal values in Text Box Csharp


You can handle this in keyPress event of Text Box as in following way....


private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{
      if (!char.IsControl(e.KeyChar)
                    && !char.IsDigit(e.KeyChar)
                    && e.KeyChar != '.')
      {
               e.Handled = true;
      }

            // only allow one decimal point
     if (e.KeyChar == '.'
                && (sender as TextBox).Text.IndexOf('.') > -1)
     {
               e.Handled = true;
     }
}

Tuesday, September 18, 2012

Handling xaml not found error while navigating to non-existing xaml form

In some cases in your silverlight application the link is present for non-existing or old xaml view form. which does not exist in actual or removed after any updation. In this case while nevigating to this form silverlight runtime exception will occur. To handle this exception you need to add NavigationFailed event for the frame.

In Xaml:

<sdk:Frame  Height="175" HorizontalAlignment="Left" Margin="48,92,0,0" Name="frame1" VerticalAlignment="Top" Width="293" BorderBrush="#006C1313" NavigationFailed="frame1_NavigationFailed" />

In Xaml.cs


private void frame1_NavigationFailed(object sender, NavigationFailedEventArgs e)
{
            e.Handled = true;
            this.frame1.Navigate(new Uri("/Testview/ErrorPage.xaml", UriKind.Relative));
}

How to navigate the frame to another view in another silver-light project under same solution

You can navigate from one view to another view in different silver-light project under same solution by using the following code.



private void button1_Click(object sender, RoutedEventArgs 
{



this.frame1.Navigate(new Uri("/MyNewSDXProject;component/TestViews/MyTestUC.xaml", UriKind.Relative));
}


Xaml will look like this:


<UserControl x:Class="SilverlighProjectTestingApp.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:navigation="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation"
  
    mc:Ignorable="d"
    d:DesignHeight="300" d:DesignWidth="400" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk">

    <Grid x:Name="LayoutRoot" Background="White">
        <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="48,47,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="button1_Click" />
        <sdk:Frame  Height="175" HorizontalAlignment="Left" Margin="48,92,0,0" Name="frame1" VerticalAlignment="Top" Width="293" BorderBrush="#006C1313" NavigationFailed="frame1_NavigationFailed" />
    </Grid>
</UserControl>