数据库学习
来源:互联网 发布:技术导航网源码 编辑:程序博客网 时间:2024/05/02 03:21
/***************************************************/
/////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文件夹里
- 学习数据库
- 学习数据库
- 数据库学习
- 数据库学习
- 数据库学习
- 学习数据库
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 数据库学习
- 学习数据库
- 数据库学习
- 学习“数据库”
- MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践
- Big Data
- 正则表达式
- 有关SVN Checkout
- FTP文件传输错误代码550
- 数据库学习
- 01背包 问题 思路 整理
- makefile编写(最终整理完整版)
- 数据库事务
- 改变javascript函数内部this指针指向的三种方法
- GDI/GDI+ 绘制网站流量统计报表 总结(1)
- CentOS 6.4 Nginx 安装过程中遇到的两个问题
- jquery判断页面元素是否存在
- Linux netstat命令详解