How to connect an Sql server database in a user control in Umbraco?

Question

I am a newbie in Umbraco. I am trying to develop a custom registration page in umbraco using dot net User control. For that, I have created a custom table named "registerTable" in umbraco database. I just want to insert the data in to that table using the Usercontrol. . The connection string "CM_Connection" is in the Webconfig file.

This is the code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace thesis
{
       public partial class test : System.Web.UI.UserControl
       {
          protected void Page_Load(object sender, EventArgs e)
          {
          }

        protected void Button1_Click(object sender, EventArgs e)
        {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    Guid guid;
                    guid = Guid.NewGuid();
                    string sql = "INSERT INTO registerTable (Firstname) VALUES (@Name)";
                    cmd.Parameters.AddWithValue("@Name", TextBox1.Text.Trim() );
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    con.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                        Label1.Text = "Registered successfully.";
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
        }
    }
}

What is your issue? Do you get an exception? Your code should work out of the box. — Martijn van der Put
the code is working perfect when we put it to an .aspx page. But its not working when we implement the code in to a dot net control page in Umbraco. — Manu

Dave Gordon Dave Gordon · Accepted Answer · 2014-06-26T15:48:11

This will get you going - this code is a MACRO within Umbraco terms. IT just gets the current database size - but you can change the query as you like of course.

@using Umbraco.Core
    <div>
    @{
        var context = ApplicationContext.Current;

        var databaseContext = context.DatabaseContext;

        databaseContext.Database.OpenSharedConnection();

        var db = ApplicationContext.Current.DatabaseContext.Database;

        var sql = "SELECT (SUM(reserved_page_count) * 8192)  FROM sys.dm_db_partition_stats";
        var result = db.ExecuteScalar<int>(sql);        
}

@if (databaseContext.Database.Connection.State == System.Data.ConnectionState.Open)
    {
    <span> Database is Open </span>
    <span> Size: @result</span>
    }
else
    {
    <span> Database is Closed</span>
    }

Basically all you are doing is

Getting the Application Context
Getting the Database Context
Opening The Database
Executing your Query
Display the Result

You need to use Umbraco.Core for this.

How to connect an Sql server database in a user control in Umbraco?

1 Answers