string str = $"select from TBa where ... ";
DataSet ds = new DataSet();
using (SqlConnection conn= new SqlConnection("server=000.000.0.000;database=数据库名;user id=登录的用户名;password=密码;Pooling=true"))
{
try
{
conn.Open();
SqlCommand com = new SqlCommand(str, conn);
SqlDataAdapter sda = new SqlDataAdapter(com);
sda.Fill(ds);
}
catch
{
}
finally
{
conn.Close();
}
}
DataTable dt = ds.Tables[0];
string strVALID = $"select from IQC_TestSamplePosition where ... ";
DataSet ds = new DataSet();
using (SqlConnection conn= new SqlConnection("server=000.000.0.000;database=数据库名;user id=登录名;password=密码;Pooling=true"))
{
try
{
conn.Open();
SqlCommand com = new SqlCommand(strVALID, conn);
SqlDataAdapter sda = new SqlDataAdapter(com);
sda.Fill(ds);
}
catch
{
}
finally
{
conn.Close();
}
}
DataTable dt = ds.Tables[0];
DataRow[] temp_valid;
string re = "";
temp_valid = dt.Select(" column= '...'");
if (temp_valid.Length > 0)
{
re+= temp_valid[0]["column"].ToString().Trim();
}
temp_valid = null;
textBox1.Text = re;
excel 数据批量导入数据库指定表
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
namespace inportExcelDataToDB
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/*
*
* 安装NuGet包:Microsoft.Office.Interop.Excel 和 System.Data.SqlClient
* */
private void button1_Click(object sender, EventArgs e)
{
string excelFilePath = @"F:\\destop\\1.xlsx";
string connectionString = "server=000.000.0.000;database=数据库名;user id=登录名;password=密码;Pooling=true";
Application excelApp = new Application();
Workbook excelWorkbook = excelApp.Workbooks.Open(excelFilePath);
Worksheet excelWorksheet = excelWorkbook.Sheets[1];
Range excelRange = excelWorksheet.UsedRange;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
try
{
sqlConnection.Open();
for (int i = 2; i <= excelRange.Rows.Count; i++)
{
string value1 = excelRange.Cells[i, 1].Value2.ToString().Trim();
string value2 = excelRange.Cells[i, 2].Value2.ToString().Trim();
string value3 = excelRange.Cells[i, 3].Value2.ToString().Trim();
string query = $"INSERT INTO TBa(column1,column2,column3) VALUES ('{value1}', '{value2}','{value3}')";
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
excelWorkbook.Close();
excelApp.Quit();
sqlConnection.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString(), "异常信息");
}
finally
{
sqlConnection.Close();
}
}
Console.WriteLine("Data imported successfully.");
}
}
}