Connect to SQL Server Stored procedure with ADO.NET

Question

Currently I am making one program and I want to connect to database.

I wonder how do I connect to database with ADO.NET concept.

There are few step what I generated. please follow below step.

1. Generate ValidateModel.edmx (ADO.NET).

   • Connect with MS SQL server already completely key-in Id and password.

2. Generate Windows application form and try to call.

anyone know some tip how to connect to SQL Server or some command.

I already try to connect to use Microsoft.Practices.EnterpriseLibrary.

However There are exception when it created database.

private string dbInstance = "testEntities";

try
{
   Database db = DatabaseFactory.CreateDatabase(dbInstance);
   DbCommand dbCommand = db.GetStoredProcCommand("GetUserNameByUserId");
   db.AddInParameter(dbCommand, "@UserId", DbType.String, sPIN);
   db.AddOutParameter(dbCommand, "@UserName", DbType.String, 30); //sTechnicianName
   sTechnicianName = db.ExecuteScalar(dbCommand).ToString();
}

and have TypeInitializationException.

that testEntities information setting in App.Config.

Answer 1

Here is an example using SqlConnection:

int result; 

using (SqlConnection conn = new SqlConnection("hereYourConnectionString"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("StoredProcedureName", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // input parameters 
        cmd.Parameters.Add(new SqlParameter("@paramName", paramValue));

        // output parameters
        SqlParameter retval = cmd.Parameters.Add("@returnValue", SqlDbType.Int); // assiming that the return type is an int
        retval.Direction = ParameterDirection.ReturnValue;

       cmd.ExecuteNonQuery();
       result = (int)cmd.Parameters["@returnValue"].Value; // assuming is an int
    }               
}

but be sure to check the Data Provider Factory Model