说明 分析过的文档存储在 SQL Server 2000 的内部高速缓存中。MSXML 语法分析器使用 SQL Server 可用总内存的八分之一。若要避免内存不足,请运行 sp_xml_removedocument 以释放内存。 语法 sp_xml_preparedocument hdoc OUTPUT [, xmltext] [, xpath_namespaces]参数 hdoc是新创建的文档的句柄。hdoc 的数据类型为 integer。[xmltext]是原 XML 文档。MSXML 语法分析器分析该 XML 文档。xmltext 是 text 类型(char、nchar、varchar、nvarchar、text 或 ntext)的参数。默认值是 NULL,在这种情况下,将创建空 XML 文档的内部表示法。[xpath_namespaces]指定 OPENXML 的行和列 XPath 表达式中所使用的命名空间声明。默认值是 <root xmlns:mp='urn:schemas-microsoft-com:xml-metaprop'>。 xpath_namespaces 通过符合语法规则的 XML 文档的方式,为在 OPENXML 的 Xpath 表达式中使用的前缀提供命名空间 URI。xpath_namespaces 声明前缀必须用于引用命名空间 urn:schemas-microsoft-com:xml-metaprop,该命名空间提供有关分析后的 XML 元素的元数据。尽管可以使用此方法为元属性命名空间重新定义命名空间前缀,但此命名空间不会丢失。此前缀 mp 对 urn:schemas-microsoft-com:xml-metaprop 仍有效,即使 xpath_namespaces 不包含此类声明。xpath_namespaces 是 text 类型(char、nchar、varchar、nvarchar、text 或 ntext)的参数。 返回代码值 0(成功)或 >0(失败)权限 执行权限默认授予 public 角色。示例 A. 为符合语法规则的 XML 文档准备内部表示方式 下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。在对 sp_xml_preparedocument 的调用中,使用了默认命名空间前缀映射。DECLARE @hdoc int DECLARE @doc varchar(1000) SET @doc =' <ROOT> <Customer CustomerID='VINET' ContactName='Paul Henriot'> <Order CustomerID='VINET' EmployeeID='5' OrderDate='1996-07-04T00:00:00'> <OrderDetail OrderID='10248' ProductID='11' Quantity='12'/> <OrderDetail OrderID='10248' ProductID='42' Quantity='10'/> </Order> </Customer> <Customer CustomerID='LILAS' ContactName='Carlos Gonzlez'> <Order CustomerID='LILAS' EmployeeID='3' OrderDate='1996-08-16T00:00:00'> <OrderDetail OrderID='10283' ProductID='72' Quantity='3'/> </Order> </Customer> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc -- Remove the internal representation. exec sp_xml_removedocument @hdocB. 为带 DTD 的符合语法规则的 XML 文档准备内部表示方式 下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。存储过程根据文档中包含的 DTD 来验证装载的文档。在对 sp_xml_preparedocument 的调用中,使用了默认命名空间前缀映射。DECLARE @hdoc int DECLARE @doc varchar(2000) SET @doc = ' <?xml version='1.0' encoding='UTF-8' ?> <!DOCTYPE root [<!ELEMENT root (Customers)*> <!ELEMENT Customers EMPTY> <!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]> <root> <Customers CustomerID='ALFKI' ContactName='Maria Anders'/> </root>'EXEC sp_xml_preparedocument @hdoc OUTPUT, @docC. 指定命名空间 URI 下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。在对 sp_xml_preparedocument 的调用中,保留了元属性命名空间映射的 mp 前缀,并将 xyz 映射前缀添加到了命名空间 urn:MyNamespace。DECLARE @hdoc int DECLARE @doc varchar(1000) SET @doc =' <ROOT> <Customer CustomerID='VINET' ContactName='Paul Henriot'> <Order CustomerID='VINET' EmployeeID='5' OrderDate='1996-07-04T00:00:00'> <OrderDetail OrderID='10248' ProductID='11' Quantity='12'/> <OrderDetail OrderID='10248' ProductID='42' Quantity='10'/> </Order> </Customer> <Customer CustomerID='LILAS' ContactName='Carlos Gonzlez'> <Order CustomerID='LILAS' EmployeeID='3' OrderDate='1996-08-16T00:00:00'> <OrderDetail OrderID='10283' ProductID='72' Quantity='3'/> </Order> </Customer> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<root xmlns:xyz='run:MyNamespace'/>'
创建一个DataTable 再 把DataTable 写如数据库~
To:laji97(未完成~) 如何将DataTable 写入数据库?
奇怪,用dataset.writeXml()导出的xml,用adapter.update竟然提示:ERROR [22018] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Implicit conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f unction to run this query.
用dataset.writeXml()导出的xml,用adapter.update总是提示:ERROR [22018] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Implicit conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f unction to run this query.代码如下: OdbcDataAdapter adapter = new OdbcDataAdapter("select * from table1",Conn1); OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);
同样的代码在vs2005下执行update()出现下面异常:Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo rted against a SelectCommand that does not return any base table information.
to:liujia_0421(SnowLover) 还是不行: Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo rted against a SelectCommand that does not return any base table information.
adapter.update()时出现异常:System.InvalidOperationException: Parameter[5] '' has no default value. Parameter[6] '' has no default value. Parameter[7] '' has no default value. Parameter[13] '' has no default value. Parameter[15] '' has no default value.如何解决?
代码如下: string select = "select count(*) as col_num from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; int col_num = 0; string sInsert = "Insert " + sTableName +" ("; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) { col_num = System.Convert.ToInt16(reader[col_num]); } select = "select name from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) {
sInsert += reader["name"].ToString() + ",";
} //处理掉最后的',' sInsert = sInsert.Substring(0, sInsert.Length - 1); sInsert += ") VALUES ( "; for (int i = 1; i <= col_num; i++) { sInsert += "?,"; } //处理掉最后的',' sInsert = sInsert.Substring(0, sInsert.Length - 1); //生成最后的insert z (a,b,c,d) values(?,?,?, ?) sInsert += ")"; //生成adapter ole_adapter.InsertCommand = new OleDbCommand(sInsert,Conn_S); select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) { string col_name = reader["name"].ToString().Trim(); string col_type = reader["typename"].ToString().Trim(); int length = System.Convert.ToInt32(reader["length"].ToString()); int status = System.Convert.ToInt32(reader["status"].ToString()); OleDbType dbType = OleDbType.Char;
switch (col_type) { case "char": dbType = OleDbType.Char; break; case "varchar": dbType = OleDbType.VarChar; break; case "decimal": dbType = OleDbType.Decimal; break; case "int": dbType = OleDbType.Integer; break; case "smallint": dbType = OleDbType.SmallInt; break; case "datetime": dbType = OleDbType.DBTimeStamp; break;
select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) {这段是通过系统表取每个字段信息,然后加到参数里。
我的数据库是用的ODBC而不是OleDb,有没有类似的例子?
Xml我已读到DataSet中,我很关心如何Insert(update)到数据库中
方法二:解析XML,提取数据插入到数据库中
直接使用数据适配器的UPDATE方法进行更新就可以
sp_xml_preparedocument 返回一个句柄,可用于访问 XML 文档的新创建的内部表示方式。该句柄在连接到 Microsoft® SQL Server™ 2000 期间保持有效,直到重置连接或执行 sp_xml_removedocument 使句柄无效为止。
说明 分析过的文档存储在 SQL Server 2000 的内部高速缓存中。MSXML 语法分析器使用 SQL Server 可用总内存的八分之一。若要避免内存不足,请运行 sp_xml_removedocument 以释放内存。
语法
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]参数
hdoc是新创建的文档的句柄。hdoc 的数据类型为 integer。[xmltext]是原 XML 文档。MSXML 语法分析器分析该 XML 文档。xmltext 是 text 类型(char、nchar、varchar、nvarchar、text 或 ntext)的参数。默认值是 NULL,在这种情况下,将创建空 XML 文档的内部表示法。[xpath_namespaces]指定 OPENXML 的行和列 XPath 表达式中所使用的命名空间声明。默认值是 <root xmlns:mp='urn:schemas-microsoft-com:xml-metaprop'>。
xpath_namespaces 通过符合语法规则的 XML 文档的方式,为在 OPENXML 的 Xpath 表达式中使用的前缀提供命名空间 URI。xpath_namespaces 声明前缀必须用于引用命名空间 urn:schemas-microsoft-com:xml-metaprop,该命名空间提供有关分析后的 XML 元素的元数据。尽管可以使用此方法为元属性命名空间重新定义命名空间前缀,但此命名空间不会丢失。此前缀 mp 对 urn:schemas-microsoft-com:xml-metaprop 仍有效,即使 xpath_namespaces 不包含此类声明。xpath_namespaces 是 text 类型(char、nchar、varchar、nvarchar、text 或 ntext)的参数。 返回代码值
0(成功)或 >0(失败)权限
执行权限默认授予 public 角色。示例
A. 为符合语法规则的 XML 文档准备内部表示方式
下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。在对 sp_xml_preparedocument 的调用中,使用了默认命名空间前缀映射。DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID='VINET' ContactName='Paul Henriot'>
<Order CustomerID='VINET' EmployeeID='5' OrderDate='1996-07-04T00:00:00'>
<OrderDetail OrderID='10248' ProductID='11' Quantity='12'/>
<OrderDetail OrderID='10248' ProductID='42' Quantity='10'/>
</Order>
</Customer>
<Customer CustomerID='LILAS' ContactName='Carlos Gonzlez'>
<Order CustomerID='LILAS' EmployeeID='3' OrderDate='1996-08-16T00:00:00'>
<OrderDetail OrderID='10283' ProductID='72' Quantity='3'/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Remove the internal representation.
exec sp_xml_removedocument @hdocB. 为带 DTD 的符合语法规则的 XML 文档准备内部表示方式
下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。存储过程根据文档中包含的 DTD 来验证装载的文档。在对 sp_xml_preparedocument 的调用中,使用了默认命名空间前缀映射。DECLARE @hdoc int
DECLARE @doc varchar(2000)
SET @doc = '
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE root
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID='ALFKI' ContactName='Maria Anders'/>
</root>'EXEC sp_xml_preparedocument @hdoc OUTPUT, @docC. 指定命名空间 URI
下例返回作为输入提供的新创建的 XML 文档内部表示法的句柄。在对 sp_xml_preparedocument 的调用中,保留了元属性命名空间映射的 mp 前缀,并将 xyz 映射前缀添加到了命名空间 urn:MyNamespace。DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID='VINET' ContactName='Paul Henriot'>
<Order CustomerID='VINET' EmployeeID='5'
OrderDate='1996-07-04T00:00:00'>
<OrderDetail OrderID='10248' ProductID='11' Quantity='12'/>
<OrderDetail OrderID='10248' ProductID='42' Quantity='10'/>
</Order>
</Customer>
<Customer CustomerID='LILAS' ContactName='Carlos Gonzlez'>
<Order CustomerID='LILAS' EmployeeID='3'
OrderDate='1996-08-16T00:00:00'>
<OrderDetail OrderID='10283' ProductID='72' Quantity='3'/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<root xmlns:xyz='run:MyNamespace'/>'
如何将DataTable 写入数据库?
conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f
unction to run this query.
conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f
unction to run this query.代码如下:
OdbcDataAdapter adapter = new OdbcDataAdapter("select * from table1",Conn1);
OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);
adapter.Fill(ds_main,"table1");
ds_main.ReadXml("c:\\1.xml",XmlReadMode.ReadSchema);
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
急!请帮帮忙
OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);adapter.Fill(ds_main,"table1");
ds_main.ReadXml("c:\\1.xml",XmlReadMode.ReadSchema);
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
DataTable datatable = ds_main.Tables[0];
ds_main.Tables[0].PrimaryKey = new DataColumn[] {ds_main.Tables[0].Columns[0]};
DataTable datatable = ds_main.Tables[0];
DataRow row = ds_main.Tables[0].Rows[0];
row["col1"] = "xp";
adapter.Update(ds_main,"table1");
ds_main.Tables[0].AcceptChanges();
Conn1.Close();
rted against a SelectCommand that does not return any base table information.
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
DataTable datatable = ds_main.Tables[0];
ds_main.Tables[0].PrimaryKey = new DataColumn[] {ds_main.Tables[0].Columns[0]};
DataTable datatable = ds_main.Tables[0];
DataRow row = ds_main.Tables[0].Rows[0];
row["col1"] = "xp";这一段改成:
ds_main.Tables["table1"].Rows[0]["col1"]="xp";试下看..如果没有问题,那就说明可能是你操作xml的问题了..
还是不行:
Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo
rted against a SelectCommand that does not return any base table information.
用commandBuilder的几点限制:注意点:
1.只能更新一个表,不能更新两个或两个以上相关联的表
2.表中必须有主键
3.更新的表中字段不能有image类型的
为什么没有这句呢。不是说要更新吗。
高手给我发信,谢谢。
adapter.Update(ds,"table1");
为什么没有这句呢。不是说要更新吗。有这句的,仔细看..
Parameter[6] '' has no default value.
Parameter[7] '' has no default value.
Parameter[13] '' has no default value.
Parameter[15] '' has no default value.如何解决?
string select = "select count(*) as col_num from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
int col_num = 0;
string sInsert = "Insert " + sTableName +" (";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
col_num = System.Convert.ToInt16(reader[col_num]); }
select = "select name from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
sInsert += reader["name"].ToString() + ",";
}
//处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1);
sInsert += ") VALUES ( ";
for (int i = 1; i <= col_num; i++)
{
sInsert += "?,";
}
//处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1); //生成最后的insert z (a,b,c,d) values(?,?,?, ?)
sInsert += ")"; //生成adapter
ole_adapter.InsertCommand = new OleDbCommand(sInsert,Conn_S); select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
string col_name = reader["name"].ToString().Trim();
string col_type = reader["typename"].ToString().Trim();
int length = System.Convert.ToInt32(reader["length"].ToString());
int status = System.Convert.ToInt32(reader["status"].ToString());
OleDbType dbType = OleDbType.Char;
switch (col_type)
{
case "char":
dbType = OleDbType.Char;
break;
case "varchar":
dbType = OleDbType.VarChar;
break;
case "decimal":
dbType = OleDbType.Decimal;
break;
case "int":
dbType = OleDbType.Integer;
break;
case "smallint":
dbType = OleDbType.SmallInt;
break;
case "datetime":
dbType = OleDbType.DBTimeStamp;
break;
default:
dbType = OleDbType.Char;
break;
}
ole_adapter.InsertCommand.Parameters.Add("@" + col_name, dbType, length,col_name);
//处理可空的字段
if (status == 8)
{
ole_adapter.InsertCommand.Parameters["@" + col_name].IsNullable = true;
}
int col_num = 0;
string sInsert = "Insert into" + sTableName + " (";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
if (reader.Read())
{
col_num = System.Convert.ToInt32(reader["col_num"]); }
//关闭Reader
reader.Close(); select = "select name from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{ sInsert += reader["name"].ToString() + ","; }
//关闭Reader
reader.Close(); //处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1);
sInsert += ") VALUES ( ";
for (int i = 1; i <= col_num; i++)
{
sInsert += "?,";
}
//处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1); //生成最后的insert z (a,b,c,d) values(?,?,?, ?)
sInsert += ")"; //生成adapter
ole_adapter.InsertCommand = new OleDbCommand(sInsert, Conn_S); select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
string col_name = reader["name"].ToString().Trim();
string col_type = reader["typename"].ToString().Trim();
int length = System.Convert.ToInt32(reader["length"].ToString());
int status = System.Convert.ToInt32(reader["status"].ToString());
OleDbType dbType = OleDbType.Char; switch (col_type)
{
case "char":
dbType = OleDbType.Char;
break;
case "varchar":
dbType = OleDbType.VarChar;
break;
case "decimal":
dbType = OleDbType.Decimal;
break;
case "int":
dbType = OleDbType.Integer;
break;
case "smallint":
dbType = OleDbType.SmallInt;
break;
case "datetime":
dbType = OleDbType.DBTimeStamp;
break; default:
dbType = OleDbType.Char;
break;
} ole_adapter.InsertCommand.Parameters.Add("@" + col_name, dbType, length, col_name); //.*********************************
//还有那么多参数你都没有添加呢
//******************************** //处理可空的字段
if (status == 8)
{
ole_adapter.InsertCommand.Parameters["@" + col_name].IsNullable = true;
}
}
reader.Close();
{
sInsert += "?,";
}所以你也要添加这么多的参数,并且还要给这些参数值啊..
cmd.Parameters.AddWithValue("@Sno",this.TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@Sname",this.TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@Sage",Convert.ToInt32(this.TextBox3.Text.Trim());
//插入操作
cmd.ExecuteNonQuery();以上是添加了三个参数,并且注意添加的顺序也要和数据库中的一致..
ole_adapter.InsertCommand = new OleDbCommand(sInsert, Conn_S);后面添加参数时:ole_adapter.InsertCommand.Parameters.Add("@" + col_name, dbType, length, col_name);
//处理可空的字段
if (status == 8)
{
ole_adapter.InsertCommand.Parameters["@" + col_name].IsNullable = true;
}就两个参数吗?
还有参数是在哪添加的?
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{这段是通过系统表取每个字段信息,然后加到参数里。