asp.net与SQL数据库连接
public string m_sMessage = ""; //返回信息
private string m_sConnectionString = ""; //连接字符串
private string m_sServerName = ""; //服务器
private string m_sDataBase = ""; //数据库
private string m_sUserId = ""; //帐号
private string m_sPassword = ""; //密码
public SqlConnection m_conObj = new SqlConnection(); //连接对象
this.m_sConnectionString = "data source=" + this.m_sServerName + ";" +
"initial catalog=" + this.m_sDataBase + ";" +
"user id=" + this.m_sUserId + ";" +
"password=" + this.m_sPassword + "";
或者
this.m_sConnectionString = "server=" + this.m_sServerName + ";" + "database=" + this.m_sDataBase + ";" + "user id=" + this.m_sUserId + ";" + "password=" + this.m_sPassword;
#region "打开连接（OpenConnection）"
/// <returns>打开成功返回true，失败返回false</returns>
public bool OpenConnection()
{
try
{
//-------检测连接是否已打开---------
if (this.m_conObj.State == System.Data.ConnectionState.Open)
return true;
//----------------------------------
//-------打开连接--------
this.m_conObj.ConnectionString = this.m_sConnectionString;
this.m_conObj.Open();
//-----------------------
//-------检测连接状态--------
if (this.m_conObj.State != System.Data.ConnectionState.Open)
this.m_sMessage = "打开连接失败！";
//---------------------------
}
catch (Exception ex)
{
this.m_sMessage = ex.ToString();
return false;
}
return true;
}
#endregion
#region "关闭连接（CloseConnection）"
public bool CloseConnection()
{
if (this.m_conObj.State == System.Data.ConnectionState.Open)
{
this.m_conObj.Close();
}
this.m_conObj.Dispose();
return true;
}
#endregion
#region "执行无返回值的sql语句（ExecuteNonQuery）"
public bool ExecuteNonQuery(string p_sCommand)
{
//--------打开连接失败的话不执行查询---------
if (!this.OpenConnection())
return false;
//-------------------------------------------
try
{
SqlCommand cmdObj = this.m_conObj.CreateCommand();
cmdObj.CommandText = p_sCommand;
//----------------无返回值---------------------------
cmdObj.ExecuteNonQuery();
}
catch (Exception ex)
{
this.m_sMessage = ex.ToString();
return false;
}
finally
{
this.CloseConnection();
}
return true;
}
#endregion
#region "执行单返回值的sql语句（ExecuteResult）"
public object ExecuteResult(string p_sCommand)
{
object oReturn = null;
//--------打开连接失败的话不执行查询---------
if (!this.OpenConnection())
return oReturn;
//-------------------------------------------
try
{
SqlCommand cmdObj = this.m_conObj.CreateCommand();
cmdObj.CommandText = p_sCommand;
oReturn = cmdObj.ExecuteScalar();//执行单返回值的sql语句
}
catch (Exception ex)
{
this.m_sMessage = ex.ToString();
}
finally
{
this.CloseConnection();
}
return oReturn;
}
#endregion
#region "事务控制执行sql命令组（ExecuteSqlsByAffair）"
public bool ExecuteSqlsByAffair(ArrayList p_oSqls)
{
//--------打开连接失败的话不执行查询---------
if (!this.OpenConnection())
return false;
//-------------------------------------------
SqlTransaction oTransaction = this.m_conObj.BeginTransaction(); //起动事务操作
try
{
SqlCommand cmdObj = this.m_conObj.CreateCommand();
cmdObj.Transaction = oTransaction; //指定command对象的事务对象
for (int i = 0; i < p_oSqls.Count; i++)
{
cmdObj.CommandText = p_oSqls[i].ToString();
cmdObj.ExecuteNonQuery();
}
oTransaction.Commit(); //成功执行提交事务
}
catch (Exception ex)
{
oTransaction.Rollback(); //执行失败事务回滚
this.m_sMessage = ex.ToString();
return false;
}
finally
{
this.CloseConnection();
}
return true;
}
#endregion
#region "获取数据表（GetDataTable）"
public DataTable GetDataTable(string p_sCommand, string p_sTableName, int p_iCurrentPage, int p_iPageSize)
{
//--------打开连接失败的话不执行查询---------
if (!this.OpenConnection())
return null;
//-------------------------------------------
try
{
DataSet oDs = new DataSet();
SqlCommand cmdObj = this.m_conObj.CreateCommand();
cmdObj.CommandText = p_sCommand;
SqlDataAdapter daObj = new SqlDataAdapter(cmdObj);
daObj.Fill(oDs, (p_iCurrentPage - 1) * p_iPageSize, p_iPageSize, p_sTableName);
return oDs.Tables[0];
}
catch (Exception ex)
{
this.m_sMessage = ex.ToString();
return null;
}
finally
{
this.CloseConnection();
}
}
#endregion
