CODEWITHSUNDEEP
c#servicestack
NevrMore
NevrMore

Reputation: 203

Posting data to sql server using servicestack and c#

I'm just starting to learn servicestack and c# and I need some help with posting data to sql server. When I test using swagger I get a 200 response but nothing is actually being inserted into the database and I'm not sure where I'm going wrong.

Model.Type

public class Book
{
    [PrimaryKey]
    [AutoIncrement]
    public int BookID { get; set; }
    public string Author { get; set; }
    public string Title { get; set; }
    public int NumberOfPages { get; set; }
    public int Isbn { get; set; }
}

Manager Interface:

namespace ServiceStackServiceLog4NetTemplate.Interfaces.Managers
{
   public interface IPostBookManager
  {
    Book CreateBooks();
  }
}

Repository Interface: 

namespace ServiceStackServiceLog4NetTemplate.Interfaces.Repositories
{
   public interface IPostBookRepository
   {
     Book PostBooks();
   }
}

Messages.Request

namespace ServiceStackServiceLog4NetTemplate.ServiceModel.Messages
{
[Route("/PostBooks", Verbs = "POST")]
public class PostBooksRequest
{
    [AutoIncrement]
    public int BookID { get; set; }
    public string Author { get; set; }
    public string Title { get; set; }
    public int NumberOfPages { get; set; }
    public int Isbn { get; set; }
}
}

Messages.Response

namespace ServiceStackServiceLog4NetTemplate.ServiceModel.Messages
{
public class PostBooksResponse : IHasResponseStatus
{
    public Book Book { get; set; }
    public ResponseStatus ResponseStatus { get; set; }
}
}

Manager

    class PostBooksManager : IPostBookManager
    {
    private IPostBookRepository postRepo;

    public PostBooksManager(IPostBookRepository pRepo)
    {
        postRepo = pRepo;
    }

    public Book CreateBooks()
    {
        var bookCreations = postRepo.PostBooks();
        return bookCreations;
    }
}
}

Repository

namespace ServiceStackServiceLog4NetTemplate.Repositories
{
public class PostBookSqlRepo : IPostBookRepository
{
    private readonly string connection = ConfigurationManager.AppSettings["BooksDB"];

    public Book PostBooks()
    {
        var postBooks = CreateBooks();
        return postBooks;
    }

    private Book CreateBooks()
    {
        var newBooks = new Book();
        string query = "INSERT INTO dbo.BooksTable(BookID, Author, Title, NumberOfPages, ISBN)" +
                       "VALUES(@BookID, @Author, @Title, @NumberOfPages, @ISBN)";
        SqlConnection dbConnect = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand(query, dbConnect);


        using (dbConnect)
        {
            dbConnect.Open();


            var b = new Book()
            {
                BookID = newBooks.BookID,
                Author = newBooks.Author,
                Title = newBooks.Title,
                NumberOfPages = newBooks.NumberOfPages,
                Isbn = newBooks.Isbn
            };

            cmd.Parameters.AddWithValue("@BookID", b.BookID);
            cmd.Parameters.AddWithValue("@Author", b.Author);
            cmd.Parameters.AddWithValue("@Title", b.Title);
            cmd.Parameters.AddWithValue("@NumberOfPages", b.NumberOfPages);
            cmd.Parameters.AddWithValue("@ISBN", b.Isbn);


            dbConnect.Close();
        }

        return newBooks;

    }

}
}

Service Definition

namespace ServiceStackServiceLog4NetTemplate.ServiceDefinition
{
[EnableCors(allowedMethods: "GET,POST,DELETE")]
class PostBooksServiceDefinition : Service
{
    private IPostBookManager postManager;

    public PostBooksServiceDefinition(IPostBookManager bookP)
    {
        postManager = bookP;
    }

    public object Post(PostBooksRequest request)
    {
        var postBook = request.ConvertTo<Book>();

        PostBooksResponse resp = new PostBooksResponse()
        {
            Book = postBook
        };

        return resp;
    }
}
}

Upvotes: 3

Views: 73

Answers (1)

mythz
mythz

Reputation: 143339

First you shouldn't have [AutoIncrement] on your Request DTO, you're converting the DTO to a Book object which is the data model you're saving, not the Request DTO. (It doesn't have any impact on anything, it's just unnecessary and unused).

Secondly, you're using OrmLite Data Attributes in your Book data model but you're not using OrmLite to insert the record.

To use OrmLite to insert a new Book in your ServiceStack Services you can just use:

Db.Insert(postBook);

If you also need to create the Book RDBMS table, you can create it if it doesn't already exist with:

using (var db = dbFactory.Open())
{
    db.CreateTableIfNotExists<Book>();
}

If you haven't registered your OrmLiteConnectionFactory with ServiceStack, you can register it with:

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider));

See the docs on OrmLite project page for more info:

Using a Repository

If you want to use PostBookSqlRepo to save your books you should configure it with your IDbConnectionFactory, e.g:

public class PostBookSqlRepo : IPostBookRepository
{
    IDbConnectionFactory dbFactory;

    public PostBookSqlRepo(IDbConnectionFactory dbFactory)
    {
        this.dbFactory = dbFactory;
    }

//...

    public Book CreateBooks(Book book)
    {
        using (var db = dbFactory.OpenDbConnection())
        {
            db.Insert(book);
        }
    }
}

Which you can configure in your ServiceStack IOC with:

container.Register<IPostBookRepository>(c =>
    new PostBookSqlRepo(c.Resolve<IDbConnectionFactory>()));

Upvotes: 4

Related Questions