SqlConnection，SqlDataAdapter，SqlCommand，SqlParameter

最新推荐文章于 2023-07-08 03:21:52 发布
最新推荐文章于 2023-07-08 03:21:52 发布 · 407 阅读 · 1
文章标签：

#数据库

本文介绍了在VB.NET中使用SqlConnection、SqlCommand、SqlDataAdapter等类进行数据库连接、执行SQL命令及数据填充的方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

这次做vb.net版机房收费系统中常常使用这样一些类——SqlConnection，SqlDataAdapter，SqlCommand，SqlParameter，这些类都是SqlClient类，SqlClient类位于System.Data命名空间中，此命名空间在ADO.NET中也可以算的上是核心部分了，下面我就来分别介绍一下这几个常用类

1.SqlConnection类:提供了与SQL Server数据库的连接。

在构造SqlConnection对象时，可以把连接字符串指定为一个参数，连接字符串包含了打开数据库连接所需要的所有信息。下面是使用SqlConnection类初始化一个连接对象，可以连接SQL Server 数据库，该对象在连接字符串中分别使用了：计算机名：chenjinrong，数据库名：MychargeTwo，SQL Server的用户名，和相应的密码。

DimsqlConnectStr As SqlConnection = New SqlConnection("server=chenjinrong;database=MychargeTwo;uid=sa;pwd=123456")

平时运用的时候一旦初始化了一个连接对象，就可以调用SqlConnection对象的方法了，如：

SqlConnectStr.Open() '打开数据库

SqlConnectStr.Close() '关闭数据库

2.SqlCommand类：执行数据存储的一个SQL命令

这个命令通常指增，删，改，查命令，执行的查询可以含参数，也可以不含，虽然SqlCommand类的构造函数有很多吧，但是最简单的一种还是不含参数的方法。下面是说如何初始化一个SqlCommand对象。



      Dim cmdd As SqlCommand =NewSqlCommand ()




 当你初始化了对相关就可以设置需要的属性和方法来执行任务了，下面介绍执行查询前必须给SqlCommand设置的一些属性








1.Connection属性


该属性被设置为一个SqlConnection对象，为了使命令执行成功，必须在执行命令时打开连接




 cmdd.Connection = sqlConnection1




2.CommandText属性


该属性指定要执行的SQL语句或存储过程


例如：







Dim sqlConnectStr As SqlConnection = New SqlConnection("server=chenjinrong;database=MychargeTwo;uid=sa;pwd=123456")

Dim cmdd AsSqlCommand =New SqlCommand ()

cmdd.Connection= sqlConnection1

Dim sql2 As String = "insert intoStuBasicInfo(CardNo,StuNo,StuName,StuSex,System,Status,Grade,Classs,Balance,Explain,UserName,RegDay,RegTime,IsCheckout)values(@TxtCardNo,@TxtStuNo,@TxtStuName,@CmbStuSex,@TxtSystem,@CmbStatus,@TxtGrade,@TxtClass,@TxtBalanced,@TxtExplain,@UserName,@Day,@Time,@IsCheckOut)"  ’@变量表示SQL语句中的占位符，需要用参数去填充
cmdd.CommandText= sql2







 


3.Parameters属性


该属性是用来访问SqlCommand对象的Parameter集合的，一旦访问了此集合，就可以利用它的属性和方法在集合中创建一个或多个参数了




我们在介绍CommandText属性的时候举得例子中，好多前缀为@的变量，这是SQL语句中的占位符，需要创建参数，在执行SQL语句的时候这些参数去向占位符插入值




例如：


Dim sqlConnectStr As SqlConnection = New SqlConnection("server=chenjinrong;database=MychargeTwo;uid=sa;pwd=123456")

Dim cmdd AsSqlCommand =New SqlCommand ()

cmdd.Connection= sqlConnection1

Dim sql2 As String = "insert intoStuBasicInfo(CardNo,StuNo,StuName,StuSex,System,Status,Grade,Classs,Balance,Explain,UserName,RegDay,RegTime,IsCheckout)values(@TxtCardNo,@TxtStuNo,@TxtStuName,@CmbStuSex,@TxtSystem,@CmbStatus,@TxtGrade,@TxtClass,@TxtBalanced,@TxtExplain,@UserName,@Day,@Time,@IsCheckOut)"  ’@变量表示SQL语句中的占位符，需要用参数去填充
        cmdd.CommandText= sql2



'下面是填充上面的占位符的，创建参数，执行SQL语句的时候这些参数去向占位符插入值
        cmdd.Parameters.Add(NewSqlParameter("@TxtCardNo", StuInfor.CardNo))

        cmdd.Parameters.Add(NewSqlParameter("@TxtStuNo", StuInfor.StuNo))

            cmdd.Parameters.Add(NewSqlParameter("@TxtStuName", StuInfor.StuName))

        cmdd.Parameters.Add(NewSqlParameter("@CmbStuSex", StuInfor.StuSex))

        cmdd.Parameters.Add(NewSqlParameter("@TxtSystem", StuInfor.System))

        cmdd.Parameters.Add(NewSqlParameter("@CmbStatus", StuInfor.Status))

        cmdd.Parameters.Add(NewSqlParameter("@TxtGrade", StuInfor.Grade))

        cmdd.Parameters.Add(NewSqlParameter("@TxtClass", StuInfor.Classs))

        cmdd.Parameters.Add(NewSqlParameter("@TxtBalanced", StuInfor.Balance))

        cmdd.Parameters.Add(NewSqlParameter("@TxtExplain", StuInfor.Explain))

        cmdd.Parameters.Add(NewSqlParameter("@UserName", StuInfor.UserName))

        cmdd.Parameters.Add(NewSqlParameter("@Day", StuInfor.RegDay))

        cmdd.Parameters.Add(NewSqlParameter("@Time", StuInfor.RegTime))

        cmdd.Parameters.Add(NewSqlParameter("@IsCheckOut", StuInfor.IsCheckOut))



4.ExecuteNonQuery 方法


 此方法是用来执行SQL语句的


 下面是向表StuBasicInfo中插入数据的完整代码




          


Dim sqlConnectStr As SqlConnection = New SqlConnection("server=chenjinrong;database=MychargeTwo;uid=sa;pwd=123456")Dim cmdd As SqlCommand =New SqlCommand ()cmdd.Connection = sqlConnection1Dim sql2 As String = "insert into StuBasicInfo (CardNo,StuNo,StuName,StuSex,System,Status,Grade,Classs,Balance,Explain,UserName,RegDay,RegTime,IsCheckout)values(@TxtCardNo,@TxtStuNo,@TxtStuName,@CmbStuSex,@TxtSystem,@CmbStatus,@TxtGrade,@TxtClass,@TxtBalanced,@TxtExplain,@UserName,@Day,@Time,@IsCheckOut)"  ’@变量表示SQL语句中的占位符，需要用参数去填充                                           cmdd.CommandText = sql2                                                             '下面是填充上面的占位符的，创建参数，执行SQL语句的时候这些参数去向占位符插入值             cmdd.Parameters.Add(New SqlParameter("@TxtCardNo", StuInfor.CardNo))
	     cmdd.Parameters.Add(New SqlParameter("@TxtStuNo", StuInfor.StuNo))
	     cmdd.Parameters.Add(New SqlParameter("@TxtStuName", StuInfor.StuName))
	     cmdd.Parameters.Add(New SqlParameter("@CmbStuSex", StuInfor.StuSex))
	     cmdd.Parameters.Add(New SqlParameter("@TxtSystem", StuInfor.System))
	     cmdd.Parameters.Add(New SqlParameter("@CmbStatus", StuInfor.Status))
	     cmdd.Parameters.Add(New SqlParameter("@TxtGrade", StuInfor.Grade))
	     cmdd.Parameters.Add(New SqlParameter("@TxtClass", StuInfor.Classs))
	     cmdd.Parameters.Add(New SqlParameter("@TxtBalanced", StuInfor.Balance))
	     cmdd.Parameters.Add(New SqlParameter("@TxtExplain", StuInfor.Explain))
	     cmdd.Parameters.Add(New SqlParameter("@UserName", StuInfor.UserName))
	     cmdd.Parameters.Add(New SqlParameter("@Day", StuInfor.RegDay))
	     cmdd.Parameters.Add(New SqlParameter("@Time", StuInfor.RegTime))
	     cmdd.Parameters.Add(New SqlParameter("@IsCheckOut", StuInfor.IsCheckOut))
	     sqlConnection1.Open()  '打开连接
	     cmdd.ExecuteNonQuery() '执行SQL语句，并把数据插入到数据库中
	     sqlConnection1.Close()












3.SqlDataAdapter类


 像SqlCommand类一样，SqlDataAdapter类也有其相应的属性和方法






1.SelectCommand属性


该属性用于把SQL Server数据库中的数据填充到DataSet中，要从数据存储中读取数据，必须先设置SqlDataAdapter类的SelectCommand属性。该属性是一个SelectCommand对象，用来指定选取哪些数据和如何选取这些数据。为此SelectCommand也有其自己的属性


  • Connection：设置用来访问数据存储的SqlConnection对象
  • CommandText：设置用来选取数据的SQL语句或存储过程名称
下面是设置这些属性的代码


Dim data As New SqlDataAdapter() '定义一个SqlDataAdapter变量	        data.SelectCommand = New SqlCommand()
data.SelectCommand.Connection = sqlConnection1 '把Connection属性设置为一个有效的连接对象
data.SelectCommand.CommandText = "select CardNo,Status from StuBasicInfo order by StuInfo.CardNo,StuInfo.Status"





2.Fill方法


此方法可以在DataSet对象中填充由SqlDataAdapter对象使用其SelectCommand从数据存储中检索到的数据







 Dim data As New SqlDataAdapter() '定义一个SqlDataAdapter变量	        Dim das As DataSet = New DataSet()
 data.SelectCommand = New SqlCommand()
 data.SelectCommand.Connection = sqlConnection1 '把Connection属性设置为一个有效的连接对象
 data.SelectCommand.CommandText = "select CardNo,Status from StuBasicInfo order by StuInfo.CardNo,StuInfo.Status"
 data.Fill(das, "StuBasicInfo")




以上是我对自己这部分知识的归纳，希望对正在学习这部分知识的朋友有所帮助！



                

        




    

    
  



    

      

        

            

              
                
                  iteye_14608
                
              
            

            

                关注
              
            

        

        

          
      

      










                



	

		

			

				
					
SqlDataAdapter使用

				
			

			

				

					12-11
				

			

		

		

			
				
这个讲述了SqlDataAdapter完全使用教程。

			
		

	



                

            
              



	

		

			

				
					
C#SqlParameter参数写法

				
			

			

				

					04-17
				

			

		

		

			
				
根据提供的信息，我们可以深入探讨C#中的`SqlParameter`参数及其在数据库操作中的应用。下面将详细介绍如何使用`SqlParameter`以及其在SQL查询执行中的具体作用。  ### C# SqlParameter 参数详解  `SqlParameter`类...

			
		

	



              


  
              

                


	

		

			

				
					
SqlDataAdapter

				
			

			

				

					HenryWang
				

				

					03-31
					
					2010
					
				

			

		

		

			
				
SqlDataAdapter 类表示用于填充 DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接。无法继承此类。命名空间:System.Data.SqlClient程序集:System.Data（在 system.data.dll 中）SqlDataAdapter 是 DataSet 和 SQL Server 之间的桥接器

			
		

	





	

		

			

				
					
一篇文章搞定SqlDataAdapter

				
			

			

				

					weixin_30304375的博客
				

				

					05-04
					
					786
					
				

			

		

		

			
				
ado.net提供了丰富的数据库操作，在这些操作中SqlConnection和SqlCommand类是必须使用的，但接下来可以分为两类操作：
一类是用SqlDataReader直接一行一行的读取数据库。
第二类是SqlDataAdapter联合DataSet来读取数据。


两者比较：
SqlDataReader由于是直接访问数据库，所以效率较高。但使用起来不方便。
Sql...

			
		

	



		

			
		



	

		

			

				
					
sqlconnection、sqldataadapter、sqlcomandbuilder 组合去update，更新数据库？

				
			

			

				

					读书 行路 做人 成事
				

				

					06-22
					
					524
					
				

			

		

		

			
				
试试再说

			
		

	





	

		

			

				
					
ADO.NET中的（SQLConnection&SQLCommand&SQLDataReader&SQLDataAdapter等）

				
			

			

				

					胡振涛
				

				

					11-12
					
					865
					
				

			

		

		

			
				
目录

前言

详细介绍

机房七层解释

知识点小结（CommandType和CommandBehavior）

    1.Commandtype：

   2.CommandBehavior

关于DataAdapter(数据适配器) 

  一、特点介绍

 二、DataAdapter和DataReader

前言

    昨晚和小伙伴研究了一下...

			
		

	





	

		

			

				
					
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace RxjhServer.DbClss
{
	public class DBA
	{
		public static void serlog(string txt)
		{
			string sqlJl = World.SqlJl;
			if (sqlJl.Length == 0)
			{
				return;
			}
			string text = sqlJl;
			char[] separator = new char[1] { '|' };
			string[] array = text.Split(separator);
			string[] array2 = array;
			string[] array3 = array2;
			foreach (string text2 in array3)
			{
				if (txt.ToLower().IndexOf(text2.ToLower()) != -1)
				{
					Form1.WriteLine(99, txt);
				}
			}
		}

		public static void serlog(string txt, SqlParameter[] prams)
		{
			string sqlJl = World.SqlJl;
			if (sqlJl.Length == 0)
			{
				return;
			}
			string[] array = sqlJl.Split('|');
			for (int i = 0; i < array.Length; i++)
			{
				if (txt.ToLower().IndexOf(array[i].ToLower()) != -1)
				{
					Form1.WriteLine(99, txt);
				}
			}
			for (int j = 0; j < array.Length; j++)
			{
				foreach (SqlParameter sqlParameter in prams)
				{
					if (sqlParameter.SqlValue.ToString().ToLower().IndexOf(array[j].ToLower()) != -1)
					{
						Form1.WriteLine(99, txt + " " + sqlParameter.SqlValue.ToString());
					}
				}
			}
		}

		public static void Setlog(string txt, SqlParameter[] prams, Exception ex)
		{
			Form1.WriteLine(100, "-----------DBA数据层_错误-----------");
			Form1.WriteLine(100, txt);
			if (prams != null)
			{
				foreach (SqlParameter sqlParameter in prams)
				{
					Form1.WriteLine(100, sqlParameter.SqlValue.ToString());
				}
			}
			Form1.WriteLine(100, ex.Message);
		}

		public static string getstrConnection(string db)
		{
			try
			{
				if (db == null)
				{
					db = "GameServer";
				}
				World.sql = (World.Db.TryGetValue(db, out var value) ? value.SqlConnect : null);
				DbClass value2;
				return World.Db.TryGetValue(db, out value2) ? value2.SqlConnect : null;
			}
			catch
			{
				return null;
			}
		}

		public static int ExeSqlCommand(string sqlCommand, SqlParameter[] prams)
		{
			serlog(sqlCommand, prams);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = SqlDBA.CreateCommandSql(sqlConnection, sqlCommand, prams);
			int result = -1;
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return -1;
			}
			try
			{
				result = sqlCommand2.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				Setlog(sqlCommand, prams, ex);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static int ExeSqlCommand(string sqlCommand, SqlParameter[] prams, string server)
		{
			serlog(sqlCommand, prams);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(server));
			using SqlCommand sqlCommand2 = SqlDBA.CreateCommandSql(sqlConnection, sqlCommand, prams);
			int result = -1;
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return -1;
			}
			try
			{
				result = sqlCommand2.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				Setlog(sqlCommand, prams, ex);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static int ExeSqlCommand(string sqlCommand)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			int result = -1;
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return -1;
			}
			try
			{
				result = sqlCommand2.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				Setlog(sqlCommand, null, ex);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static int ExeSqlCommand(string sqlCommand, string server)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(server));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			int result = -1;
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return -1;
			}
			try
			{
				result = sqlCommand2.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				Setlog(sqlCommand, null, ex);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static int ExeSqlCommand(string sqlCommand, ref Exception exception, string db)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			try
			{
				sqlConnection.Open();
			}
			catch (Exception ex)
			{
				Exception ex2 = (exception = ex);
				return -1;
			}
			int result = sqlCommand2.ExecuteNonQuery();
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static DataTable GetDBToDataTable(string sqlCommand, SqlParameter[] prams)
		{
			serlog(sqlCommand, prams);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			SqlCommand sqlCommand5 = (sqlDataAdapter.SelectCommand = SqlDBA.CreateCommandSql(sqlConnection, sqlCommand, prams));
			SqlCommand sqlCommand2 = sqlCommand5;
			SqlCommand sqlCommand3 = sqlCommand2;
			using (sqlCommand3)
			{
				try
				{
					sqlConnection.Open();
				}
				catch (Exception ex)
				{
					Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
					return null;
				}
				DataTable dataTable = new DataTable();
				try
				{
					sqlDataAdapter.Fill(dataTable);
				}
				catch (Exception ex2)
				{
					Setlog(sqlCommand, prams, ex2);
				}
				sqlDataAdapter.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return dataTable;
			}
		}

		public static DataTable GetDBToDataTable(string sqlCommand, SqlParameter[] prams, string server)
		{
			serlog(sqlCommand, prams);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(server));
			using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			SqlCommand sqlCommand5 = (sqlDataAdapter.SelectCommand = SqlDBA.CreateCommandSql(sqlConnection, sqlCommand, prams));
			SqlCommand sqlCommand2 = sqlCommand5;
			SqlCommand sqlCommand3 = sqlCommand2;
			using (sqlCommand3)
			{
				try
				{
					sqlConnection.Open();
				}
				catch (Exception ex)
				{
					Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
					return null;
				}
				DataTable dataTable = new DataTable();
				try
				{
					sqlDataAdapter.Fill(dataTable);
				}
				catch (Exception ex2)
				{
					Setlog(sqlCommand, prams, ex2);
				}
				sqlDataAdapter.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return dataTable;
			}
		}

		public static DataTable GetDBToDataTable(string sqlCommand)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			SqlCommand sqlCommand5 = (sqlDataAdapter.SelectCommand = new SqlCommand(sqlCommand, sqlConnection));
			SqlCommand sqlCommand2 = sqlCommand5;
			SqlCommand sqlCommand3 = sqlCommand2;
			using (sqlCommand3)
			{
				try
				{
					sqlConnection.Open();
				}
				catch (Exception ex)
				{
					Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
					return null;
				}
				DataTable dataTable = new DataTable();
				try
				{
					sqlDataAdapter.Fill(dataTable);
				}
				catch (Exception ex2)
				{
					Setlog(sqlCommand, null, ex2);
				}
				sqlDataAdapter.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return dataTable;
			}
		}

		public static DataTable GetDBToDataTable(string sqlCommand, string server)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(server));
			using SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
			SqlCommand sqlCommand5 = (sqlDataAdapter.SelectCommand = new SqlCommand(sqlCommand, sqlConnection));
			SqlCommand sqlCommand2 = sqlCommand5;
			SqlCommand sqlCommand3 = sqlCommand2;
			using (sqlCommand3)
			{
				try
				{
					sqlConnection.Open();
				}
				catch
				{
					return null;
				}
				DataTable dataTable = new DataTable();
				try
				{
					sqlDataAdapter.Fill(dataTable);
				}
				catch (Exception ex)
				{
					Setlog(sqlCommand, null, ex);
				}
				sqlDataAdapter.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return dataTable;
			}
		}

		public static DataRowCollection GetDBValue(string sqlCommand, string db)
		{
			return GetDBToDataTable(sqlCommand).Rows;
		}

		public static ArrayList GetDBValue_1(string sqlCommand, string db)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return null;
			}
			SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader();
			if (!sqlDataReader.HasRows)
			{
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return null;
			}
			ArrayList arrayList = new ArrayList();
			if (sqlDataReader.Read())
			{
				for (int i = 0; i < sqlDataReader.FieldCount; i++)
				{
					arrayList.Add(sqlDataReader[i]);
				}
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			sqlCommand2.Dispose();
			return arrayList;
		}

		public static ArrayList GetDBValue_2(string sqlCommand, string db)
		{
			serlog(sqlCommand);
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return null;
			}
			SqlDataReader sqlDataReader = sqlCommand2.ExecuteReader();
			if (!sqlDataReader.HasRows)
			{
				sqlDataReader.Close();
				sqlDataReader.Dispose();
				sqlConnection.Close();
				sqlConnection.Dispose();
				return null;
			}
			ArrayList arrayList = new ArrayList();
			while (sqlDataReader.Read())
			{
				arrayList.Add(sqlDataReader[0]);
			}
			sqlDataReader.Close();
			sqlDataReader.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			sqlCommand2.Dispose();
			return arrayList;
		}

		public static object GetDBValue_3(string sqlCommand)
		{
			serlog(sqlCommand);
			object result = null;
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return null;
			}
			try
			{
				result = sqlCommand2.ExecuteScalar();
			}
			catch (Exception ex)
			{
				Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static object GetDBValue_3(string sqlCommand, SqlParameter[] prams)
		{
			serlog(sqlCommand, prams);
			object result = null;
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(null));
			using SqlCommand sqlCommand2 = SqlDBA.CreateCommandSql(sqlConnection, sqlCommand, prams);
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return null;
			}
			try
			{
				result = sqlCommand2.ExecuteScalar();
			}
			catch (Exception ex)
			{
				Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}

		public static object GetDBValue_3(string sqlCommand, string db)
		{
			serlog(sqlCommand);
			object result = null;
			using SqlConnection sqlConnection = new SqlConnection(getstrConnection(db));
			using SqlCommand sqlCommand2 = new SqlCommand(sqlCommand, sqlConnection);
			try
			{
				sqlConnection.Open();
			}
			catch
			{
				return null;
			}
			try
			{
				result = sqlCommand2.ExecuteScalar();
			}
			catch (Exception ex)
			{
				Form1.WriteLine(100, "DBA数据层_错误" + ex.Message + " " + sqlCommand);
			}
			sqlCommand2.Dispose();
			sqlConnection.Close();
			sqlConnection.Dispose();
			return result;
		}
	}
}
这是我的DBA类

				
			

			

				

					07-19
				

			

		

		

			
				
 public DataTable ExecuteStoredProcedure(string procedureName, params SqlParameter[] parameters)  {  DataTable dt = new DataTable();  try  {  using (SqlConnection conn = new SqlConnection...

			
		

	





	

		

			

				
					
C#中 SqlConnection, SqlCommand与数据库操作详解

				
			

			

				

					
				

			

		

		

			
				
3. SqlParameter: 当在SqlCommand中执行参数化的SQL时，SqlParameter类派上用场。它可以存储SQL查询中的参数，提供安全性和灵活性，避免SQL注入攻击。创建SqlParameter实例时，需要指定名称、数据库类型和值。  4. ...

			
		

	





	

		

			

				
					
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace DBUtility
{
    public class DbHelperSQL
    {
        // Fields
        public static string connectionString;

        // Methods
        static DbHelperSQL()
        {
            connectionString = ConfigurationManager.AppSettings["ConnectionString"];
        }

        public DbHelperSQL() { }
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public static SqlDataReader ExecuteReader(string strSQL)
        {
          SqlDataReader dr;
          SqlConnection connection = new SqlConnection(connectionString);
          SqlCommand cmd = new SqlCommand(strSQL, connection);
          try
          {
              connection.Open();
              dr= cmd.ExecuteReader();
          }
          catch (SqlException e)
          {
              throw new Exception(e.Message);
          }
          return dr;
        }

        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlDataReader dr;
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                dr = myReader;
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            return dr;
        }

        public static int ExecuteSql(string SQLString)
        {
            int count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
            return count;
        }

        public static int ExecuteSql(string SQLString, string content)
        {
            int count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    count = cmd.ExecuteNonQuery();
                }
                catch (SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return count;
        }

        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            int count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
            return count;
        }

        public static int ExecuteSqlByTime(string SQLString, int Times)
        {
            int count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
            return count;
        }

        public static object ExecuteSqlGet(string SQLString, string content)
        {
            object esg;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                SqlParameter myParameter = new SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                    {
                        return null;
                    }
                    esg = obj;
                }
                catch (SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return esg;
        }

        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            int count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                SqlParameter myParameter = new SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    count = cmd.ExecuteNonQuery();
                }
                catch (SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return count;
        }

        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public static bool Exists(string strSql)
        {
            int cmdresult;
            object obj = GetSingle(strSql);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            return true;
        }

        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int cmdresult;
            object obj = GetSingle(strSql, cmdParms);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            return true;
        }

        public static int GetMaxID(string FieldName, string TableName)
        {
            object obj = GetSingle("select max(" + FieldName + ")+1 from " + TableName);
            if (obj == null)
            {
                return 1;
            }
            return int.Parse(obj.ToString());
        }

        public static object GetSingle(string SQLString)
        {
            object gs;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            return null;
                        }
                        return obj;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
            return gs;
        }

        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            object gs;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            return null;
                        }
                        return obj;
                    }
                    catch (SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
            return gs;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    new SqlDataAdapter(SQLString, connection).Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataSet Query(string SQLString,int pageindx,int pagesize,string tablename)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    new SqlDataAdapter(SQLString, connection).Fill(ds,pageindx,pagesize,tablename);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataSet Query(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            DataSet dsq;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    dsq = ds;
                }
            }
            return dsq;
        }

        public static DataSet Query(string SQLString,string tablename)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    new SqlDataAdapter(SQLString, connection).Fill(ds, tablename);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                int result = (int)command.Parameters["ReturnValue"].Value;
                connection.Close();
                return result;
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static void RunStatProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                connection.Close();   
            }
        }
    }
}


					
最新发布

				
			

			

				

					07-25
				

			

		

		

			
				
3. 使用SqlConnection, SqlCommand, SqlDataAdapter等对象。  注意：这里我们假设使用SQL Server数据库，所以使用System.Data.SqlClient命名空间。  代码实现如下：</think>### C# 数据库操作帮助类 DbHelperSQL ...

			
		

	





	

		

			

				
					
SqlDataAdapter.Update批量更新.txt

				
			

			

				

					03-08
				

			

		

		

			
				
 SqlParameter CODE_NOParam = new SqlParameter("@CODE_NO", SqlDbType.VarChar, 50, "CODE_NO");  adp.UpdateCommand.Parameters.Add(CODE_NOParam);   SqlParameter IDParam = new SqlParameter("@ID", SqlDbType...

			
		

	





	

		

			

				
					
SqlDataAdapter概述

				
			

			

				

					demonzsmj12315的博客
				

				

					03-14
					
					227
					
				

			

		

		

			
				
SqlDataAdapter是DataSet和SQLServer之间的桥接器，用于检索和保存数据。SqlDataAdapter通过对数据源使用适当的Transact-SQL语句映射Fill（它可更改DataSet中的数据以匹配数据源中的数据）和Update（它可更改数据源中的数据以匹配DataSet中的数据）来提供这一桥接。当SqlDataAdapter填充DataSet时...

			
		

	





	

		

			

				
					
sqlDataAdapter

				
			

			

				

					魔幻之翼的坚持
				

				

					09-05
					
					739
					
				

			

		

		

			
				
using System;using System.Data;using System.Data.SqlClient;class ExecuteSelect...{  public static void Main()  ...{    // 创建一个 SqlConnection 对象用于连接数据库    SqlConnection mySqlConnection =      new Sql

			
		

	





	

		

			

				
					
SqlDataAdapter类充当 DataSet 和 SQL数据库间的桥梁

				
			

			

				

					book_dw5189的博客
				

				

					07-08
					
					1316
					
				

			

		

		

			
				
SqlDataAdapter类充当 DataSet 和 SQL数据库间的桥梁

			
		

	





	

		

			

				
					
ADO.NET(SqlDataAdapter)

				
			

			

				

					Mr_Zhangsl1的博客
				

				

					11-21
					
					529
					
				

			

		

		

			
				
1.SqlDataAdapter 对象称为数据适配器对象 是DataSet与数据库之间通信的桥梁。

2.可以把SqlDataAdapter看成功能更强大的SqlCommand对象

3.通过SqlDataAdapter对象 可以实现数据库与DataSet DataTable 之间的数据传输，实现了离线操作数据库数据。

4.也SqlDataAdapter对象直接交互的是DataSet对象。

5.DataSet是数据集，是在计算机内存中存储的小小的数据库，DataSet 包含DataTable，Da...

			
		

	





	

		

			

				
					
ADO.NET 中的 SqlDataAdapter

				
			

			

				

					lemonGuo的大脑皮层、小脑、海马体、杏仁核.....
				

				

					07-10
					
					1656
					
				

			

		

		

			
				
一. SqlDataReader和SqlDataAdapter区别：SqlDataReader：建立连接后，一直保持连接状态。数据都在数据库中，需要某个数据时才会去取！SqlDataAdapter： 建立连接后，一次性的把所有的数据加载到C#的内存中，然后就关闭了。二.运用场景：SqlDataAdapter一次性拿取数据固然是方便，但是当数据量过大时，会造成内存不足等问题，这时SqlDataRead

			
		

	





	

		

			

				
					
SqlDataAdapter简单介绍

				
			

			

				

					u014683488的专栏
				

				

					08-15
					
					596
					
				

			

		

		

			
				
http://www.cnblogs.com/nliao/archive/2012/11/18/2775848.html
 SqlDataAdapter的Fill方法调用前不需要有活动的SqlConnection对象，SqlDataAdapter会自己打开strConn语句中的数据库，获取查询结果后，关闭与数据库的连接。如果已经存在SqlConnection对象，无论是否已经打开，SqlData

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值