using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace FriendsInfoAdministrators.Web.sqlDB
{
#region 数据库类
public class Ado
{
#region 实例化
SqlConnection sqlcon ;//= new SqlConnection();
SqlCommand sqlcmd;// = new SqlCommand();
SqlDataReader reader;
#endregion
#region 声明全局变量保存字符串
/// <summary>
/// 声明全局变量保存字符串
/// </summary>
public static string constr;// = "Data Source=10.10.5.33;Initial Catalog=FrindsInfoAdministrators;Persist Security Info=True;User ID=sa";//保存连接字符串
public String Get_ConnectionStr() ///获取当前连接数据库的连接字符串
{
String constr = sqlcon.ConnectionString;
return constr;
}
#endregion
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
public Ado()
{
////workstation id=CDZ;timeout=5;packet size=4096;uid=1215;pwd=1215;data source=.;persist security info=False;initial catalog=DW
//constr = "workstation id=CDZ;timeout=5;packet size=4096;uid=" + ConfigurationSettings.AppSettings["uid"].ToString() + ";";
//constr += "pwd=" + ConfigurationSettings.AppSettings["pwd"].ToString() + ";";
//constr += "data source=" + ConfigurationSettings.AppSettings["ip"].ToString() + ";persist security info=False;initial catalog=" + ConfigurationSettings.AppSettings["database"].ToString();
//sqlcon.ConnectionString = constr;
sqlcon = new SqlConnection(SqlConnectionStr);
}
#endregion
/// <summary>
/// 获取日期
/// </summary>
/// <returns></returns>
public DateTime GetDate()///////////////////////////////////////////////////jie
{
return (DateTime)ExcuteSqlRetVal("select GetDate()");
}
#region 设置连接属性
/// <summary>
/// 获取或设置数据库连接属性
/// </summary>
public string SqlConnectionStr
{
get
{
return sqlcon.ConnectionString;
}
set
{
constr = value;
}
}
#endregion
#region 对外操作的方法
#region
/// <summary>
/// 执行一条SQL命令，返回是否执行成功//////////////////////////////////////////////jie
/// </summary>
/// <param name="str">SQL语句</param>
/// <returns></returns>
public bool ExcuteStrNonRet(string str)
{
//sqlcmd.Connection = sqlcon;
sqlcon.ConnectionString = constr;
sqlcmd = new SqlCommand(str, sqlcon);
try
{
sqlcon.Open();
if (sqlcmd.ExecuteNonQuery() >= 1)
return true;
else
return false;
}
catch
{
return false;
}
finally
{
sqlcon.Close();
}
}
#endregion
/// <summary>
/// 返回一个值////////////////////////////////////////////////jie
/// </summary>
/// <param name="Str"></param>
/// <returns></returns>
public object ExcuteSqlRetVal(string Str)
{
object ret;
sqlcmd = new SqlCommand(Str, sqlcon);
try
{
sqlcon.Open();
ret = sqlcmd.ExecuteScalar();
return ret;
}
catch
{
return "操作失败!";
}
finally
{
sqlcon.Close();
}
}
#region 数据集
/// <summary>
/// 获取带数据的DataSet
/// </summary>
/// <param name="cmd">设置过的SqlCommand</param>
/// <returns></returns>
public DataSet GetDataSet(SqlCommand cmd)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
cmd.Connection = sqlcon;
try
{
sqlcon.Open();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
sqlcon.Close();
}
}
#endregion
#region 数据表
/// <summary>
/// 获取带数据的数据表DataTable
/// </summary>
/// <param name="cmd">设置过的SqlCommand</param>
/// <returns></returns>
public DataTable GetDataTable(SqlCommand cmd)
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
cmd.Connection = sqlcon;
try
{
sqlcon.Open();
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
sqlcon.Close();
}
}
#endregion
/// <summary>
/// 返回一张表///////////////////////////////////////////////////jie
/// </summary>
/// <param name="Str"></param>
/// <returns></returns>
public DataTable GetTable(string Str)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlDataAdapter Dadp = new SqlDataAdapter(Str, sqlcon);
try
{
sqlcon.Open();
Dadp.Fill(ds);
dt = ds.Tables[0];
return dt;
}
catch
{
return null;
}
finally
{
sqlcon.Close();
}
}
#region
/// <summary>
/// 执行SQL语句，返回受影响条数count
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>返回int</returns>
public int GetCount(string sql)
{
try
{
sqlcon.ConnectionString = constr;
sqlcon.Open();
sqlcmd.Connection = sqlcon;
sqlcmd.CommandText = sql;
int count = (int)sqlcmd.ExecuteScalar();
return count;
}
catch
{
return 0;
}
finally
{
sqlcon.Close();
}
}
#endregion
#region
/// <summary>
/// 得到执行SQL语句后的SqlDataAdapter
/// </summary>
/// <param name="sql"></param>
/// <returns>调用完本方法后必须调用方法Close_Connection关闭数据库连接</returns>
public SqlDataAdapter Get_SqlDataAdapter(String sql)
{
SqlDataAdapter sqldataadapter = new SqlDataAdapter();
try
{
sqlcon.ConnectionString = constr;
sqlcon.Open();
sqlcmd.Connection = sqlcon;
sqlcmd.CommandText = sql;
sqldataadapter.SelectCommand = sqlcmd;
return sqldataadapter;
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
sqlcon.Close();
}
}
#endregion
#region 关闭与数据库的连接
/// <summary>
/// 关闭与数据库的连接
/// </summary>
public void Close_Connection()
{
try
{
sqlcon.Close();
}
catch
{
}
}
#endregion
#region 读取器
/// <summary>
/// 获取流读取器
/// </summary>
/// <param name="cmd">设置过的SqlCommand</param>
/// <returns></returns>
public SqlDataReader GetSqlDataReader(SqlCommand cmd)
{
cmd.Connection = sqlcon;
try
{
sqlcon.Open();
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
//需要手动调用方法关闭数据库连接
}
}
#endregion
#region 得到执行SQL语句后的SqlDataReader
/// <summary>
/// 得到执行SQL语句后的SqlDataReader
/// </summary>
/// <param name="sql"></param>
/// <returns>调用完本方法后必须调用方法Close_Connection关闭数据库连接</returns>
public SqlDataReader Get_SqlDataReader(String sql)
{
try
{
sqlcon.ConnectionString = constr;
sqlcmd.Connection = sqlcon;
sqlcmd.CommandText = sql;
sqlcon.Open();
SqlDataReader dr = sqlcmd.ExecuteReader();
return dr;
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
}
#endregion
#endregion
}
#endregion
}
