public Dictionary <int, timewatchConnModel> GetDataBaseSqlConnDic(DbConn PubConn, int dbtype)
{
return(SqlHelper.Visit(ps =>
{
Dictionary <int, timewatchConnModel> connDic = new Dictionary <int, timewatchConnModel>();
ps.Add("dbtype", dbtype);
string sql = "select * from tb_database_config where dbtype=@dbtype";
DataTable dt = PubConn.SqlToDataTable(sql, ps.ToParameters());
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_database_config_model m = CreateModel(dr);
string sqlconn = ReplaceConnectStringTemplate(m);
timewatchConnModel model = new timewatchConnModel()
{
name = m.dblocalname,
connect = sqlconn
};
connDic.Add(m.id, model);
}
return connDic;
}
else
{
return connDic;
}
}));
}
public List <tb_database_config_model> GetList(DbConn PubConn, string keyword, int dbtype, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_database_config_model> model = new List <tb_database_config_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
StringBuilder sql = new StringBuilder();
sql.Append("select ROW_NUMBER() over(order by id desc) as rownum,* from tb_database_config ");
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere += " and ( dblocalname like '%'+@keyword+'%' or dbname like '%'+@keyword+'%' or dbserver like '%'+@keyword+'%' )";
}
if (dbtype != -1)
{
ps.Add("dbtype", dbtype);
sqlwhere += " and dbtype=@dbtype ";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_database_config " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return(ds);
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_database_config_model m = CreateModel(dr);
model.Add(m);
}
count = _count;
return(model);
}
/// <summary>
/// 根据模板替换连接字符串
/// </summary>
/// <param name="connectstringTemplate"></param>
/// <param name="o"></param>
/// <returns></returns>
public string ReplaceConnectStringTemplate(tb_database_config_model o)
{
if (o == null)
{
throw new Exception("找不到数据库连接分库配置");
}
string connectstringTempLate = "server={dbserver};Initial Catalog={dbname};User ID={dbuser};Password={dbpass};";
return(connectstringTempLate.Replace("{dbserver}", o.dbserver).Replace("{dbname}", o.dbname)
.Replace("{dbuser}", o.dbuser).Replace("{dbpass}", o.dbpass));
}
public string GetDataBaseSqlConn(DbConn PubConn, int dbtype)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("dbtype", dbtype);
string sql = "select * from tb_database_config where dbtype=@dbtype";
DataTable dt = PubConn.SqlToDataTable(sql, ps.ToParameters());
if (dt.Rows.Count > 0)
{
tb_database_config_model m = CreateModel(dt.Rows[0]);
string sqlconn = ReplaceConnectStringTemplate(m);
return sqlconn;
}
else
{
return "";
}
}));
}
public ActionResult Update(tb_database_config_model model)
{
tb_database_config_dal dal = new tb_database_config_dal();
try
{
using (DbConn PubConn = DbConfig.CreateConn(Config.PlatformManageConnectString))
{
PubConn.Open();
dal.Edit(PubConn, model);
return(RedirectToAction("index"));
}
}
catch (Exception ex)
{
ModelState.AddModelError("Error", ex.Message);
return(View(model));
}
}
public virtual tb_database_config_model CreateModel(DataRow dr)
{
var o = new tb_database_config_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//数据库本地昵称
if (dr.Table.Columns.Contains("dblocalname"))
{
o.dblocalname = dr["dblocalname"].Tostring();
}
//数据库服务器地址
if (dr.Table.Columns.Contains("dbserver"))
{
o.dbserver = dr["dbserver"].Tostring();
}
//数据库名称
if (dr.Table.Columns.Contains("dbname"))
{
o.dbname = dr["dbname"].Tostring();
}
//数据库用户名
if (dr.Table.Columns.Contains("dbuser"))
{
o.dbuser = dr["dbuser"].Tostring();
}
//数据库密码
if (dr.Table.Columns.Contains("dbpass"))
{
o.dbpass = dr["dbpass"].Tostring();
}
//数据库类型
if (dr.Table.Columns.Contains("dbtype"))
{
o.dbtype = dr["dbtype"].ToByte();
}
return(o);
}
public virtual bool Add(DbConn PubConn, tb_database_config_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//数据库本地昵称
new ProcedureParameter("@dblocalname", model.dblocalname),
//数据库服务器地址
new ProcedureParameter("@dbserver", model.dbserver),
//数据库名称
new ProcedureParameter("@dbname", model.dbname),
//数据库用户名
new ProcedureParameter("@dbuser", model.dbuser),
//数据库密码
new ProcedureParameter("@dbpass", model.dbpass),
//数据库类型
new ProcedureParameter("@dbtype", model.dbtype)
};
int rev = PubConn.ExecuteSql(@"insert into tb_database_config(dblocalname,dbserver,dbname,dbuser,dbpass,dbtype)
values(@dblocalname,@dbserver,@dbname,@dbuser,@dbpass,@dbtype)" , Par);
return(rev == 1);
}
public virtual bool Edit(DbConn PubConn, tb_database_config_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//数据库本地昵称
new ProcedureParameter("@dblocalname", model.dblocalname),
//数据库服务器地址
new ProcedureParameter("@dbserver", model.dbserver),
//数据库名称
new ProcedureParameter("@dbname", model.dbname),
//数据库用户名
new ProcedureParameter("@dbuser", model.dbuser),
//数据库密码
new ProcedureParameter("@dbpass", model.dbpass),
//数据库类型
new ProcedureParameter("@dbtype", model.dbtype)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_database_config set dblocalname=@dblocalname,dbserver=@dbserver,dbname=@dbname,dbuser=@dbuser,dbpass=@dbpass,dbtype=@dbtype where id=@id", Par);
return(rev == 1);
}
public List <string> GetDataBaseSqlConnList(DbConn PubConn, int dbtype)
{
return(SqlHelper.Visit(ps =>
{
List <string> connLisg = new List <string>();
ps.Add("dbtype", dbtype);
string sql = "select * from tb_database_config where dbtype=@dbtype";
DataTable dt = PubConn.SqlToDataTable(sql, ps.ToParameters());
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_database_config_model m = CreateModel(dr);
string sqlconn = ReplaceConnectStringTemplate(m);
connLisg.Add(sqlconn);
}
return connLisg;
}
else
{
return connLisg;
}
}));
}
