using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
namespace WOffice.DAL
{
/// 创建人:jack
/// version 1.2
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = "server=.;database=myoffice;uid=sa;pwd=wcj";//ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行一个增删改存储过程(有参)
/// </summary>
/// <param name="procName">存储过程名字</param>
/// <param name="values">参数列表</param>
/// <returns>影响行数</returns>
public static int ExecuteProc(string procName, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int i= cmd.ExecuteNonQuery();
Connection.Close();
return i;
}
/// <summary>
/// 执行一个无参增删改存储过程
/// </summary>
/// <param name="procName">存储过程名字</param>
/// <returns>影响行数</returns>
public static int ExecuteProc(string procName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
int i = cmd.ExecuteNonQuery();
Connection.Close();
return i;
}
/// <summary>
/// 执行一个(无参)增删改语句
/// </summary>
/// <param name="safeSql">语句</param>
/// <returns>影响行数</returns>
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
Connection.Close();
return result;
}
/// <summary>
/// 执行一个有参增删改操作
/// </summary>
/// <param name="sql">语句</param>
/// <param name="values">参数</param>
/// <returns>影响行数 </returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int i = cmd.ExecuteNonQuery();
Connection.Close();
return i;
}
/// <summary>
/// 查询第一行第一列数据(无参)(返回的是什么类型就转换成什么类型)
/// </summary>
/// <param name="safeSql">语句</param>
/// <returns>object</returns>
public static object GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
object obj= cmd.ExecuteScalar();
Connection.Close();
return obj;
}
/// <summary>
/// 查询第一行第一列数据(有参)(返回的是什么类型就转换成什么类型)
/// </summary>
/// <param name="values">参数</param>
/// <returns>object</returns>
public static object GetScalar(string safeSql,params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql,Connection);
cmd.Parameters.AddRange(values);
object obj = cmd.ExecuteScalar();
Connection.Close();
return obj;
}
/// <summary>
/// 返回int
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int GetScalarInt(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int i= Convert.ToInt32(cmd.ExecuteScalar());
Connection.Close();
return i;
}
/// <summary>
/// 返回string
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static string GetScalarString(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
string str= Convert.ToString(cmd.ExecuteScalar());
Connection.Close();
return str;
}
/// <summary>
/// 返回一个Datatable(无参)
/// </summary>
/// <param name="safeSql">语句</param>
/// <returns>DataTable</returns>
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
Connection.Close();
return ds.Tables[0];
}
/// <summary>
/// 返回一个Datatable(有参)
/// </summary>
/// <param name="sql">语句</param>
/// <param name="values">参数</param>
/// <returns>DataTable</returns>
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
Connection.Close();
return ds.Tables[0];
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
SqlTransaction tx = Connection.BeginTransaction();
cmd.Transaction=tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
Connection.Close();
}
}
}
}
C#数据查询帮助类
最新推荐文章于 2024-04-15 00:14:24 发布