public string CreateDescription(Energy.Source.Structure.Table table) { List <string> script = new List <string>(); string schema = "dbo"; script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Caption' , @value=N'Klucz' , @level0type='SCHEMA' , @level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='id'"); foreach (Energy.Source.Structure.Column column in table.Columns) { script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Caption',@value=N'" + column.Label + "',@level0type='SCHEMA',@level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='" + column.Name + "'"); } script.Add(""); script.Add("GO"); script.Add(""); script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'Klucz',@level0type='SCHEMA',@level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='id'"); foreach (Energy.Source.Structure.Column column in table.Columns) { string description = String.IsNullOrEmpty(column.Description) ? column.Label : column.Description; script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'" + description + "',@level0type='SCHEMA',@level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='" + column.Name + "'"); } script.Add(""); script.Add("GO"); return(String.Join(Energy.Base.Text.NL, script.ToArray())); }
private static void Test1() { Energy.Source.Structure.Table table = Energy.Source.Structure.Table.Create(typeof(UserTableRecord)); string query; Energy.Interface.IDialect script = new Energy.Query.Dialect.MYSQL(); query = script.DropTable(table.Name); if (db.Execute(query) < 0) { Console.WriteLine(db.ErrorMessage); } Console.WriteLine(query); Energy.Query.Script scriptBuilder = new Energy.Query.Script(); scriptBuilder.Dialect = Energy.Enumeration.SqlDialect.SQLSERVER; Energy.Query.Format format = Energy.Enumeration.SqlDialect.MYSQL; query = script.CreateTable(table); if (db.Execute(query) < 0) { Console.WriteLine(db.ErrorMessage); } Console.WriteLine(query); Console.ReadLine(); }
public string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration) { if (table == null || table.Columns.Count == 0) { return(""); } if (configuration == null) { configuration = Energy.Query.Style.Global; } List <string> script = new List <string>(); string identity = configuration.IdentityName; string tableName = "[" + table.Name + "]"; script.Add("IF ( OBJECT_ID('" + tableName + "') ) IS NULL"); script.Add(""); script.Add("CREATE TABLE " + tableName); script.Add("("); if (!String.IsNullOrEmpty(table.Identity)) { Energy.Source.Structure.Column column = table.Columns.Get(table.Identity) ?? new Energy.Source.Structure.Column(table.Identity); string type = column.Type; if (type == null || type.Length == 0) { type = configuration.IdentityType; } script.Add("\t[" + column.Name + "] " + type + " IDENTITY(1,1) NOT NULL ,"); identity = table.Identity; } else if (table.Columns.Get(identity) == default(Energy.Source.Structure.Column)) { script.Add("\t[" + identity + "] " + configuration.IdentityType + " IDENTITY(1,1) NOT NULL ,"); } foreach (Energy.Source.Structure.Column column in table.Columns) { if (!column.Ignore && !String.IsNullOrEmpty(column.Name) && column.Name != table.Identity) { script.Add("\t[" + column.Name + "] " + column.Type + " NOT NULL ,"); } } script.Add(""); script.Add("\tCONSTRAINT [PK_" + table.Name + "] PRIMARY KEY NONCLUSTERED"); script.Add("\t("); script.Add("\t\t[" + identity + "] ASC"); script.Add("\t)"); script.Add("\tWITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )"); script.Add(")"); script.Add("ON [PRIMARY]"); script.Add(""); script.Add("GO"); return(String.Join(Energy.Base.Text.NL, script.ToArray())); }
public string Merge(Energy.Source.Structure.Table table, bool compact) { string name = Format.Object(table.Name); StringBuilder script = new StringBuilder(); for (int i = 0; i < table.Rows.Count; i++) { List <string> where = new List <string>(); List <string> column = new List <string>(); List <string> insert = new List <string>(); List <string> update = new List <string>(); //for (int j = 0; j < table.Keys.Count; j++) //{ // string key = table.Keys[j]; // string value = table.Rows[i][table.Keys[j]].ToString(); // key = "[" + key + "]"; // value = "'" + value + "'"; // where.Add(key + " = " + value); // column.Add(key); // insert.Add(value); //} //foreach (string key in table.Rows[i].Keys) //{ // if (table.Keys.Contains(key)) // continue; // string value = table.Rows[i][key].ToString(); // update.Add("[" + key + "] = '" + value + "'"); //} script.Append("IF NOT EXISTS ( SELECT 1 FROM " + name + " WHERE " + String.Join(" AND ", where.ToArray()) + " )"); if (!compact) { script.Append(Energy.Base.Text.NL); } script.Append(compact ? " " : "\t"); script.Append("INSERT INTO " + name + " ( " + String.Join(" , ", column.ToArray()) + " ) VALUES ( " + String.Join(" , ", insert.ToArray()) + " )"); if (!compact) { script.Append(Energy.Base.Text.NL); } if (compact) { script.Append(" "); } script.Append("UPDATE " + name + " SET " + String.Join(" , ", update.ToArray())); if (!compact) { script.Append(Energy.Base.Text.NL); } script.Append(compact ? " " : "\t"); script.Append("WHERE " + String.Join(" AND ", where.ToArray())); script.Append(Energy.Base.Text.NL); } return(script.ToString().Trim()); }
public string CreateTable(Energy.Source.Structure.Table table) { StringBuilder s = new StringBuilder(); s.Append("CREATE TABLE IF NOT EXISTS "); s.Append(Format.Object(table.Schema, table.Name)); s.Append(Energy.Base.Text.NL); s.Append("("); s.Append(Energy.Base.Text.NL); string identity = Energy.Base.Text.Select(table.Identity, table.Columns.GetPrimaryName()); List <string> list = new List <string>(); for (int i = 0; i < table.Columns.Count; i++) { Energy.Source.Structure.Column column = table.Columns[i]; if (column.Ignore || string.IsNullOrEmpty(column.Name)) { continue; } string line = string.Concat(Format.Object(column.Name), " ", column.Type, " ", (column.NotNull ? "NOT " : ""), "NULL"); if (column.Name == identity) { line += " PRIMARY KEY"; if (column.Increment > 0) { line += " AUTOINCREMENT"; } } list.Add(line); } string join = string.Concat(" ,", Energy.Base.Text.NL, "\t"); s.Append(string.Concat("\t", string.Join(join, list.ToArray()))); s.Append(Energy.Base.Text.NL); s.Append(")"); s.Append(Energy.Base.Text.NL); s.Append(Energy.Base.Text.NL); return(s.ToString()); }
static void Main(string[] args) { Energy.Core.Tilde.WriteLine("~y~MySQL ~w~connection example"); Energy.Core.Log.Default.Destination.Add(new Energy.Core.Log.Target.Console()); Energy.Source.Connection <MySql.Data.MySqlClient.MySqlConnection> db; db = new Energy.Source.Connection <MySql.Data.MySqlClient.MySqlConnection>(); db.ConnectionString = @"Server=127.0.0.1;Database=test;Uid=test;Pwd=test;"; db.OnCreate += Db_OnCreate; db.OnClose += Db_OnClose; string query = "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST"; //Console.WriteLine(db.Read)) Console.WriteLine(db.Fetch(query).ToPlain()); Energy.Core.Tilde.WriteLine("~c~Using 3 threads to retreive current timestamp 3 times with delay of 1 second each"); Energy.Core.Tilde.WriteLine("~y~Starting in persistent mode (one connection only)"); db.Persistent = true; for (int i = 0; i < 3; i++) { Thread thread = new Thread(Work); thread.Start(new Tuple <int, Energy.Source.Connection>(i, db)); } Energy.Core.Tilde.Pause(); Energy.Core.Tilde.WriteLine("~y~Now in not persistent mode (opening new connection per each statement)"); db.Persistent = false; for (int i = 0; i < 3; i++) { Thread thread = new Thread(Work); thread.Start(new Tuple <int, Energy.Source.Connection>(i, db)); } Energy.Core.Tilde.Pause(); db.ConnectionString = @"Server=127.0.0.1;Database=platoon;Uid=platoon;Pwd=platoon;"; db.Open(); Console.WriteLine(db.Scalar("SELECT CURRENT_TIMESTAMP()")); Energy.Source.Structure.Table table = Energy.Source.Structure.Table.Create(typeof(UserTableRecord)); Energy.Interface.IDialect script = new Energy.Query.Dialect.MYSQL(); query = script.CreateDescription(table); Console.WriteLine(query); query = script.DropTable(table.Name); if (db.Execute(query) < 0) { Console.WriteLine(db.ErrorMessage); } Console.WriteLine(query); query = script.CreateTable(table); if (db.Execute(query) < 0) { Console.WriteLine(db.ErrorMessage); } Console.WriteLine(query); Console.ReadLine(); }
public virtual string CreateIndex(Energy.Source.Structure.Table table) { throw new NotImplementedException(); }
public virtual string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration) { throw new NotImplementedException(); }
public string Merge(Energy.Source.Structure.Table table) { return(Merge(table, false)); }
public string DropTable(Energy.Source.Structure.Table table) { throw new NotImplementedException(); }
/// <summary> /// Create table /// </summary> /// <param name="table"></param> /// <param name="configuration"></param> /// <returns></returns> public string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration) { if (table == null || table.Columns.Count == 0) { return(""); } if (configuration == null) { configuration = Energy.Query.Style.Global; } StringBuilder script = new StringBuilder(); if (table == null || table.Columns.Count == 0) { return(""); } if (configuration == null) { configuration = Energy.Query.Style.Global; } string identity = configuration.IdentityName; string tableName = table.Name; script.Append("CREATE TABLE IF NOT EXISTS " + Format.Object(tableName)); script.Append(Energy.Base.Text.NL); script.Append("("); script.Append(Energy.Base.Text.NL); Energy.Source.Structure.Column primary = null; if (!string.IsNullOrEmpty(table.Identity)) { identity = table.Identity; } else { primary = table.Columns.GetPrimary(); if (primary != null) { identity = primary.Name; } } if (primary == null) { primary = table.Columns.Get(identity) ?? new Energy.Source.Structure.Column(table.Identity); } List <string> list = new List <string>(); string line; string type = primary.Type; if (type == null || type.Length == 0) { type = configuration.IdentityType; } line = string.Concat(Format.Object(primary.Name), " ", type, " NOT NULL"); if (Energy.Query.Type.IsNumeric(type)) { line += " AUTO_INCREMENT"; } list.Add(line); for (int i = 0; i < table.Columns.Count; i++) { Energy.Source.Structure.Column column = table.Columns[i]; if (column.Ignore || string.IsNullOrEmpty(column.Name) || column.IsName(identity)) { continue; } line = string.Concat(Format.Object(column.Name), " ", column.Type, " ", (column.NotNull ? "NOT " : ""), "NULL"); list.Add(line); } script.Append(string.Concat("\t", string.Join(" ," + Energy.Base.Text.NL + "\t", list.ToArray()), Energy.Base.Text.NL)); script.Append(")"); script.Append(Energy.Base.Text.NL); string engine = string.IsNullOrEmpty(table.Engine) ? DefaultEngine : table.Engine; if (!string.IsNullOrEmpty(engine)) { script.Append("ENGINE = " + engine); } script.Append(";"); script.Append(Energy.Base.Text.NL); return(script.ToString()); }