2014-5-26每日一敲--Linq

1.var推断类型

    //var推断类型
    var varInt = 123;
    var varStr = "123";
    var varArr = new string[] { "1", "2", "3" };
    var varList = new List<int> { 1, 2, 3 };

    //var varNull = null;不可这样乱弄！错误！
    //只能用于局部变量，用于字段是不可以的

2.匿名类型

    //匿名类型
    var Person = new { PersenName = "xiao A", age = 12 };
    MessageBox.Show(Person.PersenName);
            

 3.扩展方法

    //扩展方法，只能在静态类中定义并且是静态方法
    public static class Helper
    {
        /// <summary>
        /// 扩展方法
        /// </summary>
        public static string ToStringForInt(this int N)
        {
            return N.ToString("#00.00");
        }
    }
    
    //调用
    int inta = 2234;
    MessageBox.Show(inta.ToStringForInt());//2234.00

4.自动属性

    //自动属性
    public class Person
    {
        //编译器自动为你生成get、set操作以及字段，
        //并且你不能使用字段也不能自定义get、set操作
        public string PersonName { get; set; }
        //不过你可以分别定义get和set的访问级别
        public int PersonAge { get; protected set; }

        public Person() { }

        public Person(string Name)
        {
            this.PersonName = Name;
        }
    }

5.对象初始化器\集合初始化器

    //对象初始化器
    Person P1 = new Person() { PersonName = "xiao A", PersonAge = 15 };
    //集合初始化器
    List<Person> Ps = new List<Person>()
    {
        new Person{PersonName="xiao A",PersonAge=16},
        new Person{PersonName="xiao B",PersonAge=15},
        new Person{PersonName="xiao C",PersonAge=14}                
    };
    
    public class Person
    {
        public string PersonName { get; set; }

        public int PersonAge { get; set; }
    }

6.Lambda

            //Lambda
            var list = new[] { "aa", "bb", "ac" };
            var result = Array.FindAll(list, s => (s.IndexOf("a") > -1));
            //还有以下的方式
            //var result = Array.FindAll(list, (s) => { return s.IndexOf("a") > -1; });
            //var result = Array.FindAll(list,(string s) =>{return s.IndexOf("a")>-1;});
            //var result = Array.FindAll(list, delegate(string s) { return s.IndexOf("a") > -1; });
            //var result = Array.FindAll(list, FindIndex);
            foreach (var v in result)
            {
                MessageBox.Show(v);
            }
            
            
        public bool FindIndex(string stringS)
        {
            return stringS.IndexOf("a") > -1;
        }

7.Linq简单查询

            //Linq简单查询
        private void button1_Click(object sender, EventArgs e)
        {
            //集合初始化器
            List<Person> Ps = new List<Person>()
            {
                new Person{PersonName="xiao A",PersonAge=16},
                new Person{PersonName="xiao B",PersonAge=15},
                new Person{PersonName="xiao C",PersonAge=14}                
            };
            //LinQ 语句查询  from <别名> in <集合> select <别名>
            var PersonOfAll = from P in Ps select P;//别名
            foreach (Person item in PersonOfAll)
            {
                MessageBox.Show(item.PersonName + "  : " + item.PersonAge);
            }
            
            //LinQ where条件查询
            //语法 from <别名> in <集合> where <<别名>条件等式> select <别名>
            var PersonWithAge = from p in Ps where p.PersonAge == 15 select p;
            foreach (Person item in PersonWithAge)
            {
                MessageBox.Show(item.PersonName + "  : " + item.PersonAge);
            }
            
            //方法简写
            var PersonWithAge = Ps.Where(p => p.PersonAge == 15);
            foreach (Person item in PersonWithAge)
            {
                MessageBox.Show(item.PersonName + "  : " + item.PersonAge);
            }
            
            
            
            
            //LinQ where条件查询(指定列名)
            //语法 from <别名> in <集合> where <<别名>条件等式> select <别名>{列名}
            var PersonWithAge = from p in Ps where p.PersonAge == 15 select p.PersonName.ToUpper();
            foreach (var item in PersonWithAge)
            {
                MessageBox.Show(item);//XIAO B
            }
            
            //简写
            var PersonWithAge = Ps.Where(p => p.PersonAge == 15).Select(p => p.PersonName.ToUpper());
            foreach (var item in PersonWithAge)
            {
                MessageBox.Show(item);//XIAO B
            }
        }
        
    public class Person
    {
        public string PersonName { get; set; }

        public int PersonAge { get; set; }
    }            

 8.Linq 取数据

//SQL 数据
1    xiao A    20    A
2    xiao B    21    B
3    xiao C    22    C

    //对应SQL数据库Students表的Students类
    //using System.Data.Linq.Mapping; 添加引用
    [Table(Name = "Students")]
    public class Students
    {
        //主键
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }

        //Name 可以设置列名
        [Column(Name = "Name")]
        public string Name { get; set; }

        [Column(Name = "Age")]
        public int Age { get; set; }

        //如果没有指定Column特性的Name属性，
        //那么系统会把属性名作为数据表的字段名，
        //也就是说实体类的属性名就需要和数据表中的字段名一致。
        [Column]
        public string Address { get; set; }
    }

    //取得数据，展示
        private void button1_Click(object sender, EventArgs e)
        {
            //DataContext类型（数据上下文）功能 执行SQL(更新查询)、新建删除数据库
            //命名空间 using System.Data.Linq;
            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            DataContext ctx = new DataContext(conn);
            Table<Students> Students = ctx.GetTable<Students>();
            var students = from c in Students select c;
            //var students = from c in Students where c.Name.StartsWith("X") select new { 编号 = c.Id, 姓名 = c.Name, 年龄 = c.Age, 住址 = c.Address };
            //从student表中，取出姓名X开头的记录，并把各个字段封装成匿名类返回。
            foreach (var item in students)
            {
                MessageBox.Show(item.Id + " " + item.Name + " " + item.Age + " " + item.Address);
            }
        }

9.强类型DataContext

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            StudentsDataContext ctx = new StudentsDataContext(conn);
            //Table<Students> Students = ctx.GetTable<Students>();
            var students = from c in ctx.Students select c;
            foreach (var item in students)
            {
                MessageBox.Show(item.Id + " " + item.Name + " " + item.Age + " " + item.Address);
            }
        }
    }
    //using System.Data.Linq.Mapping; 添加引用
    [Table(Name = "Students")]
    public class Students
    {
        //主键
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }

        //Name 可以设置列名
        [Column(Name = "Name")]
        public string Name { get; set; }

        [Column(Name = "Age")]
        public int Age { get; set; }

        //如果没有指定Column特性的Name属性，
        //那么系统会把属性名作为数据表的字段名，
        //也就是说实体类的属性名就需要和数据表中的字段名一致。
        [Column]
        public string Address { get; set; }
    }


    /// <summary>
    /// 强类型DataContext
    /// </summary>
    /// partial是局部类型的意思
    /// 就是说有这个关键字的类、结构或接口可以写成几个部分比如 
    /// public partial class Program  
    /// {    
    ///     static void Main(string[] args)
    ///     {
    ///     
    ///     }  
    /// }  
    /// partial class Program  
    /// {
    ///     public void Test()
    ///     {
    ///     
    ///     }
    /// }
    /// 编译后它相当于
    /// public class Program
    /// {
    ///     static void Main(string[] args)
    ///     {
    ///     
    ///     }
    ///     public void Test()
    ///     {
    ///     
    ///     }
    /// }
    public partial class StudentsDataContext : DataContext
    {
        public Table<Students> Students;

        public StudentsDataContext(string connection) : base(connection) { }

        public StudentsDataContext(IDbConnection connection) : base(connection) { }
    }

10.强类型DataContext写日志 

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            StudentsDataContext ctx = new StudentsDataContext(conn);

            /*
            //取得路径的方法
            MessageBox.Show("AppDomain.CurrentDomain.BaseDirectory:" + AppDomain.CurrentDomain.BaseDirectory);
            MessageBox.Show("Application.StartupPath:" + Application.StartupPath);
            //using System.Diagnostics;
            MessageBox.Show("Process.GetCurrentProcess().MainModule.FileName:" + Process.GetCurrentProcess().MainModule.FileName);
            MessageBox.Show("Environment.CurrentDirectory:" + Environment.CurrentDirectory);
            MessageBox.Show("Directory.GetCurrentDirectory():" + Directory.GetCurrentDirectory());
             */

            //using System.IO;
            StreamWriter writer = new StreamWriter(@"~\..\..\..\Log\log.txt", true);//追加方式添加到文本文件中            
            ctx.Log = writer;
            var students = from c in ctx.Students select c;            
            foreach (var item in students)
            {
                MessageBox.Show(item.Id + " " + item.Name + " " + item.Age + " " + item.Address);
            }
            writer.Close();

        }
    }
    //using System.Data.Linq.Mapping; 添加引用
    [Table(Name = "Students")]
    public class Students
    {
        //主键
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }

        //Name 可以设置列名
        [Column(Name = "Name")]
        public string Name { get; set; }

        [Column(Name = "Age")]
        public int Age { get; set; }

        //如果没有指定Column特性的Name属性，
        //那么系统会把属性名作为数据表的字段名，
        //也就是说实体类的属性名就需要和数据表中的字段名一致。
        [Column]
        public string Address { get; set; }
    }


    /// <summary>
    /// 强类型DataContext
    /// </summary>
    /// partial是局部类型的意思
    /// 就是说有这个关键字的类、结构或接口可以写成几个部分比如 
    /// public partial class Program  
    /// {    
    ///     static void Main(string[] args)
    ///     {
    ///     
    ///     }  
    /// }  
    /// partial class Program  
    /// {
    ///     public void Test()
    ///     {
    ///     
    ///     }
    /// }
    /// 编译后它相当于
    /// public class Program
    /// {
    ///     static void Main(string[] args)
    ///     {
    ///     
    ///     }
    ///     public void Test()
    ///     {
    ///     
    ///     }
    /// }
    public partial class StudentsDataContext : DataContext
    {
        public Table<Students> Students;

        public StudentsDataContext(string connection) : base(connection) { }

        public StudentsDataContext(IDbConnection connection) : base(connection) { }
    }

11.Linq查询

        private void button1_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            StudentsDataContext ctx = new StudentsDataContext(conn);
            var select = from c in ctx.Students where c.Name.StartsWith("X") select c;
            //using System.Data.Common;
            DbCommand cmd = ctx.GetCommand(select);//得到Linq to SQL
            /*
                SELECT [t0].[Id], [t0].[Name], [t0].[Age], [t0].[Address]
                FROM [Students] AS [t0]
                WHERE [t0].[Name] LIKE @p0
             */
            textBox1.Text = cmd.CommandText + "\r\n";
            /*
                参数名：@p0   值：X%
             */
            foreach (DbParameter item in cmd.Parameters)
            {
                textBox1.Text += "参数名：" + item.ParameterName + "   值：" + item.Value;
            }
            Students Student = ctx.Students.First();
            //1 xiao A 20 A
            textBox1.Text = Student.Id + " " + Student.Name + " " + Student.Age + " " + Student.Address;
            //修改
            Student.Name = "sha A";
            Student.Age = 52;
            //1 sha A 52 A
            textBox1.Text = Student.Id + " " + Student.Name + " " + Student.Age + " " + Student.Address;
            textBox1.Text = string.Empty;
            //输出
            /*
             1 sha A 52 A
             2 xiao B 21 B
             3 xiao C 22 C
             */
            foreach (var item in ctx.Students)
            {
                textBox1.Text += item.Id + " " + item.Name + " " + item.Age + " " + item.Address + "\r\n";
            }
            IList<object> text = ctx.GetChangeSet().Updates;
            //sha A
            textBox1.Text = ((Students)text[0]).Name;
          
        }

12.Linq 执行 SQL语句

        private void button1_Click(object sender, EventArgs e)
        {
            //Linq 执行 SQL语句
            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            StudentsDataContext ctx = new StudentsDataContext(conn);
            //直接执行SQL语句,返回影响的行数
            int intSuccess = ctx.ExecuteCommand("UPDATE Students SET Address={0}", "Beijing");
            //影响3行
            textBox1.Text = intSuccess.ToString() + "\r\n";
            //using System.Collections;
            IEnumerable<Students> select = ctx.ExecuteQuery<Students>("select * from students where address like 'B%'");
            foreach (var item in select)
            {
                textBox1.Text += item.Id + " " + item.Name + " " + item.Age + " " + item.Address + "\r\n";
            }
            /*
                1 xiao A 20 Beijing
                2 xiao B 21 Beijing
                3 xiao C 22 Beijing
             */
        }

13.利用Linq生成创建\删除数据库

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=TestDateBase;Integrated Security=True");
            PeopleDataContext ctx = new PeopleDataContext(conn);
            //数据库服务器中必须没有TestDateBase这个数据库，要不然会报错，已存在
            ctx.CreateDatabase();
            //数据库必须存在，要不然回报错
            //ctx.DeleteDatabase();
        }
    }
    //using System.Data.Linq.Mapping; 添加引用
    [Table(Name = "People")]
    public class People
    {
        [Column(IsPrimaryKey = true)]
        public int Id { get; set; }

        [Column(Name = "PeopleName")]
        public string PeopleName { get; set; }

        [Column(Name = "PeopleAge")]
        public int PeopleAge { get; set; }

        [Column]
        public string Address { get; set; }
    }


    /// <summary>
    /// 强类型DataContext
    /// </summary>    
    public partial class PeopleDataContext : DataContext
    {
        public Table<People> People;
    
    //多表
    //public Table<Students> Students;

        public PeopleDataContext(string connection) : base(connection) { }

        public PeopleDataContext(IDbConnection connection) : base(connection) { }
    }

 14.Linq 将DbDataReader 转化成对象

            SqlConnection conn = new SqlConnection(@"Data Source=.\MSSQLR2;Initial Catalog=MyDateBase;Integrated Security=True");
            DataContext ctx = new DataContext(conn);
            var cmd = new SqlCommand("select * from students", conn);
            conn.Open();
            var reader = cmd.ExecuteReader();
            //DbDataReader 转化成对象
            IEnumerable<Students> Students = ctx.Translate<Students>(reader);
            foreach (Students item in Students)
            {
                textBox1.Text += item.Id + " " + item.Name + " " + item.Age + " " + item.Address + "\r\n";
            }

15.Linq to SQL 增删改

 表设计如图

 

 

 

参考:http://www.cnblogs.com/aehyok/archive/2013/04/24/3039930.html