一个程序详细研究DataReader

//***********************************************************

//*名称一个程序详细研究DataReader同时显示Command用法

//*功能演示DataReader的各项操作

//*说明如果需要文字说明请查看我的BLOG里面关于的文章

//*作者雪冬寒

//*BOLG

//***********************************************************

using System;

using SystemData;

using SystemDataSqlClient;

using SystemDataOleDb;

class SqlReader

{

const string connStr = data source=bineon;user id=sa;password=;initial catalog=contract;

SqlConnection conn;

public SqlReader()

{

conn = new SqlConnection(connStr);

}

//**************************************

//*演示DataReader的两种取值方法

//**************************************

public void basicReader()

{

string sql=select * from friend;

SqlCommand cmd;

cmd = connCreateCommand();

cmdCommandText = sql;

connOpen();

SqlDataReader reader = cmdExecuteReader();

while(readerRead())

{

ConsoleWriteLine(No:{}\tName:{}\tPhoneNum:{}\tAddress:{}readerGetInt()ToString()readerGetString()reader[]ToString()reader[Faddress]ToString());

}

showSplit();

readerClose();

connClose();

}

//**************************************

//*演示带参数查询的操作使用SqlCilent

//**************************************

public void hasParamReader()

{

SqlCommand cmd;

cmd = connCreateCommand();

string sql = select FnameFphoneFaddress from friend where Fid > @Fid;

cmdCommandText = sql;

SqlParameter param = new SqlParameter(@FidSqlDbTypeInt);

paramValue = ;

cmdParametersAdd(param);

connOpen();

//当关闭reader的时候同时关闭数据库连接

SqlDataReader reader = cmdExecuteReader(CommandBehaviorCloseConnection);

while(readerRead())

{

ConsoleWriteLine(Name:{}\tPhoneNum:{}\tAddress:{}readerGetString()readerGetString()readerGetString());

}

showSplit();

//无需关闭conn系统会自动调用这个方法来关闭conn的

readerClose();

}

//**************************************

//*演示带参数查询的操作使用OleDb

//**************************************

public void hasOledbParamReader()

{

SqlCommand cmd;

cmd = connCreateCommand();

string sql = select FnameFphoneFaddress from friend where Fid > ?;

string oledbConnStr = Provider=sqloledb; + connStr;

OleDbConnection oleConn = new OleDbConnection(oledbConnStr);

OleDbCommand oleCmd = new OleDbCommand(sqloleConn);

oleCmdParametersAdd(nothing);

oleConnOpen();

OleDbDataReader oleReader = oleCmdExecuteReader();

while(oleReaderRead())

{

ConsoleWriteLine(Name:{}\tPhoneNum:{}\tAddress:{}oleReaderGetString()oleReaderGetString()oleReaderGetString());

}

showSplit();

oleReaderClose();

oleConnClose();

}

//**************************************

//*演示存储过程的输出参数

//**************************************

public void outParamShow()

{

SqlCommand cmd;

cmd = connCreateCommand();

cmdCommandText = GetInfo;

cmdCommandType = CommandTypeStoredProcedure;

SqlParameter param = cmdParametersAdd(@Fid);

param = cmdParametersAdd(@FnameSqlDbTypeVarChar);

paramDirection = ParameterDirectionOutput;

param = cmdParametersAdd(@FphoneSqlDbTypeVarChar);

paramDirection = ParameterDirectionOutput;

connOpen();

cmdExecuteNonQuery();

string Fname = cmdParameters[@Fname]ValueToString();

string Fphone = cmdParameters[@Fphone]ValueToString();

ConsoleWriteLine(Fname + + Fphone);

connClose();

showSplit();

}

//**************************************

//*演示读取多个无关记录集

//**************************************

public void multiResult()

{

SqlCommand cmd;

cmd = connCreateCommand();

string sqla = select Fname from friend;

string sqlb = select Fphone from friend;

cmdCommandText = sqla + ; + sqlb;

connOpen();

SqlDataReader reader= cmdExecuteReader();

int i = ;

do

{

ConsoleWriteLine(第 + iToString() + 个记录集内容如下\n);

while(readerRead())

{

ConsoleWriteLine(reader[]ToString() + \t);

}

i++;

}while(readerNextResult());//NextResult()移动到下一个记录集

readerClose();

connClose();

showSplit();

}

//**************************************

//*使用DataReader获得数据库模式信息

//**************************************

public void getSchema()

{

SqlCommand cmd;

cmd = connCreateCommand();

string sql = select FidFnameFphone from friend;

cmdCommandText = sql;

connOpen();

SqlDataReader reader = cmdExecuteReader();

DataTable SchemaTable = readerGetSchemaTable();

DataRowCollection SchemaColumns = SchemaTableRows;

DataColumnCollection SchemaProps = SchemaTableColumns;

foreach(DataRow SchemaColumn in SchemaColumns)

{

foreach(DataColumn SchemaColumnProp in SchemaProps)

{

ConsoleWriteLine(SchemaColumnPropColumnName + = + SchemaColumn[SchemaColumnPropColumnName]ToString());

}

}

readerClose();

connClose();

showSplit();

}

//**************************************

//*从数据库读取二进制数据的代码段

//*该代码段只是读取二进制的片断不是

//*整个程序所以不能执行你可以把它

//*集成到你的WinForm项目里面

//**************************************

public void getBinary()

{

/*

SystemIOMemoryStream stream = new SystemIOMemoryStream();

SystemIOBinaryWriter writer = new SystemIOBinaryWriter(stream);

int BufferSize = ;

byte[] Buffer = new Byte[BufferSize];

long Offset = ;

long BytesRead = ;

do

{

BytesRead = readerGetBytes(OffsetBufferBufferSize);

writerWriter(Buffer(int)BytesRead);

writerFlush();

Offset += BytesRead;

}

while(BytesRead == BufferSize);

*/

}

//添加输出分隔

private void showSplit()

{

ConsoleWriteLine(\n********************************************************************\n);

}

public static void Main(string [] args)

{

SqlReader sqlreader = new SqlReader();

sqlreaderbasicReader();

sqlreaderhasParamReader();

sqlreaderhasOledbParamReader();

sqlreaderoutParamShow();

sqlreadermultiResult();

sqlreadergetSchema();

}

}

上一篇：VB Shell调用后 等待程序运行结束

下一篇：在gridview中使用DataFromatString