/// <summary> /// 得到一个连接所有表 /// </summary> /// <param name="conn"></param> /// <returns></returns> private List <string> getTables_SqlServer(MyCreek.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(MyCreek.Data.Model.DBConnection model) { int i = dataDBConnection.Update(model); ClearCache(); return(i); }
/// <summary> /// 添加记录 /// </summary> /// <param name="model">MyCreek.Data.Model.DBConnection实体类</param> /// <returns>操作所影响的行数</returns> public int Add(MyCreek.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> /// 得到一个连接一个表所有字段(Oracle) /// </summary> /// <param name="conn"></param> /// <param name="table"></param> /// <returns></returns> private Dictionary <string, string> getFields_Oracle(MyCreek.Data.Model.DBConnection conn, string table) { 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> /// <param name="table"></param> /// <returns></returns> private Dictionary <string, string> getFields_SqlServer(MyCreek.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="model">MyCreek.Data.Model.DBConnection实体类</param> public int Update(MyCreek.Data.Model.DBConnection model) { string sql = @"UPDATE DBConnection SET Name=:Name,Type=:Type,ConnectionString=:ConnectionString,Note=:Note WHERE ID=:ID" ; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":Name", OracleDbType.Varchar2, 500) { Value = model.Name }, new OracleParameter(":Type", OracleDbType.Varchar2, 500) { Value = model.Type }, new OracleParameter(":ConnectionString", OracleDbType.Clob) { Value = model.ConnectionString }, model.Note == null ? new OracleParameter(":Note", OracleDbType.Clob) { Value = DBNull.Value } : new OracleParameter(":Note", OracleDbType.Clob) { Value = model.Note }, new OracleParameter(":ID", OracleDbType.Varchar2, 40) { Value = model.ID } }; return(dbHelper.Execute(sql, parameters)); }
/// <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(MyCreek.Data.Model.DBConnection conn, string table, string field, Dictionary <string, string> pkFieldValue) { 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> /// 添加记录 /// </summary> /// <param name="model">MyCreek.Data.Model.DBConnection实体类</param> /// <returns>操作所影响的行数</returns> public int Add(MyCreek.Data.Model.DBConnection model) { string sql = @"INSERT INTO DBConnection (ID,Name,Type,ConnectionString,Note) VALUES(:ID,:Name,:Type,:ConnectionString,:Note)" ; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ID", OracleDbType.Varchar2, 40) { Value = model.ID }, new OracleParameter(":Name", OracleDbType.Varchar2, 500) { Value = model.Name }, new OracleParameter(":Type", OracleDbType.Varchar2, 500) { Value = model.Type }, new OracleParameter(":ConnectionString", OracleDbType.Clob) { Value = model.ConnectionString }, model.Note == null ? new OracleParameter(":Note", OracleDbType.Clob) { Value = DBNull.Value } : new OracleParameter(":Note", OracleDbType.Clob) { Value = model.Note } }; return(dbHelper.Execute(sql, parameters)); }
/// <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(MyCreek.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); } } }
/// <summary> /// 更新记录 /// </summary> /// <param name="model">MyCreek.Data.Model.DBConnection实体类</param> public int Update(MyCreek.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> /// 得到一个连接所有表(oracle) /// </summary> /// <param name="conn"></param> /// <returns></returns> private List <string> getTables_Oracle(MyCreek.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); } } }
/// <summary> /// 测试一个sql条件合法性(oracle) /// </summary> /// <param name="conn"></param> /// <param name="where"></param> /// <returns></returns> private string testSql_Oracle(MyCreek.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(""); } }
protected void Page_Load(object sender, EventArgs e) { string editid = Request.QueryString["id"]; MyCreek.Platform.DBConnection bdbConn = new MyCreek.Platform.DBConnection(); MyCreek.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 MyCreek.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); MyCreek.Platform.Log.Add("添加了应用程序库", dbconn.Serialize(), MyCreek.Platform.Log.Types.角色应用); Page.ClientScript.RegisterStartupScript(Page.GetType(), "ok", "alert('添加成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();", true); } else { bdbConn.Update(dbconn); MyCreek.Platform.Log.Add("修改了应用程序库", "", MyCreek.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); }
public ActionResult Edit(FormCollection collection) { string editid = Request.QueryString["id"]; MyCreek.Platform.DBConnection bdbConn = new MyCreek.Platform.DBConnection(); MyCreek.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 MyCreek.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); MyCreek.Platform.Log.Add("添加了应用程序库", dbconn.Serialize(), MyCreek.Platform.Log.Types.角色应用); ViewBag.Script = "alert('添加成功!');new RoadUI.Window().reloadOpener();new RoadUI.Window().close();"; } else { bdbConn.Update(dbconn); MyCreek.Platform.Log.Add("修改了应用程序库", "", MyCreek.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(MyCreek.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(MyCreek.Data.Model.DBConnection conn) { using (OracleConnection sqlConn = new OracleConnection(conn.ConnectionString)) { try { sqlConn.Open(); return("连接成功!"); } catch (OracleException err) { return(err.Message); } } }
/// <summary> /// 将DataRedar转换为List /// </summary> private List <MyCreek.Data.Model.DBConnection> DataReaderToList(OracleDataReader dataReader) { List <MyCreek.Data.Model.DBConnection> List = new List <MyCreek.Data.Model.DBConnection>(); MyCreek.Data.Model.DBConnection model = null; while (dataReader.Read()) { model = new MyCreek.Data.Model.DBConnection(); model.ID = dataReader.GetString(0).ToGuid(); 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> /// 根据连接实体得到连接 /// </summary> /// <param name="linkID"></param> /// <returns></returns> public System.Data.IDbConnection GetConnection(MyCreek.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> /// 根据连接实体得到数据表 /// </summary> /// <param name="linkID"></param> /// <returns></returns> public DataTable GetDataTable(MyCreek.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(MyCreek.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); }