如何使用C＃中的访问数据库在单个字段中插入多行datagrid列 [英] How to insert multiple rows of a datagrid column in single field using access database in C#

问题描述

我想将列的所有行（ItemName）保存到访问数据库中的（itemname）的单个字段中。现在行在1个字段中保存1行数据。但我想将所有行的数据存储在ItemName上的1个字段中。

这是该列的截图。其行必须存储在一个字段中。

https://lh3.googleusercontent.com/-Q-kPiuPwlM4/V2eStcNAC5I/AAAAAAAAME/XXjEVI_R3Nk7vvFbCX0_c9OxtVVEls13QCCo/s800/Untitled.png [ ^ ]



我的尝试：



con.Open（）;



for（int i = 0; i< ; dataGridView1.Rows.Count - 1; i ++）

{



string amount = dataGridView1.Rows [i] .Cells [0] .Value.ToString（）;



OleDbCommand cmd = new OleDbCommand（insert into sale（ItemName）values（+ amount +'），con） ;



cmd.ExecuteNonQuery（）;



MessageBox.Show（Dat a注册）;



}

解决方案

首先，为什么？这通常不是一个好主意 - 尽管它很容易做到，并且在您的数据库设计中省去了一些工作，但是当您想要再次使用数据时，它会带来巨大的问题。将它们保持为单独的行通常要好得多，某些形式的键可以返回公共行信息。设计稍微复杂一点，但从长远来看要容易得多。

其次，不要那样做数据库访问。永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



但是如果你必须......那很简单。

 StringBuilder sb =  new  StringBuilder（）; 
  string  sep =  ; 
  for （ int  i =  0 ; i <  dataGridView1.Rows.Count  -   1 ; i ++）
 {
 sb.AppendFormat（  {0} {1}，sep，dataGridView1.Rows [ i] .Cells [ 0 ]。Value.ToString（））; 
 sep =   |; 
} 
  string  amount = sb.ToString（）; 

然后，您可以使用参数化查询插入单个值：

 使用（OleDbCommand cmd =  new  OleDbCommand（  INSERT INTO sale（ItemName）VALUES（@AM），con））
 {
 cmd.Parameters.AddWithValue（  @ AM，金额）; 
 cmd.ExecuteNonQuery（）; 
} 
 MessageBox.Show（ 数据已注册）; 

现在......正如我的应用程序描述的第一张照片。我从组合和其他数据中选择项目名称。当我按下时保存。所有选定的数据应该保存在相关列的数据库中。现在让我知道你的建议。我如何在单独的字段中保存项目名称，但他们的发票ID应该相同？
$ b $嗯。你做到了哦亲爱的...



这是一个糟糕的数据库设计。您担心您的数据库会变得太大，并且您将相同的文本信息存储在一起，一遍又一遍地重复！

查看您的第二个屏幕截图。您要存储多少次相同的CompanyName？相同的ItemName？这不是一个好的设计。

您需要的是单独的表格。

一个表格持有公司：

 ID CoName CoAddress TelNo电子邮件DiscountPercentage ... 

一个表保存项目：

 ID描述价格

另一个持有发票：

 ID CompanyID InvoiceDate InvoiceNumber InvoiceTotalValue ... 

另一个持有InvoiceLines：

 ID InvoiceID ItemID数量值

其中CompanyID，InvoiceID和ItemID是外键，它们引用相关的表ID值。

所有ID值都可以是IDENTITY字段，因此DB会在数据被插入时自动生成它们。 />


当你这样做时，许多事情变得微不足道：某物的价格变化是一个改变的地方，因为你使用了这个价值o生成每个发票行值（item.price * quantity）它不会影响现有发票 - 这非常重要：你可以去监狱这样做！

这样，你只存储一次信息，变化都变得微不足道。并且所有数据库系统都非常非常擅长处理这样组织的数据。他们有特殊的查询（查找SQL JOIN以获取详细信息）来处理它们，并且它们可以确保您不会在数据输入中出错。

看起来设置的工作要多得多，但事实并非如此 - 这是更多的工作，但并不多。请相信我：它会为您节省大量的工作！

您好，因为数据从数据库返回多行，所以您需要使用DataSet用于那个目的。



例如：

 var ds = new DataSet（ ）; 
 var conn = DACUtil.GetConnection（）; 
 try 
 {
 SqlCommand cmd = new SqlCommand（Your-Query-Here，conn）; 
 var da = new SqlDataAdapter {Select7Command = cmd}; 
 
 da.Fill（ds）; 
 
 ds.Tables [0] .TableName =Emp; 
 ds.AcceptChanges（）; 
 
} 
最后是
 {
 conn？.Dispose（）; 
} 
返回ds; 
 

希望它对您有所帮助，请不要忘记标记我的好评。 ：）

i want to save all rows of column(ItemName) into single field of (itemname) in access database. right now rows are saving in 1 row data in 1 field. but i want to store all row's data in 1 field on ItemName.
here is screenshot of that column. whose rows are need to be store in one field.
https://lh3.googleusercontent.com/-Q-kPiuPwlM4/V2eStcNAC5I/AAAAAAAAAME/XXjEVI_R3Nk7vvFbCX0_c9OxtVVEls13QCCo/s800/Untitled.png[^]

What I have tried:

con.Open();

for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{

string amounts = dataGridView1.Rows[i].Cells[0].Value.ToString();

OleDbCommand cmd = new OleDbCommand("insert into sale (ItemName) values ('" + amounts + "')", con);

cmd.ExecuteNonQuery();

MessageBox.Show("Data Registered");

}

解决方案

First off, why? That's not generally a good idea - although it's easy to do, and saves you a little work in your DB design, it gives huge problems later when you want to use the data again. It's normally a lot better to keep them as separate rows which some form of key back to the "common" row information. Slightly more complex to design, but considerably easier in the long run.
Second, don't do database access like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

But if you must...it's simple.

StringBuilder sb = new StringBuilder();
string sep = "";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
   {
   sb.AppendFormat("{0}{1}", sep, dataGridView1.Rows[i].Cells[0].Value.ToString());
   sep = "|";
   }
string amounts = sb.ToString();

You can then insert the single value using a parameterized query:

using (OleDbCommand cmd = new OleDbCommand("INSERT INTO sale (ItemName) VALUES (@AM)", con))
   {
   cmd.Parameters.AddWithValue("@AM", amounts);
   cmd.ExecuteNonQuery();
   }
MessageBox.Show("Data Registered");

"now... as the first picture of my application describes. i select item names from combo and other data. when i press save. all selected data should save in db on its relevant column.now let me know your suggestion. how i can save item names in seperate field but their invoice id should be same?"
Ah. You did that. Oh dear...

That's a poor database design. You're concerned that your DB will grow too large, and you are storing the same text information over, and over, and over again!
Look at your second screen shot. How many times are you going to store the same CompanyName? The same ItemName? That's not a good design.
What you need is separate tables.
One table holds Companies:

ID    CoName    CoAddress    TelNo    Email   DiscountPercentage   ...

One table holds Items:

ID    Description    Price

Another holds Invoices:

ID    CompanyID    InvoiceDate   InvoiceNumber   InvoiceTotalValue...

Another holds InvoiceLines:

ID    InvoiceID    ItemID     Quantity     Value

Where CompanyID, InvoiceID, and ItemID are foreign keys which refer back to the relevant table ID values.
All the ID values can be IDENTITY fields, so the DB will generate them for you automatically when the data is INSERTed.

When you do it like this, many things become trivial: a change in the price of something is a single location to change, and because you use that value to generate each invoice line value (item.price * quantity) it doesn't affect existing invoices - that's seriously important: you can go to jail for doing that!
This way, you only store info once, and changes all become trivial. And all DB systems are very, very good at working with data organised like this. They have special queries (look up SQL JOIN for details) to handle them, and they can ensure that you don't make mistakes in your data entry.
It looks like a lot more work to set up, but it isn't really - it's more work, but not much. And trust me on this: it saves you a HUGE amount of work later!

Hi, as data is return in multiple rows from database, so you need use "DataSet" that is use for that purpose.

For Example:

var ds = new DataSet();
var conn = DACUtil.GetConnection();
try
{
    SqlCommand cmd = new SqlCommand("Your-Query-Here", conn);
    var da = new SqlDataAdapter { Select7Command = cmd };

    da.Fill(ds);

    ds.Tables[0].TableName = "Emp";
    ds.AcceptChanges();

}
finally
{
    conn?.Dispose();
}
return ds;

Hope it will helpful for you, please don't forget to mark my good rating. :)

这篇关于如何使用C＃中的访问数据库在单个字段中插入多行datagrid列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！