最近在开发一个新闻系统;
新闻系统本是一个非常之简单的东西;
以前用ASP做开发的时候,这些东西,不管界面的话,基本上可以说一天就可以写出来的(当然也要看功能和复杂程度啦)
在用.Net也没有做过多少开发;可以说现在追求的只是一种结果;
由于刚开始学,也只能追求一种结果,先达到会用,不管效率和安全,只要实现就行了;
当然这种东西做出来也只是做为学习和交流所用吧,不能应用于商业和网站;
但做为一个新手来说的,一般公司是不会让你直接去写或用你的写出来的东西的
所有只有自己先多练练技术啦……
呵呵~~
这里只是一个数据库的开发,因为考虑到同时要用SQL语句和存储过程,所以在整个数据层中可以用SQL或存储过程两种方式调用的;在这个DEMO过程中请大家多多指教;一起交流一下.NET开发和三层架构
以下是代码:
using
System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace PowerLeader.Components
... {
/**//// <summary>
/// 数据层基类
/// </summary>
public class DataBase
...{
private SqlConnection myConnection;
private CommandType procCommandType;
public DataBase()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
Open&Close Connection.打开关闭数据库连接#region Open&Close Connection.打开关闭数据库连接
/**//// <summary>
/// Create and Open Connection 生成一个数据库连接
/// </summary>
private void Open()
...{
if (myConnection == null)
...{
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"]);
myConnection.Open();
}
}
/**//// <summary>
/// Close Connection 关闭数据库连接
/// </summary>
private void Close()
...{
if (myConnection != null)
myConnection.Close();
}
#endregion
Run stored procedure.执行一个存储过程#region Run stored procedure.执行一个存储过程
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure. 存储过程名</param>
/// <returns>Stored procedure return value.存储过程返回值</returns>
public int RunProc(string procName)
...{
SqlCommand cmd = CreateCommand(procName,null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["@v_return"].Value;
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程或SQL语句
/// </summary>
/// <param name="procName">Name of stored procedure. 存储过程名或SQL语句</param>
/// <param name="pams">Stored procedure params.存储过程参数</param>
/// <returns>Stored procedure return value.存储过程返回值</returns>
public int RunProc(string procName,SqlParameter[] pams)
...{
SqlCommand cmd = CreateCommand(procName,pams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["@v_return"].Value;
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名或SQL语句</param>
/// <param name="DataReader">Return result of procedure.存储过程返回值</param>
public void RunProc(string procName,out SqlDataReader DataReader)
...{
SqlCommand cmd = CreateCommand(procName,null);
DataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名或SQL语句</param>
/// <param name="pams">Stored procedure params.存储过程参数</param>
/// <param name="DataReader">Return result of procedure.存储过程返回值</param>
public void RunProc(string procName,SqlParameter[] pams,out SqlDataReader DataReader)
...{
SqlCommand cmd = CreateCommand(procName,pams);
DataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程#region Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程
/**//// <summary>
/// Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名</param>
/// <param name="prams">Params to stored procedure.存储过程参数</param>
/// <returns>Command object.返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName,SqlParameter[] pams)
...{
this.Open();
SqlCommand cmd = new SqlCommand(procName,myConnection);
if (pams!=null)
foreach(SqlParameter p in pams)
cmd.Parameters.Add(p);
// return param 返回参数
if (storedOrSqlCommandType == CommandType.Text) // (procName.Replace(" ","").Length != procName.Length)
...{
cmd.Parameters.Add(
new SqlParameter("@v_return", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
}
else
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
#endregion
Make input param.创建一个输入参数#region Make input param.创建一个输入参数
/**//// <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 param.返回一个新参数类型对象</returns>
public SqlParameter MakeInParam(string paramName,SqlDbType DbType,int size,object Value)
...{
return MakeParam(paramName,DbType,size,ParameterDirection.Input,Value);
}
#endregion
Make output param.创建一个输出参数#region Make output param.创建一个输出参数
/**//// <summary>
/// Make output param.创建一个输出参数
/// </summary>
/// <param name="paramName">Name of param. 参数名</param>
/// <param name="DbType">param type.参数类型</param>
/// <param name="size">param size. 参数值长度</param>
/// <returns>new param.返回一个新参数类型对象</returns>
public SqlParameter MakeOutParam(string paramName,SqlDbType DbType,int size)
...{
return MakeParam(paramName,DbType,size,ParameterDirection.Output,null);
}
#endregion
Make stored procedure param.创建一个存储过程参数#region Make stored procedure param.创建一个存储过程参数
/**//// <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 param.返回一个新参数类型对象</returns>
public SqlParameter MakeParam(
string paramName,
SqlDbType DbType,
int 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
根据查询SQL语句返回DataTable值#region 根据查询SQL语句返回DataTable值
/**//// <summary>
/// 查询SQL语句,返回DataTable
/// </summary>
/// <param name="procName">SQL语句参数或存储过程名</param>
/// <returns>返回DataTable</returns>
public DataTable GetAll(string procName)
...{
this.Open();
SqlDataAdapter oda = new SqlDataAdapter(procName,myConnection);
DataSet ds = new DataSet();
oda.Fill(ds,"articles");
this.Close();
return ds.Tables[0];
}
#endregion
public CommandType storedOrSqlCommandType
...{
get
...{
if (procCommandType == 0)
...{
procCommandType = CommandType.StoredProcedure;
}
return procCommandType;
}
set
...{
procCommandType = value;
}
}
}
}
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace PowerLeader.Components
... {
/**//// <summary>
/// 数据层基类
/// </summary>
public class DataBase
...{
private SqlConnection myConnection;
private CommandType procCommandType;
public DataBase()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
Open&Close Connection.打开关闭数据库连接#region Open&Close Connection.打开关闭数据库连接
/**//// <summary>
/// Create and Open Connection 生成一个数据库连接
/// </summary>
private void Open()
...{
if (myConnection == null)
...{
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"]);
myConnection.Open();
}
}
/**//// <summary>
/// Close Connection 关闭数据库连接
/// </summary>
private void Close()
...{
if (myConnection != null)
myConnection.Close();
}
#endregion
Run stored procedure.执行一个存储过程#region Run stored procedure.执行一个存储过程
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure. 存储过程名</param>
/// <returns>Stored procedure return value.存储过程返回值</returns>
public int RunProc(string procName)
...{
SqlCommand cmd = CreateCommand(procName,null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["@v_return"].Value;
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程或SQL语句
/// </summary>
/// <param name="procName">Name of stored procedure. 存储过程名或SQL语句</param>
/// <param name="pams">Stored procedure params.存储过程参数</param>
/// <returns>Stored procedure return value.存储过程返回值</returns>
public int RunProc(string procName,SqlParameter[] pams)
...{
SqlCommand cmd = CreateCommand(procName,pams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["@v_return"].Value;
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名或SQL语句</param>
/// <param name="DataReader">Return result of procedure.存储过程返回值</param>
public void RunProc(string procName,out SqlDataReader DataReader)
...{
SqlCommand cmd = CreateCommand(procName,null);
DataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/**//// <summary>
/// Run stored procedure.执行一个存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名或SQL语句</param>
/// <param name="pams">Stored procedure params.存储过程参数</param>
/// <param name="DataReader">Return result of procedure.存储过程返回值</param>
public void RunProc(string procName,SqlParameter[] pams,out SqlDataReader DataReader)
...{
SqlCommand cmd = CreateCommand(procName,pams);
DataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
#endregion
Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程#region Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程
/**//// <summary>
/// Create command object used to call stored procedure.创建一个SqlCommand对象并执行存储过程
/// </summary>
/// <param name="procName">Name of stored procedure.存储过程名</param>
/// <param name="prams">Params to stored procedure.存储过程参数</param>
/// <returns>Command object.返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName,SqlParameter[] pams)
...{
this.Open();
SqlCommand cmd = new SqlCommand(procName,myConnection);
if (pams!=null)
foreach(SqlParameter p in pams)
cmd.Parameters.Add(p);
// return param 返回参数
if (storedOrSqlCommandType == CommandType.Text) // (procName.Replace(" ","").Length != procName.Length)
...{
cmd.Parameters.Add(
new SqlParameter("@v_return", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
}
else
cmd.CommandType = CommandType.StoredProcedure;
return cmd;
}
#endregion
Make input param.创建一个输入参数#region Make input param.创建一个输入参数
/**//// <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 param.返回一个新参数类型对象</returns>
public SqlParameter MakeInParam(string paramName,SqlDbType DbType,int size,object Value)
...{
return MakeParam(paramName,DbType,size,ParameterDirection.Input,Value);
}
#endregion
Make output param.创建一个输出参数#region Make output param.创建一个输出参数
/**//// <summary>
/// Make output param.创建一个输出参数
/// </summary>
/// <param name="paramName">Name of param. 参数名</param>
/// <param name="DbType">param type.参数类型</param>
/// <param name="size">param size. 参数值长度</param>
/// <returns>new param.返回一个新参数类型对象</returns>
public SqlParameter MakeOutParam(string paramName,SqlDbType DbType,int size)
...{
return MakeParam(paramName,DbType,size,ParameterDirection.Output,null);
}
#endregion
Make stored procedure param.创建一个存储过程参数#region Make stored procedure param.创建一个存储过程参数
/**//// <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 param.返回一个新参数类型对象</returns>
public SqlParameter MakeParam(
string paramName,
SqlDbType DbType,
int 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
根据查询SQL语句返回DataTable值#region 根据查询SQL语句返回DataTable值
/**//// <summary>
/// 查询SQL语句,返回DataTable
/// </summary>
/// <param name="procName">SQL语句参数或存储过程名</param>
/// <returns>返回DataTable</returns>
public DataTable GetAll(string procName)
...{
this.Open();
SqlDataAdapter oda = new SqlDataAdapter(procName,myConnection);
DataSet ds = new DataSet();
oda.Fill(ds,"articles");
this.Close();
return ds.Tables[0];
}
#endregion
public CommandType storedOrSqlCommandType
...{
get
...{
if (procCommandType == 0)
...{
procCommandType = CommandType.StoredProcedure;
}
return procCommandType;
}
set
...{
procCommandType = value;
}
}
}
}