asp.net使用ado连接数据库

来源：互联网发布：java 精确毫秒后6位编辑：程序博客网时间：2024/05/14 05:34

/// <summary>
/// 数据访问操作类
/// chy710.cnblogs.com
/// </summary>

public class SqlDAO
{
//数据库连接
private SqlConnection myConnection;
private SqlTransaction sqlTrans;

public SqlDAO()
{
myConnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());

}

#region 执行存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="myPar">查询参数</param>
/// <returns>int</returns>
public int RunProc(string procName, SqlParameter[] myPar)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand(procName, myConnection);
cmd.CommandType = CommandType.StoredProcedure;
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["ReturnValue"].Value;
}
catch (Exception ex)
{
return -1;
}
finally
{
myConnection.Close();
}
}

#region 查询返回DataTable
/// <summary>
///
/// </summary>
/// <param name="procName"></param>
/// <param name="myPar"></param>
/// <returns></returns>
public DataTable RunProcGetData(string procName, SqlParameter[] myPar)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand(procName, myConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
return null;
}
finally
{
myConnection.Close();
}
}
#endregion

#region 查询返回DataTable
/// <summary>
///
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="myPar">参数列表</param>
/// <param name="startRecord">起始记录</param>
/// <param name="maxRecords">最大记录</param>
/// <returns>数据表</returns>
public DataTable RunProcGetData(string procName, SqlParameter[] myPar, int startRecord, int maxRecords)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand(procName, myConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
DataSet ds = new DataSet();
da.Fill(ds, startRecord, (maxRecords - startRecord + 1), "ReturnTable");
return ds.Tables["ReturnTable"];
}
catch (Exception ex)
{
return null;
}
finally
{
myConnection.Close();
}
}
#endregion
#endregion

#region 执行SQL语句

public int ExecuteSql(string strSql, SqlParameter[] myPar)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand(strSql, myConnection);
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
int result = cmd.ExecuteNonQuery();
return result;
}
catch(Exception ex)
{
return -1;
}
finally
{
myConnection.Close();
}
}

#region 查询返回DataTable
public DataTable GetDataTable(string strSql, SqlParameter[] myPar)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand(strSql, myConnection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
catch
{
return null;
}
finally
{
myConnection.Close();
}
}
#endregion
#endregion

#region 带事务的SQL语句返回True OR False
/// <summary>
/// sql 语句事务
/// </summary>
/// <param name="listSqlPara">数据字典</param>
/// <returns>bool</returns>
public Boolean ExecuteTransation(Dictionary<string, SqlParameter[]> listSqlPara) //使用传入sql语句<BR>   
{
myConnection.Open();
SqlCommand cmd = myConnection.CreateCommand();
cmd.Connection = myConnection;
SqlTransaction tx = myConnection.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (KeyValuePair<string, SqlParameter[]> item in listSqlPara)
{
cmd.CommandText = item.Key.ToString();

if (item.Value != null)
{
foreach (SqlParameter spar in item.Value)
{
cmd.Parameters.Add(spar);
}
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tx.Commit();
}
catch
{
tx.Rollback();
return false;
}
finally
{
myConnection.Close();
}
return true;
}
#endregion
/// <summary>
/// 数据库的打开
/// </summary>
/// <returns>SqlConnection</returns>
public void ConnectionOpen()
{
if (myConnection.State == ConnectionState.Closed)
myConnection.Open();

}
/// <summary>
/// 数据库的关闭
/// </summary>
public void ConnectionClose()
{
myConnection.Close();
}
/// <summary>
/// 事务的开始
/// </summary>
/// <param name="stx">SqlTransaction</param>
public void TransactionBegin()
{
sqlTrans = myConnection.BeginTransaction();

}
/// <summary>
/// 事务提交
/// </summary>
/// <param name="sqltrans">事务类</param>
public void TransactionCommit()
{
sqlTrans.Commit();
}
/// <summary>
/// 事务的回滚
/// </summary>
/// <param name="sqltrans">事务类</param>
public void TransactionRollback()
{
sqlTrans.Rollback();
}
/// <summary>
/// 需要手动打开数据库连接
/// </summary>
/// <param name="procName">参数过程名</param>
/// <param name="myPar">参数</param>
/// <returns>int</returns>
public int ExceturnProc(string procName, SqlParameter[] myPar)
{
try
{
SqlCommand cmd = new SqlCommand(procName, myConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = sqlTrans;
if (myPar != null)
{
foreach (SqlParameter spar in myPar)
{
cmd.Parameters.Add(spar);
}
}
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
cmd.ExecuteNonQuery();
return (int)cmd.Parameters["ReturnValue"].Value;
}
catch
{
return -1;
}
}
}

0 0