c#中executereader执行查询示例分享

executereader执行查询实例

executereader方法存在的目的只有一个：尽可能快地对数据库进行查询并得到结果。executereader 返回一个datareader对象：如果在sqlcommand对象中调用，则返回sqldatareader；如果在oledbcommand对象中调用，返回的是oledbdatareader。可以调用datareader的方法和属性迭代处理结果集。它是一个快速枚举数据库查询结果的机制，是只读、只进的。对sqldatareader.read的每次调用都会从结果集中返回一行。

using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.windows;
using system.windows.controls;
using system.windows.data;
using system.windows.documents;
using system.windows.input;
using system.windows.media;
using system.windows.media.imaging;
using system.windows.navigation;
using system.windows.shapes;
using system.data.sqlclient;

namespace executereader执行查询
{
    /// <summary>
    /// window1.xaml 的交互逻辑
    /// </summary>
    public partial class window1 : window
    {
        public window1()
        {
            initializecomponent();
        }

        private void button1_click(object sender, routedeventargs e)
        {
            using (sqlconnection conn = new sqlconnection("data source=.;initial catalog=mytest;user id=sa;password=123;"))
            {
                conn.open();
                using (sqlcommand cmd = conn.createcommand())
                {
                    cmd.commandtext = "select * from t_student where age<100";
                    using (sqldatareader reader = cmd.executereader())//executereader返回的对象类型是sqldatareader
                    {
                           //read是bool类型，初始指针指向第一条数据之前，每调用一次reader，指针就下移一条，只要没有移到最后一条之后，就返回true。
                        while (reader.read())
                        {
                            string name = reader.getstring(1);//getstring(1)得到表中第一列的值，用name接收，因为查的是*，所以就和表中的列数一样。
                            int age = reader.getint32(2);
                            messagebox.show(name+","+age);
                        }
                    }
                }
            }
        }

        private void btnquery_click(object sender, routedeventargs e)
        {
            using (sqlconnection conn = new sqlconnection("data source=.;initial catalog=mytest;user id=sa;password=123;"))
            {
                conn.open();
                using (sqlcommand cmd = conn.createcommand())
                {
                    //cmd.commandtext = "select age from t_student where name='"+ txtname.text +"'";//字符串拼接查找数据库。
                    cmd.commandtext = "select age from t_student where name=@name or age>@age";//与数据库进行数据对比。
                    //@参数:不能用来替换表名，字段名，select之类的关键字等。
                    cmd.parameters.add(new sqlparameter("@name",txtname.text));
                    cmd.parameters.add(new sqlparameter("@age",convert.toint32(txtage.text)));
                    using (sqldatareader reader = cmd.executereader())
                    {
                        while (reader.read())
                        {
                            //getint32获得的是int类型
                            //getint64获得的是long类型（bigint）
                            int age = reader.getint32(0);//getint32(0)中的参数是看cmd.commandtext中的查询结果有几个。
                            messagebox.show(age.tostring());
                        }
                    }
                }
            }
        }

        private void btnhobby_click(object sender, routedeventargs e)
        {
            using (sqlconnection conn = new sqlconnection("data source=.;initial catalog=mytest;user id=sa;password=123;"))
            {
                conn.open();
                using (sqlcommand cmd = conn.createcommand())
                {
                    cmd.commandtext = "select * from t_student where name like '张%'";
                    using (sqldatareader reader = cmd.executereader())
                    {
                        while (reader.read())
                        {
                            string hobby = reader.getstring(3);
                            messagebox.show(hobby);
                        }
                    }
                }
            }
        }

        private void btnquery1_click(object sender, routedeventargs e)
        {
            using (sqlconnection conn = new sqlconnection("data source=.;initial catalog=mytest;user id=sa;password=123;"))
            {
                conn.open();
                using (sqlcommand cmd = conn.createcommand())
                {
                    cmd.commandtext = "select hobbit from t_student where age>@age or hobbit =@hobbit";
                    cmd.parameters.add(new sqlparameter("@age", txtage1.text));
                    cmd.parameters.add(new sqlparameter("@hobbit",txthobby.text));
                    using (sqldatareader reader = cmd.executereader())
                    {
                        while (reader.read())
                        {
                            string str = reader.getstring(0);
                            messagebox.show(str);
                        }
                    }
                }
            }
        }
    }
}