如何从C＃中的数据表生成插入脚本，我想将其保存在文本文件中。 [英] How do I generate insert script from data table in C# and I want to save it in a text file.

问题描述

我有excel表数据，我想从excel表生成插入脚本。我将excel表数据导入数据表但我不知道如何从数据表生成插入脚本并通过c＃代码将其保存在文本文件中。

我想自动显示列名使用循环但不在打印命令中写每个列名。



请帮我解决它



我尝试过：

I have excel sheet data and I want to generate insert script from excel sheet. I am getting excel sheet data into data table but I don't know how to generate insert script from the data table and save it in a text file through c# code.
I want to display column names automatically using a loop but not to write every column name in print command.

please help me to solve it

What I have tried:

protected void btnconverter_Click(object sender, EventArgs e)
        {
            if (btnFleUpld.HasFile)
            {
                string FileName = Path.GetFileName(btnFleUpld.FileName);
                string Extension = Path.GetExtension(btnFleUpld.PostedFile.FileName);
                //string fileExtension = filename.Substring(filename.LastIndexOf("."));
                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                string Filepath = FolderPath + FileName;
                // fileUpld.SaveAs(Server.MapPath(Filepath));
                btnFleUpld.SaveAs(Server.MapPath("~/fileupload/" + FileName));


                Import_To_Grid(Filepath, Extension);//, rbHDR.SelectedItem.Text
            }

        }




        private void Import_To_Grid(string Filepath, string Extension)//, string isHDR
        {
            OleDbConnection oledbConn = new OleDbConnection();
            if (Path.GetExtension(Filepath) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filepath + ";Extended Properties=Excel 12.0;");
            }

            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand(); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            DataTable dt = new DataTable();
            //DataSet ds = new DataSet();

         

          
            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [PsslInOrders$]";
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(dt);



            StreamWriter swExtLogFile = new StreamWriter("D:/fileupload/vishnu2.txt", true);
            swExtLogFile.Write(Environment.NewLine);
            //foreach (DataRow row in dt.Rows)
                for (int i = 0; i <= dt.Rows.Count-1;i++ )
                {
                    for (int k = 0; k < dt.Columns.Count; k++)
                    {
                        string ColName = dt.Columns[k].ColumnName.ToString();
                       
                       
                    }
               
            swExtLogFile.Write(" INSERT [dbo].[Orders] ([OrderType], [OrderNumber], [Status], [Date], [RequestedOn], [CustomerID], [Location]) VALUES ( '" + dt.Rows[i]["OrderType"].ToString().Trim() + "','" + dt.Rows[i]["OrderNumber"].ToString().Trim() + "','" + dt.Rows[i]["Status"].ToString().Trim() + "','" + dt.Rows[i]["Date"].ToString().Trim() + "','" + dt.Rows[i]["RequestedOn"].ToString().Trim() + "','" + dt.Rows[i]["CustomerID"].ToString().Trim() + "','" + dt.Rows[i]["Location"].ToString().Trim() + "')");
                   
  
                }
            swExtLogFile.Write("*****END OF DATA****" + DateTime.Now.ToString());
            swExtLogFile.Flush();
            swExtLogFile.Close();
        }

推荐答案

;
oleda = new OleDbDataAdapter（cmd）;
oleda.Fill（dt）;



StreamWriter swExtLogFile = new StreamWriter（ D：/fileupload/vishnu2.txt， true ）;
swExtLogFile.Write（Environment.NewLine）;
// foreach（dt.Rows中的DataRow行）
for （ int i = 0 ; i < = dt.Rows.Count-1; i ++ ）
{
for （ int k = 0 ; k < dt.Columns.Count; k ++）
{
string ColName = dt.Columns [k] .ColumnName.ToString（）;


}

swExtLogFile.Write（ INSERT [dbo]。[Orders]（[OrderType]，[OrderNumber]，[Status]，[Date]，[RequestedOn]，[CustomerID]，[Location]）VALUES（' + dt.Rows [ i] [ OrderType]。ToString（）。Trim（）+ '，' + dt.Rows [i] [ OrderNumber]。ToString（）。Trim（）+ '，' + dt.Rows [i] [ 状态]。 ToString（）。修剪（）+ '，' + dt.Rows [i] [< span class =code-string> Date]。ToString（）。Trim（）+ '，' + dt.Rows [i] [ RequestedOn]。ToString（）。Trim（）+ '，' + dt.Rows [i] [ CustomerID]。ToString（）。修剪（）+ '，' + dt.Rows [ i] [ Location]。ToString（）。Trim（）+ '））;


}
swExtLogFile.Write（ **** *数据结束**** + DateTime.Now.ToString（））;
swExtLogFile.Flush（）;
swExtLogFile.Close（）;
}

"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(dt); StreamWriter swExtLogFile = new StreamWriter("D:/fileupload/vishnu2.txt", true); swExtLogFile.Write(Environment.NewLine); //foreach (DataRow row in dt.Rows) for (int i = 0; i <= dt.Rows.Count-1;i++ ) { for (int k = 0; k < dt.Columns.Count; k++) { string ColName = dt.Columns[k].ColumnName.ToString(); } swExtLogFile.Write(" INSERT [dbo].[Orders] ([OrderType], [OrderNumber], [Status], [Date], [RequestedOn], [CustomerID], [Location]) VALUES ( '" + dt.Rows[i]["OrderType"].ToString().Trim() + "','" + dt.Rows[i]["OrderNumber"].ToString().Trim() + "','" + dt.Rows[i]["Status"].ToString().Trim() + "','" + dt.Rows[i]["Date"].ToString().Trim() + "','" + dt.Rows[i]["RequestedOn"].ToString().Trim() + "','" + dt.Rows[i]["CustomerID"].ToString().Trim() + "','" + dt.Rows[i]["Location"].ToString().Trim() + "')"); } swExtLogFile.Write("*****END OF DATA****" + DateTime.Now.ToString()); swExtLogFile.Flush(); swExtLogFile.Close(); }

代替dt.Rows [i] [OrderType]，您可以使用例如

Instead of dt.Rows[i]["OrderType"] you can use e.g.

dt.Rows[i][2]

而不是 swExtLogFile.Write 我会用：

Instead of swExtLogFile.Write I would use:

swExtLogFile.WriteLine

否则你的行将没有行结尾。



而不是：string ColName = dt.Columns [k] .ColumnName.ToString（）;

Otherwise your lines will have no line endings.

Instead of: string ColName = dt.Columns[k].ColumnName.ToString();

string[] ColNames = New String[dt.Columns.Count];
for (int k = 0; k < dt.Columns.Count; k++)
{
  ColNames[k] = dt.Columns[k].ColumnName.ToString();
}

之后在INSERT语句中使用 ColNames 。

After that use the ColNames in your INSERT statements.

这篇关于如何从C＃中的数据表生成插入脚本，我想将其保存在文本文件中。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！