237 lines
8.6 KiB
C#
237 lines
8.6 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 CopyDb
|
|
{
|
|
/// <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=100;
|
|
|
|
/// <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)
|
|
{
|
|
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("allow user variables=").Append("true").Append("; ");
|
|
sb.Append("default command timeout=").Append("300").Append("; ");
|
|
sb.Append("connect timeout=").Append("300").Append(" ");
|
|
connectionString=sb.ToString();
|
|
MySqlConnection connection=new MySqlConnection(connectionString);
|
|
connection.Open();
|
|
return connection;
|
|
}
|
|
catch(SqlException exception)
|
|
{
|
|
throw 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)
|
|
//{
|
|
// 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(" ");
|
|
// connectionString=sb.ToString();
|
|
// MySqlConnection connection=new MySqlConnection(connectionString);
|
|
// connection.Open();
|
|
// return connection;
|
|
// }
|
|
// catch(SqlException 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)
|
|
//{
|
|
// if (Utility.IsEpoch(dateTime)) return null;
|
|
// return Utility.DateTimeToStringYYYYHMMHDD(dateTime);
|
|
//}
|
|
//public static String ToSqlDateTime(DateTime dateTime)
|
|
//{
|
|
// return Utility.DateTimeToStringYYYYHMMHDDHHMMSS(dateTime);
|
|
//}
|
|
//public static String ToSqlDateTimeTT(DateTime dateTime)
|
|
//{
|
|
// return Utility.DateTimeToStringYYYYHMMHDDHHMMSSTT(dateTime);
|
|
//}
|
|
/// <summary>SqlString - Return minimum sql date.</summary>
|
|
/// <returns>DateTime</returns>
|
|
public static String SqlString(String value)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
foreach(char ch in value)
|
|
{
|
|
sb.Append(ch);
|
|
if(ch=='\'')sb.Append("'");
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
public static String ToSqlString(String value)
|
|
{
|
|
StringBuilder sb=new StringBuilder();
|
|
return sb.Append("'").Append(SqlString(value)).Append("'").ToString();
|
|
}
|
|
}
|
|
}
|