同步存储过程命令
最近写了一个自动执行的程序,运行每次跨数据库或者服务器同步存储过程或者函数的方法。
下面直接上代码,其中只需要注意一点: 那就是AdoNet 不能执行带Go的语句,所以,想把所有的命令一起执行显然行不通,只能分开一条一条执行。
static string Current_Constr = "xxxx"; static string TargerConstr = "xxx"; public static void SyncUp_DataBase_Schema() { string type = "('v','fn','tn','p')"; string sqlCmd = "select object_id,definition,name,xtype FROM sys.sql_modules inner join sysobjects on sys.sql_modules.object_id = sysobjects.id where xtype in {0}"; string sql = string.Format(sqlCmd, type); var dicts = GetSqlString(sql); ExecuteSql(dicts); } private static void ExecuteSql(Dictionary<string, string> dicts) { using (SqlConnection conn = new SqlConnection(TargerConstr)) { conn.Open(); SqlTransaction tran = conn.BeginTransaction(); SqlCommand cmd = new SqlCommand(); cmd.Transaction = tran; cmd.Connection = conn; try { foreach (var item in dicts) { cmd.CommandText = item.Key; cmd.ExecuteNonQuery(); cmd.CommandText = item.Value; cmd.ExecuteNonQuery(); } } catch { tran.Rollback(); } finally { tran.Commit(); tran.Dispose(); } } } private static Dictionary<string, string> GetSqlString(string sql) { Dictionary<string, string> dicts = new Dictionary<string, string>(); DataTable dt = new DataTable(); using (SqlDataAdapter da = new SqlDataAdapter(sql, Current_Constr)) { da.Fill(dt); } foreach (DataRow dr in dt.Rows) { string type = "function"; string xtype = dr[3].ToString().ToLower().Trim(); string str = "if object_id(N'{0}') is not null drop {1} {0} "; if (xtype == "v") { type = "view"; } else if(xtype=="p"){ type = "proc"; } string dropSql = string.Format(str, dr[2].ToString(), type); dicts.Add(dropSql,dr[1].ToString()); } return dicts; }
posted on 2014-07-16 15:59 Richard__Lee 阅读(380) 评论(0) 编辑 收藏 举报