private void putMongoDBData(xFerDb db, DataTable dt, xFerTransferType t, string mongoCollection)
{
if (dt != null)
{
if (dt.Rows.Count > 0)
{
try
{
Console.WriteLine("Insert mongodb begin");
MongoServer server = MongoServer.Create(db.ConnectionString);
MongoDatabase database = server.GetDatabase(db.DbName); // "test" is the name of the database
MongoCollection<BsonDocument> collection = database.GetCollection<BsonDocument>(mongoCollection);
if (t == xFerTransferType.REPLACE)
{
collection.RemoveAll();
}
List<BsonDocument> docs = new List<BsonDocument>();
foreach (DataRow dr in dt.Rows)
{
BsonDocument d = new BsonDocument();
foreach (DataColumn dc in dt.Columns)
{
Type colType = dr[dc.ColumnName].GetType();
string val = string.Empty;
if (colType.FullName != "System.String")
{
if (colType.FullName == "System.DateTime")
{
d.Add(dc.ColumnName.ToString(), Convert.ToDateTime(dr[dc.ColumnName].ToString()));
}
else if (colType.FullName == "System.Int32")
{
d.Add(dc.ColumnName.ToString(), Convert.ToInt32(dr[dc.ColumnName].ToString()));
}
else if (colType.FullName == "System.Int64")
{
d.Add(dc.ColumnName.ToString(), Convert.ToInt64(dr[dc.ColumnName].ToString()));
}
else if (colType.FullName == "System.Boolean")
{
d.Add(dc.ColumnName.ToString(), Convert.ToBoolean(dr[dc.ColumnName].ToString()));
}
else if (colType.FullName == "System.Decimal")
{
d.Add(dc.ColumnName.ToString(), Convert.ToDouble(dr[dc.ColumnName].ToString()));
}
}
else
{
d.Add(dc.ColumnName.ToString(), dr[dc.ColumnName].ToString());
}
}
docs.Add(d);
}
collection.InsertBatch(docs);
Console.WriteLine("Insert mongodb finish: rows added to " + db.DbName + "." + mongoCollection + "(" + dt.Rows.Count + ")");
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
public void putSqlDbData(xFerDb db, DataTable dt, xFerTransferType t, xFerQuery q)
{
CommandType ct = CommandType.StoredProcedure;
if (dt != null)
{
if (dt.Rows.Count > 0)
{
if (q.QueryType == xFerQueryType.Text)
{
ct = CommandType.Text;
}
if (q.QueryText.ToString() != string.Empty && db.ConnectionString != string.Empty)
{
SqlConnection oSqlConn = new SqlConnection(db.ConnectionString);
SqlCommand com = new SqlCommand();
try
{
com.Connection = oSqlConn;
com.CommandTimeout = db.CommandTimeout;
com.Connection = oSqlConn;
com.CommandType = ct;
com.CommandText = q.QueryText.ToString();
com.Connection.Open();
foreach (DataRow dr in dt.Rows)
{
if (com.Parameters.Count > 0)
{
com.Parameters.Clear();
}
if (q.Paramaters != null)
{
if (q.Paramaters.Count == 0)
{
foreach (DataColumn c in dt.Columns)
{
q.Paramaters.Add(new xFerParamater(c.ColumnName.Trim(), ""));
}
}
foreach (xFerParamater p in q.Paramaters)
{
try
{
com.Parameters.AddWithValue("@" + p.Name, dr[p.Name].ToString());
}
catch
{
_errors.Add("Insert missing column " + p.Name);
}
}
}
com.ExecuteNonQuery();
}
com.Connection.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
finally
{
if (com.Connection.State == ConnectionState.Open)
{
com.Connection.Close();
}
}
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
else
{
Console.WriteLine("Source failed to return data.");
}
}
private void putData(xFerDb db, DataTable dt, xFerTransferType t, xFerQuery xOut, string mongoCollection)
{
switch (db.DbType)
{
case xFerDbType.mongoDb:
putMongoDBData(db, dt, t, mongoCollection);
break;
case xFerDbType.MSSQL:
putSqlDbData(db, dt, t, xOut);
break;
case xFerDbType.MySql:
putMySqlDbData(db, dt, t, xOut);
break;
}
}
public DataTable getMongoData(xFerDb db, xFerQuery q, string mongoCollection)
{
DataTable dt = new DataTable();
if (q.QueryText != null)
{
try
{
MongoServer server = MongoServer.Create(db.ConnectionString);
MongoDatabase database = server.GetDatabase(db.DbName);
MongoCollection<BsonDocument> collection = database.GetCollection<BsonDocument>(mongoCollection);
setQuery(q.QueryText.ToString());
QueryDocument _q = new QueryDocument();
if (q.QueryText.ToString() == string.Empty)
{
q.QueryText = "{}";
}
BsonDocument query = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<BsonDocument>(_Query);
_q.Add(query);
FieldsDocument _fields = MongoDB.Bson.Serialization.BsonSerializer.Deserialize<FieldsDocument>(_Field);
MongoCursor<BsonDocument> _ret = collection.Find(_q).SetFields(_fields).SetSortOrder(_Sort).SetLimit(_Limit);
bool isSet = false;
foreach (BsonDocument i in _ret)
{
if (!isSet)
{
foreach (BsonElement mei in i.Elements)
{
dt.Columns.Add(mei.Name);
isSet = true;
}
}
DataRow _dr = dt.NewRow();
foreach (BsonElement mei in i.Elements)
{
_dr[mei.Name] = mei.Value;
}
dt.Rows.Add(_dr);
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message.ToString());
}
}
return dt;
}
private DataTable getData(xFerDb x, xFerQuery q, string mongoCollection)
{
DataTable dt = new DataTable();
switch (x.DbType)
{
case xFerDbType.MSSQL:
dt = sqlGetData(q.QueryText.ToString(), x.ConnectionString, q.Paramaters, q.QueryType, x.CommandTimeout);
break;
case xFerDbType.mongoDb:
dt = getMongoData(x, q, mongoCollection);
break;
case xFerDbType.MySql:
dt = sqlMyGetData(q.QueryText.ToString(), x.ConnectionString, q.Paramaters, q.QueryType, x.CommandTimeout);
break;
}
return dt;
}
//private List<xFerParamater> _props = new List<xFerParamater>();
//public List<xFerParamater> Props
//{
// get { return _props; }
// set { _props = value; }
//}
#endregion
#region method: init
private void initMe(string path)
{
//EMPTY PREVIOUS
_xFerPackages.Clear();
_initPath = path;
string[] files = xFerFiles.openAllFiles(path);
foreach (string file in files)
{
if (file.StartsWith(path + "package_"))
{
string sLoad = xFerFiles.getFile(file);
if (sLoad.Length > 0)
{
try
{
xFerPackage _x = new xFerPackage();
_x = LitJson.JsonMapper.ToObject<xFerPackage>(sLoad);
if (_x != null)
{
_xFerPackages.Add(_x);
Console.WriteLine("Loaded Package: " + _x.PackageName);
foreach (xFer xf in _x.XFers)
{
Console.WriteLine("--" + xf.XFerName);
}
}
}
catch (Exception ex)
{
Console.WriteLine("failed to load file " + file);
}
}
}
}
//GET Saved DBs
string[] dbFiles = xFerFiles.openAllFiles(path + "\\db");
foreach (string db in dbFiles)
{
try
{
string dbJSON = xFerFiles.getFile(db);
if (dbJSON.Length > 0)
{
xFerDb _d = new xFerDb();
_d = LitJson.JsonMapper.ToObject<xFerDb>(dbJSON);
if (_d != null)
{
_dbs.Add(_d);
Console.WriteLine("Loaded DB: " + _d.DbName);
}
}
}
catch
{
}
}
}
public void createDb(List<xFerParamater> _vars)
{
//JSON or COMMA SEPERATED?
//CHECK LENGTH TO MAKE SURE ENOUGH VARIABLES WERE PASSED
if (_vars.Count == 4)
{
string dbname = _vars[0].Value.ToString();
string connString = _vars[1].Value.ToString();
int timeout = Convert.ToInt32(_vars[2].Value.ToString());
string type = _vars[3].Value.ToString();
List<xFerParamater> _params = new List<xFerParamater>();
if (_vars.Count > 4)
{
try
{
_params = getParametersPassed(_vars[4].Value.ToString());
}
catch(Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
xFerDb db = new xFerDb();
db.ConnectionString = connString;
db.CommandTimeout = timeout;
switch (type)
{
case "MSSQL":
db.DbType = xFerDbType.MSSQL;
break;
case "MONGO":
db.DbType = xFerDbType.mongoDb;
break;
case "MYSQL":
db.DbType = xFerDbType.MySql;
break;
}
db.DbName = dbname;
if (_params.Count > 0)
{
//username : value, password : value, isadmin
foreach (xFerParamater p in _params)
{
if (p.Name.Trim().ToLower() == "username")
{
db.UserName = p.Value.Trim();
}
else if (p.Name.Trim().ToLower() == "password")
{
db.Password = p.Value.Trim();
}
else if (p.Name.ToLower().Trim() == "isadmin")
{
db.IsAdmin = Convert.ToBoolean(p.Value.Trim().ToLower());
}
}
}
_dbs.Add(db);
xFerFiles.createFile(LitJson.JsonMapper.ToJson(db), _initPath + "\\db\\db_" + db.DbName + ".txt");
}
else
{
Console.WriteLine("missing variables");
}
}