1，SQLHelper帮助类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;//引入读取配置文件的命名空间
namespace DAL.Helper
{
/// <summary>
/// 通用数据访问类
/// </summary>
public class SQLHelper
{
// private static string connString = "Server=aaaa\sqlexpress;DataBase=StudentManageDB;Uid=sa;Pwd=password01!";
//public static readonly string connString = Common.StringSecurity.DESDecrypt(ConfigurationManager.ConnectionStrings["connString"].ToString());
static string connString = "Server=.;DataBase=TighteningResultDB;Uid=sa;Pwd=123";
/// <summary>
/// 执行增、删、改方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//将错误信息写入日志...
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行单一结果（select）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
//将错误信息写入日志...
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行结果集查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
conn.Close();
//将错误信息写入日志...
throw ex;
}
}
/// <summary>
/// 执行查询返回一个DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);//创建数据适配器对象
DataSet ds = new DataSet();//创建一个内存数据集
try
{
conn.Open();
da.Fill(ds);//使用数据适配器填充数据集
return ds;
}
catch (Exception ex)
{
//将错误信息写入日志...
throw ex;
}
finally
{
conn.Close();
}
}
}
}
2，插入一条数据：
public int AddTighteningResult(PMOpenProtocol.TighteningResultData data)
{
//【1】编写SQL语句
StringBuilder sqlBuilder = new StringBuilder();//如果字符串比较长，可以用StringBuilder
sqlBuilder.Append("insert into TighteningResult(TighteningID,ProductSN,StationCode,StationName,BoltNumber,TighteningStatus,ResultDateTime,FinalTouque,FinalAngle,OperateDateTime,OperateFlat,ErrorInfo)");
sqlBuilder.Append(" values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}')");
//【2】解析对象
string sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_1, data.t_D_TIGHTENING_STATUS_1, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_1, data.t_D_ANGLE_1, "", 0, "");
//【3】提交到数据库
try
{
return SQLHelper.Update(sql);
}
catch (SqlException ex)
{
throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
sql语句：
insert into TighteningResult(TighteningID,ProductSN,StationCode,StationName,BoltNumber,TighteningStatus,ResultDateTime,FinalTouque,FinalAngle,OperateDateTime,OperateFlat,ErrorInfo) values('','','','Stn 01','01','NG','2021/8/29 23:05:42','4.1983','0','',0,'')
3，插入多条数据：
搜索到的方法：使用UNION ALL来进行插入操作：
代码如下:
INSERT INTO MyTable(ID,NAME)
SELECT 4,'000'
UNION ALL
SELECT 5,'001'
UNION ALL
SELECT 6,'002'
SELECT 4,'000'
UNION ALL
SELECT 5,'001'
UNION ALL
SELECT 6,'002'
实现上面的方法：
public int AddMultiTighteningResult(PMOpenProtocol.TighteningResultData data)
{
//【1】编写SQL语句
StringBuilder sqlBuilder = new StringBuilder();//如果字符串比较长，可以用StringBuilder
sqlBuilder.Append("insert into TighteningResult(TighteningID,ProductSN,StationCode,StationName,BoltNumber,TighteningStatus,ResultDateTime,FinalTouque,FinalAngle,OperateDateTime,OperateFlat,ErrorInfo)");
sqlBuilder.Append(" select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
//【2】解析对象
string sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_1, data.t_D_TIGHTENING_STATUS_1, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_1, data.t_D_ANGLE_1, "", 0, "");
if (data.t_D_Number_of_Bolts >= 2)
{
sqlBuilder=new StringBuilder(sql);
sqlBuilder.Append("union all select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_2, data.t_D_TIGHTENING_STATUS_2, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_2, data.t_D_ANGLE_2, "", 0, "");
}
if (data.t_D_Number_of_Bolts >= 3)
{
sqlBuilder = new StringBuilder(sql);
sqlBuilder.Append("union all select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_3, data.t_D_TIGHTENING_STATUS_3, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_3, data.t_D_ANGLE_3, "", 0, "");
}
if (data.t_D_Number_of_Bolts >= 4)
{
sqlBuilder = new StringBuilder(sql);
sqlBuilder.Append("union all select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_4, data.t_D_TIGHTENING_STATUS_4, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_4, data.t_D_ANGLE_4, "", 0, "");
}
if (data.t_D_Number_of_Bolts >= 5)
{
sqlBuilder = new StringBuilder(sql);
sqlBuilder.Append("union all select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_5, data.t_D_TIGHTENING_STATUS_5, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_5, data.t_D_ANGLE_5, "", 0, "");
}
if (data.t_D_Number_of_Bolts >= 6)
{
sqlBuilder = new StringBuilder(sql);
sqlBuilder.Append("union all select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},'{11}'");
sql = string.Format(sqlBuilder.ToString(),
"", "", "", data.StationName, data.OrdinalBoltNumber_6, data.t_D_TIGHTENING_STATUS_6, Convert.ToDateTime(data.t_D_REAL_TIME), data.t_D_TORQUE_6, data.t_D_ANGLE_6, "", 0, "");
}
//【3】提交到数据库
try
{
return SQLHelper.Update(sql);
}
catch (SqlException ex)
{
throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
sql语句：
insert into TighteningResult(TighteningID,ProductSN,StationCode,StationName,BoltNumber,TighteningStatus,ResultDateTime,FinalTouque,FinalAngle,OperateDateTime,OperateFlat,ErrorInfo)
select '','','','Stn 01','01','NG','2021/8/29 23:09:20','4.1983','0','',0,''
union all select '','','','Stn 01','02','NG','2021/8/29 23:09:20','0','0','',0,''
union all select '','','','Stn 01','03','OK','2021/8/29 23:09:20','475.19','360.791','',0,''
union all select '','','','Stn 01','04','NG','2021/8/29 23:09:20','4.5254','0','',0,''
union all select '','','','Stn 01','05','NG','2021/8/29 23:09:20','4.6731','0','',0,''
union all select '','','','Stn 01','06','NG','2021/8/29 23:09:20','3.9974','0','',0,''