using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Collections;///
/// 对数据库操作的类
///
public class DataBase
{
private SqlConnection myCon;
public DataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}///
/// 获取连接字符串
///
///
private string GetConnectionString()
{
string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
return strConn;
}
///
/// 打开数据库连接
///
/// 打开是否成功
private bool Open()
{
string strCon=GetConnectionString();
if (String.IsNullOrEmpty(strCon) != true)
{
myCon = new SqlConnection(strCon);
}
else
{
return false;
}
try
{
myCon.Open();
return true;
}
catch
{
return false;
}
}
///
/// 关闭对数据库的连接
///
private void Close()
{
if(myCon.State == ConnectionState.Open)
{
myCon.Close();
myCon.Dispose();
}}
///
/// 执行无返回值的查询语句
///
/// 要执行的数据库字符串
/// 执行是否成功
public bool ExecNoQuery(string strSql)
{
if (Open())
{
SqlCommand myCom = new SqlCommand(strSql, myCon);
try
{
myCom.ExecuteNonQuery();
Close();
return true;
}
catch
{
Close();
return false;
}
}
else
{
return false;
}
}
///
/// 返回首行首列
///
/// 查询语句
/// 执行的结果,是一个Object的值,根据具体情况转换
public object ExecNoScalar(string strSql)
{
if (Open())
{
SqlCommand myCom = new SqlCommand(strSql, myCon);
try
{
object returnValue = myCom.ExecuteScalar();
Close();
return returnValue;
}
catch
{
Close();
return null;
}
}
else
{
return null;
}
}
///
/// 执行一个返回SqlDataReader的查询
///
/// 查询语句
///
public SqlDataReader ExecReader(string strSql)
{
if (Open())
{
SqlCommand myCom = new SqlCommand(strSql, myCon);
try
{
SqlDataReader dr = myCom.ExecuteReader(CommandBehavior.CloseConnection);
//关闭dr的同时,关闭数据库连接
return dr;
}
catch (Exception ex)
{
return null;
System.Web.HttpContext.Current.Response.Write(ex.Message);
System.Web.HttpContext.Current.Response.End();
}}
else
{
return null;
}
}
///
/// 将DataReader转换为DataTable
///
/// 要被转换的DataReader
/// 转换后的DataTable
public static DataTable ConvertDataReaderToDataTable(IDataReader reader)
{
DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;
//将列名添加到DataTable中
for (int intCounter = 0; intCounter
/// 创建存储过程命令
///
/// 存储过程名
/// 存储过程参数列表
/// 完整的存储过程命令
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确认打开连接
Open();SqlCommand cmd = new SqlCommand(procName, myCon);
cmd.CommandType = CommandType.StoredProcedure;
// 依次把参数传入存储过程
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
return cmd;
}
///
/// 执行存储过程
///
/// 存储过程名称
/// 返回的数据集合
public SqlDataReader RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//return (int)cmd.Parameters["ReturnValue"].Value;
return dr;
}
///
/// 执行存储过程
///
/// 存储过程名称
/// 参数数组
/// 返回的数据集合
public SqlDataReader RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//return (int)cmd.Parameters["ReturnValue"].Value;
return dr;
}
///
/// 根据查询语句返回
///
/// 查询语句
/// 返回数据表
public DataTable GetDataTable(string QuerySentence)
{
SqlConnection con=new SqlConnection ();
con.ConnectionString =GetConnectionString();
SqlDataAdapter ad = new SqlDataAdapter(QuerySentence, con);
DataTable dtable = new DataTable();
ad.Fill(dtable);
return dtable;
}}///在数据库 创建存储过程Create proc [dbo].[Proc_GetAllResumeInfo](@type int)asSelect r.id,userid,startendTime,workPlace,working,degree,yearInfo,adder,changer,owner,statusFrom tb_resume r ,tb_userInfo uWhere type = @type and r.owner=u.idGO///////////////////////////using System.Data.SqlClient;protected void Button2_Click(object sender, EventArgs e)
{
//连接数据库
SqlConnection conn = new SqlConnection("server=.;database=kk;uid=sa");
//选择存储过程
SqlCommand cmd = new SqlCommand("Proc_Gett1name", conn);
cmd.CommandType = CommandType.StoredProcedure;
//添加参数
SqlParameter uid = new SqlParameter("@id", SqlDbType.VarChar, 50);
uid.Value = "1";
cmd.Parameters.Add(uid);
//读取表数据
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
sdr.Read();
this.TextBox1.Text = sdr.GetValue(0).ToString();
sdr.Dispose();
cmd.Dispose();
conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
//连接数据库
SqlConnection conn = new SqlConnection("server=.;database=kk;uid=sa");
//选择存储过程
SqlCommand cmd1 = new SqlCommand("Proc_int1", conn);
cmd1.CommandType = CommandType.StoredProcedure;
SqlCommand cmd2 = new SqlCommand("Proc_int2", conn);
cmd2.CommandType = CommandType.StoredProcedure;
//添加参数
SqlParameter id1 = new SqlParameter("@id", SqlDbType.VarChar, 50);
id1.Value = this.Txt11.Text.ToString();
cmd1.Parameters.Add(id1);
SqlParameter name1 = new SqlParameter("@name", SqlDbType.VarChar, 50);
name1.Value = this.Txt12.Text.ToString();
cmd1.Parameters.Add(name1);
SqlParameter id2 = new SqlParameter("@id", SqlDbType.VarChar, 50);
id2.Value = this.Txt21.Text.ToString();
cmd2.Parameters.Add(id2);
SqlParameter name2 = new SqlParameter("@name", SqlDbType.VarChar, 50);
name2.Value = this.Txt22.Text.ToString();
cmd2.Parameters.Add(name2);
conn.Open();
//创建事务
SqlTransaction myt = conn.BeginTransaction();
//事务关联数据库操作
cmd1.Transaction = myt;
cmd2.Transaction = myt;
//执行事务
try
{
//执行数据库更新
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
//提交事务
myt.Commit();
Response.Write("成功");
}
catch (Exception ex)
{
//回滚事务
myt.Rollback();
Response.Write("失败");
}
finally
{
conn.Dispose();
}
} |
