search
HomeDatabaseMysql TutorialC#中Sql DataAdapter的使用

SqlDataAdapter概述 SqlDataAdapter是 DataSet和 SQL Server之间的桥接器,用于检索和保存数据。SqlDataAdapter通过对数据源使用适当的Transact-SQL语句映射 Fill(它可更改DataSet中的数据以匹配数据源中的数据)和 Update(它可更改数据源中的数据以匹配 Data

   SqlDataAdapter概述

  SqlDataAdapter是 DataSet和 SQL Server之间的桥接器,用于检索和保存数据。SqlDataAdapter通过对数据源使用适当的Transact-SQL语句映射 Fill(它可更改DataSet中的数据以匹配数据源中的数据)和 Update(它可更改数据源中的数据以匹配 DataSet中的数据)来提供这一桥接。当SqlDataAdapter填充 DataSet时,它为返回的数据创建必需的表和列(如果这些表和列尚不存在)。

  我们可以通过以下三种方法来创建SqlDataAdapter对象:

  使用方法

  1、通过连接字符串和查询语句

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  strSql="SELECT * FROM 表名";

  SqlDataAdapter da=new SqlDataAdapter(strSql,strConn);

  DataSet ds=new DataSet();//创建DataSet实例

  da.Fill(ds,"自定义虚拟表名");//使用DataAdapter的Fill方法(填充),调用SELECT命令

  这种方法有一个潜在的缺陷。假设应用程序中需要多个SqlDataAdapter对象,用这种方式来创建的话,会导致创建每个SqlDataAdapter时,都同时创建一个新的SqlConnection对象,方法二可以解决这个问题

  2、通过查询语句和SqlConnection对象来创建

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  SqlConnection conn=new SqlConnection(strConn);

  string strSql="SELECT * FROM 表名";

  SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

  DataSet ds=new DataSet();//创建DataSet实例

  da.Fill(ds,"自定义虚拟表名");//使用DataAdapter的Fill方法(填充),调用SELECT命令

  3、通过SqlCommand对象来创建

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  SqlConnection connSql=new SqlConnection (strConn); //Sql链接类的实例化

  connSql.Open ();//打开数据库

  //使用SqlDataAdapter时没有必要从Connection.open()打开,

  //SqlDataAdapter会自动打开关闭它。

  string strSql = "SELECT * FROM 表名"; //要执行的SQL语句

  SqlCommand cmd=new SqlCommand(strSql,connsql);

  SqlDataAdapter da=new SqlDataAdapter(cmd); //创建DataAdapter数据适配器实例

  DataSet ds=new DataSet();//创建DataSet实例

  da.Fill(ds,"自定义虚拟表名");//使用DataAdapter的Fill方法(填充),调用SELECT命令

  ConnSql.Close ();//关闭数据库

  SqlDataAdapter da=new SqlDataAdapter(strSQL,ConnSql); //创建DataAdapter数据适配器实例DataSet ds=new DataSet();//创建DataSet实例da.Fill(ds,"自定义虚拟表名");//使用DataAdapter的Fill方法(填充),调用SELECT命令ConnSql.Close ();//关闭数据库

  注意

  如果只需要执行SQL语句或SP,就没必要用到DataAdapter ,直接用SqlCommand的Execute系列方法就可以了。sqlDataadapter的作用是实现Dataset和DB之间的桥梁:比如将对DataSet的修改更新到数据库。

  SqlDataAdapter的UpdateCommand的执行机制是:当调用SqlDataAdapter.Update()时,检查DataSet中的所有行,然后对每一个修改过的Row执行SqlDataAdapter.UpdateCommand ,也就是说如果未修改DataSet中的数据,SqlDataAdapter.UpdateCommand不会执行。

  使用要点

  1、SqlDataAdapter内部通过SqlDataReader获取数据,而默认情况下SqlDataReader不能获知其查询语句对应的数据库表名,

  所以下面的代码:

  string strConn = "uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  strSql="SELECT * FROM 表名";

  SqlDataAdapter da = new SqlDataAdapter(strSql,strConn);

  DataSet ds = new DataSet();

  da.Fill(ds);

  会在DataSet中创建一个新的DataTable,这个新的DataTable会拥有名为CustomerID和CompanyName 列,但是DataTable对象的名称是Table,而不是我们希望的Customers。

  这个问题,可以通过添加TableMapping来解决:

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  strSql="SELECT * FROM 表名";

  SqlDataAdapter da=new SqlDataAdapter(strSQL,strConn);

  da.TableMappings.Add("Table",,"Customers"); // 设置对象名称

  DataSet ds=new DataSet();

  da.Fill(ds);

  其实最简洁的方法是通过使用Fill方法的重载,通过指定DataTable,像这样:

  SqlDataAdapter.Fill(DataSet,"MyTableName");

  这样就可以不必使用TableMappings集合。

  2、在使用Fill方式时,可以指定DataTable,而不是DataSet:

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  strSql="SELECT * FROM 表名";

  SqlDataAdapter da = new SqlDataAdapter(strSql, strConn);

  DataTable tbl=new DataTable( );

  da.Fill(tbl);

  3、注意打开和关闭连接的处理

  在调用SqlCommand对象执行sql命令之前,需要保证与该对象关联的SqlConnection对象时打开的,否则SqlCommand的方法执行时将引发一个异常,但是我们在上面的代码中看到,SqlDataAdapter没有这样的要求。

  如果调用SqlDataAdapter的Fill方法,并且其SelectCommand属性的SqlConnection是关闭状态,则SqlDataAdapter会自动打开它,然后提交查询,获取结果,最后关闭连接。如果在调用Fill方法前,SqlConnection是打开的,则查询执行完毕后,SqlConnection还将是打开的,也就是说SqlDataAdapter会保证SqlConnection的状态恢复到原来的情形。

  这有时会导致性能问题,需要注意,例如下面的代码:

  string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串

  SqlConnection conn=new SqlConnection(strConn);

  SqlDataAdapter daCustomers,daOrders;

  strSql="SELECT * FROM Customers";

  daCustomers = new SqlDataAdapter(strSql, conn);

  strSql="SELECT * FROM Orders";

  daOrders=new SqlDataAdapter(strSql, conn);

  DataSet ds=new DataSet();

  daCustomers.Fill(ds,"Customers");

  daOrders.Fill(ds,"Orders");

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

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

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

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 do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

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.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

SecLists

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.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!