using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace utility
{
static public class DBhelp
{
private static SqlConnection connection;
public static string connectionString = "Data Source=.;Initial Catalog=gydata_backup_2017_02_06_000001_6794231;User ID=sa;Password=123456";
/// <summary>
/// 执行 T-SQL 语句,并返回所受影响的行数
/// </summary>
/// <param name="safeSql">Sql语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommand(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行 T-SQL 语句,并返回所受影响的行数
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values">参数集合</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommand(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery(); ;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
// SqlCommand cmd = new SqlCommand(sql, Connection);
//cmd.Parameters.AddRange(values);
// return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行查询,并返回所查询的结果集中的第一行中第一列的值,忽略其他行和列
/// </summary>
/// <param name="safeSql">Sql语句</param>
/// <returns>返回查询结果</returns>
public static int ExecuteScalar(string SQLString)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
int rows = Convert.ToInt32(cmd.ExecuteScalar());
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
// SqlCommand cmd = new SqlCommand(safeSql, Connection);
//return Convert.ToInt32(cmd.ExecuteScalar());
}
/// <summary>
/// 执行查询,并返回所查询的结果集中的第一行中第一列的值,忽略其他行和列
/// </summary>
/// <param name="sql"></param>
/// <param name="values">参数集合</param>
/// <returns>返回查询结果</returns>
public static int ExecuteScalar(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
cmd.Parameters.AddRange(values);
int rows = Convert.ToInt32(cmd.ExecuteScalar());
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 返回 SqlDataReader 的对象
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connections = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connections);
try
{
connections.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
connections.Close();
throw new Exception(e.Message);
}
// SqlCommand cmd = new SqlCommand(safeSql, Connection);
// SqlDataReader reader = cmd.ExecuteReader();
//return reader;
}
/// <summary>
/// 返回 SqlDataReader 的对象
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] values)
{
SqlConnection connections = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connections);
try
{
connections.Open();
cmd.Parameters.AddRange(values);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
// connections.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataTable GetDataSet(string SQLString)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
DataTable tb = new DataTable();
try
{
DataSet ds = new DataSet();
connections.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connections);
command.Fill(ds, "ds");
tb = ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return tb;
}
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values">参数集合</param>
/// <returns></returns>
public static DataTable GetDataSet(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
DataTable tb = new DataTable();
try
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(SQLString, connections);
//connections.Open();
cmd.Parameters.AddRange(values);
SqlDataAdapter command = new SqlDataAdapter(cmd);
command.Fill(ds, "ds");
tb = ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return tb;
}
}
}
}
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace utility
{
static public class DBhelp
{
private static SqlConnection connection;
public static string connectionString = "Data Source=.;Initial Catalog=gydata_backup_2017_02_06_000001_6794231;User ID=sa;Password=123456";
/// <summary>
/// 执行 T-SQL 语句,并返回所受影响的行数
/// </summary>
/// <param name="safeSql">Sql语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommand(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行 T-SQL 语句,并返回所受影响的行数
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values">参数集合</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteCommand(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery(); ;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
// SqlCommand cmd = new SqlCommand(sql, Connection);
//cmd.Parameters.AddRange(values);
// return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行查询,并返回所查询的结果集中的第一行中第一列的值,忽略其他行和列
/// </summary>
/// <param name="safeSql">Sql语句</param>
/// <returns>返回查询结果</returns>
public static int ExecuteScalar(string SQLString)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
int rows = Convert.ToInt32(cmd.ExecuteScalar());
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
// SqlCommand cmd = new SqlCommand(safeSql, Connection);
//return Convert.ToInt32(cmd.ExecuteScalar());
}
/// <summary>
/// 执行查询,并返回所查询的结果集中的第一行中第一列的值,忽略其他行和列
/// </summary>
/// <param name="sql"></param>
/// <param name="values">参数集合</param>
/// <returns>返回查询结果</returns>
public static int ExecuteScalar(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connections))
{
try
{
connections.Open();
cmd.Parameters.AddRange(values);
int rows = Convert.ToInt32(cmd.ExecuteScalar());
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connections.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 返回 SqlDataReader 的对象
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connections = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connections);
try
{
connections.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
connections.Close();
throw new Exception(e.Message);
}
// SqlCommand cmd = new SqlCommand(safeSql, Connection);
// SqlDataReader reader = cmd.ExecuteReader();
//return reader;
}
/// <summary>
/// 返回 SqlDataReader 的对象
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] values)
{
SqlConnection connections = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connections);
try
{
connections.Open();
cmd.Parameters.AddRange(values);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
// connections.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataTable GetDataSet(string SQLString)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
DataTable tb = new DataTable();
try
{
DataSet ds = new DataSet();
connections.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connections);
command.Fill(ds, "ds");
tb = ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return tb;
}
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="values">参数集合</param>
/// <returns></returns>
public static DataTable GetDataSet(string SQLString, params SqlParameter[] values)
{
using (SqlConnection connections = new SqlConnection(connectionString))
{
DataTable tb = new DataTable();
try
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(SQLString, connections);
//connections.Open();
cmd.Parameters.AddRange(values);
SqlDataAdapter command = new SqlDataAdapter(cmd);
command.Fill(ds, "ds");
tb = ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return tb;
}
}
}
}