调用存储过程以及用户自定义函数来更新匿名关系数据

【示例】

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
using 通过linq查询关联数据库表;

namespace 更新匿名关系型数据
{
    public class DataContextBase:DataContext
    {
        //定义一个连接到数据的连接字符串
        private static readonly string connectionString = "Data Source=.;Initial Catalog=db_Customer;Integrated Security=true;";
        /// <summary>
        /// 定义一个构造函数，并指定调用父类的构造函数
        /// </summary>
        public DataContextBase() : base(connectionString)
        {
            //可以选择输出SQL语句
            this.Log = Console.Out;
        }
        private void Update(Customer obj)
        {
            this.Update(obj.CompanyName, obj.ContactName, obj.ContactTitle);
        }
        //FunctionAttribute用于指定存储过程的名称
        //ParameterAttribute用于描述和映射存储过程的参数
        [Function(Name = "proc_name")]
        public int Update(
            [Parameter(Name ="CompanyName")]
            string CompanyName,
            [Parameter(Name ="ContactName")]
            string ContactName,
            [Parameter(Name ="ContactTitle")]
            string ContactTitle)
        {
            //Update实际上是用反射((MethodInfo)(MethodInfo.GetCurrentMethod()))来访问需要调用的方法
            //如果存储过程返回了一个值，它就会通过IExecuteResult接口得到这个值
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), CompanyName,
                ContactName, ContactTitle);
            return ((int)(result.ReturnValue));
        }
    }
}

【类】

using System;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace 通过linq查询关联数据库表
{
    [Table(Name="tb_Customer")]
    public class Customer
    {
        //字段
        private string customerid;
        private string companyname;
        private string contactname;
        private string contacttitle;
        private string address;
        private string city;
        private string region;
        private string postalcode;
        private string country;
        private string phone;
        private string fax;

        //属性
        [Column(Name ="customerid",Storage ="customerid",DbType ="varchar(20)",CanBeNull =false)]
        public string CustomerID
        {
            get { return customerid; }
            set { customerid = value; }
        }
        [Column(Name ="companyname",Storage ="companyname",DbType ="nvarchar(50)",CanBeNull =false)]
        public string CompanyName
        {
            get { return companyname; }
            set { companyname = value; }
        }
        [Column(Name ="contactname",Storage ="contactname",DbType ="nvarchar(50)",CanBeNull =true)]
        public string ContactName
        {
            get { return contactname; }
            set { companyname = value; }
        }
        [Column(Name ="contacttitle",Storage ="contacttitle",DbType ="nvarchar(50)",CanBeNull =true)]
        public string ContactTitle
        {
            get { return contacttitle; }
            set { contacttitle = value; }
        }
        [Column(Name ="address",Storage ="address",DbType ="nvarchar(50)",CanBeNull =true)]
        public string Address
        {
            get { return address; }
            set { address = value; }
        }
        [Column(Name ="city",Storage ="city",DbType ="navrchar(20)",CanBeNull =true)]
        public string City
        {
            get { return city; }
            set { city = value; }
        }
        [Column(Name ="region",Storage ="region",DbType ="nvarchar(20)",CanBeNull =true)]
        public string Region
        {
            get { return region; }
            set { region = value; }
        }
        [Column(Name ="postalcode",Storage ="postalcode",DbType ="varchar(50)",CanBeNull =true)]
        public string PostalCode
        {
            get { return postalcode; }
            set { postalcode = value; }
        }
        [Column(Name ="country",Storage ="country",DbType ="nvarchar(20)",CanBeNull =true)]
        public string Country
        {
            get { return country; }
            set { country = value; }
        }
        [Column(Name ="phone",Storage ="phone",DbType ="varchar(11)",CanBeNull=true)]
        public string Phone
        {
            get { return phone; }
            set { phone = value; }
        }
        [Column(Name ="fax",Storage ="fax",DbType ="varchar(50)",CanBeNull =true)]
        public string Fax
        {
            get { return fax; }
            set { fax = value; }
        }

        public override string ToString()
        {
            StringBuilder builder = new StringBuilder();
            PropertyInfo[] props = this.GetType().GetProperties();

            Array.ForEach(props.ToArray(), prop => builder.AppendFormat("{0}:{1}", prop.Name,
                 prop.GetValue(this, null) == null ? "<empty>" : prop.GetValue(this, null).ToString() + "\n"));

            return builder.ToString();
        }
    }
}

/*映射到数据库表的类叫实体类。对LINQ而言，实体都要用TableAttribute和ColumnAttribute进行修饰。
 ColumnAttribute可以应用在任何字段或属性上（public、private、internal），不过当LINQ把修改保存
 回数据库时，实体中只有那些带有ColumnAttribute的元素才会被持久化*/

【存储过程】

create proc proc_update
(
	@CustomerID nvarchar(20),
	@CompanyName nvarchar(20),
	@ContactName nvarchar(20),
	@ContactTitle nvarchar(20)
)
as
	update tb_Customer
	set
		companyname=@CompanyName,
		contactname=@ContactName,
		contacttitle=@ContactTitle
		where customerid=@CustomerID
	return @@rowcount

【调用用户自定义函数】

调用自定义函数与调用存储过程有相同之处。调用自定义函数的关键在于，将上述的DataContextBase类中的函数映射到数据库中对应的用户函数上。在函数名上用FunctionAttribute进行标记，并使用SQL用户自定义函数的名称，还要用ParameterAttribute标记所有的参数。

【示例】

using System;
using System.Linq;

namespace 调用用户自定义函数
{
    class Program
    {
        static void Main(string[] args)
        {
            DataContextBase context = new DataContextBase();
            var result = from c in context.OrderDetailPrice(15)
                         select c;
            Array.ForEach(result.ToArray(), r =>
            {
                Console.WriteLine(r);
                Console.WriteLine(new string('*', 40));
            });
            Console.ReadLine();
        }
    }
}

【类】

using System;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;
using System.Text;

namespace 调用用户自定义函数
{
    /// <summary>
    /// 订单明细类
    /// </summary>
    [Table(Name ="tb_OrderDetail")]
    public class OrderDetail
    {
        [Column()]
        public int OrderID { get; set; }
        [Column()]
        public int ProductID { get; set; }
        [Column()]
        public decimal Unitprice { get; set; }
        [Column()]
        public int Quantity { get; set; }
        [Column()]
        public decimal Discount { get; set; }
        public override string ToString()
        {
            var builder = new StringBuilder();
            PropertyInfo[] props = this.GetType().GetProperties();
            Array.ForEach(props.ToArray(), prop =>
            {
                builder.AppendFormat("Name:{0},Value:{1}\n", prop.Name,
                    prop.GetValue(this, null) == null ? "none" : prop.GetValue(this, null));
            });
            return builder.ToString();
        }
    }
}

【 DataContextBase类】

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;

namespace 调用用户自定义函数
{
    public class DataContextBase:DataContext
    {
        //定义一个连接到数据的连接字符串
        private static readonly string connectionString = "Data Source=.;Initial Catalog=db_Customer;Integrated Security=true;";
        public DataContextBase() : base(connectionString)
        {

        }
        //获取或设置一个值，该值指示将方法映射到函数(true)还是映射到存储过程(false)
        [Function(Name ="functionName",IsComposable =true)]
        public IQueryable<OrderDetail> OrderDetailPrice([Parameter(DbType ="Decimal")] System.Nullable<decimal> unitprice)
        {
            return this.CreateMethodCallQuery<OrderDetail>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), unitprice);
        }
    }
}