268 lines
10 KiB
C#
268 lines
10 KiB
C#
using System;
|
|
using System.Data.SqlClient;
|
|
using System.Collections;
|
|
using System.Collections.Generic;
|
|
using System.Data.SqlTypes;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
using MarketData.DataAccess;
|
|
|
|
namespace MarketData.Utils
|
|
{
|
|
/// <summary>SQlUtils - SQL utility class</summary>
|
|
public class SqlUtils
|
|
{
|
|
public static int COMMAND_TIMEOUT=300;
|
|
public static int MIN_POOL_SIZE=10;
|
|
public static int MAX_POOL_SIZE=50;
|
|
public static string COLLATION="utf8mb4_unicode_ci";
|
|
|
|
/// <summary>CreateMySqlConnection - Creates an SQL connection.</summary>
|
|
/// <param name="datasource">datasource.</param>
|
|
/// <param name="database">database.</param>
|
|
/// <param name="username">username.</param>
|
|
/// <param name="password">users password.</param>
|
|
/// <returns>SqlConnection or null on error.</returns>
|
|
public static MySqlConnection CreateMySqlConnection(string datasource,string port,string database,string username,string password)
|
|
{
|
|
lock(typeof(SqlUtils))
|
|
{
|
|
try
|
|
{
|
|
String connectionString=null;
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("server=").Append(datasource).Append("; ");
|
|
sb.Append("user id=").Append(username).Append("; ");
|
|
sb.Append("password=").Append(password).Append("; ");
|
|
sb.Append("database=").Append(database).Append("; ");
|
|
sb.Append("SslMode=").Append("None").Append("; ");
|
|
sb.Append("pooling=").Append("true").Append(";");
|
|
sb.Append("MinimumPoolSize=").Append(MIN_POOL_SIZE).Append(";");
|
|
sb.Append("MaximumPoolSize=").Append(MAX_POOL_SIZE).Append(";");
|
|
sb.Append("allow user variables=").Append("true").Append("; ");
|
|
sb.Append("default command timeout=").Append("300").Append("; ");
|
|
sb.Append("connect timeout=").Append("300").Append(";");
|
|
sb.Append("charset=").Append("utf8mb4").Append(" ");
|
|
connectionString=sb.ToString();
|
|
MySqlConnection connection=new MySqlConnection(connectionString);
|
|
connection.Open();
|
|
//SetConnectionCollation(connection);
|
|
return connection;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
|
|
return null;
|
|
}
|
|
}
|
|
}
|
|
/// <summary>CreateSqlConnection - Creates an SQL connection.</summary>
|
|
/// <param name="datasource">the datasource.</param>
|
|
/// <returns>SqlConnection or null on error.</returns>
|
|
public static MySqlConnection CreateMySqlConnection(DataSourceEx datasource)
|
|
{
|
|
lock(typeof(SqlUtils))
|
|
{
|
|
try
|
|
{
|
|
String connectionString=null;
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("server=").Append(datasource.Datasource).Append("; ");
|
|
sb.Append("user id=").Append(datasource.Username).Append("; ");
|
|
sb.Append("password=").Append(datasource.Password).Append("; ");
|
|
sb.Append("database=").Append(datasource.Database).Append("; ");
|
|
sb.Append("SslMode=").Append("None").Append("; ");
|
|
sb.Append("pooling=").Append("true").Append(";");
|
|
sb.Append("allow user variables=").Append("true").Append("; ");
|
|
sb.Append("default command timeout=").Append("300").Append("; ");
|
|
sb.Append("connect timeout=").Append("300").Append(";");
|
|
sb.Append("MinimumPoolSize=").Append(MIN_POOL_SIZE).Append(";");
|
|
sb.Append("MaximumPoolSize=").Append(MAX_POOL_SIZE).Append(";");
|
|
sb.Append("charset=").Append("utf8mb4").Append(" ");
|
|
connectionString=sb.ToString();
|
|
MySqlConnection connection=new MySqlConnection(connectionString);
|
|
connection.Open();
|
|
return connection;
|
|
}
|
|
catch(Exception exception)
|
|
{
|
|
MDTrace.WriteLine(LogLevel.DEBUG,exception.ToString());
|
|
return null;
|
|
}
|
|
}
|
|
}
|
|
|
|
public static String FormatDate(DateTime dateTime)
|
|
{
|
|
return Utility.DateTimeToStringYYYYHMMHDD(dateTime);
|
|
}
|
|
/// <summary>CreateInClause - Creates an In Clause.</summary>
|
|
/// <param name="list">ArrayList of items.</param>
|
|
/// <returns>String.</returns>
|
|
public static String CreateInClause(ArrayList list)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
Hashtable hashtable=new Hashtable();
|
|
foreach(string item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
|
|
list=new ArrayList(hashtable.Keys);
|
|
sb.Append("(");
|
|
for(int index=0;index<list.Count;index++)
|
|
{
|
|
sb.Append("'").Append((string)list[index]).Append("'");
|
|
if(index<list.Count-1)sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
/// <summary>CreateInClause - Creates an In Clause.</summary>
|
|
/// <param name="list">List<String></String> of items.</param>
|
|
/// <returns>String.</returns>
|
|
public static String CreateInClause(List<String> list)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
Hashtable hashtable = new Hashtable();
|
|
foreach (string item in list) if (!hashtable.ContainsKey(item)) hashtable.Add(item, item);
|
|
ArrayList uniqueKeys = new ArrayList(hashtable.Keys);
|
|
sb.Append("(");
|
|
for (int index = 0; index < uniqueKeys.Count; index++)
|
|
{
|
|
sb.Append("'").Append((string)uniqueKeys[index]).Append("'");
|
|
if (index < uniqueKeys.Count - 1) sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
/// <summary>CreateInClause - Creates an In Clause.</summary>
|
|
/// <param name="list">ArrayList of items.</param>
|
|
/// <returns>String.</returns>
|
|
public static String CreateInClauseInt(List<String> list)
|
|
{
|
|
ArrayList uniqueList=new ArrayList();
|
|
StringBuilder sb=new StringBuilder();
|
|
Hashtable hashtable=new Hashtable();
|
|
foreach(string item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
|
|
uniqueList=new ArrayList(hashtable.Keys);
|
|
sb.Append("(");
|
|
for(int index=0;index<uniqueList.Count;index++)
|
|
{
|
|
sb.Append((string)uniqueList[index]);
|
|
if(index<uniqueList.Count-1)sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
/// <summary>CreateInClause - Creates an In Clause.</summary>
|
|
/// <param name="list">ArrayList of items.</param>
|
|
/// <returns>String.</returns>
|
|
public static String CreateInClauseInt(List<int> list)
|
|
{
|
|
ArrayList uniqueList=new ArrayList();
|
|
StringBuilder sb=new StringBuilder();
|
|
Hashtable hashtable=new Hashtable();
|
|
foreach(int item in list)if(!hashtable.ContainsKey(item))hashtable.Add(item,item);
|
|
uniqueList=new ArrayList(hashtable.Keys);
|
|
sb.Append("(");
|
|
for(int index=0;index<uniqueList.Count;index++)
|
|
{
|
|
sb.Append((int)uniqueList[index]);
|
|
if(index<uniqueList.Count-1)sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
/// <summary>CreateInClause - Creates an In Clause.</summary>
|
|
/// <param name="list">ArrayList of items.</param>
|
|
/// <returns>String.</returns>
|
|
public static String CreateInClauseYear(List<DateTime> list)
|
|
{
|
|
ArrayList uniqueList = new ArrayList();
|
|
StringBuilder sb = new StringBuilder();
|
|
Hashtable hashtable = new Hashtable();
|
|
foreach (DateTime item in list)
|
|
{
|
|
String strRep = Utility.DateTimeToStringYYYYHMMHDD(item);
|
|
if (!hashtable.ContainsKey(strRep)) hashtable.Add(strRep, strRep);
|
|
}
|
|
uniqueList = new ArrayList(hashtable.Keys);
|
|
sb.Append("(");
|
|
for (int index = 0; index < uniqueList.Count; index++)
|
|
{
|
|
sb.Append(Utility.AddQuotes((string)uniqueList[index]));
|
|
if (index < uniqueList.Count - 1) sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
//
|
|
public static String CreateInClause(List<int> list)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.Append("(");
|
|
for (int index = 0; index < list.Count; index++)
|
|
{
|
|
sb.Append(list[index].ToString());
|
|
if (index < list.Count - 1) sb.Append(",");
|
|
}
|
|
sb.Append(")");
|
|
return sb.ToString();
|
|
}
|
|
/// <summary>MinDate - Return minimum sql date.</summary>
|
|
/// <returns>DateTime</returns>
|
|
public static DateTime MinSqlDate()
|
|
{
|
|
return SqlDateTime.MinValue.Value;
|
|
}
|
|
public static String AddQuotes(String item)
|
|
{
|
|
return "'" + item + "'";
|
|
}
|
|
public static String SqlDate(DateTime dateTime, bool addQuotes=false)
|
|
{
|
|
if (Utility.IsEpoch(dateTime)) return null;
|
|
return addQuotes?AddQuotes(Utility.DateTimeToStringYYYYHMMHDD(dateTime)):Utility.DateTimeToStringYYYYHMMHDD(dateTime);
|
|
}
|
|
public static String ToSqlDate(DateTime dateTime, bool addQuotes=false)
|
|
{
|
|
return SqlDate(dateTime, addQuotes);
|
|
}
|
|
public static String ToSqlDateTime(DateTime dateTime, bool addQuotes=false)
|
|
{
|
|
return addQuotes?AddQuotes(Utility.DateTimeToStringYYYYHMMHDDHHMMSS(dateTime)):Utility.DateTimeToStringYYYYHMMHDDHHMMSS(dateTime);
|
|
}
|
|
public static String ToSqlDateTimeTT(DateTime dateTime, bool addQuotes=false)
|
|
{
|
|
return addQuotes?AddQuotes(Utility.DateTimeToStringYYYYHMMHDDHHMMSSTT(dateTime)):Utility.DateTimeToStringYYYYHMMHDDHHMMSSTT(dateTime);
|
|
}
|
|
public static String SqlString(String value,bool addQuotes=false)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
foreach(char ch in value)
|
|
{
|
|
sb.Append(ch);
|
|
if(ch=='\'')sb.Append("'");
|
|
}
|
|
return addQuotes?AddQuotes(sb.ToString()):sb.ToString();
|
|
}
|
|
public static String ToSqlString(String value)
|
|
{
|
|
return SqlString(value,true);
|
|
}
|
|
public static string Set(string name,string value)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("SET ").Append(name).Append("=").Append(AddQuotes(value)).Append(" ").Append(Collation()).Append(";");
|
|
return sb.ToString();
|
|
}
|
|
public static string Set(string name,DateTime date)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
sb.Append("SET ").Append(name).Append("=").Append(SqlDate(date,true)).Append(" ").Append(Collation()).Append(";");
|
|
return sb.ToString();
|
|
}
|
|
private static string Collation()
|
|
{
|
|
return "COLLATE "+COLLATION;
|
|
}
|
|
}
|
|
}
|