如何调用现有的store proc并将数据从datatable插入数据库？ [英] How do I call existing store proc and insert data in to database from datatable?

问题描述

大家好，

我的问题很简单，但我不知道我的错误

今天无法让我的大脑工作!!

好吧我在我的sql中创建了一个存储过程。

我有三个数据表，每个颜色值都是从我的sqldb中检索到的。

现在使用我需要的存储过程更新我的数据库表中的一行。

我调用了我的商店proc，但是在我的代码的后续阶段我感到困惑。

请帮助我

SqlCommand Cmd3 =新的SqlCommand（abc，连接）;

Cmd3.CommandType = CommandType.StoredProcedure;

SqlParameter xyz = Cmd3.Parameters.Add （@ xyz，dr0 [xyz]）;



我的问题是

1）如何插入coloumn值我需要更新表中的xyz。

我将xyz值存储在datatable dt中，dr0是datable中的数据行。

我只是需要帮助语法。

谢谢

Lia

Hello everyone,
My question is pretty simple but I dont know whats wrong with me
couldnt get my brain working today!!
Okay I created a stored procedure in my sql.
I have three datatables with one coloumn values each which i retrieved from my sqldb.
Now using the store proc I need to update a row in my database table.
I called my store proc but I am getting confusion at the following stage in my code.
Kindly help me out
SqlCommand Cmd3 = new SqlCommand("abc", connection);
Cmd3.CommandType = CommandType.StoredProcedure;
SqlParameter xyz = Cmd3.Parameters.Add("@xyz",dr0["xyz"]);

My question is
1)How do I insert the coloumn value xyz in the table which I need to be updated .
I stored the xyz values in datatable dt and dr0 is the datarow in datable here.
I just need help with the syntax.
Thanks
Lia

推荐答案

这是便宜的自制数据访问层的一个例子但是我强烈建议你研究第三方工具，例如：NHibernate，NHydrate或Entity Framework等。



你需要添加输入参数您的存储过程，然后通过应用程序传递，如下所示：

This is an example of a cheap homemade data access layer however I'd strongly suggest you look into a third party tool such as: NHibernate, NHydrate or Entity Framework, etc.

You'll need to add input parameters in your stored procedure and then pass those in via the application like so:

// If you're not using a QueryReader then 
// you can create an 'AppDataAccess' singleton 
class SomeDataAccess : AppDataAccess
{

    public int SomeDataAccessMethod(int index, string value)
    {
        return ExecuteNonQuery("procNameHere",
                new SqlParameter("@index", index),
                new SqlParameter("@value", value));
    }
}
// This could be a data access helper. 
internal class AppDataAccess
{
        public string ConnectionString
        {
            get
            {
                return "Your full connection string here";
// OR If grab it from the web.config (or other config file)
// return ConfigurationManager.ConnectionStrings["dbconnstringname"].ConnectionString;
            }
        }



    public int ExecuteNonQuery(string storedProcedure, params SqlParameter[] sqlParameters)
    {
        SqlConnection connection = new SqlConnection(ConnectionString);
        SqlCommand command = new SqlCommand(storedProcedure, connection);
        command.CommandType = CommandType.StoredProcedure;

        if (sqlParameters != null)
        {
            foreach (SqlParameter parameter in sqlParameters)
            {
                command.Parameters.Add(parameter);
            }
        }

        int rows = -1;

        try
        {
            connection.Open();
            rows = command.ExecuteNonQuery();
            connection.Close();
        }
        catch (SqlException ex)
        {
            // Log Exception
        }
        finally
        {
            connection.Dispose();
            command.Dispose();
        }

        return rows;
    }
}

希望这有帮助，



Chad

Hope this helps,

Chad

这篇关于如何调用现有的store proc并将数据从datatable插入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！