/// <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(FoWoSoft.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">FoWoSoft.Data.Model.DBConnection实体类</param>
/// <returns>操作所影响的行数</returns>
public int Add(FoWoSoft.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="conn">连接ID</param>
/// <param name="table">表名</param>
/// <param name="field">字段名</param>
/// <param name="pkFieldValue">主键和值字典</param>
/// <returns></returns>
private string getFieldValue_SqlServer(FoWoSoft.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="model">FoWoSoft.Data.Model.DBConnection实体类</param>
public int Update(FoWoSoft.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="conn"></param>
/// <param name="table"></param>
/// <returns></returns>
private Dictionary <string, string> getFields_SqlServer(FoWoSoft.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>
/// 得到一个连接一个表所有字段(Oracle)
/// </summary>
/// <param name="conn"></param>
/// <param name="table"></param>
/// <returns></returns>
private Dictionary <string, string> getFields_Oracle(FoWoSoft.Data.Model.DBConnection conn, string table)
{
return(null);
//using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// sqlConn.Open();
// }
// catch (OracleException err)
// {
// Log.Add(err);
// return new Dictionary<string, string>();
// }
// Dictionary<string, string> fields = new Dictionary<string, string>();
// string sql = string.Format("select COLUMN_NAME,COMMENTS from user_col_comments where TABLE_NAME='{0}'", table);
// using (OracleCommand sqlCmd = new OracleCommand(sql, sqlConn))
// {
// OracleDataReader 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"></param>
/// <returns></returns>
private List <string> getTables_SqlServer(FoWoSoft.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>
/// 得到一个连接所有表(oracle)
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private List <string> getTables_Oracle(FoWoSoft.Data.Model.DBConnection conn)
{
//using (OracleConnection oraConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// oraConn.Open();
// }
// catch (SqlException err)
// {
// Log.Add(err);
// return new List<string>();
// }
// List<string> tables = new List<string>();
// string sql = "select * from tab where instr(tname,'$',1,1)=0";
// using (OracleCommand sqlCmd = new OracleCommand(sql, oraConn))
// {
// OracleDataReader dr = sqlCmd.ExecuteReader();
// while (dr.Read())
// {
// tables.Add(dr.GetString(0));
// }
// dr.Close();
// return tables;
// }
//}
return(null);
}
/// <summary>
/// 测试一个sql条件合法性(oracle)
/// </summary>
/// <param name="conn"></param>
/// <param name="where"></param>
/// <returns></returns>
private string testSql_Oracle(FoWoSoft.Data.Model.DBConnection conn, string sql)
{
//using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// sqlConn.Open();
// }
// catch (OracleException err)
// {
// return err.Message;
// }
// using (OracleCommand cmd = new OracleCommand(sql, sqlConn))
// {
// try
// {
// cmd.ExecuteNonQuery();
// }
// catch (OracleException err)
// {
// return err.Message;
// }
// }
return("");
//}
}
/// <summary>
/// 更新
/// </summary>
public int Update(FoWoSoft.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(FoWoSoft.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>
/// 将DataRedar转换为List
/// </summary>
private List <FoWoSoft.Data.Model.DBConnection> DataReaderToList(SqlDataReader dataReader)
{
List <FoWoSoft.Data.Model.DBConnection> List = new List <FoWoSoft.Data.Model.DBConnection>();
FoWoSoft.Data.Model.DBConnection model = null;
while (dataReader.Read())
{
model = new FoWoSoft.Data.Model.DBConnection();
model.ID = dataReader.GetGuid(0);
model.Name = dataReader.GetString(1);
model.Type = dataReader.GetString(2);
// if (model.Name == "平台连接") model.ConnectionString = ConfigurationManager.ConnectionStrings["PlatformConnection"].ToString();
// else
model.ConnectionString = dataReader.GetString(3);
if (!dataReader.IsDBNull(4))
{
model.Note = dataReader.GetString(4);
}
List.Add(model);
}
//var connstring = ConfigurationManager.ConnectionStrings;
//foreach (System.Configuration.ConnectionStringSettings item in connstring)
//{
// List.Add(new FoWoSoft.Data.Model.DBConnection()
// {
// ConnectionString=item.ConnectionString ,
// Name=item.Name,
// Type=item.ProviderName,
// });
//}
return(List);
}
protected void Page_Load(object sender, EventArgs e)
{
string editid = Request.QueryString["id"];
FoWoSoft.Platform.DBConnection bdbConn = new FoWoSoft.Platform.DBConnection();
FoWoSoft.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 FoWoSoft.Data.Model.DBConnection();
dbconn.ID = Guid.NewGuid();
}
else
{
oldXML = dbconn.Serialize();
}
if (IsPostBack)
{
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);
FoWoSoft.Platform.Log.Add("添加了应用程序库", dbconn.Serialize(), FoWoSoft.Platform.Log.Types.角色应用);
Page.ClientScript.RegisterStartupScript(Page.GetType(), "ok", "alert('添加成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();", true);
}
else
{
bdbConn.Update(dbconn);
FoWoSoft.Platform.Log.Add("修改了应用程序库", "", FoWoSoft.Platform.Log.Types.角色应用, oldXML, dbconn.Serialize());
Page.ClientScript.RegisterStartupScript(Page.GetType(), "ok", "alert('修改成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();", true);
}
bdbConn.ClearCache();
}
if (dbconn != null)
{
this.Name.Value = dbconn.Name;
this.ConnStr.Value = dbconn.ConnectionString;
this.Note.Value = dbconn.Note;
}
this.TypeOptions.Text = bdbConn.GetAllTypeOptions(dbconn.Type);
}
/// <summary>
/// 测试一个连接
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private string test_SqlServer(FoWoSoft.Data.Model.DBConnection conn)
{
using (SqlConnection sqlConn = new SqlConnection(conn.ConnectionString))
{
try
{
sqlConn.Open();
return("连接成功!");
}
catch (SqlException err)
{
return(err.Message);
}
}
}
/// <summary>
/// 测试一个连接(oracle)
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private string test_Oracle(FoWoSoft.Data.Model.DBConnection conn)
{
//using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// sqlConn.Open();
// return "连接成功!";
// }
// catch (OracleException err)
// {
// return err.Message;
// }
//}
return("");
}
/// <summary>
/// 根据连接实体得到连接
/// </summary>
/// <param name="linkID"></param>
/// <returns></returns>
public System.Data.IDbConnection GetConnection(FoWoSoft.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;
case "Oracle":
//conn = new OracleConnection(dbconn.ConnectionString);
break;
}
return(conn);
}
/// <summary>
/// 得到一个连接一个表一个字段的值(Oracle)
/// </summary>
/// <param name="conn">连接ID</param>
/// <param name="table">表名</param>
/// <param name="field">字段名</param>
/// <param name="pkFieldValue">主键和值字典</param>
/// <returns></returns>
private string getFieldValue_Oracle(FoWoSoft.Data.Model.DBConnection conn, string table, string field, Dictionary <string, string> pkFieldValue)
{
return(null);
//using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// sqlConn.Open();
// }
// catch (OracleException 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 (OracleCommand sqlCmd = new OracleCommand(sql.ToString(), sqlConn))
// {
// OracleDataReader dr = sqlCmd.ExecuteReader();
// string value = string.Empty;
// if (dr.HasRows)
// {
// dr.Read();
// value = dr.GetString(0);
// }
// dr.Close();
// return value;
// }
//}
}
/// <summary>
/// 得到一个连接一个表一个字段的值(Oracle)
/// </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_Oracle(FoWoSoft.Data.Model.DBConnection conn, string table, string field, string pkField, string pkFieldValue)
{
string v = "";
//using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString))
//{
// try
// {
// sqlConn.Open();
// }
// catch (OracleException err)
// {
// Log.Add(err);
// return "";
// }
// string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", field, table, pkField, pkFieldValue);
// using (OracleDataAdapter dap = new OracleDataAdapter(sql, sqlConn))
// {
// try
// {
// DataTable dt = new DataTable();
// dap.Fill(dt);
// if (dt.Rows.Count > 0)
// {
// v = dt.Rows[0][0].ToString();
// }
// }
// catch (OracleException err)
// {
// Log.Add(err);
// }
// return v;
// }
//}
return(v);
}
/// <summary>
/// 根据连接实体得到数据表
/// </summary>
/// <param name="linkID"></param>
/// <returns></returns>
public DataTable GetDataTable(FoWoSoft.Data.Model.DBConnection dbconn, string sql, IDataParameter[] parameterArray = null)
{
if (dbconn == null || dbconn.Type.IsNullOrEmpty() || dbconn.ConnectionString.IsNullOrEmpty())
{
return(null);
}
DataTable dt = new DataTable();
switch (dbconn.Type)
{
#region SqlServer
case "SqlServer":
using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (parameterArray != null && parameterArray.Length > 0)
{
cmd.Parameters.AddRange((SqlParameter[])parameterArray);
}
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
dap.Fill(dt);
}
}
}
catch (SqlException ex)
{
Platform.Log.Add(ex);
}
}
break;
#endregion
#region Oracle
//case "Oracle":
// using (OracleConnection conn = new OracleConnection(dbconn.ConnectionString))
// {
// try
// {
// conn.Open();
// using (OracleCommand cmd = new OracleCommand(sql, conn))
// {
// if (parameterArray != null && parameterArray.Length > 0)
// {
// cmd.Parameters.AddRange((OracleParameter[])parameterArray);
// }
// using (OracleDataAdapter dap = new OracleDataAdapter(cmd))
// {
// dap.Fill(dt);
// }
// }
// }
// catch (OracleException ex)
// {
// Platform.Log.Add(ex);
// }
// }
// break;
#endregion
}
return(dt);
}
/// <summary>
/// 测试一个sql是否合法
/// </summary>
/// <param name="dbconn"></param>
/// <param name="sql"></param>
/// <returns></returns>
public bool TestSql(FoWoSoft.Data.Model.DBConnection dbconn, string sql)
{
if (dbconn == null)
{
return(false);
}
switch (dbconn.Type)
{
#region SqlServer
case "SqlServer":
using (SqlConnection conn = new SqlConnection(dbconn.ConnectionString))
{
try
{
conn.Open();
}
catch
{
return(false);
}
using (SqlCommand cmd = new SqlCommand(sql.ReplaceSelectSql(), conn))
{
try
{
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
}
#endregion
#region Oracle
// case "Oracle":
//using (OracleConnection conn = new OracleConnection(dbconn.ConnectionString))
//{
// try
// {
// conn.Open();
// }
// catch
// {
// return false;
// }
// using (OracleCommand cmd = new OracleCommand(sql.ReplaceSelectSql(), conn))
// {
// try
// {
// cmd.ExecuteNonQuery();
// return true;
// }
// catch
// {
// return false;
// }
// }
//}
#endregion
}
return(false);
}
