//***********************************************************
//*名称：一个程序详细研究datareader,同时显示command用法
//*功能：演示datareader的各项操作。
//*说明：如果需要文字说明请查看我的blog里面关于ado.net的文章
//*作者：雪冬寒
//*bolg：http://blog.csdn.net/bineon
//***********************************************************
using system;
using system.data;
using system.data.sqlclient;
using system.data.oledb;
class sqlreader
{
const string connstr = "data source=bineon;user id=sa;password=87345587;initial catalog=contract";
sqlconnection conn;
public sqlreader()
{
conn = new sqlconnection(connstr);
}
//**************************************
//*演示datareader的两种取值方法
//**************************************
public void basicreader()
{
string sql="select * from friend";
sqlcommand cmd;
cmd = conn.createcommand();
cmd.commandtext = sql;
conn.open();
sqldatareader reader = cmd.executereader();
while(reader.read())
{
console.writeline("no:{0}/tname:{1}/tphonenum:{2},/taddress:{3}",reader.getint32(0).tostring(),reader.getstring(1),reader[2].tostring(),reader["faddress"].tostring());
}
showsplit();
reader.close();
conn.close();
}
//**************************************
//*演示带参数查询的操作，使用sqlcilent
//**************************************
public void hasparamreader()
{
sqlcommand cmd;
cmd = conn.createcommand();
string sql = "select fname,fphone,faddress from friend where fid > @fid";
cmd.commandtext = sql;
sqlparameter param = new sqlparameter("@fid",sqldbtype.int,4);
param.value = 15;
cmd.parameters.add(param);
conn.open();
//当关闭reader的时候同时关闭数据库连接
sqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
while(reader.read())
{
console.writeline("name:{0}/tphonenum:{1}/taddress:{2}",reader.getstring(0),reader.getstring(1),reader.getstring(2));
}
showsplit();
//无需关闭conn,系统会自动调用这个方法来关闭conn的。
reader.close();
}
//**************************************
//*演示带参数查询的操作,使用oledb
//**************************************
public void hasoledbparamreader()
{
sqlcommand cmd;
cmd = conn.createcommand();
string sql = "select fname,fphone,faddress from friend where fid > ?";
string oledbconnstr = "provider=sqloledb;" + connstr;
oledbconnection oleconn = new oledbconnection(oledbconnstr);
oledbcommand olecmd = new oledbcommand(sql,oleconn);
olecmd.parameters.add("nothing",15);
oleconn.open();
oledbdatareader olereader = olecmd.executereader();
while(olereader.read())
{
console.writeline("name:{0}/tphonenum:{1}/taddress:{2}",olereader.getstring(0),olereader.getstring(1),olereader.getstring(2));
}
showsplit();
olereader.close();
oleconn.close();
}
//**************************************
//*演示存储过程的输出参数
//**************************************
public void outparamshow()
{
sqlcommand cmd;
cmd = conn.createcommand();
cmd.commandtext = "getinfo";
cmd.commandtype = commandtype.storedprocedure;
sqlparameter param = cmd.parameters.add("@fid",16);
param = cmd.parameters.add("@fname",sqldbtype.varchar,8);
param.direction = parameterdirection.output;
param = cmd.parameters.add("@fphone",sqldbtype.varchar,8);
param.direction = parameterdirection.output;
conn.open();
cmd.executenonquery();
string fname = cmd.parameters["@fname"].value.tostring();
string fphone = cmd.parameters["@fphone"].value.tostring();
console.writeline(fname + " " + fphone);
conn.close();
showsplit();
}
//**************************************
//*演示读取多个无关记录集
//**************************************
public void multiresult()
{
sqlcommand cmd;
cmd = conn.createcommand();
string sqla = "select fname from friend";
string sqlb = "select fphone from friend";
cmd.commandtext = sqla + ";" + sqlb;
conn.open();
sqldatareader reader= cmd.executereader();
int i = 1;
do
{
console.writeline("第" + i.tostring() + "个记录集内容如下：/n");
while(reader.read())
{
console.writeline(reader[0].tostring() + "/t");
}
i++;
}while(reader.nextresult()); //nextresult()移动到下一个记录集
reader.close();
conn.close();
showsplit();
}
//**************************************
//*使用datareader获得数据库模式信息
//**************************************
public void getschema()
{
sqlcommand cmd;
cmd = conn.createcommand();
string sql = "select fid,fname,fphone from friend";
cmd.commandtext = sql;
conn.open();
sqldatareader reader = cmd.executereader();
datatable schematable = reader.getschematable();
datarowcollection schemacolumns = schematable.rows;
datacolumncollection schemaprops = schematable.columns;
foreach(datarow schemacolumn in schemacolumns)
{
foreach(datacolumn schemacolumnprop in schemaprops)
{
console.writeline(schemacolumnprop.columnname + "=" + schemacolumn[schemacolumnprop.columnname].tostring());
}
}
reader.close();
conn.close();
showsplit();
}
//**************************************
//*从数据库读取二进制数据的代码段
//*该代码段只是读取二进制的片断，不是
//*整个程序，所以不能执行，你可以把它
//*集成到你的winform项目里面。
//**************************************
public void getbinary()
{
/*
system.io.memorystream stream = new system.io.memorystream();
system.io.binarywriter writer = new system.io.binarywriter(stream);
int buffersize = 1024;
byte[] buffer = new byte[buffersize];
long offset = 0;
long bytesread = 0;
do
{
bytesread = reader.getbytes(2,offset,buffer,0,buffersize);
writer.writer(buffer,0,(int)bytesread);
writer.flush();
offset += bytesread;
}
while(bytesread == buffersize);
*/
}
//添加输出分隔
private void showsplit()
{
console.writeline("/n********************************************************************/n");
}
public static void main(string [] args)
{
sqlreader sqlreader = new sqlreader();
sqlreader.basicreader();
sqlreader.hasparamreader();
sqlreader.hasoledbparamreader();
sqlreader.outparamshow();
sqlreader.multiresult();
sqlreader.getschema();
}
}
