/// <summary>
/// 查询获取游标
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlDataReader GetCursor(string sql, SqlParameter[] parameters)
{
SqlDataReader sdr = null;
SqlCommand cmd = GetCmd(sql);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
try
{
cmd.Connection.Open();
sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex, "Function:GetCursor SQL:" + sql);
}
if(sdr!=null&&!sdr.HasRows)
{
sdr.Close();
cmd.Connection.Close();
sdr=null;
}
return sdr;
}
public static DateTime GetNowDate()
{
DateTime dt = DateTime.Now;
Object date = DBHelper.GetSingle("select getdate();");
if (!DateTime.TryParse(date.ToString(), out dt))
{
Logo lg = new Logo();
lg.WriteLogo(new Exception("日期转换失败"), "在获取数据时间并进行日期转换时失败 Function:GetNowDate");
// throw new Exception("日期转换失败!!!!!!!!!!!");
}
return dt;
}
public static int RunProc(string procname, SqlParameter[] parameters)
{
int ret=0;
Object obj;
using (SqlCommand cmd = new SqlCommand(procname, GetConn()))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
SqlParameter retparam = new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4);
retparam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retparam);
try
{
cmd.Connection.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex, "Function:RunProc PROCNAME:" + procname);
}
finally
{
cmd.Connection.Close();
}
if (cmd.Parameters["@RETURN_VALUE"] == null || cmd.Parameters["@RETURN_VALUE"].Value==null || cmd.Parameters["@RETURN_VALUE"].Value == DBNull.Value)
{
return -1;
}
if (!Int32.TryParse(cmd.Parameters["@RETURN_VALUE"].Value.ToString(), out ret))
{
Logo lg = new Logo();
lg.WriteLogo(new Exception("CONVRT ERROR: VALUE:" + cmd.Parameters["@RETURN_VALUE"].Value.ToString()));
}
}
return ret;
}
/// <summary>
/// 查询受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int NonQuery(string sql, SqlParameter[] parameters)
{
int row = 0;
SqlCommand cmd = GetCmd(sql);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
try
{
cmd.Connection.Open();
row = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex, "Function:NonQuery SQL:" + sql);
}
finally
{
cmd.Connection.Close();
}
return row;
}
/// <summary>
/// 查询并获取第一行,第一列的数据
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object GetSingle(string sql, SqlParameter[] parameters)
{
object obj=null;
SqlCommand cmd = GetCmd(sql);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
try
{
cmd.Connection.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex, "Function:GetSingle SQL:" + sql);
}
finally
{
cmd.Connection.Close();
}
return obj;
}
public DataTable DBoperationsGetDt(SqlParameter[] parameters)
{
DataTable dt = new DataTable();
if (!String.IsNullOrEmpty(_connstr))
{
try
{
_conn = new SqlConnection(_connstr);
}
catch (SqlException e)
{
throw e;
}
}
if (_conn == null)
{
throw new Exception("没有初始化数据库连接对象!!");
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.Parameters.AddRange(parameters);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _procname;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
try
{
sda.Fill(dt);
}
catch (Exception ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex,"没有获取到分页数据!");
}
_totalcount = Convert.ToInt32(cmd.Parameters[0].Value);
_pageindex = Convert.ToInt32(cmd.Parameters[6].Value);
if (_totalcount == 0)
{
return null;
}
if (_pagesize != 0)
{
_totalpage = _totalcount / _pagesize;
if (_totalcount % _pagesize != 0)
{
_totalpage++;
}
}
else
{
_totalpage = _totalcount / 20;
if (_totalcount % 20 != 0)
{
_totalpage++;
}
}
return dt;
}
public SqlDataReader DBoperationsGetCursor(SqlParameter[] parameters)
{
SqlDataReader sdr=null;
if (!String.IsNullOrEmpty(_connstr))
{
try
{
_conn = new SqlConnection(_connstr);
}
catch (SqlException e)
{
throw e;
}
}
if (_conn == null)
{
throw new Exception("没有初始化数据库连接对象!!");
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.Parameters.AddRange(parameters);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _procname;
try
{
_conn.Open();
sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
Logo lg = new Logo();
lg.WriteLogo(ex, "分页存储过程获取游标失败!!!");
}
_totalcount = Convert.ToInt32(cmd.Parameters[0].Value);
_pageindex = Convert.ToInt32(cmd.Parameters[6].Value);
if (_totalcount == 0)
{
return null;
}
if (_pagesize != 0)
{
_totalpage = _totalcount / _pagesize;
if (_totalcount % _pagesize != 0)
{
_totalpage++;
}
}
else
{
_totalpage = _totalcount / 20;
if (_totalcount % 20 != 0)
{
_totalpage++;
}
}
return sdr;
}