近日和小伙伴们在进行机房合作,我负责的是D层。在重新写sqlhelper时,发现查询操作用到的是SqlDataAdapter对象,我记得还有个SqlDataReader对象也是执行对数据库的查询操作。还有增删改方法除了ExecuteNonQuery()还有没有其他方法,返回integer和boolean有
近日和小伙伴们在进行机房合作,我负责的是D层。在重新写sqlhelper时,发现查询操作用到的是SqlDataAdapter对象,我记得还有个SqlDataReader对象也是执行对数据库的查询操作。还有增删改方法除了ExecuteNonQuery()还有没有其他方法,返回值integer和boolean有什么不同。个人重构时没好好研究,现在好好总结一下,欢迎各位斧正。
一、增删改的方法
我们在对数据库进行增删改操作时一般使用sqlcommand命令对象,这个对象的方法有三个。非别是ExecuteNonQuery()、ExecuteReader()、ExecuteScalar()。其中ExecuteNonQuery();它的返回值类型为int型。多用于执行增加,删除,修改数据。返回受影响的行数。
具体来说,此方法用于执行任何不从数据库返回结果集的SQL操作命令,包括INSERT INTO、UPDATE、DELETE语句、没有返回数值的存储过程、CREATE TABLE和CREATEINDEX之类的DDL语句。ExecuteNonQuery方法对于UPDATE、INSERT INTO和DELETE这些操作语句,其返回值为该命令所影响的行数。对于所有其他类型的语句,返回值为-1。如果发生回滚,返回值也为-1。所以如果让其执行查询语句,返回值为-1,无意义。我在个人重构时返回的是Boolean型。其实是一样的,当返回值为非零时,说明数据库中受影响的行不为零,所以执行增删改操作True. 当返回值为零或负值时,说明该操作没有执行成功。
二、查询的方法
我们知道sqlcommand对象中有2个查询的方法,分别是ExecuteReader()、ExecuteScalar(),但是我们在写sqlhelper时一般用到的是SqlDataAdapter对象,这三者有什么不同呢?
ExecuteScalar()返回一个int型变量。如果SQL语句是Select查询,则仅仅返回查询结果集中第一行第一列,而忽略其他行和列。如果SQL语句不是Select查询,则这个返回结果没任何作用。由于不知道sql语句到底是什么样的结构(有可能是int,有可能是Char等其它,)所以ExecuteScalar()方法返回一个最基本的类型Object,这个类型是所有类型的基类,可以转换为任意类型,所以用前需强制转换。
ExecuteReader 返回一个DataReader对象,如果在SqlCommand对象中调用,则返回SqlDataReader,如果在OledbCommand对象中调用,返回的是OledbDataReader,可以调用DataReader的方法和属性迭代处理结果集。ExecuteReader方法存在的目的:尽可能快的对数据库进行查询并得到结果。
还有SqlDataAdapter对象,下面主要讲它和SqlDataReaderr的区别。
三、SqlDataReader与SqlDataAdapter的区别
我们重构都用SqlDataAdapter+DataSet。SqlDataReader咋了,招谁惹谁了,为啥没人用。这其实是合适不合适的问题,就像电视剧中经常出现的那句台词“你是个好人,但是我们不合适”。他俩具体适合哪些情况呢。
1.SqlDataReader //基于连接,只读访问适合数据量较小。(连接模式)
SqlDataAdapter //基于非连接,适于数据量较大时,可以另行修改,最后再把修改结果返回给数据库。要求资源也大一点 (断开模式)
2.SqlDataAdapter 读取数据后将数据集放入DataSet ,DataSet 的数据存在本地客服机内存。
3.SqlDataReader返回的是一个数据读写器,只能一条条的读,操作起来不灵活,一般在只读的时候才用到。
SqlDataAdapter返回的是数据集或者表,可以对其中的数据作任意操作
4.写法上不同:
SqlDatReader执行前须先打开数据库,然后须生成一个command对象。再由command.ExecuteReader()方法赋值。完成后须手动关闭联接。
SqlCommand cmd = newSqlCommand("select * from stu", conn);
conn.Open();
SqlDataReader rdr= cmd.ExecuteReader();
conn.close();
SqlDataAdapter 执行时,自动打数据库,且不用Command的ExecuteReader方法进行赋值,完成后自动断开联接。
SqlDataAdapteradptr = new SqlDataAdapter(sql, conn);
DataSet ds = newDataSet();
adptr.Fill(ds,"stu");
四、总结
回想一年前就看了红皮书,就简单了解了涉及SQLSERVER数据库操作的7个对象,对现在每次遇到时都有新的收获。觉自己知之甚少,叹其之强大。

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
