/***************************************************/
/SQL Server数据库
/***************************************************/
在web.config中添加:
<appSettings>
<add key="DBConnectionString" value="Data Source=TRIMPS-WZF-PC\SQL2005;Initial Catalog=TEST;Persist Security Info=True;User ID='SA';Password=''"/>
</appSettings>
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=TRIMPS-WZF-PC\SQL2005;Initial Catalog=TEST;Persist Security Info=True;User ID='SA';Password=''" providerName="System.Data.SqlClient"/>
</connectionStrings>
在正文中添加:
using System.Data.SqlClient;
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DBConnectionString"]);//SqlConnection建立链接
string sql = "SELECT * from emploee_tb where pi_id='" + id + "'";
SqlDataAdapter DA = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
con.Open();
DA.Fill(ds);
con.Close();
DataTable dt = ds.Tables[0];
// int count = dt.Rows.Count;
gvDicType.DataSource = dt; //gvDicType要赋值的控件的名字
gvDicType.DataBind(); //绑定数据
操作数据库:
1、如果用控件的话 SqlDataSource1.Insert(); //调用控件自带的方法
2、如果用SQL语句的话
string userName = this.TxtName.Text; //获取用户名
string nickName = this.TxtNickName.Text;
string sex = "";
if (radlistSex.SelectedValue.Trim() == "男")
{
sex = "男";
}
else
{
sex = "女";
}
string phone = this.TxtPhone.Text;
string email = this.TxtEmail.Text;
string work = this.TxtWork.Text;
string city = this.TxtCity.Text;
//创建SQL语句,用来添加用户详细信息
string sqlInsert = "insert into class_Table values('" + userName + "','" + nickName + "','" + sex + "','" + phone + "','" + email + "','" + work + "','" + city + "')";
OperateDataBase odb =new OperateDataBase(); //实例化对象
bool add = false;
add=odb.ExceSql(sqlInsert); //调用odb类的ExceSql方法执行添加操作 //ExceSql(sqlInsert)添加
if(add == true)
{
Response.Write("<script language=javascript>alert('添加成功');location='../Default.aspx'</script>");
}
else
{
Response.Write("<script language=javascript>alert('添加失败');location='javascript:history.go(-1)'</script>");
}
//ExceSql(sqlInsert)/
//此方法用来执行SQL语句
public bool ExceSql(string strSqlCom)
{
Open();
SqlCommand sqlCom = new SqlCommand(strSqlCom, conn);
try
{
sqlCom.ExecuteNonQuery(); //执行添加操作的SQL语句
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
/****************************************************************/
//MySQL数据库//
/****************************************************************/
protected void BtnRead_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DBConnectionString"]);
string sql = "select * from wzf ";
SqlDataAdapter DA = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
con.Open(); //打开数据库
DA.Fill(ds);
con.Close();
DataTable dt = ds.Tables[0]; //存到数据表中
GridView1.DataSource = dt;
GridView1.DataBind();
}
/// <summary>
/// mysql数据库的连接和 读、
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e) //mysql数据库
{
string connStr = "server=localhost;user id=root;password=3393;database=0622";
MySqlConnection myConn = new MySqlConnection(connStr);
myConn.Open();
读(查)///
string sql = "select * from wzf where id=2";
MySqlCommand cmd = new MySqlCommand(sql,myConn);
MySqlDataAdapter dr = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
dr.Fill(dt);
if(dt.Rows.Count>0)
{
txt_SendContent.Text = dt.Rows[0][0].ToString() + "---" + dt.Rows[0][1].ToString() + "---" + dt.Rows[0][2].ToString();
}
///写(增)/
string sqlWrite = "insert into wzf(name,number) values('tgy','123456')";
MySqlCommand cmd1 = new MySqlCommand(sqlWrite, myConn);
cmd1.ExecuteNonQuery();
///更新/
string sqlUp = "update wzf set number = 445566 where id=2";
MySqlCommand cmd2 = new MySqlCommand(sqlUp, myConn);
cmd2.ExecuteNonQuery();
删除
string sqlDelete = "delete from wzf where id=4";
MySqlCommand cmd3 = new MySqlCommand(sqlDelete, myConn);
cmd3.ExecuteNonQuery();
string sql = "select * from wzf where id<=7 order by id desc"; //降序排序
string sqlOrder = "select * from wzf where id in (select max(id) from wzf)";//取表中最大id的那条记录
// string sql1 = "CREATE TEMPORARY TABLE tmp_table (maxonline int,srv_ip VARCHAR(30),fld_date datetime)";
// string sql2 = @"insert into tmp_table SELECT 33,'ip1','2007-5-5'";//测试数据
// string sql3 = "select sum(maxonline),DATE_FORMAT(fld_date, '%Y-%m-%d') from tmp_table group by fld_date";
// cmd.CommandText = sql1;
// cmd.ExecuteNonQuery();
// cmd.CommandText = sql2;
// cmd.ExecuteNonQuery();
// cmd.CommandText = sql3;
// MySqlDataReader rd = cmd.ExecuteReader();
//cmd.CommandText = sql;
//MySqlDataReader rd = cmd.ExecuteReader();
//txt_SendContent.Text = rd;
//GridView1.DataSource = rd;
//GridView1.DataBind();
//myConn.Close();
}
}
说明:首先添加引用->.NET->MySql.Data v2.0.50727 (vs2005时选择) / MySql.Data v4.0.30319 (vs2010时选择)
添加命名空间:using MySql.Data.MySqlClient;
总结:若是创建数据库、表、增、删、改、查 则用cmd.ExecuteNonQuery();
若是读MySqlDataReader rd = cmd.ExecuteReader();
调用DLL的话,要把DLL文件放在debug的bin文件夹里
/SQL Server数据库
/***************************************************/
在web.config中添加:
<appSettings>
<add key="DBConnectionString" value="Data Source=TRIMPS-WZF-PC\SQL2005;Initial Catalog=TEST;Persist Security Info=True;User ID='SA';Password=''"/>
</appSettings>
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=TRIMPS-WZF-PC\SQL2005;Initial Catalog=TEST;Persist Security Info=True;User ID='SA';Password=''" providerName="System.Data.SqlClient"/>
</connectionStrings>
在正文中添加:
using System.Data.SqlClient;
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DBConnectionString"]);//SqlConnection建立链接
string sql = "SELECT * from emploee_tb where pi_id='" + id + "'";
SqlDataAdapter DA = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
con.Open();
DA.Fill(ds);
con.Close();
DataTable dt = ds.Tables[0];
// int count = dt.Rows.Count;
gvDicType.DataSource = dt; //gvDicType要赋值的控件的名字
gvDicType.DataBind(); //绑定数据
操作数据库:
1、如果用控件的话 SqlDataSource1.Insert(); //调用控件自带的方法
2、如果用SQL语句的话
string userName = this.TxtName.Text; //获取用户名
string nickName = this.TxtNickName.Text;
string sex = "";
if (radlistSex.SelectedValue.Trim() == "男")
{
sex = "男";
}
else
{
sex = "女";
}
string phone = this.TxtPhone.Text;
string email = this.TxtEmail.Text;
string work = this.TxtWork.Text;
string city = this.TxtCity.Text;
//创建SQL语句,用来添加用户详细信息
string sqlInsert = "insert into class_Table values('" + userName + "','" + nickName + "','" + sex + "','" + phone + "','" + email + "','" + work + "','" + city + "')";
OperateDataBase odb =new OperateDataBase(); //实例化对象
bool add = false;
add=odb.ExceSql(sqlInsert); //调用odb类的ExceSql方法执行添加操作 //ExceSql(sqlInsert)添加
if(add == true)
{
Response.Write("<script language=javascript>alert('添加成功');location='../Default.aspx'</script>");
}
else
{
Response.Write("<script language=javascript>alert('添加失败');location='javascript:history.go(-1)'</script>");
}
//ExceSql(sqlInsert)/
//此方法用来执行SQL语句
public bool ExceSql(string strSqlCom)
{
Open();
SqlCommand sqlCom = new SqlCommand(strSqlCom, conn);
try
{
sqlCom.ExecuteNonQuery(); //执行添加操作的SQL语句
return true;
}
catch
{
return false;
}
finally
{
Close();
}
}
/****************************************************************/
//MySQL数据库//
/****************************************************************/
protected void BtnRead_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["DBConnectionString"]);
string sql = "select * from wzf ";
SqlDataAdapter DA = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
con.Open(); //打开数据库
DA.Fill(ds);
con.Close();
DataTable dt = ds.Tables[0]; //存到数据表中
GridView1.DataSource = dt;
GridView1.DataBind();
}
/// <summary>
/// mysql数据库的连接和 读、
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e) //mysql数据库
{
string connStr = "server=localhost;user id=root;password=3393;database=0622";
MySqlConnection myConn = new MySqlConnection(connStr);
myConn.Open();
读(查)///
string sql = "select * from wzf where id=2";
MySqlCommand cmd = new MySqlCommand(sql,myConn);
MySqlDataAdapter dr = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
dr.Fill(dt);
if(dt.Rows.Count>0)
{
txt_SendContent.Text = dt.Rows[0][0].ToString() + "---" + dt.Rows[0][1].ToString() + "---" + dt.Rows[0][2].ToString();
}
///写(增)/
string sqlWrite = "insert into wzf(name,number) values('tgy','123456')";
MySqlCommand cmd1 = new MySqlCommand(sqlWrite, myConn);
cmd1.ExecuteNonQuery();
///更新/
string sqlUp = "update wzf set number = 445566 where id=2";
MySqlCommand cmd2 = new MySqlCommand(sqlUp, myConn);
cmd2.ExecuteNonQuery();
删除
string sqlDelete = "delete from wzf where id=4";
MySqlCommand cmd3 = new MySqlCommand(sqlDelete, myConn);
cmd3.ExecuteNonQuery();
string sql = "select * from wzf where id<=7 order by id desc"; //降序排序
string sqlOrder = "select * from wzf where id in (select max(id) from wzf)";//取表中最大id的那条记录
// string sql1 = "CREATE TEMPORARY TABLE tmp_table (maxonline int,srv_ip VARCHAR(30),fld_date datetime)";
// string sql2 = @"insert into tmp_table SELECT 33,'ip1','2007-5-5'";//测试数据
// string sql3 = "select sum(maxonline),DATE_FORMAT(fld_date, '%Y-%m-%d') from tmp_table group by fld_date";
// cmd.CommandText = sql1;
// cmd.ExecuteNonQuery();
// cmd.CommandText = sql2;
// cmd.ExecuteNonQuery();
// cmd.CommandText = sql3;
// MySqlDataReader rd = cmd.ExecuteReader();
//cmd.CommandText = sql;
//MySqlDataReader rd = cmd.ExecuteReader();
//txt_SendContent.Text = rd;
//GridView1.DataSource = rd;
//GridView1.DataBind();
//myConn.Close();
}
}
说明:首先添加引用->.NET->MySql.Data v2.0.50727 (vs2005时选择) / MySql.Data v4.0.30319 (vs2010时选择)
添加命名空间:using MySql.Data.MySqlClient;
总结:若是创建数据库、表、增、删、改、查 则用cmd.ExecuteNonQuery();
若是读MySqlDataReader rd = cmd.ExecuteReader();
调用DLL的话,要把DLL文件放在debug的bin文件夹里