WebService执行SQL返回DataTable(转)

WebService如果直接返回DataTable会出现“无法将其序列化”的错误，如果需要可考虑返回DataSet或者给DataTable命名后再返回

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

/// <summary>
///WebService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
//若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。 
// [System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService {

    public WebService () {

        //如果使用设计的组件，请取消注释以下行 
        //InitializeComponent(); 
    }

    //数据库链接字符串
    string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    [WebMethod(Description = "返回DataSet数据集")]
    /// <summary>
    /// 返回数据集
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public DataSet returnData(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            //创建一个数据集 
            DataSet ds = new DataSet();
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            // conn.Open();
            //创建sqldataadapter对象 
            SqlDataAdapter adp = new SqlDataAdapter(strSql, conn);

            adp.Fill(ds, "table");
            //adp.Dispose();

            conn.Close();
            return ds;
        }

    }

    [WebMethod(Description = "返回DataTable数据表")]
    /// <summary>
    /// 返回数据表
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public DataTable returnDataTable(string strSql)
    {
        DataTable dt = new DataTable();
        DataSet ds = returnData(strSql);

        if (ds != null && ds.Tables["table"].Rows.Count != 0)
        {
            dt = ds.Tables["table"];
        }
        return dt;
    }
    [WebMethod(Description = "执行SQL语句，返回影响行数,Insert/Update/Delete")]
    /// <summary>
    /// 执行数据库语句
    /// </summary>
    /// <param name="strSql"></param>
    public int ExecuteSql(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(strSql, conn);
            int ret = cmd.ExecuteNonQuery();
            conn.Close();
            return ret;
        }
    }

    [WebMethod(Description = "执行SQL语句，返回预设执行的值,SELECT @@IDENTITY AS ID")]
    /// <summary>
    /// 返回一个数据
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public string ExecuteScalar(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(strSql, conn);
            string tmp = cmd.ExecuteScalar().ToString();
            conn.Close();
            return tmp;
        }
    }
    [WebMethod(Description = "判断数据是否存在，返回bool值")]
    /// <summary>
    /// 判断数据是否存在
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public bool HasData(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(strSql, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            bool flag = dr.Read();
            conn.Close();
            return flag;
        }
    }    
    
}

 

转载于:https://www.cnblogs.com/logo005/p/6550400.html