Converting datatable to CSV file using C#

This method is used to converting datatable to CSV file .

SaveDataTableToCsvFile(@”c:\temp\test111.csv”, dtSample);

SaveDataTableToCsvFile(@”c:\temp\test222.csv”, dtSample, “;”);

/// <summary>
  /// Creates a CSV file
  /// </summary>
  /// <param name=”AbsolutePathAndFileName”></param>
  /// <param name=”TheDataTable”></param>
  /// <param name=”Options”>[0] = separator, e.g. “;” (default = “,”)</param>
  public static void SaveDataTableToCsvFile(string AbsolutePathAndFileName, DataTable TheDataTable, params string[] Options)
  {
   //variables
   string separator;
   if (Options.Length > 0)
   {
    separator = Options[0];
   }
   else
   {
    separator = “,”; //default
   }
   string quote = “\”";

   //create CSV file
   StreamWriter sw = new StreamWriter(AbsolutePathAndFileName);

   //write header line
   int iColCount = TheDataTable.Columns.Count;
   for (int i = 0; i < iColCount; i++)
   {
    sw.Write(TheDataTable.Columns[i]);
    if (i < iColCount – 1)
    {
     sw.Write(separator);
    }
   }
   sw.Write(sw.NewLine);

   //write rows
   foreach (DataRow dr in TheDataTable.Rows)
   {
    for (int i = 0; i < iColCount; i++)
    {
     if (!Convert.IsDBNull(dr[i]))
     {
      string data = dr[i].ToString();
      data = data.Replace(“\”", “\\\“”);
      sw.Write(quote + data + quote);
     }
     if (i < iColCount – 1)
     {
      sw.Write(separator);
     }
    }
    sw.Write(sw.NewLine);
   }
   sw.Close();
  }

CTE (Common Table Expression) to parse a delimited string into a table

This function uses a recursive CTE (Common Table Expression) to parse a delimited string into a table.

Here is a simple example of how the function can be used:

DECLARE @Gmins nvarchar(max)  

SET @Gmins = ’100,101,102,103′;

SET @Gmins = @Gmins + ‘,’;

SELECT data FROM dbo.fn_SplitString_To_Table(@Gmins,’,')  

The first parameter is the string that you want to parse, the second is the delimiter that you want to use to

split the string (normally a comma or a semi-colon).  

Recursive CTE’s are limited to 100 levels of recursion by default.  As soon as a string is split into more than 101 parts,

 you will get an error stating that this limit has been exceeded.  

 If you change the value “102″ to “101″ the statement will work without any problems. 

To bypass the limit of 100 levels of recursion, you need to add a query hint to the code – OPTION(MAXRECURSION 0)

CREATE FUNCTION [dbo].[fn_SplitString_To_Table]
(
     @string NVARCHAR(MAX),
     @delimiter CHAR(1)
)
RETURNS table
AS
    RETURN
 WITH Strings(data, r)
 AS
 (
  SELECT
   SUBSTRING(@string,1, CHARINDEX(@delimiter, @string)-1) data,
   SUBSTRING(@string,CHARINDEX(@delimiter, @string)+1, len(@string)) r
  UNION ALL
  SELECT
   SUBSTRING(r,1, CHARINDEX(@delimiter, r)-1) data,
   SUBSTRING(r,CHARINDEX(@delimiter, r)+1, len(r)) r
  FROM Strings
  WHERE
   CHARINDEX(@delimiter, r) > 0
 )
    SELECT data FROM Strings

Convert HTML string to PDF using iTextSharp

This code is very useful for generating PDF page.
Actually I have a HTML string .In side that  string there are  some tokens available.
For example :
<p><strong>Introduction</strong></p><p>Hi Good morning </p><p>Employee Details</p><p>{EMPLOYEETABLE}</p><p>Thanks…</p>
Assume that above string is  being fetched from database.
In this string i am using one token (  {EMPLOYEETABLE}  ).
I am replacing this token by employee table , there after I generate a PDF page.

In my  next article I will give more information as how to generate PDF.
1) Pdf paging
2) Water Mark
This article Only generating pdf page from HTML string.

First create dynamic temporary Datatable
  public DataTable GetFirstTable()
        {
            DataTable FirstTable = new DataTable();
            // Declare DataColumn and DataRow variables.   
            DataColumn column;
            DataRow row;
            // Create new DataColumn, set DataType, ColumnName and add to DataTable.   
            column = new DataColumn();
            column.DataType = System.Type.GetType(“System.Int32″);
            column.ColumnName = “id”;
            FirstTable.Columns.Add(column);
            // Create second column.          
            column = new DataColumn();
            column.DataType = Type.GetType(“System.Int32″);
            column.ColumnName = “Amount1″;
            FirstTable.Columns.Add(column);
            column = new DataColumn();
            column.DataType = Type.GetType(“System.Int32″);
            column.ColumnName = “Amount2″;
            FirstTable.Columns.Add(column);
            // Create new DataRow objects and add to DataTable. 
            for (int i = 0; i < 15; i++)
            {
                row = FirstTable.NewRow();
                row["id"] = i;
                row["Amount1"] = i.ToString();
                row["Amount2"] = “100″ + i.ToString();
                FirstTable.Rows.Add(row);
            }
            return FirstTable;
        }
    }
 public void ConvertHtmlStringToPDF()
        {
            GridView grdTemp = new GridView();
            grdTemp.DataSource = GetFirstTable();
            grdTemp.DataBind();
            HtmlForm form = new HtmlForm();
            form.Controls.Add(grdTemp);
            StringWriter sw = new StringWriter();
            HtmlTextWriter hTextWriter = new HtmlTextWriter(sw);
            form.Controls[0].RenderControl(hTextWriter);
            string html = sw.ToString();
            string htmlDisplayText = @”<p><strong>Introduction</strong></p><p>Hi Good morning </p><p>Employee Details</p><p>{EMPLOYEETABLE}</p><p>Thanks…</p>”;
            htmlDisplayText = htmlDisplayText.Replace(“{EMPLOYEETABLE}”, html);         
            Document document = new Document();          
            MemoryStream ms = new MemoryStream();
            PdfWriter writer = PdfWriter.GetInstance(document, ms);        
            StringReader se = new StringReader(htmlDisplayText);
            HTMLWorker obj = new HTMLWorker(document);
            document.Open();
            obj.Parse(se);
            // step 5: we close the document
            document.Close();      
            Response.Clear();
            Response.AddHeader(“content-disposition”, “attachment; filename=report.pdf”);
            Response.ContentType = “application/pdf”;                    
            Response.Buffer = true;        
            Response.OutputStream.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
            Response.OutputStream.Flush();
            Response.End();

        }

See the Pdf image

 

Delete records in GridView using Check boxes

STEP 1:
————–
First we will  create table in databse

/****** Object:  Table [dbo].[emp]    Script Date: 05/11/2009 12:46:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[emp](
 [eno] [int] NULL,
 [ename] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [sal] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [status] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

STEP 2:
——————-<asp:GridView ID=”grdView1″ runat=”server” AutoGenerateColumns=”false” >
<Columns>
<asp:TemplateField >
<ItemTemplate>
<asp:CheckBox ID=”chkSelect” runat=”server” />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField=”eno” HeaderText=”Eno” />
<asp:BoundField DataField=”ename” HeaderText=”Ename” />
</Columns>
</asp:GridView>
<br />
<asp:Button ID=”btnDelete” runat=”server” Text=”Delete” onclick=”btnDelete_Click” />
STEP 3:
———————-

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Specialized;

    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                BindData();
        }
      
        public void BindData()
        {
                SqlConnection con = new SqlConnection(“server=(local);user id=;password=;database=;”);
                SqlCommand cmd = new SqlCommand(“SELECT eno,ename FROM Emp”, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();             
                grdView1.DataSource = ds.Tables[0];
                Page.DataBind();          
        }

        protected void btnDelete_Click(object sender, EventArgs e)
        {
            //Create a string collection to store ids of records to be deleted.
            StringCollection idCollection = new StringCollection();
            string iDs = string.Empty;
            //Loop through gridview rows to find checked rows
            foreach (GridViewRow row in grdView1.Rows)
            {
                CheckBox chkDelete = (CheckBox)row.Cells[0].FindControl(“chkSelect”);
                if (chkDelete != null)
                {

                    if (chkDelete.Checked)
                    {
                        iDs = row.Cells[1].Text;
                        idCollection.Add(iDs);
                    }
                }
            }
            //Call the method to delete records
            DeleteMultipleRecords(idCollection);
            //Rebind the gridview
            BindData();
        }

        private void DeleteMultipleRecords(StringCollection idCollection)
        {
           //Create a sql connection and sql command
            SqlConnection con = new SqlConnection(“server=(local);user id=;password=;database=;”);
            SqlCommand cmd = new SqlCommand();
            string iDs = “”;
            foreach (string id in idCollection)
                iDs += id.ToString() + “,”;
            string strIDs = iDs.Substring(0,iDs.LastIndexOf(“,”));
            string query = “DELETE FROM EMP WHERE ENO IN (” + strIDs + “)”;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

        }
    }

Removing Duplicate Records using SQL Server 2005

First i have created Employee table.

CREATE TABLE Employee

(

ENo INT,

ENAME VARCHAR(30),

SALARY INT

)

INSERT INTO Employee(Eno,Ename,Sal)

VALUES(1,’ABC’,2000)

UNION ALL

SELECT 2,’DEF’,3000

UNION ALL

SELECT 3,’GHI’,4000

UNION ALL

SELECT 1,’JKL’,5000

 

Now, We will run the following query ,we will remove all of my duplicate records.

;WITH TempEmployeeTbl(Eno,Ename,Ranking)

AS

(

SELECT Eno,Ename,Ranking = DENSE_RANK() OVER(PARTITION BY  Eno ORDER BY NEWID() ASC)

)

DELETE FROM TempEmployeeTbl

WHERE Ranking > 1

Find Last Date Time Updated for Any Table

Now we have created a table and populated it with data. Next, we will run the following query to find out when it was last updated.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘AdventureWorks’)
AND OBJECT_ID=OBJECT_ID(‘test’)

Delete projects from VS.Net 2005 Start Page

Use regedit and go to: HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList

Pass Multiple Values from a GridView to Another Page using ASP.NET

Single Value:
add the following hyperlink control after the <Columns> tag in the GridView as shown below:
<Columns>
<asp:HyperLinkField DataNavigateUrlFields=”CustomerID” DataNavigateUrlFormatString=”CustomerDetails.aspx?CID={0}” Text=”Pass Single Value” />
Multiple Values:
Just below the first hyperlink field, add another hyperlink field as shown below:
<asp:HyperLinkField DataNavigateUrlFields=”CustomerID, CompanyName, ContactName, Address, City” DataNavigateUrlFormatString=”CustomerDetails.aspx?CID={0}&CName={1}&ContactName={2}&Addr={3}&City={4}” Text=”Pass Multiple Values” />

Dynamically Create a DIV tag using Server-Side code

This sample shows how to create a <Div> dynamically using C#.

In .aspx page

 <asp:Button ID=”Botton1″ runat=”server”

        onclick=”Button1_Click” style=”height:
 26px”

        Text=”Code BehindPage” />

In .aspx.cs page (CodeBehind)

 

 

 

 

 

 

 

System.Web.UI.HtmlControls.

 

HtmlGenericControl dynDiv = new System.Web.UI.HtmlControls.HtmlGenericControl(“DIV”);dynDiv.ID =

“dynDivCode”;dynDiv.Style.Add(

HtmlTextWriterStyle.BackgroundColor,“Gray”);dynDiv.Style.Add(

HtmlTextWriterStyle.Height, “20px”);dynDiv.Style.Add(

HtmlTextWriterStyle.Width, “300px”);dynDiv.InnerHtml =

“I was created using Code Behind”; 

this.Controls.Add(dynDiv);

How to globally register user and custom controls in ASP.NET 2.0

In previous versions of ASP.NET, if you wanted to import and use any user or custom control, you needed to add a page directive <%@ Register %> in that .aspx page. Shown below is an example of the register directive for a user control.
<%@ Register  TagPrefix=”uc”  TagName=”table”  Src=”table.ascx” %>
<body>
    <form id=”form1″ runat=”server”>
    <div>
        <uc:table ID=”Table1″ runat=”server” />
    </div>
    </form>
</body>
In ASP.NET 2.0, managing controls has become easier. Instead of declaring them on every page, you can declare them only once in your web.config file and use them in your entire project.
<configuration>
    <system.web>       
      <pages>
            <controls>
                  <add tagPrefix=”uc” tagName=”table” src=”table.ascx” />
            </controls >
      </pages >  
    </system.web>
</configuration>
Once you have registered this control in your web.config, you can use this control on any page without explicitly adding a register directive on the page.
<body>
    <form id=”form1″ runat=”server”>
    <div>
        <uc:table ID=”Table1″ runat=”server” />
    </div>
    </form>
</body>
While using this control, you will also notice the intellisense support which saves us from remembering the syntax for using the control.
Well that’s it. It’s that easy. Thanks to the book MVP: Hacks and Tips book from Wrox which mentioned this tip.

One thing to note above is the use of the “~” syntax with the user-controls.  For those of you not familiar with this notation, the “~” keyword in ASP.NET means “resolve from the application root path”, and provides a good way to avoid adding “..\” syntax all over your code.  You will always want/need to use it when declaring user controls within web.config files since pages might be using the controls in different sub-directories – and so you always need to resolve paths from the application root to find the controls consistently.

Creating a Dynamic Hyperlink in ASP.NET using the Web.Config

<appSettings>

    <add key=”someurl” value=”http://mvijayreddy.wordpress.com />

  </appSettings>

 

  <asp:HyperLink ID=”HyperLink1″ runat=”server”

    NavigateUrl=”<%$ Appsettings:someurl %>”  Text=”Click Here”>
    </asp:HyperLink>