“在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: Named Pipes Provider, error: 40 - 无法打开到 SQL Server 的连接)”
无奈打开msdn，看到SqlBulkCopy类的功能介绍直接崩溃：“lets you efficiently bulk load a SQL Server table with data from another source.”。原来SqlBulkCopy只对SQL Server有效啊，这点从它的命名空间（System.Data.SqlClient）大致可以猜到，MS真不厚道。
batchinsert1 /// <summary>
/// 拼接字符串批量插入
/// </summary>
/// <param name="sqlString">sql插入语句，形如INSERT INTO test.Person(FirstName) VALUES或 INSERTINTO test.Person(FirstName)</param>
/// <param name="columes">插入的列数</param>
/// <param name="paramValues">需要插入的值</param>
/// <param name="strConnection">数据库连接字符串</param>
/// <returns></returns>
public static int BatchInsert(string sqlString, int columes, object[] paramValues, string strConnection)
{
if (paramValues.Length % columes != 0)
{
throw new ArgumentException("参数个数有误");
}
StringBuilder sb = new StringBuilder();
sb.Append(sqlString);
if (sqlString.LastIndexOf(" VALUES", StringComparison.OrdinalIgnoreCase) == -1)
{
sb.Append(" VALUES ");
}
introws = paramValues.Length / columes;
for (int i = 0; i < rows; i++)
{
sb.Append("(");
for (int j = 0; j < columes; j++)
{
sb.AppendFormat("'{0}'", paramValues[columes * i + j]);
if (j != columes - 1)
{
sb.Append(",");
}
}
sb.Append("),");
}
int affectNum = ExecuteNonQuery(strConnection, sb.ToString().Trim(','), CommandType.Text, true);//拼接字符串批量插入
return affectNum;
}
调用如下：
testbatchinsert1string sqlString = "INSERT INTO test.Person(FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark) VALUES";
int columes = 7;//需要插入的列数
var listObj = new List<object>();
for (int i = 0; i < 30; i++) //拼接字符串，批量插入30条记录
{
var paramVals = newobject[] { string.Format("jeff{0}", columes * i + j), "wong", 28, DateTime.Now, DateTime.Now, 1, "this is a simple add person test" };
listObj.AddRange(paramVals);
}
int affectNums = MySqlHelper.BatchInsert(sqlString, columes, listObj.ToArray(), strSqlConn);
Console.WriteLine(affectNums);
BATCHINSERT/// <summary>/// 批量插入/// </summary>/// <typeparam name="T"></typeparam>/// <param name="tbName">要插入的目标表名称</param>/// <param name="columeArr">要插入的列名数组</param>/// <param name="listModels">要插入的实体数组</param>/// <param name="strConnection">数据库连接字符串</param>/// <returns></returns>publicstaticint BatchInsert<T>(string tbName, string[] columeArr, IList<T> listModels, string strConnection) where T : class, new()
{
if (listModels == null || listModels.Count == 0)
{
thrownew ArgumentException("没有需要批量插入的数据");
}
int columes = columeArr.Length;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} ", tbName);
AppendColumes(sb, columeArr);
sb.Append(" VALUES ");
var listParamKeys = new List<string>();//参数的键值string paramKey = string.Empty;
for (int i = 0; i < listModels.Count; i++) //构造参数
{
sb.Append("(");
for (int j = 0; j < columes; j++)
{
paramKey = string.Format("@v_{0}_{1}", columeArr[j], columes * i + j); //参数前必须加入@
sb.Append(paramKey);
listParamKeys.Add(paramKey);
if (j < columes - 1)
{
sb.Append(",");
}
}
sb.Append("),");
}
var listParamValues = new List<object>();
for (int i = 0; i < listModels.Count; i++) //构造参数值数组
{
FastPrepareParamValue<T>(listModels[i], columeArr, listParamValues);
}
string sqlText = sb.ToString().Trim(',') + ";";
int affectNum = ExecuteNonQuery(strConnection, sqlText, CommandType.Text, PrepareParameters(listParamKeys.ToArray(), listParamValues.ToArray()), true);//拼接字符串批量插入return affectNum;
}
privatestaticvoid AppendColumes(StringBuilder sb, string[] columeArr)
{
if (columeArr == null || columeArr.Length == 0)
{
thrownew ArgumentException("插入列不能为空");
}
sb.Append("(");
for (int i = 0; i < columeArr.Length; i++)
{
sb.Append(columeArr[i]);
if (i < columeArr.Length - 1)
{
sb.Append(",");
}
}
sb.Append(")");
}
privatestaticvoid FastPrepareParamValue<T>(T model, string[] columeArr, List<object> listPramValues)
{
object objValue = null;
var objType = model.GetType();
var properties = objType.GetProperties(bf);
foreach (var columeName in columeArr)
{
foreach (var propInfo in properties)
{
if (string.Compare(columeName, propInfo.Name, true) != 0)
{
continue;
}
try
{
objValue = propInfo.FastGetValue(model);
}
catch
{
objValue = null;
}
finally
{
listPramValues.Add(objValue);
}
break;
}
}
}
调用方式也比较简洁，如下所示：
TestBatchInsertModelsstring[] columeArr = newstring[] { "FirstName", "LastName", "Age", "CreateDate", "UpdateDate", "State", "Remark" };
var listPersons = new List<Person>();
Person person = null;
for (int i = 0; i < 30; i++)
{
person = new Person
{
FirstName = string.Format("jeff{0}", i.ToString()),
LastName = "wong",
Age = 28,
CreateDate = DateTime.Now,
UpdateDate = DateTime.Now,
State = 1,
Remark = string.Format("this is test person {0} ", i + 1)
};
listPersons.Add(person);
}
int affectNums = MySqlHelper.BatchInsert<Person>("test.Person", columeArr, listPersons, strSqlConn); //参数化插入
Console.WriteLine(affectNums);
querypersonsCREATE DEFINER=`root`@`localhost` PROCEDURE `QueryPersons`(
v_FirstName varchar(16),
v_State int
)
BEGINSELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark
FROM test.Person
WHERE 1=1
AND FirstName LIKE CONCAT('%',v_FirstName,'%')
AND State=v_State;
END
在程序中调用非常简单，正确传参即可：
querypersons var paramKeys = new string[] { "@v_FirstName", "@v_State" };
var paramVals = new object[] { "ff", 1 };
var listPerson = MySqlHelper.QueryForList<Person>("QueryPersons", CommandType.StoredProcedure, strSqlConn, paramKeys, paramVals);
getpagerperson1-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons`(
CurrentPage int, /* 当前页 */
RecordCount int/* 每页记录数 */
)
BEGINIF RecordCount<=1 THENSET RecordCount=20;
ENDIF;
IF CurrentPage < 1 THENSET CurrentPage = 1;
ENDIF;
/* 按条件分页查询 */SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC',' LIMIT '
,CurrentPage*RecordCount-RecordCount,',',RecordCount);
prepare stmtsql from @strsql;
execute stmtsql;
deallocateprepare stmtsql;
/* 查询总记录数 */SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');
prepare stmtsqlcount from @strsqlcount;
execute stmtsqlcount;
deallocateprepare stmtsqlcount;
END
getpagerperson2-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons1`(
BeginRecordNumber int, /* 开始记录 */
EndRecordNumber int/* 结束记录 *//*
如每页记录为10条，则
第一页：BeginRecordNumber：1 EndRecordNumber：10
第二页：BeginRecordNumber：11 EndRecordNumber：20
*/
)
BEGIN/* 按条件分页查询 */SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC',' LIMIT '
,BeginRecordNumber-1,',',EndRecordNumber-BeginRecordNumber+1);
prepare stmtsql from @strsql;
execute stmtsql;
deallocateprepare stmtsql;
/* 查询总记录数 */SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');
prepare stmtsqlcount from @strsqlcount;
execute stmtsqlcount;
deallocateprepare stmtsqlcount;
END
quickpagergetpersons-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons`(
CurrentPage int, /* 当前页 */
RecordCount int, /* 每页记录数 */
MinId int/*前CurrentPage-1页的最小id，CurrentPage>1*/
)
BEGINIF RecordCount<=1 THENSET RecordCount=20;
ENDIF;
IF CurrentPage < 1 THENSET CurrentPage = 1;
ENDIF;
-- 第一页IF CurrentPage=1 THENSET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC LIMIT ',RecordCount);
ENDIF;
-- 第n页(n>1)IF CurrentPage>1 THENSET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person WHERE Id<',MinId,' ORDER BY Id DESC LIMIT ',RecordCount);
ENDIF;
prepare stmtsql from @strsql;
execute stmtsql;
deallocateprepare stmtsql;
/* 查询总记录数数 */SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');
prepare stmtsqlcount from @strsqlcount;
execute stmtsqlcount;
deallocateprepare stmtsqlcount;
END
DeletePersonCREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePerson`(
Id int
)
BEGIN
DELETE FROM Person WHERE Id=Id;
END
获取一条的存储过程如下：
GetPersonByIDCREATE DEFINER=`root`@`localhost` PROCEDURE `GetPersonByID`(
Id int
)
BEGINSELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark
FROM test.Person
WHERE Id=Id;
END
更新一条记录的存储过程如下：
updatepersonCREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePerson`(
Id int,
FirstName varchar(16),
LastName varchar(16),
Age int,
CreateDate datetime,
UpdateDate datetime,
State int,
Remark varchar(256)
)
BEGIN
UPDATE test.Person SET
FirstName=FirstName,
LastName=LastName,
Age=Age,
CreateDate=CreateDate,
UpdateDate=UpdateDate,
State=State,
Remark=Remark
WHERE Id=Id;
END
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构