一、获取获取数据库名称
存储过程存储在 information_schema 系统库的 routines 表中,
routine_type列 表示是存储过程还是存储函数。
routine_name列 表示是存储过程名称。
routine_schema 列 表示的是数据库名称。
查询 routines 就可以得到存储过程名称了

select routine_name,routine_type from information_schema.ROUTINES where routine_schema = 'mytest';

routine_name

routine_type

addShouKuanMa_

PROCEDURE

FindUserInfo

PROCEDURE

GetQrCodeTableName

FUNCTION

TextFunction

PROCEDURE

二、获得存储过程代码
根据名 show create Procedure|Function FunName;
查询得到的列 Create Procedure|Function 即是存储过程完整代码。
即可获取到存储过程代码。

show create procedure TextFunction;

mysql 存储过程 备份 导入_存储过程

有了以上两个命令就可以把存储过程存储为单文件了。

三、把存储为文件形式的存储过程恢复到数据库。
只需要文件读取出来，像执行sql语句一样执行就可以恢复成存储过程了。
要注意的是
1.如果存储过程本身就存在,则会报错，所以要在保存为sql文件的时候加一句 Drop 存储过程。并且后面需要加上 Commit;不然不会报错，也恢复不成功!
如:

Drop PROCEDURE if exists tmp;
Commit;
CREATE DEFINER=`root`@`localhost` PROCEDURE `tmp`()
.....

2.如果存储过程中有会话变量会报错。解决方法
链接字符串中加上AllowUserVariables=True;

代码送上

#if MySQL

using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Threading.Tasks;
using System.Text;
using System.Diagnostics;
using System.IO;

namespace MrWu.DB {
	/// <summary>
	/// mysql操作
	/// </summary>
	public class MySQL {
		/// <summary>
		/// 链接池
		/// </summary>
		private class _ConnectionPool {
			/// <summary>
			/// 链接字符串
			/// </summary>
			private readonly string _connstr;

			/// <summary>
			/// 缓存链接数量
			/// </summary>
			private readonly int _itemCount;

			/// <summary>
			/// 所有的链接
			/// </summary>
			private readonly ConcurrentQueue<MySqlConnection> _conns = new ConcurrentQueue<MySqlConnection>();

			public _ConnectionPool(SqlConfig config) {
				this._itemCount = config.cacheCount;
				this._connstr = string.Format("server={0};port={1};user={2};password={3};AllowUserVariables={4};database=",
				                              config.host, config.port, config.username, config.pwd,config.AllowUserVariables);
			}

			/// <summary>
			/// 获取一个连接
			/// </summary>
			/// <param name="dbbase"></param>
			/// <returns></returns>
			public MySqlConnection GetConnection(string dbbase) {
				MySqlConnection conn;
				if (_conns.TryDequeue(out conn)) {
					conn.ChangeDatabase(dbbase);
					return conn;
				}
				Console.WriteLine("创建一链接!" + _conns.Count);
				return new MySqlConnection(this._connstr + dbbase);
			}

			/// <summary>
			/// 放入一个连接
			/// </summary>
			/// <param name="conn"></param>
			public void Push(MySqlConnection conn) {
				Task.Factory.StartNew(
					() => {
						if (_conns.Count < _itemCount) {
							//Console.WriteLine("放入一个连接Begin");
							_conns.Enqueue(conn); //好像不会阻塞,Task可能是多余的,
							//Console.WriteLine("放入一个链接!");
						} else
							conn.Close();
					}
				);
			}
		}

		/*
		 * 1.执行sql语句 获得执行sql语句后的结果
		 * 2.执行存储过程 获得存储过程的结果
		 * 
		 * 每个库都有一个链接
		 * 
		 * */

		private _ConnectionPool _ConnPool = null;

		/// <summary>
		/// 初始化
		/// </summary>
		/// <param name="config"></param>
		public MySQL(SqlConfig config) {
			this._ConnPool = new _ConnectionPool(config);
		}
		
		/// <summary>
		/// 获取一个连接
		/// </summary>
		/// <param name="dbbase"></param>
		/// <returns></returns>
		public MySqlConnection GetConnection(string dbbase){
			return _ConnPool.GetConnection(dbbase);
		}
		
	

		/// <summary>
		/// 执行sql语句
		/// </summary>
		/// <param name="dbbase">数据库</param>
		/// <param name="sql">sql语句</param>
		/// <param name="ds">如果是查询 使用传入ds实例</param>
		public int ExecuteSql(string dbbase, string sql, List<MySqlAloneParameter> mysqlparams = null, DataSet ds = null) {
			MySqlCommand cmd = new MySqlCommand(sql,_ConnPool.GetConnection(dbbase));
			cmd.CommandType = CommandType.Text;

			if (mysqlparams != null) {
				int len = mysqlparams.Count;
				for (int i=0;i<len;i++) {
					MySqlAloneParameter msap = mysqlparams[i];
					cmd.Parameters.Add(msap.paramname, msap.type, msap.valuelength);
					cmd.Parameters[msap.paramname].Value = msap.paramvalue;
				}
			}

			return _Execute(cmd, ds);
		}

		/// <summary>
		/// 普通查询一个表
		/// </summary>
		/// <param name="dbbase">数据库名称</param>
		/// <param name="table">表名</param>
		/// <param name="column">列名</param>
		/// <param name="where">条件</param>
		/// <param name="count">查询的数量</param>
		/// <returns>查询到的表</returns>
		public DataTable Select(string dbbase, string table, string[] column = null, string where = null, int count = 0) {
			StringBuilder sb = new StringBuilder();
			sb.Append("select ");
			if (count > 0) {
				sb.Append(string.Format("Top {0} ", count));
			}
			if (column != null && column.Length > 0) {
				int len = column.Length;
				for (int i = len - 1; i >= 0; i--) {
					sb.Append(column[i]);
					if (i > 0)
						sb.Append(",");
				}
			} else {
				sb.Append("*");
			}

			sb.Append(" from ");
			sb.Append(table);
			if (!string.IsNullOrEmpty(where)) {
				sb.Append(" where ");
				sb.Append(where);
			}
			DataSet ds = new DataSet();

			Console.WriteLine("sql:" + sb.ToString());
			string ttt = sb.ToString();
			ExecuteSql(dbbase, sb.ToString(), null, ds);
			if (ds.Tables == null || ds.Tables.Count == 0)
				return null;
			return ds.Tables[0];
		}

		/// <summary>
		/// 执行sql命令
		/// </summary>
		/// <param name="cmd"></param>
		/// <param name="ds"></param>
		private int _Execute(MySqlCommand cmd, DataSet ds = null) {
			int result = 0;
			if (cmd.Connection.State == ConnectionState.Closed)
				cmd.Connection.Open();
			if (ds == null)
				result = cmd.ExecuteNonQuery();
			else {
				MySqlDataAdapter sda = new MySqlDataAdapter();
				sda.SelectCommand = cmd;
				sda.Fill(ds);   //调用会自动执行 ExecuteNonQuery();	不要重复调用,很容易出错!
				sda.Dispose();	//一定要关闭,不然下次执行报错;
			}
			_ConnPool.Push(cmd.Connection);   //放入链接
			return result;
		}

		/// <summary>
		/// 保存每个存储过程为单个文件
		/// </summary>
		/// <param name="dbbase">那个数据库的</param>
		/// <param name="path">存储的路径</param>
		/// <param name="method">如果导出其中一个存储过程，则填存储过程名称</param>
		public void SaveMethod(string dbbase,string path,string method=null){
			
			DataTable dt;
			
			if(method == null)
				dt = Select("information_schema","routines",new string[]{"routine_name","routine_type"},string.Format("routine_schema='{0}'",dbbase));
			else
				dt = Select("information_schema","routines",new string[]{"routine_name","routine_type"},string.Format("routine_schema='{0}' and routine_name='{1}'",dbbase,method));
			
			foreach(DataColumn item in dt.Columns){
				Console.WriteLine(item.ColumnName);
			}
			int len = dt.Rows.Count;
			for(int i=0;i<len;i++){
				SaveMethod(dbbase,path,dt.Rows[i]["routine_name"] as string,dt.Rows[i]["routine_type"] as string);
			}
		}
		
		/// <summary>
		/// 保存方法
		/// </summary>
		/// <param name="dbbase"></param>
		/// <param name="path"></param>
		/// <param name="methodName"></param>
		/// <param name="methodType"></param>
		private void SaveMethod(string dbbase,string path,string methodName,string methodType){
			if(!Directory.Exists(path)){
				Directory.CreateDirectory(path);
			}
			
			string sql = string.Format("show create {0} {1};",methodType,methodName);
			DataSet ds = new DataSet();
			ExecuteSql(dbbase,sql,null,ds);
			
			if(ds.Tables == null || ds.Tables.Count == 0 || ds.Tables[0].Rows == null || ds.Tables[0].Rows.Count == 0)
				return;
			path = string.Format("{0}/{1}.sql",path,methodName);
			
			string sqlCode = string.Format("Drop {0} if exists {1};{2}",methodType,methodName,Environment.NewLine);
			sqlCode  += "Commit;"+Environment.NewLine;
			sqlCode += ds.Tables[0].Rows[0][string.Format("Create {0}",methodType)] as string;
			
			File.WriteAllText(path,sqlCode,Encoding.UTF8);
		}
		
		/// <summary>
		/// 执行sql文件
		/// </summary>
		/// <param name="path"></param>
		public void Execute(string dbbase,string path){
			if(File.Exists(path)){
				try{
					string sql = File.ReadAllText(path);
					ExecuteSql(dbbase,sql);
				}catch(Exception e){
					Console.WriteLine("运行出错:" + path + Environment.NewLine + e.ToString());
				}
			}else if(Directory.Exists(path)){
				DirectoryInfo dinfo = new DirectoryInfo(path);
				
				string[] paths = Directory.GetDirectories(path);
				
				foreach(var item in paths){
					
					Console.WriteLine(item);
					
					Execute(dbbase,item);
				}
				
				FileInfo[] finfos = dinfo.GetFiles();
				
				foreach(var fl in finfos){
					Execute(dbbase,fl.FullName);
				}
			}
		}
	}
}
#endif
/*
 * 由SharpDevelop创建。
 * 用户： Administrator
 * 日期: 2019-03-21
 * 时间: 10:10
 * 
 * 要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using System.Net;

namespace MrWu.DB
{
	/// <summary>
	/// sql 配置
	/// </summary>
	public class SqlConfig
	{	
        /// <summary>
        /// 配置名称
        /// </summary>
        public string name{
        	get;
        	set;
        }   
        
		/// <summary>
		/// 
		/// </summary>
		public IPAddress host{
			get;
			set;
		}
		
		/// <summary>
		/// 端口
		/// </summary>
		public int port{
			get;
			set;
		}
		
		/// <summary>
		/// 用户名
		/// </summary>
		public string username{
			get;
			set;
		}
		
		/// <summary>
		/// 密码
		/// </summary>
		public string pwd{
			get;
			set;
		}

        /// <summary>
        /// 版本
        /// </summary>
        public string verison{
        	get;
        	set;
        }

        /// <summary>
        /// 是否允许使用会话变量
        /// </summary>
        public bool AllowUserVariables{
        	get;
        	set;
        }
        
        private int _cacheCount = 10;
        
        /// <summary>
        /// 缓存链接数量
        /// </summary>
        public int cacheCount{
        	get{
        		return _cacheCount;
        	}
        	set{
        		_cacheCount = value;
        	}
        }
	}
}
using System;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Data;

namespace MrWu.DB {

    /// <summary>
    /// 存储过程查询键
    /// </summary>
    public class SqliteProdureParameter {
        public SQLiteCommand sqlitecommand {
            get;
            private set;
        }

        /// <summary>
        /// 错误信息
        /// </summary>
        public Exception exception {
            get;
            internal set;
        }

        /// <summary>
        /// 是否报错
        /// </summary>
        public bool isException {
            get {
                return exception != null;
            }
        }

        private SqliteProdureParameter() { }

        internal SqliteProdureParameter(SQLiteCommand sqlitecommand) {
            this.sqlitecommand = sqlitecommand;
        }

        /// <summary>
        /// 释放
        /// </summary>
        internal void Dispose() {
            sqlitecommand = null;
        }
    }

    /// <summary>
    /// 单独设置参数
    /// </summary>
    public class SqliteAloneParameter {

        /// <summary>
        /// 参数名称
        /// </summary>
        public string paramname {
            get;
            private set;
        }

        /// <summary>
        /// 参数值
        /// </summary>
        public object paramvalue {
            get;
            private set;
        }

        /// <summary>
        /// 参数类型
        /// </summary>
        public DbType type {
            get;
            private set;
        }

        /// <summary>
        /// 参数长度
        /// </summary>
        public int valuelength {
            get;
            private set;
        }

        /// <summary>
        /// sql语句单独设置参数
        /// </summary>
        /// <param name="paramname">参数名称</param>
        /// <param name="paramvalue">参数值</param>
        /// <param name="valuelength">参数值的长度</param>
        /// <param name="type">参数类型</param>
        public SqliteAloneParameter(string paramname, object paramvalue, int valuelength, DbType type = DbType.Binary) {
            this.paramname = paramname;
            this.paramvalue = paramvalue;
            this.valuelength = valuelength;
            this.type = type;
        }
    }

}