using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
public class SqlClass
{
protected SqlConnection Connection;
protected string connectionstring;
public SqlClass()
{
connectionstring = "Server=(local);Database=LightA;uid=sa;pwd=;";
Connection = new SqlConnection(connectionstring);
}
public SqlClass(string connstring)
{
connectionstring = connstring;
Connection = new SqlConnection(connectionstring);
}
/// <summary>
/// 得到SQL连接
/// </summary>
public SqlConnection GetConnection()
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
return Connection;
}
public void CloseConnection()
{
if (Connection.State != ConnectionState.Closed)
Connection.Close();
}
/*执行返回结果和不返回结果的存储过程方法*/
/// <summary>
/// 根据存储过程名称和参数生成对应的SQL命令对象
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
private SqlCommand BuilderQueryCommand(string storedProcName, SqlParameter[] parameters)
{
SqlCommand command = new SqlCommand();
command.Connection = Connection;
command.CommandText = storedProcName.Trim();
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter p in parameters)
{
command.Parameters.Add(p);
}
}
return command;
}
/// <summary>
/// 返回结果的存储过程
/// </summary>
public SqlDataReader GetDataReaderByProc(string storedProcName, SqlParameter[] parameters)
{
SqlDataReader reader;
SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
/// <summary>
/// 不返回结果的存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">参数值</param>
/// <returns></returns>
public int GetEffect(string storedProcName, SqlParameter[] parameters)
{
int result = 0;
try
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
cmd.Transaction = trans;
result = cmd.ExecuteNonQuery();
trans.Commit();
Connection.Close();
return result;
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
return result;
}
throw ex;
}
finally
{
if (trans != null)
{
trans.Dispose();
}
Connection.Close();
}
}
catch (Exception ex1)
{
Debug.WriteLine(ex1.ToString());
return 0;
//throw new Exception(ex1.Message);
}
}
/// <summary>
/// 返回dateSet
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, string tableName)
{
try
{
DataSet ds = new DataSet();
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
SqlDataAdapter myDa = new SqlDataAdapter();
myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
myDa.Fill(ds, tableName);
return ds;
}
catch
{
return null;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 自定义分页
/// </summary>
/// <param name="ProcName">存储过程名</param>
/// <param name="parameters">参数名</param>
/// <param name="start">起始页</param>
/// <param name="maxRecord">记录数</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataSet GetDataSetByProc(string ProcName, SqlParameter[] parameters, int start, int maxRecord, string tableName)
{
try
{
DataSet ds = new DataSet();
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
Connection.Open();
SqlDataAdapter myDa = new SqlDataAdapter();
myDa.SelectCommand = BuilderQueryCommand(ProcName, parameters);
myDa.Fill(ds, start, maxRecord, tableName);
return ds;
}
catch
{
Connection.Close();
return null;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 返回object类型 比如首行首列
/// </summary>
/// <param name="storedProcName">存储过程名称</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public object GetObjectByProc(string storedProcName, SqlParameter[] parameters)
{
object result = null;
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
// SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = BuilderQueryCommand(storedProcName, parameters);
// cmd.Transaction = trans;
result = cmd.ExecuteScalar();
// trans.Commit();
Connection.Close();
return result;
}
catch
{
// trans.Rollback();
return result;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 通过ID号操作某条记录,比如删除ID号的记录
/// </summary>
/// <param name="ProcName">存储过程名</param>
/// <param name="id">值</param>
/// <param name="paraname">参数名如:@ID</param>
/// <returns></returns>
public int RunProcByID(string ProcName, int id, string paraname)
{
try
{
SqlParameter[] p ={ new SqlParameter(paraname, SqlDbType.Int) };
p[0].Value = id;
return GetEffect(ProcName, p);
}
catch
{
Connection.Close();
return 0;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 修改ID号为keyValue的记录的ParaDieldName字段的值为FiledValue
/// </summary>
/// <param name="ProcName">相信过程名</param>
/// <param name="keyValue">ID号的值</param>
/// <param name="paraKeyName">ID号对应的字段名</param>
/// <param name="paraFieldName">要修改的字段名</param>
/// <param name="FieldValue">要修改字段的值</param>
/// <param name="length">字段的长度</param>
/// <returns></returns>
public int UpdateByID(string ProcName, int keyValue, string paraKeyName, string paraFieldName, string FieldValue, int length)
{
try
{
SqlParameter[] p ={ new SqlParameter(paraFieldName,SqlDbType.VarChar,length),
new SqlParameter(paraFieldName, SqlDbType.Int)
};
p[0].Value = FieldValue;
p[1].Value = keyValue;
return GetEffect(ProcName, p);
}
catch
{
Connection.Close();
return 0;
}
finally
{
Connection.Close();
}
}
#region 执行SQL,返回DataSet
/// <summary>
/// 执行SQL语句返回DataSet
/// </summary>
public DataSet GetDataSet(string strSql)
{
try
{
DataSet ds = new DataSet();
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
SqlDataAdapter myDa = new SqlDataAdapter();
myDa.SelectCommand = new SqlCommand(strSql, Connection);
myDa.Fill(ds);
return ds;
}
catch
{
return null;
}
finally
{
Connection.Close();
}
}
#endregion
#region 执行SQL,返回影响行数
/// <summary>
/// 执行SQL语句返回影响行数
/// </summary>
public int RunSql(string strSql)
{
int effect = 0;
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.Transaction = trans;
effect = cmd.ExecuteNonQuery();
trans.Commit();
return effect;
}
catch
{
trans.Rollback();
Connection.Close();
return effect;
}
finally
{
Connection.Close();
}
}
#endregion
#region 执行SQL语句返回SqlDataReader
/// <summary>
/// 执行SQL语句返回SqlDataReader
/// </summary>
public SqlDataReader GetDataReader(string strsql)
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
try
{
SqlCommand cmd = new SqlCommand(strsql, Connection);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
Connection.Close();
return null;
}
}
#endregion
#region 执行SQL,返回首行首列
/// <summary>
/// 执行SQL语句返回object
/// </summary>
public object GetObject(string strsql)
{
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
object obj = null;
try
{
SqlCommand cmd = new SqlCommand(strsql, Connection);
obj = cmd.ExecuteScalar();
Connection.Close();
return obj;
}
catch
{
Connection.Close();
return null;
}
finally
{
Connection.Close();
}
}
#endregion
#region 执行SQL,得到DataTable
public DataTable GetDataTable(string strSql)
{
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
cmd.Connection = GetConnection();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
CloseConnection();
}
finally
{
CloseConnection();
}
return dt;
}
#endregion
}
使用举例:
SqlClass sql = new SqlClass();
SqlDataReader dr = sql.GetDataReader("select * from");
if (dr != null)
{
while (dr.Read())
{
if (dr["RecvTime"] != DBNull.Value)
{
//......
}
}
dr.Close();
dr.Dispose();
}
分享到:
相关推荐
ADO.net操作数据库总结,包括SqlConnection、SqlCommand等
asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net数据库操作类asp.net...
ASP.NET操作数据库实例ASP.NET操作数据库实例ASP.NET操作数据库实例ASP.NET操作数据库实例
C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类C#.net数据库操作类
ADO.NET操作数据库的总结,非常的基础。。。针对SQL SERVER 2005
使用ADO.NET操作数据库 显示数据库中的内容 (listbox dropdownlist datalist datagrid 及显示数据库中的图片) 使用ASP.NET操作数据库 断开式数据库访问 使用数据网格处理数据 常用ASP.NET模块 ASP.NET网络数据库...
C#(VB.net)数据库访问操作类库 你知道,一些类库把常用的操作封装起来,以后可以直接调用,就节省了普通开发人员的大量精力. 对于CN 大多数公司都是开发MIS系统 说白了就是数据库系统 使用数据访问类库,必将节省大量...
一个自己总结的 .net数据库操作大全 拿来就用 节省时间
因为工作至今接触的数据库还算比较全面,每次搭建新项目开发数据库选择后,都要编写整理对应的.NET与数据库交互的DBHelper助手类,时间一长,便将各类型数据库助手类整理出来共享,在提升大家开发效率的同时,减少...
ASP.NET 中数据库操作初步,很基础的东西。
ADO.NET操作数据库
VB.NET 针对数据库操作的程序,VB.NET直接操作,数据库支持SQL,ACCESS等
asp.net 连接数据库及数据库详细操作代码,请下载!
vb.net对数据库操作的详细例子,使用vb.net操作数据库的可以参考一下。
ASP.NET操作数据库,通过对ADO.NET 的基本讲解,以及讲解了一些数据源控件的基本用法后,本章将介绍一些ASP.NET 操作数据库的高级用法,包括使用SQLHelper,以及数据源控件对数据的操作。本章是对前面的数据库 知识...
这个是上数据库时,老师给的PPT教程。连接SQLServer 与 VS的。
ch5\Example_5_1~ Example_5_4 ---- 第5章中的使用ADO.NET操作数据库; ch6\Example_6_1~ Example_6_7 ---- 第6章中的显示数据库中的内容; ch7\Example_7_1~ Example_7_11 ---- 第7章中的使用ASP.NET操作数据库; ...