Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Denis 47 posts 68 karma points
    May 12, 2012 @ 20:03
    Denis
    0

    Razor ans SQL commands

    Is it possible with razor scripts manipulating sql data from database ?

    Somthing like:

    @using something ...
    @{

        var db Database.Open("umbraco_db");
        var insert "INSERT INTO umbracoLog somthiing ...";
        var select "SELECT * FROM umbracoLog";

     

        db.Execute(insert)
    }

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  • Grant Thomas 291 posts 323 karma points
    May 12, 2012 @ 20:51
    Grant Thomas
    0

    Yes, you can without issues (maybe only a lack of Intellisense). Did you try this?

  • Denis 47 posts 68 karma points
    May 13, 2012 @ 10:05
    Denis
    0

    This example is not working, i use mssql (not compact) database. I have error that Database is not existing.

    Is it there any example how can i insert data to database, with connection settings and working commands ?

    Please help ...

  • Jonas Eriksson 930 posts 1825 karma points
    May 13, 2012 @ 11:31
    Jonas Eriksson
    0

    Db.Open(s) works if you have added a connectionstring in your web.config with that name (umbraco_db)

    You can specify regular connection string using OpenConnectionString instead. And if you for example want to re-use the umbraco connection string you can do:

     

    var umbracoDbConnectionString = System.Configuration.ConfigurationManager.AppSettings["umbracoDbDSN"];
    using (var db = Database.OpenConnectionString(umbracoDbConnectionString,"System.Data.SqlClient"))
    {
    
      db...
    
    }
    
  • Denis 47 posts 68 karma points
    May 13, 2012 @ 11:40
    Denis
    0

    wwwroot\macroScripts\634725060163618433_systemLogVisitors.cshtml(2): error CS0103: The name 'Database' does not exist in the current context

  • Jonas Eriksson 930 posts 1825 karma points
    May 13, 2012 @ 11:42
    Jonas Eriksson
    0

    Ah, I thought you already had the WebMatrix.Data, check out http://joeriks.com/2011/01/23/data-the-easy-way-webmatrix-data-in-umbraco/

    HTH

  • Grant Thomas 291 posts 323 karma points
    May 13, 2012 @ 11:45
    Grant Thomas
    0

    Denis, the point is that yes of course you can do this - it doesn't mean it will automatically add all required dependencies and configure connection strings for you. You still have to write the code properly, add the dependencies and configure any configurables.

    Just pasting that code into a template Console application wouldn't work, either, until you managed it accordingly.

    But the answer is still yes: if you know what you're doing, yes you can do this.

  • Denis 47 posts 68 karma points
    May 13, 2012 @ 11:54
    Denis
    0

    ok, i have allredy custom connection string, but this webmatrix maybe wont work on live win 7 server with mssql !?!

    i have try to ad namespace webmatrix, but not found ..

    can i ask you one thing more, can i put c# script in cshtml, somthing like;

    <script language="cs" runat="server">
        void Page_Load(object senderSystem.EventArgs e
        {
            Response.Write("ASDAASD");
        }     
    </script>

    this is also no working ...

    in 4.7.2 if ii use ascx i have error message 

    System.ArgumentException: The relative virtual path 'usercontrols/aaSystemLogVisitors.ascx' is not allowed here. at System.Web.VirtualPath.FailIfRelativePath() at System.Web.Hosting.HostingEnvironment.MapPathActual(VirtualPath virtualPath, Boolean permitNull) at System.Web.Hosting.HostingEnvironment.MapPathInternal(VirtualPath virtualPath) at System.Web.Hosting.HostingEnvironment.MapPath(VirtualPath virtualPath) at umbraco.IO.IOHelper.MapPath(String path, Boolean useHttpContext) at umbraco.IO.IOHelper.ValidateEditPath(String filePath, String validDir) at umbraco.developer.assemblyBrowser.Page_Load(Object sender, EventArgs e)

    At this time, i want to develop simple script, at any way (c# script or razor) for log visitor page views ...

     

  • Jonas Eriksson 930 posts 1825 karma points
    May 13, 2012 @ 12:11
    Jonas Eriksson
    1

    You need to add the WebMatrix.Data.dll to your /bin

    That said, many prefer PetaPoco over WebMatrix.Data. I know I do. And since you dont already have WebMatrix.Data it's just as simple to add PetaPoco instead:

    To do that - drop the .cs ( 
    https://github.com/toptensoftware/PetaPoco/blob/master/PetaPoco/PetaPoco.cs ) to your /App_Code folder. Then you can do this in your razor scripts:

    @{
    var myDb = new PetaPoco.Database(my-connection-string-name);
    var keyName = "PageCounter";
    myDb.Execute("UPDATE MyStatisticsTable SET SomeValue=SomeValue+1 WHERE SomeKey=@0",keyName);
    var counter = myDb.ExecuteScalar("SELECT SomeValue FROM MyStatisticsTable WHERE SomeKey=@0",keyName)

    <p>Page counter: @counter</p>

     

    or

    @{
    var myDb = new PetaPoco.Database(connectionstring);
    var someResult = myDb.Fetch("SELECT * FROM MyTable WHERE SomeColumn=@0", "Some value");


    <ul>
    @foreach(var row in someResult)
    {
    <li>
    @row.SomeOtherColumn 
    </li>
    }
    </ul> 


     

  • Grant Thomas 291 posts 323 karma points
    May 13, 2012 @ 12:12
    Grant Thomas
    0

    To my knowledge (whch isn't that much when it comes to Razor, granted) you can't add runat=server scripts like that and it would seem pointless because it's mixing the statelessness of ASP.NET MVC with the state-maintainedness of ASP.NET WebForms, but you can define a 'functions' section to house any number of methods then have the body or other sections utilise them.

    Because Umbraco does allow you to mix the two, you can do this in your Templates.

  • Denis 47 posts 68 karma points
    May 13, 2012 @ 12:17
    Denis
    0

    Thank You very much for help mr. Erikkson, Umbraco and Support is THE BEST :-)

  • Jonas Eriksson 930 posts 1825 karma points
    May 13, 2012 @ 13:07
    Jonas Eriksson
    0

    cool :)

    I edited my answer, and unfortunately some code was removed in the edit. PetaPoco needs some type defs to work properly:

     

    var counter = myDb.ExecuteScalar<int>("...
    ...
    var someResult = myDb.Fetch<dynamic>("...

     

  • keilo 558 posts 1013 karma points
    Nov 06, 2015 @ 04:16
    keilo
    0

    Hi Jonas

    I came across this post when looking for the simple way to make calls to an external database with v7.2, even though its dated and i wasnt sure if putting petapoco.cs inside the App_Code will create a conflict (petapoco is built-in now i believe),

    I have simply copied the petapoco.cs to appcode and use .Fetch

    Not sure if its still relavant, like creating a potential conflict on PetaPoco that ships with Umbraco 7.1 or 7.2, but it works!

    Thank you for sharing this!

  • Denis 47 posts 68 karma points
    May 13, 2012 @ 14:35
    Denis
    0

    Thanks mr. Grant

Please Sign in or register to post replies

Write your reply to:

Draft