c# - 如何使用 C# 在运行时在 MS Access 中创建存储查询

Question

我正在开发 Windows 应用程序。在这里，我需要在运行时在 MS Access 2007 中创建存储查询。(即单击按钮)用于选择、插入、更新、删除，如何根据参数从表单调用查询？

cmmd.CommandText = "CREATE TABLE tblCustomers ([CustomerID] AUTOINCREMENT PRIMARY KEY, CustomerName Text(50), [CategoryID] Long REFERENCES tblCategories (CategoryID), [IsActive] YesNo, [ModifiedBy] Long REFERENCES tblUsers (UserID), [ModifiedDate] Date)";
cmmd.ExecuteNonQuery();

//Above Create table tblCustomers executed successfully,  

cmmd.CommandText = @"CREATE PROCEDURE prAddCustmer (CustName Text(50), CatID Long, Inact No, ModBY Long, ModDate date ) AS INSERT INTO tblCustomers  (CustomerName , CategoryID, Inactive, ModifiedBy, ModifiedDate) VALUES(CustName, [CatID], [Inac], [ModBy], [ModDate]);";
cmmd.ExecuteNonQuery();
//But got error in Create Procedure prAddCustomers, Please find error in the Stored query and suggest the correct answer 
//Syntax error in PARAMETER clause.

Answer 1

如果您想在 Access 数据库中创建一个新的存储查询，您只需准备一个命令并执行它。

例如，假设您有一个客户表并且您想要使用查询检索单个客户的记录。

创建查询

 string cmdText = @"CREATE PROCEDURE Customer_SelectOne (custID Long) as
     SELECT * FROM Customers WHERE IDCustomer = [custID]";
 OleDbCommand cmd = new OleDbCommand(cmdText, connection);
 cmd.ExecuteNonQuery();

要调用查询，它也是一个标记为 CommandType = CommandType.StoredProcedure

的简单命令

 string cmdText = "Customer_SelectOne";
 OleDbCommand cmd = new OleDbCommand(cmdText, connection);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("custID", customerID);
 OleDbDataReader reader = cmd.ExecuteReader();

上面的例子非常简单。对于更复杂的场景，您需要查看MS-Access Data Definition Language 区域中的引用资料。 .

编辑

语法:

CREATE PROCEDURE ProcName 
                 (optional list of parameters with type and size for Text)
                    AS
                 (Valid SQL Statement using the optional parameters list)

所以这应该是您的“prAddCustomers”

cmmd.CommandText = @"CREATE PROCEDURE prAddCustomers 
                     (CustName Text(50), 
                      CatID Long, 
                      IsActive BIT, 
                      ModBY Long, 
                      ModDate DATETIME )
                    as
                    INSERT INTO tblCustomers 
                      (CustomerName, CategoryID, IsActive, ModifiedBy, ModifiedDate) 
                    VALUES([CustName], [CatID], [IsActive], [ModID],[ModDate])";