zhengxuan.zhang
364 lines
12 KiB
C#
364 lines
12 KiB
C#
using PLCBaseFunction;
|
|
using System;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Windows.Forms;
|
|
|
|
namespace DAL
|
|
{
|
|
public class SQLHelper
|
|
{
|
|
//private static SqlConnection conn = null;
|
|
//private static SqlCommand cmd = null;
|
|
//private static SqlDataReader sdr = null;
|
|
public static string connStr = "";
|
|
|
|
public static int iFlag = 0;
|
|
|
|
private static SqlConnection GetConn()
|
|
{
|
|
var conn = new SqlConnection(connStr);
|
|
if (conn.State == ConnectionState.Closed)
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
if (iFlag == 0)
|
|
{
|
|
iFlag++;//必须放在前面，这样才起左右，放在后面，不会赋值 如果不点击确定的话
|
|
|
|
MyBase.TraceWriteLine(" 数据库打开连接失败" + ex.ToString());
|
|
MessageBox.Show("数据库打开连接失败，请检查数据库是否正确连接！原因：" + ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
|
}
|
|
return conn;
|
|
}
|
|
}
|
|
return conn;
|
|
}
|
|
|
|
#region 执行不带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
|
|
/// <summary>
|
|
/// 执行不带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
/// </summary>
|
|
/// <param name="cmdText">增删改SQL语句或存储过程</param>
|
|
/// <param name="ct">命令类型</param>
|
|
/// <returns>返回受影响的行数</returns>
|
|
//public static int ExecuteNonQuery(string cmdText, CommandType ct)
|
|
//{
|
|
// int res = 0;
|
|
// try
|
|
// {
|
|
// var cmd = new SqlCommand(cmdText, GetConn());
|
|
// cmd.CommandType = ct;
|
|
// res = cmd.ExecuteNonQuery(); //返回受影响的行数
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// throw ex;
|
|
// }
|
|
// finally
|
|
// {
|
|
// if (conn.State == ConnectionState.Open)
|
|
// {
|
|
// conn.Close();
|
|
// }
|
|
// }
|
|
// return res;
|
|
//}
|
|
|
|
public static int ExecuteNonQuery(string cmdText, CommandType ct)
|
|
{
|
|
int res = 0;
|
|
try
|
|
{
|
|
using (var conn = GetConn())
|
|
using (var cmd = new SqlCommand(cmdText, conn))
|
|
{
|
|
cmd.CommandType = ct;
|
|
res = cmd.ExecuteNonQuery(); // 返回受影响的行数
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MyBase.TraceWriteLine("ExecuteNonQuery异常: " + ex.ToString());
|
|
throw; // 建议让上层处理异常
|
|
}
|
|
return res;
|
|
}
|
|
|
|
#endregion 执行不带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
|
|
#region 执行带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
|
|
/// <summary>
|
|
/// 执行带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
/// </summary>
|
|
/// <param name="cmdText">增删改SQL语句或存储过程</param>
|
|
/// <param name="ct">命令类型</param>
|
|
/// <returns>返回受影响的行数</returns>
|
|
public static int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
|
|
{
|
|
int res = 0;
|
|
|
|
using (var cmd = new SqlCommand(cmdText, GetConn()))
|
|
{
|
|
cmd.CommandType = ct;
|
|
cmd.Parameters.AddRange(paras);
|
|
res = cmd.ExecuteNonQuery();
|
|
}
|
|
return res;
|
|
}
|
|
|
|
#endregion 执行带参数的增删改SQL语句或存储过程 返回int类型 返回受影响的行数
|
|
|
|
#region 执行不带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
|
|
/// <summary>
|
|
/// 执行不带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
/// </summary>
|
|
/// <param name="cmdText">查询SQL语句或存储过程</param>
|
|
/// <param name="ct">命令类型</param>
|
|
/// <returns>DataTable型</returns>
|
|
public static DataTable ExecuteQuery(string cmdText, CommandType ct)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var cmd = new SqlCommand(cmdText, GetConn());
|
|
cmd.CommandType = ct;
|
|
//cmd.CommandTimeout = 60;
|
|
|
|
using (var sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
|
|
{
|
|
dt.Load(sdr);
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
#endregion 执行不带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
|
|
#region 执行带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
|
|
/// <summary>
|
|
/// 执行带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
/// </summary>
|
|
/// <param name="cmdText">查询SQL语句或存储过程</param>
|
|
/// <param name="paras">参数集合</param>
|
|
/// <param name="ct">命令类型</param>
|
|
/// <returns>DataTable型</returns>
|
|
public static DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var cmd = new SqlCommand(cmdText, GetConn());
|
|
cmd.CommandType = ct;
|
|
cmd.Parameters.AddRange(paras);
|
|
using (var sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
|
|
{
|
|
dt.Load(sdr);
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
#endregion 执行带参数的查询SQL语句或存储过程 返回DataTable类型
|
|
|
|
/// <summary>
|
|
/// 执行SQL语句并返回DataSet
|
|
/// </summary>
|
|
/// <param name="Sqlstr">SQL语句</param>
|
|
/// <returns></returns>
|
|
public static DataSet ExecuteDs(String Sqlstr)
|
|
{
|
|
using (SqlDataAdapter da = new SqlDataAdapter(Sqlstr, GetConn()))
|
|
{
|
|
DataSet ds = new DataSet();
|
|
da.Fill(ds);
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
|
|
/// </summary>
|
|
/// <param name="connection">数据库连接</param>
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
/// <param name="parameters">存储过程参数</param>
|
|
/// <returns>SqlCommand</returns>
|
|
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, SqlParameter[] parameters)
|
|
{
|
|
SqlCommand command = new SqlCommand(storedProcName, connection);
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
foreach (SqlParameter parameter in parameters)
|
|
{
|
|
if (parameter != null)
|
|
{
|
|
// 检查未分配值的输出参数,将其分配以DBNull.Value.
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
|
|
{
|
|
parameter.Value = DBNull.Value;
|
|
}
|
|
command.Parameters.Add(parameter);
|
|
}
|
|
}
|
|
|
|
return command;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行存储过程
|
|
/// </summary>
|
|
/// <param name="storedProcName">存储过程名</param>
|
|
/// <param name="parameters">存储过程参数</param>
|
|
/// <param name="tableName">DataSet结果中的表名</param>
|
|
/// <returns>DataSet</returns>
|
|
public static DataSet RunProcedure(string storedProcName, SqlParameter[] parameters, string tableName)
|
|
{
|
|
using (SqlConnection connection = new SqlConnection(connStr))
|
|
{
|
|
DataSet dataSet = new DataSet();
|
|
connection.Open();
|
|
SqlDataAdapter sqlDA = new SqlDataAdapter();
|
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
|
sqlDA.Fill(dataSet, tableName);
|
|
connection.Close();
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
#region 使用SqlBulkCopy插入测量数据
|
|
|
|
/// <summary>
|
|
/// 要插入的数据表的结构，与函数内部定义的映射表要一模一样
|
|
/// </summary>
|
|
/// <param name="InsertDT">要插入的数据表</param>
|
|
public static int InsertMeasureDataToDB(DataTable InsertDT)
|
|
{
|
|
int iResult = 1;
|
|
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(GetConn()))
|
|
{
|
|
try
|
|
{
|
|
bulkCopy.DestinationTableName = "TMeasureData";//要插入的表的表明，创造映射关系，比下面的直接写表名称 更加灵活
|
|
bulkCopy.ColumnMappings.Add("CarID", "CarID");//映射字段名 DataTable列名 ,数据库 对应的列名
|
|
bulkCopy.ColumnMappings.Add("CarType", "CarType");//映射字段名 DataTable列名 ,数据库 对应的列名
|
|
bulkCopy.ColumnMappings.Add("MeasPointName", "MeasPointName");
|
|
bulkCopy.ColumnMappings.Add("DimensionName", "DimensionName");
|
|
bulkCopy.ColumnMappings.Add("NormalValue", "NormalValue");
|
|
bulkCopy.ColumnMappings.Add("LowerTolVal", "LowerTolVal");
|
|
bulkCopy.ColumnMappings.Add("UpperTolVal", "UpperTolVal");
|
|
bulkCopy.ColumnMappings.Add("MeasureValue", "MeasureValue");
|
|
bulkCopy.ColumnMappings.Add("MeasureItemResult", "MeasureItemResult");
|
|
bulkCopy.ColumnMappings.Add("MeasureDate", "MeasureDate");
|
|
bulkCopy.ColumnMappings.Add("Remark", "Remark");
|
|
bulkCopy.WriteToServer(InsertDT);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MessageBox.Show("批量插入测量数据到数据库失败！原因：" + ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|
iResult = -1;
|
|
}
|
|
}
|
|
return iResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 要插入的数据表的结构，与函数内部定义的映射表要一模一样
|
|
/// </summary>
|
|
/// <param name="InsertDT">要插入的数据表</param>
|
|
public static int InsertRangeDataToDB(DataTable InsertDT)
|
|
{
|
|
int iResult = 1;
|
|
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(GetConn()))
|
|
{
|
|
try
|
|
{
|
|
bulkCopy.DestinationTableName = "TRangeData";//要插入的表的表明，创造映射关系，比下面的直接写表名称 更加灵活
|
|
bulkCopy.ColumnMappings.Add("CarID", "CarID");//映射字段名 DataTable列名 ,数据库 对应的列名
|
|
bulkCopy.ColumnMappings.Add("RangeName", "RangeName");//映射字段名 DataTable列名 ,数据库 对应的列名
|
|
bulkCopy.ColumnMappings.Add("RangeValue", "RangeValue");
|
|
bulkCopy.ColumnMappings.Add("RangeLowUpp", "RangeLowUpp");
|
|
bulkCopy.ColumnMappings.Add("Result", "Result");
|
|
bulkCopy.ColumnMappings.Add("CreateTime", "CreateTime");
|
|
bulkCopy.ColumnMappings.Add("Remark", "Remark");
|
|
bulkCopy.WriteToServer(InsertDT);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MessageBox.Show("批量插入RangeData数据到数据库失败！原因：" + ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
|
iResult = -1;
|
|
}
|
|
}
|
|
return iResult;
|
|
}
|
|
|
|
#endregion 使用SqlBulkCopy插入测量数据
|
|
|
|
#region 使用SqlBulkCopy插入批量数据方法
|
|
|
|
/// <summary>
|
|
/// 要插入的数据表的结构，与函数内部定义的映射表要一模一样
|
|
/// </summary>
|
|
/// <param name="InsertDT">要插入的数据表</param>
|
|
public static void TWorkpieceListToSQLServer(DataTable InsertDT)
|
|
{
|
|
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(GetConn()))
|
|
{
|
|
try
|
|
{
|
|
bulkCopy.DestinationTableName = "TWorkpieceList";//要插入的表的表明，创造映射关系，比下面的直接写表名称 更加灵活
|
|
bulkCopy.ColumnMappings.Add("WorkpieceID", "WorkpieceID");//映射字段名 DataTable列名 ,数据库 对应的列名
|
|
bulkCopy.ColumnMappings.Add("DrawerID", "DrawerID");
|
|
bulkCopy.ColumnMappings.Add("WorkpieceType", "WorkpieceType");
|
|
bulkCopy.ColumnMappings.Add("TrayType", "TrayType");
|
|
bulkCopy.ColumnMappings.Add("WorkpieceStatus", "WorkpieceStatus");
|
|
bulkCopy.ColumnMappings.Add("WorkpiecePos", "WorkpiecePos");
|
|
bulkCopy.WriteToServer(InsertDT);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
// Close the SqlDataReader. The SqlBulkCopy
|
|
// object is automatically closed at the end
|
|
// of the using block.
|
|
}
|
|
}
|
|
}
|
|
|
|
#endregion 使用SqlBulkCopy插入批量数据方法
|
|
|
|
#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
|
|
|
|
/// <summary>
|
|
/// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中,用此函数，创建的InsertDataTable类型必须跟数据库中的类型，列数一模一样
|
|
/// </summary>
|
|
/// <param name="strDBTableName">数据库中对应的表名</param>
|
|
/// <param name="InsertDataTable">数据集</param>
|
|
public static void SqlBulkCopyInsert(string strDBTableName, DataTable InsertDataTable)
|
|
{
|
|
try
|
|
{
|
|
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(GetConn()))//引用SqlBulkCopy
|
|
{
|
|
sqlRevdBulkCopy.DestinationTableName = strDBTableName;//数据库中对应的表名
|
|
|
|
sqlRevdBulkCopy.NotifyAfter = InsertDataTable.Rows.Count;//有几行数据
|
|
|
|
sqlRevdBulkCopy.WriteToServer(InsertDataTable);//数据导入数据库
|
|
|
|
sqlRevdBulkCopy.Close();//关闭连接
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine("数据库处理出错，SqlBulkCopyInsert，原因：" + ex.Message);
|
|
throw (ex);
|
|
}
|
|
}
|
|
|
|
#endregion 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
|
|
}
|
|
} |