SQL Server - Adding Leading Zeros

noreply@blogger.com (Nataniel) — Fri, 12 Jul 2013 16:23:00 +0000

Well, sometimes you need to do some query in a numeric field and you need to add leading zeros for formatation purpose.

It's prety easy to do this! Take a look in the following query:

You'll use 'REPLICATE' to do the work here!

REPLICATE ( string_expression ,integer_expression )

Where string_expression is the string you want to replicate and integer_expression is the amount of times o want to replicate string_expression.

In my example:
string_expression: '0'. Wich means I want to replicate '0'.
integer_expression: 4 - LEN(CAST(id_user as varchar))). I subtract id_user's length from the amount of times that I want to replicate '0'.

And in the end I concatenate the result of REPLICATE function with the field I did all the work.

This is the example without REPLICATE:

This will be the result with REPLICATE:

Connection Class C# (SQLServer, Oracle, MySQL)

noreply@blogger.com (Nataniel) — Fri, 12 Jul 2013 12:27:00 +0000

What's up? Yeah, I'm a little lazy I know. It's been almost 2 years since I don't create new content to post in here. The time passed and now I don't really like Java that much :p

Now, I will show a example of connection in C# + SQLServer, C# + MySQL and C# + Oracle.

All the three are much alike (it will just change de library that you'll work with).

Here's a Class wich a created for connect with the three Data Bases:

Download Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data;

namespace Connection
{
    public class Conexao
    {
        #region globals

        private bool _State;                    // shows connections last state
        private string _ErrorMessage;           // shows last error message
        private int _ErrorNumber;               // shows last connections last error
        private bool _CompleteCommand;          // shows last command completed with success
        
        // constantes das strings de conexão do SqlServer, Oracle e MySQL
        public const string SQL_STR_CON = "yourConnectionString";
        private const string ORA_STR_CON = "yourConnectionString";
        private const string MYSQL_STR_CON = "yourConnectionString";

        #endregion

        #region properties

        public int ErrorNumber
        {
            get { return _ErrorNumber; }
        }
        public string ErrorDescription
        {
            get { return _ErrorMessage; }
        }
        public bool ConnectionState
        {
            get { return _State; }
        }
        public bool CompleteCommand
        {
            get { return _CompleteCommand; }
        }
        #endregion
       
        /// 
        /// Executes a sql command with no return. Uses Transaction. 
        /// 
        /// Query a ser executada. (INSERT, UPDATE, DELETE, TRUNCATE, DROP)
        /// SQLSERVER, ORACLE, MYSQL
        public void ExecutaNonQuery(string myQuery, string banco)
        {
            // Definir valores padrões das variaveis
            _ErrorMessage = "";
            _State = false;

            // Se não for informado comando T-SQL retorno error
            if (myQuery.Length > 0) // Se realmente foi passado um comando a ser executado
            {
                switch (banco)
                {
                    case "SQLSERVER":
                        SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);
                        SqlTransaction sqlTransaction;                            
                        sqlCon.Open();
                        sqlTransaction = sqlCon.BeginTransaction();
                        SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon, sqlTransaction);
                        try
                        {
                            sqlCommand.ExecuteNonQuery();
                            sqlTransaction.Commit();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (SqlException sqlEx)
                        {
                            sqlTransaction.Rollback();
                            _ErrorMessage = sqlEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = sqlEx.GetHashCode();
                            _CompleteCommand = false;
                        }                                                                                    
                        sqlCon.Close();
                        sqlCommand.Dispose();
                        break;
                    case "ORACLE":
                        OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
                        OracleTransaction oraTransaction;
                        oraCon.Open();
                        oraTransaction = oraCon.BeginTransaction();
                        OracleCommand oraCommand = new OracleCommand(myQuery, oraCon, oraTransaction);
                        try
                        {
                            oraCommand.ExecuteNonQuery();
                            oraTransaction.Commit();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (OracleException oraEx)
                        {
                            oraTransaction.Rollback();
                            _ErrorMessage = oraEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = oraEx.GetHashCode();
                            _CompleteCommand = false;
                        }                            
                        oraCon.Close();
                        oraCommand.Dispose();
                        break;
                    case "MYSQL":
                        MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
                        MySqlTransaction mySqlTransaction;
                        mySqlCon.Open();
                        mySqlTransaction = mySqlCon.BeginTransaction();
                        MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon, mySqlTransaction);
                        try
                        {
                            mySqlCommand.ExecuteNonQuery();
                            mySqlTransaction.Commit();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (MySqlException myEx)
                        {
                            mySqlTransaction.Rollback();
                            _ErrorMessage = myEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = myEx.GetHashCode();
                            _CompleteCommand = false;
                        }                            
                        mySqlCon.Close();
                        mySqlCommand.Dispose();
                        break;
                    default:
                        _ErrorMessage = "BANCO INFORMADO INVÁLIDO";
                        break;
                }                                    
            }
        }

        /// 
        /// Executes a sql query.
        /// 
        /// Query.
        /// SQLSERVER, ORACLE, MYSQL
        /// DataTable
        public DataTable ExecutaReader(string myQuery, string banco)
        {
            DataTable dtResult = new DataTable();
            _ErrorMessage = "";
            _State = false;
            if (myQuery.Length > 0)
            {
                switch (banco)
                {
                    case "SQLSERVER":
                        SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);                           
                        sqlCon.Open();
                        SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon);
                        try
                        {
                            SqlServerReader = sqlCommand.ExecuteReader();
                            dtResult.Load(SqlServerReader);
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (SqlException sqlEx)
                        {
                            _ErrorMessage = sqlEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = sqlEx.GetHashCode();
                            _CompleteCommand = false;
                        }                                                                                    
                        sqlCon.Close();
                        sqlCommand.Dispose();
                        break;
                    case "ORACLE":
                        OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
                        oraCon.Open();
                        OracleCommand oraCommand = new OracleCommand(myQuery, oraCon);
                        try
                        {
                            OraReader = oraCommand.ExecuteReader();
                            dtResult.Load(OraReader);
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (OracleException oraEx)
                        {
                            _ErrorMessage = oraEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = oraEx.GetHashCode();
                            _CompleteCommand = false;
                        }                            
                        oraCon.Close();
                        oraCommand.Dispose();
                        break;
                    case "MYSQL":
                        MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
                        mySqlCon.Open();
                        MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon);
                        try
                        {
                            MysqlReader = mySqlCommand.ExecuteReader();
                            dtResult.Load(MysqlReader);
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (MySqlException myEx)
                        {
                            _ErrorMessage = myEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = myEx.GetHashCode();
                            _CompleteCommand = false;
                        }                            
                        mySqlCon.Close();
                        mySqlCommand.Dispose();
                        break;
                    default:
                        _ErrorMessage = "BANCO INFORMADO INVÁLIDO";
                        break;
                }
            }
            return dtResult;
        }

        /// 
        /// Sql query with a single return.
        /// 
        /// Query
        /// SQLSERVER, ORACLE, MYSQL
        /// Object
        public Object ExecutaScalar(string myQuery, string banco)
        {
            Object objResult = new Object();
            _ErrorMessage = "";
            _State = false;
            if (myQuery.Length > 0)
            {
                switch (banco)
                {
                    case "SQLSERVER":
                        SqlConnection sqlCon = new SqlConnection(SQL_STR_CON);
                        sqlCon.Open();
                        SqlCommand sqlCommand = new SqlCommand(myQuery, sqlCon);
                        try
                        {
                            objResult = sqlCommand.ExecuteScalar();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (SqlException sqlEx)
                        {
                            _ErrorMessage = sqlEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = sqlEx.GetHashCode();
                            _CompleteCommand = false;
                        }
                        sqlCon.Close();
                        sqlCommand.Dispose();
                        break;
                    case "ORACLE":
                        OracleConnection oraCon = new OracleConnection(ORA_STR_CON);
                        oraCon.Open();
                        OracleCommand oraCommand = new OracleCommand(myQuery, oraCon);
                        try
                        {
                            objResult = oraCommand.ExecuteScalar();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (OracleException oraEx)
                        {
                            _ErrorMessage = oraEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = oraEx.GetHashCode();
                            _CompleteCommand = false;
                        }
                        oraCon.Close();
                        oraCommand.Dispose();
                        break;
                    case "MYSQL":
                        MySqlConnection mySqlCon = new MySqlConnection(MYSQL_STR_CON);
                        mySqlCon.Open();
                        MySqlCommand mySqlCommand = new MySqlCommand(myQuery, mySqlCon);
                        try
                        {
                            objResult = mySqlCommand.ExecuteScalar();
                            _State = true;
                            _CompleteCommand = true;
                        }
                        catch (MySqlException myEx)
                        {
                            _ErrorMessage = myEx.Message.ToString();
                            _State = false;
                            _ErrorNumber = myEx.GetHashCode();
                            _CompleteCommand = false;
                        }
                        mySqlCon.Close();
                        mySqlCommand.Dispose();
                        break;
                    default:
                        _ErrorMessage = "BANCO INFORMADO INVÁLIDO";
                        break;
                }
            }
            return objResult;
        }
    }
}

Connecting Java+MySQL [Java]

noreply@blogger.com (Nataniel) — Tue, 23 Aug 2011 05:24:00 +0000

Ahoy!

This post will explain how to connect MySQL to your application Java .

(The IDE used was NetBeans 7.0.1)

First of all, you need to certificate if you have the correct library for this; here, I will use the J Connector(JDBC); for check if you have or not this library:

Right Click on "Libraries" in a project that you already created, click "Add Library", and search for "mysql jdbc driver" or something that looks like this.

Don't have the library? Just go here, and download it.

After the download ends, repeat the previous steps but instead of click "Add Library", click "Add JAR/Folder" and then select the file that you downloaded

And now, a simple code for connection:

Prograstinate: Pick up your Towels

SQL Server - Adding Leading Zeros

Connection Class C# (SQLServer, Oracle, MySQL)

Connecting Java+MySQL [Java]