C# .NET - How to add to sql data in listview of window form

Asked By suresh kotte on 28-Aug-11 07:27 AM

How to add to sql data in listview of window form

dipa ahuja replied to suresh kotte on 28-Aug-11 08:01 AM

private void Form1_Load(object sender, EventArgs e)
{
  string conn = "ConnectionString";
  SqlDataAdapter da = new SqlDataAdapter("Select ename from emp", conn);
  DataTable dt = new DataTable();
  da.Fill(dt);
 
  listView1.DataBindings.Clear();
 
  foreach (DataRow row in dt.Rows)
  {
    listView1.Items.Add(row["ename"].ToString());
  }
}

Ravi S replied to suresh kotte on 28-Aug-11 09:44 AM

hi

//very simple databinding in ListView

<LayoutTemplate>

<table border="0" cellpadding="1">

<tr style="background-color:#E5E5FE">

<th align="left"><asp:LinkButton ID="lnkId" runat="server">Id</asp:LinkButton></th>

<th align="left"><asp:LinkButton ID="lnkName" runat="server">Name</asp:LinkButton></th>

<th align="left"><asp:LinkButton ID="lnkType" runat="server">Type</asp:LinkButton></th>

<th></th>

</tr>

<tr id="itemPlaceholder" runat="server"></tr>

</table>

</LayoutTemplate>

<ItemTemplate>

<tr>

<td><asp:Label runat="server" ID="lblId"><%#Eval("ID") %></asp:Label></td>

<td><asp:Label runat="server" ID="lblName"><%#Eval("FirstName")+"

"+Eval("LastName") %></asp:Label></td>

<td><asp:Label runat="server" ID="lblType"><%#Eval("ContactType") %></asp:Label></td>

<td></td>

</tr>

</ItemTemplate>

<AlternatingItemTemplate>

<tr style="background-color:#EFEFEF">

<td><asp:Label runat="server" ID="lblId"><%#Eval("ID") %></asp:Label></td>

<td><asp:Label runat="server" ID="lblName"><%#Eval("FirstName")+" "+

Eval("LastName") %></asp:Label></td>

<td><asp:Label runat="server" ID="lblType"><%#Eval("ContactType") %></asp:Label></td>

<td></td>

</tr>

</AlternatingItemTemplate>



Here Layout template

is making header of the control, and ItemTemplate is showing data taken from table by Binding columns with Label controls, and AlternativeItemTemplate does the same as ItemTemplate just changing CSS for

alternative columns.  





refer link also

http://www.codeproject.com/KB/webforms/CompleteListView.aspx#

http://www.dbtutorials.com/advanced/listview-inserting.aspx

Reena Jain replied to suresh kotte on 28-Aug-11 10:35 AM

Hi,
As listview do not have DataSource propery, we have to manually add items to it. Try the below code. Please make necessary changes as per your requirements.

private void LoadListView()

{

listView1.View = View.Details;

using (SqlConnection connection = new SqlConnection("Server=localhost;Data Source=MyDatabase;Integrated Security=TRUE;"))

{

using (SqlCommand cmd = new SqlCommand("Select * from myTable where name='" + textBox1.Text + "'", connection))

{

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataTable dt = new DataTable();

da.Fill(dt);

foreach (DataRow dr in dt.Rows)

{

string[] values = new string[dr.ItemArray.Length];

for (int i = 0; i < dr.ItemArray.Length; i++)

{

values[i] = dr.ItemArray[i].ToString();

}

listView1.Items.Add(new ListViewItem(values));

}

Radhika roy replied to suresh kotte on 28-Aug-11 01:25 PM

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Form1 : System.Windows.Forms.Form {
   private System.Windows.Forms.Label label1;
   private System.Windows.Forms.Button cmdExecute;
   private System.Windows.Forms.TextBox txtSql;
   private System.Windows.Forms.ListView lvwResult;
   private System.ComponentModel.Container components = null;

   public Form1() {
    InitializeComponent();
   }

   private void InitializeComponent() {
    this.label1 = new System.Windows.Forms.Label();
    this.cmdExecute = new System.Windows.Forms.Button();
    this.txtSql = new System.Windows.Forms.TextBox();
    this.lvwResult = new System.Windows.Forms.ListView();
    this.SuspendLayout();

    this.label1.Location = new System.Drawing.Point(0, 0);
    this.label1.Name = "label1";
    this.label1.Size = new System.Drawing.Size(296, 16);
    this.label1.TabIndex = 0;
    this.label1.Text = "Enter a SQL query or statement and click Execute.";

    this.cmdExecute.Location = new System.Drawing.Point(227, 224);
    this.cmdExecute.Name = "cmdExecute";
    this.cmdExecute.TabIndex = 1;
    this.cmdExecute.Text = "Execute";
    this.cmdExecute.Click += new System.EventHandler(this.cmdExecute_Click);

    this.txtSql.Location = new System.Drawing.Point(0, 16);
    this.txtSql.Multiline = true;
    this.txtSql.Name = "txtSql";
    this.txtSql.Size = new System.Drawing.Size(528, 200);
    this.txtSql.TabIndex = 2;
    this.txtSql.Text = "";

    this.lvwResult.GridLines = true;
    this.lvwResult.Location = new System.Drawing.Point(0, 256);
    this.lvwResult.Name = "lvwResult";
    this.lvwResult.Size = new System.Drawing.Size(528, 200);
    this.lvwResult.TabIndex = 3;
    this.lvwResult.View = System.Windows.Forms.View.Details;

    this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
    this.ClientSize = new System.Drawing.Size(528, 452);
    this.Controls.Add(this.lvwResult);
    this.Controls.Add(this.txtSql);
    this.Controls.Add(this.cmdExecute);
    this.Controls.Add(this.label1);
    this.Name = "Form1";
    this.Text = "Query Processor";
    this.ResumeLayout(false);
   }

   static void Main() {
    Application.Run(new Form1());
   }

   private void cmdExecute_Click(object sender, System.EventArgs e) {
   SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

   try {
     lvwResult.Columns.Clear() ;
     lvwResult.Items.Clear();

     conn.Open();
     txtSql.Text ="select * from Employee";

     SqlCommand cmd = conn.CreateCommand();
     cmd.CommandText = txtSql.Text;

     SqlDataReader dr = cmd.ExecuteReader();

     for (int i = 0; i< dr.FieldCount; i++) {
     ColumnHeader ch = new ColumnHeader();
     ch.Text=dr.GetName(i);
     lvwResult.Columns.Add(ch);
     }

     ListViewItem itmX;

     while (dr.Read()) {
     itmX=new ListViewItem();
     itmX.Text= dr.GetValue(0).ToString();

     for (int i=1 ; i< dr.FieldCount; i++) {
      itmX.SubItems.Add(dr.GetValue(i).ToString());
     }
     lvwResult.Items.Add(itmX);
     }
     dr.Close();
  } catch ( System.Data.SqlClient.SqlException  ex) {
     Console.WriteLine("There was an error in executing the SQL." +
         "\nError Message:" + ex.Message, "SQL");
  } finally {
     conn.Close();
  }
  }
}

Radhika roy replied to suresh kotte on 28-Aug-11 01:25 PM

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Form1 : System.Windows.Forms.Form {
   private System.Windows.Forms.Label label1;
   private System.Windows.Forms.Button cmdExecute;
   private System.Windows.Forms.TextBox txtSql;
   private System.Windows.Forms.ListView lvwResult;
   private System.ComponentModel.Container components = null;

   public Form1() {
    InitializeComponent();
   }

   private void InitializeComponent() {
    this.label1 = new System.Windows.Forms.Label();
    this.cmdExecute = new System.Windows.Forms.Button();
    this.txtSql = new System.Windows.Forms.TextBox();
    this.lvwResult = new System.Windows.Forms.ListView();
    this.SuspendLayout();

    this.label1.Location = new System.Drawing.Point(0, 0);
    this.label1.Name = "label1";
    this.label1.Size = new System.Drawing.Size(296, 16);
    this.label1.TabIndex = 0;
    this.label1.Text = "Enter a SQL query or statement and click Execute.";

    this.cmdExecute.Location = new System.Drawing.Point(227, 224);
    this.cmdExecute.Name = "cmdExecute";
    this.cmdExecute.TabIndex = 1;
    this.cmdExecute.Text = "Execute";
    this.cmdExecute.Click += new System.EventHandler(this.cmdExecute_Click);

    this.txtSql.Location = new System.Drawing.Point(0, 16);
    this.txtSql.Multiline = true;
    this.txtSql.Name = "txtSql";
    this.txtSql.Size = new System.Drawing.Size(528, 200);
    this.txtSql.TabIndex = 2;
    this.txtSql.Text = "";

    this.lvwResult.GridLines = true;
    this.lvwResult.Location = new System.Drawing.Point(0, 256);
    this.lvwResult.Name = "lvwResult";
    this.lvwResult.Size = new System.Drawing.Size(528, 200);
    this.lvwResult.TabIndex = 3;
    this.lvwResult.View = System.Windows.Forms.View.Details;

    this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
    this.ClientSize = new System.Drawing.Size(528, 452);
    this.Controls.Add(this.lvwResult);
    this.Controls.Add(this.txtSql);
    this.Controls.Add(this.cmdExecute);
    this.Controls.Add(this.label1);
    this.Name = "Form1";
    this.Text = "Query Processor";
    this.ResumeLayout(false);
   }

   static void Main() {
    Application.Run(new Form1());
   }

   private void cmdExecute_Click(object sender, System.EventArgs e) {
   SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

   try {
     lvwResult.Columns.Clear() ;
     lvwResult.Items.Clear();

     conn.Open();
     txtSql.Text ="select * from Employee";

     SqlCommand cmd = conn.CreateCommand();
     cmd.CommandText = txtSql.Text;

     SqlDataReader dr = cmd.ExecuteReader();

     for (int i = 0; i< dr.FieldCount; i++) {
     ColumnHeader ch = new ColumnHeader();
     ch.Text=dr.GetName(i);
     lvwResult.Columns.Add(ch);
     }

     ListViewItem itmX;

     while (dr.Read()) {
     itmX=new ListViewItem();
     itmX.Text= dr.GetValue(0).ToString();

     for (int i=1 ; i< dr.FieldCount; i++) {
      itmX.SubItems.Add(dr.GetValue(i).ToString());
     }
     lvwResult.Items.Add(itmX);
     }
     dr.Close();
  } catch ( System.Data.SqlClient.SqlException  ex) {
     Console.WriteLine("There was an error in executing the SQL." +
         "\nError Message:" + ex.Message, "SQL");
  } finally {
     conn.Close();
  }
  }
}

Radhika roy replied to suresh kotte on 28-Aug-11 01:30 PM

visit these links----

http://www.akadia.com/services/dotnet_listview_sort_dataset.html

http://www.codeproject.com/KB/list/ListView_DataBinding.aspx

http://www.csharptutorials.org/csharp/listview-combo-box-csharp.cfm

http://www.codeproject.com/KB/list/TreeView_ListView.aspx

Devil Scorpio replied to suresh kotte on 28-Aug-11 02:45 PM

Hi Suresh,

If you do not know how to add the SQL erver database as datasource into C# project, please perform the following steps:

Click "Data" item.
Then click the "Add new datasource" option. It will show a data source confiuration wizard.
Then select the data type as "Database" and click "Next"
Click the "new connection" button select the "datasouce" as "Microsoft SQL Server" and input the server name.
Choose the how to log on the server and click "OK"
Then click the "Connection Strng" and it will show the connect string and click "Next".
At last choose the datatable you want to add

Then you can use the select ayntax to get the data form specific datatable and do the logic as you want.

Please refer the following website for code, help, post & procedure
http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/6cda07aa-9e0c-486f-8e9c-c941fd312e03/