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