how to set schema in ado.net

Posted by Srinup97 under ADO.NET on 9/3/2013 | Points: 10 | Views : 5584 | Status : [Member] | Replies : 6
Hi

Iam connecting to db2 database i have a problem that i will going to schema in after connection opening
i want to set the schema

iam connecting to the db2 database in this database different schemas are there.
I want to connect to connect particular schema only,
i tried that in connection string we cant give the schema,
After connection opening only we have to set the schema,
i have a code that i.e by using connect to the active data object(ADO) only,
but in ado.net how to give i dont know
Below is the code for ado connection
db.Open DBcon_string
db.Execute ("SET SCHEMA=" & AppSchema)
db.Execute ("SET PATH=""SYSIBM"",""SYSFUN"",""SYSPROC"",""SYSIBMADM"",""" & AppSchema & """")

Note: db is adodb.connection
Replace AppSchema with ‘ETWRMS’

srinivasp


Responses

Posted by: Allemahesh on: 9/3/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can see the links below:-

http://stackoverflow.com/questions/153769/how-to-see-the-schema-of-a-db2-table-file
http://forums.pentaho.com/showthread.php?47314-Setting-DB-SCHEMA-name-when-using-DB2
https://forums.oracle.com/thread/2310251

Happy Coding.


Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer the following link "Using an ADO.NET Entity Framework Data Provider"
http://www.datadirect.com/download/eval_docs/dotnet_win_quickstart.htm

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Srinu,
Look into the following connection strings.. There you can refer setting default schema
http://www.connectionstrings.com/ibm-db2/

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.swg.im.dbclient.adonet.ref.doc%2Fdoc%2FDB2ConnectionClassConnectionStringProperty.html
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.swg.im.dbclient.adonet.ref.doc%2Fdoc%2FDB2ConnectionStringBuilderClassSchemaListProperty.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srinup97 on: 9/3/2013 [Member] Starter | Points: 25

Up
0
Down
iam connecting to the db2 database in this database different schemas are there.
I want to connect to connect particular schema only,
i tried that in connection string we cant give the schema,
After connection opening only we have to set the schema,
i have a code that i.e by using connect to the active data object(ADO) only,
but in ado.net how to give i dont know
Below is the code for ado connection
db.Open DBcon_string
db.Execute ("SET SCHEMA=" & AppSchema)
db.Execute ("SET PATH=""SYSIBM"",""SYSFUN"",""SYSPROC"",""SYSIBMADM"",""" & AppSchema & """")

Note: db is adodb.connection
Replace AppSchema with ‘ETWRMS’


Please tell me

srinivasp

Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srinup97 on: 9/4/2013 [Member] Starter | Points: 25

Up
0
Down
Hi to everybody,
it is solved how to set schema in ado.net below is the code
OleDbConnection _connection = new OleDbConnection(strconnection);
OleDbCommand _cmd = new OleDbCommand();
_cmd.Connection = _connection;
_connection.Open();

string strRMSSchema = "SET SCHEMA= Schema name";
_cmd.CommandType = CommandType.Text;
_cmd.CommandText = strRMSSchema;
_cmd.ExecuteNonQuery();

string strpath = "SET PATH=SYSIBM,SYSFUN,SYSPROC,SYSIBMADM, Schema name
_cmd.CommandType = CommandType.Text;
_cmd.CommandText = strpath;
_cmd.ExecuteNonQuery();

string sql = "Spname";

_cmd.CommandType = CommandType.StoredProcedure;
_cmd.ExecuteNonQuery();

srinivasp

Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Misterbaanu on: 8/17/2017 [Member] Starter | Points: 25

Up
0
Down
Filling a DataSet with Schema
When filling a DataSet with data, the DataAdapter.Fill method uses the existing schema of the DataSet and populates it with data returned from the SelectCommand. If there is no table name in the DataSet that matches the name of the table being filled, the Fill method creates a table. By default, Fill only defines columns and column types.
You can override the default behavior of Fill by setting the MissingSchemaAction property of the DataAdapter. For example, to have Fill create a table schema that also includes primary key information, unique constraints, column properties, whether nulls are allowed, the maximum length of the column, read-only columns, and auto-increment columns, specify that DataAdapter.MissingSchemaAction is MissingSchemaAction.AddWithKey. Alternatively, you can call DataAdapter.FillSchema before calling DataAdapter.Fill to ensure that the schema is in place when the DataSet is filled.
Calling FillSchema will result in an extra trip to the server to retrieve the additional schema information. For best performance, specify the schema of the DataSet, or set the MissingSchemaAction of the DataAdapter before calling Fill.
Best Practices with the CommandBuilder
The CommandBuilder automatically generates the InsertCommand, UpdateCommand, and DeleteCommand properties of a DataAdapter based on the SelectCommand property of the DataAdapter, provided that the SelectCommand performs a single table SELECT. Here are some tips for best performance using the CommandBuilder.
Use of the CommandBuilder should be limited to design time or ad-hoc scenarios. The processing required to generate the DataAdapter command properties hinders performance. If you know the contents of your INSERT/UPDATE/DELETE statements beforehand, set them explicitly. A good design tip is to create stored procedures for your INSERT/UPDATE/DELETE commands and explicitly configure the DataAdapter command properties to use them.
The CommandBuilder uses the SelectCommand property of the DataAdapter to determine the values for the other command properties. If the SelectCommand of the DataAdapter itself is ever changed, be sure to call RefreshSchema to update the command properties.
The CommandBuilder only generates a command for a DataAdapter command property if that command property is null (the command properties are null by default). If you explicitly set a command property, the CommandBuilder does not overwrite it. If you want the CommandBuilder to generate a command for a command property that has been set previously, set the command property to null.
https://tekslate.com/ Explore more at : https://msdn.microsoft.com/en-us/library/ms971481.aspx

Srinup97, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response