C#數(shù)據(jù)庫幫助類SqlHelper
2018-07-20 來源：open-open
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
namespace DAL
{
public class SqlHelper
{
public static string connstring = ConfigurationManager.ConnectionStrings["zzconstr"].ConnectionString;
//public static string connstring = "Data Source=ZZ-PC;Initial Catalog=IPTVDB;User ID=sa;Password=sa";
/// <summary>
/// 執(zhí)行非查詢，返回受影響行數(shù),異常返回-1；
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static bool ExceNonQuery(string sql, CommandType type, IDataParameter[] pars)
{
SqlConnection con = new SqlConnection(connstring);
SqlCommand com = new SqlCommand(sql, con);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter pp in pars)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
try
{
con.Open();
int t = com.ExecuteNonQuery();
if (t > 0)
{
return true;
}
else
return false;
}
catch (Exception e) { return false; }
finally
{
com.Parameters.Clear();
com.Dispose();
con.Close();
}
}
/// <summary>
/// 執(zhí)行sql語句的查詢，返回查詢的數(shù)量。異常返回-1.
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static int ExceQuery(string sql, CommandType type, IDataParameter[] pars)
{
SqlConnection con = new SqlConnection(connstring);
SqlCommand com = new SqlCommand(sql, con);
com.CommandType = type;
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter pp in pars)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
try
{
con.Open();
if (com.ExecuteScalar() != null)//查詢結(jié)果為空時返回0
{
int t = (int)com.ExecuteScalar();
return t;
}
else
return -1;
}
catch (Exception e) { return -1; }
finally
{
com.Parameters.Clear();
com.Dispose();
con.Close();
}
}
/// <summary>
/// 執(zhí)行查詢，返回一個數(shù)據(jù)集
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public static DataSet ExcueReturnDataset(string sql, IDataParameter[] pars)
{
SqlConnection con = new SqlConnection(connstring);
DataSet set = new DataSet();
SqlCommand com = new SqlCommand(sql, con);
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter pp in pars)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
SqlDataAdapter adpter = new SqlDataAdapter(com);
try
{
set.Clear();
adpter.Fill(set);
return set;
}
catch (Exception ex) { return null; }
finally {
com.Parameters.Clear();
com.Dispose();
con.Close();
}
}
public static DataSet ExcueReturnDataset(string sql,CommandType type,IDataParameter[] pars)
{
SqlConnection con = new SqlConnection(connstring);
DataSet set = new DataSet();
SqlCommand com = new SqlCommand(sql, con);
com.CommandType = type;
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter pp in pars)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
SqlDataAdapter adpter = new SqlDataAdapter(com);
try
{
set.Clear();
adpter.Fill(set);
return set;
}
catch (Exception ex) { return null; }
finally
{
com.Parameters.Clear();
com.Dispose();
con.Close();
}
}
public static IDataReader ExcueReturnDataReader(string sql, IDataParameter[] pars)
{
SqlConnection con = new SqlConnection(connstring);
SqlCommand com = new SqlCommand(sql, con);
SqlDataReader reader;
if (pars != null && pars.Length > 0)
{
foreach (SqlParameter pp in pars)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
try
{
con.Open();
reader = com.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex)
{
return null;
}
finally {
com.Parameters.Clear();
com.Dispose();
//con.Close();
}
}
/// <summary>
/// 執(zhí)行存儲過程，返回影響的行數(shù)
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數(shù)</param>
/// <param name="rowsAffected">影響的行數(shù)</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connstring))
{
int result;
connection.Open();
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters!= null && parameters.Length > 0)
{
foreach (SqlParameter pp in parameters)//把參數(shù)集全部加進(jìn)去
command.Parameters.Add(pp);
}
command.Parameters.Add("@return","").Direction = ParameterDirection.ReturnValue;
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["@return"].Value;
connection.Close();
return result;
}
}
/// <summary>
/// 執(zhí)行存儲過程
/// </summary>
/// <param name="storedProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數(shù)</param>
/// <param name="tableName">DataSet結(jié)果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connstring))
{
DataSet dataSet = new DataSet();
SqlCommand com = new SqlCommand(storedProcName, connection);
com.CommandType =CommandType.StoredProcedure;
if (parameters != null && parameters.Length > 0)
{
foreach (SqlParameter pp in parameters)//把參數(shù)集全部加進(jìn)去
com.Parameters.Add(pp);
}
SqlDataAdapter adpter = new SqlDataAdapter(com);
adpter.Fill(dataSet, tableName);
return dataSet;
}
}
/// <summary>
/// 執(zhí)行查詢語句，返回DataSet
/// </summary>
/// <param name="SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connstring))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
}
標(biāo)簽： isp
版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。
最新資訊
熱門推薦