//***********************************************************
//*名稱：一個程序詳細研究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,系統(tǒng)會自動調用這個方法來關閉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();
}
//**************************************
//*從數據庫讀取二進制數據的代碼段
//*該代碼段只是讀取二進制的片斷，不是
//*整個程序，所以不能執(zhí)行，你可以把它
//*集成到你的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();
}
}
