public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
Ftype ftype = (Ftype)value;
switch (ftype)
{
case Ftype.File:
writer.WriteValue("file");
break;
case Ftype.Folder:
writer.WriteValue("folder");
break;
case Ftype.vPrint:
writer.WriteValue("vprint");
break;
case Ftype.Page:
writer.WriteValue("page");
break;
}
}
private List <Model> index(int ModelId, int Page, Ftype ftype)
{
if (!CheckExpiration())
{
throw new Exception("token refresh fails");
}
RestRequest request = new RestRequest(String.Format("{0}/folders", ApiPath), Method.GET);
if (ftype != Ftype.All)
{
request.AddParameter("ftype", ftype.ToString());
}
request.AddParameter("api_token", ApiToken);
if (Page != 0)
{
request.AddParameter("page", Page);
}
if (Configuration["PerPage"] != null)
{
request.AddParameter("per_page", Configuration["PerPage"]);
}
try
{
IRestResponse httpResponse = RestClient.Execute(request);
Models models = JsonConvert.DeserializeObject <Models>(httpResponse.Content, TSCloud.serializer_settings());
int num_pages = models.Pagination.NumPages;
if (Page == 0)
{
List <Model> model_list = new List <Model>();
for (int i = 1; i <= num_pages; i++)
{
model_list.AddRange(index(ModelId, i, ftype));
}
return(model_list);
}
List <Model> filtered_model_list = new List <Model>();
if (ftype != Ftype.All)
{
foreach (Model model in models.Contents)
{
if (Convert.ToString(model.Ftype) == ftype.ToString())
{
filtered_model_list.Add(model);
}
}
filtered_model_list.ForEach(x => x.SysInfo = GetSysInfo());
return(filtered_model_list);
}
else
{
models.Contents.ForEach(x => x.SysInfo = GetSysInfo());
return(models.Contents);
}
}
catch
{
return(new List <Model>());
}
}
//private string parse_model_ids(int[] model_ids)
//{
// int length = model_ids.Length;
// string strResult = null;
// if (length == 1)
// return Convert.ToString(model_ids[0]);
// try
// {
// StringBuilder buildString = new StringBuilder();
// for (int i = 0; i < model_ids.Length; i++)
// {
// buildString = buildString.Append(Convert.ToString(model_ids[i]));
// if (i != length)
// buildString = buildString.Append(',');
// }
// strResult = buildString.ToString();
// }
// catch
// {
// throw;
// }
// return strResult;
//}
//private string get_acl()
//{
// Acl AclObject = new Acl(Int32.Parse(CurrentUser.Id.ToString()));
// return JsonConvert.SerializeObject(AclObject, Formatting.None);
//}
//private bool is_meta_search(string query)
//{
// if (String.IsNullOrEmpty(query))
// return false;
// try
// {
// if (query.Contains("="))
// return true;
// }
// catch
// {
// throw;
// }
// return false;
//}
//private Meta get_meta(string query)
//{
// if (!is_meta_search(query))
// return new Meta();
// try
// {
// string[] Split = query.Split('=');
// string key = Split[0].Trim();
// string value = Split[1].Trim();
// if (value[0] == '"' || value[0] == '\'')
// value = value.Remove(0, 1);
// if (value[value.Length - 1] == '"' || value[value.Length - 1] == '\'')
// value = value.Remove(value.Length - 1, 1);
// return new Meta(key, value);
// }
// catch
// {
// throw;
// }
//}
private List <Model> index(int Page, Ftype ftype)
{
return(index(0, Page, ftype));
}
public Models GetModels(int Page, Ftype ftype, int FolderId, params GetModelsOption[] GetModelsOptions)
{
if (!CheckExpiration())
{
throw new Exception("token refresh fails");
}
RestRequest request = new RestRequest(String.Format("{0}/folders", ApiPath), Method.GET);
if (ftype == Ftype.Folder)
{
request.AddParameter("folder", "true");
}
else if (ftype == Ftype.File)
{
request.AddParameter("ftype", "file");
}
request.AddParameter("api_token", ApiToken);
if (Page != 0)
{
request.AddParameter("page", Page);
}
if (FolderId != 0)
{
request.AddParameter("parent_id", FolderId);
}
if (Configuration["PerPage"] != null)
{
request.AddParameter("per_page", Configuration["PerPage"]);
}
if (GetModelsOptions.Length > 0)
{
foreach (GetModelsOption Option in GetModelsOptions)
{
switch (Option)
{
case GetModelsOption.OnlyChildren:
if (FolderId == 0)
{
request.AddParameter("root", "true");
}
request.AddParameter("descendants", "false");
break;
case GetModelsOption.AllDescendants:
request.AddParameter("descendants", "true");
break;
}
}
}
try
{
IRestResponse httpResponse = RestClient.Execute(request);
Models models = JsonConvert.DeserializeObject <Models>(httpResponse.Content, TSCloud.serializer_settings());
models.Contents.ForEach(x => x.SysInfo = GetSysInfo());
return(models);
}
catch (Exception ee)
{
throw ee;
}
}
public Models GetModels(int Page, Ftype ftype, params GetModelsOption[] Options)
{
return(GetModels(Page, ftype, 0, Options));
}
public Models GetModels(Ftype ftype, int FolderId, params GetModelsOption[] Options)
{
return(GetModels(0, ftype, FolderId, Options));
}
public Models GetModels(Ftype ftype, params GetModelsOption[] Options)
{
return(GetModels(0, ftype, 0, Options));
}
public List <Model> All(Ftype type)
{
return(index(0, type));
}
static void Main(string[] args)
{
bool singletable = false;
char csvSeperatorChar = '\t';
Console.WriteLine("HolyOne csv 2 mssql importer, by Aytek Ustundag, www.tahribat.com");
if (args.Length < 2)
{
Console.WriteLine("USAGE:");
Console.WriteLine("csv2mssql.exe <ConnectionString> <Filename>");
Console.WriteLine("EXAMPLE:");
Console.WriteLine(@"csv2mssql.exe ""Data Source=(local);Initial Catalog=dbname;Integrated Security=SSPI"" ""data.csv""");
Console.WriteLine(@"csv2mssql.exe ""Data Source=(local);Initial Catalog=dbname;Integrated Security=SSPI"" ""excelfile.xlsx""");
Console.WriteLine("");
return;
}
string filename = args[1];
string connstr = args[0];
if (args.Length >= 3)
{
singletable = args[2].Equals("/single", StringComparison.InvariantCultureIgnoreCase);
}
if (!System.IO.File.Exists(filename))
{
Console.WriteLine(@"Input file ""{0}"" not found", filename);
return;
}
//string baglantiCumlesi = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\Database1.accdb;Persist Security Info=False;";
// foreach (string filename in args)
{
//string filename = "x.csv";
/* string bulk_data_filename = "x.csv";
* StreamReader file = new StreamReader(bulk_data_filename);
* CsvReader csv = new CsvReader(file, true, ',');
* SqlBulkCopy copy = new SqlBulkCopy(conn);
* copy.DestinationTableName = System.IO.Path.GetFileNameWithoutExtension(bulk_data_filename);
* copy.WriteToServer(csv);
*/
string tablename = System.IO.Path.GetFileNameWithoutExtension(filename);
string ext = System.IO.Path.GetExtension(filename);
List <DataTable> dts = new List <DataTable>();
Ftype mode = Ftype.csv;
if (ext.Equals(".xls", StringComparison.InvariantCultureIgnoreCase) || ext.Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
mode = Ftype.xls;
dts = exceldata(filename, singletable);
}
else
if (ext.Equals(".accdb", StringComparison.InvariantCultureIgnoreCase) || ext.Equals(".mdb", StringComparison.InvariantCultureIgnoreCase))
{
mode = Ftype.mdb;
dts = access(filename);
}
else
{
//csv mode
using (var csvStreamReader = new StreamReader(filename))
using (CsvReader csvReader = new CsvReader(csvStreamReader, true))
{
DataTable dt = new DataTable(tablename);
int tmpcnt = 0;
string myline = "";
while (tmpcnt < 100)
{
myline = csvStreamReader.ReadLine();
if (myline == null)
{
break;
}
if (tmpcnt == 0)
{//first line
Dictionary <char, int> charcounter = new Dictionary <char, int>();
foreach (char cx in sepchars)
{
int chcount = myline.Count(f => f == cx);
charcounter.Add(cx, chcount);
}
charcounter = charcounter.OrderByDescending(x => x.Value).ToDictionary(x => x.Key, x => x.Value);
csvSeperatorChar = charcounter.First().Key;
Console.WriteLine("Resolving seperator char:" + ((csvSeperatorChar == '\t')?"<TAB>":(csvSeperatorChar.ToString())));
}
tmpcnt++;
string[] cells = myline.Replace(@"""", "").Split(new char[] { csvSeperatorChar });
while (dt.Columns.Count < cells.Length)
{
dt.Columns.Add(cells[dt.Columns.Count]);
}
dt.Rows.Add(cells);
}
// dt.Load(csvReader);
dts.Add(dt);
}
}
if (dts.Count == 0)
{
Console.WriteLine("No data table found to import");
return;
}
foreach (DataTable dt in dts)
{
string str = BuildCreateTableScript(dt);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
try
{
using (SqlCommand cmd = new SqlCommand(str, conn))
{
cmd.ExecuteNonQuery();
}
}
catch (Exception exx)
{
Console.WriteLine("\tWarning:" + exx.Message + " ,Appending...");
}
DataTable dtexcelSingle = new DataTable(tablename);
SqlTransaction transaction = conn.BeginTransaction();
try
{
int batchsize = 0;
Console.WriteLine("Importing table {0}", dt.TableName);
if (mode == Ftype.csv)
{
using (StreamReader file = new StreamReader(filename))
{
using (CsvReader csv = new CsvReader(file, true, csvSeperatorChar))
// using (CsvReader csv = new CsvReader(file, true, csvSeperatorChar,'\0','\0','#', ValueTrimmingOptions.None))
{
csv.SkipEmptyLines = true;
csv.SupportsMultiline = true;
csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;
// csv.DefaultParseErrorAction = ParseErrorAction.AdvanceToNextLine;
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
// SqlBulkCopy copy = new SqlBulkCopy(connstr, SqlBulkCopyOptions.KeepIdentity );
copy.BulkCopyTimeout = 9999999;
copy.DestinationTableName = tablename;
copy.WriteToServer(csv);
batchsize = copy.RowsCopiedCount();
transaction.Commit();
}
}
}
else
{
string sheet = dt.TableName;
if (sheet.EndsWith("_"))
{
continue;
}
OleDbConnection oconn = GetOleConn(filename);
// List<DataTable> dtt = new List<DataTable>();
// oconn.Open();
try
{
// foreach (DataRow schemaRow in schemaTable.Rows)
{
//Looping a first Sheet of Xl File
// schemaRow = schemaTable.Rows[0];
// if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheet + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, oconn);
/* DataTable targettable = null;
*
* if ( singletable)
* {
* targettable=(dtexcelSingle);
* }
* else
* {
*
* DataTable dtexcel = new DataTable();
* dtexcel.Locale = CultureInfo.CurrentCulture;
* targettable = dtexcel;
*
*
* }*/
using (OleDbCommand cmd = new OleDbCommand(query, oconn))
{
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
// SqlBulkCopy copy = new SqlBulkCopy(connstr, SqlBulkCopyOptions.KeepIdentity );
copy.BulkCopyTimeout = 9999999;
copy.DestinationTableName = dt.TableName;
copy.WriteToServer(rdr);
batchsize = copy.RowsCopiedCount();
transaction.Commit();
}
}
/*
* if (!singletable)
* {
* dtt.Add(targettable);
* }*/
}
// if (assingle) dtt.Add(dtexcelSingle);
}
}
finally
{
// oconn.Close();
}
}
Console.WriteLine("Finished inserting {0} records.", batchsize);
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine("Err:" + ex.Message);
}
finally
{
conn.Close();
}
}
}
// Console.ReadKey();
}
