首先在web.config里定义了连接字符串:
<configuration>
...
<connectionStrings>
<add name="eoexmaConnectionString" connectionString="Data Source=127.0.0.1;Initial Catalog=eoexma;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
...
</configuration>
在DAO文件夹下新建SqlDAO.cs类文件:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Web.DAO
{
public class SqlDAO
{
private SqlConnection m_conn = null;
private SqlCommand m_SqlCommand = null;
SqlTransaction m_SqlTransaction = null;
public SqlDAO()
{
string connString = ConfigurationManager.ConnectionStrings["eoexmaConnectionString"].ConnectionString;
m_conn = new SqlConnection(connString);
m_SqlCommand = new SqlCommand();
m_SqlCommand.Connection = m_conn;
m_conn.Open();
}
public void BeginTrans()
{
m_SqlTransaction = m_conn.BeginTransaction();
}
public void CommitTrans()
{
m_SqlTransaction.Commit();
m_SqlTransaction = null;
}
public void Rollback()
{
m_SqlTransaction.Rollback();
m_SqlTransaction = null;
}
public void Open()
{
if (this.m_conn.State == ConnectionState.Closed)
{
m_conn.Open();
}
}
public void Close()
{
m_conn.Close();
}
public int ExecuteNonQuery(string sql, CommandType commandType, string[] sqlParams, object[] values)
{
m_SqlCommand.CommandText = sql;
m_SqlCommand.CommandType = commandType;
m_SqlCommand.Transaction = m_SqlTransaction;
m_SqlCommand.Parameters.Clear();
for (int i = 0; i < sqlParams.Length; i++)
{
SqlParameter param = new SqlParameter(sqlParams[i], values[i]);
m_SqlCommand.Parameters.Add(param);
}
return m_SqlCommand.ExecuteNonQuery();
}
public int ExecuteNonQuery(string sql)
{
m_SqlCommand.CommandText = sql;
m_SqlCommand.CommandType = CommandType.Text;
m_SqlCommand.Transaction = m_SqlTransaction;
m_SqlCommand.Parameters.Clear();
return m_SqlCommand.ExecuteNonQuery();
}
public DataSet ExecuteQuery(string sql, CommandType commandType, string[] sqlParams, Object[] values)
{
m_SqlCommand.CommandText = sql;
m_SqlCommand.CommandType = commandType;
m_SqlCommand.Transaction = m_SqlTransaction;
m_SqlCommand.Parameters.Clear();
for (int i = 0; i < sqlParams.Length; i++)
{
SqlParameter param = new SqlParameter(sqlParams[i], values[i]);
m_SqlCommand.Parameters.Add(param);
}
SqlDataAdapter sda = new SqlDataAdapter(m_SqlCommand);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public DataSet ExecuteQuery(string sql)
{
m_SqlCommand.CommandText = sql;
m_SqlCommand.CommandType = CommandType.Text;
m_SqlCommand.Transaction = m_SqlTransaction;
m_SqlCommand.Parameters.Clear();
SqlDataAdapter sda = new SqlDataAdapter(m_SqlCommand);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}
}
在Default.aspx.cs里使用:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Web
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GridView1.DataSource = new DAO.MYDAO().getAllInfo();
this.GridView1.DataBind();
}
}
}
}
即可。