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();
}
}
}
}