public void MoveBlockToNewVolume(string hash, long size, long volumeID, System.Data.IDbTransaction tr)
{
m_moveBlockToNewVolumeCommand.SetParameterValue(0, volumeID);
m_moveBlockToNewVolumeCommand.SetParameterValue(1, hash);
m_moveBlockToNewVolumeCommand.SetParameterValue(2, size);
m_moveBlockToNewVolumeCommand.Transaction = tr;
var c = m_moveBlockToNewVolumeCommand.ExecuteNonQuery();
if (c != 1)
{
throw new Exception("Unexpected update result");
}
}
public virtual void ResetSequenceNumber(Context context)
{
if (resetCommand == null)
{
//String connectionString = ConfigurationSettings.AppSettings["strConnectionString"].ToString();
//SqlConnection adoCon = new SqlConnection(connectionString);
System.Data.IDbConnection adoCon = context.PersistenceSession.Connection;
resetCommand = adoCon.CreateCommand();
resetCommand.CommandText = "exec dbo.usp_ResetSequenceNo "
+ SystemID.ToString() + ","
+ SequenceType.ToString() + ","
+ SubsequenceType.ToString() + ";";
}
resetCommand.ExecuteNonQuery();
}
/// <summary>
/// Executes a DataBase Function Command
/// </summary>
/// <param name="oFuncCmd">The DataBase Function Command</param>
/// <returns>The DataBase Function Return Value</returns>
public static object ExecFunctionCommand(System.Data.IDbCommand oFuncCmd)
{
try
{
oFuncCmd.Connection.Open();
oFuncCmd.ExecuteNonQuery();
oFuncCmd.Connection.Close();
}
catch (Exception e)
{
throw new Exception("Failed to Execute DB function " + oFuncCmd.CommandText, e);
}
return(X.Data.DB.DbFactory.GetParameterValue(oFuncCmd, "RETURN_VALUE"));
}
public void insertData(ScineEntity entity)
{
System.Data.IDbCommand dbcmd = getDbCommand();
dbcmd.CommandText =
"INSERT INTO " + TABLE_NAME
+ " ( "
+ KEY_NAME + ", "
+ PRICE + ", "
+ IS_BOUGHT + " ) "
+ "VALUES ( '"
+ entity.name + "', "
+ entity.price + ", "
+ entity.isBought + " )";
dbcmd.ExecuteNonQuery();
}
/// <summary>
/// Adds the 'set nocount off' statement prior to all statements sent to the server.
/// </summary>
/// <returns></returns>
public override IDbConnection GetConnection()
{
IDbConnection conn = Driver.CreateConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
System.Data.IDbCommand cmd = Driver.GenerateCommand(CommandType.Text, new SqlString("SET NOCOUNT OFF"), new SqlType[] { });
using (cmd) {
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
return(conn);
}
/// <summary>
/// Update or insert a new entry for the file into the database
/// </summary>
/// <returns>
/// Flase if there was a problem during the update
/// </returns>
/// <param name='user'></param>
/// <param name='thisFile'></param>
public bool UpdateFile(ServerUser user, MyFile thisFile)
{
// TODO: use OwnCloud API calls if possible perhaps: http://owncloud.org/dev/apps/database/
string path = "/" + user.id + "/files/" + thisFile.name;
string absPath = GetDataDir(user) + thisFile.name; //Server.baseDataDir + path;
FileInfo f = new FileInfo(absPath);
long mtime = Common.DateTimeToUnixTimestamp(f.LastWriteTimeUtc);
DbCommand command_checkExists = dbConnection.CreateCommand();
command_checkExists.CommandText = "SELECT count(id) FROM oc_fscache WHERE path='" + path + "'";
int checkFound = Convert.ToInt32(command_checkExists.ExecuteScalar());
DbCommand command = dbConnection.CreateCommand();
if (checkFound > 0)
{
command.CommandText = "UPDATE oc_fscache SET mtime='" + mtime + "' WHERE path='" + path + "'";
}
else
{
// if the entry does not exist, insert it instead of updating it
long ctime = Common.DateTimeToUnixTimestamp(f.CreationTimeUtc);
DbCommand command_getParent = dbConnection.CreateCommand();
command_getParent.CommandText = "SELECT id FROM oc_fscache WHERE path_hash='"
+ Common.Md5Hash(path.Substring(0, path.LastIndexOf('/'))) + "'";
int parentId = Convert.ToInt32(command_getParent.ExecuteScalar());
string mimetype = MIMEAssistant.GetMIMEType(f.Name);
string mimepart = mimetype.Substring(0, mimetype.LastIndexOf('/'));
bool writable = true; //!f.IsReadOnly;
bool encrypted = false; // ?
bool versioned = false; // ?
command.CommandText = String.Format("INSERT INTO oc_fscache (parent, name, path, path_hash, size, mtime, ctime, mimetype, mimepart,`user`,writable,encrypted,versioned) "
+ "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}')",
parentId, f.Name, path, Common.Md5Hash(path), f.Length, mtime, ctime, mimetype, mimepart, user.id, writable, encrypted, versioned);
}
return(command.ExecuteNonQuery() == 1);
}
public bool Exec(string commandText, IDataParameter[] parameters)
{
Connect();
Utility.WriteTrace(commandText);
comm.Parameters.Clear();
comm.CommandText = commandText;
if (parameters != null)
{
for (int loop = 0; loop < parameters.Length; loop++)
{
comm.Parameters.Add(parameters[loop]);
}
}
return(comm.ExecuteNonQuery() > 0);
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数 、用于增删改
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(ArrayList SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
i = -1;
return(i);
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
}
public void NonQuery(string Query)
{
//Used for Insert/Update and Deletes
//Open a connection to the database
this.Open();
using (System.Data.IDbCommand command = this._conn.CreateCommand())
{
//Assign the command the query string and execute
command.CommandText = Query;
command.ExecuteNonQuery();
}
//Once executed, close connection to database.
this.Close();
}
public bool Exec(string commandText, IDataParameter[] parameters)
{
Connect();
Logger.I(string.Concat(commandText, "\tPrm:", ParametersToString(parameters)));
comm.Parameters.Clear();
comm.CommandText = commandText;
if (parameters != null)
{
for (int loop = 0; loop < parameters.Length; loop++)
{
comm.Parameters.Add(parameters[loop]);
}
}
return(comm.ExecuteNonQuery() > 0);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
int i = 0;
var wrongsql = string.Empty;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
wrongsql = sql;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
DBClassHelper.ErrLog("错误位置：" + i + "。错误sql:" + wrongsql + "。DBClassHelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
finally
{
Conn.Close();
}
return(true);
}//Execute(string sql)
/// <summary>
/// Ejecuta un procedimiento almacenado en la base de datos,utilizando los parametros.
/// </summary>
/// <param name="ProcedimientoAlmacenado">Procedimiento Almacenado</param>
/// <param name="Parametros">Parametros</param>
/// <returns>Nro de instrucciones realizadas</returns>
public int Ejecutar(string ProcedimientoAlmacenado, params System.Object[] Parametros)
{
System.Data.IDbCommand Com = Comando(ProcedimientoAlmacenado);
CargarParametros(Com, Parametros);
int Resp = Com.ExecuteNonQuery();
for (int i = 0; i < Com.Parameters.Count; i++)
{
System.Data.IDbDataParameter Par =
(System.Data.IDbDataParameter)Com.Parameters[i];
if (Par.Direction == System.Data.ParameterDirection.InputOutput ||
Par.Direction == System.Data.ParameterDirection.Output)
{
Parametros.SetValue(Par.Value, i);
}
}
return(Resp);
}
protected void ExecuteCommand(System.Data.IDbCommand command)
{
lMgr.Connection = LibraryMgr.GetConnection(this.connectionStrType);
lMgr.SetCommandConnection(command);
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
command.Connection.Close();
}
catch (Exception exception)
{
command.Connection.Close();
WebUtils.HandleException(exception);
}
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
int rows = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public int ExecuteSqlTran(Hashtable SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
i = -1;
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
}//ExecuteQuery(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
//return 0;
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！Execute");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
finally
{
Conn.Close();
}
}//Execute(string sql)
public int ExecuteNonQuery(System.Data.IDbCommand command)
{
if (this.ReadOnly)
{
throw new InvalidOperationException("No se pueden realizar cambios en la conexión de lectura");
}
if (this.IsOpen() == false)
{
this.Open();
}
if (this.Trace)
{
Log.Debug(this.Handle.ToString() + ": " + command.CommandText);
}
int Intentos = 3;
while (true)
{
try {
if (command.Connection == null)
{
command.Connection = this.DbConnection;
}
this.ResetKeepAliveTimer();
int Res = command.ExecuteNonQuery();
return(Res);
} catch (Exception ex) {
if (this.TryToRecover(ex) || Intentos-- <= 0)
{
Log.Error(command.CommandText, ex);
ex.Data.Add("Command", command.CommandText);
throw;
}
}
}
}
private bool TryUpdateTask(uint ncq_id, DateTime oTaskUpdateTime)
{
bool bResult = false;
try
{
using (System.Data.IDbConnection dbConnection = GetDbConnection())
{
dbConnection.Open();
using (System.Data.IDbCommand oUpdateCommand = dbConnection.CreateCommand())
{
oUpdateCommand.CommandText = GetUpdateString(ncq_id, oTaskUpdateTime);
bResult = (oUpdateCommand.ExecuteNonQuery() > 0);
}
}
}
catch
{
}
return(bResult);
}
//public override void AddRange(IList<rule> datas)
//{
// using (var entity = new StockManDBEntities())
// {
// foreach (var data in datas)
// {
// var rule = entity.rule.FirstOrDefault(p => p.code == data.code);
// if (rule != null)
// {
// rule.name = data.name;
// rule.state = data.state;
// rule.description = data.description;
// }
// else
// {
// entity.Rule.Add(data);
// }
// }
// entity.SaveChanges();
// foreach (var data in datas)
// {
// foreach (var condition in data.RuleCondition)
// {
// var rule = entity.RuleCondition.FirstOrDefault(p => p.code == condition.code);
// if (rule != null)
// {
// rule.category_code = condition.category_code;
// rule.category_name = condition.category_name;
// rule.object_code = condition.object_code;
// rule.object_name = condition.object_name;
// rule.index_code = condition.index_code;
// rule.index_name = condition.index_name;
// rule.sort = condition.sort;
// rule.rule_code = condition.rule_code;
// }
// else
// {
// entity.RuleCondition.Add(condition);
// }
// }
// }
// }
//}
public void RemoveByUserId(string userId)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var list = entity.rule.Where(p => p.user_id == userId).ToList();
if (list.Count == 0)
{
return;
}
var ids = string.Empty;
foreach (var rule in list)
{
if (ids.Length == 0)
{
ids = "'" + rule.code + "'";
}
else
{
ids += ",'" + rule.code + "'";
}
}
string sql1 = @"delete from RuleCondition where rule_code in (" + ids + ");";
//entity.Database.ExecuteSqlCommand(sql1);
sql1 += @"delete from rule where code in (" + ids + ");";
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql1;
commond.ExecuteNonQuery();
entity.Database.Connection.Close();
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(List <string> list)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < list.Count; n++)
{
string strsql = list[n].ToString();
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
public int EjecutarConsulta(System.Data.IDbCommand command)
{
command.Connection = this.ValidarConnection();
command.Transaction = this.transaction;
return(command.ExecuteNonQuery());
}
public void Convert()
{
if (ValidateDestination() == false)
{
return;
}
Server SourceServer = null;
if (Configuration.SqlServerIntegratedSecurity)
{
SourceServer = new Server(Configuration.SqlServerName);
}
else
{
ServerConnection svrConn = new ServerConnection(Configuration.SqlServerName);
svrConn.LoginSecure = false;
svrConn.Login = Configuration.SqlServerUserName;
svrConn.Password = Configuration.SqlServerPassword;
SourceServer = new Server(svrConn);
}
Database sourceDb = SourceServer.Databases[Configuration.SqlServerDatabaseName];
if (sourceDb == null)
{
Console.WriteLine("Source db '{0}' not found in '{1}'",
Configuration.SqlServerDatabaseName,
Configuration.SqlServerName);
return;
}
List <string> schemaNames = new List <string>();
foreach (Schema schema in sourceDb.Schemas)
{
if (schema.Name.Substring(0, 3) != "db_")
{
schemaNames.Add(schema.Name);
}
}
List <string> tableNames = new List <string>();
foreach (Table tbl in sourceDb.Tables)
{
if (!tbl.IsSystemObject)
{
tableNames.Add(tbl.Name);
}
}
string sqlCeConnectionString = string.Empty;
if (string.IsNullOrEmpty(Configuration.SqlCePassword))
{
sqlCeConnectionString = string.Format("Data Source='{0}';Encrypt={1};SSCE:Max Database Size=4091;",
Configuration.SqlCeFileName, Configuration.SqlCeIsEncrypted.ToString().ToUpper());
}
{
sqlCeConnectionString = string.Format("Data Source='{0}';Password={1};Encrypt={2};SSCE:Max Database Size=4091;",
Configuration.SqlCeFileName, Configuration.SqlCePassword, Configuration.SqlCeIsEncrypted.ToString().ToUpper());
}
bool copiedFailed = false;
sqlCeConnectionString = sqlCeConnectionString.Replace("LCID=idpe;", "");
System.Data.SqlServerCe.SqlCeEngine eng = new System.Data.SqlServerCe.SqlCeEngine(sqlCeConnectionString);
object engine = eng;
Type type = engine.GetType();
Assembly asm = Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeEngine));
Console.WriteLine("Sql Ce Version:" + asm.GetName().Version.ToString());
//Create the database.
MethodInfo mi = type.GetMethod("CreateDatabase");
Console.WriteLine("Creating the SQL Server Compact Edition Database...");
try
{
mi.Invoke(engine, null);
}
catch (TargetInvocationException ex)
{
Console.WriteLine("You do not have permissions to save the file to " + Configuration.SqlCeFileName + ". Please select a different destination path and try again.");
return;
}
Console.WriteLine("Connecting to the SQL Server Compact Edition Database...");
Type connType = asm.GetType("System.Data.SqlServerCe.SqlCeConnection");
System.Data.IDbConnection conn = (System.Data.IDbConnection)Activator.CreateInstance(connType);
conn.ConnectionString = sqlCeConnectionString;
conn.Open();
//create all the tables
int tblCount = 0;
Type cmdType = asm.GetType("System.Data.SqlServerCe.SqlCeCommand");
System.Data.IDbCommand cmd = (System.Data.IDbCommand)Activator.CreateInstance(cmdType);
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName, Configuration.SqlServerSchemaName];
if (tbl == null)
{
Console.WriteLine("Table '" + tblName + "' was not found in the selected schema.");
copiedFailed = true;
break;
}
if (tbl.IsSystemObject)
{
continue;
}
//if (tbl.Name == "IdpeVersion")
// Debugger.Break();
Console.WriteLine("Scripting table: " + tbl.Name);
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE [").Append(tbl.Name).Append("](");
int colIdx = 0;
List <string> pKeys = new List <string>();
foreach (Column col in tbl.Columns)
{
if (colIdx > 0)
{
sb.Append(", ");
}
//if (col.Name == "Data")
// Debugger.Break();
sb.Append("[").Append(col.Name).Append("]").Append(" ");
int max = 0;
switch (col.DataType.SqlDataType)
{
case SqlDataType.VarChar:
max = col.DataType.MaximumLength;
col.DataType = new DataType(SqlDataType.NVarChar);
col.DataType.MaximumLength = max;
break;
case SqlDataType.Char:
max = col.DataType.MaximumLength;
col.DataType = new DataType(SqlDataType.NChar);
col.DataType.MaximumLength = max;
break;
case SqlDataType.Text:
case SqlDataType.VarCharMax:
col.DataType = new DataType(SqlDataType.NText);
break;
case SqlDataType.VarBinaryMax:
col.DataType = new DataType(SqlDataType.Image);
break;
case SqlDataType.Decimal:
int scale = col.DataType.NumericScale;
int precision = col.DataType.NumericPrecision;
col.DataType = new DataType(SqlDataType.Numeric);
col.DataType.NumericPrecision = precision;
col.DataType.NumericScale = scale;
break;
}
sb.Append(col.DataType.SqlDataType.ToString());
SqlDataType datatype = col.DataType.SqlDataType;
if (datatype == SqlDataType.NVarChar || datatype == SqlDataType.NChar)
{
sb.Append(" (").Append(col.DataType.MaximumLength.ToString()).Append(") ");
}
else if (datatype == SqlDataType.Numeric)
{
sb.Append(" (").Append(col.DataType.NumericPrecision).Append(",").Append(col.DataType.NumericScale).Append(")");
}
if (col.InPrimaryKey)
{
pKeys.Add(col.Name);
}
//if (col.InPrimaryKey)
// sb.Append(" CONSTRAINT PK").Append(col.Name);
if (!col.Nullable)
{
sb.Append(" NOT NULL");
}
if (col.DefaultConstraint != null && !String.IsNullOrEmpty(col.DefaultConstraint.Text))
{
string def = col.DefaultConstraint.Text.Replace("((", "(").Replace("))", ")");
sb.Append(" DEFAULT ").Append(col.DefaultConstraint.Text);
//sb.Append(" DEFAULT (1) ");
}
if (col.Identity)
{
sb.Append(" IDENTITY (").Append(col.IdentitySeed.ToString()).Append(",").Append(col.IdentityIncrement.ToString()).Append(")");
}
//if (col.InPrimaryKey)
// sb.Append(" PRIMARY KEY");
colIdx++;
}
sb.Append(")");
cmd.CommandText = sb.ToString();
cmd.CommandText = cmd.CommandText.Replace("suser_sname()", "'Manual User'");
cmd.Connection = conn;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! " + ex.Message);
copiedFailed = true;
break;
}
//add the PK constraints
if (pKeys.Count > 0)
{
sb = new StringBuilder();
sb.Append("ALTER TABLE [").Append(tbl.Name).Append("] ADD CONSTRAINT PK_");
//create the constraint name
for (int k = 0; k < pKeys.Count; k++)
{
if (k > 0)
{
sb.Append("_");
}
sb.Append(pKeys[k]);
}
sb.Append(" PRIMARY KEY(");
//add the constraint fields
for (int k = 0; k < pKeys.Count; k++)
{
if (k > 0)
{
sb.Append(", ");
}
sb.Append(pKeys[k]);
}
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! Failed creating the Primary Key(s).");
copiedFailed = true;
break;
}
}
//copy the indexes
Console.WriteLine("Scripting the indexes for table: " + tbl.Name);
foreach (Index idx in tbl.Indexes)
{
if (idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
continue;
}
sb = new StringBuilder();
sb.Append("CREATE");
if (idx.IsUnique)
{
sb.Append(" UNIQUE");
}
//if (!idx.IsClustered)
// sb.Append(" CLUSTERED");
//else
// sb.Append(" NONCLUSTERED");
sb.Append(" INDEX ").Append(idx.Name).Append(" ON [").Append(tbl.Name).Append("](");
for (int i = 0; i < idx.IndexedColumns.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("[" + idx.IndexedColumns[i].Name + "]");
}
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! Failed creating the indexes." + ex.Message);
copiedFailed = true;
break;
}
}
tblCount++;
}
if (!copiedFailed)
{
//Now copy the data
bool copyData = true;
if (copyData)
{
Console.WriteLine("Copying database data.");
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName];
if (tbl.IsSystemObject)
{
continue;
}
Console.WriteLine("Copying " + tbl.RowCount.ToString() + " rows from " + tbl.Name);
bool hasIdentity = false;
string alterSql = "ALTER TABLE [{0}] ALTER COLUMN [{1}] IDENTITY({2},{3})";
string IDColName = "";
long increment = 1;
//If the table has an Identity column then we need to re-set the seed and increment
//This is a hack since SQL Server Compact Edition does not support SET IDENTITY_INSERT <columnname> ON
foreach (Column col in tbl.Columns)
{
if (col.Identity)
{
hasIdentity = true;
IDColName = col.Name;
alterSql = String.Format(alterSql, tbl.Name, col.Name, "{0}", "{1}");
}
}
//Select SQL
string sql = "SELECT * FROM [{0}]";
//Insert Sql
string insertSql = "INSERT INTO [{0}] ({1}) VALUES ({2})";
StringBuilder sbColums = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
int idx1 = 0;
foreach (Column col in tbl.Columns)
{
if (col.Name != IDColName)
{
if (idx1 > 0)
{
sbColums.Append(",");
sbValues.Append(",");
}
sbColums.Append("[").Append(col.Name).Append("]");
sbValues.Append("?");
idx1++;
}
}
//if (tbl.Name.Contains("IdpeVersion"))
// Debugger.Break();
insertSql = String.Format(insertSql, tbl.Name, sbColums.ToString(), sbValues.ToString());
sql = String.Format(sql, tbl.Name);
DataSet ds = sourceDb.ExecuteWithResults(sql);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
int rowCnt = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
rowCnt++;
if (hasIdentity)
{
long seed = long.Parse(row[IDColName].ToString());
//seed--;
string alterTableForIDColumn = String.Format(alterSql, seed.ToString(), increment.ToString());
cmd.CommandText = alterTableForIDColumn;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Failed altering the Table for IDENTITY insert.");
copiedFailed = true;
break;
}
}
sbValues = new StringBuilder();
cmd.Parameters.Clear();
cmd.CommandText = insertSql;
for (int i = 0; i < tbl.Columns.Count; i++)
{
if (tbl.Columns[i].Name != IDColName)
{
//if (tbl.Columns[i].Name == "Data")
// Debugger.Break();
Type type1 = asm.GetType("System.Data.SqlServerCe.SqlCeParameter");
object[] objArray1 = new object[2];
objArray1[0] = tbl.Columns[i].Name;
objArray1[1] = row[tbl.Columns[i].Name];
object p = Activator.CreateInstance(type1, objArray1);
cmd.Parameters.Add(p);
}
}
cmd.CommandText = String.Format(insertSql, sbValues.ToString());
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Copy table data failed!");
copiedFailed = true;
break;
}
}
}
}
}
//Now add the FK relationships
if (!copiedFailed)
{
Console.Write("Adding ForeignKeys.");
string fkSql = "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([{4}])";
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName];
if (tbl.IsSystemObject)
{
continue;
}
int fkCnt = tbl.ForeignKeys.Count;
int fxIdx = 0;
foreach (ForeignKey fk in tbl.ForeignKeys)
{
if (!tableNames.Contains(fk.ReferencedTable))
{
continue;
}
fxIdx++;
Console.WriteLine(tbl.Name + ": " + fk.Name);
string createFKSql = String.Format(fkSql, tbl.Name, fk.Name, "{0}", fk.ReferencedTable, sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns[0].Name);
StringBuilder sbFk = new StringBuilder();
foreach (ForeignKeyColumn col in fk.Columns)
{
if (sbFk.Length > 0)
{
sbFk.Append(",");
}
sbFk.Append(col.Name);
}
createFKSql = String.Format(createFKSql, sbFk.ToString());
cmd.CommandText = createFKSql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Creating ForeignKeys failed!");
//copiedFailed = true;
//break;
}
}
}
}
Console.WriteLine("Closing the connection to the SQL Server Compact Edition Database...");
conn.Close();
conn.Dispose();
if (!copiedFailed)
{
Console.WriteLine("Completed!");
}
else
{
Console.WriteLine("Copy failed!");
}
}
else
{
Console.WriteLine("Finished!");
}
}
else
{
Console.WriteLine("Copy failed!");
}
}
public int ExecuteNonQuery(string sql)
{
command = new OracleCommand(sql, this.connection as OracleConnection);
return(command.ExecuteNonQuery());
}
/// <summary>
/// Remove a file from the index
/// </summary>
/// <param name="fileName"></param>
/// <returns>true if 1 row was affected in the database</returns>
public bool Remove(String fileName)
{
paramPath.Value = fileName;
return(commandDeleteFile.ExecuteNonQuery() == 1);
}
