Convert Comma-Separated Strings to String Arrays

String.Split Method

The Split method requires a parameter containing a character array. Each item in the array is a potential delimiting character. Handily, the parameter is a Parameter Array. This allows multiple characters to be included directly in the method without initialising the array beforehand.

The following code splits a comma-separated list:

string fruit = "Apple,Banana,Orange,Strawberry";

string[] split = fruit.Split(',');

foreach (string item in split)

{

    Console.WriteLine(item);

}

     

/* OUTPUT

Apple

Banana

Orange

Strawberry

*/

The following example is similar but this time the delimiting characters are the bar (|) and the comma. Both cause the generation of an additional result in the array.

string fruit = "Apple|Banana,Orange|Strawberry";

string[] split = fruit.Split('|', ',');

foreach (string item in split)

{

    Console.WriteLine(item);

}

/* OUTPUT

Apple

Banana

Orange

Strawberry

*/

How to add Values from Data Grid view to Combo box

How to add Values from Data Grid view to Combo box

 DataTable dt =new DataTable();

            //DataTable dt = new DataTable();
            dt.Columns.Add("ExiseINVNo", typeof(String));
            //dt.Columns.Add("Money", typeof(String));
            dt.Rows.Add(new object[] { "      <--Select-->   "});
            //dt.Rows.Add(new object[] { "Ki", 30 });


            foreach (DataGridViewRow _dgr in dgvSalesInvItems.Rows)
            {
                if (_dgr.Cells["ExciseInvoice_No"].Value != null)
                {
                    string val = _dgr.Cells["ExciseInvoice_No"].Value.ToString();

                    dt.Rows.Add(new object[] { val});
                }

            }
            //DataTable Dt1 = Masters.RemoveDuplicateRows(dt, "ExiseINVNo");
            //DataRow dr = dt.NewRow();
            //dr["Exise"] = "< -- Select -- >";
            //DSItems.Tables[0].Rows.InsertAt(dr, 0);
            cmbExciseInvNo.DataSource = Dt1;
            cmbExciseInvNo.DisplayMember = "ExiseINVNo";

            cmbExciseInvNo.SelectedIndex = 0;

Best way to remove duplicate entries from a data table

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
{
   Hashtable hTable = new Hashtable();
   ArrayList duplicateList = new ArrayList();

   //Add list of all the unique item value to hashtable, which stores combination of key, value pair.
   //And add duplicate item value in arraylist.
   foreach (DataRow drow in dTable.Rows)
   {
      if (hTable.Contains(drow[colName]))
         duplicateList.Add(drow);
      else
         hTable.Add(drow[colName], string.Empty); 
   }

   //Removing a list of duplicate items from datatable.
   foreach (DataRow dRow in duplicateList)
      dTable.Rows.Remove(dRow);

   //Datatable which contains unique records will be return as output.
      return dTable;
}

Convert Indian Currency Format in SQL server

Convert Indian Currency Format in SQL server

Use this Function It will convert and returns the Indian currency format

-- local settings may produce this for you
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
alter FUNCTION dbo.IndianDecimalFormat
(
@Currency decimal(11,2)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @tCurr varchar(12)
,@RetVal varchar(16)

SELECT @tCurr = CAST(@Currency AS varchar(12))
,@RetVal = ''

;WITH IndianCurrency (pos, ln, ord)
AS
(
SELECT 1, 2, 5
UNION ALL SELECT 4, 3, 4
UNION ALL SELECT 7, 2, 3
UNION ALL SELECT 9, 2, 2
UNION ALL SELECT 11, 2, 1
), Split (Number, ord)
AS
(
SELECT REVERSE(SUBSTRING(REVERSE(@tCurr), pos, ln))
,ord
FROM IndianCurrency
)
SELECT @RetVal = @RetVal + Number + CASE WHEN ord = 4 THEN '.' ELSE ',' END
FROM Split
WHERE LEN(Number) > 0
ORDER BY ord

RETURN LEFT(@RetVal, LEN(@RetVal) - 1)
END
GO

--select dbo.IndianDecimalFormat(454121)

O/P: 4,54,121.00

Export Excel From Data Table

protected void ExportExcel(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return;

            var xlApp = new Excel.Application();
            //Is this used?
            CultureInfo CurrentCI = Thread.CurrentThread.CurrentCulture;

            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            Excel.Workbooks workbooks = xlApp.Workbooks;

            Excel.Range range;

            Excel.Workbook workbook = workbooks.Add();
            Excel.Worksheet worksheet = workbook.Worksheets[1];

            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;

            //xlApp.worksheet.Properties.Title = @"This co";
            // worksheet.Cells[1, 3] = "BANAVATHY POWER SYSTEMS PVT. LTD.";
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

                range = worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
                range.Borders.Color = Color.Green;




            }

            for (var r = 0; r < dt.Rows.Count; r++)
            {

                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                }

                rowRead++;

                //is this used?
                percent = ((float)(100 * rowRead)) / totalCount;
            }

            Microsoft.Office.Interop.Excel.Range columns = worksheet.UsedRange.Columns;
            columns.AutoFit();
            worksheet.Activate();
            worksheet.Application.ActiveWindow.SplitRow = 1;
            worksheet.Application.ActiveWindow.FreezePanes = true;
            // Now apply autofilter
            Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
            firstRow.Activate();
            firstRow.Select();
            //firstRow.AutoFilter(1,
            //                    Type.Missing,
            //                    Excel.XlAutoFilterOperator.xlAnd,
            //                    Type.Missing,
            //                    true);
            //worksheet.Activate();
            //worksheet.Application.ActiveWindow.SplitColumn = 1;
            //worksheet.Application.ActiveWindow.FreezePanes = true;

            //worksheet.Rows[1].Insert();
            //Excel.Range newRow = worksheet.Rows[1];
            //Excel.Range newCell = newRow.Cells[9];
            //newCell.MergeCells = true;

            //newCell.Value2 = "BANAVATHY POWER SYSTEMS PVT. LTD:Sales Invoice";
            //newCell.Value2="Item Wise Sales Report";
           // newCell.Font.Bold = true;
          //  newCell.Font.Color = Color.Green;
            //newCell.Value = DateTime.Now.ToString("yyyy-MM-dd");

            dt.Clear();
            xlApp.Visible = true;
        }

Data View row filter using data table

Data View row filter using data table

I have the following code which doesn't seem to work. In the Button_Click event I populate the datatble and display the results in a grid view.


SqlCommand cmd = new SqlCommand("Select inv_No,inv_Date,Project  from Invoice_Master", con);
            
SqlDataAdapter adp = new SqlDataAdapter(cmd);           
DataTable dtserch = new DataTable();        
adp.Fill(dtserch);         
DataView dv = dtserch.DefaultView;

//Inv_No filter
dv.RowFilter = "inv_No='" + CmbSSalesInvNo.Text + "'";



//Date Filter

//convert date format as server date format

if (txtFromDate != "" && txtToDate.Text != "")
   {
                fromdate = Convert.ToDateTime(txtFromDate .Text).ToString("MM-dd-yyyy");
                Todate = Convert.ToDateTime(txtToDate.Text).ToString("MM-dd-yyyy");
    }

dv.RowFilter = "ServiceInvoiceDate >= #" + fromdate + "# and ServiceInvoiceDate <= #" + Todate + "#";


//Filter data bind to gridview

DgvAllSalesInvoices.DataSource = dv;

Row grouping in DataGridView using winforms

Here is a custom DataGridView that you add to your project, compile, add the DataGridView to a form, set the DataSource of the DataGridView to say a DataTable with repeating data, build, run, should look like the image below as a sample.

Source

''' <summary>
''' Original author
''' http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/a44622c0-74e1-463b-97b9-27b87513747e#faq8
''' </summary>
''' <remarks>
''' Original code was in C Sharp, I converted and tweaked some code
''' which did not compile under VB.NET
''' </remarks>
Public Class GroupByGrid
    Inherits DataGridView
    Protected Overrides Sub OnCellFormatting(ByVal args As DataGridViewCellFormattingEventArgs)
        MyBase.OnCellFormatting(args)
        ' First row always displays
        If args.RowIndex = 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.Value = String.Empty
            args.FormattingApplied = True
        End If
    End Sub
    Private Function IsRepeatedCellValue(ByVal rowIndex As Integer, ByVal colIndex As Integer) As Boolean
        Dim currCell As DataGridViewCell = Rows(rowIndex).Cells(colIndex)
        Dim prevCell As DataGridViewCell = Rows(rowIndex - 1).Cells(colIndex)
        If (currCell.Value Is prevCell.Value) OrElse (currCell.Value IsNot Nothing AndAlso prevCell.Value IsNot Nothing AndAlso currCell.Value.ToString() = prevCell.Value.ToString()) Then
            Return True
        Else
            Return False
        End If
    End Function
    Protected Overrides Sub OnCellPainting(ByVal args As DataGridViewCellPaintingEventArgs)
        MyBase.OnCellPainting(args)
        args.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
        ' Ignore column and row headers and first row
        If args.RowIndex < 1 OrElse args.ColumnIndex < 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
        Else
            args.AdvancedBorderStyle.Top = AdvancedCellBorderStyle.Top
        End If
    End Sub
End Class

Serial Number to be given with grouping in Report Viewer

Serial Number to be given with grouping in Report Viewer

We could achieve this requirement in RDLC report, please follow these detailed steps to make it:

1. Right-click the whole matrix  control, and then select Properties.
2. Switch to Groups tab, click Add button to add another Row group, in the expression textbox, please select groupedcolumn1 datafield(This is very important)
3.  Then click the new row group, then click Up button to move it to the top1 location.
4. Click OK
5. Switch to the report’s design surface, then type in the expression =Runningvalue(Fields!GroupColumn1Name.Value,countdistinct,"MatrixName") in the textbox created in step3.

Note: In the expression, Please make sure the datafield name in the group1(Fields!GroupColumn1Name) and Matrix name(MatrixName) is correct.

Preview the report, you will get another column in left to display 1, 2, 3, 4, ……

ex:  =Runningvalue(Fields!InvoiceNo.Value,countdistinct,"Tablix1")

OR

=RunningValue(Fields!GroupName.Value, countdistinct,Nothing)