你在这里看了吗?
https://www.connectionstrings.com/sql-server/
尝试使用最简单的场景(无安全性)进行测试。
服务器=myServerAddress;数据库=myDataBase;Trusted_Connection=True;
这是一个在我的机器上连接SQL Server数据库的C#脚本。
将Excel转换为DGV:
private void button1_Click(object sender, EventArgs e)
{
DataTable table = new DataTable();
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"",
"C:\\Users\\Ryan\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls");
using (OleDbConnection dbConnection = new OleDbConnection(strConn))
{
using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
dbAdapter.Fill(table);
dataGridView1.DataSource = table;
int rows = table.Rows.Count;
}
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns["FName"].DataPropertyName = table.Columns["FName"].ColumnName;
dataGridView1.Columns["LName"].DataPropertyName = table.Columns["LName"].ColumnName;
dataGridView1.Columns["Age"].DataPropertyName = table.Columns["Age"].ColumnName;
dataGridView1.DataSource = table;
//IF THE ORDER DOSEN'T MATTER
//DataTable table = new DataTable();
//string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls");
//using (OleDbConnection dbConnection = new OleDbConnection(strConn))
//{
// using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
// dbAdapter.Fill(table);
// dataGridView1.DataSource = table;
// int rows = table.Rows.Count;
//}
}
Excel转SQL Server:
private void button3_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls;Extended Properties=Excel 8.0;");
ExcelConnection.Open();
string expr = "SELECT * FROM [Sheet1$]";
OleDbCommand objCmdSelect = new OleDbCommand(expr, ExcelConnection);
OleDbDataReader objDR = null;
SqlConnection SQLconn = new SqlConnection();
string ConnString = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
SQLconn.ConnectionString = ConnString;
SQLconn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLconn))
{
bulkCopy.DestinationTableName = "tblTest";
try
{
objDR = objCmdSelect.ExecuteReader();
bulkCopy.WriteToServer(objDR);
ExcelConnection.Close();
//objDR.Close()
SQLconn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void button4_Click(object sender, EventArgs e)
{
BindGrid();
}
protected void BindGrid()
{
string path = "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls";
string jet = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection conn = new OleDbConnection(jet);
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
BulkUpload();
}
private void button8_Click(object sender, EventArgs e)
{
//SqlConnection connection = new SqlConnection("Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;");
DataTable dt = (DataTable)dataGridView1.DataSource;
string connection = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
using (var conn = new SqlConnection(connection))
{
conn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.ColumnMappings.Add(0, "Fname");
bulkCopy.ColumnMappings.Add(1, "Lname");
bulkCopy.ColumnMappings.Add(2, "Age");
bulkCopy.BatchSize = 10000;
bulkCopy.DestinationTableName = "Import_List";
bulkCopy.WriteToServer(dt.CreateDataReader());
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;
标准安装(无添加配置)SQL Express Server只会激活共享内存协议,您可以通过SQL Server 2014配置管理器进行检查。
这对于从VS进行本地连接(测试)已经足够了。
"...在同一台计算机上运行的客户端连接到Microsoft SQL Server使用共享内存协议。共享内存没有可配置属性。共享内存总是首先尝试,并且不能从启用的协议列表中的顶部位置移动。当故障排除其他协议时,可以禁用共享内存协议。"learn.microsoft.com
要从VS测试连接,您可以使用Data Connection中的Add Connection,数据源为“MS SQL Server”。
主要问题是您必须正确设置服务器名称(默认情况下不正确)<servername>\<instancename>
或别名(localhost\<instancename>
或.\<instancename>
)。安装SQL Server时设置实例名称(通常为SQLEXPRESS)。您可以通过SQL Server Management Studio检查“服务器名称”是否正确(与VS相比,此程序可以更简单地找到它)。将“数据库名称”字段留空,然后简单地按“测试连接”(如果您设置正确,则来自服务器的响应必须立即出现)。
P.S>
使用此SQL语句,您可以检查用于连接的协议:
SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;