Convert my SQL to LINQ

Question

hi

this is my code that I write it but I want to use LINQ , can you help me for conver this code to LINQ ?

/// <summary>
/// Stores the request in the PublisherRequests table and
/// the selected categories in the PubReq_Categories table.
/// </summary>
/// <param name="UserId">The user's ID logged in.</param>
/// <param name="Url">URL of the site that you want show the banner on it.</param>
/// <param name="TypeId">The type id of the banner type you want to show.</param>
/// <param name="CategoryIds">A set of category IDs depend on your banner.</param>
/// <returns></returns>

public static int PutRequest(string UserId,string Url,int TypeId,int[] CategoryIds)
{
 int reqId;

 using ( SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString) )
 {
  //Retrieves the last RequestId from database.
        string selectLastReq = "SELECT TOP 1 RequestId FROM PublisherRequests WHERE UserId=" +
            UserId.ToString() + "ORDER BY RequestId DESC";
  SqlCommand cmdSelect = new SqlCommand(selectLastReq, cnn);

  //For inserting the new request in the database.
  string insertRequest = string.Format("INSERT INTO PublisherRequests(" +
   "RequestId, UserId,PageUrl, TypeId) VALUES ('{0}','{1}',{2})",
   UserId, Url, TypeId);
  SqlCommand cmdInsert = new SqlCommand(insertRequest, cnn);

  //For inserting each category for the request on a new row.
  string insertReqCat = string.Format("INSERT INTO PubReq_Categories(" +
   "RequestId, CategoryId) VALUES (@ReqId,@CatId)");
  SqlCommand cmdInsertReqCat = new SqlCommand(insertReqCat, cnn);
  cmdInsertReqCat.Parameters.Add("@ReqId", SqlDbType.Int);
  cmdInsertReqCat.Parameters.Add("@CatId", SqlDbType.Int);

  cnn.Open();

  //New request insertion.
  cmdInsert.ExecuteNonQuery();

  //Retrieve last request number.
  reqId = Convert.ToInt32(cmdSelect.ExecuteScalar());

  //Categories insertion.
  cmdInsertReqCat.Parameters["@ReqId"].Value = reqId;
  foreach ( int catId in CategoryIds )
  {
   cmdInsertReqCat.Parameters["@CatId"].Value = catId;
   cmdInsertReqCat.ExecuteNonQuery();
  }
 }

 return reqId;
}

Answer 1

Assuming that you're using the designer, that the RequestId column is auto-generated, you have an association between the two classes, and your classes are named as below, I think this will work.

using (var context = new DBDataContext())
{
    var request = new PublisherRequest
                  {
                     UserId = UserId,
                     PageUrl = Url,
                     TypeId = TypeId
                  };

    for (var categoryId in CategoryIds)
    {
         request.PubReqCategories.Add( new PubReqCategory
                                       {
                                           CategoryId = categoryId 
                                       } );
    }

    context.PublisherRequests.InsertOnSubmit( request );
    context.SubmitChanges();
}