using Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
public static class SqlHelper
{
//public static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString();
public static string conStr = "server=***;uid=sa;pwd=******;database=TripMaster";
/// <summary>
/// 用一个sql语句和可变的SQLParameter数组增删改数据库的数据并且返回受影响的行数，当受影响行数为0时返回的是负数
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <param name="sqlParams">params形的变量数组</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string cmdStr, params SqlParameter[] sqlParams)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(cmdStr, con))
{
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 使用一个sql语句来查找数据库，返回查找数据的第一行第一列的数据
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <param name="sqlParams">params形式的SqlParameters数组</param>
/// <returns>返回所查找信息第一行第一列的数据，类型未知，所以用object接收</returns>
public static object ExecuteScalar(string cmdStr, params SqlParameter[] sqlParams)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(cmdStr, con))
{
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 使用一个sql语句查找数据返回一个dataTable；
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <param name="sqlParams">params形式的SqlParameter数组</param>
/// <returns>返回一张表DataTable</returns>
public static DataTable ExecuteDataTable(string cmdStr, params SqlParameter[] sqlParams)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(cmdStr, con))
{
con.Open();
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 使用一个sql语句查找数据库信息，返回一个SqldataReader，封装这一个方法时要注意，cmd是可以关闭的，
/// 而适配器在读取数据的时候con是不能关闭的
/// 所以要在适配器的创建里面加上参数System.Data.CommandBehavior.CloseConnection，
/// 这个参数的意思是当适配器关闭的时候顺便也关闭con，就是con直到不再从数据库读取数据时才可以关闭
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <param name="sqlParams">params形式的SqlParameters数组</param>
/// <returns>返回一个SqlDatareader可以进行读取数据</returns>
public static SqlDataReader ExecuteReader(string cmdStr, params SqlParameter[] sqlParams)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(cmdStr, con))
{
if (sqlParams != null)
{
cmd.Parameters.AddRange(sqlParams);
}
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return reader;
}
catch
{
con.Close();
con.Dispose();
throw;//不抛出这个就会报错
}
finally
{
}
}
}
public static int ExcuteAddMany(string cmdStr, string[] tempArray, params SqlParameter[] sqlParams)
{
DataTable table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("LayerNum");
for (int i=0; i<tempArray.Length;i++)
{
DataRow row = table.NewRow();
row["Name"] = tempArray[i].ToString();
row["LayerNum"] = 0;
table.Rows.Add(row);
}
string connectionStr = "server=PC-201701130905\B;uid=sa;pwd=s07j08b11;database=TripMaster";
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr))
{
bulkCopy.BatchSize = table.Rows.Count;
bulkCopy.DestinationTableName = "SQLBulkCopyTest";//在插入的目标表
//DataTable列名与数据库列名的映射
bulkCopy.ColumnMappings.Add("LayerNum", "LayerNum");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.WriteToServer(table);//写入到数据库中
}
return 1;
}
}
}