今天赶ASP作业时发现了一个ADO.NET对数据库访问的封装类,DbManagerSQL,可以当做一个工具类在操作数据库中使用,很方便。
使用前首先需要配置ConnectionString,只需在Web.config配置文件加入一个字段即可。
<appSettings> <add key="ConnectionString" value="server=.; database=mydatabase; integrated security=true"/> </appSettings>
using System; using System.Collections; using System.Collections.Specialized; using System.Runtime.Remoting.Messaging; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Data.Odbc; //ADO.NET数据库操作基础类。 namespace ADO.NetTest1 { public abstract class DbManagerSQL { //数据库连接字符串 protected static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; public DbManagerSQL() { } ///执行SQL语句,返回影响的记录数 public static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (SqlException E) { throw new Exception(E.Message); } } } } // 执行两条SQL语句,实现数据库事务。 public static void ExecuteSqlTran(string SQLString1, string SQLString2) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; SqlTransaction tx = connection.BeginTransaction(); cmd.Transaction = tx; try { cmd.CommandText = SQLString1; cmd.ExecuteNonQuery(); cmd.CommandText = SQLString2; cmd.ExecuteNonQuery(); tx.Commit(); } catch (SqlException E) { tx.Rollback(); throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } //执行多条SQL语句,实现数据库事务,每条语句以“;”分割。 public static void ExecuteSqlTran(string SQLStringList) { using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = conn; OdbcTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { string[] split = SQLStringList.Split(new Char[] { ';' }); foreach (string strsql in split) { if (strsql.Trim() != "") { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (OdbcException E) { tx.Rollback(); throw new Exception(E.Message); } } } //执行带一个存储过程参数的的SQL语句。 public static int ExecuteSql(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } //执行一条计算查询结果语句,返回查询结果(object)。 public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); connection.Close(); } } } //执行查询语句,返回SqlDataReader public static SqlDataReader ExecuteReader(string strSQL) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); SqlDataReader myReader; try { connection.Open(); myReader = cmd.ExecuteReader(); return myReader; } catch (SqlException e) { throw new Exception(e.Message); } finally { cmd.Dispose(); connection.Close(); } } } //执行查询语句,返回DataSet public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (SqlException ex) { throw new Exception(ex.Message); } return ds; } } } }
