这里我们使用图形化操作的SQL Manager 2005 Lite for MySQL来建立数据,它的操作界面非常类似OFFICE软件,使用方便、很容量上手、下面开始建立数据库及表
单击"Creat New DataBase":新建DB
![](https://i-blog.csdnimg.cn/blog_migrate/322b6326cddcfa197e8ce63940b71a93.jpeg)
输入密码:
![](https://i-blog.csdnimg.cn/blog_migrate/e2fc21155ec5f6819d9014c45819f6ac.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/669c5b74ce37c2d99024ed1840d509cd.jpeg)
选择客户端编码为gb2312,防乱码;也可以在新建好的数据库右键选择"DataBase Registration Info"更改编码:
![](https://i-blog.csdnimg.cn/blog_migrate/6e90fab6667cf095330a0edd05b95944.jpeg)
新建表:输入表名:
![](https://i-blog.csdnimg.cn/blog_migrate/bf9277048a7dfc29f6fbdbe4967154d8.jpeg)
新建字段
![](https://i-blog.csdnimg.cn/blog_migrate/1dd0158c1fbfe590a81efed01d6400f9.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/51461bab66495b1f6ab8f66879514487.jpeg)
点击加号,手动添加数据,点出对号,提交数据:
![](https://i-blog.csdnimg.cn/blog_migrate/deb675badf6c76238665dc2b0977dc3e.jpeg)
选择DDL选项,直接看到刚才操作的脚本;也可以不用上面的方式操作,直接写sql脚本来创建数据:
![](https://i-blog.csdnimg.cn/blog_migrate/228eb959ad160e4247e5667f2a627b9c.jpeg)
二、ADO.NET数据操作
推荐使用MySQL Connector Net 5.0.3
web.config添加连接字符串:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
为了方便,把对数据的访问封装到一个类中:
1
//
执行sql
2
public
int
ExecuteSql(
string
strSql, MySqlParameter [] myPar)
3![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
try
5![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
6
myConnection.Open();
7
MySqlCommand cmd = new MySqlCommand(strSql, myConnection);
8
if (myPar != null)
9![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
10
foreach (MySqlParameter spar in myPar)
11![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
cmd.Parameters.Add(spar);
13
}
14
}
15
int result = cmd.ExecuteNonQuery();
16
myConnection.Close();
17
return result;
18
}
19
catch
20![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
return 0;
22
}
23
}
获取数据:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
4
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
6
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
10
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
12
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
14
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
15
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
19
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
21
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
23
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
public
DataSet GetDataSet(
string
strSql)
2![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
3
try
4![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
5
MySqlDataAdapter da = new MySqlDataAdapter(strSql, myConnection);
6
DataSet ds = new DataSet();
7
da.Fill(ds);
8
return ds;
9
}
10
catch
11![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
return null;
13
}
14
}
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
3
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
5
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
10
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
12
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![ExpandedSubBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
14
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
在页面中我们用一个gridview实现数据的读取、写入、编辑、删除等操作:
![](https://i-blog.csdnimg.cn/blog_migrate/cf1f9682c1089b5efd5b76a4e4e020af.jpeg)
数据绑定:
1
//
databind
2
protected
void
BindGrid()
3![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
DataSet ds = obj.GetDataSet("SELECT * FROM book ORDER BY bid");
5
GridView1.DataSource = ds;
6
GridView1.DataBind();
7
ds.Dispose();
8
}
添加数据:
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
4
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
string
strSQL
=
"
INSERT INTO book (bname,author,publish) VALUES (?bname,?author,?publish)
"
;
2
MySqlParameter[] mysp
=
3![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
4
new MySqlParameter ("?bname",MySqlDbType.VarChar ),
5
new MySqlParameter ("?author",MySqlDbType.VarChar),
6
new MySqlParameter ("?publish",MySqlDbType.VarChar)
7
}
;
8
mysp[
0
].Value
=
txtName.Text.Trim();
9
mysp[
1
].Value
=
txtAuthor.Text.Trim();
10
mysp[
2
].Value
=
txtPublish.Text.Trim();
11
if
(obj.ExecuteSql(strSQL, mysp)
==
1
)
12![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
13
Response.Write("<script>alert('提交成功');</script>");
14
BindGrid();
15
txtName.Text = txtAuthor.Text = txtPublish.Text = "";
16
}
注意参数符号是"?"而不是"@",这一点不同于sql server![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
4
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
8
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](https://www.cnblogs.com/Images/dot.gif)
13
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![InBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![ExpandedBlockEnd.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
其它编辑等操作代码等请下载文件
下载详细完整的代码文件 /Files/chy710/MySQL_ADONET.rar
下篇预告:mysql开发中的乱码问题