实例介绍
【实例简介】
【实例截图】
【核心代码】
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace HaoCodeBuilder.Data.SqlServer { public class DataBase : IData.IDatabase { /// <summary> /// 测试数据库连接 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="errMessage">错误信息</param> /// <returns></returns> public bool TestDatabaseConnnection(string serverID, out string errMessage) { SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID)); try { conn.Open(); errMessage = string.Empty; return true; } catch (SqlException err) { errMessage = err.Message; return false; } finally { conn.Dispose(); } } /// <summary> /// 得到服务器所有数据库 /// </summary> /// <param name="connectionString"></param> /// <returns></returns> public List<string> GetDatabaseList(string serverID) { List<string> dbList = new List<string>(); string connString = Common.Config.GetConnectionString(serverID); using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("select name from sysdatabases", conn)) { SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { dbList.Add(dr.GetString(0)); } dr.Close(); dr.Dispose(); } } return dbList; } /// <summary> /// 得到数据库所有表 /// </summary> /// <param name="serverID"></param> /// <returns></returns> public List<Model.Tables> GetTables(string serverID, string dbName) { List<Model.Tables> tblList = new List<Model.Tables>(); using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT name FROM sysobjects WHERE xtype='u' order by name", conn)) { SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { tblList.Add(new Model.Tables() { Name = dr.GetString(0) }); } dr.Close(); dr.Dispose(); } } return tblList; } /// <summary> /// 得到数据库所有视图 /// </summary> /// <param name="serverID"></param> /// <returns></returns> public List<Model.Views> GetViews(string serverID, string dbName) { List<Model.Views> viewList = new List<Model.Views>(); using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); using (SqlCommand cmd = new SqlCommand("select name from sys.views order by name", conn)) { SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { viewList.Add(new Model.Views() { Name = dr.GetString(0) }); } dr.Close(); dr.Dispose(); } } return viewList; } /// <summary> /// 得到一个表中所有字段 /// </summary> /// <param name="serverID"></param> /// <param name="dbName"></param> /// <param name="tableName"></param> /// <returns></returns> public List<Model.Fields> GetFields(string serverID, string dbName, string tableName) { List<Model.Fields> fieldsList = new List<Model.Fields>(); Model.Servers server = Common.Config.GetServer(serverID); if (server == null) { return fieldsList; } using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); string sql = server.Type == Model.DatabaseType.SqlServer2000 ? string.Format(@"SELECT name=a.name,isidentity=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end,isprimarykey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,type=b.name,bbyte=a.length,length=COLUMNPROPERTY(a.id,a.name,'PRECISION'), dec=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),isnull=case when a.isnullable=1 then 1 else 0 end, isdefault=isnull(e.text,''),note=isnull(g.[value],'')FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sysproperties f on d.id=f.id and f.smallid=0 where d.name='{0}' order by a.id,a.colorder", tableName) : 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", tableName); using (SqlCommand cmd = new SqlCommand(sql, conn)) { SqlDataReader dr = cmd.ExecuteReader(); if (server.Type != Model.DatabaseType.SqlServer2000) { while (dr.Read()) { fieldsList.Add(new Model.Fields() { Name = dr.GetString(0), Type = dr.GetString(1), Length = GetFieldLength(dr.GetString(1), dr.GetInt16(2)), IsNull = 1 == dr.GetInt32(3), IsPrimaryKey = IsPrimaryKey(serverID, dbName, tableName, dr.GetString(0)), IsIdentity = IsIdentity(serverID, dbName, tableName, dr.GetString(0)), DotNetType = GetFieldType(dr.GetString(1), 1 == dr.GetInt32(3)), DotNetSqlType = GetFieldSqlType(dr.GetString(1)), Note = GetFieldNote(serverID, dbName, tableName, dr.GetString(0)) }); } } else { while (dr.Read()) { fieldsList.Add(new Model.Fields() { Name = dr["name"].ToString(), Type = dr["type"].ToString(), Length = GetFieldLength(dr["type"].ToString(), dr["length"].ToString().ToInt()), IsNull = "1" == dr["isnull"].ToString(), IsPrimaryKey = "1" == dr["isprimarykey"].ToString(), IsIdentity = "1" == dr["isidentity"].ToString(), DotNetType = GetFieldType(dr["type"].ToString(), "1" == dr["isnull"].ToString()), DotNetSqlType = GetFieldSqlType(dr["type"].ToString()), Note = dr["note"].ToString() }); } } dr.Close(); dr.Dispose(); } } return fieldsList; } /// <summary> /// 判断一个表的某列是否为主键 /// </summary> /// <param name="tableName"></param> /// <param name="filedName"></param> /// <returns></returns> private bool IsPrimaryKey(string serverID, string dbName, string tableName, string fieldName) { using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "sp_pkeys"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@table_name", tableName)); using (SqlDataAdapter dap = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); dap.Fill(dt); return dt.Select("COLUMN_NAME='" fieldName "'").Length > 0; } } } } /// <summary> /// 判断一个表的某列是否为自增列 /// </summary> /// <param name="tableName"></param> /// <param name="filedName"></param> /// <returns></returns> private bool IsIdentity(string serverID, string dbName, string tableName, string fieldName) { using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = "select COLUMNPROPERTY(object_id('" tableName "'),'" fieldName "','IsIdentity')"; return "1" == cmd.ExecuteScalar().ToString(); } } } /// <summary> /// 得到一个字段的备注说明 /// </summary> /// <param name="tableName"></param> /// <param name="filedName"></param> /// <returns></returns> private string GetFieldNote(string serverID, string dbName, string tableName, string fieldName) { using (SqlConnection conn = new SqlConnection(Common.Config.GetConnectionString(serverID, dbName))) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = @"select value from sys.extended_properties a left join sys.syscolumns b on a.major_id=b.id and a.minor_id=b.colid where a.name='MS_Description' and object_id('" tableName "')=a.major_id and b.name='" fieldName "'"; object obj = cmd.ExecuteScalar(); return obj == null ? string.Empty : obj.ToString(); } } } /// <summary> /// 得到字段的常规类型 /// </summary> /// <param name="tName"></param> /// <param name="isNull"></param> /// <returns></returns> private string GetFieldType(string typeName, bool isNull) { string r = string.Empty; switch (typeName.Trim().ToLower()) { case "varchar": case "nvarchar": case "text": case "ntext": case "char": case "nchar": case "xml": r = "string"; break; case "uniqueidentifier": r = isNull ? "Guid?" : "Guid"; break; case "bit": r = isNull ? "bool?" : "bool"; break; case "bigint": r = isNull ? "long?" : "long"; break; case "int": r = isNull ? "int?" : "int"; break; case "tinyint": r = isNull ? "byte?" : "byte"; break; case "smallint": r = isNull ? "short?" : "short"; break; case "smallmoney": case "decimal": case "numeric": case "money": r = isNull ? "decimal?" : "decimal"; break; case "real": r = isNull ? "float?" : "float"; break; case "float": r = isNull ? "double?" : "double"; break; case "date": case "datetime": case "datetime2": case "smalldatetime": case "datetimeoffset": r = isNull ? "DateTime?" : "DateTime"; break; } return r; } /// <summary> /// 得到字段的SqlDbType字符串 /// </summary> /// <param name="typeName"></param> /// <returns></returns> private string GetFieldSqlType(string typeName) { string r = string.Empty; switch (typeName) { case "varchar": r = "SqlDbType.VarChar"; break; case "nvarchar": r = "SqlDbType.NVarChar"; break; case "text": r = "SqlDbType.Text"; break; case "ntext": r = "SqlDbType.NText"; break; case "char": r = "SqlDbType.Char"; break; case "nchar": r = "SqlDbType.VarChar"; break; case "uniqueidentifier": r = "SqlDbType.UniqueIdentifier"; break; case "bigint": r = "SqlDbType.BigInt"; break; case "bit": r = "SqlDbType.Bit"; break; case "int": r = "SqlDbType.Int"; break; case "tinyint": r = "SqlDbType.TinyInt"; break; case "smallint": r = "SqlDbType.SmallInt"; break; case "smallmoney": r = "SqlDbType.SmallMoney"; break; case "numeric": case "decimal": r = "SqlDbType.Decimal"; break; case "float": r = "SqlDbType.Float"; break; case "money": r = "SqlDbType.Money"; break; case "real": r = "SqlDbType.Real"; break; case "datetime": r = "SqlDbType.DateTime"; break; case "datetime2": r = "SqlDbType.DateTime2"; break; case "smalldatetime": r = "SqlDbType.SmallDateTime"; break; case "date": r = "SqlDbType.Date"; break; case "datetimeoffset": r = "SqlDbType.DateTimeOffset"; break; } return r; } /// <summary> /// 得到字段的参数长度 /// </summary> /// <param name="typeName"></param> /// <param name="length"></param> /// <returns></returns> private int GetFieldLength(string typeName, int length) { int r = -1; switch (typeName) { case "varchar": r = length == -1 ? -1 : length; break; case "nvarchar": r = length == -1 ? -1 : length; break; case "numeric": case "decimal": case "datetime": case "smalldatetime": r = length; break; } return r; } } }
好例子网口号:伸出你的我的手 — 分享!
相关软件
小贴士
感谢您为本站写下的评论,您的评论对其它用户来说具有重要的参考价值,所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字,对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙,所以请不要反馈意义不大的重复字符,也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明,或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦,又没人会搭理,于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享,对搜索内容的合法性不具有预见性、识别性、控制性,仅供学习研究,请务必在下载后24小时内给予删除,不得用于其他任何用途,否则后果自负。基于互联网的特殊性,平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查;无论平台是否已进行审查,用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场,基于网友分享,根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定,若资源存在侵权或相关问题请联系本站客服人员,点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论