如何在vs2010中做到这一点 [英] How to do it in vs2010
问题描述
我有以下成功的SQL查询，但由于我是vs2010的初学者，我不知道我应该如何在vs2010中编写这个查询，以便我可以从UI成功插入数据....如果有人可以提供帮助我???? plz
i have following successful sql query but as i am a beginner of vs2010 i am not getting how exactly this query should i have to write in vs2010 so that i can insert the data successfully from UI....If anybody can help me????plz
DECLARE @CategoryName VARCHAR(200),
@Category_id INT
SET @CategoryName='singh1';
INSERT INTO tblCategory(CategoryName)
SELECT @CategoryName;
SELECT @Category_id= SCOPE_IDENTITY();
INSERT INTO tblProduct(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'rahul', @Category_id , 'verma', '15', '500';
------------------------------------------------- ----------------------------------
:::改进:::
1： - 这是用户界面表单，我试图从用户那里获取数据
-----------------------------------------------------------------------------------
:::improvement:::
1:-- this is user interface form where i am trying to get data from user
private void btnSave_Click(object sender, EventArgs e)
{
try
{
Product obj_product = new Product();
obj_product.PName = txtProductName.Text;
obj_product.PCompany = txtCompanyName.Text;
obj_product.PUnitPrice = txtUnitPrice.Text;
obj_product.PQty = Convert.ToInt32(txtQty.Text);
//SetCategory();
obj_product.Save();
MessageBox.Show("Record Saved Successfully");
LoadProduct();
ClearScreen();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
------------------------- -------------------------------------------------
2： - 这是中间层
--------------------------------------------------------------------------
2:- this is middle tier
public void Save()
{
clsSqlServer obj_sql = new clsSqlServer();
obj_sql.InsertProduct( _PName,
_PCategory_FK,
_PCompany,
_PUnitPrice,
_PQty);
}
---------------------- -------------------------------------------------- ---
3： - 这是数据访问层
---------------------------------------------------------------------------
3:- this is data access tier
public bool InsertProduct(string P_Name , int P_Cat_Fk , string P_Company, string P_Price, int P_Qty)
{
string con = ConfigurationManager.ConnectionStrings["InvoiceDB"].ToString();
SqlConnection obj_connection = new SqlConnection(con);
obj_connection.Open();
try
{
string sqlInsert = "'DECLARE @CategoryName VARCHAR(200),'" +
"@Category_id INT " +
"'SET @CategoryName='singh1';'" +
"'INSERT INTO tblCategory(CategoryName)'" +
"'SELECT @CategoryName;'" +
"'SELECT @Category_id= SCOPE_IDENTITY();'" +
"'INSERT INTO tblProduct(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)'" +
"'SELECT 'rahul', @Category_id , 'verma', '15', '50'";
// string sqlInsert = "insert into tblProduct (Category_id_FK)" + "values('" + P_Name + "','" + P_Company + "','" + P_Price + "'," + P_Qty + ")" + "SELECT Category_id FROM tblCategory WHERE (Category_id = Category_id)";
//string sqlInsert = "insert into tblProduct (ProductName,Category_id_FK,ProductCompany,ProductPrice,ProductQty)" + "values('" + P_Name + "'," + "SELECT Category_id FROM tblCategory WHERE (Category_id = Category_id)" + ",'" + P_Company + "','" + P_Price + "'," + P_Qty + ")";
SqlCommand obj_SqlCommand = new SqlCommand(sqlInsert, obj_connection);
obj_SqlCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
obj_connection.Close();
}
}
------------- -------------------------------------------------- ----------------
i我没有得到如何将这些全部传达给每个人其他...我试图在很多方面做到这一点，但没有成功..如果有任何身体可以帮助我plz
-------------------------------------------------------------------------------
i am not getting how to communicate these all to each other...i tried to do this in many ways but not get succeed.. if any body can help me plz
推荐答案
你需要传递方法参数到查询作为查询参数，并使用这些参数来插入数据，而不是使用硬编码值。
由于您传递了类别ID，查询的第一部分是无关紧要的。您不需要插入新类别并检索其ID，因为您已经拥有了要使用的类别ID。
包裹您的SqlConnection和SqlCommand中的对象使用块以确保它们已被清理在所有情况下都适当。
不要捕获基数异常类型;只捕捉你可以处理的例外。在这种情况下，您应该只捕获SqlException。您还应记录或显示异常的详细信息，以便在方法返回false时知道出了什么问题。
这样的事情应该有效：
You need to pass the method parameters to the query as query parameter, and use those parameters to insert the data, rather than using hard-coded values.
Since you're passing in the category ID, the first part of your query is irrelevant. You don't need to insert a new category and retrieve its ID, since you already have the category ID you want to use.
Wrap yourSqlConnectionandSqlCommandobjects in ausingblock to make sure they're cleaned up properly in all cases.
Don't catch the baseExceptiontype; only catch the exceptions you can handle. In this case, you should only be catchingSqlException. You should also be logging or displaying the details of the exception, so that you know what went wrong when your method returnsfalse.
Something like this should work:
public bool InsertProduct(string productName, int categoryId, string companyName, string price, int qty)
{
const string Query = @"INSERT INTO tblProduct (ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
VALUES (@ProductName, @CategoryID, @ProductCompany, @ProductPrice, @ProductQty);";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["InvoiceDB"].ConnectionString))
using (SqlCommand command = new SqlCommand(Query, connection))
{
command.Parameters.AddWithValue("@ProductName", productName);
command.Parameters.AddWithValue("@CategoryID", categoryId);
command.Parameters.AddWithValue("@ProductCompany", companyName);
command.Parameters.AddWithValue("@ProductPrice", price);
command.Parameters.AddWithValue("@ProductQty", qty);
try
{
connection.Open();
command.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
// TODO: Log or display the error somewhere.
return false;
}
}
}
这篇关于如何在vs2010中做到这一点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
