|
标签:ASP.NET MYSQL
在Asp.net 中,可以通过MySql.Data.dll来操作mysql数据库,写法跟操作SQL数据库类似,下面是相关的例子。
一、打开mysql数据库:
MySqlConnection DBConn = new MySqlConnection();
string connString ="Host=127.0.0.1;UserName=root;Password=root;Database=test;Port=3306;CharSet=utf8;Allow Zero Datetime=true";
DBConn.ConnectionString = connString;
DBConn.Open();
二、执行sql命令
string sqlstr="select * from test";
MySqlCommand command = new MySqlCommand(sqlstr, DBConn);
command.ExecuteNonQuery();
从上面可以看出,用法跟操作SQL数据库的SqlConnection、SqlCommand非常相似,下面我这里有一个完整的例子来实现对mysql数据库进行管理,包括浏览表结构和数据、查询-修改-插入-删除数据,导出数据和表结构等,供大家参考和互相学习。代码写得不规范,还请大家指正。
shouji138.com MYSQL Manager (DoNet)
body,td{font: 12px Arial,Tahoma;line-height: 16px;}
.input{font:12px Arial,Tahoma;background:#fff;border: 1px solid #666;padding:2px;height:18px;}
.area{font:12px 'Courier New', Monospace;background:#fff;border: 1px solid #666;padding:2px;}
.bt {border-color:#b0b0b0;background:#3d3d3d;color:#ffffff;font:12px Arial,Tahoma;height:22px;}
a {color: #00f;text-decoration:underline;}
a:hover{color: #f00;text-decoration:none;}
.alt1 td{border-top:1px solid #fff;border-bottom:1px solid #ddd;background:#f1f1f1;padding:5px 10px 5px 5px;}
.alt2 td{border-top:1px solid #fff;border-bottom:1px solid #ddd;background:#f9f9f9;padding:5px 10px 5px 5px;}
.focus td{border-top:1px solid #fff;border-bottom:1px solid #ddd;background:#ffffaa;padding:5px 10px 5px 5px;}
.head td{border-top:1px solid #fff;border-bottom:1px solid #ddd;background:#e9e9e9;padding:5px 10px 5px 5px;font-weight:bold;}
.head td span{font-weight:normal;}
form{margin:0;padding:0;}
h2{margin:0;padding:0;height:24px;line-height:24px;font-size:14px;color:#5B686F;}
ul.info li{margin:0;color:#444;line-height:24px;height:24px;}
u{text-decoration: none;color:#777;float:left;display:block;width:150px;margin-right:10px;}
p,div
{
line-height:260%;
}
private string m_Admin = "shouji138.com";
MySqlConnection DBConn = new MySqlConnection();
private string connString = string.Empty;
DataTable tblsDt = null;
int tblRowsCount = 0;
int tblsCount = 0;
float tblDbSize = 0f;
private bool OpenData()
{
if (Session["dbhost"] != null
&& Session["dbuser"] != null
&& Session["dbpass"] != null
&& Session["dbname"] != null
&& Session["dbport"] != null
&& Session["charset"] != null
&& Session["dbhost"].ToString().Trim() != string.Empty
&& Session["dbuser"].ToString().Trim() != string.Empty
&& Session["dbpass"].ToString().Trim() != string.Empty
&& Session["dbname"].ToString().Trim() != string.Empty
&& Session["dbport"].ToString().Trim() != string.Empty
&& Session["charset"].ToString().Trim() != string.Empty
)
{
connString = string.Format("Host = {0}; UserName = {1}; Password = {2}; Database = {3}; Port = {4};CharSet={5};Allow Zero Datetime=true",
Session["dbhost"].ToString().Trim(),
Session["dbuser"].ToString().Trim(),
Session["dbpass"].ToString().Trim(),
Session["dbname"].ToString().Trim(),
Session["dbport"].ToString().Trim(),
Session["charset"].ToString().Trim()
);
}
if (connString != string.Empty && DBConn.State != ConnectionState.Open)
{
DBConn.ConnectionString = connString;
try
{
DBConn.Open();
}
catch (Exception ex)
{
Response.Write("数据库连接失败,请检查连接字符串!" + ex.Message);
return false;
}
return true;
}
return false;
}
private void CloseData()
{
DBConn.Close();
}
private string FindPK(string tablename)
{
string PKName = string.Empty;
DataTable dt = RunTable("SHOW KEYS FROM " + tablename);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows["Key_name"].ToString().ToUpper() == "PRIMARY")
{
PKName = dt.Rows["Column_name"].ToString();
break;
}
}
return PKName;
}
private DataTable RunTable(string sqlstr)
{
DataTable data = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter();
try
{
OpenData();
da.SelectCommand = new MySqlCommand(sqlstr, DBConn);
da.Fill(data);
}
catch (Exception ex)
{
Response.Write("执行SQL错误:" + ex.Message + "SQL:" + sqlstr);
Response.End();
}
finally
{
da.Dispose();
DBConn.Close();
}
return data;
}
private void ShowAllTable()
{
string sqlstr = "SHOW TABLE STATUS";
tblsDt = RunTable(sqlstr);
PanTables.Visible = true;
tblRun.Visible = true;
}
private DataTable TableColumn(string tablename)
{
return RunTable("SHOW COLUMNS FROM " + tablename);
}
private DataTable TableStructure(string tablename)
{
return RunTable("SHOW FIELDS FROM " + tablename);
}
private bool isAuto_increment(string tblname, string columnname)
{
DataTable table = TableStructure(tblname);
bool boolIs = false;
for (int i = 0; i < table.Rows.Count; i++)
{
if (table.Rows["Field"].ToString().ToUpper() == columnname.ToUpper())
{
if (table.Rows["Extra"].ToString().ToLower() == "auto_increment")
{
boolIs = true;
break;
}
}
}
return boolIs;
}
private void ShowTableData()
{
PanShow.Visible = true;
tblRun.Visible = true;
sql_query.Value = "SELECT * FROM " + Request.QueryString["tblname"] + " LIMIT 0, 30";
}
private void ShowEditeData()
{
PanelEdit.Visible = true;
tblRun.Visible = true;
sql_query.Value = "SELECT * FROM " + Request.QueryString["tblname"] + " LIMIT 0, 30";
}
private void Structure()
{
PanelStructure.Visible = true;
tblRun.Visible = true;
sql_query.Value = "SELECT * FROM " + Request.QueryString["tblname"] + " LIMIT 0, 30";
}
private void InsertData()
{
PanelInsert.Visible = true;
tblRun.Visible = true;
sql_query.Value = "SELECT * FROM " + Request.QueryString["tblname"] + " LIMIT 0, 30";
}
private void ExportSucc()
{
ShowAllTable();
if (Session["exportinfo"] != null && Session["exportinfo"].ToString()!=string.Empty)
{
lblExport.Text = Session["exportinfo"].ToString();
divSucc.Visible = true;
Session["exportinfo"] = null;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["login"] == null || Session["login"].ToString().Length < 1)
{
PanelLogin.Visible = true;
}
else
{
PanelSucc.Visible = true;
}
if (!Page.IsPostBack)
{
txtpassword.Attributes.Add("onkeydown", "SubmitKeyClick('btnLogin');");
InitFrm();
if (OpenData())
{
ShowDBs();
if (Request.QueryString["action"] != null)
{
switch (Request.QueryString["action"].ToString())
{
case "show":
ShowTableData();
break;
case "edit":
ShowEditeData();
break;
case "deldata":
deldataData();
break;
case "insert":
InsertData();
break;
case "structure":
Structure();
break;
case "droptable":
DropTable();
break;
case "exportsucc":
ExportSucc();
break;
}
}
else
{
ShowAllTable();
}
}
}
ShowConnForm();
}
private void InitFrm()
{
if (Session["dbhost"] != null)
dbhost.Value = Session["dbhost"].ToString();
if (Session["dbuser"] != null)
dbuser.Value = Session["dbuser"].ToString();
if (Session["dbpass"] != null)
dbpass.Value = Session["dbpass"].ToString();
if (Session["dbname"] != null)
dbname.Value = Session["dbname"].ToString();
if (Session["dbport"] != null)
dbport.Value = Session["dbport"].ToString();
if (Session["charset"] != null)
{
charset.SelectedIndex = -1;
charset.Items.FindByValue(Session["charset"].ToString()).Selected = true;
}
//value=""
txtSavePath.Value = Server.MapPath(Request.ServerVariables["HTTP_HOST"].Replace(".", "").Replace(":", "") + "MySQL.sql");
}
private void ShowConnForm()
{
PanFrm.Visible = true;
}
protected void connect_ServerClick(object sender, EventArgs e)
{
connString = string.Format("Host = {0}; UserName = {1}; Password = {2}; Database = {3}; Port = {4};CharSet={5};Allow Zero Datetime=true",
dbhost.Value.Trim(),
dbuser.Value.Trim(),
dbpass.Value.Trim(),
dbname.Value.Trim(),
dbport.Value.Trim(),
charset.Value.Trim()
);
Session["dbhost"] = dbhost.Value.Trim();
Session["dbuser"] = dbuser.Value.Trim();
Session["dbpass"] = dbpass.Value.Trim();
Session["dbname"] = dbname.Value.Trim();
Session["dbport"] = dbport.Value.Trim();
Session["charset"] = charset.Value.Trim();
if (OpenData())
{
ShowDBs();
//ShowAllTable();
}
}
private string showSize(float size)
{
if (size > 1024 * 1024)
{
return Math.Round(size / (1024 * 1024), 3) + "M";
}
else if (size > 1024)
{
return Math.Round(size / 1024, 3) + "K";
}
else
{
return size + "B";
}
}
protected void Submit1_ServerClick(object sender, EventArgs e)
{
if (sql_query.Value.Trim() != string.Empty)
{
if (OpenData())
{
PanelQuery.Visible = true;
}
}
else
{
Response.Redirect(Request.ServerVariables["HTTP_REFERER"] + "", true);
}
}
protected void Submit2_ServerClick(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
string tblname = Request.QueryString["tblname"].Trim();
DataTable dt = TableColumn(tblname);
sb.Append(" update `" + tblname + "` set ");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i != 0)
sb.Append(",");
sb.Append("`" + dt.Rows[0].ToString().Trim() + "`=");
string columntype = dt.Rows[1].ToString().Trim();
bool mustAdd = false;
if (columntype.IndexOf("char") != -1 || columntype.IndexOf("datetime") != -1 || columntype.IndexOf("string") != -1)
{
mustAdd = true;
}
if (mustAdd)
{
sb.Append("'");
}
sb.Append(Request.Form["insertsql_" + dt.Rows[0].ToString().Trim().Replace("'", "''")]);
if (mustAdd)
{
sb.Append("'");
}
}
sb.Append(" where " + Request.QueryString["pk"].ToString() + " = " + Request.QueryString["v"].ToString() + "");
string sql = sb.ToString();
RunTable(sql);
Response.Redirect(Request.ServerVariables["Script_Name"] + "?action=show&tblname=" + tblname, true);
}
private void deldataData()
{
StringBuilder sb = new StringBuilder();
string tblname = Request.QueryString["tblname"].Trim();
sb.Append(" delete from `" + tblname + "` ");
sb.Append(" where " + Request.QueryString["pk"].ToString() + " = " + Request.QueryString["v"].ToString() + "");
string sql = sb.ToString();
RunTable(sql);
Response.Redirect(Request.ServerVariables["Script_Name"] + "?action=show&tblname=" + tblname, true);
}
private void ShowDBs()
{
string sql = "SHOW DATABASES";
seldbname.DataSource = new DataTable();
seldbname.DataBind();
ListItem item = new ListItem("选择数据库", "");
seldbname.Items.Add(item);
DataTable dt = RunTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
string dname = dt.Rows[0].ToString();
if (dname != "information_schema")
{
seldbname.Items.Add(new ListItem(dname, dname));
}
}
}
private void DropTable()
{
StringBuilder sb = new StringBuilder();
string tblname = Request.QueryString["tblname"].Trim();
sb.Append(" drop table `" + tblname + "` ");
string sql = sb.ToString();
RunTable(sql);
Response.Redirect(Request.ServerVariables["Script_Name"], true);
}
protected void btninsert_ServerClick(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
string tblname = Request.QueryString["tblname"].Trim();
DataTable dt = TableColumn(tblname);
sb.Append(" insert into `" + tblname + "` ( ");
int m = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!isAuto_increment(tblname, dt.Rows[0].ToString()))
{
m++;
if (m != 1)
sb.Append(",");
sb.Append("`" + dt.Rows[0].ToString().Trim() + "`");
}
}
sb.Append(" ) values (");
m = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!isAuto_increment(tblname, dt.Rows[0].ToString()))
{
m++;
if (m != 1)
sb.Append(",");
string columntype = dt.Rows[1].ToString().Trim();
bool mustAdd = false;
if (columntype.IndexOf("char") != -1 || columntype.IndexOf("datetime") != -1 || columntype.IndexOf("string") != -1)
{
mustAdd = true;
}
if (mustAdd)
{
sb.Append("'");
}
sb.Append(Request.Form["insertsql_" + dt.Rows[0].ToString().Trim().Replace("'", "''")]);
if (mustAdd)
{
sb.Append("'");
}
}
}
sb.Append(" ) ");
string sql = sb.ToString();
Response.Write(sql);
RunTable(sql);
Response.Redirect(Request.ServerVariables["Script_Name"] + "?action=show&tblname=" + tblname, true);
}
protected void seldbname_SelectedIndexChanged(object sender, EventArgs e)
{
Session["dbname"] = seldbname.Items[seldbname.SelectedIndex].Value.ToString().Trim();
Response.Redirect(Request.ServerVariables["Script_Name"] + "", true);
}
protected void btnLogin_Click(object sender, EventArgs e)
{
if (txtpassword.Value.Trim() == m_Admin)
{
Session["login"] = "login";
Response.Redirect(Request.ServerVariables["Script_Name"] + "", true);
}
}
//备份数据库
private string sqldumptable(string tblname)
{
StringBuilder sb = new StringBuilder();
sb.Append("DROP TABLE IF EXISTS `" + tblname + "`;\n");
sb.Append("CREATE TABLE " + tblname + " (\n");
int firstfield=1;
DataTable dtFields = RunTable("SHOW FIELDS FROM " + tblname + "");
for (int i = 0, k = dtFields.Rows.Count; i < k; i++)
{
if (firstfield != 1)
sb.Append(",\n");
else
firstfield = 0;
sb.Append("`"+dtFields.Rows["Field"].ToString() +"` "+ dtFields.Rows["Type"].ToString());
if (dtFields.Rows["Default"] != null && dtFields.Rows["Default"].ToString() != string.Empty)
sb.Append(" DEFAULT " + dtFields.Rows["Default"]);
if (dtFields.Rows["Null"].ToString().ToUpper() != "YES")
sb.Append(" NOT NULL ");
if (dtFields.Rows["Extra"].ToString() != "")
sb.Append(dtFields.Rows["Extra"].ToString());
}
dtFields.Dispose();
DataTable dtKeys = RunTable("SHOW KEYS FROM " + tblname + "");
bool haskey = false;
string PRIMARY = string.Empty;
for (int i = 0, k = dtKeys.Rows.Count; i < k; i++)
{
string kname = dtKeys.Rows["Key_name"].ToString();
if (kname.ToUpper() != "PRIMARY" && dtKeys.Rows["Non_unique"].ToString().Trim() == "0")
{
kname = "UNIQUE|" + kname + "";
}
if (kname.ToUpper() == "PRIMARY")
{
if (haskey)
{
PRIMARY = PRIMARY + ",";
}
else
{
haskey = true;
}
PRIMARY = PRIMARY + dtKeys.Rows["Column_name"];
}
else
{
sb.Append(",\n");
if (kname.Length>6 && kname.Substring(0, 6).ToUpper() == "UNIQUE")
{
kname = kname.Substring(7);
}
sb.Append(" KEY " + kname + " (" + dtKeys.Rows["Column_name"] + ")");
}
}
sb.Append(",\n PRIMARY KEY (" + PRIMARY + ") ");
sb.Append("\n);\n\n");
dtKeys.Dispose();
DataTable dtRows = RunTable("SELECT * FROM " + tblname);
for (int i = 0, k = dtRows.Rows.Count; i < k; i++)
{
sb.Append("INSERT INTO " + tblname + " VALUES(");
int fieldcounter = -1;
firstfield = 1;
for (int m = 0, n = dtRows.Columns.Count; m < n; m++)
{
if (firstfield != 1)
sb.Append(", ");
else
firstfield = 0;
if (dtRows.Rows[m] == null)
{
sb.Append("NULL");
}
else
{
sb.Append("'" + dtRows.Rows[m].ToString().Trim().Replace("'", "''") + "'");
}
}
sb.Append(");\n");
}
return sb.ToString();
}
private void SavetoFile(string info,string filepath)
{
FileStream stream = new FileStream(filepath, FileMode.Create, FileAccess.Write, FileShare.Delete | FileShare.ReadWrite);
StreamWriter writer = new StreamWriter(stream);
writer.WriteLine(info);
writer.Close();
stream.Close();
stream.Dispose();
writer.Dispose();
}
private void ExportDown(string info)
{
string filename = Request.ServerVariables["HTTP_HOST"] + "MySQL.sql";
Response.ContentType = "application/unknown";
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
Response.Write(info);
Response.End();
}
protected void btnExport_ServerClick(object sender, EventArgs e)
{
string tables = string.Empty;
StringBuilder infosb = new StringBuilder();
if(Request.Form["tables"]!=null)
{
tables = Request.Form["tables"].ToString().Trim();
string[] tableArr = tables.Split(',');
for (int i = 0, k = tableArr.Length; i < k; i++)
{
if (tableArr.Trim() != string.Empty)
{
infosb.Append(sqldumptable(tableArr.Trim()) + "\n\n\n\n\n\n");
}
}
if (cbSaveFile.Checked)
{
SavetoFile(infosb.ToString(), txtSavePath.Value.Trim());
Session["exportinfo"] = "" + txtSavePath.Value + "";
Response.Redirect(Request.ServerVariables["Script_Name"] + "?action=exportsucc", true);
}
else
{
ExportDown(infosb.ToString());
}
}
}
function SubmitKeyClick(button)
{
if (event.keyCode == 13)
{
event.keyCode=9;
event.returnValue = false;
document.getElementById("btnLogin").click();
}
}
function CheckAll(form) {
for(var i=0;i
[ 添加
| 结构
|
删除表 ]
数据库已经导出为
运行SQL语句 :
运行的SQL :
Name
Rows
Data_length
Create_time
Update_time
[ 添加 |
结构 |
删除表 ]
合计:
保存为文件
Action
编辑 | 删除
在表中编辑记录 »
0)
{
for (int i = 0; i < dColumn.Rows.Count; i++)
{
%>
在表中添加记录 »
表的结构 »
Field
Type
Null
Key
Default
Extra
Powered by shouji138.com
以上的例子需要MySql.Data.dll的支持,可以在http://www.shouji138.com/aspnet2/files/MySql.Data.dll.rar下载到。
项目演示地址: http://www.shouji138.com/aspnet2/mysql.aspx (登录密码:shouji138.com)
编译之后发布下载:http://www.shouji138.com/aspnet2/files/mysqlrelease.rar
源程序下载:http://www.shouji138.com/aspnet2/files/mysqlsource.rar
相关截图:
http://www.shouji138.com/aspnet2/files/1.jpg
http://www.shouji138.com/aspnet2/files/2.jpg
http://www.shouji138.com/aspnet2/files/3.jpg
http://www.shouji138.com/aspnet2/files/4.jpg
http://www.shouji138.com/aspnet2/files/5.jpg
http://www.shouji138.com/aspnet2/files/6.jpg
http://www.shouji138.com/aspnet2/files/7.jpg
http://www.shouji138.com/aspnet2/files/8.jpg
http://www.shouji138.com/aspnet2/files/9.jpg
|
|