Hot News!

Not Available

Click Like or share to support us!

Sep 14, 2015

Advanced Sql Selecting Rows With Max Value From Groups

SQL is never hard but definitely tedious. You know what I mean.
Occasionally I tend to create a relatively complicated query instead of fetching the data back and do the processing in my code. Sometimes for the performance, and sometimes just because I like challenges.
I know, I never am a SQL expert. But anyway, it might help.
Say we have a table like the following:
DepartmentEmployeeSalary
AJohn1000
AMichael1200
ABob880
BTed2000
BJane2000
CAlly2000
CKent4000
And we want to get a query of all employees having the highest pay from each department. What SQL query should we have?
Well, first we need to find what the highest pay of each department is:
SELECT 
  DEP.DEPARTMENT, MAX(DEP.SALARY) 
FROM 
  DEPARTMENT DEP 
GROUP BY 
  DEP.DEPARTMENT
This will give you a list of department with its highest pay. But we do want to fetch the complete row so that we could know who that employee is. So how do we proceed from here?
We need sub query and EXISTS predicate:
SELECT * 
FROM 
  DEPARTMENT 
WHERE 
  EXISTS (
    SELECT 
      DEP.DEPARTMENT 
    FROM 
      DEPARTMENT DEP 
    GROUP BY 
      DEP.DEPARTMENT 
    HAVING 
      DEPARTMENT.DEPARTMENT = DEP.DEPARTMENT AND 
      DEPARTMENT.SALARY = MAX(DEP.SALARY)
  )
The result looks like this:
DepartmentEmployeeSalary
AMichael1200
BTed2000
BJane2000
CKent4000
The speed of this query doesn’t seem very bad. In fact, it performs far better than I originally thought since it involves sub query.

source:https://bryantsai.com/advanced-sql-selecting-rows-with-max-value-from-groups/

Aug 27, 2015

MS SQL Server with Khmer Unicode

MS SQL Server is a product of Microsoft that we use it for store data, information and we can easy find that data when you need. MS SQL Server is the database application for manage data or any information of organization. If we compare with MS Access we can say MS SQL Server is better because MS SQL Server have more features, tools, function than MS Access in manage data.  Today I have one topic to share you about how to make MS SQL Server support with Khmer Unicode.
This is the topic that shows you about how to insert, update, delete data in Khmer to table and how to search data as Khmer language. It is the nice topic that can help who that starting build application with Khmer Unicode

1. How can we insert Khmer Unicode to table?
You need to configure something when you create table. If you want your table support with Khmer Unicode please choose datatype is nvarchar and in collation properties Khmer_100. Please create tblStudents(ID,Name,Sex) and set datatype below:


In field Name properties please set Collation to Khmer_100


2. After you create table you need to know the query that can input Khmer Unicode to table also.

             insert into tblStudents(ID,Name,Sex)
             values('1',N'សុវណ្ណ','M')

“N” character is the special string that allow input Khmer Unicode to field of table. Please run this script and see the result in table.


       3. How to Search with Khmer Unicode

           We need still use “N” when we search data in tblStudents. Please see example below:

       Select * from tblStudents
       where Name='សុវណ្ណ'

          if we write this select we will now have data.


            Then try to change to this scripts

      Select * from tblStudents
      where Name=N'សុវណ្ណ'

              Result:


You also can use “Like” to select with Khmer Unicode too.


This topic is very useful to help you to build application by using Khmer Unicode. And I hope it can help you to build application. If you have any issue please command I will to support you.

(source: http://kh-code.blogspot.com)

Jun 27, 2015

RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net

In this article I will explain a simple tutorial with an example and sample code to create RDLC Report in Windows Forms (WinForms) application using C# and VB.Net.
The RDLC Report in in Windows Forms (WinForms) application will be populated using Typed DataSet.
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
 
 
1. Add Typed DataSet to the Project
Since I am using disconnected RDLC Reports we will make use of Typed DataSet to populate the RDLC Reports with data from database.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
2. Adding DataTable to the Typed DataSet
Our next step would be to add a DataTable to the Type DataSet.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
3. Adding Columns or fields to DataTable
In the DataTable we need to specify the column names that we want to display in the RDLC Report.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
Note: The Column Names of the DataTable must exactly match with the actual Database Table column names.
 
By default all the columns are of String Data Type but you can also change the data type as per your need.
 
 
4. Adding the RDLC Report
Using the Add New Item option in Visual Studio you need to add new RDLC Report. I am making use of Report Wizard so that it make easier to configure the Report.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
5. Choose the DataSet
Now we need to choose the DataSet that will act as the DataSource for the RDLC Report. Thus we need to select the Customers DataSet that we have created earlier.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
6. Choose the Fields to be displayed in the RDLC Report
Next we need to choose the fields that we need to display, we need to simply drag and drop each fields into the Values Box as shown in the screenshot below.
 
 
7. Choose the Layout
The next dialog will ask us to choose the layout, we can simply skip it as of now as this is a simple Report with no calculations involved.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
8. Choose the Style
Finally we need to choose the style, i.e. color and theme of the Report.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
Once you press Finish button on the above step, the Report is ready and is displayed in the Visual Studio as shown below.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
9. Adding Report Viewer to the Form
In order to display the Report we will need to add ReportViewer control to the Form from the Toolbox.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
Once you add the ReportViewer control to the Form, your Form must look as below. You will need to click on the small arrow present on the top right corner of the Report Viewer and choose the RDLC Report as shown below.
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net
 
 
10. Populating the RDLC Report from Database
Below is the code to populate the RDLC Report from database. The Customers DataSet is populated with records from the Customers Table and is set as ReportSource to the Report.
C#
Namespaces
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WinForms;
 
Code
private void Form1_Load(object sender, EventArgs e)
{
    Customers dsCustomers = GetData();
    ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
    this.reportViewer1.LocalReport.DataSources.Clear();
    this.reportViewer1.LocalReport.DataSources.Add(datasource);
    this.reportViewer1.RefreshReport();
}
 
private Customers GetData()
{
    string constr = @"Data Source=.\Sql2005;Initial Catalog=Northwind;Integrated Security = true";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 20 * FROM customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (Customers dsCustomers = new Customers())
                {
                    sda.Fill(dsCustomers, "DataTable1");
                    return dsCustomers;
                }
            }
        }
    }
}
 
VB.Net
Namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms
 
Code
Private Sub Form1_Load(sender As System.Object, e As System.EventArgsHandles MyBase.Load
    Dim dsCustomers As Customers = GetData()
    Dim datasource As New ReportDataSource("Customers", dsCustomers.Tables(0))
    Me.ReportViewer1.LocalReport.DataSources.Clear()
    Me.ReportViewer1.LocalReport.DataSources.Add(datasource)
    Me.ReportViewer1.RefreshReport()
End Sub
 
Private Function GetData() As Customers
    Dim constr As String = "Data Source=.\Sql2005;Initial Catalog=Northwind;Integrated Security = true"
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT TOP 20 * FROM customers")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dsCustomers As New Customers()
                    sda.Fill(dsCustomers, "DataTable1")
                    Return dsCustomers
                End Using
            End Using
        End Using
    End Using
End Function
 
RDLC Report in Windows Forms (WinForms) Application using C# and VB.Net

Source:
http://www.aspsnippets.com/Articles/RDLC-Report-in-Windows-Forms-WinForms-Application-using-C-and-VBNet.aspx

Jun 20, 2015

Save and Retrieve Image from a SQL Server Database using VB.NET

How to save and retrieve an image from a SQL Server database in VB.NET.
  • Download source - 4.62 MB
  • Sample Image

    Introduction

    This article is about storing and retrieving images from a SQL Server database using VB.NET. When we create an application where we need to save images then we save images in a folder and store the path of the image in the database as string type.
    • If you save an image to a folder, you might accidentally delete the image from that folder. If this happens, you will get an error when retrieving the image. It is very difficult to handle these accidents.
    • So if you save an image into a database, you can enforce security by using the security settings of the database.

    The application

    Create a Windows application in VB.NET 2005 and design it as show in the above image. Then import namespaces as follows:
    Imports System.Data.SqlClient
    Imports System.IO

    Create the database

    Create a SQL Server database as follows. In Solution Explorer, click on project name and right click on it, then Add -> New item -> SQL,dDatabase name "Database1.mdf", then OK. Click on database1 and create a table in it namedinformation with fields as follows:
    Field NameField Type
    namenvarchar(50)
    photoImage

    Using the code

    Actually the IMAGE field is just holding a reference to the page containing the binary data so we have to convert our image into bytes.
    Imports System.Data.SqlClient
    Imports System.IO
    
        Public Class Form1
        'path variable use for Get application running path
        Dim path As String = (Microsoft.VisualBasic.Left(Application.StartupPath, Len(Application.StartupPath) - 9))
        Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
                path & "Database1.mdf;Integrated Security=True;User Instance=True")
        Dim cmd As SqlCommand
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
           If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
                Label1.Visible = True
                TextBox1.Visible = True
                Label1.Text = "Name"
                TextBox1.Clear()
            End If
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the
            ' 'Database1DataSet.Information' table. You can move, or remove it, as needed.
            Me.InformationTableAdapter.Fill(Me.Database1DataSet.Information)
            con.Open()
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles Button2.Click
            If TextBox1.Text = "" Then
                MsgBox("Fill the Name Field")
            Else
                Dim sql As String = "INSERT INTO Information VALUES(@name,@photo)"
                Dim cmd As New SqlCommand(sql, con)
                cmd.Parameters.AddWithValue("@name", TextBox1.Text)
                Dim ms As New MemoryStream()
                PictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)
                Dim data As Byte() = ms.GetBuffer()
                Dim p As New SqlParameter("@photo", SqlDbType.Image)
                p.Value = data
                cmd.Parameters.Add(p)
                cmd.ExecuteNonQuery()
                MessageBox.Show("Name & Image has been saved", "Save", MessageBoxButtons.OK)
                Label1.Visible = False
                TextBox1.Visible = False
            End If
        End Sub
        Private Sub Button3_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button3.Click
            GroupBox2.BringToFront()
            GroupBox2.Visible = True
            Label1.Visible = False
            TextBox1.Visible = False
        End Sub
    
        Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As _
                    System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
            cmd = New SqlCommand("select photo from Information where name='" & _
                      DataGridView1.CurrentRow.Cells(0).Value() & "'", con)
            Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
            If Not imageData Is Nothing Then
                Using ms As New MemoryStream(imageData, 0, imageData.Length)
                    ms.Write(imageData, 0, imageData.Length)
                    PictureBox1.BackgroundImage = Image.FromStream(ms, True)
                End Using
            End If
            GroupBox2.SendToBack()
            GroupBox2.Visible = False
            Label1.Visible = True
            Label1.Text = DataGridView1.CurrentRow.Cells(0).Value()
        End Sub
    End Class
    Retrieving images from the database is the exact reverse process of saving images to the database. The following code is used for retrieval.
    Sample Image - maximum width is 600 pixels
    The application uploads images from the database and displays it in a DataGridView. When you click on adatagridview cell then an image is displayed in the picture box.
    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, _
                   ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
                   Handles DataGridView1.CellMouseClick
        cmd = New SqlCommand("select photo from Information where name='" & _
                  DataGridView1.CurrentRow.Cells(0).Value() & "'", con)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
                PictureBox1.BackgroundImage = Image.FromStream(ms, True)
            End Using
        End If
        GroupBox2.SendToBack()
        GroupBox2.Visible = False
        Label1.Visible = True
        Label1.Text = DataGridView1.CurrentRow.Cells(0).Value()
    End Sub

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Share