在上兩篇 《 Java是如何快速煮成C#的？》（一）：相似的方法 和 《Java是如何快速煮成C#的？》（一）：相似的方法(2) 中，我們開啟了C#與Java中的話題之一:相似的方法。其實我寫主這兩篇文章的目的，有兩個：
1、總結自己的學習歷程，這個是主要的。
2、給c#轉入java的同學一個快速熟悉的感覺，所以這個系列的名稱也是“快速煮成”。
因為我對這兩門語言僅限了解，對c#也僅限于熟悉，如有理解不妥之處，請指正。
今天我們看看這兩種語言環境下純粹的數據訪問。
首先我再次聲明：
1、 本文不比較這兩種語言的數據訪問的性能差異。
2、 本文不涉及各自的OR框架 ， 如C#的ADO.NET Entity Framework,MVC,Nhibernate,spring.net，以及Java領域的Spring/Hibernate/Struts等第三方框架, 只是純粹的數據訪問 。
3、數據庫采用MS SQL server 2008,其實也可以用mySQL,MySQL提供官方支持。oracle平時很少用,DB2沒用過。
準備工作：一個用于測試的部門表DepartDemo,表結構如下：

相關的SQL語句：
Create database Db2010Demo go use Db2010Demo go if exists (select 1 from sysobjects where id = object_id('DepartDemo') and type = 'U') drop table DepartDemo go /*==============================================================*/ /* Table: DepartDemo */ /*==============================================================*/ create table DepartDemo ( PKID int identity(101,1), DName nvarchar(200) null, DCode nvarchar(500) null, Manager nvarchar(50) null, ParentID int null default 0, AddUser nvarchar(50) null, AddTime datetime null, ModUser nvarchar(50) null, ModTime datetime null, CurState smallint not null default 0, Remark nvarchar(500) null, F1 int not null default 0, F2 nvarchar(300) null, constraint PK_DEPARTDEMO primary key (PKID) ) go --插入一條測試數據 insert into DepartDemo select '國家統計局房產審計一科','0','胡不歸',0,'DeomUser',getdate(), '','1900-01-01',1,'專業評估全國房價，為老百姓謀福祉',0,'' --創建一個存儲過程，在C#程序中用到 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DepartDemoSelectByPKID]') AND type='P') DROP PROCEDURE [dbo].[DepartDemoSelectByPKID] GO CREATE PROCEDURE [dbo].[DepartDemoSelectByPKID] ( @Pkid int ) AS BEGIN TRAN Select * From [DepartDemo] Where [Pkid]=@Pkid IF @@ERROR!=0 BEGIN ROLLBACK END ELSE BEGIN COMMIT END GO

一、我們看看C#環境下一個數據訪問的簡單例子。

在vs2010中新建一控制臺項目，結構如下：

相應的代碼,

基礎數據層：

Database.cs:

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace demo2010.database { public class DataBase { private static string connectionString = System.Configuration.ConfigurationManager.AppSettings["GlobalsConnString"]; public static string ConnectionString { get { return connectionString; } set { connectionString = value; } } #region Helpers internal protected static IDataReader GetReader(string commandText, SqlParameter[] p) { return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText, p); } internal protected static IDataReader GetReader(string commandText) { return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, commandText); } internal protected static int NonQueryInt(string commandText, SqlParameter[] p) { return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, commandText, p); } internal protected static bool NonQueryBool(string commandText, SqlParameter[] p) { return NonQueryInt(commandText, p) > 0; } internal protected void RunSql(string commandText) { SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText); } public static void ExecuteSQL(string commandText) { SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, commandText); } public static DataTable GetDataTable(string commandText) { return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, commandText); } public static DataTable GetDataTable(string commandText, CommandType commandType) { return SqlHelper.ExecuteDataTable(ConnectionString, commandType, commandText); } internal protected static string ReturnString(string commandText, SqlParameter[] p) { object result = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.StoredProcedure, commandText, p); return result.ToString(); } internal protected static SqlParameter ReTurnStringValue { get { return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.NVarChar, 200); } } internal protected static SqlParameter ReTurnLongValue { get { return SqlHelper.MakeOutParam("@ReTurnValue", SqlDbType.BigInt, 8); } } #endregion } }

DynamicBuilder.cs:

using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Reflection; using System.Reflection.Emit; using System.Data; namespace demo2010.database { /// <summary> /// Tony 2008.8.28 Add /// </summary> /// <typeparam name="T"></typeparam> public class DynamicBuilder<T> { private static readonly MethodInfo getValueMethod = typeof(IDataRecord).GetMethod("get_Item", new Type[] { typeof(int) }); private static readonly MethodInfo isDBNullMethod = typeof(IDataRecord).GetMethod("IsDBNull", new Type[] { typeof(int) }); private delegate T Load(IDataRecord dataRecord); private const BindingFlags BINDING_FLAGS = BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase;//Added by Tony 2008.09.25 private Load handler; private DynamicBuilder() { } public T Build(IDataRecord dataRecord) { return handler(dataRecord); } public static DynamicBuilder<T> CreateBuilder(IDataRecord dataRecord) { DynamicBuilder<T> dynamicBuilder = new DynamicBuilder<T>(); DynamicMethod method = new DynamicMethod("DynamicCreate", typeof(T), new Type[] { typeof(IDataRecord) }, typeof(T), true); ILGenerator generator = method.GetILGenerator(); LocalBuilder result = generator.DeclareLocal(typeof(T)); generator.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes)); generator.Emit(OpCodes.Stloc, result); for (int i = 0; i < dataRecord.FieldCount; i++) { PropertyInfo propertyInfo = typeof(T).GetProperty(dataRecord.GetName(i),BINDING_FLAGS);//performance'Maxvalue decrease 5% **** Test By Tony 2008.09.25 Label endIfLabel = generator.DefineLabel(); if (propertyInfo != null && propertyInfo.GetSetMethod() != null) { generator.Emit(OpCodes.Ldarg_0); generator.Emit(OpCodes.Ldc_I4, i); generator.Emit(OpCodes.Callvirt, isDBNullMethod); generator.Emit(OpCodes.Brtrue, endIfLabel); generator.Emit(OpCodes.Ldloc, result); generator.Emit(OpCodes.Ldarg_0); generator.Emit(OpCodes.Ldc_I4, i); generator.Emit(OpCodes.Callvirt, getValueMethod); generator.Emit(OpCodes.Unbox_Any, dataRecord.GetFieldType(i)); generator.Emit(OpCodes.Callvirt, propertyInfo.GetSetMethod()); generator.MarkLabel(endIfLabel); } } generator.Emit(OpCodes.Ldloc, result); generator.Emit(OpCodes.Ret); dynamicBuilder.handler = (Load)method.CreateDelegate(typeof(Load)); return dynamicBuilder; } } }

SqlHelper.cs:(我們將最常用的SQL在大家熟悉的SqlHelper中。)

using System; using System.Configuration; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Collections; namespace demo2010.database { #region Disclaimer/Info #endregion /// <summary> /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for /// common uses of SqlClient. /// </summary> public sealed class SqlHelper { #region private utility methods & constructors //Since this class provides only static methods, make the default constructor private to prevent //instances from being created with "new SqlHelper()". private SqlHelper() { } /// <summary> /// This method is used to attach array of SqlParameters to a SqlCommand. /// /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. /// /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">an array of SqlParameters tho be added to command</param> private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /// <summary> /// This method assigns an array of values to an array of SqlParameters. /// </summary> /// <param name="commandParameters">array of SqlParameters to be assigned values</param> /// <param name="parameterValues">array of Components holding the values to be assigned</param> private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the SqlParameters, assigning the values from the corresponding position in the //value array for (int i = 0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } /// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the SqlCommand to be prepared</param> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } #endregion private utility methods & constructors #region DataHelpers public static string CheckNull(object obj) { return (string)obj; } public static string CheckNull(DBNull obj) { return null; } #endregion #region AddParameters public static object CheckForNullString(string text) { if (text == null || text.Trim().Length == 0) { return string.Empty; //return System.DBNull.Value; } else { return text; } } public static object CheckForNullDateTime(object obj) { if (obj == null) { return Convert.ToDateTime("1900-01-01"); //return System.DBNull.Value; } else { return obj; } } public static SqlParameter MakeInParam(string ParamName, object Value) { return new SqlParameter(ParamName, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } /// <summary> /// Make stored procedure param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Direction">Parm direction.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size > 0) param = new SqlParameter(ParamName, DbType, Size); else param = new SqlParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } #endregion #region ExecuteNonQuery /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion ExecuteNonQuery #region ExecuteDataSet /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the dataset return ds; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the dataset return ds; } #endregion ExecuteDataSet #region ExecuteDataTable /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataTable(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the DataTable return dt; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the DataTable return dt; } #endregion ExecuteDataTable #region ExecuteReader /// <summary> /// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader() /// </summary> private enum SqlConnectionOwnership { /// <summary>Connection is owned and managed by SqlHelper</summary> Internal, /// <summary>Connection is owned and managed by the caller</summary> External } /// <summary> /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param> /// <returns>SqlDataReader containing the results of the command</returns> private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); //create a reader SqlDataReader dr; // call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return dr; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connectionString); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } #endregion ExecuteReader #region ExecuteScalar /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion ExecuteScalar #region ExecuteXmlReader /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion ExecuteXmlRead

DAL：

SqlDataprovider.cs

using System; using System.Data; using System.Data.SqlClient; using demo2010.database; namespace demo2010.DAL { public class SqlDataProvider : DataBase { internal static IDataReader DepartDemoSelectByPKID(int pKid) { SqlParameter[] p = { SqlHelper.MakeInParam("@pkid", SqlDbType.Int, 4, pKid) }; try { return GetReader("DepartDemoSelectByPKID", p); } catch { return null; } } } }

DepartDemo.cs

using System; using demo2010.database; using System.Data; namespace demo2010.DAL { // File: DepartDemo.cs // Author: Administrator // Created: 2010年9月20日 16:34:05 // Purpose: Definition of Class DepartDemo public class DepartDemo { #region Primaey private int pKID; private string dName; private string dCode; private string manager; private int parentID; private string addUser; private DateTime addTime; private string modUser; private DateTime modTime; private short curState; private string remark; private int f1; private string f2; public int PKID { get { return pKID; } set { this.pKID = value; } } public string DName { get { return dName; } set { this.dName = value; } } public string DCode { get { return dCode; } set { this.dCode = value; } } public string Manager { get { return manager; } set { this.manager = value; } } public int ParentID { get { return parentID; } set { this.parentID = value; } } public string AddUser { get { return addUser; } set { this.addUser = value; } } public DateTime AddTime { get { return addTime; } set { this.addTime = value; } } public string ModUser { get { return modUser; } set { this.modUser = value; } } public DateTime ModTime { get { return modTime; } set { this.modTime = value; } } public short CurState { get { return curState; } set { this.curState = value; } } public string Remark { get { return remark; } set { this.remark = value; } } public int F1 { get { return f1; } set { this.f1 = value; } } public string F2 { get { return f2; } set { this.f2 = value; } } #endregion public static DepartDemo FindDepartDemoByPKID(int _pkid) { IDataReader reader = null; try { reader = SqlDataProvider.DepartDemoSelectByPKID(_pkid); if (reader.Read()) { return LoadSingleDepartDemo(reader); } return null; } catch { return null; } finally { if (reader != null) { reader.Close(); reader.Dispose(); } } } public static DepartDemo LoadSingleDepartDemo(IDataReader reader) { DynamicBuilder<DepartDemo> builder = DynamicBuilder<DepartDemo>.CreateBuilder(reader); return builder.Build(reader); } } }

測試代碼　：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using demo2010.DAL; namespace demo2010 { class Program { static void Main(string[] args) { GetDepartDemoByPKID(101); Console.ReadKey(); } public static void GetDepartDemoByPKID(int pkid) { try { DepartDemo d = DepartDemo.FindDepartDemoByPKID(pkid); if (d != null) { Console.WriteLine("部門：" + d.DName); Console.WriteLine("領導：" + d.Manager); Console.WriteLine("職能：" + d.Remark); } } catch (Exception e1) { Console.WriteLine("出錯:" + e1.Message); } } } }

    相應的配置文件App.Config內容,如果是web項目，則在web.config中。
    

    

    

    

    

    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="GlobalsConnString" value="server=ap3/DBServer;uid=sa;pwd=sa;database=Db2010Demo;Connect Timeout=15"/>
  </appSettings>
</configuration>    

    

    

    

    
運行結果：
    

    

    

    

    

    

    

    

    

    

    

    
二、在Eclipse中新建一Java項目,如下：
  

代碼如下：

DBConnectionManager.java

package demo2010.database; /** * @Project:JavaDemoDB2010 * @Package:demo2010.database.DBConnectionManager * @TypeName:DBConnectionManager * @FileName:DBConnectionManager.java * @Author Administrator:downmoon(3w@live.cn) * @CreateDate: 2010-9-20 上午09:13:10 * @Version: * @Comment: TODO */ import java.io.*; import java.sql.*; import java.util.*; import java.util.Date; public class DBConnectionManager { static private DBConnectionManager instance; // 唯一實例 static private int clients; private Vector drivers = new Vector(); private Hashtable pools = new Hashtable(); /** * 將文本信息在后臺打印出來 */ private void log(String msg) { System.out.println(new Date() + ": " + msg); } /** * 返回唯一實例.如果是第一次調用此方法,則創建實例 * * @return DBConnectionManager 唯一實例 */ static synchronized public DBConnectionManager getInstance() { if (instance == null) { instance = new DBConnectionManager(); } clients++; return instance; } /** * 建構函數私有以防止其它對象創建本類實例 */ private DBConnectionManager() { init(); } /** * 將連接對象返回給由名字指定的連接池 * * @param name * 在屬性文件中定義的連接池名字 * @param con * 連接對象 */ public void freeConnection(String name, Connection con) { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { pool.freeConnection(con); } } /** * 獲得一個可用的(空閑的)連接.如果沒有可用連接,且已有連接數小于最大連接數 限制,則創建并返回新連接 * * @param name * 在屬性文件中定義的連接池名字 * @return Connection 可用連接或null */ public Connection getConnection(String name) { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { return pool.getConnection(); } return null; } /** * 獲得一個可用連接.若沒有可用連接,且已有連接數小于最大連接數限制, 則創建并返回新連接.否則,在指定的時間內等待其它線程釋放連接. * * @param name * 連接池名字 * @param time * 以毫秒計的等待時間 * @return Connection 可用連接或null */ public Connection getConnection(String name, long time) { DBConnectionPool pool = (DBConnectionPool) pools.get(name); if (pool != null) { return pool.getConnection(time); } return null; } /** * 關閉所有連接,撤銷驅動程序的注冊 */ public synchronized void release() { // 等待直到最后一個客戶程序調用 if (--clients != 0) { return; } Enumeration allPools = pools.elements(); while (allPools.hasMoreElements()) { DBConnectionPool pool = (DBConnectionPool) allPools.nextElement(); pool.release(); } Enumeration allDrivers = drivers.elements(); while (allDrivers.hasMoreElements()) { Driver driver = (Driver) allDrivers.nextElement(); try { DriverManager.deregisterDriver(driver); log("撤銷JDBC驅動程序 " + driver.getClass().getName() + "的注冊"); } catch (SQLException e) { log("無法撤銷下列JDBC驅動程序的注冊: " + driver.getClass().getName()); } } } /** * 根據指定屬性創建連接池實例. * * @param props * 連接池屬性 */ private void createPools(Properties props) { Enumeration propNames = props.propertyNames(); while (propNames.hasMoreElements()) { String name = (String) propNames.nextElement(); if (name.endsWith(".url")) { String poolName = "sqlserver"; String url = props.getProperty(poolName + ".url"); if (url == null) { log("沒有為連接池" + poolName + "指定URL"); continue; } String user = props.getProperty(poolName + ".user"); String password = props.getProperty(poolName + ".password"); String maxconn = props.getProperty(poolName + ".maxconn", "0"); int max; try { max = Integer.valueOf(maxconn).intValue(); } catch (NumberFormatException e) { log("錯誤的最大連接數限制: " + maxconn + " .連接池: " + poolName); max = 0; } DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, max); pools.put(poolName, pool); log("成功創建連接池" + poolName); } } } /** * 讀取屬性完成初始化 */ private void init() { InputStream is = getClass().getResourceAsStream("/demo2010.txt"); Properties dbProps = new Properties(); try { dbProps.load(is); } catch (Exception e) { log("不能讀取屬性文件.請確保db.properties在CLASSPATH指定的路徑中"); return; } loadDrivers(dbProps); createPools(dbProps); } /** * 裝載和注冊所有JDBC驅動程序 * * @param props * 屬性 */ private void loadDrivers(Properties props) { String driverClasses = props.getProperty("driver"); StringTokenizer st = new StringTokenizer(driverClasses); while (st.hasMoreElements()) { String driverClassName = st.nextToken().trim(); try { Driver driver = (Driver) Class.forName(driverClassName) .newInstance(); DriverManager.registerDriver(driver); drivers.addElement(driver); log("成功注冊JDBC驅動程序" + driverClassName); } catch (Exception e) { log("無法注冊JDBC驅動程序: " + driverClassName + ", 錯誤: " + e); } } } /** * 此內部類定義了一個連接池.它能夠根據要求創建新連接,直到預定的最大連接數為止.在返回連接給客戶程序之前,它能夠驗證連接的有效性. */ class DBConnectionPool { private int userCount; private Vector freeConnections = new Vector(); private int maxConn; private String name; private String password; private String URL; private String user; // private String databaseUrl; /** * 創建新的連接池 * * @param name * 連接池名字 * @param URL * 數據庫的JDBC URL * @param user * 數據庫帳號,或 null * @param password * 密碼,或 null * @param maxConn * 此連接池允許建立的最大連接數 */ public DBConnectionPool(String name, String URL, String user, String password, int maxConn) { this.name = name; this.URL = URL; this.user = user; this.password = password; this.maxConn = maxConn; } /** * 將不再使用的連接返回給連接池 * * @param con * 客戶程序釋放的連接 */ public synchronized void freeConnection(Connection con) { // 將指定連接加入到向量末尾 freeConnections.addElement(con); userCount--; notifyAll(); } /** * 從連接池獲得一個可用連接.如沒有空閑的連接且當前連接數小于最大連接 數限制,則創建新連接.如原來登記為可用的連接不再有效,則從向量刪除之, * 然后遞歸調用自己以嘗試新的可用連接. */ public synchronized Connection getConnection() { Connection con = null; if (freeConnections.size() > 0) { // 獲取向量中第一個可用連接 con = (Connection) freeConnections.firstElement(); freeConnections.removeElementAt(0); try { if (con.isClosed()) { log("從連接池" + name + "刪除一個無效連接"); // 遞歸調用自己,嘗試再次獲取可用連接 con = getConnection(); } } catch (SQLException e) { log("從連接池" + name + "刪除一個無效連接"); // 遞歸調用自己,嘗試再次獲取可用連接 con = getConnection(); } } else if (maxConn == 0 || userCount < maxConn) { con = newConnection(); } if (con != null) { userCount++; } return con; } /** * 從連接池獲取可用連接.可以指定客戶程序能夠等待的最長時間 參見前一個getConnection()方法. * * @param timeout * 以毫秒計的等待時間限制 */ public synchronized Connection getConnection(long timeout) { long startTime = new Date().getTime(); Connection con; while ((con = getConnection()) == null) { try { wait(timeout); } catch (InterruptedException e) { } if ((new Date().getTime() - startTime) >= timeout) { // wait()返回的原因是超時 return null; } } return con; } /** * 關閉所有連接 */ public synchronized void release() { Enumeration allConnections = freeConnections.elements(); while (allConnections.hasMoreElements()) { Connection con = (Connection) allConnections.nextElement(); try { con.close(); log("關閉連接池" + name + "中的一個連接"); } catch (SQLException e) { log("無法關閉連接池" + name + "中的連接"); } } freeConnections.removeAllElements(); } /** * 創建新的連接 */ private Connection newConnection() { Connection con = null; try { if (user == null) { con = DriverManager.getConnection(URL); } else { con = DriverManager.getConnection(URL, user, password); } log("連接池" + name + "創建一個新的連接"); } catch (SQLException e) { log("無法創建下列URL的連接: " + URL); return null; } return con; } } }

    SQLHelper.java(
    
      
        Null
      
    
    
提供的一個Java版的SQLHelper類。)
    

    

    

    /**
 * 
 */
package demo2010.database;

import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.table.DefaultTableModel;

/**
 * @Project:JavaDemoDB2011
 * @Package:demo2010.database.SQLHelper
 * @TypeName:SQLHelper
 * @FileName:SQLHelper.java
 * @Author Administrator:downmoon(3w@live.cn)
 * @CreateDate: 2010-9-21 上午09:36:28
 * @Version: 
 * @Comment:  SQL 基本操作 * 通過它,可以很輕松的使用 JDBC 來操縱數據庫
 */

public class SQLHelper {
    /**
     * 驅動
     */
    //public static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    /**
     * 連接字符串
     */
    //public static String url = "jdbc:sqlserver://localhost:1433;databaseName=J2ee;";
    /**
     * 用戶名
     */
    //public static String user = "null";
    /**
     * 密碼
     */
    //public static String password = "123456";

    /**
     * 不允許實例化該類
     */
    private SQLHelper()
    {
    }

    /**
     * 獲取一個數據庫連接
     * 通過設置類的  driver / url / user / password 這四個靜態變量來 設置數據庫連接屬性
     * @return 數據庫連接
     */
    public static Connection getConnection()
    {
        /*try
        {
            // 獲取驅動,這里使用的是 sqljdbc_1.2.2828.100_chs.exe,不同版本的驅動,語句有所不同
            Class.forName(driver);
        } catch (ClassNotFoundException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
        }*/

        try
        {
            ////return DriverManager.getConnection(url, user, password);
            
            return DBConnectionManager.getInstance().getConnection("sqlserver");
        } catch (Exception ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    /**
     * 獲取一個 Statement
     * 該 Statement 已經設置數據集 可以滾動,可以更新
     * @return 如果獲取失敗將返回 null,調用時記得檢查返回值
     */
    public static Statement getStatement()
    {
        Connection conn = getConnection();
        if (conn == null)
        {
            return null;
        }
        try
        {
            return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
        // 設置數據集可以滾動,可以更新
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            close(conn);
        }
        return null;
    }

    /**
     * 獲取一個 Statement
     * 該 Statement 已經設置數據集 可以滾動,可以更新
     * @param conn 數據庫連接
     * @return 如果獲取失敗將返回 null,調用時記得檢查返回值
     */
    public static Statement getStatement(Connection conn)
    {
        if (conn == null)
        {
            return null;
        }
        try
        {

            return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        // 設置數據集可以滾動,可以更新
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    /**
     * 獲取一個帶參數的 PreparedStatement
     * 該 PreparedStatement 已經設置數據集 可以滾動,可以更新
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 如果獲取失敗將返回 null,調用時記得檢查返回值
     */
    public static PreparedStatement getPreparedStatement(String cmdText, Object... cmdParams)
    {
        Connection conn = getConnection();
        if (conn == null)
        {
            return null;
        }

        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams)
            {
                pstmt.setObject(i, item);
                i++;
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            close(conn);
        }
        return pstmt;
    }

    /**
     *  獲取一個帶參數的 PreparedStatement
     * 該 PreparedStatement 已經設置數據集 可以滾動,可以更新
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 如果獲取失敗將返回 null,調用時記得檢查返回值
     */
    public static PreparedStatement getPreparedStatement(Connection conn, String cmdText, Object... cmdParams)
    {
        if (conn == null)
        {
            return null;
        }

        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams)
            {
                pstmt.setObject(i, item);
                i++;
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
            close(pstmt);
        }
        return pstmt;
    }

    /**
     * 執行 SQL 語句,返回結果為整型
     * 主要用于執行非查詢語句
     * @param cmdText SQL 語句
     * @return 非負數:正常執行; -1:執行錯誤; -2:連接錯誤
     */
    public static int ExecSql(String cmdText)
    {
        Statement stmt = getStatement();
        if (stmt == null)
        {
            return -2;
        }
        int i;
        try
        {
            i = stmt.executeUpdate(cmdText);
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null,
                    ex);
            i = -1;
        }
        closeConnection(stmt);
        return i;
    }

    /**
     * 執行 SQL 語句,返回結果為整型
     * 主要用于執行非查詢語句
     * @param cmdText SQL 語句
     * @return 非負數:正常執行; -1:執行錯誤; -2:連接錯誤
     */
    public static int ExecSql(Connection conn, String cmdText)
    {
        Statement stmt = getStatement(conn);
        if (stmt == null)
        {
            return -2;
        }
        int i;
        try
        {
            i = stmt.executeUpdate(cmdText);

        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null,
                    ex);
            i = -1;
        }
        close(stmt);
        return i;
    }

    /**
     * 執行 SQL 語句,返回結果為整型
     * 主要用于執行非查詢語句
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 非負數:正常執行; -1:執行錯誤; -2:連接錯誤
     */
    public static int ExecSql(String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams);
        if (pstmt == null)
        {
            return -2;
        }
        int i;
        try
        {
            i = pstmt.executeUpdate();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null,
                    ex);
            i = -1;
        }
        closeConnection(pstmt);
        return i;
    }

    /**
     * 執行 SQL 語句,返回結果為整型
     * 主要用于執行非查詢語句
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 非負數:正常執行; -1:執行錯誤; -2:連接錯誤
     */
    public static int ExecSql(Connection conn, String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams);
        if (pstmt == null)
        {
            return -2;
        }
        int i;
        try
        {
            i = pstmt.executeUpdate();

        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            i = -1;
        }
        close(pstmt);
        return i;
    }

    /**
     * 返回結果集的第一行的一列的值,其他忽略
     * @param cmdText SQL 語句
     * @return
     */
    public static Object ExecScalar(String cmdText)
    {
        ResultSet rs = getResultSet(cmdText);
        Object obj = buildScalar(rs);
        closeConnection(rs);
        return obj;
    }

    /**
     * 返回結果集的第一行的一列的值,其他忽略
     * @param conn 數據庫連接
     * @param cmdText SQL 語句
     * @return
     */
    public static Object ExecScalar(Connection conn, String cmdText)
    {
        ResultSet rs = getResultSet(conn, cmdText);
        Object obj = buildScalar(rs);
        closeEx(rs);
        return obj;
    }

    /**
     * 返回結果集的第一行的一列的值,其他忽略
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return
     */
    public static Object ExecScalar(String cmdText, Object... cmdParams)
    {
        ResultSet rs = getResultSet(cmdText, cmdParams);
        Object obj = buildScalar(rs);
        closeConnection(rs);
        return obj;
    }

    /**
     * 返回結果集的第一行的一列的值,其他忽略
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return
     */
    public static Object ExecScalar(Connection conn, String cmdText, Object... cmdParams)
    {
        ResultSet rs = getResultSet(conn, cmdText, cmdParams);
        Object obj = buildScalar(rs);
        closeEx(rs);
        return obj;
    }

    /**
     * 返回一個 ResultSet
     * @param cmdText SQL 語句
     * @return
     */
    public static ResultSet getResultSet(String cmdText)
    {
        Statement stmt = getStatement();
        if (stmt == null)
        {
            return null;
        }
        try
        {
            return stmt.executeQuery(cmdText);
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            closeConnection(stmt);
        }
        return null;
    }

    /**
     * 返回一個 ResultSet
     * @param conn
     * @param cmdText SQL 語句
     * @return
     */
    public static ResultSet getResultSet(Connection conn, String cmdText)
    {
        Statement stmt = getStatement(conn);
        if (stmt == null)
        {
            return null;
        }
        try
        {
            return stmt.executeQuery(cmdText);
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            close(stmt);
        }
        return null;
    }

    /**
     * 返回一個 ResultSet
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return
     */
    public static ResultSet getResultSet(String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams);
        if (pstmt == null)
        {
            return null;
        }
        try
        {
            return pstmt.executeQuery();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            closeConnection(pstmt);
        }
        return null;
    }

    /**
     * 返回一個 ResultSet
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return
     */
    public static ResultSet getResultSet(Connection conn, String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams);
        if (pstmt == null)
        {
            return null;
        }
        try
        {
            return pstmt.executeQuery();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            close(pstmt);
        }
        return null;
    }

    public static Object buildScalar(ResultSet rs)
    {
        if (rs == null)
        {
            return null;
        }
        Object obj = null;
        try
        {
            if (rs.next())
            {
                obj = rs.getObject(1);
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return obj;
    }

    /**
     * 從 ResultSet 中構建 DefaultTableModel
     * @param rs ResultSet
     * @return
     */
    public static DefaultTableModel buildTableModel(ResultSet rs)
    {
        if (rs == null)
        {
            return null;
        }
        ResultSetMetaData rsm;
        String[] columnNames = null; // 列標題
        Object[][] data = null; // 數據項
        DefaultTableModel model; // 表格模型
        try
        {
            // 查詢語句
            rsm = rs.getMetaData();
            // 判斷時候可以實現對數據庫的更新
            if (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE)
            {
                System.out.println("Can UPDATABLE");
            } else
            {
                System.out.println("Only Ready");
            }
            // 獲取列標題
            columnNames = new String[rsm.getColumnCount()];
            for (int i = 0; i < rsm.getColumnCount(); i++)
            {
                columnNames[i] = rsm.getColumnName(i + 1);
            }

            int row = 0;
            int colum = 0;
            int columCount = rsm.getColumnCount();
            // 獲取行數,沒有直接的方法,這里先移動到紀錄結尾,獲取行號,即為行數,然后再移回來
            rs.last();
            int rowCount = rs.getRow();
            rs.beforeFirst();
            System.out.println("rowCount:" + rowCount);
            // 讀取數據到數據項變量
            data = new Object[rowCount][columCount];
            while (rs.next())
            {
                for (colum = 0; colum < rsm.getColumnCount(); colum++)
                {
                    data[row][colum] = rs.getObject(colum + 1);
                }
                row++;
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null,
                    ex);
            return null;
        }

        // 初始化數據模型
        model = new DefaultTableModel(data, columnNames)
        {

            /**
             * 重寫 getColumnClass 可以使表格自動識別數據類型
             */
            @SuppressWarnings("unchecked")
            @Override
            public Class getColumnClass(int c)
            {
                // 這里要對空數據集進行檢驗
                if (dataVector.isEmpty() == false && getValueAt(0, c) != null)
                {
                    return getValueAt(0, c).getClass();
                } else
                {
                    return Object.class;
                }
            }
        };

        return model;
    }

    /**
     * 獲取一個數據模型 該表格模型只能用來顯示數據，如果需要更新數據，請使用 DataSet getDataSet(String cmdText)
     *
     * @param conn 數據庫連接
     * @param cmdText
     *                能返回一個數據集的查詢SQL 語句
     * @return 表格數據模型
     */
    public static DefaultTableModel getTableModel(Connection conn, String cmdText)
    {
        ResultSet rs = getResultSet(conn, cmdText);
        DefaultTableModel tm = buildTableModel(rs);
        closeEx(rs);
        return tm;
    }

    /**
     * 獲取一個數據模型 該表格模型只能用來顯示數據，如果需要更新數據，請使用 DataSet getDataSet(String cmdText)
     *
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 表格數據模型
     */
    public static DefaultTableModel getTableModel(String cmdText, Object... cmdParams)
    {
        // 查詢語句
        ResultSet rs = getResultSet(cmdText, cmdParams);
        DefaultTableModel tm = buildTableModel(rs);
        closeConnection(rs);
        return tm;
    }

    /**
     * 獲取一個數據模型 該表格模型只能用來顯示數據，如果需要更新數據，請使用 DataSet getDataSet(String cmdText)
     *
     * @param cmdText
     *                能返回一個數據集的查詢SQL 語句
     * @return 表格數據模型
     */
    public static DefaultTableModel getTableModel(String cmdText)
    {
        // 查詢語句
        ResultSet rs = getResultSet(cmdText);
        DefaultTableModel tm = buildTableModel(rs);
        closeConnection(rs);
        return tm;
    }

    /**
     * 獲取一個數據模型 該表格模型只能用來顯示數據，如果需要更新數據，請使用 DataSet getDataSet(String cmdText)
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 表格數據模型
     */
    public static DefaultTableModel getTableModel(Connection conn, String cmdText, Object... cmdParams)
    {
        ResultSet rs = getResultSet(conn, cmdText, cmdParams);
        DefaultTableModel tm = buildTableModel(rs);
        closeEx(rs);
        return tm;
    }

    /**
     * 獲取一個具有更新功能的數據模型 如果只要讀取數據，就不要用它了
     * @param cmdText 能返回一個數據集的查詢SQL 語句
     * @return 表格數據模型
     */
   /* public static DataSet getDataSet(String cmdText)
    {
        Statement stmt = getStatement();
        DataSet dbc = new DataSet();
        if (stmt == null)
        {
            dbc.code = -2;
            return dbc;
        }
        try
        {
            // 查詢語句
            dbc.rs = stmt.executeQuery(cmdText);
            dbc.model = buildTableModel(dbc.rs);
            dbc.code = dbc.model.getRowCount();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            dbc.code = -1;
        }

        return dbc;
    }*/

    /**
     * 獲取一個具有更新功能的數據模型 如果只要讀取數據，就不要用它了
     * @param conn 數據庫連接
     * @param cmdText 能返回一個數據集的查詢SQL 語句
     * @return 表格數據模型
     */
   /* public static DataSet getDataSet(Connection conn, String cmdText)
    {
        Statement stmt = getStatement(conn);
        DataSet dbc = new DataSet();
        if (stmt == null)
        {
            dbc.code = -2;
            return dbc;
        }
        try
        {
            // 查詢語句
            dbc.rs = stmt.executeQuery(cmdText);
            dbc.model = buildTableModel(dbc.rs);
            dbc.code = dbc.model.getRowCount();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            dbc.code = -1;
        }

        return dbc;
    }*/

    /**
     * 獲取一個具有更新功能的數據模型 如果只要讀取數據，就不要用它了
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 表格數據模型
     */
   /* public static DataSet getDataSet(String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(cmdText, cmdParams);
        DataSet dbc = new DataSet();
        if (pstmt == null)
        {
            dbc.code = -2;
            return dbc;
        }
        try
        {
            // 查詢語句
            dbc.rs = pstmt.executeQuery();
            dbc.model = buildTableModel(dbc.rs);
            dbc.code = dbc.model.getRowCount();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            dbc.code = -1;
        }

        return dbc;
    }
*/
    /**
     * 獲取一個具有更新功能的數據模型 如果只要讀取數據，就不要用它了
     * @param conn 數據庫連接
     * @param cmdText 需要 ? 參數的 SQL 語句
     * @param cmdParams SQL 語句的參數表
     * @return 表格數據模型
     */
   /* public static DataSet getDataSet(Connection conn, String cmdText, Object... cmdParams)
    {
        PreparedStatement pstmt = getPreparedStatement(conn, cmdText, cmdParams);
        DataSet dbc = new DataSet();
        if (pstmt == null)
        {
            dbc.code = -2;
            return dbc;
        }
        try
        {
            // 查詢語句
            dbc.rs = pstmt.executeQuery();
            dbc.model = buildTableModel(dbc.rs);
            dbc.code = dbc.model.getRowCount();
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
            dbc.code = -1;
        }
        return dbc;
    }*/

    private static void close(Object obj)
    {
        if (obj == null)
        {
            return;
        }
        try
        {
            if (obj instanceof Statement)
            {
                ((Statement) obj).close();
            } else if (obj instanceof PreparedStatement)
            {
                ((PreparedStatement) obj).close();
            } else if (obj instanceof ResultSet)
            {
                ((ResultSet) obj).close();
            } else if (obj instanceof Connection)
            {
                ((Connection) obj).close();
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static void closeEx(Object obj)
    {
        if (obj == null)
        {
            return;
        }

        try
        {
            if (obj instanceof Statement)
            {
                ((Statement) obj).close();
            } else if (obj instanceof PreparedStatement)
            {
                ((PreparedStatement) obj).close();
            } else if (obj instanceof ResultSet)
            {
                ((ResultSet) obj).getStatement().close();
            } else if (obj instanceof Connection)
            {
                ((Connection) obj).close();
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    private static void closeConnection(Object obj)
    {
        if (obj == null)
        {
            return;
        }
        try
        {
            if (obj instanceof Statement)
            {
                ((Statement) obj).getConnection().close();
            } else if (obj instanceof PreparedStatement)
            {
                ((PreparedStatement) obj).getConnection().close();
            } else if (obj instanceof ResultSet)
            {
                ((ResultSet) obj).getStatement().getConnection().close();
            } else if (obj instanceof Connection)
            {
                ((Connection) obj).close();
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(SQLHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}    

    

    

    

    

    

    

    
DepartDemo.java
    
      /**
 * 
 */
package demo2010.DAL;
/**
 * @Project:JavaDemoDB2010
 * @Package:demo2010.DAL.DepartDemo
 * @TypeName:DepartDemo
 * @FileName:DepartDemo.java
 * @Author Administrator:downmoon(3w@live.cn)
 * @CreateDate: 2010-9-20 上午10:25:56
 * @Version: 
 * @Comment: TODO
 */
/***********************************************************************
 * Module:  DepartDemo.java
 * Author:  Administrator
 * Purpose: Defines the Class DepartDemo
 ***********************************************************************/
import java.sql.ResultSet;
import java.util.*;
import demo2010.database.SQLHelper;

/** @pdOid 57256f7a-9449-41c6-93a5-0eef465552d4 */
public class DepartDemo {
   /** @pdOid 437bfdf8-3750-4db5-af5d-50747eb01559 */
   private long PKID;
   /** @pdOid 5ebafadf-55ac-4fb1-8641-439df447cc24 */
   private java.lang.String DName;
   /** @pdOid c896af73-a72a-4b8e-96ae-cb5ba5ad5792 */
   private java.lang.String DCode;
   /** @pdOid 53fe19a9-eb7b-4ac6-acaa-a508475f5692 */
   private java.lang.String Manager;
   /** @pdOid d950a7fc-2ea5-4653-aab3-fbdc616edbb2 */
   private int ParentID = 0;
   /** @pdOid b389d149-b255-47cb-928e-a0adcda19639 */
   private java.lang.String AddUser;
   /** @pdOid 824e133e-f450-4a32-a4be-326527337c66 */
   private java.util.Date AddTime;
   /** @pdOid 4845ee5e-93cf-4f1b-8478-f7ab983cd549 */
   private java.lang.String ModUser;
   /** @pdOid 0d89814b-5879-43a7-9c43-e88013ae96c8 */
   private java.util.Date ModTime;
   /** @pdOid cca1880e-0b09-47af-baf5-9fea47998e06 */
   private short CurState = 0;
   /** @pdOid cd288b50-8b3a-4ff4-96f8-34777c772558 */
   private java.lang.String Remark;
   /** @pdOid 8f8bc089-6df7-4f1b-b647-697b12de08cc */
   private int F1 = 0;
   /** @pdOid 43ebcbf9-c5ac-4346-9624-ab6a3c1afd9a */
   private java.lang.String F2;
   
   /** @pdOid cd03a547-3f8a-4e78-a006-dff1d4e1d359 */
   public long getPKID() {
      return PKID;
   }
   
   /** @param newPKID
    * @pdOid 8f40d44d-04d2-4a10-a1ef-42b337257747 */
   public void setPKID(long newPKID) {
      PKID = newPKID;
   }
   
   /** @pdOid 94341fa0-b4e2-4a85-b155-70ce5f90ae5f */
   public java.lang.String getDName() {
      return DName;
   }
   
   /** @param newDName
    * @pdOid 11f173c3-acbe-4846-a3a7-62b10f4f53b4 */
   public void setDName(java.lang.String newDName) {
      DName = newDName;
   }
   
   /** @pdOid 5f8e85b9-2219-4fee-ae6f-fc48e8fca92e */
   public java.lang.String getDCode() {
      return DCode;
   }
   
   /** @param newDCode
    * @pdOid f419799f-b64c-44cb-832b-71658a3a6363 */
   public void setDCode(java.lang.String newDCode) {
      DCode = newDCode;
   }
   
   /** @pdOid b125d8b8-4861-40c0-9918-683aa3510e3d */
   public java.lang.String getManager() {
      return Manager;
   }
   
   /** @param newManager
    * @pdOid 8066a269-a08c-451e-ab41-0e8adceca178 */
   public void setManager(java.lang.String newManager) {
      Manager = newManager;
   }
   
   /** @pdOid b6581403-2003-4059-b46a-b686e8fe87a7 */
   public int getParentID() {
      return ParentID;
   }
   
   /** @param newParentID
    * @pdOid 21272935-8797-4d46-882f-415677f7545f */
   public void setParentID(int newParentID) {
      ParentID = newParentID;
   }
   
   /** @pdOid 662710f6-bc84-4071-90da-b8f9bd639174 */
   public java.lang.String getAddUser() {
      return AddUser;
   }
   
   /** @param newAddUser
    * @pdOid c0642e79-f4c3-4dd9-ac79-ba06db58794b */
   public void setAddUser(java.lang.String newAddUser) {
      AddUser = newAddUser;
   }
   
   /** @pdOid ad230e1d-1caa-4568-8778-b3431bdbf542 */
   public java.util.Date getAddTime() {
      return AddTime;
   }
   
   /** @param newAddTime
    * @pdOid 7394dcb9-efd2-412f-81da-d329a67cf247 */
   public void setAddTime(java.util.Date newAddTime) {
      AddTime = newAddTime;
   }
   
   /** @pdOid 5d16cd72-49f3-40b6-b271-fec20375deef */
   public java.lang.String getModUser() {
      return ModUser;
   }
   
   /** @param newModUser
    * @pdOid 09ab4834-5cc0-40ef-8f84-ce6787d1f99b */
   public void setModUser(java.lang.String newModUser) {
      ModUser = newModUser;
   }
   
   /** @pdOid 96f2da21-bc48-4e0e-a5a1-2db386424401 */
   public java.util.Date getModTime() {
      return ModTime;
   }
   
   /** @param newModTime
    * @pdOid 93c8126d-0ce7-48f4-826a-6265da194c1b */
   public void setModTime(java.util.Date newModTime) {
      ModTime = newModTime;
   }
   
   /** @pdOid a464afb2-fd2f-4d79-b35b-d8e0b6ef6ea5 */
   public short getCurState() {
      return CurState;
   }
   
   /** @param newCurState
    * @pdOid 1e0fa26a-ba8f-416d-ae7a-e428479593c7 */
   public void setCurState(short newCurState) {
      CurState = newCurState;
   }
   
   /** @pdOid 362cbcd2-f2b7-4764-b29c-3895fe980e32 */
   public java.lang.String getRemark() {
      return Remark;
   }
   
   /** @param newRemark
    * @pdOid f77ee744-cd9b-4b54-8b2a-3b7d41f1727e */
   public void setRemark(java.lang.String newRemark) {
      Remark = newRemark;
   }
   
   /** @pdOid 16f132e7-ae70-4e97-a46e-26325c88980b */
   public int getF1() {
      return F1;
   }
   
   /** @param newF1
    * @pdOid 8ebb8cc2-f113-4486-93f9-629d374aa639 */
   public void setF1(int newF1) {
      F1 = newF1;
   }
   
   /** @pdOid ad061b23-d17b-408e-a3fa-d6817deaf3ff */
   public java.lang.String getF2() {
      return F2;
   }
   
   /** @param newF2
    * @pdOid 746c8dd2-288b-4478-bfac-83af9ba736be */
   public void setF2(java.lang.String newF2) {
      F2 = newF2;
   }
   
    /*
     * Find User by UserName
     */
    public static DepartDemo FindByPKID(int _pkid) 
    {
        
        DepartDemo depart = new DepartDemo();
        try {
                    
            ResultSet rs = SQLHelper.getResultSet("SELECT * FROM DepartDemo WHERE PKID = ?",_pkid);
            if (rs.next()) 
            {
                depart.setPKID(rs.getInt("PKID"));
                depart.setDName(rs.getString("DName"));
                depart.setManager(rs.getString("Manager"));
                depart.setDCode(rs.getString("DCode"));
                depart.setParentID(rs.getInt("ParentID"));
                depart.setAddUser(rs.getString("AddUser"));
                depart.setModUser(rs.getString("ModUser"));
                //depart.setAddTime(DataConvert.StrTosqlDate(rs.getString("AddTime")));
                depart.setAddTime(rs.getDate("AddTime"));
                depart.setModTime(rs.getDate("ModTime"));
                depart.setF1(rs.getInt("F1"));
                depart.setF2(rs.getString("F2"));
                depart.setRemark(rs.getString("Remark"));
            }
            else 
            {
                depart = null;
            }
        } 
        catch (Exception e) 
        {
            System.err.println(e);
        }
        
        return depar      

      

      

      

      

      

      

      

      

      

      

      

      

    
    

    

    

    

    
測試代碼
    
    

    

    

    

    

    package demo2010.test;
import demo2010.DAL.DepartDemo;
public class TestDbMain {
/**
* @param args
*/
public static void main(String[] args) {
GetDepartDemoByPKID(101);
}
public static void GetDepartDemoByPKID(int pkid) {
try {
DepartDemo d = DepartDemo.FindByPKID(pkid);
if (d != null) {
System.out.println("部門："+d.getDName());
System.out.println("領導："+d.getManager());
System.out.println("職能："+d.getRemark());
}
} catch (Exception e1) {
System.out.println("出錯:" + e1);
}
}
}
    

    

    

    

    

  

    測試結果
    

    

    

    

    

    

    

    

    

    

    

    

    

    

    

    
為了更加清楚地說明數據連接的有關特性，我們用一種更原始的數據讀取方式：
  

    DBConnect.java的代碼如下：
    

    

    

    

    /**
*
*/
package demo2010.database;
/**
* @Project:JavaDemoDB2010
* @Package:demo2010.database.DBConnect
* @TypeName:DBConnect
* @FileName:DBConnect.java
* @Author Administrator:downmoon(3w@live.cn)
* @CreateDate: 2010-9-20 上午09:12:21
* @Version:
* @Comment: TODO
*/
import java.sql.*;
import demo2010.database.DBConnectionManager;
public class DBConnect {
private Connection conn = null;
private Statement stmt = null;
private PreparedStatement prepstmt = null;
private DBConnectionManager dcm = null;
void init() {
dcm = DBConnectionManager.getInstance();
conn = dcm.getConnection("sqlserver");
}
/**
* 構造數據庫的連接和訪問類
*/
public DBConnect() throws Exception {
init();
stmt = conn.createStatement();
}
public DBConnect(int resultSetType, int resultSetConcurrency)
throws Exception {
init();
stmt = conn.createStatement(resultSetType, resultSetConcurrency);
}
/**
* 構造數據庫的連接和訪問類 預編譯SQL語句
*
* @param sql
* SQL語句
*/
public DBConnect(String sql) throws Exception {
init();
this.prepareStatement(sql);
}
public DBConnect(String sql, int resultSetType, int resultSetConcurrency)
throws Exception {
init();
this.prepareStatement(sql, resultSetType, resultSetConcurrency);
}
/**
* 返回連接
*
* @return Connection 連接
*/
public Connection getConnection() {
return conn;
}
/**
* PreparedStatement
*
* @param sql
* 預設SQL語句
*/
public void prepareStatement(String sql) throws SQLException {
prepstmt = conn.prepareStatement(sql);
}
public void prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
prepstmt = conn.prepareStatement(sql, resultSetType,
resultSetConcurrency);
}
/**
* 設置對應值
*
* @param index
* 參數索引
* @param value
* 對應值
*/
public void setString(int index, String value) throws SQLException {
prepstmt.setString(index, value);
}
public void setInt(int index, int value) throws SQLException {
prepstmt.setInt(index, value);
}
public void setBoolean(int index, boolean value) throws SQLException {
prepstmt.setBoolean(index, value);
}
public void setDate(int index, Date value) throws SQLException {
prepstmt.setDate(index, value);
}
public void setTimestamp(int index, Timestamp value) throws SQLException {
prepstmt.setTimestamp(index, value);
}
public void setLong(int index, long value) throws SQLException {
prepstmt.setLong(index, value);
}
public void setFloat(int index, float value) throws SQLException {
prepstmt.setFloat(index, value);
}
public void setBytes(int index, byte[] value) throws SQLException {
prepstmt.setBytes(index, value);
}
public void clearParameters() throws SQLException {
prepstmt.clearParameters();
prepstmt = null;
}
/**
* 返回預設狀態
*/
public PreparedStatement getPreparedStatement() {
return prepstmt;
}
/**
* 返回狀態
*
* @return Statement 狀態
*/
public Statement getStatement() {
return stmt;
}
/**
* 執行SQL語句返回字段集
*
* @param sql
* SQL語句
* @return ResultSet 字段集
*/
public ResultSet executeQuery(String sql) throws SQLException {
if (stmt != null) {
return stmt.executeQuery(sql);
} else
return null;
}
public ResultSet executeQuery() throws SQLException {
if (prepstmt != null) {
return prepstmt.executeQuery();
} else
return null;
}
/**
* 執行SQL語句
*
* @param sql
* SQL語句
*/
public void executeUpdate(String sql) throws SQLException {
if (stmt != null)
stmt.executeUpdate(sql);
}
public void executeUpdate() throws SQLException {
if (prepstmt != null)
prepstmt.executeUpdate();
}
/**
* 關閉連接
*/
public void close() throws Exception {
if (stmt != null) {
stmt.close();
stmt = null;
}
if (prepstmt != null) {
prepstmt.close();
prepstmt = null;
}
if (conn != null) {
dcm.freeConnection("sqlserver", conn);
}
}
}