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);
                    }
                }
        }
    }
}

Answer 1

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

1. Getting the Application Context
2. Getting the Database Context
3. Opening The Database
4. Executing your Query
5. Display the Result

You need to use Umbraco.Core for this.