连接级别数据库-如何编辑表 [英] connection level dataBase - how to edit tables
问题描述
使用 c＃、. net Framwork 4.5，VS 12
尝试了解数据库访问的连接级别。
这个问题有很长的解释！
当前正在创建数据库，操作简单， dll，用于与数据库和控制台应用程序配合使用，以访问db。
关于创建的一些词汇：
下一个我的数据库：
过程- GetPetName ：
更改过程GetPetName（@carId int，@petName char（10）OUTPUT）
AS
SELECT @petName = PetName from其中CarId = @carId
的清单也在dll中为数据库创建连接，并在控制台应用程序中尝试使用此Dll中的方法。
问题是少数方法不起作用，也不知道为什么-检查过几次方法，但没有结果。
问题方法和我所遇到的问题-如下所述：
-
从DB使用过程的方法
公共字符串LookUpPetName （int CarID）
{
string CarPetName = string.Empty;
使用（SqlCommand cmd =新的SqlCommand（ GetPetName，this.sqlCn））
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter（）;
param.ParameterName = @carID;
param.SqlDbType = SqlDbType.Int;
param.Value = CarID;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add（param）;
param = new SqlParameter（）;
param.ParameterName = @petName;
param.SqlDbType = SqlDbType.Char;
param.Size = 10;
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add（param）;
CarPetName =（string）cmd.Parameters [ petName]。Value;
}
返回CarPetName；
}
}
使用此方法时，当我尝试调用它时出现错误消息-SQLParameterName为 petName的SQLParameter SQLParameter的问题在您的SQLParameterCollection中不存在。但是据我了解，我使用过程 GetPetName 返回 petName 。
问题编号1 -我是否正确理解db中过程的用法？如果不是，请为我解释，如果是，为什么我会得到这样的结果。
第二种方法是在表中插入条目-在这里，我要下一个
public void InsertoAuto（int CarID，字符串颜色，
字符串Make，字符串PetName）
{
string sql = string.Format（插入库存
+（CarID，Make，Color，PetName）值
+（ @ mycarID，@ Make，@ Color，@ PetName ））;
使用（SqlCommand sqlCommand = new SqlCommand（sql，this.sqlCn））
{
//添加参数-帮助避免攻击，
//发出请求更快一点
SqlParameter param = new SqlParameter（）;
param.ParameterName = @mycarID;
param.Value = CarID;
param.SqlDbType = SqlDbType.Int;
sqlCommand.Parameters.Add（param）;
param = new SqlParameter（）;
param.ParameterName = @Make;
param.Value =制造;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add（param）;
param = new SqlParameter（）;
param.ParameterName = @Color;
param.Value =颜色;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add（param）;
param = new SqlParameter（）;
param.ParameterName = @PetName;
param.Value = PetName;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add（param）;
sqlCommand.ExecuteNonQuery（）;
}
}
和第二种插入方式（使用某些实体）
public void UpdateCarPetName（int carId，字符串carName）
{
字符串sql = string.Format（ 更新库存集PetName ='{0}'，其中CarID ='{1}'，
carName，carId）；
使用（SqlCommand cmd =新的SqlCommand（sql，sqlCn））
{
cmd.ExecuteNonQuery（）;
}
}
当我尝试调用此方法时-两者（请注意：在调用方法一个int值和3个字符串值之前，我得到了错误），我得到了描述错误-第一个方法的 @mycarID 语法不正确，而第二个变体的PetName语法不正确。但是，如果所有参数都适合字符串值，那么这是怎么回事。还要检查（如您所见），表中是否使用了相同类型的变量。
问题编号2 -我错了这种方法，为什么我不能采用int参数？
在创建用于处理数据库的dll期间，我也无法检查Dll中的方法功能，如果我不将其用于其他项目。
其他问题-在创建DLL时是否有一些方法可以检查方法，或者我总是必须为并行创建一个支持项目咀嚼我的dll的可使用性？意味着我在创建过程中如何逐步调试？
Thnik一个贴子有很多问题，但将它们放在一起以减少解释部分
在第一种方法中，将最后几行更改为：
cmd.ExecuteNonQuery（）;
CarPetName =（string）cmd.Parameters [ @ petName]。Value;
您没有执行命令，还请注意 @ 在参数名称之前。
在第二种方法中，将括号保留在查询之外：
+（ @mycarID，@Make，@Color，@PetName））；
应该是
+（（@mycarID，@Make，@Color，@PetName））; //注意字符串
中的括号
在第三种方法中，您正在比较 CarId 带有字符串
其中CarID ='{1}'
应该是
其中的CarID = {1}
嗯，它不应该：您应该像以前那样使用参数。 / p>
using c#, .net Framwork 4.5, VS 12
Try to understand connection level of database access.
This Question has long explanation!
Currently create database, simple procedure, dll for work with data base and Console app for access to db.
Few words about created:
My data base next :
Procedure - GetPetName:
ALTER PROCEDURE GetPetName ( @carId int, @petName char(10) OUTPUT )
AS
SELECT @petName = PetName from Inventory where CarId = @carId
also in dll i create connection for my DB, and in console app try to use method from this Dll.
Problem, is that few method not work, and have no idea why - checked few times methods, but has no result. Also method for updating information and showing all info from DB works.
Problemic methods, and problems that i have with it - described below:
Method for using Procedure from DB
public string LookUpPetName(int CarID) { string CarPetName = string.Empty; using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn)) { cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter(); param.ParameterName = "@carID"; param.SqlDbType = SqlDbType.Int; param.Value = CarID; param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@petName"; param.SqlDbType = SqlDbType.Char; param.Size = 10; param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); CarPetName = (string)cmd.Parameters["petName"].Value; } return CarPetName; } }
with this method, when I try to call it got error message - problem with next desciprion SQLParameter with SQLParameterName "petName" don't exist in your SQLParameterCollection. But as I understand I return petName using my procedure GetPetName.
Question number 1 - Am I correctly understand usage of procedure from db? If no - please explain it for me, if yes, why i got such result.
The second method is created for inserting entries in my table - here i mede next
public void InsertoAuto(int CarID, string Color,
string Make, string PetName)
{
string sql = string.Format("Insert into Inventory"
+ " (CarID, Make, Color, PetName) Values"
+ ("@mycarID, @Make, @Color, @PetName"));
using (SqlCommand sqlCommand = new SqlCommand(sql, this.sqlCn))
{
//add parameters - help to avoid attacks,
//make request a little bit faster
SqlParameter param = new SqlParameter();
param.ParameterName = "@mycarID";
param.Value = CarID;
param.SqlDbType = SqlDbType.Int;
sqlCommand.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Make";
param.Value = Make;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Color";
param.Value = Color;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PetName";
param.Value = PetName;
param.Size = 10;
param.SqlDbType = SqlDbType.Char;
sqlCommand.Parameters.Add(param);
sqlCommand.ExecuteNonQuery();
}
}
and the second variant for insertion (using some entities)
public void UpdateCarPetName (int carId, string carName)
{
string sql = string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
carName, carId);
using (SqlCommand cmd = new SqlCommand(sql, sqlCn))
{
cmd.ExecuteNonQuery();
}
}
When i try to call this method - both (note: I got before calling methods one int value and 3 string values), i got error with description - incorrect syntacsis for "@mycarID" for first method and incorrect syntacsis for PetName for second variant. But if all parameters ok for string values - whats wrong for this one. Check also (as you can see abowe), that in table used the same types of variables.
Question number 2 - Where i wrong with this methods, and why i can't take int parameter?
Also during creating dll for working with my dataBase found, that I can't check my methods in Dll for functionality if I not use it with another project.
Additional question - is there are some method for checking methods during creating DLL, or I always must to create some "supporting" project for parallel cheking workability of my dll? Means how can i make debug step-by-step during creating?
Thnik a lot of question for one post, but put it together for reduction of explanation part for question in others posts.
In the first method change the last lines to:
cmd.ExecuteNonQuery();
CarPetName = (string)cmd.Parameters["@petName"].Value;
you are not executing the command and also note the @ before the parameter name.
In the second method you are keeping the parenthesis outside your query:
+ ("@mycarID, @Make, @Color, @PetName"));
should be
+ "(@mycarID, @Make, @Color, @PetName)"); // note the parenthesis inside the string
In the third method you are comparing CarId with a string
Where CarID = '{1}'
Should be
Where CarID = {1}
Well, it shouldn't: you should use parameters as you did before.
这篇关于连接级别数据库-如何编辑表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
