You must Sign In to post a response.
  • Category: ASP.NET

    For truncating table what statement is used?

    I have created one sp that tuncate table.Table name is dynamic.Here I used dynamic sql.Sp is working fine.I want to execute that sp from C#.net(from cs file). I know executenonquery returns no of row affected.executenonquery is used for insert,update and delete command.Exectesclare is used for select which has only one cell.EceuteReader is used for selecting multiple record.What shall i use that tell my tuncate table clause executed properly or not?
  • #762496
    Hi

    You can use ExecuteNonQuery. This truncate is also a kind of delete but with out any where condition.

    SqlConnection cn=new SqlConnection("connection string");
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "your sp";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection=cn;
    try
    {
    cn.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.ExecuteNonQuery();

    }
    catch (Exception ex)
    {
    ex.ToString();
    }
    finally
    {
    cn.Close();
    }

    Sridhar Thota.
    Editor: DNS Forum.

  • #762497
    Hello Pinky,

    You can use ExecuteNonQuery for truncating table.

    Sample code below:

    SqlCommand cmd;
    SqlConnection con;
    con=new SqlConnection("Connection String here");

    try
    {
    con.Open();
    string strSql = "TRUNCATE TABLE table_name";
    cmd=new SqlCommand(strSql,con);
    cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    finally
    {
    con.Close();
    }


    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #762498
    Thanks Sridhar and Nirav for ur reply.I used same.

  • #762499
    Hi

    You can follow this steps for execute Truncate Procedure in C# code.


    Create Table dynamic
    (
    Id int primary key identity(1,1),
    ProductName varchar(30)
    )

    Insert into dynamic VALUES('Mouse')
    Insert into dynamic VALUES('Optical-Mouse')
    Insert into dynamic VALUES('Keyboard')
    Insert into dynamic VALUES('101-Keyboard')


    CREATE PROCEDURE SP_TRUNCATETABLE
    AS
    Truncate table dynamic

    -- How to select
    Select * from dynamic

    --How to call From Sql Side
    Select * from dynamic
    Exec SP_TRUNCATETABLE



    Server Side Code c#



    protected void BtDel_Click(object sender, EventArgs e)
    {
    string constr = ConfigurationManager.AppSettings["s"].ToString();
    using (SqlConnection con = new SqlConnection(constr))
    {
    using (SqlCommand cmd = new SqlCommand("SP_TRUNCATETABLE", con))
    {
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
    int result = cmd.ExecuteNonQuery();
    }
    }

    }



    I have attached snapshot given below.

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.