简单的sql server->bs或cs数据交互模式

主要记录工作当中遇到的一些问题和总结的一些经验

客户端请求-->web服务接口-->sql 语句执行(存储在数据库中)-->web服务(客户端通过调用web服务接口)-->返回DataTable或Dataset(sql server)--> 统一的DataTable或Dataset转换成对象-->提交给客户端(xml、json等等其他的)

1、首先通过sql语句返回结果，返回的结果一般都以Dataset的形式和DataTable的形式返回。

2、统一的DataTable或Dataset转换成对象

#region 写对象信息

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public T WriteTObjectInfo<T>(string tableName, DataRow dr, string[] exceptArray)
        {
            try
            {
                if (this.Status == 0)
                {
                    throw new Exception(this.Msg);
                }

                T item = Activator.CreateInstance<T>();

                List<Parameter> listParameter = GetProperties<T>(item, exceptArray);

                foreach (Parameter p in listParameter)
                {
                    foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
                    {
                        if (dc.ColumnName == p.Name)
                        {
                            Type type = item.GetType();

                            MethodInfo method = type.GetMethod("SetAttributeValue");

                            method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
                        }
                    }
                }

                return item;
            }
            catch (Exception ex)
            {
                throw new Exception("写" + Activator.CreateInstance<T>().ToString() + "信息发生错误，错误原因：" + ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public T WriteTObjectInfo<T>(string tableName)
        {
            try
            {
                if (this.Status == 0)
                {
                    throw new Exception(this.Msg);
                }

                T item = Activator.CreateInstance<T>();

                if (this.dsResult.Tables.Contains(tableName))
                {
                    DataRow dr = this.dsResult.Tables[tableName].Rows[0];

                    List<Parameter> listParameter = GetProperties<T>(item);

                    foreach (Parameter p in listParameter)
                    {
                        foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
                        {
                            if (dc.ColumnName == p.Name)
                            {
                                Type type = item.GetType();

                                MethodInfo method = type.GetMethod("SetAttributeValue");

                                method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
                            }
                        }
                    }
                }

                return item;
            }
            catch (Exception ex)
            {
                throw new Exception("写" + Activator.CreateInstance<T>() + "信息发生错误，错误原因：" + ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public T WriteTObjectInfo<T>(string tableName, string[] exceptArray)
        {
            try
            {
                if (this.Status == 0)
                {
                    throw new Exception(this.Msg);
                }

                T item = Activator.CreateInstance<T>();

                if (this.dsResult.Tables.Contains(tableName))
                {
                    DataRow dr = this.dsResult.Tables[tableName].Rows[0];

                    List<Parameter> listParameter = GetProperties<T>(item, exceptArray);

                    foreach (Parameter p in listParameter)
                    {
                        foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
                        {
                            if (dc.ColumnName == p.Name)
                            {
                                Type type = item.GetType();

                                MethodInfo method = type.GetMethod("SetAttributeValue");

                                method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
                            }
                        }
                    }
                }

                return item;
            }
            catch (Exception ex)
            {
                throw new Exception("写" + Activator.CreateInstance<T>() + "信息发生错误，错误原因：" + ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<T> WriteTObjectInfoList<T>(string tableName, string[] exceptArray)
        {
            try
            {
                if (this.Status == 0)
                {
                    throw new Exception(this.Msg);
                }

                List<T> list = new List<T>();

                if (this.dsResult.Tables.Contains(tableName))
                {
                    foreach (DataRow dr in this.dsResult.Tables[tableName].Rows)
                    {
                        T item = WriteTObjectInfo<T>(tableName, dr, exceptArray);

                        list.Add(item);
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("写" + Activator.CreateInstance<T>().ToString() + "信息发生错误，错误原因：" + ex.Message);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<T> WriteTObjectInfoList<T>(string tableName)
        {
            return WriteTObjectInfoList<T>(tableName, new string[] { });
        }

        #endregion

　 以上代码统一用泛型实现

比较实用的获取属性的代码

/// <summary>
        /// 获取对象的属性名称、值和描述
        /// </summary>
        /// <typeparam name="T">对象的类型</typeparam>
        /// <param name="t">对象</param>
        /// <returns>对象列表</returns>
        public List<Parameter> GetProperties<T>(T t)
        {
            List<Parameter> list = new List<Parameter>();

            if (t == null)
            {
                return list;
            }
            PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return list;
            }
            foreach (PropertyInfo item in properties)
            {
                string name = item.Name; //名称
                object value = item.GetValue(t, null);  //值

                string des = string.Empty;

                try
                {
                    des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 属性值
                }
                catch { }

                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    Parameter parameter = new Parameter();

                    parameter.Name = name;
                    parameter.Value = value == null ? "" : value.ToString();
                    parameter.Object = des;

                    list.Add(parameter);
                }
                else
                {
                    GetProperties(value);
                }
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="exceptArray"></param>
        /// <returns></returns>
        public List<Parameter> GetProperties<T>(T t, string[] exceptArray)
        {
            List<Parameter> list = new List<Parameter>();

            if (t == null)
            {
                return list;
            }
            PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return list;
            }
            foreach (PropertyInfo item in properties)
            {
                string name = item.Name; //名称
                object value = item.GetValue(t, null);  //值
                string des = string.Empty;

                try
                {
                    des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 属性值
                }
                catch (Exception ex)
                {
                    des = string.Empty;
                }

                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (!((IList)exceptArray).Contains(name))
                    {
                        Parameter parameter = new Parameter();

                        parameter.Name = name;
                        parameter.Value = value == null ? "" : value.ToString();
                        parameter.Object = des;

                        list.Add(parameter);
                    }
                }
                else
                {
                    GetProperties(value);
                }
            }
            return list;
        }

    基础的Parameter类

public class Parameter
    {
        /// <summary>
        /// 名称
        /// </summary>
        private string _name = string.Empty;

        /// <summary>
        /// 获取或设置名称
        /// </summary>
        public string Name
        {
            get { return this._name; }
            set { this._name = value; }
        }

        /// <summary>
        /// 值
        /// </summary>
        private string _value = string.Empty;

        /// <summary>
        /// 获取或设置值
        /// </summary>
        public string Value
        {
            get { return this._value; }
            set { this._value = value; }
        }

        private object _object = null;


        public object Object
        {
            get { return this._object; }
            set { this._object = value; }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="value">值</param>
        public Parameter(string name, string value)
        {
            this.Name = name;
            this.Value = value;
        }

        public Parameter(string name, object obj)
        {
            this.Name = name;
            this.Object = obj;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public Parameter()
        {

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return string.Format(@"名称(Name)：{0}，值(Value)：{1}，对象(Object)：{2}", this.Name, this.Value, this.Object);
        }
    }

    对象例子(这个对象例子的类，这个类其实和上面的DataTable和Dataset是对应的，通过以上的操作可以把DataTable或Dataset转换成具体的对象)，因为这个类是比较统一的可以用代码生成工具可以直接生成

public class Log
    {
        #region 属性

        [Description("数据日志编号")]
        public string LogID { get; set; }

        [Description("设备编号")]
        public string DeviceID { get; set; }

        [Description("设备名称")]
        public string DeviceName { get; set; }

        [Description("质控项目编号")]
        public string QCItemDicID { get; set; }

        [Description("质控项目中文名称")]
        public string CNName { get; set; }

        [Description("质控项目英文名称")]
        public string ENName { get; set; }

        [Description("质控项目名称简拼码")]
        public string JPM { get; set; }

        [Description("质控项目名称简拼码")]
        public string NameAB { get; set; }

        [Description("质控项目单位")]
        public string Unit { get; set; }

        [Description("设备质控编号")]
        public string Dev_QC_No { get; set; }

        [Description("设备质控序号")]
        public string Dev_QC_SequenceNo { get; set; }

        [Description("设备质控名称")]
        public string Dev_QC_Name { get; set; }

        [Description("质控时间")]
        public string QCTime { get; set; }

        [Description("值类型")]
        public string TextType { get; set; }

        [Description("数值")]
        public string ItemValue { get; set; }

        [Description("创建时间")]
        public string CreateTime { get; set; }

        [Description("创建人")]
        public string CreateUser { get; set; }

        [Description("序号(通道号)")]
        public string Serial { get; set; }

        #endregion

        /// <summary>
        /// 设置属性值
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="value">值</param>
        public void SetAttributeValue(string name, string value)
        {
            switch (name)
            {
                case "LogID"://数据日志编号
                    this.LogID = value;
                    break;
                case "DeviceID"://设备编号
                    this.DeviceID = value;
                    break;
                case "DeviceName"://设备名称
                    this.DeviceName = value;
                    break;
                case "QCItemDicID"://质控项目编号
                    this.QCItemDicID = value;
                    break;
                case "CNName"://质控项目中文名称
                    this.CNName = value;
                    break;
                case "ENName"://质控项目英文名称
                    this.ENName = value;
                    break;
                case "JPM"://质控项目名称简拼码
                    this.JPM = value;
                    break;
                case "NameAB"://质控项目名称简拼码
                    this.NameAB = value;
                    break;
                case "Unit"://质控项目单位
                    this.Unit = value;
                    break;
                case "Dev_QC_No"://设备质控编号
                    this.Dev_QC_No = value;
                    break;
                case "Dev_QC_SequenceNo"://设备质控序号
                    this.Dev_QC_SequenceNo = value;
                    break;
                case "Dev_QC_Name"://设备质控名称
                    this.Dev_QC_Name = value;
                    break;
                case "QCTime"://质控时间
                    this.QCTime = value;
                    break;
                case "TextType"://值类型
                    this.TextType = value;
                    break;
                case "ItemValue"://数值
                    this.ItemValue = value;
                    break;
                case "CreateTime"://创建时间
                    this.CreateTime = value;
                    break;
                case "CreateUser"://创建人
                    this.CreateUser = value;
                    break;
                case "Serial"://序号(通道号)
                    this.Serial = value;
                    break;
                default:
                    break;
            }
        }
    }

 另外也可以把对象转换成DataTable或Dataset 根据具体使用的情况进行具体的转换

#region 获取对象和对象转换成DataTable

        /// <summary>
        /// 返回数据列
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="caption"></param>
        /// <returns></returns>
        public static DataColumn AddDataColumn(string columnName, string caption)
        {
            DataColumn dc = new DataColumn();

            dc.ColumnName = columnName;
            dc.Caption = caption;

            return dc;
        }

        /// <summary>
        /// 获取表格的数据列
        /// </summary>
        /// <param name="name"></param>
        /// <param name="caption"></param>
        /// <returns></returns>
        public static DataColumn GetColumn(string name, string caption)
        {
            DataColumn dc = new DataColumn();

            dc.ColumnName = name;
            dc.Caption = caption;

            return dc;
        }

        /// <summary>
        /// 获取对象的属性名称、值和描述
        /// </summary>
        /// <typeparam name="T">对象的类型</typeparam>
        /// <param name="t">对象</param>
        /// <returns>对象列表</returns>
        public static List<Parameter> GetProperties<T>(T t)
        {
            List<Parameter> list = new List<Parameter>();

            if (t == null)
            {
                return list;
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return list;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                string name = item.Name; //名称
                object value = item.GetValue(t, null);  //值
                string des = string.Empty;

                try
                {
                    des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 属性值
                }
                catch { }

                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    Parameter parameter = new Parameter();

                    parameter.Name = name;
                    parameter.Value = value == null ? string.Empty : value.ToString();
                    parameter.Object = des;

                    list.Add(parameter);
                }
                else
                {
                    GetProperties(value);
                }
            }
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="exceptArray"></param>
        /// <returns></returns>
        public static List<Parameter> GetProperties<T>(T t, string[] exceptArray)
        {
            List<Parameter> list = new List<Parameter>();

            if (t == null)
            {
                return list;
            }
            PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0)
            {
                return list;
            }
            foreach (PropertyInfo item in properties)
            {
                string name = item.Name; //名称
                object value = item.GetValue(t, null);  //值
                string des = string.Empty;

                try
                {
                    des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 属性值
                }
                catch (Exception ex)
                {
                    des = string.Empty;
                }

                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (!((IList)exceptArray).Contains(name))
                    {
                        Parameter parameter = new Parameter();

                        parameter.Name = name;
                        parameter.Value = value == null ? "" : value.ToString();
                        parameter.Object = des;

                        list.Add(parameter);
                    }
                }
                else
                {
                    GetProperties(value);
                }
            }
            return list;
        }

        /// <summary>
        /// 类型对象生成DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static DataTable TToDataTable<T>(T obj, List<T> listT)
        {
            DataTable dt = new DataTable();

            int flag = 0;

            if (listT != null)
            {
                foreach (T t in listT)
                {
                    List<Parameter> listProperty = GetProperties<T>(t);

                    if (flag <= 0)
                    {
                        foreach (Parameter parameter in listProperty)
                        {
                            flag++;

                            dt.Columns.Add(GetColumn(parameter.Name, parameter.Object.ToString()));
                        }
                    }

                    DataRow dr = dt.NewRow();

                    foreach (Parameter parameter in listProperty)
                    {
                        dr[parameter.Name] = parameter.Value;
                    }

                    dt.Rows.Add(dr);
                }
            }
            else
            {
                List<Parameter> listProperty = GetProperties<T>(obj);

                foreach (Parameter parameter in listProperty)
                {
                    dt.Columns.Add(GetColumn(parameter.Name, parameter.Object.ToString()));
                }

                DataRow dr = dt.NewRow();

                foreach (Parameter parameter in listProperty)
                {
                    dr[parameter.Name] = parameter.Value;
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static DataTable TToDataTable<T>(T obj)
        {
            return TToDataTable<T>(obj, null);
        }

        /// <summary>
        /// 类型对象生成DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="listT"></param>
        /// <returns></returns>
        public static DataTable TToDataTable<T>(List<T> listT)
        {
            return TToDataTable<T>(default(T), listT);
        }

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static Parameter GetParameter(string name, string value)
        {
            Parameter parameter = new Parameter();

            parameter.Name = name;
            parameter.Value = value;

            return parameter;
        }

 要是客户端为bs架构，用一下代码进行发送

/// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void SendDataObject<T>(T t)
        {
            string json = Newtonsoft.Json.JsonConvert.SerializeObject(t);

            SendDataByJson(json);
        }

具体的后端向前端发送的代码可以参考如下：

#region 公共方法
        /// <summary>
        /// 向客户端发送数据
        /// </summary>
        /// <param name="contentEncoding">字符编码</param>
        /// <param name="contentType">输出流的MIME类型</param>
        /// <param name="content">输出的内容</param>
        public void SendData(Encoding contentEncoding, string contentType, string content)
        {
            Response.Clear();
            Response.ContentEncoding = contentEncoding;
            Response.ContentType = contentType;
            Response.Write(content);
            Response.Flush();
            Response.End();
        }
        /// <summary>
        /// 向客户端发送数据
        /// </summary>
        /// <param name="content">输出的内容</param>
        public void SendData(string content)
        {
            SendData(Encoding.UTF8, "application/json", content);
        }

        public void SendDataFile(string filePath, string fileName)
        {
            System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

            byte[] b = new Byte[fs.Length];
            fs.Read(b, 0, b.Length);
            fs.Flush();
            fs.Close();

            Response.Clear();
            Response.ClearHeaders();
            Response.Clear();
            Response.ClearHeaders();
            Response.Buffer = false;
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            Response.AppendHeader("Content-Length", b.Length.ToString());
            fs.Close();
            fs.Close();
            if (b.Length > 0)
            {
                Response.OutputStream.Write(b, 0, b.Length);
            }
            Response.Flush();
            Response.End();
        }
        /// <summary>
        /// 通过json的形式发送文本
        /// </summary>
        /// <param name="content">要发送的内容</param>
        public void SendDataByJson(string content)
        {
            SendData(Encoding.UTF8, "application/json", content);
        }
        /// <summary>
        /// 向客户端发送数据
        /// </summary>
        /// <param name="content">输出的内容</param>
        public void SendData(string contentType, string content)
        {
            SendData(Encoding.UTF8, contentType, content);
        }
        /// <summary>
        /// 通过文本的形式发送文件
        /// </summary>
        /// <param name="content">要发送的内容</param>
        public void SendDataByText(string content)
        {
            SendData(Encoding.UTF8, "text/plain", content);
        }
        /// <summary>
        /// 处理错误消息
        /// </summary>
        /// <param name="message">要处理的消息</param>
        /// <returns>处理之后的消息</returns>
        public string DealErrorMsg(string message)
        {
            return message.Replace((char)13, (char)0).Replace((char)10, (char)0).Replace(""", "'").Replace("", "");
        }

        #endregion