private int ExecuteSqlTran2(DBConnect connect, List <SqlObject> sqlobj)
{
switch (connect.Type)
{
case "sqlserver":
{
var dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
return(dbHelperMySql.ExecuteSqlTran2(sqlobj.Select(
m => new SqlItem <SqlParameter>(m.Sql, m.Parameters.Select(
p => new SqlParameter(p.Name, SqlDbType.NVarChar)
{
Value = p.Value
}
).ToArray())
).ToList()));
}
break;
default:
{
var dbHelperMySql = new DbHelperMySql(connect.ConnectString);
return(dbHelperMySql.ExecuteSqlTran2(sqlobj.Select(
m => new SqlItem(m.Sql, m.Parameters.Select(
p => new MySqlParameter(p.Name, MySqlDbType.VarChar)
{
Value = p.Value
}
).ToArray())
).ToList()));
}
break;
}
return(0);
}
/// <summary>
/// 获取数据库表列表
/// </summary>
/// <returns></returns>
public ResponseMessage Tables(long ProjectID, int connectid, string databasename)
{
// 判断是否有权限
var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);
if (userproject == null)
{
return(new ResponseMessage(MessageResult.Error, "项目不存在"));
}
var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);
if (connect == null)
{
return(new ResponseMessage(MessageResult.Error, "连接不存在"));
//return Content(Result(1, "连接不存在", null).ToString());
}
var list = new List <string>();
switch (connect.Type)
{
case "sqlserver":
{
var connectstring = Regex.Replace(connect.ConnectString, @"initial catalog=([^;]*)", "initial catalog=" + databasename);
DbHelperSqlServer dbHelperMySql = new DbHelperSqlServer(connectstring);
var sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name;";
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
list.Add(_row["name"].ToString());
}
}
break;
default:
{
var sql = string.Format(
"select table_name from information_schema.tables where table_schema='{0}' and table_type='base table';", databasename);
DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
list.Add(_row["table_name"].ToString());
}
}
break;
}
return(new ResponseMessage(MessageResult.Success, "成功", list));
//return Content(JsonConvert.SerializeObject(list));
// return Json(list, JsonRequestBehavior.AllowGet);
}
///// <summary>
///// 获取数据库列表
///// </summary>
///// <param name="connectid"></param>
///// <returns></returns>
//public ResponseMessage Connects(long ProjectID)
//{
// var connect = GetConnectList(ProjectID);
// return new ResponseMessage(MessageResult.Success, "成功");
//}
/// <summary>
/// 获取数据库列表
/// </summary>
/// <param name="connectid"></param>
/// <returns></returns>
public ResponseMessage Databases(long ProjectID, int connectid)
{
// 判断是否有权限
var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);
if (userproject == null)
{
return(new ResponseMessage(MessageResult.Error, "项目不存在"));
}
var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);
if (connect == null)
{
return(new ResponseMessage(MessageResult.Error, "连接不存在"));
}
var list = new List <string>();
switch (connect.Type)
{
case "sqlserver":
{
DbHelperSqlServer dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
var sql = "Select Name FROM Master.dbo.SysDatabases orDER BY Name ;";
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
list.Add(_row[0].ToString());
}
}
break;
default:
{
DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
var sql = "show databases;";
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
list.Add(_row[0].ToString());
}
}
break;
}
return(new ResponseMessage(MessageResult.Success, "成功", list));
}
private DataSet Query(DBConnect connect, string sql, List <Parameter> Parameters)
{
switch (connect.Type)
{
case "sqlserver":
{
var dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
return(dbHelperMySql.Query(sql, Parameters.Select(p => new SqlParameter(p.Name, SqlDbType.NVarChar)
{
Value = p.Value
}).ToArray()));
}
default:
{
var dbHelperMySql = new DbHelperMySql(connect.ConnectString);
return(dbHelperMySql.Query(sql, Parameters.Select(p => new MySqlParameter(p.Name, MySqlDbType.VarChar)
{
Value = p.Value
}).ToArray()));
}
}
}
/// <summary>
/// 获取字段列表
/// </summary>
/// <param name="connectid"></param>
/// <param name="databasename"></param>
/// <param name="tablename"></param>
/// <returns></returns>
public ResponseMessage Columns(long ProjectID, int connectid, string databasename, string tablename)
{
// 判断是否有权限
var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);
if (userproject == null)
{
return(new ResponseMessage(MessageResult.Error, "项目不存在"));
}
var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);
if (connect == null)
{
return(new ResponseMessage(MessageResult.Error, "连接不存在"));
}
var list = new List <object>();
switch (connect.Type)
{
case "sqlserver":
{
var connectstring = Regex.Replace(connect.ConnectString, @"initial catalog=([^;]*)", "initial catalog=" + databasename);
var sql = string.Format(@"
select sc.name,st.name type from syscolumns sc,systypes st where sc.xusertype=st.xusertype and sc.id in(select id from sysobjects where xtype='U' and name='{0}');
EXEC sp_pkeys @table_name='{0}';
", tablename);
// var sql = string.Format("Select name FROM SysColumns Where id=Object_Id('{0}');", tablename, databasename);
var dbHelperMySql = new DbHelperSqlServer(connectstring);
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
var ispk = false;
foreach (DataRow pkrow in ds.Tables[1].Rows)
{
if (pkrow["column_name"].ToString() == _row["name"].ToString())
{
ispk = true;
}
}
list.Add(new
{
column_name = _row["name"].ToString(),
data_type = _row["type"].ToString(),
primary_key = ispk,
display = true
});
}
}
break;
default:
{
var sql =
string.Format("select * from information_schema.columns where table_schema='{1}' and table_name='{0}';", tablename, databasename);
DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
var ds = dbHelperMySql.Query(sql);
foreach (DataRow _row in ds.Tables[0].Rows)
{
list.Add(new
{
column_name = _row["column_name"].ToString(),
data_type = _row["data_type"].ToString(),
primary_key = _row["column_key"].ToString().ToUpper() == "PRI",
display = true
});
}
}
break;
}
return(new ResponseMessage(MessageResult.Success, "成功", list));
// return Json(list, JsonRequestBehavior.AllowGet);
}