Sql表的内容需要建类吗?
老师布置了一个培训机构管理的项目,将各种实体的信息都建成了表结构。在用VS开发时,是直接调用数据呢,还是要在项目里建这些实体类呢?请各位大大指点下~ --------------------编程问答-------------------- 有一个SqlDBHelper类,操作SQL用的,你可以了解一下. --------------------编程问答-------------------- 既然是老师布置的任务,那就建吧。 --------------------编程问答-------------------- 我估计,老师的意思不是SQL表一定要建类,而是通过这个让你对类和SQL有深入的了解。 --------------------编程问答--------------------那一般实际开发中是怎么样的?
--------------------编程问答--------------------
实际开发中也有这样做的。。
我觉得他的好处是:方便数据传递、方便。 --------------------编程问答-------------------- 看你这个人的爱好 简单就不用了 如果你想锻炼了 还是建比较好 --------------------编程问答-------------------- 可以把 表都在项目中建对应的实体类,我就这么干的,不过这样代码好像多了 --------------------编程问答-------------------- 一般都建设实体类 --------------------编程问答-------------------- 要得。。。。 --------------------编程问答-------------------- sql表里哪有类。 --------------------编程问答-------------------- 需要将数据库中表建立对应的实体类,方便操作数据库 --------------------编程问答-------------------- 一个简单的数据库处理基类
可完成:添加、修改、删除数据,以及读取数据结果集(DataSet)
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace WorkFlow.CommonLibrary.DbHelper
{
enum ExecuteType
{
NonQuery,
Identity
}
public sealed class DbBaseHelper
{
private string _message = string.Empty;
private string _connectionString = "Server=.;Database=dbName;Uid=sa;PWD=sa;";
private SqlConnection _dbConnection = null;
private SqlCommand _sqlCommand = null;
private SqlDataAdapter _adapter = null;
private SqlTransaction _transaction = null;
private Stack stack = new Stack();
public string Message
{
get { return _message; }
}
public DbBaseHelper()
{
}
public DbBaseHelper(string connectionString)
{
_connectionString = connectionString;
}
public DbBaseHelper(DbBaseHelper db)
{
if (db != null)
{
this._transaction = db._transaction;
this._dbConnection = db._dbConnection;
this.stack = db.stack;
}
}
#region " 连接、关闭数据库 "
private bool Connect()
{
_message = string.Empty;
if (_dbConnection == null)
_dbConnection = new SqlConnection();
if (string.IsNullOrEmpty(_message))
{
if (_dbConnection.State == ConnectionState.Closed)
{
try
{
_dbConnection.Open();
}
catch (SqlException ex)
{
StringBuilder msg = new StringBuilder();
msg.AppendFormat("数据库连接错误:{0}", ex.Message);
if (ex.Number == 17)
msg.Append("SQL Server连接配置错误或无权访问!请检查连接配置或用户权限。");
_message = msg.ToString();
throw new Exception(msg.ToString(), ex);
}
}
}
return string.IsNullOrEmpty(_message);
}
private void Close()
{
if (_dbConnection.State == ConnectionState.Open)
_dbConnection.Close();
try
{
_dbConnection.Dispose();
}
catch { }
try
{
//_dbConnection = null;
}
catch { }
}
#endregion
#region " 事务处理 "
public void BeginTransaction()
{
this.Connect();
if (stack.Count == 0)
{
_transaction = _dbConnection.BeginTransaction("transcation");
stack.Push("transcation");
}
else
{
_transaction.Save("transactionSavePoint_" + stack.Count.ToString());
stack.Push("transactionSavePoint_" + stack.Count.ToString());
}
}
public void CommitTransaction()
{
if (_transaction != null)
{
try
{
if (stack.Count > 1)
stack.Pop();
else
{
stack.Pop();
_transaction.Commit();
_transaction.Dispose();
this.Close();
}
}
catch (SqlException ex)
{
this.RollBackTransaction();
StringBuilder msg = new StringBuilder();
msg.AppendFormat("提交数据库错误:{0}", ex.Message);
throw new Exception(msg.ToString(), ex);
}
}
}
public void RollBackTransaction()
{
if (_transaction != null)
{
if (stack.Count > 1)
_transaction.Rollback(stack.Pop().ToString());
else if (stack.Count == 1)
{
stack.Pop();
_transaction.Rollback();
_transaction.Dispose();
this.Close();
}
}
}
#endregion
--------------------编程问答--------------------
#region " **************** 初始化sqlCommand 和 Adapter 对象 ********************* "
private void InitSqlCommand(string sql)
{
InitSqlCommand(sql, false);
}
/// <summary>
/// 初始化command对象。
/// </summary>
/// <param name="sql_spName">要执行的sql语句或存储过程的名称</param>
/// <param name="isProc">是否执行的是存储过程</param>
private void InitSqlCommand(string sql_spName, bool isProc)
{
_sqlCommand = new SqlCommand(sql_spName, _dbConnection);
if (isProc)
{
_sqlCommand.CommandType = CommandType.StoredProcedure;
_sqlCommand.CommandTimeout = 1800;// '限定超时时间为30分钟
}
if (_transaction != null)
_sqlCommand.Transaction = _transaction;
}
private void AppendSqlParamters(List<SqlParameter> sqlParams)
{
if (sqlParams != null)
{
foreach (SqlParameter p in sqlParams)
{
_sqlCommand.Parameters.Add(p);
}
}
}
private void InitSqlDataAdapter()
{
if (_sqlCommand != null)
{
_adapter = new SqlDataAdapter(_sqlCommand);
if (_transaction != null)
_adapter.SelectCommand.Transaction = _transaction;
}
}
private void InitSqlDataAdapter(string sql)
{
_adapter = new SqlDataAdapter(sql, _dbConnection);
if (_transaction != null)
_adapter.SelectCommand.Transaction = _transaction;
}
#endregion
#region " 执行返回整数 "
public int ExecuteNonSql(string sql)
{
return Convert.ToInt32(this.ExecuteNonQuery(sql, ExecuteType.NonQuery, null));
}
public int ExecuteNonSql(string sql, List<SqlParameter> sqlParams)
{
return Convert.ToInt32(this.ExecuteNonQuery(sql, ExecuteType.NonQuery, sqlParams));
}
public long ExecuteNonSqlIdentity(string sql)
{
sql += " SELECT @@IDENTITY AS 'Identity' ";
return this.ExecuteNonQuery(sql, ExecuteType.Identity, null);
}
public long ExecuteNonSqlIdentity(string sql, List<SqlParameter> sqlParams)
{
sql += " SELECT @@IDENTITY AS 'Identity' ";
return this.ExecuteNonQuery(sql, ExecuteType.Identity, sqlParams);
}
private long ExecuteNonQuery(string sql, ExecuteType et, List<SqlParameter> sqlParams)
{
try
{
this.Connect();
InitSqlCommand(sql);
AppendSqlParamters(sqlParams);
if (et == ExecuteType.NonQuery)
return Convert.ToInt64(_sqlCommand.ExecuteNonQuery());
else if (et == ExecuteType.Identity)
{
object o = _sqlCommand.ExecuteScalar();
if (!object.Equals(o, null) && !object.Equals(o, DBNull.Value))
return Convert.ToInt64(o);
}
}
catch (SqlException ex)
{
_message = DbConnectionStringConfig.GetSqErrorlMessage(ex, sql);
throw;
}
finally
{
if (_sqlCommand != null)
{
_sqlCommand.Dispose(); _sqlCommand = null;
}
if (_transaction == null)
this.Close();
}
return 0;
}
#endregion
#region " 执行返回DataSet "
public DataSet ExecuteSql(string sql, string tablename)
{
return ExecuteToDataSet(sql, tablename, null);
}
public DataSet ExecuteSql(DataSet ds, string sql, string tablename)
{
return ExecuteToDataSet(sql, tablename, ds);
}
#endregion
#region " ExecuteToDataSet "
private DataSet ExecuteToDataSet(string sql, string tablename, DataSet ds)
{
try
{
this.Connect();
this.InitSqlDataAdapter(sql);
if (ds == null)
ds = new DataSet();
_adapter.Fill(ds, tablename);
}
catch (SqlException ex)
{
_message = DbConnectionStringConfig.GetSqErrorlMessage(ex, sql);
throw;
}
finally
{
if (_transaction == null)
this.Close();
}
return ds;
}
#endregion
/// <summary>
/// 返回结果集中的第一行数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataRow ExecuteSqlToDataRow(string sql)
{
DataRow row = null;
DataSet ds = this.ExecuteSql(sql, "row");
if (ds != null && ds.Tables[0].Rows.Count > 0)
row = ds.Tables[0].Rows[0];
if (ds != null) { ds.Dispose(); ds = null; }
return row;
}
}
}
补充:.NET技术 , C#