CJLR-Analysis/Analysis/DAL/SQLHelper.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace NSAnalysis.DAL
{
	public class SQLHelper
	{
		public static string connStr = "";

		public static int iFlag = 0;

		#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)
		{
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (var cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = ct;
					return cmd.ExecuteNonQuery();
				}
			}
		}

		#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)
		{
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (var cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = ct;
					cmd.Parameters.AddRange(paras);
					return cmd.ExecuteNonQuery();
				}
			}
		}

		#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();
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (var cmd = new SqlCommand(cmdText, conn))
				{
					cmd.CommandType = ct;
					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();
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (var cmd = new SqlCommand(cmdText, conn))
				{
					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 (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn))
				{
					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 (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
				{
					try
					{
						bulkCopy.DestinationTableName = "TMeasureData";
						bulkCopy.ColumnMappings.Add("CarID", "CarID");
						bulkCopy.ColumnMappings.Add("CarType", "CarType");
						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;
		}

		#endregion 使用SqlBulkCopy插入测量数据

		#region 使用SqlBulkCopy插入批量数据方法

		/// <summary>
		/// 要插入的数据表的结构，与函数内部定义的映射表要一模一样
		/// </summary>
		/// <param name="InsertDT">要插入的数据表</param>
		public static void TWorkpieceListToSQLServer(DataTable InsertDT)
		{
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
				{
					try
					{
						bulkCopy.DestinationTableName = "TWorkpieceList";
						bulkCopy.ColumnMappings.Add("WorkpieceID", "WorkpieceID");
						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);
					}
				}
			}
		}

		#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)
		{
			using (var conn = new SqlConnection(connStr))
			{
				conn.Open();
				using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(conn))
				{
					sqlRevdBulkCopy.DestinationTableName = strDBTableName;
					sqlRevdBulkCopy.NotifyAfter = InsertDataTable.Rows.Count;
					sqlRevdBulkCopy.WriteToServer(InsertDataTable);
				}
			}
		}

		#endregion 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
	}
}