点击Excel到SQL Server Express上的c#按钮
本文关键字:上的 按钮 Express Server Excel SQL 点击 | 更新日期: 2023-09-27 18:18:08
在一个按钮上单击我试图导出一个excel文件到SQL Server表。首先,我创建了第一个按钮,它将在点击时创建表格:
private void button1_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=LPMSW09000012JD''SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
string query = "CREATE TABLE [dbo].[" + textBox1.Text + "](" + "[Code] [varchar] (7) NOT NULL," +
"[Description] [varchar] (38) NOT NULL," + "[NDC] [varchar] (12) NULL, " +
"[Supplier Code] [varchar] (38) NOT NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(query, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
我的下一个函数应该是通过OpenFileDialog
抓取excel文件,选择它,然后将相关内容上传到从上面的按钮单击创建的表中。这是函数的重要部分:
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog ope = new OpenFileDialog();
ope.Filter = "Excel Files |*.xlsx;*.xlsm";
if (ope.ShowDialog() == DialogResult.Cancel)
return;
FileStream stream = new FileStream(ope.FileName, FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
DataClasses1DataContext conn = new DataClasses1DataContext();
foreach (DataTable table in result.Tables)
{
foreach (DataRow dr in table.Rows)
{
test addtable = new test()
{
test_id = Convert.ToString(result[0]);
test_name = Convert.ToString(dr[1]);
};
conn.tests.inInsertOnSubmit(addtable);
}
}
}
我的问题是,它将工作,但目前它只工作,如果我创建从我的第一个按钮点击与我的c#代码创建的表的关系。如。假设我的SQL Server表中有名为"test_id"answers"test_name"的列名。然后我必须抓住那个表,把在我的c#代码和匹配的列名。这将是非常不方便的,因为我的目标是用户单击按钮创建一个表,然后导出到该表,每次他们想要从一些记录读取数据。我想从数据库管理中消除交互。我想做的是创建一个表,并导出一个excel文件到同一表,而不必在我的代码之前创建某种关系。附加信息:所有的个人excel表格将有相同的一组列,每次。保持不变。不知道这是否有帮助
我终于让它工作了,对于任何可能遇到这个的人。我所做的只是将我的文件类型更改为。csv文件而不是excel扩展名。Excel的问题太多了。下面是在单击按钮时导出到MSSQL表的函数:
private void button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=LPMSW09000012JD''SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True");
string filepath = textBox2.Text; //"C:''Users''jdavis''Desktop''CRF_105402_New Port Maria Rx.csv";
StreamReader sr = new StreamReader(filepath);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
}
}
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = textBox1.Text;
bc.BatchSize = dt.Rows.Count;
con.Open();
bc.WriteToServer(dt);
bc.Close();
con.Close();
}