using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; namespace ADONET { public class SqlHelper:IDisposable { private static string sqlConnectionString = ConfigurationManager.ConnectionStrings["database"].ConnectionString;//连接字符串 private SqlConnection connection; //连接对象 #region //构造函数 public SqlHelper() { this.connection = CreateConnection(sqlConnectionString); } #endregion #region ///<summary> ///根据配置文件中的连接字符串创建数据库连接对象 ///</summary> ///<returns>所创建的连接对象</returns> public static SqlConnection CreateConnection() { SqlConnection sqlConn = new SqlConnection(sqlConnectionString); sqlConn.Open(); return sqlConn; } ///<summary> ///用指定的连接字符串创建数据库连接对象 ///</summary> ///<param name = "connectionString">连接字符串</param> ///<returns>所创建的连接对象</returns> public static SqlConnection CreateConnection(string connectionString) { SqlConnection sqlConn = new SqlConnection(connectionString); sqlConn.Open(); return sqlConn; } #endregion #region 创建命令 ///<summary> ///创建存储命令过程 ///</summary> ///<param name = "storedProcedure">存储过程名称</param> ///<returns>所创建的命令</returns> public SqlCommand GetStoredProcCommand(string storedProcedure) { SqlCommand command = connection.CreateCommand(); command.CommandText = storedProcedure; command.CommandType = CommandType.StoredProcedure; return command; } ///<summary> ///创建文本命令过程 ///</summary> ///<param name = "sql">命令文本</param> ///<returns>所创建的命令</returns> public SqlCommand GetSqlStringCommand(string sql) { SqlCommand command = connection.CreateCommand(); command.CommandText = sql; command.CommandType = CommandType.Text; return command; } #endregion #region 添加命令参数 ///<summary> ///为命令添加输出参数 ///</summary> ///<param name = "cmd">命令</param> ///<para name = "parameterName">参数名</para> ///<para name = "dbtype">参数类型</para> ///<para name = "size">大小</para> public void AddOutParameter(SqlCommand cmd, string parameterName, DbType sqlDbtype, int size) { SqlParameter sqlParameter = cmd.CreateParameter(); sqlParameter.DbType = sqlDbtype; sqlParameter.ParameterName = parameterName; sqlParameter.Size = size; sqlParameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(sqlParameter); } ///<summary> ///为命令添加输入参数 ///</summary> ///<param name = "cmd">命令</param> ///<para name = "parameterName">参数名</para> ///<para name = "value">数值</para> public void AddInParameter(SqlCommand cmd, string parameterName, DbType sqlDbtype, object value) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.DbType = sqlDbtype; sqlParameter.ParameterName = parameterName; sqlParameter.Value = value; sqlParameter.Direction = ParameterDirection.Input; cmd.Parameters.Add(sqlParameter); } ///<summary> ///根据参数名得到参数 ///</summary> ///<param name = "cmd">命令</param> ///<para name = "parameterName">参数名</para> public SqlParameter GetParameter(SqlCommand cmd, string parameterName) { return cmd.Parameters[parameterName]; } #endregion ///执行命令,得到DataReader对象 public SqlDataReader ExecuteReader(SqlCommand cmd) { cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } //执行没有查询结果的命令。如(insert,delete),返回受影响的行 public int ExecuteNonQuery(SqlCommand cmd) { cmd.Connection.Open(); int ret = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return ret; } //执行命令并返回查询到的单个值 public object ExecuteScalar(SqlCommand cmd) { cmd.Connection.Open(); object ret = cmd.ExecuteScalar(); cmd.Connection.Close(); return ret; } ///<summary> ///执行命令返回一个DataTable ///</summary> ///<param name = "cmd">要执行的命令</param> ///<returns>得到的DataTable</returns> public DataTable ExcuteDataTable(SqlCommand cmd) { SqlDataReader reader = cmd.ExecuteReader(); DataTable table = new DataTable(); table.Load(reader); reader.Close(); return table; } #region 释放资源 private bool disposed = false; //资源是否已经被释放 #endregion public void Dispose() { dispose(true); } ///<summary> ///释放资源 ///</summary> ///<param name = "disposing">是否用户代码调用</parm> ///<remarks> ///此方法将在两种情况下被调用,(1)用户调用Dispose()方法 (2)垃圾回收 ///在第二种情况下,数据库连接已经被垃圾回收自动处理,不需要再释放 ///只有在第一种情况下需要在代码中显示关闭数据库连接 ///</remark> private void dispose(bool disposing) { if (disposed) return; disposed = true; if (disposing) { if (connection.State != ConnectionState.Closed) connection.Close(); } } } } <form id="form1" runat="server"> <div> <h3>用户列表</h3> <asp:Table ID="result" runat="server" GridLines = "Both" CellPadding = "2"> </asp:Table> </div> <hr/> <div> <h3>添加新用户</h3> 用户登录ID:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br /> 用户名称: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br /> 登录密码: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <br /> 性别:<asp:RadioButton ID="male" runat="server" Checked = "true" GroupName = "sex" /> <asp:RadioButton ID="female" runat="server" GroupName = "sex" /> <br /> <asp:Button ID="Button1" runat="server" Text="添加用户" οnclick="Button1_Click" /> </div> </form> protected void Page_Load(object sender, EventArgs e) { SqlHelper db = new SqlHelper(); SqlCommand command = db.GetSqlStringCommand("Select * from Loginuser"); using (SqlDataReader reader = db.ExecuteReader(command)) { while (reader.Read()) { TableRow row = new TableRow(); result.Rows.Add(row); for (int i = 0; i < 5; i++) { TableCell cell = new TableCell(); cell.Text = Convert.ToString(reader[i]); row.Cells.Add(cell); } } } db.Dispose(); } SqlHelper db = new SqlHelper(); SqlCommand command = db.GetStoredProcCommand("AddUser");//得到存储过程命令 db.AddInParameter(command, "@id",DbType.String, TextBox1.Text); db.AddInParameter(command, "@name",DbType.String, TextBox2.Text); db.AddInParameter(command, "@pass",DbType.String, TextBox3.Text); db.AddInParameter(command, "@sex", DbType.String, male.Checked ? "M":"F"); db.ExecuteNonQuery(command); db.Dispose(); // Response.Redirect("addUser_sqlHelper.aspx");