using System;
using System.Data;
using System.Data.SqlClient;namespace Examples
{
/// <summary>
/// sWinZ(死蚊子)
/// </summary>
public class SqlDataProvider
{
private string SqlConString; public SqlDataProvider()
{
SqlConString = "data source=127.0.0.1;initial catalog=Examples;user id=sa;password=1";
} public SqlParameter InputSP(string SPName, SqlDbType SDT, int Size, object Value)
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Input, Value);
} public SqlParameter OutputSP(string SPName, SqlDbType SDT, int Size)
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Output, null);
} private SqlParameter CreateSP(string SPName, SqlDbType SDT, int Size, ParameterDirection PD, object Value)
{
SqlParameter SP = new SqlParameter(SPName, SDT, Size); SP.Direction = PD;
if ( PD == ParameterDirection.Input )
{
SP.Value = Value;
}
return SP;
} public void ExecuteNonQuery(string StoredProcedureName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
SqlCom.ExecuteNonQuery();
}
} public void ExecuteNonQuery(string StoredProcedureName, SqlParameter[] SPs)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs); SqlCon.Open();
SqlCom.ExecuteNonQuery();
}
} public SqlDataReader ExecuteReader(string StoredProcedureName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
} public SqlDataReader ExecuteReader(string StoredProcedureName, SqlParameter[] SPs)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
} public DataSet ExecuteDataSet(string StoredProcedureName, string TableName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null);
SqlDataAdapter SDA = new SqlDataAdapter(SqlCom);
DataSet DS = new DataSet(); SDA.Fill(DS, TableName);
return DS;
}
} public DataSet ExecuteDataSet(string StoredProcedureName, SqlParameter[] SPs, string TableName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs);
SqlDataAdapter SDA = new SqlDataAdapter(SqlCom);
DataSet DS = new DataSet(); SDA.Fill(DS, TableName);
return DS;
}
} private SqlCommand CreateSqlCom(string StoredProcedureName, SqlConnection SqlCon, SqlParameter[] SPs)
{
SqlCommand SqlCom = new SqlCommand(StoredProcedureName, SqlCon); SqlCom.CommandType = CommandType.StoredProcedure;
if ( SPs != null )
{
foreach ( SqlParameter SP in SPs)
{
SqlCom.Parameters.Add(SP);
}
}
return SqlCom;
}
}
}
using System.Data;
using System.Data.SqlClient;namespace Examples
{
/// <summary>
/// sWinZ(死蚊子)
/// </summary>
public class SqlDataProvider
{
private string SqlConString; public SqlDataProvider()
{
SqlConString = "data source=127.0.0.1;initial catalog=Examples;user id=sa;password=1";
} public SqlParameter InputSP(string SPName, SqlDbType SDT, int Size, object Value)
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Input, Value);
} public SqlParameter OutputSP(string SPName, SqlDbType SDT, int Size)
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Output, null);
} private SqlParameter CreateSP(string SPName, SqlDbType SDT, int Size, ParameterDirection PD, object Value)
{
SqlParameter SP = new SqlParameter(SPName, SDT, Size); SP.Direction = PD;
if ( PD == ParameterDirection.Input )
{
SP.Value = Value;
}
return SP;
} public void ExecuteNonQuery(string StoredProcedureName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
SqlCom.ExecuteNonQuery();
}
} public void ExecuteNonQuery(string StoredProcedureName, SqlParameter[] SPs)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs); SqlCon.Open();
SqlCom.ExecuteNonQuery();
}
} public SqlDataReader ExecuteReader(string StoredProcedureName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
} public SqlDataReader ExecuteReader(string StoredProcedureName, SqlParameter[] SPs)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
} public DataSet ExecuteDataSet(string StoredProcedureName, string TableName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null);
SqlDataAdapter SDA = new SqlDataAdapter(SqlCom);
DataSet DS = new DataSet(); SDA.Fill(DS, TableName);
return DS;
}
} public DataSet ExecuteDataSet(string StoredProcedureName, SqlParameter[] SPs, string TableName)
{
using ( SqlConnection SqlCon = new SqlConnection(SqlConString) )
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs);
SqlDataAdapter SDA = new SqlDataAdapter(SqlCom);
DataSet DS = new DataSet(); SDA.Fill(DS, TableName);
return DS;
}
} private SqlCommand CreateSqlCom(string StoredProcedureName, SqlConnection SqlCon, SqlParameter[] SPs)
{
SqlCommand SqlCom = new SqlCommand(StoredProcedureName, SqlCon); SqlCom.CommandType = CommandType.StoredProcedure;
if ( SPs != null )
{
foreach ( SqlParameter SP in SPs)
{
SqlCom.Parameters.Add(SP);
}
}
return SqlCom;
}
}
}
或者NHibernate等ORM框架 确实要自己写得话,可以使用工厂模式,
可以切换数据库而无需修改代码。
不错啊
---------------------------
那你的SqlConString最好还是不要硬编码,
可以使用配置文件,方便修改
public SqlDataProvider():this(null)
{
} //增加
public SqlDataProvider(string connString)
{
if (conn == null)
SqlConString = "data source=127.0.0.1;initial catalog=Examples;user id=sa;password=1";
else
SqlConString = connString;
}
使引用类不需再using System.Data.SqlClient;命名空间,
public IDataReader ExecuteReader(string StoredProcedureName)
{
using (SqlConnection SqlCon = new SqlConnection(SqlConString))
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
} public IDataReader ExecuteReader(string StoredProcedureName, SqlParameter[] SPs)
{
using (SqlConnection SqlCon = new SqlConnection(SqlConString))
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, SPs); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
}
比如
public IDataReader ExecuteReader(string StoredProcedureName, string DbType)
{
switch(DbBType)
{
case("sql")":
using (SqlConnection SqlCon = new SqlConnection(SqlConString))
{
SqlCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
break;
case("oledb"):
using (OleDBConnection SqlCon = new OleDBConnection(SqlConString))
{
OleDBCommand SqlCom = CreateSqlCom(StoredProcedureName, SqlCon, null); SqlCon.Open();
return SqlCom.ExecuteReader(CommandBehavior.CloseConnection);
}
break;
}
}
这样你可以把DBType写在配置文件里面,换成其他的数据库,就不需要重新写数据访问层了.
不然每次都要编译,不太方便,楼主认为呢
{
SqlConString = "data source=127.0.0.1;initial catalog=Examples;user id=sa;password=1";
}
这个改一个改成在webconfig提取还有好象不能执行存储过程,异常处理也做的不是很好希望改进
{
SqlConString = "data source=127.0.0.1;initial catalog=Examples;user id=sa;password=1";
}public SqlDataProvider(string SqlConStr)
{
SqlConString = SqlConStr;
}
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Input, Value);
}public SqlParameter OutputSP(string SPName, SqlDbType SDT, int Size)
{
return CreateSP(SPName, SDT, Size, ParameterDirection.Output, null);
}修改为:public SqlParameter CreateInputParam( string name, SqlDbType type, int size, object value )public SqlParameter CreateOutputParam( string name, SqlDbType type, int size )甚至于:public SqlParameter CreateParameter( string name, SqlDbType type, int size, object value )public SqlParameter CreateParameter( string name, SqlDbType type, int size )
CreateSP -> CreateParameterInternal or _CreateParameterSP可能是StoreProducer(存储过程),SDT也可能是Standard Design Template(标准设计模板),根本就没人能看懂……。
通过这些接口可以实现和数据库类型隔离的数据访问对象.
你的类缺少了一个重要的特性就是数据库事务处理。有兴趣以到我的blog看下SQL Artisan 组件。
1. 没有使用接口定义
2. 代码命名不规范,(如缩写,大小写,变量和函数命名等等)
3. 硬编码,构造函数那里(前面已有人提出了)
4. public的函数没有参数检查和异常处理,全部吞掉了.