Altering a SQL View from C# Ado.Net

Question

Hi I would like to alter a sql view from C# Ado.net. I am using frontend as c# and backend as SQL Server. I tried below options: option#1: alter view vw_employeesbyId as select enamelocationemail from dbo.employees where empid = @empid option#2: var sql = @alter view vw_employeesbyId as select enamelocationemail from dbo.employees where empid = @empid ; Exec sp_ExecuteSql (sql) Option3: Drop the view and create view from c# all 3 options not worked. any other thougths are appreciated Thanks Sreenivasa Rao K

sreenivasa k · Answer

I am getting error - ALTER VIEW must be the first statement in a query batch.

sreenivasa k · Answer

Thanks for responses. Hi Azarudeen  i had tried one the same option which you provided. but getting incorrect syntax near view. Please let me know if you have any other thoughts.

Mudzakkir Toha · Answer

Do you understand about view in SQL Server? You are about changing DDL and I guess you need DML. Do not changing the View on the fly if you not need it it will causing impact on another users. This is the view. create view vw_employeesbyId as select enamelocationemail from dbo.employees This is your DML: select * from vw_employeesbyId where empid = @empid Why you need to re-create view? when you need to get data by EmpId?

Mohamed Azarudeen Z · Answer

Hi Srini Try this Connect to the SQL Server database using a SqlConnection object. Create a SqlCommand object with the ALTER VIEW statement to modify the view. Set any necessary parameters in the SqlCommand object if you need to pass dynamic values. Execute the ALTER VIEW statement using the ExecuteNonQuery method of the SqlCommand object. Close the SqlConnection object once you're done. using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string alterViewSql = ALTER VIEW vw_employeesbyId AS SELECT ename location email FROM dbo.employees WHERE empid = @empid; using (SqlCommand command = new SqlCommand(alterViewSql connection)) { command.Parameters.AddWithValue(@empid empId); // Set the parameter value if required command.ExecuteNonQuery(); // Execute the ALTER VIEW statement } connection.Close(); } Dont forget to replace the connection strings Hope this helps

Altering a SQL View from C#, Ado.Net