SQL 类封装 (简化存储过程调用、查询和 .NET 的 DataSet 返回)

• 下载源文件 - 4.56 Kb

引言

假设您有一个具有 SQL Server 后端的应用程序，并且必须处理所有不同的 SQL 连接类，例如 SqlConnection、SqlDataAdapter、DataSet 和 SqlCommand。您还需要管理所有这些类协同工作，以便您可以返回一个 DataSet 或在代码中运行一些查询。这是一个很好的工具，可以帮助初学者了解如何连接到 SQL 数据库。它也是一个完整的类，使所有 SQL 交互变得非常容易。

该类封装了所有这些功能。COperationsSQL 执行以下操作

1. 封装了返回表、调用存储过程和返回查询所需的所有类。
2. 将数据格式化为有效的 SQL 字符串。例如：日期周围的单引号、varchar 自动完成（取决于传入的变量类型）。
3. 轻松连接测试，以确保已传入有效的登录名和用户名。
4. 保存用户帐户和角色信息的结构。
5. 用于传入参数的结构，带有变量名，格式为：myStoredProcedure @startDate = '05/01/2005', @endDate = '05/31/2005'.
6. 为每个调用的存储过程返回一个 Dataset。

如何使用该类

您需要将下载中提供的 .cs 文件添加到您的项目中。

以下是构造函数

public COperationsSQL(string Server, string Database, 
                      string LoginName, string Password)

指定 Server、Database、LoginName 和 Password。构造函数测试该函数，以确保 Server、Database、LoginName 和 Password 有效。

public COperationsSQL(string ConnectionStringCoded)

连接字符串也可以作为单个字符串传递。例如

 "server=MyServer;database=MyDatabase;uid=johndoe;pwd=1234"

这将对其进行解析，以提取 server、database 和 userid，以供日后参考（如果需要）。

public COperationsSQL(COperationsSQL rhs)

从已实例化的类中获取 server、database、userid 和 password。

如何实例化该类

这里有一个如何实例化该类的例子

COperationsSQL connection = new COperationsSQL("MyServer", 
              "MyDataBase", "JohnDoe", "1234"); //Simple Enough

注意：如果无法连接到数据库，将抛回一个错误。如果能够连接，则登录人员的 uID 和 RoleInfo 将存储在一个可供使用的结构中。另请注意，不允许以 SA 身份登录（如果您愿意，可以更改）。

一旦该类被实例化，就可以使用了。

方法

以下是该类中可用的一些方法及其用法的描述

• .ExecuteSelectStatement: 此方法将为已编码的 SQL 查询返回一个 DataSet。以下是如何使用它的一个例子

  (在实例化该类之后)

  System.Data.DataSet returnedValue = 
                 new System.Data.DataSet();
  returnedValue = connection.ExecuteSelectStatement(
                            "select * from sysojects");
  //This will display the first column 
  //of the first row in a message box.
  MessageBox.Show(
      returnedValue.Tables[0].Rows[0][0].ToString());

• .ExecuteStoredProcedure: 这是关于这个类的最好的地方。好吧，假设您有要运行的存储过程，其规格如下

  --This stored procedure will save a person into a table
  --RETURNS one column named "ID" (the identity column ID) 
  --to signify @@identity (select @@identity [ID] at the 
  --end of the stored procedure)
  savePerson @firstName = 'John', @lastName = 'Doe', 
       @DOB = '01/01/1980', @currentAge = 25, @isAlive = 1
  
  --This procedure will return the ID, firstname, lastname, 
  --dob, currentage and isAlive when given an ID of the person
  getPerson @ID = 0 --we are assuming that John Doe is the 
                    --first person to get entered.

  这是它在 SQL 中运行的输入方式。现在，使用这个类在 .NET 中执行此存储过程有两种方法。

示例

第一种方式（这种方式使用常规变量，传递到存储过程中的变量必须按照 SP 指定的确切顺序排列）

    //This is where the data/table will be 
    //stored for the returned person
    System.Data.DataSet myDataSet = new DataSet(); 
    //the name of the stored procedure to run
    string saveSp = "savePerson"; 
    //the name of the stored procedure to run
    string getSp = "getPerson"; 
    string firstName = "John"; //parameters
    string lastName = "Doe"; //parameters
    System.DateTime DOB = "01/01/1980";
    int currentAge = 25;
    bool isAlive = true;
    //This is where the person id will be stored
    int personID; 

    try
    {
        //Instantiation of the class
        CUtilities.COperationsSQL myconn = 
                 new COperationsSQL("MyServer", 
                    "MyDataBase", "RobertFrost", "1234");
        //After the class has been instantiated, 
        //we will then save a person into the database
        //NOTE: personID will also be set to the returned 
        //value for the stored procedure
        //NOTE: the class willl automatically put ticks 
        //        around whatever needs to have ticks
        //        and will change c# boolean to sql boolean. 
        //        An error will be thrown if a SQL type
        //        has not been handled for.
        
        //There are two ways you can add the parameters to 
        //the sp. Either as an ArrayList or an Array 
        //of objects.
        object []param = new object[5];
        param[0] = firstName;
        param[1] = lastName;
        param[2] = DOB;
        param[3] = currentAge;
        param[4] = isAlive;

        //NOTE: when you use this method, the 
        //PARAMETERS MUST BE PASSED IN CORRECT ORDER
        //We will now execute the storedprocedure 
        //and set the personID in one step.
        personID = (int)myconn.ExcecuteStoredProcedure(saveSp, 
                                    param).Tables[0].Rows[0][0];
        //We will now execute the getPerson stored 
        //procedure but with the ArrayList approach.
        System.Collections.ArrayList parameters = 
                    new System.Collections.ArrayList();
        parameters.Add(personID);
        //Lets say that the Stored procedure returns Columns 
        //with column names: ID, FirstName, LastName, DOB, 
        //CurrentAge,isAlive
        myDataSet = 
            myconn.ExcecuteStoredProcedure(getSp, parameters);
        MessageBox.Show("Hello! My name is " +
                    myDataSet.Tables[0].Rows[0]["FirstName"] +
                    " " + 
                    myDataSet.Tables[0].Rows[0]["LastName"] +
                    ". And my ID is : " + 
                    myDataSet.Tables[0].Rows[0]["ID"]);
    }
    catch(Exception error)
    {
        MessageBox.Show(error.Message);
    }

第二种方式（这种方式使用类中可用的结构，以便可以按任何顺序传递参数）

    //This is where the data/table will 
    //be stored for the returned person
    System.Data.DataSet myDataSet = new DataSet(); 
    //the name of the stored procedure to run
    string saveSp = "savePerson"; 
    //the name of the stored procedure to run
    string getSp = "getPerson"; 
    string firstName = "John"; //parameters
    string lastName = "Doe"; //parameters
    System.DateTime DOB = "01/01/1980";
    int currentAge = 25;
    bool isAlive = true;
    //This is where the person id will be stored
    int personID; 

    try
    {
        //Instantiation of the class
        CUtilities.COperationsSQL myconn = 
            new COperationsSQL("MyServer", 
               "MyDataBase", "RobertFrost", "1234");
        //After the class has been instantiated, we 
        //will then save a person into the database
        //NOTE: personID will also be set to the 
        //returned value for the stored procedure
        //NOTE: the class willl automatically put 
        //        ticks around whatever needs to have ticks
        //        and will change c# boolean to sql boolean. 
        //        An error will be thrown if a SQL type
        //        has not been handled for.
        
        //passing in the parameters out of order using 
        //the sIdentifierItem Structure in the class
        CUtilities.COperationsSQL.sIdentifierItem []param = 
               new CUtilities.COperationsSQL.sIdentifierItem[5];
        param[0] = 
          new CUtilities.COperationsSQL.sIdentifierItem("@lastName", 
                                                lastName, "varchar");
        param[1] = 
          new CUtilities.COperationsSQL.sIdentifierItem("@firstName", 
                                               firstName, "varchar");
        param[2] = 
          new CUtilities.COperationsSQL.sIdentifierItem("@isAlive", 
                                                     isAlive, "bit");
        param[3] = 
          new CUtilities.COperationsSQL.sIdentifierItem("@dob", 
                                                    DOB, "datetime");
        param[4] = 
          new CUtilities.COperationsSQL.sIdentifierItem("@currentage", 
                                                   currentAge, "int");

        //We will now execute the storedprocedure 
        //and set the personID in one step.
        personID = (int)myconn.ExcecuteStoredProcedure(saveSp, 
                                      param).Tables[0].Rows[0][0];

        //We will now execute the getPerson stored 
        //procedure but with the ArrayList approach.
        System.Collections.ArrayList parameters = 
                               new System.Collections.ArrayList();
        parameters.Add(personID);
        //Lets say that the Stored procedure returns Columns 
        //with column names: ID, FirstName, LastName, DOB, 
        //CurrentAge,isAlive
        myDataSet = myconn.ExcecuteStoredProcedure(getSp, parameters);
        MessageBox.Show("Hello! My name is " +
                        myDataSet.Tables[0].Rows[0]["FirstName"] +
                        " " + 
                        myDataSet.Tables[0].Rows[0]["LastName"] +
                        ". And my ID is : " + 
                        myDataSet.Tables[0].Rows[0]["ID"]);
    }
    catch(Exception error)
    {
        MessageBox.Show(error.Message);
    }

这基本上就是该类的用途，它是应用程序和 SQL Server 后端之间的一个简单接口。使用该类的最佳方法是将其作为您自己的对象的一部分（例如人员对象），然后执行从数据库获取数据并将数据保存到数据库所需的交互。整个类作为链接位于顶部。

将 .cs 文件添加到您的项目中并享受。