如何将gridview中的数据插入数据库 [英] how to insert data from gridview into database

问题描述

您好我需要从表[A]中选择数据并插入表格[M]

Hi i need to select data from table [A] and insert to table [M]

protected void Button3_Click1(object sender, EventArgs e)
        {
            if (RadioButton2.Checked)
            {
                SqlConnection con = new SqlConnection(MyConnectionString);
                // con.Open(); // don't need the Open, the Fill will open and close the connection automatically
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM ADMS_Machining where datetime='" + TextBox1.Text + "'", con);
                mytable = new DataTable();
                da.Fill(mytable);
                GridView2.DataSource = mytable;
                GridView2.DataBind();


            }
            else
            {

                SqlConnection con = new SqlConnection(MyConnectionString);
                // con.Open(); // don't need the Open, the Fill will open and close the connection automatically
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Machining_Master where datetime='" + TextBox1.Text + "'", con);
                mytable = new DataTable();
                da.Fill(mytable);
                GridView2.DataSource = mytable;
                GridView2.DataBind();

            }


        }

        
        
        protected void Button4_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            String strConnString, strSQL;

            strConnString = "Server=kane-pc;UID=sa;PASSWORD=1234;Database=Machining;Max Pool Size=400;Connect Timeout=600;";

        strSQL = "INSERT INTO Machining_Master (DailyMean,Process_Mean,Long_Term_Stdev,CPK_ESTIMATE,DAILY_MINIMUM,DAILY_MAXIMUM,Model)" + 
                                 "VALUES" + "(@DailyMean, @Process_Mean, @Long_Term_Stdev,@CPK_ESTIMATE,@DAILY_MINIMUM,@DAILY_MAXIMUM,@Model";
             
 
//here code

          
       conn.ConnectionString = conn;
            conn.Open();
           cmd.Connection = conn;
            cmd.CommandText = strSQL;
           
           

    }

推荐答案

首先正确命名控件（和变量）.Button1，Button2，... LabelN是非描述性的不太可读。努力并给出正确的名称，如updateButton，btnSelectData，nameLabel等。



第二个养成关闭连接的习惯，并在SqlTransaction中嵌入所有非查询。



第三个回答你的问题，你也可以使用dataAdapter来更新数据。

First of all name your controls (and variables) properly. Button1, Button2,...LabelN is non-descriptive and less readable. Make an effort and give proper names like updateButton, btnSelectData, nameLabel etc.

Second make a habit of closing your connections and embed all non queries witnin SqlTransaction.

Third to answer your question, you can use dataAdapter to update data as well.

string insertCommandText = "your insert (or update) command goes here";
SqlConnection conn = new SqlConnection("someConnectionString");
SqlCommand insertCmd = null;
SqlTransaction trans = null;

try
{
    //Open connection, setup transaction and command
    conn.Open();
    trans = conn.BeginTransaction();
    insertCmd = new SqlCommand(insertCommandText, conn, trans);
    SqlDataAdapter da = new SqlDataAdapte(); 
    da.UpdateCommand = insertCmd;
    
    //get data from gridView 
    DataTable dt = yourGridView.DataSource as DataTable;
    if(dt != null)
    {
        da.Update(dt);
    }

    //commits changes to db
    trans.Commit();
}
catch(Exception ex)
{
    //rollbacks any changes
    trans.Rollback();
    //do some error handling here
}
finally
{   
    //get rid of transaction
    if(trans != null)
    {
        trans.Dispose();
    }
    if(inserCmd != null)
    {
        cmd.Dispose();
    }
    //close connection
    conn.Close();
}

有关更新方法的更多信息这里。

有两种方法可以将批量数据插入数据库表。



1。）使用datatable和tabletype参数/ UDT（用户定义）表）

2.）使用XML。



逻辑：



如果要使用数据表或数据集从代码后面一次向表中插入大量数据，则需要遵循以上2种方法。为此，您需要将要插入表中的数据收集到数据表中，然后可以将数据表作为tabletype参数/ UDT或XML格式传递到存储过程中，并且您的作业将在存储过程中完成。 />


请点击选项1的链接。

1.） bulk-insert-into-table-using-user-defined-table-type

2.） bulk-insert-using-sql -type-user-defined



请遵循以下XML格式。

1.）导入和加工数据从 - XML的文件-into-sql-server-tables



你唯一需要传递的是你的数据作为数据格式他存储过程。使用这些方法中的任何一种，您的工作都将有效地完成。



谢谢

Sisir Patro

Hi,

There are 2 methods by which you can able to insert bulk data to database table.

1.) Using datatable and tabletype parameter / UDT (User defined tables)
2.) Using XML.

Logic:

If you want to insert a bulk of data into a table at a time from your code behind using a datatable or dataset, then you need to follow above 2 methods. For that you need to collect the data you want to insert into a table into a datatable and then you can pass the datatable into a stored procedure as tabletype parameter / UDT or XML format and your job will be done in the stored procedure.

Please follow the links for option 1.
1.) bulk-insert-into-table-using-user-defined-table-type
2.) bulk-insert-using-sql-type-user-defined

Please follow the XML format below.
1.) importing-and-processing-data-from-xml-files-into-sql-server-tables

The only thing you have to pass is your data as datatable format to the stored procedure. Use any one of these method and your job will be done efficiently.

Thanks
Sisir Patro

这篇关于如何将gridview中的数据插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！