Asp.net MVC 从 MS SQL Server 获取数据

本文关键字：Server 获取 数据 SQL MS net MVC Asp | 更新日期: 2023-09-27 18:34:52

我正在尝试从我的ms sql服务器获取DataTable。我添加了连接字符串，测试了连接，一切正常。但每次我得到的结果都是空的。

项目数据库连接.class 

 public class ProjectDbConnection
    {
    protected SqlConnection SqlConnection;
            #region Open Connection
            public bool Open(string Connection = "WebProjectDB")
            {
                SqlConnection = new SqlConnection(@WebConfigurationManager.ConnectionStrings[Connection].ToString());
                try
                {
                    if (SqlConnection.State != ConnectionState.Open)
                    {
                        SqlConnection.Open();
                    }
                    return true;
                }
                catch (SqlException ex)
                {
                    return false;
                }
            }
            #endregion
            #region Close Connection
            public bool Close()
            {
                try
                {
                    SqlConnection.Close();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
            #endregion
      #region get table
            public DataTable GetTable(string sql)
            {
                var query = sql;
                var d = new DataTable();
                try
                {
                    if (SqlConnection.State == ConnectionState.Open)
                    {
                        var cmd = new SqlCommand(query, SqlConnection);
                        var reader = cmd.ExecuteReader();
                        d.Load(reader);
                        reader.Close();
                        return d;
                    }
                    return d;
                }
                catch (Exception ex)
                {
                    return d;
                }
            }
            #endregion
    }

在这里，我调用方法：

    ProjectDbConnection dbConnection = new ProjectDbConnection();
    dbConnection.Open();
    DataTable survey = dbConnection.GetTable("SELECT * FROM tbl_survey");
    DataTable criteriaClasification = dbConnection.GetTable("SELECT * FROM tbl_criteria_classification");
    DataTable criteriaElement = dbConnection.GetTable("SELECT * FROM tbl_criteria");
    dbConnection.Close();

这是Web.config

 <connectionStrings>
    <add connectionString="Data Source=test-pc;Initial Catalog=WebProject;Integrated Security=True" name="WebProjectDB"/>
  </connectionStrings>

Asp.net MVC 从 MS SQL Server 获取数据

看起来 SqlConnection 没有打开。使用 Windows 身份验证的连接字符串，但网络凭据可能无权访问数据库服务器。

请将连接更改为使用 SQL 凭据(传递 sql 用户名和密码(并尝试连接。还要更改代码，如下所述，以确保仅在连接打开时才调用 Gettable 方法。如果可能，如果连接打开失败，请记录错误。

ProjectDbConnection dbConnection = new ProjectDbConnection();
if (dbConnection.Open())
{
    DataTable survey = dbConnection.GetTable("SELECT * FROM tbl_survey");
    DataTable criteriaClasification = dbConnection.GetTable("SELECT * FROM tbl_criteria_classification");
    DataTable criteriaElement = dbConnection.GetTable("SELECT * FROM tbl_criteria");
    dbConnection.Close();
}
相关文章：