/// <summary>
/// 更新记录
/// </summary>
/// <param name="model">Data.Model.DBConnection实体类</param>
public int Update(Data.Model.DBConnection model)
{
string sql = @"UPDATE DBConnection SET
Name=@Name,Type=@Type,ConnectionString=@ConnectionString,Note=@Note
WHERE ID=@ID" ;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Name", SqlDbType.VarChar, 500)
{
Value = model.Name
},
new SqlParameter("@Type", SqlDbType.VarChar, 500)
{
Value = model.Type
},
new SqlParameter("@ConnectionString", SqlDbType.VarChar, -1)
{
Value = model.ConnectionString
},
model.Note == null ? new SqlParameter("@Note", SqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new SqlParameter("@Note", SqlDbType.VarChar, -1)
{
Value = model.Note
},
new SqlParameter("@ID", SqlDbType.UniqueIdentifier, -1)
{
Value = model.ID
}
};
return(dbHelper.Execute(sql, parameters));
}
/// <summary>
/// 得到一个连接一个表一个字段的值
/// </summary>
/// <param name="linkID">连接ID</param>
/// <param name="table">表</param>
/// <param name="field">字段</param>
/// <param name="pkField">主键字段</param>
/// <param name="pkFieldValue">主键值</param>
/// <returns></returns>
private string getFieldValue_SqlServer(Data.Model.DBConnection conn, string table, string field, string pkField, string pkFieldValue)
{
string v = "";
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
Log.Add(err);
return("");
}
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", field, table, pkField, pkFieldValue);
using (SqlDataAdapter dap = new SqlDataAdapter(sql, sqlConn))
{
try
{
DataTable dt = new DataTable();
dap.Fill(dt);
if (dt.Rows.Count > 0)
{
v = dt.Rows[0][0].ToString();
}
}
catch (SqlException err)
{
Log.Add(err);
}
return(v);
}
}
}
/// <summary>
/// 添加记录
/// </summary>
/// <param name="model">Data.Model.DBConnection实体类</param>
/// <returns>操作所影响的行数</returns>
public int Add(Data.Model.DBConnection model)
{
string sql = @"INSERT INTO DBConnection
(ID,Name,Type,ConnectionString,Note)
VALUES(@ID,@Name,@Type,@ConnectionString,@Note)" ;
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ID", SqlDbType.UniqueIdentifier, -1)
{
Value = model.ID
},
new SqlParameter("@Name", SqlDbType.VarChar, 500)
{
Value = model.Name
},
new SqlParameter("@Type", SqlDbType.VarChar, 500)
{
Value = model.Type
},
new SqlParameter("@ConnectionString", SqlDbType.VarChar, -1)
{
Value = model.ConnectionString
},
model.Note == null ? new SqlParameter("@Note", SqlDbType.VarChar, -1)
{
Value = DBNull.Value
} : new SqlParameter("@Note", SqlDbType.VarChar, -1)
{
Value = model.Note
}
};
return(dbHelper.Execute(sql, parameters));
}
/// <summary>
/// 根据连接实体得到数据表
/// </summary>
/// <param name="linkID"></param>
/// <returns></returns>
public System.Data.DataTable GetDataTable(Data.Model.DBConnection dbconn, string sql)
{
if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
{
return(null);
}
DataTable dt = new DataTable();
switch (dbconn.Type)
{
case "SqlServer":
using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
using (SqlDataAdapter dap = new SqlDataAdapter(sql, conn))
{
dap.Fill(dt);
}
}
catch (SqlException ex)
{
Platform.Log.Add(ex);
}
}
break;
}
return(dt);
}
/// <summary>
/// 得到一个连接一个表所有字段
/// </summary>
/// <param name="conn"></param>
/// <param name="table"></param>
/// <returns></returns>
private Dictionary <string, string> getFields_SqlServer(Data.Model.DBConnection conn, string table)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
Log.Add(err);
return(new Dictionary <string, string>());
}
Dictionary <string, string> fields = new Dictionary <string, string>();
string sql = string.Format(@"SELECT a.name as f_name, b.value from
sys.syscolumns a LEFT JOIN sys.extended_properties b on a.id=b.major_id AND a.colid=b.minor_id AND b.name='MS_Description'
WHERE object_id('{0}')=a.id ORDER BY a.colid", table);
using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
{
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
fields.Add(dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1));
}
dr.Close();
return(fields);
}
}
}
/// <summary>
/// 得到一个连接一个表一个字段的值
/// </summary>
/// <param name="conn">连接ID</param>
/// <param name="table">表名</param>
/// <param name="field">字段名</param>
/// <param name="pkFieldValue">主键和值字典</param>
/// <returns></returns>
private string getFieldValue_SqlServer(Data.Model.DBConnection conn, string table, string field, Dictionary <string, string> pkFieldValue)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
Log.Add(err);
return("");
}
List <string> fields = new List <string>();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("select {0} from {1} where 1=1", field, table);
foreach (var pk in pkFieldValue)
{
sql.AppendFormat(" and {0}='{1}'", pk.Key, pk.Value);
}
using (SqlCommand sqlCmd = new SqlCommand(sql.ToString(), sqlConn))
{
SqlDataReader dr = sqlCmd.ExecuteReader();
string value = string.Empty;
if (dr.HasRows)
{
dr.Read();
value = dr.GetString(0);
}
dr.Close();
return(value);
}
}
}
/// <summary>
/// 得到一个连接所有表
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private List <string> getTables_SqlServer(Data.Model.DBConnection conn)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
Log.Add(err);
return(new List <string>());
}
List <string> tables = new List <string>();
string sql = "SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name";
using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
{
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
tables.Add(dr.GetString(0));
}
dr.Close();
return(tables);
}
}
}
/// <summary>
/// 更新
/// </summary>
public int Update(Data.Model.DBConnection model)
{
int i = dataDBConnection.Update(model);
ClearCache();
return(i);
}
/// <summary>
/// 测试一个sql条件合法性
/// </summary>
/// <param name="conn"></param>
/// <param name="where"></param>
/// <returns></returns>
private string testSql_SqlServer(Data.Model.DBConnection conn, string sql)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
return(err.Message);
}
using (SqlCommand cmd = new SqlCommand(sql, sqlConn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException err)
{
return(err.Message);
}
}
return("");
}
}
/// <summary>
/// 新增
/// </summary>
public int Add(Data.Model.DBConnection model)
{
int i = dataDBConnection.Add(model);
ClearCache();
return(i);
}
/// <summary>
/// 得到一个连接一个表所有字段
/// </summary>
/// <param name="conn"></param>
/// <param name="table"></param>
/// <returns></returns>
private List <string> getFields_SqlServer(Data.Model.DBConnection conn, string table)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
}
catch (SqlException err)
{
Log.Add(err);
return(new List <string>());
}
List <string> fields = new List <string>();
string sql = string.Format(@"select a.name as f_name,b.name as t_name,[length],a.isnullable as is_null from
sys.syscolumns a inner join sys.types b on b.user_type_id=a.xtype
where object_id('{0}')=id order by a.colid", table);
using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
{
SqlDataReader dr = sqlCmd.ExecuteReader();
while (dr.Read())
{
fields.Add(dr.GetString(0));
}
dr.Close();
return(fields);
}
}
}
public ActionResult Edit(FormCollection collection)
{
string editid = Request.QueryString["id"];
Business.Platform.DBConnection bdbConn = new Business.Platform.DBConnection();
Data.Model.DBConnection dbconn = null;
if (editid.IsGuid())
{
dbconn = bdbConn.Get(editid.ToGuid());
}
bool isAdd = !editid.IsGuid();
string oldXML = string.Empty;
if (dbconn == null)
{
dbconn = new Data.Model.DBConnection();
dbconn.ID = Guid.NewGuid();
}
else
{
oldXML = dbconn.Serialize();
}
if (collection != null)
{
string Name = Request.Form["Name"];
string LinkType = Request.Form["LinkType"];
string ConnStr = Request.Form["ConnStr"];
string Note = Request.Form["Note"];
dbconn.Name = Name.Trim();
dbconn.Type = LinkType;
dbconn.ConnectionString = ConnStr;
dbconn.Note = Note;
if (isAdd)
{
bdbConn.Add(dbconn);
Business.Platform.Log.Add("添加了应用程序库", dbconn.Serialize(), Business.Platform.Log.Types.角色应用);
ViewBag.Script = "alert('添加成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();";
}
else
{
bdbConn.Update(dbconn);
Business.Platform.Log.Add("修改了应用程序库", "", Business.Platform.Log.Types.角色应用, oldXML, dbconn.Serialize());
ViewBag.Script = "alert('修改成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();";
}
bdbConn.ClearCache();
}
ViewBag.TypeOptions = bdbConn.GetAllTypeOptions(dbconn.Type);
return(View(dbconn));
}
/// <summary>
/// 测试一个连接
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private string test_SqlServer(Data.Model.DBConnection conn)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
return("连接成功!");
}
catch (SqlException err)
{
return(err.Message);
}
}
}
/// <summary>
/// 根据连接实体得到连接
/// </summary>
/// <param name="linkID"></param>
/// <returns></returns>
public System.Data.IDbConnection GetConnection(Data.Model.DBConnection dbconn)
{
if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
{
return(null);
}
IDbConnection conn = null;
switch (dbconn.Type)
{
case "SqlServer":
conn = new SqlConnection(dbconn.ConnectionString);
break;
}
return(conn);
}
/// <summary>
/// 将DataRedar转换为List
/// </summary>
private List <Data.Model.DBConnection> DataReaderToList(SqlDataReader dataReader)
{
List <Data.Model.DBConnection> List = new List <Data.Model.DBConnection>();
Data.Model.DBConnection model = null;
while (dataReader.Read())
{
model = new Data.Model.DBConnection();
model.ID = dataReader.GetGuid(0);
model.Name = dataReader.GetString(1);
model.Type = dataReader.GetString(2);
model.ConnectionString = dataReader.GetString(3);
if (!dataReader.IsDBNull(4))
{
model.Note = dataReader.GetString(4);
}
List.Add(model);
}
return(List);
}
/// <summary>
/// 测试一个sql是否合法
/// </summary>
/// <param name="dbconn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public bool TestSql(Data.Model.DBConnection dbconn, string sql)
{
if (dbconn == null)
{
return(false);
}
switch (dbconn.Type)
{
case "SqlServer":
using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
}
catch
{
return(false);
}
using (SqlCommand cmd = new SqlCommand(sql.ReplaceSelectSql(), (SqlConnection)conn))
{
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
}
break;
}
return(false);
}
