mardi 3 mars 2015

Cant create NpgsqlDataAdapter with insert/update

I am trying to create a generic form which would be configurable from the XML config. That config is supposed to store the settings for NpgsqlDataAdapter, which will be used to insert/update/delete data in the form. I've created such config, but however the dataAdapter is showing errors when I am trying to insert/update msth into database. Could you please help? ERROR CODE: 42703 (Column does not exists. Hint: table contains column "key", but it cannot be refernced from this part of the query).


Here is the XML:



<Frm>
<SelectCommand type="text">
<text>
SELECT * FROM npdata
</text>
</SelectCommand>
<InsertCommand type="text">
<text>
INSERT INTO npdata VALUES (@key, @ndata)
</text>
<params>
<param name="@key" column="key" type="19" sourceversion="current"> </param>
<param name="@ndata" column="ndata" type="1" sourceversion="current">> </param>
</params>
</InsertCommand>
<UpdateCommand type="text">
<text>
UPDATE npdata SET key = @key, ndata = @ndata WHERE key = @key
</text>
<params>
<param name="@key" column="key" type="19" sourceversion="original"> </param>
<param name="@ndata" column="ndata" type="1" sourceversion="current"> </param>
</params>
</UpdateCommand>
<DeleteCommand type="text">
<text>
DELETE FROM npdata WHERE key = @key
</text>
<params>
<param name="@key" column="key" type="19" sourceversion="original"> </param>
</params>
</DeleteCommand>
</Frm>


And this is the code behing for the form:



string txt;
string _pname;
NpgsqlTypes.NpgsqlDbType _type;
string _colname;

conn = new NpgsqlConnection(@"Server=localhost;Port=5432;Database=npgsqldata_test;UserId=postgres;Password=postgres;TIMEOUT=120");

using (var cmd = new NpgsqlCommand("SELECT xml FROM npsettings", conn))
{

if (cmd.Connection.State != ConnectionState.Open)
{
conn.Open();
}

txt = cmd.ExecuteScalar().ToString();
}

XDocument doc = XDocument.Parse(txt);

dset = new DataSet("npdata");

NpAdapter = new NpgsqlDataAdapter();

string _selcmd = doc.Root.Element("SelectCommand").Value;

NpAdapter.SelectCommand = new NpgsqlCommand(_selcmd, conn);

if (doc.Root.Element("SelectCommand").Attribute("type").Value.ToLower() == ("Procedure").ToLower())
{
NpAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
}

NpAdapter.Fill(dset, "npdata");
dtsource = dset.Tables["npdata"];

string _delcommand = doc.Root.Element("DeleteCommand").Element("text").Value;

if (!String.IsNullOrEmpty(_delcommand))
{
NpAdapter.DeleteCommand = new NpgsqlCommand(_delcommand, conn);

foreach (var c in doc.Root.Element("DeleteCommand").Element("params").Elements())
{
_pname = c.Attribute("name").Value;
_type = (NpgsqlTypes.NpgsqlDbType)c.Attribute("type").Value.NToInt32();
_colname = c.Attribute("column").Value;

NpParam = NpAdapter.DeleteCommand.Parameters.Add(_pname, _type);
NpParam.SourceColumn = _colname;

if (c.Attribute("sourceversion").Value == "original")
{
NpParam.SourceVersion = DataRowVersion.Original;
}
else
{
NpParam.SourceVersion = DataRowVersion.Current;
}

}

if (doc.Root.Element("DeleteCommand").Attribute("type").Value.ToLower() == ("Procedure").ToLower())
{
NpAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
}
}

string _inscommand = doc.Root.Element("InsertCommand").Element("text").Value;

if (!String.IsNullOrEmpty(_inscommand))
{
NpAdapter.InsertCommand = new NpgsqlCommand(_inscommand, conn);

foreach (var c in doc.Root.Element("InsertCommand").Element("params").Elements())
{
_pname = c.Attribute("name").Value;
_type = (NpgsqlTypes.NpgsqlDbType)c.Attribute("type").Value.NToInt32();
_colname = c.Attribute("column").Value;

NpParam = NpAdapter.DeleteCommand.Parameters.Add(_pname, _type);
NpParam.SourceColumn = _colname;

if (c.Attribute("sourceversion").Value == "original")
{
NpParam.SourceVersion = DataRowVersion.Original;
}
else
{
NpParam.SourceVersion = DataRowVersion.Current;
}

}

if (doc.Root.Element("InsertCommand").Attribute("type").Value.ToLower() == ("Procedure").ToLower())
{
NpAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
}
}

string _updcommand = doc.Root.Element("UpdateCommand").Element("text").Value;

if (!String.IsNullOrEmpty(_updcommand))
{
NpAdapter.UpdateCommand = new NpgsqlCommand(_inscommand, conn);

foreach (var c in doc.Root.Element("UpdateCommand").Element("params").Elements())
{
_pname = c.Attribute("name").Value;
_type = (NpgsqlTypes.NpgsqlDbType)c.Attribute("type").Value.NToInt32();
_colname = c.Attribute("column").Value;

NpParam = NpAdapter.DeleteCommand.Parameters.Add(_pname, _type);
NpParam.SourceColumn = _colname;

if (c.Attribute("sourceversion").Value == "original")
{
NpParam.SourceVersion = DataRowVersion.Original;
}
else
{
NpParam.SourceVersion = DataRowVersion.Current;
}
}

if (doc.Root.Element("UpdateCommand").Attribute("type").Value.ToLower() == ("Procedure").ToLower())
{
NpAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
}
}

dataGridView1.DataSource = dset.Tables["npdata"];
dtsource.RowChanged += (ss, ee) => { MessageBox.Show("changed"); };

Aucun commentaire:

Enregistrer un commentaire