public void CreateIndex(string tableName, CommandVirtualization commandIndexes) { CommandVirtualization command; string sqlStr = string.Empty; string strUnique = string.Empty; if (commandIndexes.NewIndexes.Unique) { strUnique = "UNIQUE "; } for (int i = 0; i < commandIndexes.NewIndexes.Count; i++) { if (i > 0) { sqlStr += ","; } sqlStr += commandIndexes.NewIndexes[i]; } sqlStr = "CREATE " + strUnique + " " + "INDEX" + " " + commandIndexes.NewIndexes.IndexName + " " + "ON" + " " + tableName + "(" + sqlStr + ")"; command = new CommandVirtualization(sqlStr, CommandVirtualization.SqlType.nonQuery); Execute(command, false); }
//public string ProduceNewColumnQuery(ColumnNewItem item) //{ // string retVal; // string sqlStr = string.Empty; // string parsedType; //Stores the type as a string according to database type // parsedType = ParseColType2string(item); // retVal = "ALTER TABLE " + tableName + " ADD " + ci.Name + " " + parsedType; // //switch (item.ColType) // //{ // // case ColumnType.String: // // break; // //} // //switch ((SqlDbType)ci.type) // //{ // // case SqlDbType.VarChar: // // sqlStr = "ALTER TABLE " + tableName + " ADD " + ci.Name + " " + (SqlDbType)ci.type + "(" + ci.len + ")"; // // break; // // case SqlDbType.DateTime: // // case SqlDbType.SmallInt: // // case SqlDbType.BigInt: // // case SqlDbType.Bit: // // sqlStr = "ALTER TABLE " + tableName + " ADD " + ci.Name + " " + (SqlDbType)ci.type; // // break; // //} // return sqlStr; //} /// <summary> /// Example: /// CREATE TABLE Persons ///( ///P_Id int NOT NULL PRIMARY KEY IDENTITY, ///LastName varchar(255) NOT NULL, ///FirstName varchar(255), ///Address varchar(255), ///City varchar(255) ///) /// Usage in acces to create a primary key field autoNumber: /// columnNewItem.Name = "efitableId"; /// columnNewItem.Type = "AUTOINCREMENT"; /// columnNewItem.PrimaryKey = "primary key"; /// /// Usage in sql server to create a primary key field autoNumber: /// columnNewItem.Name = "eftableId"; /// columnNewItem.Type = "bigInt"; /// columnNewItem.NullValue = "not null"; /// columnNewItem.PrimaryKey = "primary key"; // columnNewItem.Identity = "identity"; /// </summary> /// <param name="tableName"></param> /// <returns>True if the table was added, false in case the table already exists</returns> ///<remarks> ///issue #9999 ///</remarks> public bool CreateTable(string tableName, CommandVirtualization commandColumns) { CommandVirtualization command; string sqlStr = string.Empty; ColumnVirtualization.ColumnItem columnNewItem; Dictionary <string, byte> dicTableNames; bool retVal = false; //populate the existing table names to a dictionary dicTableNames = GetTablesNamesFromDataSet(); ////check if the table does not exist already if (!IsTableAlreadyExist(tableName, dicTableNames)) { //prepare the sqlStr for (int i = 0; i < commandColumns.NewColumns.Count; i++) { columnNewItem = commandColumns.NewColumns[i]; if (i > 0) { sqlStr += ","; } sqlStr += ProduceNewColumnQuery(columnNewItem); } sqlStr = "CREATE TABLE " + tableName + " (" + sqlStr + ")"; command = new CommandVirtualization(sqlStr, CommandVirtualization.SqlType.nonQuery); Execute(command, false); retVal = true; } return(retVal); }
/// <summary> /// Alter(type\size..)existing columns from a specific table. /// Note:In case of type text,the function does not alter the Allow sero length property. /// return the number of columns altered. /// </summary> /// <remarks> /// issue 8046 /// </remarks> /// <param name="tableName"></param> /// <param name="commandColumns"></param> /// <returns></returns> public int AlterColoumn(string tableName, CommandVirtualization commandColumns) { CommandVirtualization command; string sqlStr = string.Empty; int retVal = 0; //get the column of the table and check if a specefic column exists. //if not,do nothing. DataTable dtColumn = null; ColumnVirtualization.ColumnItem ci; dtColumn = GetTableColoumns(tableName); for (int i = 0; i < commandColumns.NewColumns.Count; i++) { ci = commandColumns.NewColumns[i]; if (ColumnExists(dtColumn, ci.Name) == true) { sqlStr = "ALTER TABLE " + tableName + " ALTER COLUMN " + ProduceNewColumnQuery(ci); command = new CommandVirtualization(sqlStr, CommandVirtualization.SqlType.nonQuery); Execute(command, false); retVal++; //#1102 } } return(retVal); //#1102 }
/// <summary> /// Use this function in case you want the system to create automatic name for the new data table. /// tblName will store the new name of the data table which was created in the Data Set. /// </summary> /// <param name="command"></param> /// <param name="tblName"></param> /// <returns></returns> /// <remarks> /// Issue #1185 /// </remarks> public int ExecuteAutoDtName(CommandVirtualization command, out string tblName) { DateTime dt = DateTime.Now; tblName = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() + dt.Millisecond.ToString(); System.Threading.Thread.Sleep(20); return(Execute(command, tblName)); }
/// <summary> /// Remove an index from a table. /// Syntax : DROP INDEX index_name ON table_name /// </summary> /// <param name="tableName"></param> /// <param name="indexName"></param> public void DeleteIndex(string tableName, string indexName) { string sqlStr = string.Empty; CommandVirtualization command; sqlStr = "DROP INDEX " + indexName + " ON " + tableName; command = new CommandVirtualization(sqlStr, CommandVirtualization.SqlType.nonQuery); Execute(command, false); }
/// <summary> /// get all the columns of the specified table /// the data table contains 0 rows. /// </summary> /// <param name="tableName"></param> public DataTable GetTableColoumns(string tableName) { CommandVirtualization command; DataTable dtColumns = null; string sqlStr = "SELECT * from " + tableName + " WHERE 1=2"; command = new CommandVirtualization(sqlStr, null, null, null); Execute(command, "Columns"); dtColumns = Import("Columns"); return(dtColumns); }
/// <summary> /// /// </summary> /// <param name="tableName"></param> /// <returns></returns> public DataTable GetTableStructure(string tableName) { string query; CommandVirtualization command; string tblAutoName; DataTable retVal; query = "SELECT * FROM " + tableName + " WHERE 1=2"; command = new CommandVirtualization(query); command.addSchema = true; ExecuteAutoDtName(command, out tblAutoName); retVal = Import(tblAutoName); return(retVal); }
/// <summary> /// Use this function for disconnected commands. The result will be stored as a table in the data set /// </summary> /// <param name="command"></param> /// <param name="tblName">name of the table, which will store the result in the dataSet </param> /// <returns> The number of records the new table has.</returns> public int Execute(CommandVirtualization command, string tblName) { int retVal = 0; DbDataAdapter adapter = null; DbCommand dbCommand = null; tableCommands currTableCommands; if (objDS.Tables.Contains(tblName)) { objDS.Tables[tblName].Clear(); objDS.Tables[tblName].Dispose(); objDS.Tables.Remove(tblName); } try { adapter = GetNewAdapter(); if (command.addSchema) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; } dbCommand = GetNewCommand(); currTableCommands = new tableCommands(); currTableCommands.selectSql = command.DisconnectedSqls.sqlQuery; currTableCommands.insertSql = command.DisconnectedSqls.sqlInsert; currTableCommands.updateSql = command.DisconnectedSqls.sqlUpdate; currTableCommands.deleteSql = command.DisconnectedSqls.sqlDelete; if (transactionMode) { dbCommand.Transaction = dbTransaction; } else { conn.Open(); } dbCommand.Connection = conn; if (m_usedStroedProcedures) { dbCommand.CommandType = CommandType.StoredProcedure; } dbCommand.CommandText = command.DisconnectedSqls.sqlQuery; SetParameters(command.Parameters, dbCommand); adapter.SelectCommand = dbCommand; //join query on adama project throw an exception. We omit this row until bug detection //adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; retVal = adapter.Fill(objDS, tblName); //create new table in the data set if (m_tablesCommands.ContainsKey(tblName)) { m_tablesCommands.Remove(tblName); } m_tablesCommands.Add(tblName, currTableCommands); //add table properties to dictionary return(retVal); } catch (System.OutOfMemoryException outOfMemEx) { throw outOfMemEx; } catch (Exception ex) { throw ex; } finally { //#1380 if (adapter != null) { adapter.Dispose(); } if (dbCommand != null) { dbCommand.Parameters.Clear(); dbCommand.Dispose(); } //End #1380 if (!transactionMode) { if (conn.State == ConnectionState.Open) { conn.Close(); } } } }
public object Execute(CommandVirtualization command, bool Identity) { object retVal = 0; DbCommand dbCommand = null; try { dbCommand = GetNewCommand(); if (transactionMode) { dbCommand.Transaction = dbTransaction; } else { conn.Open(); } dbCommand.Connection = conn; if (m_usedStroedProcedures) { dbCommand.CommandType = CommandType.StoredProcedure; } dbCommand.CommandText = command.ConnectedSql; SetParameters(command.Parameters, dbCommand); switch (command.Type) { case CommandVirtualization.SqlType.query: //dbCommand.ExecuteReader().GetSchemaTable(); break; case CommandVirtualization.SqlType.nonQuery: retVal = dbCommand.ExecuteNonQuery(); if (Identity) { dbCommand.CommandType = CommandType.Text; //In case we are using strored procedures.. dbCommand.CommandText = "SELECT @@Identity"; retVal = dbCommand.ExecuteScalar(); } break; case CommandVirtualization.SqlType.scalar: retVal = dbCommand.ExecuteScalar(); break; } return(retVal); } catch (System.OutOfMemoryException outOfMemEx) { throw outOfMemEx; } catch (Exception e) { throw e; } finally { //#1380 if (dbCommand != null) { dbCommand.Parameters.Clear(); dbCommand.Dispose(); dbCommand = null; } //End #1380 if (!transactionMode) { if (conn.State == ConnectionState.Open) { conn.Close(); } } } }