如何在Dataset / DataTable上执行DML操作并保存到数据库 [英] How to perform DML operations on a Dataset / DataTable and save to database

问题描述

这里我试图将Excel文件加载到网格视图而不保存到数据库。

我需要在网格上执行DML操作保存在DataSet / DataTable中。

我不知道在数据集上执行操作，这里我将整个Excel工作表保存在ViewState中并将其分配给gridview。

任何人都有想法在DataSet / DataTable上执行DML操作。如果是这样，请在这方面提供帮助。



谢谢。



这是我的过程关注

首先：



将excel加载到gridview

Hi,
Here i am trying to load a Excel file to a grid view without saving to the database.
I need to perform DML operations on the grid that is saved in a DataSet / DataTable.
I dont have any idea of performing operations on a dataset and here i am saving the whole excel sheet in a ViewState and assiging it to gridview.
Any one have idea of performing DML operations on a DataSet/DataTable. If So, helpme out in this aspect.

Thank You.

This is the process i am following
First:

Loading excel to gridview

DataTable _dtTable = new DataTable();
        DataSet _objDs = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                OleDbConnection connection = null;
                string excelPath = ConfigurationManager.AppSettings["ExcelPath"].ToString();
                excelPath = excelPath + FileUpload1.PostedFile.FileName;
                ViewState["ExcelSheetName"] = FileUpload1.PostedFile.FileName;
                FileUpload1.SaveAs(excelPath);
                string FileType = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("."));
                if (FileType == ".xlsx")
                {
                    connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 12.0 Xml;");
                }
                else if (FileType == ".xls")
                {
                    connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;");
                }
                connection.Open();
                OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
                OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
                da.Fill(_objDs);
                _dtTable = _objDs.Tables[0];
                Label1.Text = "Total No.Records:";
                lblcount.Text = _dtTable.Rows.Count.ToString();
                ViewState["PinsTable"] = _dtTable;
                GridView1.DataSource = _dtTable;
                GridView1.DataBind();
            }


Second:

protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string connectionString = "";
            DataTable _dt = (DataTable)ViewState["PinsTable"];
// This is where i was struck ed

我有网格编辑的想法，但这里是不同的场景。

这里我必须对数据表进行一些DML操作，这必须保存到数据库而不是Excel工作表数据只有gridview数据。

I have idea of grid editing but here it is different scenario.
Here i have to do some DML operations on the datatable and this has to be saved to the database not the Excel sheet data only the gridview data.

推荐答案

，连接）;
OleDbDataAdapter da = new OleDbDataAdapter（ocmd）;
da.Fill（_objDs）;
_dtTable = _objDs.Tables [ 0 ];
Label1.Text = Total No.Records：;
lblcount.Text = _dtTable.Rows.Count.ToString（）;
ViewState [ PinsTable] = _dtTable ;
GridView1.DataSource = _ dtTable;
GridView1.DataBind（）;
}


秒：

受保护 void btnSubmit_Click（ object sender，EventArgs e）
{
string connectionString = ;
DataTable _dt =（DataTable）ViewState [ PinsTable];
// 这就是我被击中的地方

", connection); OleDbDataAdapter da = new OleDbDataAdapter(ocmd); da.Fill(_objDs); _dtTable = _objDs.Tables[0]; Label1.Text = "Total No.Records:"; lblcount.Text = _dtTable.Rows.Count.ToString(); ViewState["PinsTable"] = _dtTable; GridView1.DataSource = _dtTable; GridView1.DataBind(); } Second: protected void btnSubmit_Click(object sender, EventArgs e) { string connectionString = ""; DataTable _dt = (DataTable)ViewState["PinsTable"]; // This is where i was struck ed

我有网格编辑的想法，但这里是不同的场景。

这里我必须对数据表进行一些DML操作，这必须保存到数据库而不是Excel工作表数据只有gridview数据。

I have idea of grid editing but here it is different scenario.
Here i have to do some DML operations on the datatable and this has to be saved to the database not the Excel sheet data only the gridview data.

您可能听说过表有价值的参数 [ ^ ] 。请参阅下面的文章，这些文章将演示TVP的用法。

将数据表发送到存储过程 [ ^ ]

使用表值参数（数据库引擎） [ ^ ]







--Amit

You might have heard about Table Valued Prameters[^]. Refer the articles below which will demonstrate the usage of TVP.
Sending a DataTable to a Stored Procedure[^]
Use Table-Valued Parameters (Database Engine)[^]



--Amit

感谢Amit，我已经浏览了链接但是我对T-Sql一无所知。

感谢您的建议。

Thanks Amit ,i have gone through the links But i don''t have any idea regarding T-Sql.
thanks for ur suggestion .

这篇关于如何在Dataset / DataTable上执行DML操作并保存到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！