从SQL获取数据以及在C＃中读取excel [英] Get data from SQL along with read excel in C#

问题描述

我有一个程序可以将excel文件导入到datagridview的数据表绑定。目前我需要添加另一个列名称InvtID，并根据我导入的条形码列从sql获取该InvtID数据。



如何才能实现这一目标，因为我的编码能够在导入excel后获取数据，因此列数据不在旁边 - 条形码列。这是我遇到的唯一一个完成这项任务的问题，请帮帮我。



我尝试过的事情：

Hi, I have a program that are able to import an excel file to datatable binding with datagridview. Currently I need to add another column name InvtID and get that InvtID data from sql based on Barcode column that I have been imported.

How am I able to achieve this one as right now my coding are able to get the data AFTER import an excel, so the column data are not in side-by-side with Barcode column. This is the only problem I encounter to finish this task, please help me.

What I have tried:

public void filldatagridview(ExcelWorksheet workSheet)
       {
           DataTable dt = new DataTable();

           //Create the data column
           for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
           {
               dt.Columns.Add(col.ToString());
           }

          for (int row = 12; row <= 26; row++)
           {
               DataRow newRow = dt.NewRow(); //Create a row
               int i = 0;
               for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
               {
                   newRow[i++] = workSheet.Cells[row, col].Text;
               }
               dt.Rows.Add(newRow);
           }

           dt.Columns.RemoveAt(0); //remove No
           dt.Columns.RemoveAt(0); //remove article

      //Get BookCode
      using (SqlConnection conn = new SqlConnection("Server"))
      using (SqlCommand cmd = new SqlCommand(null, conn))
     {
    StringBuilder sb = new StringBuilder("WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'No Bookcode Found' ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In (");
             for (int i = 0; i < dt.Rows.Count; i++)
              {
                 if (i != 0) sb.Append(",");
                  string name = "@P" + i;
                  cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]); //"3" is barcode column
                  sb.Append(name);
              }
              sb.Append(") ORDER BY Barcode Asc");
              cmd.CommandText = sb.ToString();
              SqlDataAdapter da = new SqlDataAdapter(cmd);
              da.Fill(dt);

              dt.Columns["BOOKCODE"].SetOrdinal(0);
              dataGridView2.DataSource = dt;
           }
      }

推荐答案

我会这样做：

1）创建数据集 [ ^ ]（ ds ）

// Excel

2）创建 OleDbConnection [ ^ ]到Excel文件

3）创建 OleDbCommand [ ^ ]从Excel中获取数据

4）将数据加载到 DataTable [ ^ ]（ dt1 ）对象通过 OleDbDataReader [ ^ ]

5）将 dt1 对象添加到 ds

// SQL Server

6）创建 SqlConnection [ ^ ]到SQL数据库

7）创建 SqlCommand [ ^ ]从SQL服务器获取数据

8）通过 DataTable （ dt2 ）对象：//docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader？view = netframework-4.7.2> SqlDataReader [ ^ ]

9）将 dt2 添加到 ds

//最终工作

10）通过Linq加入数据：

I'll do that this way:
1) create DataSet[^] (ds)
//Excel
2) create OleDbConnection[^] to Excel file
3) create OleDbCommand[^] to grab data from Excel
4) load data into DataTable[^] (dt1) object via OleDbDataReader[^]
5) add dt1 object to ds
//SQL Server
6) create SqlConnection[^] to SQL database
7) create SqlCommand[^] to grab data from SQL server
8) load data into DataTable (dt2) object via SqlDataReader[^]
9) add dt2 to ds
//final job
10) join data via Linq:

	string sFileName = @"D:\yourExcelFile.xlsx";
	string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName);

        string sSqlConn = "enter connection details to SQL server";
	//create dt1 and dt2 to be able to use them later
	DataTable dt1 = new DataTable();
	DataTable dt2 = new DataTable();
	
	//1.
	DataSet ds = new DataSet();
	//2.
	using (OleDbConnection connection = new OleDbConnection(sConStr))
	{
		string sql = @"SELECT * FROM [Sheet1

;< /跨度>;
connection.Open（）;
// 3.
使用（OleDbCommand command = new OleDbCommand（sql，connection））
{
// 4。
使用（OleDbDataReader reader = command.ExecuteReader（） ）
{
dt1.Load（读者）;
}
}
// 5.
ds.Tables.Add（DT1）;
// 清理
command.Dispose（）;
}

// 6.

使用（SqlConnection connection = new SqlConnection（sSqlConn））
{
string sql = @ SELECT * FROM YourTable< /跨度>;
connection.Open（）;
// 7.
使用（SqlCommand command = new SqlCommand（sql，connection））
{
// 8.
使用（SqlDbDataReader reader = command.ExecuteReader（） ）
{
dt2.Load（读者）;
}
}
// 9.
ds.Tables.Add（DT2）;
// 清理
command.Dispose（）;
}

// 10.
< span class =code-keyword> var commonData =（ from r1 ds.Tables（ dt1）。AsEnumerable（）
join r2 ds.Tables（ dt1）。r1.Field上的AsEnumerable（）< string>（ 条形码）等于r1.Field< string>（ 条形码））
。选择（x => new
{
A = r1.Field< Type>（ Name1），
B = r1.Field< Type>（ Name2），
C = r1.Field< Type>（ <跨度class =code-string> Name3），
// 第二个数据表数据
D = r2.Field< Type>（ Name1）
}）
.ToList（）;

// 打开Excel文件并将数据转储到其中！
foreach （ var row in commonData）
{
// 这里的逻辑......

}

;"; connection.Open(); //3. using(OleDbCommand command = new OleDbCommand(sql, connection)) { //4. using (OleDbDataReader reader = command.ExecuteReader()) { dt1.Load(reader); } } //5. ds.Tables.Add(dt1); //clean up command.Dispose(); } //6. using (SqlConnection connection = new SqlConnection(sSqlConn)) { string sql = @"SELECT * FROM YourTable"; connection.Open(); //7. using(SqlCommand command = new SqlCommand(sql, connection)) { //8. using(SqlDbDataReader reader = command.ExecuteReader()) { dt2.Load(reader); } } //9. ds.Tables.Add(dt2); //clean up command.Dispose(); } //10. var commonData = (from r1 ds.Tables("dt1").AsEnumerable() join r2 ds.Tables("dt1").AsEnumerable() on r1.Field<string>("Barcode") equals r1.Field<string>("Barcode")) .Select(x=>new { A = r1.Field<Type>("Name1"), B = r1.Field<Type>("Name2"), C = r1.Field<Type>("Name3"), //second datatable data D = r2.Field<Type>("Name1") }) .ToList(); //open Excel file and dump data into it! foreach(var row in commonData) { //your logic here... }

注意：上面的代码是直接写在我头上的，所以它可能包含错误。



祝你好运！

Note: above code has been writen direct from my head, so it can contains errors.

Good luck!

这篇关于从SQL获取数据以及在C＃中读取excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！