asp.net管理mysql数据库的小工具

来源:网络时间:2011-06-21

  在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数据库进行管理,包括浏览表结构和数据、查询-修改-插入-删除数据,导出数据和表结构等,供大家参考和互相学习。代码写得不规范,还请大家指正。

  <%@ Page Language="C#" %>

  <%@ Import Namespace="System.Data" %>

  <%@ Import Namespace="System.IO" %>

  <%@ Import Namespace="System.Text" %>

  <%@ Import Namespace="MySql.Data.MySqlClient" %>

  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

  <html xmlns="http://www.w3.org/1999/xhtml">

  <head runat="server">

  <title>shouji138.com MYSQL Manager (DoNet)</title>

  <style type="text/CSS">

  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%;

  }

  </style>

  <script runat="server">

  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[i]["Key_name"].ToString().ToUpper() == "PRIMARY")

  {

  PKName = dt.Rows[i]["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 + "<br>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[i]["Field"].ToString().ToUpper() == columnname.ToUpper())

  {

  if (table.Rows[i]["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="<%=Server.MapPath("MySQL.sql") %>"

  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";

  }

发表评论

最新评论(共0条)