(关注-博客园两年了不好意思,以前一直没有时间写点现在觉得应该和大家分享点东西 )
如何从SqlDataReader中read出一个datatable 基本思想是
1>根据SQL去构造一个datatable,
2>根据SQL获得read的数据类型
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;
using Demo.DAL.COMM ;
namespace Demo.DAL
{
/// <summary>
/// DALDemo 的摘要说明。
/// </summary>
public class DALDemo: SQLHelper
{
public DALDemo()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public DataTable GetTable(string A_strCondition)
{
string strSQL="";
if (A_strCondition=="")
{
strSQL= "select top 100 UserID,UserName,Tel,PassWord,Address,Status from UserName ";
}
else
{
strSQL= "select UserID,UserName,Tel,PassWord,Address,Status from UserName where "+A_strCondition ;
}
SqlParameter[] arParams = new SqlParameter[0];
return GetTableValue(CommandType.Text,strSQL,arParams);
}
}
}
下面是基类
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;
namespace Demo.DAL.COMM
{
public abstract class SQLHelper
{
private string CONN_STRING = "Password= ;Persist Security Info=True;User ID=sa;Initial Catalog=Test1;Data Source=.";
protected System.Data.SqlClient.SqlConnection Con =new SqlConnection();
protected System.Data.SqlClient.SqlCommand Cmd =new SqlCommand();
protected System.Data.SqlClient.SqlDataAdapter Dtapt =new SqlDataAdapter();
protected System.Data.SqlClient.SqlTransaction sqlTran ;
public SqlDataReader dtrValue = null;
/// <summary>
/// 开始事务
/// </summary>
public void BeginTransection()
{
if (Con.State == ConnectionState.Closed)
{
//打开连接
OpenCn();
//开始事务
sqlTran = Con.BeginTransaction();
Cmd.Transaction = sqlTran;
}
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTransection()
{
sqlTran.Commit();
CloseCn();
sqlTran.Dispose();
sqlTran = null;
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTransection()
{
sqlTran.Rollback();
CloseCn();
sqlTran.Dispose();
sqlTran = null;
}
protected System.Data.SqlClient.SqlConnection GetConnection()
{
string strConnectString;
strConnectString = CONN_STRING;
SqlConnection Con1 = new SqlConnection(strConnectString);
return Con1;
}
/// <summary>
/// 打开数据连接
/// </summary>
protected void OpenCn()
{
//判断事务是否开始
if (sqlTran == null)
{
Con = GetConnection();
}
if (Con.State != ConnectionState.Open)
{
Con.Open();
}
}
/// <summary>
/// 关闭数据连接
/// </summary>
protected void CloseCn()
{
if (Con.State != ConnectionState.Closed)
{
Con.Close();
Con.Dispose();
Cmd.Dispose();
dtrValue.Close();
}
}
/// <summary>
/// ExecuteReader
/// </summary>
/// <param name="objCmdType">CommandType</param>
/// <param name="strCmdText">strSQL</param>
/// <param name="arrparmCmdParms">SqlParameter</param>
/// <returns></returns>
protected SqlDataReader ExecuteReader(CommandType objCmdType,string strSQL, SqlParameter[] arrparmCmdParms)
{
SqlDataReader dtrRet = null;
try
{
OpenCn();
Cmd.Connection = Con;
Cmd.CommandText = strSQL;
Cmd.CommandType = objCmdType;
if (arrparmCmdParms != null)
{
foreach (SqlParameter objParm in arrparmCmdParms)
Cmd.Parameters.Add(objParm);
}
dtrRet = Cmd.ExecuteReader();
Cmd.Parameters.Clear();
return dtrRet;
}
catch (Exception e)
{
if (Con.State != ConnectionState.Closed && sqlTran==null)
{
CloseCn();
}
Debug.Write(e.Message);
return null;
}
finally
{
if (Con.State != ConnectionState.Closed && sqlTran == null && dtrRet==null)
{
CloseCn();
}
}
}
/// <summary>
/// 返回表数据 未测试
/// </summary>
/// <param name="objCmdType"></param>
/// <param name="strSQL"></param>
/// <param name="arrparmCmdParms"></param>
/// <returns></returns>
protected DataTable ExecuteDataTable(CommandType objCmdType, string strSQL, SqlParameter[] arrparmCmdParms)
{
DataTable dtb = new DataTable();
try
{
OpenCn();
Cmd.Connection = Con;
Cmd.CommandText = strSQL;
Cmd.CommandType = objCmdType;
if (arrparmCmdParms != null)
{
foreach (SqlParameter objParm in arrparmCmdParms)
Cmd.Parameters.Add(objParm);
}
SqlDataAdapter Dtapt = new SqlDataAdapter(strSQL, Con);
DataSet dst = new DataSet();;
Dtapt.Fill(dst);
Cmd.Parameters.Clear();
dtb= dst.Tables[0] ;
return dtb;
}
catch (Exception e)
{
if (Con.State != ConnectionState.Closed && sqlTran == null)
{
CloseCn();
}
Debug.Write(e.Message);
return dtb;
}
finally
{
if (Con.State != ConnectionState.Closed && sqlTran == null)
{
CloseCn();
}
}
}
#region " 2006-4-14 小草 获得datatable "
/// <summary>
/// 1. 根据存储过程和参数值得到DataTable 值
/// 2. 根据SQL的得到DataTable 值
/// </summary>
/// <param name="A_strSql">可是是SQL 也可以是存储过程</param>
/// <param name="A_aryParm">SqlParameter参数列表</param>
/// <returns>返回DataTable</returns>
protected DataTable GetTableValue ( CommandType objCmdType, string A_strSql, SqlParameter[] A_arrparmCmdParms )
{
DataTable dtb=new DataTable();
dtrValue = ExecuteReader(objCmdType,A_strSql, A_arrparmCmdParms);
int intColLength=dtrValue.FieldCount;
for (int i=0 ; i <intColLength ;i++)
{
dtb.Columns.Add(dtrValue.GetName(i),GetColType(i));
}
DataRow dr;
if(dtrValue.HasRows)
{
while(dtrValue.Read())
{
dr=dtb.NewRow();
for (int j=0; j< intColLength ;j++)
{
dr[dtrValue.GetName(j)]=GetValue(j,GetFieldType(j).ToString());
}
dtb.Rows.Add(dr);
}
}
CloseCn();
return dtb;
}
/// <summary>
/// SQLRead根据类型得到该列数据的读取得方式
/// </summary>
/// <param name="i">select 出来的table的列的索引</param>
/// <param name="A_strType">table的索引列的数据类型</param>
/// <returns>该索引类的值</returns>
protected Object GetValue (int A_intIndex,string A_strType)
{
switch (A_strType)
{
case "System.Guid":
return GetGuid1(A_intIndex);
case"System.String":
return GetString(A_intIndex);
default:
return GetString(A_intIndex);
}
}
/// <summary>
/// SQLRead根据类型得到该列数据的数据类型
/// </summary>
/// <param name="A_intIndex">select 出来的table的列的索引</param>
/// <returns>该列数据的数据类型</returns>
protected System.Type GetColType (int A_intIndex)
{
switch (dtrValue.GetFieldType(A_intIndex).ToString())
{
case "System.Guid":
return typeof(string) ;
case"System.String":
return typeof(string);
default:
return typeof(string);
}
}
#endregion
//=================================================================
# region "读取数据"
//' 读取数据'
/// <summary>
/// String
/// </summary>
/// <param name="intIndex"></param>
/// <returns></returns>
public string GetString(int intIndex)
{
if (dtrValue != null && dtrValue.IsClosed == false)
{
if (dtrValue.IsDBNull(intIndex) == true) return null;
else return dtrValue.GetString(intIndex).Trim();
}
else throw new InvalidOperationException("SQLDAL.SQLHelp中dtrValue为null");
}
/// <summary>
/// Guid
/// </summary>
/// <param name="intIndex"></param>
/// <returns></returns>
public Guid GetGuid1(int intIndex)
{
if (dtrValue != null && dtrValue.IsClosed == false)
{
if (dtrValue.IsDBNull(intIndex) == true) return Guid.Empty ;
else return dtrValue.GetGuid(intIndex);
}
else throw new InvalidOperationException("dtrValue对象为实例");
}
#endregion
}
}
浙公网安备 33010602011771号