Export GridView Data Into CSV File in Asp.net 2.0

Steps.

1) Create Simple Web Application ( asp.net using C#).

2) put the below control in to the page (.aspx)

2.1) GridView (id= GridView1)

2.2) Button (id=button1 and Text =Create CSV File)

or put the below code

<form id="form1" runat="server">
<center>
<div>
<table>
    <tr>
      <td>
       Create CSV File
      </td>
     </tr>
     <tr>
       <td>
<asp:GridView ID="GridView1" runat="server"  CellPadding="4" ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
                               <AlternatingRowStyle BackColor="White" />
</asp:GridView>
    </td>
    </tr>
     <tr>
     <td align="center">
 <asp:Button ID="Button1" runat="server" Text="Create CSV File" OnClick="Button1_Click" />
                       </td>
                   </tr>
               </table>
           </div>
       </center>
   </form>

3) assign data source to GridView. here i created dynamic dataset (on page load).  save the DataSet in to ViewState.

public DataSet MyData
    {
        get
        {
            if (ViewState["MyData"] == null)
               ViewState["MyData"] = new DataSet();
            return (DataSet)ViewState["MyData"];
        }
        set { ViewState["MyData"] = value; }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //create new data set
            DataSet Ds = new DataSet();
            //create new datatable
            DataTable dt = new DataTable();
            //create new column
       DataColumn dc1 = new DataColumn("Id");
       DataColumn dc2 = new DataColumn("Code");
       DataColumn dc3 = new DataColumn("Name");
       DataColumn dc4 = new DataColumn("datetime");

            //add column to datatable
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            for (int i = 0; i < 10; i++)
            {
                //add new row in to datatable
                DataRow dr = dt.NewRow();
                dr["Id"] = i;
                dr["Code"] = "A" + i.ToString();
                dr["Name"] = "B" + i.ToString();
                dr["datetime"] = System.DateTime.Now.AddDays(Convert.ToDouble(i));
                dt.Rows.Add(dr);
            }
            Ds.Tables.Add(dt);
            MyData = Ds;
            GridView1.DataSource = MyData;
            GridView1.DataBind();
        }
        else
        {
            GridView1.DataSource = MyData;
            GridView1.DataBind();
        }
    }

4) Export GridView Data in to CSV format

protected void Button1_Click(object sender, EventArgs e)
   {
       try
       {
           //Create CSV file 
           StreamWriter objSw = new StreamWriter(Server.MapPath("~/demo.csv"));
           //get table from GridView1
           DataTable objDt = ((DataSet)GridView1.DataSource).Tables[0];
           //Get No Of Column in GridView
           int NoOfColumn = objDt.Columns.Count;
           //Create Header
           for (int i = 0; i < NoOfColumn; i++)
           {
               objSw.Write(objDt.Columns[i]);
               //check not last column
               if (i < NoOfColumn - 1)
               {
                   objSw.Write(",");
               }
           }
           objSw.Write(objSw.NewLine);
           //Create Data  
           foreach (DataRow dr in objDt.Rows)
           {
               for (int i = 0; i < NoOfColumn; i++)
               {
                   objSw.Write(dr[i].ToString());

                   if (i < NoOfColumn - 1)
                   {
                       objSw.Write(",");
                   }
               }
               objSw.Write(objSw.NewLine);
           }
           objSw.Close();
       }
       catch (Exception ex)
       {
           Response.Write("Can Not Generate CSV File");
       }       
   }

Thanks.

http://amitpatriwala.wordpress.com

Insert Null Value in DateTime Column SqlServer 2005 using asp.net

steps:

1) Create Simple web application (asp.net with C#).

2) create below table in the database. here i made table (table_1 in test database).

DataBase Name :Test
Table Name : Table_1
ColumnName ColumnDataType
id int (Auto Increment & Primary Key)
code varchar(50) (Allow Null True)
date datetime (Allow Null True)
3) put the below control on the form.
3.1) Calendar (id --> Calendar1)
3.2) Button (id --> btnInsert)
3.3) Button (id --> btnGet)
3.4) Label (id --> lblDataValue)
4) put the below code in to C# page.

Add below name space first :

using System.Data.SqlClient;
copy the below code and check.

 //connection string
    //please change as per your server and database 
    string constr = @"Data Source=(local);Initial Catalog=test;Integrated Security=True";
    public int insertedId
    {
        get { return Convert.ToInt32(ViewState["inserttedid"]); }
        set { ViewState["inserttedid"] = value; }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        //if user is not selected any value from calendar at that time null is inserted
        //otherwise selected date is inserted
        string datetime = Calendar1.SelectedDate.Date.Equals(DateTime.MinValue) == true ? "Null" : "'" + Calendar1.SelectedDate + "'";
        string strins = "insert into table_1 values " +
            "('amit'," + datetime + ");select scope_identity();";
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strins, sqlcon);
        sqlcon.Open();
        //get the id of inserted row using Scope_Identity function
         insertedId = Convert.ToInt32(cmd.ExecuteScalar());
        sqlcon.Close();
    }
    protected void btnGet_Click(object sender, EventArgs e)
    {
        //get the inserted record
        string strSelect = "Select * from table_1 where id =" + insertedId;
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strSelect, sqlcon);
        sqlcon.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            //set the value in the label for display purpose  
            lblDataValue.Text = dr["date"] == System.DBNull.Value ? DateTime.MinValue.ToShortDateString() : Convert.ToString(dr["date"]);
        }
        sqlcon.Close();
    }

Thanks.

http://amitpatriwala.wordpress.com