asp.net 读取mysql数据库_asp.net访问MySQL数据库

数据库链接字符串:

数据访问类：

using MySql.Data.MySqlClient;

using System.Data;

using System.Data.SqlClient;

namespace DAL

{

///

/// 访问数据库底层

///

public sealed class SQLHelper

{

private readonly string MySqlConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;

private static SQLHelper helper;

private SQLHelper() { }

public static SQLHelper getInstance()

{

if (helper != null)

{

helper = new SQLHelper();

}

return helper;

}

///

/// 返回影响的行数

///

/// sql 语句

/// 参数化

public int ExecuteNonq(string sql, SqlParameter[] parameter)

{

using (MySqlConnection conn = new MySqlConnection(MySqlConnStr))

{

conn.Open();

using (MySqlCommand cmd = conn.CreateCommand())

{

try

{

cmd.CommandText = sql;

if (parameter != null)

{

cmd.Parameters.AddRange(parameter);

}

return cmd.ExecuteNonQuery();

}

finally

{

cmd.Dispose();

conn.Close();

}

}

}

}

///

/// 返回首行首列

///

/// sql 语句

/// 参数化

public object ExecuteScalar(string sql, SqlParameter[] parameter)

{

using (MySqlConnection conn = new MySqlConnection(MySqlConnStr))

{

conn.Open();

using (MySqlCommand cmd = conn.CreateCommand())

{

try

{

cmd.CommandText = sql;

if (parameter != null)

{

cmd.Parameters.AddRange(parameter);

}

return cmd.ExecuteScalar();

}

finally

{

cmd.Dispose();

conn.Close();

}

}

}

}

///

/// 返回一个DataTable

///

/// sql 语句

/// 参数化

public DataTable ExecuteDataTable(string sql, SqlParameter[] parameter)

{

using (MySqlConnection conn = new MySqlConnection(MySqlConnStr))

{

conn.Open();

using (MySqlCommand cmd = conn.CreateCommand())

{

MySqlDataAdapter adapter = null;

DataSet ds = null;

try

{

cmd.CommandText = sql;

if (parameter != null)

{

cmd.Parameters.AddRange(parameter);

}

adapter = new MySqlDataAdapter(cmd);

ds = new DataSet();

adapter.Fill(ds);

return ds.Tables[0];

}

finally

{

adapter.Dispose();

ds.Dispose();

cmd.Dispose();

conn.Close();

}

}

}

}

///

/// 返回一个结果集

///

/// sql语句

/// 参数化

public DataSet ExecuteDataSet(string sql, SqlParameter[] parameter)

{

using (MySqlConnection conn = new MySqlConnection(MySqlConnStr))

{

conn.Open();

using (MySqlCommand cmd = conn.CreateCommand())

{

MySqlDataAdapter adapter = null;

DataSet ds = null;

try

{

cmd.CommandText = sql;

if (parameter != null)

{

cmd.Parameters.AddRange(parameter);

}

adapter = new MySqlDataAdapter(cmd);

ds = new DataSet();

adapter.Fill(ds);

return ds;

}

finally

{

adapter.Dispose();

ds.Dispose();

cmd.Dispose();

conn.Close();

}

}

}

}

}

}