分类

软件天地

首页 >>电脑

从一个程序出发详细研究 DataReader‖

class SqlReader

{

const string connSt 87345587;initial catalog

r = "data source=bineon;user =contract";

id=sa;password=

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.Exe

cuteReader();

while(reader.Read())

{

Console.WriteLine("No:{0}tNa ddress:{3}",reader.GetInt32(0).ToStr ),reader[2].ToString(),reader["Faddr

me:{1}tPhoneNum:{2},tA ing(),reader.GetString(1 ess"].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",S

qlDbType.Int,4);

param.Value = 15;

cmd.Parameters.Add(param);

conn.Open();

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

SqlDataReader read

er = cmd.ExecuteReader(Comma

ndBehavior.CloseConnection);

while(reader.Read())

{

Console.WriteLine r.GetString(0),reader.Ge

("Name:{0}tPhoneNum:{1}tAd tString(1),reader.GetString(

dress:{2}",reade 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 oledbConnSt

r = "Provider=sqloledb;" + c

onnStr;

OleDbConnection ol

eConn = new OleDbConnection(

oledbConnStr);

OleDbCommand oleCmd = new OleD

bCommand(sql,oleConn);

oleCmd.Parameters.Add("nothing",15);

oleConn.Open();

OleDbDataReader ol

eReader = oleCmd.ExecuteRead

er();

while(oleReader.Read())

{

Console.WriteLine ader.GetString(0),oleRea

("Name:{0}tPhoneNum:{1}tAd der.GetString(1),oleReader.G

dress:{2}",oleRe etString(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.Parame

ters.Add("@Fname",SqlDbType.

VarChar,8);

param.Direction = ParameterDir

ection.Output;

param = cmd.Parame

ters.Add("@Fphone",SqlDbType

.VarChar,8);

param.Direction = ParameterDir

ection.Output;

conn.Open();

cmd.ExecuteNonQuery();

string Fname = cmd.Parameters[

"@Fname"].Value.ToString();

string Fphone = cm

d.Parameters["@Fphone"].Valu

e.ToString();

Console.WriteLine(

Fname + "　" + Fphone);

conn.Close();

showSplit();

}

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

*********

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

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

*********

public void multiResult()

{

SqlCommand cmd;

cmd = conn.CreateCommand();

string sqla = "sel

ect Fname from friend";

string sqlb = "sel

ect Fphone from friend";

cmd.CommandText = sqla + ";" + sqlb;

conn.Open();

SqlDataReader read

er= cmd.ExecuteReader();

int i = 1;

do

{

Console.WriteLine

("第" + i.ToString() + "个记

录集内容如下：n");

while(reader.Read())

{

Console.WriteLin

e(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 r

eader = cmd.ExecuteReader();

DataTable SchemaTable = rea

der.GetSchemaTable();

DataRowCollecti

on SchemaColumns = SchemaTab

le.Rows;

DataColumnColle

ction SchemaProps = SchemaTa

ble.Columns;

foreach(DataRow SchemaColum

n in SchemaColumns)

{

foreach(DataCo

lumn SchemaColumnProp in Sch

emaProps)

{

Console.WriteLine(SchemaC SchemaColumn[SchemaColumnProp.Column

olumnProp.ColumnName + "=" + Name].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)B

ytesRead);

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();

?nbsp;

sqlreader.getSchema();

}

}

上一页 下一页