Use procedure of one database and save data into another database table using C#

Question

I have many databases and the database is dynamically changed by input in connection string. But I have procedure in one database suppose named 'DB1' and want to insert data into another database named 'DB2'.

I can't make another connection string for inserting purpose, because I don't know about databases.

My code:

SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

conn1.Open();

SqlCommand cmd3 = new SqlCommand();
cmd3.Connection = conn1;
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.CommandText = "storedProcedure";

cmd3.Parameters.AddWithValue("@Col0", string.IsNullOrEmpty(index[0].ToString()) ? (object)DBNull.Value : index[0].ToString());    
cmd3.Parameters.AddWithValue("@Col1", string.IsNullOrEmpty(index[1].ToString()) ? (object)DBNull.Value : index[1].ToString());
cmd3.Parameters.AddWithValue("@Col2", string.IsNullOrEmpty(index[2].ToString()) ? (object)DBNull.Value : index[2].ToString());
cmd3.Parameters.AddWithValue("@Col3", string.IsNullOrEmpty(index[3].ToString()) ? (object)DBNull.Value : index[3].ToString());

cmd3.ExecuteNonQuery();

I am using two connection strings, one for use the procedure and second for insert data into a specific database. I want insert data into 2nd connection string named connString1. But I don't know how can I do this.

My connection string:

<add name="connString" 
     connectionString="Data Source=DBSERVER-PC\SQL2012;Database=DB1;User ID=sa;Password=a "
     providerName="System.Data.SqlClient" />

<add name="connString1" 
     connectionString="Data Source=DBSERVER-PC\SQL2012;Database=DB2;User ID=sa;Password=a "
     providerName="System.Data.SqlClient" />

Any idea how I can solve this problem?

Any reason you can't deploy the stored procedure into both databases? — John Wu, Oct 04 '19 at 06:16
I have Db1 that have stored procedure but i want to insert data into another database. So we can use one connectionstring at a time, right? then how can i do? — scoop, Oct 04 '19 at 06:22
Question not clear. Is the stored procedure for data retrieval or for inserting the data? — John Wu, Oct 04 '19 at 06:29
stored procedure is for inserting data. Procedure is in database1 and want to insert data into another database2's table. — scoop, Oct 04 '19 at 06:36
If the databases are in the same server, have a look at this [link](https://stackoverflow.com/questions/3502269/how-to-insert-table-values-from-one-database-to-another-database) — Ardit, Oct 04 '19 at 07:10

score 0 · Accepted Answer · answered Oct 04 '19 at 07:18

Suppose You have a Table in (Database) DB1 called FROM dbo.item

--------------------
item_id | base_price
--------------------
   1    |   10.5

   2    |   6.5

You want this table insert into another (Database) DB2 called [dbo].[Testitem]

CREATE PROCEDURE dbo.Sp_InsertData
(
@DBNAME VARCHAR(100) = NULL     
)
AS
BEGIN

DECLARE @str VARCHAR(MAX)       
SET @str='INSERT INTO ' + @DBNAME + '.dbo.Testitem(item_id, base_price) select item_id, base_price from dbo.item '
exec (@str)

END

Testing Script For Execute SP

EXEC dbo.Sp_InsertData
@DBNAME='DB2'

Note:- You have pass Database(DB2) name From Code behind as @Paramter in DB1 using web.config.....

Use procedure of one database and save data into another database table using C#

1 Answers1