/// <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); }