你的位置:首页 > ASP.net教程

[ASP.net教程]SQLServer数据库通用访问类


private static string connString=ConfigurationManager.ConnStrings["connString"].ToString(); //在app.config中configuration添加相应的数据库连接配置文件

1.格式化sql语句

//执行增删改查

public static int Update(string sql)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

    conn.Open();

    return cmd.ExecuteNonQuery();

  }

  catch(Exception ex)

  {

    throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//单一结果查询

public static object GetSingleResult(string sql)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

     conn.Open();

    return cmd.ExecuteScalar();

  }

  catch(Exception ex)

  {

    throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//只读结果集查询

public static SqlDataReader GetReader(string sql)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

     conn.Open();

    return cmd.ExecuteReader(CommandBehavior.CloseConnection);

  }

  catch(Exception ex)

  {

    conn.Close();

    throw ex;

  }

}

//DataSet数据集查询

public static DataSet GetDataSet(string sql)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  DataSet ds=new DataSet();

  SqlDataAdapter da=new SqlDataAdapter();

  try

  {

     conn.Open();

    da.Fill(ds);

    return ds;

  }

  catch(Exception ex)

  {

    throw ex;

  }

  finally

  {

    conn.Close();

  }

}

2.带参数的sql语句

//执行增删改查

public static int Update(string sql,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

    conn.Open();

    cmd.Parameters.AddRange(param);

    return cmd.ExecuteNonQuery();

  }

  catch(Exception ex)

  {

    throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//查询单一结果

public static object GetSingleResult(string sql,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

     conn.Open();

    cmd.Parameters.AddRange(param);

    retrurn cmd.ExecuteScalar();

  }

  catch(Exception ex)

  {

     throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//查询只读结果集

public static SqlDataReader GetReader(string sql,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand(sql,conn);

  try

  {

     conn.Open();

    cmd.Parameters.AddRange(param);

    return cmd.ExecuteReader(CommandBehavior.CloseConnection);

  }

  catch(Exception ex)

  {

     conn.Close();

    throw ex;

  }

}

3.带参数的存储过程

//执行增删改查

public static int UpdateByProcedure(string procedureName,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand();

  cmd.Connection=conn;

  try

  {

    conn.Open();

    cmd.CommandType=CommandType.StoredProcedure;

    cmd.CommandText=procedureName;

    cmd.Parameters.AddRange(param);

    return cmd.ExecuteNonQuery();

  }

  catch(Exception ex)

  {

    throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//查询单一结果

public static object GetSingleResultByProcedure(string procedureName,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand();

  cmd.Connection=conn;

  try

  {

     conn.Open();

    cmd.CommandType=CommandType.StoredProcedure;

    cmd.CommandText=procedureName;

    cmd.Parameters.AddRange(param);

    retrurn cmd.ExecuteScalar();

  }

  catch(Exception ex)

  {

     throw ex;

  }

  finally

  {

    conn.Close();

  }

}

//查询只读结果集

public static SqlDataReader GetReaderByProcedure(string procedureName,SqlParameter[] param)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand();

  cmd.Connection=conn;

  try

  {

     conn.Open();

    cmd.CommandType=CommandType.StoredProcedure;

    cmd.CommandText=procedureName;

    cmd.Parameters.AddRange(param);

    return cmd.ExecuteReader(CommandBehavior.CloseConnection);

  }

  catch(Exception ex)

  {

     conn.Close();

    throw ex;

  }

}

4.使用事务更新

public static bool UpdateByTracsaction(List<string> sqlList)

{

  SqlConnection conn=new SqlConnection(connString);

  SqlCommand cmd=new SqlCommand();

  cmd.Connection=conn;

  try

  {

    conn.Open();

    cmd.Transaction=conn.BeginTransaction();//开启事务

    foreach(string sql in sqlList)

    {

      cmd.CommandText=sql;

      cmd.ExecuteNonQuery();

    }

    cmd.Transaction.Commit();//提交事务

    return true;

  }

  catch(Exception ex)

  {

    if(cmd.Transaction!=null)

    {

      cmd.Transaction.Rollback();//回滚事务

    }

    throw ex;

  }

  finally

  {

    if(cmd.Transaction!=null)

    {

      cmd.Transaction=null;//清空事务

    }

    conn.Close();

  }

}