Introduction

In our chapter 8, we learned how to create a simple C# in the Console Project using Visual Studio to access to ASDW. In this new chapter, we will learn how to create a C# WPF project, how to work with a configuration file (app.config), how to work with stored procedures with ASDW, and how to create an account with less privileges than the ASDW administrator.

WPF (Windows Presentation Foundation) is a popular Microsoft graphical interface for desktop applications. It is trying to replace the Windows Forms. We will create a WPF application to insert data in ASDW.

Requirements

1. Visual Studio 2015 or later versions.
2. An Azure Account
3. SSMS 2017
4. We will use the table already created in chapter 8, the csharpsales table.
5. An Azure SQL Data Warehouse installed

Getting started

In Visual Studio, go to File>New>Project

Select the WPF Application:

In toolbox, drag and drop 3 textboxes in the design pane:

Add 3 labels in the design pane and label the columns ID, InvoiceID and Description:

Drag and drop a button and change the Content property to Insert:

Double click the Insert button to add code to the button. The following code will insert the data:

       private void button_Click(object sender, RoutedEventArgs e)
        {
            string connectionString = "Server=tcp:sqlcentralserver.database.windows.net,1433;Initial Catalog = sqlcentralwarehouse;Persist Security Info = False; User ID = daniel; Password =MysupperP#%&rd ;MultipleActiveResultSets=False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30";
            //T-SQL to create a table named csharpsales
            string query = "insert into csharpsales values(@id,@invoiceid,@description) ";
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(query, cn))
            {
                cmd.Parameters.AddWithValue("id", Convert.ToInt16(textBox1.Text));
                cmd.Parameters.AddWithValue("invoiceid", Convert.ToInt16(textBox2.Text));
                cmd.Parameters.AddWithValue("description", textBox3.Text);
                cn.Open();
                //Execute the T-SQL
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }

The connectionstring contains the information to connect to Azure SQL Data Warehouse (ASDW). Sqlcentralserver.database.windows.net is the ASDW Server name and the database name is sqlcentralwarehouse.

Add the following namespaces:

using System.Data;
using System.Data.SqlClient;

We will insert data into a table named csharpsales (see the requirements). In the textboxes, we will get the data to insert.

If we run the WPF application with F5 in Visual Studio, we will be able to write the values for the column id, Invoice ID and a description and press the insert button:

If everything is OK, you will be able to see the data inserted. We will try to connect to SSMS:

If we run the query, we will be able to see the new data inserted:

Working with Stored Procedures

It is a good practice to use stored procedures. Here you have some advantages:

• Stored procedures are secure (reduces the possibility of injections or other access). You can provide access only to the stored procedure and not to the table itself.
• Reduces the traffic because you are only sending the procedure name instead of all your query code.
• It is easier to understand and read the code, because you are separating the database layer and the business layer.
• The code is optimized.

In this new example, we will create a stored procedure instead of the code used before to insert data in the csharpsales table.

In SSMS, create a procedure in ASDW:

CREATE PROCEDURE [dbo].[insertcsharpsales]
(
 @id int,
 @invoiceid int,
 @description varchar(8000)
)
AS
INSERT INTO [dbo].[csharpsales]
           ([id]
           ,[invoiceid]
           ,[description])
     VALUES
           (@id,
           @invoiceid,
           @description)

The stored procedure named insertcsharpsales inserts the id, invoiceid and description into the csharpsales table. It uses the parameters @id, @invoiceid, @description.

To invoke the stored procedure in our WPF C# application, we will use the following code:

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            string connectionString = "Server=tcp:sqlcentralserver.database.windows.net,1433;Initial Catalog = sqlcentralwarehouse;Persist Security Info = False; User ID = daniel; Password =MysupperP#%&rd ;MultipleActiveResultSets=False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30";
            //T-SQL to create a table named csharpsales
            string query = "insertcsharpsales ";
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(query, cn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("id", Convert.ToInt16(textBox1.Text));
                cmd.Parameters.AddWithValue("invoiceid", Convert.ToInt16(textBox2.Text));
                cmd.Parameters.AddWithValue("description", textBox3.Text);
                cn.Open();
                //Execute the T-SQL
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }

The main difference is that we are invoking the procedure name instead of the T-SQL query. We are also specifying that the command type is stored procedure.

Working with Configuration Files

For connection information, it is better to store the connection information in a Configuration file in C#. This file is named app.config. We will store the information in the app.config file stored by default in the WPF projects:

The app.config is an XML file where you can store project settings. In this example, we will store 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
  <connectionStrings>
    <add name="sqlconnection"
    connectionString="Server=tcp:sqlcentralserver.database.windows.net,1433;Initial Catalog = sqlcentralwarehouse;Persist Security Info = False; User ID = daniel; Password =MysupperP#%&rd ;MultipleActiveResultSets=False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30"/>
  </connectionStrings>
</configuration>

We are adding the connection information to the app.config  file. The name of the property is sqlconnection. To work with the app.config file, it is necessary to add the System.configuration library: 

Also, make sure to include the configuration file namespace:

using System.Configuration;

The following code will get the information from the configuration file:

        private void button2_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                var connectionString = ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString;
                //T-SQL to create a table named csharpsales
                string query = "insertcsharpsales ";
                using (SqlConnection cn = new SqlConnection(connectionString))
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("id", Convert.ToInt16(textBox1.Text));
                    cmd.Parameters.AddWithValue("invoiceid", Convert.ToInt16(textBox2.Text));
                    cmd.Parameters.AddWithValue("description", textBox3.Text);
                    cn.Open();
                    //Execute the T-SQL
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }

In the following line, we are invoking the app.config file information:

var connectionString = ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString;

Using an account with less privileges

For security reasons, it is better to use an account with less privileges. We are going to create a login named test with a database user named test and grant values to the stored procedure only. We will first create in ASDW a login named test in SSMS:

CREATE LOGIN test
WITH PASSWORD = 'MySuperpwd123!!!' 
GO

CREATE USER test
FOR LOGIN test
WITH DEFAULT_SCHEMA =dbo
GO

GRANT EXECUTE ON OBJECT::  [dbo].[insertcsharpsales] TO test;

  <connectionStrings>
    <add name="sqlconnection"
    connectionString="Server=tcp:sqlcentralserver.database.windows.net,1433;Initial Catalog = sqlcentralwarehouse;Persist Security Info = False; User ID = test; Password =MySuperpwd123!!! ;MultipleActiveResultSets=False; Encrypt = True; TrustServerCertificate = False; Connection Timeout = 30"/>
  </connectionStrings>

Conclusion

In this article, we learned how to create our first WPF application to insert data into ASDW. We also learned how to work with stored procedures and how to store the information in a configuration file. 

Finally, we learned how to create an Azure SQL Login and database user and assign the permissions to the stored procedure.

References

• How to: Add an Application Configuration File to a C# Project
• How to: Execute a Stored Procedure that Returns Rows
• SQL SERVER – Stored Procedures Advantages and Best Advantage