ADO.NET how to add parameter if I use connection class in a separate file?

Question

ADO.NET how to add parameter if I use class in a separate file?

Class:

class SQLconnect
{
    public static void Sql(string Command_Text)
    {
        string connectionPath =
            "Data Source=USER\\SQLEXPRESS;Initial Catalog=db;Integrated Security=SSPI;";

        SqlConnection Connection = new SqlConnection(connectionPath);

        Connection.Open();

        SqlCommand Command = Connection.CreateCommand();
        Command.CommandText = Command_Text;
        Command.ExecuteNonQuery();

        Connection.Close();
    }
}

Parameter:

        SQLconnect.Sql("INSERT INTO [dbo].[work] ([name],[code])VALUES(@name, @code)");

        SqlParameter param = new SqlParameter();
        param.ParameterName = "@name";
        param.Value = nameTextBox.Text;
        param.SqlDbType = SqlDbType.Text;
        //   Parameters.Add(param);


        param = new SqlParameter();
        param.ParameterName = "@code";
        param.Value = codeTextBox.Text;
        param.SqlDbType = SqlDbType.Text;
        //   Parameters.Add(param);

Peter · Accepted Answer · 2012-05-23 11:48:01Z

2

One option would be to update your SqlConnect.Sql() method to accept a set of parameters:

class SQLconnect
{
    public static void Sql(string Command_Text, params SqlParameter[] parameters)
    {
        string connectionPath =
            "Data Source=USER\\SQLEXPRESS;Initial Catalog=db;Integrated Security=SSPI;";

        SqlConnection Connection = new SqlConnection(connectionPath);

        Connection.Open();

        SqlCommand Command = Connection.CreateCommand();
        Command.CommandText = Command_Text;

        if(parameters != null && parameters.Length > 0) 
        {
          foreach(var p in parameters)
            Command.Parameters.Add(p);
        }


        Command.ExecuteNonQuery();

        Connection.Close();
    }
}

Then your calling code would be something like this:

    SqlParameter param1 = new SqlParameter();
    param1.ParameterName = "@name";
    param1.Value = nameTextBox.Text;
    param1.SqlDbType = SqlDbType.Text;

    param2 = new SqlParameter();
    param2.ParameterName = "@code";
    param2.Value = codeTextBox.Text;
    param2.SqlDbType = SqlDbType.Text;

    SQLconnect.Sql("INSERT INTO [dbo].[work] ([name],[code])VALUES(@name, @code)", param1, param2);

edited May 23, 2012 at 11:48

answered May 23, 2012 at 11:42

Peter

12.8k4 gold badges36 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Taylor Over a year ago

SQLconnect.Sql_gs("INSERT INTO [dbo].[work] ([name],[code])VALUES(@name, @code)",param1,param2); None of the overloads of the method "Sql" not accept "3" argument

Peter Over a year ago

Does your Sql() method signature look like this: public static void Sql(string Command_Text, params SqlParameter[] parameters) Note the params keyword...

Apocatastasis Over a year ago

Well, if the other class is in other library, you need to add a reference to SqlClient in the other library with the solely purpose of pass the SqlParameter(). Must be another way to do this.

HatSoft · Accepted Answer · 2012-05-23 12:06:52Z

Here is my version hope it helps

public class SqlConnect
{
    public string ConnectionString { get; private set; }
    public string CommandText { get; private set; }
    public SqlParameterCollection Parameters { get; private set; }

    public SqlConnect(string connectionString, string commandText)
    {
        ConnectionString = connectionString;
        CommandText = commandText;
        Parameters = null;
    }

    public SqlConnect(string connectionString, string commandText, SqlParameterCollection parameters)
        : this(connectionString, commandText)
    {
        Parameters = parameters;
    }

    public int Execute()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = CommandText;

            foreach (var sqlParameter in Parameters)
            {
                command.Parameters.Add(sqlParameter);
            }

            int rowsAffected = command.ExecuteNonQuery();
            connection.Close();
            return rowsAffected;
        }
    }
}

Chetan S · Accepted Answer · 2012-05-23 11:37:45Z

You can create a separate class for Database connection...then create a properties for each parameter like follows

public class StudDataAccess
{
    public string connectionString = "Data Source=USER\\SQLEXPRESS;Initial Catalog=db;Integrated Security=SSPI;";
    public int pStudId
    {
        set;
        get;
    }
    public string pStudName
    {
        set;
        get;
    } 
    public void NewStudent()
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand cmd = new SqlCommand("AddStudent", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@StudId", SqlDbType.Int).Value = pStudId;
        cmd.Parameters.Add("@StudName", SqlDbType.VarChar, 50).Value = pStudName;            
        cmd.Prepare();
        cmd.ExecuteNonQuery();
        conn.Close();
    }  
}

Here we created two parameters id ,name so you can set values of it from the class where you need to insert,select data. as follows by creating object of that class

        StudDataAccess objDataAccess = new StudDataAccess();
        objDataAccess.pStudId =Convert.ToInt32(TextBox1.Text);
        objDataAccess.pStudName = TextBox2.Text;           
        objDataAccess.NewStudent(); //Call addstudent store procedure with parameters

Collectives™ on Stack Overflow

ADO.NET how to add parameter if I use connection class in a separate file?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related