connection level dataBase - how to edit tables

Question

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:

1. 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.

Answer 1

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.