|
|
 |
|
|
Thread Tools | Display Modes |
Sep 26th, 2005, 1:48 PM
|
#1 | |
|
Programming Guru
 
Join Date: May 2004
Location: Brandon, Manitoba, Canada
Posts: 2,023
Rep Power: 7
   |
Npgsql, weirdness
Yeah, it's confusing the crap out of me. Here's some code that i know works just fine for inserting into a postgresql db using C# and the Npgsql stuff, it's based off of the example found on the mono website:
using System;
using System.Data;
using Npgsql;
public class Test
{
public static void Main(string[] args)
{
int intRowAff;
int intTotRowAff = 0;
string connectionString = "Server=192.168.0.10;" +
"Database=test;" +
"User ID=postgres;" +
"Password=******;" +
"Port=5432";
IDbConnection dbcon;
dbcon = new NpgsqlConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
string [] sql = new string[2];
sql[0] = "INSERT INTO employee VALUES ('Billy1', 'Bob1');";
sql[1] = "INSERT INTO employee VALUES ('Billy2', 'Bob2');";
for (int x = 0; x < 2; x++)
{
dbcmd.CommandText = sql[x];
try
{
dbcon.Open();
intRowAff = dbcmd.ExecuteNonQuery();
intTotRowAff = intTotRowAff + intRowAff;
}
finally
{
dbcon.Close();
}
}
Console.WriteLine("{0} Rows Effected!", intTotRowAff);
dbcmd.Dispose();
dbcmd = null;
dbcon = null;
}
}that will run the two insert staments that are part of the sql array just fine. Heres some code that's a little more advanced (simply because i am generating the sql on the fly), but in my eye's it should still work: using System;
using System.Data;
using System.IO;
using Npgsql;
class Importer
{
static void Main()
{
string strLine;
string strD = ",";
char [] charD = strD.ToCharArray();
string sql;
int intRowAff = 0;
int intTotRowAff = 0;
string strConnection = "Server=192.168.0.10;" +
"Database=any_data_mine;" +
"User ID=postgres;" +
"Password=******;" +
"Port=5432";
IDbConnection dbconn; //connection
dbconn = new NpgsqlConnection(strConnection); //make the connection to the database.
IDbCommand dbcmd = dbconn.CreateCommand(); //comand interface.
//Now to open the csv file and start chunking this thing into the db.
FileInfo Raw_Data = new FileInfo (@"/home/nick/Projects/Any-Data/data.csv");
StreamReader stream = Raw_Data.OpenText();
dbconn.Open();
do
{
sql = "INSERT INTO Data VALUES ("; //first part of the sql command.
strLine = stream.ReadLine(); //i now have 1 line of text.
strLine = strLine.Replace('\"', '\''); //change the " to a '
string [] strSplit = strLine.Split(charD); //split at the ,
foreach (string s in strSplit)
{
sql = sql + s + ","; //dump the changed string into the sql statment.
}
sql = sql + ");"; //Finish off sql.
//set the sql to the command interface.
dbcmd.CommandText = sql;
try
{
intRowAff = dbcmd.ExecuteNonQuery();
}
finally
{
intTotRowAff = intTotRowAff + intRowAff;
}
} while (strLine != null);
Console.WriteLine("There where {0} rows added to the database.", intTotRowAff);
//Cleanup.
dbconn.Close();
dbcmd.Dispose();
dbcmd = null;
dbconn = null;
}
}The second code (the one with the Importer class), complies fine, but i get these exceptions when i run it. Quote:
It's confusing me to no end, just ask Ooble :-P Here's a sample of the data.csv: "ANY","BCNE",900020,08/03/2005 00:10:00,"ICR","E",84152,"INTERCHANGE TRACK",22,"DFLT",2991340,"CWRL","SUNCOR","DEF" "ANY","BCNE",900020,08/04/2005 16:30:00,"ARI","E",84166,"YARD TRACK",22,"DFLT",2991340,"CWRL","SUNCOR","DEF" "ANY","BCNE",900020,08/05/2005 14:20:00,"ARR","E",84223,"SUNCOR",22,"DFLT",2991340,"CWRL","SUNCOR","DEF" "ANY","BCNE",900020,08/09/2005 11:28:00,"PAC","E",84223,"SUNCOR",22,"DFLT",2991340,,"SUNCOR","DEF" "ANY","BCNE",900020,08/09/2005 16:28:00,"RLO","L",84223,"SUNCOR",22,"DFLT",2991314,,"SUMLYN","DEF" "ANY","BCNE",900020,08/11/2005 02:59:00,"PFP","L",84166,"YARD TRACK",22,"DFLT",2991314,,"SUMLYN","DEF" I have also checked to see if the sql was being generated right and it is. You can check (if you feel like it) by removing the lines where the sql get's executed, and replacing it with a "Console.WriteLine(sql);". BTW the compiler commands for both codes are: mcs -g -r:System.Data -r:Npgsql main.cs mono --debug main.exe so there's nuthin' wrong with the references or anything. Any Idea's? Thanks for the help in advance.
__________________
Profanity is the one language that all programmers understand. Check out my Blog <---updated Nov 30 2007! |
|
|
|
|
Sep 26th, 2005, 4:03 PM
|
#2 |
|
Programming Guru
Join Date: May 2004
Location: Brandon, Manitoba, Canada
Posts: 2,023
Rep Power: 7
|
K, i thought that i figured it out, but i haven't, this is my new code:
using System;
using System.Data;
using System.IO;
using Npgsql;
class Importer
{
static void Main()
{
string strLine;
string strD = ",";
char [] charD = strD.ToCharArray();
string sql;
int intRowAff = 0;
int intTotRowAff = 0;
string strConnection = "Server=192.168.0.10;" +
"Database=any_data_mine;" +
"User ID=postgres;" +
"Password=*****;" +
"Port=5432";
IDbConnection dbconn; //connection
dbconn = new NpgsqlConnection(strConnection); //make the connection to the database.
IDbCommand dbcmd = dbconn.CreateCommand(); //comand interface.
//Now to open the csv file and start chunking this thing into the db.
FileInfo Raw_Data = new FileInfo (@"/home/nick/Projects/Any-Data/data.csv");
StreamReader stream = Raw_Data.OpenText();
do
{
sql = "INSERT INTO Data VALUES ("; //first part of the sql command.
strLine = stream.ReadLine(); //i now have 1 line of text.
if (strLine != null)
{
strLine = strLine.Replace('\"', '\''); //change the " to a '
string [] strSplit = strLine.Split(charD); //split at the ,
for (int s = 0; s < strSplit.Length; s++)
{
if (s < (strSplit.Length - 1))
{
sql = sql + strSplit[s] + ","; //dump the changed string into the s
}
else
{
//added this because the last part of the sql was getting a , when
sql = sql + strSplit[s];
}
}
sql = sql + ");"; //Finish off sql.
//Console.WriteLine(sql); //SQL Debug.
//set the sql to the command interface.
dbcmd.CommandText = sql;
try
{
dbconn.Open();
intRowAff = dbcmd.ExecuteNonQuery();
intTotRowAff = intTotRowAff + intRowAff;
}
finally
{
dbconn.Close();
}
}
} while (strLine != null);
Console.WriteLine("There where {0} rows added to the database.", intTotRowAff);
//Cleanup.
dbcmd.Dispose();
dbcmd = null;
dbconn = null;
}
}I am still getting the same exceptions as the above post.
__________________
Profanity is the one language that all programmers understand. Check out my Blog <---updated Nov 30 2007! Last edited by Pizentios; Sep 27th, 2005 at 8:47 AM. |
|
|
|
|
Sep 27th, 2005, 11:05 AM
|
#3 |
|
Programming Guru
Join Date: May 2004
Location: Brandon, Manitoba, Canada
Posts: 2,023
Rep Power: 7
|
K, i figured it out with a little help from Dameon and Ooble in the irc room.
It turns out, that my sql wasn't being generated correctly. It's ugly code, but it works for what i need, i might make a program later to import cvs files into a postgresql db later where you only need to give it the csv file, and it would do the rest including create the table etc. Anyways, here's my working code: //////////////////////////////////////////////////////////////////////
// Purpose: This file will import the data into a postgresql db. //
// Author: Nick Peters //
// Date Created: 2005-09-22 //
// Date Finished: 2005-09-27 //
// Comments: yeah i know it took me a while for something so simple //
// , but give me a god dam break! :-P //
//////////////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.IO;
using Npgsql;
class Importer
{
static void Main()
{
string strLine;
string strD = ",";
char [] charD = strD.ToCharArray();
string sql;
int intRowAff = 0;
int intTotRowAff = 0;
string strConnection = "Server=192.168.0.10;" +
"Database=any_data_mine;" +
"User ID=postgres;" +
"Password=******;" +
"Port=5432";
IDbConnection dbconn; //connection
dbconn = new NpgsqlConnection(strConnection); //make the connection to the database.
IDbCommand dbcmd = dbconn.CreateCommand(); //comand interface.
//Now to open the csv file and start chunking this thing into the db.
FileInfo Raw_Data = new FileInfo (@"/home/nick/Projects/Any-Data/data.csv");
StreamReader stream = Raw_Data.OpenText();
do
{
sql = "INSERT INTO data (org_roadmark, equip_initial, equip_num, last_move_datetime, equip_
move_type_cd, equip_status_cd, fsac, track_location_name, reclaim_cd, reclaim_pool, stcc_cd, interchange_road, patr
on_id, addr_type) VALUES ("; //first part of the sql command.
strLine = stream.ReadLine(); //i now have 1 line of text.
if (strLine != null)
{
strLine = strLine.Replace('\"', '\''); //change the " to a '
string [] strSplit = strLine.Split(charD); //split at the ,
for (int s = 0; s < strSplit.Length; s++)
{
//The date time needs quotes.
if (s == 3)
{
strSplit[s] = "'" + strSplit[s] + "'";
}
//Console.WriteLine(strSplit[s]); //Data debug
if (strSplit[s] == "" || strSplit[s] == " ")
{
strSplit[s] = "''";
}
if (s < (strSplit.Length - 1))
{
sql = sql + strSplit[s] + ", "; //dump the changed string into the sql statment.
}
else
{
//added this because the last part of the sql was getting a , when it doesn't need it.
sql = sql + strSplit[s];
}
}
sql = sql + ");"; //Finish off sql.
Console.WriteLine(sql); //SQL Debug.
//set the sql to the command interface.
dbcmd.CommandText = sql;
try
{
dbconn.Open();
intRowAff = dbcmd.ExecuteNonQuery();
intTotRowAff = intTotRowAff + intRowAff;
}
finally
{
dbconn.Close();
}
}
} while (strLine != null);
Console.WriteLine("There where {0} rows added to the database.", intTotRowAff);
//Cleanup.
dbcmd.Dispose();
dbcmd = null;
dbconn = null;
}
}
__________________
Profanity is the one language that all programmers understand. Check out my Blog <---updated Nov 30 2007! |
|
|
|
|
| Bookmarks |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
