C# .NET - Datagridview to Dataset - Asked By Ismael on 28-Jul-11 02:09 PM

I have a datagridview with like 30 columns and 100 rows, i want to insert the whole gridview into a dataset so i can insert to my mysql database

dipa ahuja replied to Ismael on 28-Jul-11 02:21 PM

Check this:

void AddRows()
{
  //Create DataTable
  DataTable dt = new DataTable();
  dt.Columns.Add("Names", typeof(string));
  dt.Columns.Add("country", typeof(string));
  string[] names = { "dipa", "priya" };
  string[] country = { "India", "UK" };
  for (int i = 0; i < names.Length; i++)
  {
    //Adding Row 
    DataRow dr = dt.NewRow();
    dr["Names"] = names[i];
    dr["country"] = country[i];
    dt.Rows.Add(dr);
  }
  dataGridView1.DataSource = dt;
}

Same way create other 28 columns and insert this dt by looping in table

Riley K replied to Ismael on 28-Jul-11 09:22 PM

Use A DataAdapter to update any changes made

Bind a datagridview using the following steps

//create the connection string

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

//create the database query

string query = "SELECT * FROM MyTable";

//create an OleDbDataAdapter to execute the query

OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

//create a command builder

OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

//create a DataTable to hold the query results

DataTable dTable = new DataTable();

//fill the DataTable

dAdapter.Fill(dTable);

To enter the changes to database

Now that we have a DataTable filled with database information, let's see how to synchronize it with aDataGridView.

//the DataGridView

DataGridView dgView = new DataGridView();

//BindingSource to sync DataTable and DataGridView

BindingSource bSource = new BindingSource();

//set the BindingSource DataSource

bSource.DataSource = dTable;

//set the DataGridView DataSource

dgView.DataSource = bSource;

any changes made by the user in the DataGridView will automically be made to the DataTable, dTable. Now we need a way to get the changes back into the database. All you have to do is call the Update function of the OleDbDataAdapter with the DataTableas the argument to accomplish this.

dAdapter.Update(dTable);

Ravi S replied to Ismael on 28-Jul-11 09:32 PM

HI

try this

static string strCon = "provider=Microsoft.Jet.OLEDB.4.0;data source=D:\\c.mdb";

public static OleDbConnection oleCon = new OleDbConnection(strCon);

public static OleDbCommand oleCommand = oleCon.CreateCommand();

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace test
{
public partial class Form5 : Form
{
public Form5()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
connection.oleCon.Open();
string[,] a= new string[100,100];
int countRow = dataGridView1.RowCount;
int countCol = dataGridView1.ColumnCount;
Console.WriteLine("row = " + countRow);
Console.WriteLine("col = " + countCol);
int j = 0;
int k = 0;
string data = "'";
for (k = 0; k < (countRow-1); k++)
{
for (j = 0; j < (countCol); j++)
{
Console.WriteLine("j=" + j);
Console.WriteLine("k=" + k);
a[j,k] = string.Format("" + dataGridView1[j, k].Value);
if( (j == (countCol - 1))&&(k == l))
{
data = data + a[j, k] + "'";
}else
if (j < (countRow - 1))
{
data = data + a[j, k] + "','";
}
Console.WriteLine("a[" + j + "," + k + "]=" + a[j, k]);
Console.WriteLine("a = " + data);
}
connection.oleCommand.CommandText =
"INSERT INTO customer(name,address) " +
"VALUES (" + data + ")";
connection.oleCommand.ExecuteNonQuery();
}
}
}
}

Ravi S replied to Ismael on 28-Jul-11 09:32 PM

HI

try this also

<asp:datagrid id="dgTest" Runat="server" Width="90%" 
          EnableViewState="False" ShowHeader="True"
          AutoGenerateColumns="False" GridLines="Horizontal" 
          BackColor="lightblue">
    <HeaderStyle Wrap="False" Font-Bold="True" BackColor="#3300cc" 
                          ForeColor="white"></HeaderStyle>
    <AlternatingItemStyle BackColor="LawnGreen"></AlternatingItemStyle>
    <Columns>
      <asp:BoundColumn HeaderText="Last Name" DataField="LastName" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="First Name" DataField="FirstName" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="Address" DataField="Address" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="City" DataField="city" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="Region" DataField="Region" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="PostalCode" DataField="PostalCode" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:BoundColumn HeaderText="Country" DataField="Country" 
                          ReadOnly="True"></asp:BoundColumn>
      <asp:TemplateColumn>
        <HeaderTemplate>
          View Detail
        </HeaderTemplate>
        <HeaderStyle HorizontalAlign="Center"></HeaderStyle>
        <ItemTemplate>
          <asp:LinkButton ID="lnkSelect" Runat="server" 
                    text="Select"></asp:LinkButton>
        </ItemTemplate>
        <ItemStyle HorizontalAlign="Center"></ItemStyle>
      </asp:TemplateColumn>
    </Columns>
</asp:datagrid>
<br>
<asp:Button ID="SortButton" style="DISPLAY:none" Runat="server" 
                                  Text="Sort"></asp:Button>

To transfer an event click to non-display button, modify head text and add some JavaScript as follows:

Collapse

private void SetHeadText(SORT_ORDER order)
{
    string headtext = "<a href='#' onclick='javascript:document" + 
            ".getElementById(\"{0}\").click();'> Address</a>";
    if(order == SORT_ORDER.DESC)
    {
        this.dgTest.Columns[this.Col_Address].HeaderText = 
            string.Format(headtext,this.SortButton.ClientID);
    }
    else
    {
        this.dgTest.Columns[this.Col_Address].HeaderText = 
            string.Format(headtext,this.SortButton.ClientID);
    }
}

In button event handler, add the following code. Sort order has been saved in page viewstate.

Collapse

private void SortButton_Click(object sender, System.EventArgs e)
{
    SORT_ORDER order = (SORT_ORDER)ViewState[SORTORDER];
    if(order == SORT_ORDER.ASC)
    {
        this.BindData(SORT_ORDER.DESC);
    }
    else
    {
        this.BindData(SORT_ORDER.ASC);
    }
}

Next is bind data. SetHeadText must be called before calling DataGrid's DataBind function.

Collapse

private void BindData(SORT_ORDER order)
{
    DataView dv = this.GetDataSource().DefaultView;
    if(order == SORT_ORDER.ASC)
    {
        this.SetHeadText(SORT_ORDER.DESC);//for future

        this.ViewState[SORTORDER] = SORT_ORDER.ASC; //for current

        dv.Sort = "Address ASC";
        this.SortLabel.Text = "Current sorting order is ascending";
    }
    else
    {
        this.SetHeadText(SORT_ORDER.ASC);//for future

        this.ViewState[SORTORDER] = SORT_ORDER.DESC; //for current

        dv.Sort = "Address DESC";
        this.SortLabel.Text = "Current sorting order is descending";
    }
    this.dgTest.DataSource = dv;
    dgTest.DataBind();
}

To minimize database access, we keep data in cache.

Collapse

private DataTable  RetrieveDataFromDB()
{
    string SelectString = "select * from Employees";
    DataTable dt = new DataTable();
    using(SqlDataAdapter adpt = new 
          SqlDataAdapter(SelectString,this.NorthWindConnStr ))
    {
        adpt.Fill(dt);
    }
    return dt;
}
private DataTable GetDataSource()
{
    object o;
    o = this.Cache[this.CacheKey];
    if (o != null)
    {
        return (DataTable)o;
    }
    else
    {
        DataTable dt = RetrieveDataFromDB();
        this.Cache.Add(this.CacheKey,dt,null, 
             System.Web.Caching.Cache.NoAbsoluteExpiration, 
             System.TimeSpan.FromMinutes(this.CacheTimeSpan),
             System.Web.Caching.CacheItemPriority.Normal,null);
        return dt;
    }
}

Jitendra Faye replied to Ismael on 29-Jul-11 01:00 AM

Use this working code-

private void FillDataTabl_Click(object sender, EventArgs e)

{

DataTable dt = new DataTable();

int colnum = dataGridView1.ColumnCount;

for (int i = 0; i < colnum; i++)

{

dt.Columns.Add("col" + i.ToString());

}

foreach (DataGridViewRow row in dataGridView1.Rows)

{

DataRow r = dt.NewRow();

for (int i = 0; i < colnum; i++)

{

r[i] = row.Cells[i].ToString();

}

dt.Rows.Add(r);

}

After filling DataTable , using Update() method of DataAdapter you can store data to database.

Hope this will help you.

harsh shah replied to Ismael on 29-Jul-11 01:27 AM

Hi,

i think u need to transfer data from grid to dataset.

Dataset ds =(Dataset)gridview1.datasource;

with the use of this line u have geeting all gridview data into the dataset.

Regards,

Harsh Shah

Radhika roy replied to Ismael on 29-Jul-11 10:10 AM

you can use this

DataTable dtFromGrid = new DataTable();
dtFromGrid = dataGridView1.DataSource as DataTable;

this will work

Hope this will help you