using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
/// <summary>
/// WebService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService { private SqlConnection conn;
private SqlCommand cmd;
private SqlDataAdapter myda;
private DataSet myds;
private SqlDataReader mydr;
private string ConnString; public WebService () {
ConnString=ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
conn=new SqlConnection(ConnString);
cmd=new SqlCommand();
myda=new SqlDataAdapter();
myds=new DataSet();
} public void clear()
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (myds != null)
{
myds.Dispose();
}
if (mydr != null)
{
mydr.Dispose();
}
} /// <summary>
/// 处理无返回的sql语句
/// </summary>
/// <param name="strSql"></param>
[WebMethod]
public void ExcuteSql(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
//conn.Close();
//conn.Dispose();
}
/// <summary>
/// 返回一个表
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
[WebMethod]
public DataTable ExcuteSelect(string strSql)
{
myda.SelectCommand = new SqlCommand(strSql, conn);
myda.Fill(myds, "table");
return myds.Tables["table"];
} /// <summary>
/// 判断重复
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
//[WebMethod]
public bool HasName(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
mydr = cmd.ExecuteReader();
return mydr.Read();
}
}using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class Default2 : System.Web.UI.Page
{
WebService webs=new WebService();
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
string strCardID = this.tbCardID.Text;
string strSql = "select CardID from Staffinfo where CardID='" + strCardID + "';";
if (webs.HasName(strSql))
{
this.rfCardID.ErrorMessage = "对不起,此人以添加,请认真填写!";
}
else
{
string strName = this.tbName.Text;
string strPsw = this.tbPsw.Text;
string strAge = this.tbAge.Text;
string strBirthday = this.tbBirthday.Text;
string strSex = this.ddSex.SelectedValue;
string strPhone = this.tbPhote.Text;
string strAddress = this.tbAddress.Text;
string strRes = this.tbRes.Text;
strSql = "insert into Staffinfo(CardID,Name,Psw,Age,Birthday,Sex,Phone,Address,Res,Audit,Time) values('" + strCardID + "','" + strName + "','" + strPsw + "','" + strAge + "','" + strBirthday + "','" + strSex + "','" + strPhone + "','" + strAddress + "','" + strRes + "',0,'" + DateTime.Now + "');";
webs.ExcuteSql(strSql);
strSql = "select CardID from Staffinfo where Name='" + strName + "';";
DataTable dtTable = webs.ExcuteSelect(strSql);
Session["UserLevel"] = "User";
Session["UserID"] = dtTable.Rows[0]["CardID"].ToString();
Response.Redirect("Default.aspx");
}
}
}
}上面的代码提示的是
public void ExcuteSql(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
//conn.Close();
//conn.Dispose();
}里的conn.open();这句
提示是:连接未关闭,连接的当前状态为已打开 请问 怎么改写好呢?
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
/// <summary>
/// WebService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService { private SqlConnection conn;
private SqlCommand cmd;
private SqlDataAdapter myda;
private DataSet myds;
private SqlDataReader mydr;
private string ConnString; public WebService () {
ConnString=ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
conn=new SqlConnection(ConnString);
cmd=new SqlCommand();
myda=new SqlDataAdapter();
myds=new DataSet();
} public void clear()
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (myds != null)
{
myds.Dispose();
}
if (mydr != null)
{
mydr.Dispose();
}
} /// <summary>
/// 处理无返回的sql语句
/// </summary>
/// <param name="strSql"></param>
[WebMethod]
public void ExcuteSql(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
//conn.Close();
//conn.Dispose();
}
/// <summary>
/// 返回一个表
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
[WebMethod]
public DataTable ExcuteSelect(string strSql)
{
myda.SelectCommand = new SqlCommand(strSql, conn);
myda.Fill(myds, "table");
return myds.Tables["table"];
} /// <summary>
/// 判断重复
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
//[WebMethod]
public bool HasName(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
mydr = cmd.ExecuteReader();
return mydr.Read();
}
}using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class Default2 : System.Web.UI.Page
{
WebService webs=new WebService();
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
string strCardID = this.tbCardID.Text;
string strSql = "select CardID from Staffinfo where CardID='" + strCardID + "';";
if (webs.HasName(strSql))
{
this.rfCardID.ErrorMessage = "对不起,此人以添加,请认真填写!";
}
else
{
string strName = this.tbName.Text;
string strPsw = this.tbPsw.Text;
string strAge = this.tbAge.Text;
string strBirthday = this.tbBirthday.Text;
string strSex = this.ddSex.SelectedValue;
string strPhone = this.tbPhote.Text;
string strAddress = this.tbAddress.Text;
string strRes = this.tbRes.Text;
strSql = "insert into Staffinfo(CardID,Name,Psw,Age,Birthday,Sex,Phone,Address,Res,Audit,Time) values('" + strCardID + "','" + strName + "','" + strPsw + "','" + strAge + "','" + strBirthday + "','" + strSex + "','" + strPhone + "','" + strAddress + "','" + strRes + "',0,'" + DateTime.Now + "');";
webs.ExcuteSql(strSql);
strSql = "select CardID from Staffinfo where Name='" + strName + "';";
DataTable dtTable = webs.ExcuteSelect(strSql);
Session["UserLevel"] = "User";
Session["UserID"] = dtTable.Rows[0]["CardID"].ToString();
Response.Redirect("Default.aspx");
}
}
}
}上面的代码提示的是
public void ExcuteSql(string strSql)
{
cmd.CommandText = strSql;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
//conn.Close();
//conn.Dispose();
}里的conn.open();这句
提示是:连接未关闭,连接的当前状态为已打开 请问 怎么改写好呢?
//思路:在使用完连接后,及时关闭
// 重新使用后,再打开
//这种情况,一般是上述两种情况之一public void ExcuteSql(string strSql)
{
if(conn.ConnectionState != State.Opened)
{
conn.Open();
}
cmd.CommandText = strSql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//conn.Close();
//conn.Dispose();
}
public SqlDataAdapter DataAd = null;
public SqlDataReader DataRe = null;
public SqlCommand Com_mand = null;
public SqlCommandBuilder ComBuider = null;
public DataSet DataSe = null; public void Cnn_HS()
{
if (Cnn.State == ConnectionState.Open) //判断连接状况,如果打开,就把连接关闭
{
Cnn.Close();
}
Cnn.Open(); //打开连接 }
你可以把这个写成一个类,以后每次调用这个类就可以了。
应该很详细吧?
{
Cnn.Close();
}
Cnn.Open(); //打开连接
很实用