简单三层架构数据库设计(基于DALParam)

1、简单三层架构数据库设计（基于DALParam）如果原项目中有解决方案如下，直接点击右键添加类项目。如果看不到上面的解决方案名称，只有项目名称如下：则可添加一个新建一个空的解决方案如下：然后在里面添加不同解决方案文件夹，然后添加现有项目，可以把原项目加进来。一、添加三层架构项目鼠标右键点击解决方案名称，添加新的类项目，如下：添加BLL类项目添加DAL类项目添加DBUtility类项目，数据访问类添加实体类项目二、添加具体类的实现1、添加数据访问类代码如下：usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSyste

2、m.Text;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Collections;namespaceRC101ZhangSan.DBUtility/数据访问抽象基础类/Copyright(C)Maticsoft/publicabstractclassDbHelperSQL/数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现./publicstaticstringconnectionString=/ConfigurationManager.AppSettingsConnectionStr

3、ing/PubConstant.ConnectionString;protectedstaticstringconnectionString=DataSource=.SQLEXPRESS;InitialCatalog=qqData;IntegratedSecurity=SSPI;publicDbHelperSQL()#region公用方法/判断是否存在某表的某个字段/表名称/列名称/是否存在publicstaticboolColumnExists(stringtableName,stringcolumnName)stringsql=selectcount(1)fromsyscolumnswhe

4、reid=object_id(+tableName+)andname=+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;elsereturnint.Parse

5、(obj.ToString();publicstaticboolExists(stringstrSql)objectobj=GetSingle(strSql);intcmdresult;if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value)cmdresult=0;elsecmdresult=int.Parse(obj.ToString();/也可能=0if(cmdresult=0)returnfalse;elsereturntrue;/表是否存在/publicstaticboolTabExists(stringTab

6、leName)stringstrsql=selectcount(*)fromsysobjectswhereid=object_id(N+TableName+)andOBJECTPROPERTY(id,NIsUserTable)=1;/stringstrsql=SELECTcount(*)FROMsys.objectsWHEREobject_id=OBJECT_ID(Ndbo.+TableName+)ANDtypein(NU);objectobj=GetSingle(strsql);intcmdresult;if(Object.Equals(obj,null)|(Object.Equals(ob

7、j,System.DBNull.Value)cmdresult=0;elsecmdresult=int.Parse(obj.ToString();if(cmdresult=0)returnfalse;elsereturntrue;publicstaticboolExists(stringstrSql,paramsSqlParametercmdParms)objectobj=GetSingle(strSql,cmdParms);intcmdresult;if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value)cmdres

8、ult=0;elsecmdresult=int.Parse(obj.ToString();if(cmdresult=0)returnfalse;elsereturntrue;#endregion#region执行简单SQL语句/执行SQL语句，返回影响的记录数/SQL语句/影响的记录数publicstaticintExecuteSql(stringSQLString)using(SqlConnectionconnection=newSqlConnection(connectionString)using(SqlCommandcmd=newSqlCommand(SQLString,connect

9、ion)tryconnection.Open();introws=cmd.ExecuteNonQuery();returnrows;catch(System.Data.SqlClient.SqlExceptione)connection.Close();throwe;publicstaticintExecuteSqlByTime(stringSQLString,intTimes)using(SqlConnectionconnection=newSqlConnection(connectionString)using(SqlCommandcmd=newSqlCommand(SQLString,c

10、onnection)tryconnection.Open();cmd.CommandTimeout=Times;introws=cmd.ExecuteNonQuery();returnrows;catch(System.Data.SqlClient.SqlExceptione)connection.Close();throwe;/执行Sql和Oracle滴混合事务/SQL命令行列表/Oracle命令行列表/执行结果0-由于SQL造成事务失败-1由于Oracle造成事务失败1-整体事务执行成功/publicstaticintExecuteSqlTran(Listlist,ListoracleCm

11、dSqlList)/using(SqlConnectionconn=newSqlConnection(connectionString)/conn.Open();/SqlCommandcmd=newSqlCommand();/cmd.Connection=conn;/SqlTransactiontx=conn.BeginTransaction();/cmd.Transaction=tx;/try/foreach(CommandInfomyDEinlist)/stringcmdText=myDE.CommandText;/SqlParametercmdParms=(SqlParameter)my

12、DE.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=fal

13、se;/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();/throw

14、newException(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:违背要求+m

15、yDE.CommandText+返回值必须大于0);/return0;/if(myDE.EffentNextType=EffentNextType.WhenNoHaveContine&isHave)/tx.Rollback();/thrownewException(SQL:违背要求+myDE.CommandText+返回值必须等于0);/return0;/continue;/intval=cmd.ExecuteNonQuery();/if(myDE.EffentNextType=EffentNextType.ExcuteEffectRows&val=0)/tx.Rollback();/thro

16、wnewException(SQL:违背要求+myDE.CommandText+必须有影响行);/return0;/cmd.Parameters.Clear();/stringoraConnectionString=PubConstant.GetConnectionString(ConnectionStringPPC);/boolres=OracleHelper.ExecuteSqlTran(oraConnectionString,oracleCmdSqlList);/if(!res)/tx.Rollback();/thrownewException(Oracle执行失败);/return-1

17、;/tx.Commit();/return1;/catch(System.Data.SqlClient.SqlExceptione)/tx.Rollback();/throwe;/catch(Exceptione)/tx.Rollback();/throwe;/执行多条SQL语句，实现数据库事务。/多条SQL语句publicstaticintExecuteSqlTran(ListSQLStringList)using(SqlConnectionconn=newSqlConnection(connectionString)conn.Open();SqlCommandcmd=newSqlComma

18、nd();cmd.Connection=conn;SqlTransactiontx=conn.BeginTransaction();cmd.Transaction=tx;tryintcount=0;for(intn=0;n1)cmd.CommandText=strsql;count+=cmd.ExecuteNonQuery();tx.Commit();returncount;catchtx.Rollback();return0;/执行带一个存储过程参数的的SQL语句。/SQL语句/参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加/影响的记录数publicstaticin

19、tExecuteSql(stringSQLString,stringcontent)using(SqlConnectionconnection=newSqlConnection(connectionString)SqlCommandcmd=newSqlCommand(SQLString,connection);System.Data.SqlClient.SqlParametermyParameter=newSystem.Data.SqlClient.SqlParameter(content,SqlDbType.NText);myParameter.Value=content;cmd.Param

20、eters.Add(myParameter);tryconnection.Open();introws=cmd.ExecuteNonQuery();returnrows;catch(System.Data.SqlClient.SqlExceptione)throwe;finallycmd.Dispose();connection.Close();/执行带一个存储过程参数的的SQL语句。/SQL语句/参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加/影响的记录数publicstaticobjectExecuteSqlGet(stringSQLString,stringco

21、ntent)using(SqlConnectionconnection=newSqlConnection(connectionString)SqlCommandcmd=newSqlCommand(SQLString,connection);System.Data.SqlClient.SqlParametermyParameter=newSystem.Data.SqlClient.SqlParameter(content,SqlDbType.NText);myParameter.Value=content;cmd.Parameters.Add(myParameter);tryconnection

22、.Open();objectobj=cmd.ExecuteScalar();if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value)returnnull;elsereturnobj;catch(System.Data.SqlClient.SqlExceptione)throwe;finallycmd.Dispose();connection.Close();/向数据库里插入图像格式的字段(和上面情况类似的另一种实例)/SQL语句/图像字节,数据库的字段类型为image的情况/影响的记录数publicstaticintE

23、xecuteSqlInsertImg(stringstrSQL,bytefs)using(SqlConnectionconnection=newSqlConnection(connectionString)SqlCommandcmd=newSqlCommand(strSQL,connection);System.Data.SqlClient.SqlParametermyParameter=newSystem.Data.SqlClient.SqlParameter(fs,SqlDbType.Image);myParameter.Value=fs;cmd.Parameters.Add(myPara

24、meter);tryconnection.Open();introws=cmd.ExecuteNonQuery();returnrows;catch(System.Data.SqlClient.SqlExceptione)throwe;finallycmd.Dispose();connection.Close();/执行一条计算查询结果语句，返回查询结果（object）。/计算查询结果语句/查询结果（object）publicstaticobjectGetSingle(stringSQLString)using(SqlConnectionconnection=newSqlConnection(

25、connectionString)using(SqlCommandcmd=newSqlCommand(SQLString,connection)tryconnection.Open();objectobj=cmd.ExecuteScalar();if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value)returnnull;elsereturnobj;catch(System.Data.SqlClient.SqlExceptione)connection.Close();throwe;publicstaticobjectGetSingle(stringSQLString,intTimes)using(SqlConnectionconnection=newSqlConnection(connectionString)using(SqlCommandcmd=newSqlCommand(SQLString,connection)tryconnection.Open();cmd.CommandTime