您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > 简单三层架构数据库设计(基于DALParam)
简单三层架构数据库设计(基于DALParam)如果原项目中有解决方案如下,直接点击右键添加类项目。如果看不到上面的解决方案名称,只有项目名称如下:则可添加一个新建一个空的解决方案如下:然后在里面添加不同解决方案文件夹,然后添加现有项目,可以把原项目加进来。一、添加三层架构项目鼠标右键点击解决方案名称,添加新的类项目,如下:添加BLL类项目添加DAL类项目添加DBUtility类项目,数据访问类添加实体类项目二、添加具体类的实现1、添加数据访问类代码如下:usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Collections;namespaceRC101ZhangSan.DBUtility{///summary///数据访问抽象基础类///Copyright(C)Maticsoft////summarypublicabstractclassDbHelperSQL{//数据库连接字符串(web.config来配置),多数据库可使用DbHelperSQLP来实现.//publicstaticstringconnectionString=//ConfigurationManager.AppSettings[ConnectionString]//PubConstant.ConnectionString;protectedstaticstringconnectionString=DataSource=.\\SQLEXPRESS;InitialCatalog=qqData;IntegratedSecurity=SSPI;publicDbHelperSQL(){}#region公用方法///summary///判断是否存在某表的某个字段////summary///paramname=tableName表名称/param///paramname=columnName列名称/param///returns是否存在/returnspublicstaticboolColumnExists(stringtableName,stringcolumnName){stringsql=selectcount(1)fromsyscolumnswhere[id]=object_id('+tableName+')and[name]='+columnName+';objectres=GetSingle(sql);if(res==null){returnfalse;}returnConvert.ToInt32(res)0;}publicstaticintGetMaxID(stringFieldName,stringTableName){stringstrsql=selectmax(+FieldName+)+1from+TableName;objectobj=GetSingle(strsql);if(obj==null){return1;}else{returnint.Parse(obj.ToString());}}publicstaticboolExists(stringstrSql){objectobj=GetSingle(strSql);intcmdresult;if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))){cmdresult=0;}else{cmdresult=int.Parse(obj.ToString());//也可能=0}if(cmdresult==0){returnfalse;}else{returntrue;}}///summary///表是否存在////summary///paramname=TableName/param///returns/returnspublicstaticboolTabExists(stringTableName){stringstrsql=selectcount(*)fromsysobjectswhereid=object_id(N'[+TableName+]')andOBJECTPROPERTY(id,N'IsUserTable')=1;//stringstrsql=SELECTcount(*)FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[+TableName+]')ANDtypein(N'U');objectobj=GetSingle(strsql);intcmdresult;if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))){cmdresult=0;}else{cmdresult=int.Parse(obj.ToString());}if(cmdresult==0){returnfalse;}else{returntrue;}}publicstaticboolExists(stringstrSql,paramsSqlParameter[]cmdParms){objectobj=GetSingle(strSql,cmdParms);intcmdresult;if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))){cmdresult=0;}else{cmdresult=int.Parse(obj.ToString());}if(cmdresult==0){returnfalse;}else{returntrue;}}#endregion#region执行简单SQL语句///summary///执行SQL语句,返回影响的记录数////summary///paramname=SQLStringSQL语句/param///returns影响的记录数/returnspublicstaticintExecuteSql(stringSQLString){using(SqlConnectionconnection=newSqlConnection(connectionString)){using(SqlCommandcmd=newSqlCommand(SQLString,connection)){try{connection.Open();introws=cmd.ExecuteNonQuery();returnrows;}catch(System.Data.SqlClient.SqlExceptione){connection.Close();throwe;}}}}publicstaticintExecuteSqlByTime(stringSQLString,intTimes){using(SqlConnectionconnection=newSqlConnection(connectionString)){using(SqlCommandcmd=newSqlCommand(SQLString,connection)){try{connection.Open();cmd.CommandTimeout=Times;introws=cmd.ExecuteNonQuery();returnrows;}catch(System.Data.SqlClient.SqlExceptione){connection.Close();throwe;}}}}///summary///执行Sql和Oracle滴混合事务////summary///paramname=listSQL命令行列表/param///paramname=oracleCmdSqlListOracle命令行列表/param///returns执行结果0-由于SQL造成事务失败-1由于Oracle造成事务失败1-整体事务执行成功/returns//publicstaticintExecuteSqlTran(ListCommandInfolist,ListCommandInfooracleCmdSqlList)//{//using(SqlConnectionconn=newSqlConnection(connectionString))//{//conn.Open();//SqlCommandcmd=newSqlCommand();//cmd.Connection=conn;//SqlTransactiontx=conn.BeginTransaction();//cmd.Transaction=tx;//try//{//foreach(CommandInfomyDEinlist)//{//stringcmdText=myDE.CommandText;//SqlParameter[]cmdParms=(SqlParameter[])myDE.Parameters;//PrepareCommand(cmd,conn,tx,cmdText,cmdParms);//if(myDE.EffentNextType==EffentNextType.SolicitationEvent)//{//if(myDE.CommandText.ToLower().IndexOf(count()==-1)//{//tx.Rollback();//thrownewException(违背要求+myDE.CommandText+必须符合selectcount(..的格式);////return0;//}//objectobj=cmd.ExecuteScalar();//boolisHave=false;//if(obj==null&&obj==DBNull.Value)//{//isHave=false;//}//isHave=Convert.ToInt32(obj)0;//if(isHave)//{////引发事件//myDE.OnSolicitationEvent();//}//}//if(myDE.EffentNextType==EffentNextType.WhenHaveContine||myDE.EffentNextType==EffentNextType.WhenNoHaveContine)//{//if(myDE.CommandText.ToLower().IndexOf(count()==-1)//{//tx.Rollback();//thrownewException(SQL:违背要求+myDE.CommandText+必须符合selectcount(..的格式);////return0;//}//objectobj=cmd.ExecuteScalar();//boolisHave=false;//if(obj==null&&obj==DBNull.Value)//{//isHave=false;//}//isHave=Convert.ToInt32(obj)0;//if(myDE.EffentNextType==EffentNextType.WhenHaveContine&&!isHave)//{//tx.Rollback();//thrownewException(SQL:违背要求+myDE.CommandText+返回值必须大于0);////return0;//}//if(myDE.EffentNextType==EffentNextType.WhenNoHaveContine&&isHave)//{//tx.
本文标题:简单三层架构数据库设计(基于DALParam)
链接地址:https://www.777doc.com/doc-5084126 .html