Export records to database and get filtered records

Hi all,

I am doing SEO works where for link building we have some websites they are repeated so i need the filtered websites and duplicate websites with there counting numbers.
There is a excelsheet where i have one columns as website and this contains data.I have to export these data to sql server database.And these records will be shown in a gridview when we click button.In one button when we click all duplicate records will be shown in gridview.I need the filter records from the database removing the duplicate records in gridview then export it to excel.One more thing, i have many excel files so after doing one file i want to delete the records from the database and the above all process will be repeated for those all the excelsheets.

Thanks in advance

Hi Amulya,

Here is your solution.Run the attachments.

First create a table
create table Websites
(
sno int identity,
website nvarchar(200)
)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    Untitled Page

Insert Data	View Data	View Filter Data	View Duplicate Data	Delete Data

            Text="Export to Excel" />

using System;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Websites.xls") + ";Extended Properties=Excel 8.0");
       OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
       conn.Open();
       OleDbDataReader reader = com.ExecuteReader();
       string website = "";

     while (reader.Read())
     {
         website = valid(reader, 0);
         insertdataintosql(website);
     }
         conn.Close();
         l1.Text = "Records Inserted successfully";

    }
    protected string valid(OleDbDataReader reader, int str1)

    {
        object val = reader[str1];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }

    public void insertdataintosql(string website)
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand com = new SqlCommand();
        com.Connection = conn;
        com.CommandText = "insert into Websites(website)values(@website)";
        com.Parameters.Add("@website", SqlDbType.NVarChar).Value = website;
        com.CommandType = CommandType.Text;
        conn.Open();
        com.ExecuteNonQuery();
        conn.Close();
    }

protected void viewdata_Click(object sender, EventArgs e)
{
      l1.Text ="";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from Websites", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void viewfilterdata_Click(object sender, EventArgs e)
{
      viewdata1();

}

void viewdata1()
{
      l1.Text = "";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website FROM Websites GROUP BY website", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void viewduplicatedata_Click(object sender, EventArgs e)
{
      l1.Text = "";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website, COUNT(*) AS 'Count'FROM Websites GROUP BY website", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void Button1_Click(object sender, EventArgs e)
{
      ExportToExcel("Report.xls", GridView1);
}

private void ExportToExcel(string strFileName, GridView dg)
{
      Response.Clear();
      Response.Buffer = true;
      Response.ContentType = "application/vnd.ms-excel";
      Response.Charset = "";
      this.EnableViewState = false;
      System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
      System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
      GridView1.RenderControl(oHtmlTextWriter);
      Response.Write(oStringWriter.ToString());
      Response.End();

}
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void deletedata_Click(object sender, EventArgs e)
{
      SqlConnection conn = new SqlConnection(connStr);
      SqlCommand com = new SqlCommand();
      com.Connection = conn;
      //cmd.CommandText = "delete from emp";
      com.CommandText = "truncate table Websites";
      com.CommandType = CommandType.Text;
      conn.Open();
      com.ExecuteScalar();
      conn.Close();
      l1.Text = "Records Deleted successfully";
      viewdata1();
    }
}

Thanks

If this post helps you mark it as answer

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak15y ago
Accepted answer
Hi Amulya,

Here is your solution.Run the attachments.

First create a table
create table Websites
(
sno int identity,
website nvarchar(200)
)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    Untitled Page




Insert
    Data

View Data

View Filter Data

View Duplicate Data

Delete Data

            Text="Export to Excel" />

using System;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Websites.xls") + ";Extended Properties=Excel 8.0");
       OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
       conn.Open();
       OleDbDataReader reader = com.ExecuteReader();
       string website = "";

     while (reader.Read())
     {
         website = valid(reader, 0);
         insertdataintosql(website);
     }
         conn.Close();
         l1.Text = "Records Inserted successfully";

    }
    protected string valid(OleDbDataReader reader, int str1)

    {
        object val = reader[str1];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }

    public void insertdataintosql(string website)
    {
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand com = new SqlCommand();
        com.Connection = conn;
        com.CommandText = "insert into Websites(website)values(@website)";
        com.Parameters.Add("@website", SqlDbType.NVarChar).Value = website;
        com.CommandType = CommandType.Text;
        conn.Open();
        com.ExecuteNonQuery();
        conn.Close();
    }

protected void viewdata_Click(object sender, EventArgs e)
{
      l1.Text ="";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from Websites", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void viewfilterdata_Click(object sender, EventArgs e)
{
      viewdata1();

}

void viewdata1()
{
      l1.Text = "";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website FROM Websites GROUP BY website", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void viewduplicatedata_Click(object sender, EventArgs e)
{
      l1.Text = "";
      SqlConnection conn = new SqlConnection(connStr);
      SqlDataAdapter sqlda = new SqlDataAdapter("SELECT website, COUNT(*) AS 'Count'FROM Websites GROUP BY website", conn);
      DataSet ds = new DataSet();
      sqlda.Fill(ds, "Websites");
      GridView1.DataSource = ds;
      GridView1.DataMember = "Websites";
      GridView1.DataBind();

}
protected void Button1_Click(object sender, EventArgs e)
{
      ExportToExcel("Report.xls", GridView1);
}

private void ExportToExcel(string strFileName, GridView dg)
{
      Response.Clear();
      Response.Buffer = true;
      Response.ContentType = "application/vnd.ms-excel";
      Response.Charset = "";
      this.EnableViewState = false;
      System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
      System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
      GridView1.RenderControl(oHtmlTextWriter);
      Response.Write(oStringWriter.ToString());
      Response.End();

}
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void deletedata_Click(object sender, EventArgs e)
{
      SqlConnection conn = new SqlConnection(connStr);
      SqlCommand com = new SqlCommand();
      com.Connection = conn;
      //cmd.CommandText = "delete from emp";
      com.CommandText = "truncate table Websites";
      com.CommandType = CommandType.Text;
      conn.Open();
      com.ExecuteScalar();
      conn.Close();
      l1.Text = "Records Deleted successfully";
      viewdata1();
    }
}

Thanks

If this post helps you mark it as answer
0
Amulya Dehury15y ago
Perfect solution.I exactly need this output.

Thanks Satya for your great help.

Regards
Amulya
0