Set the Default Value to the Column Of a Table Programmaticaly

Normally we face this type of problem when we want to set the column default value pro-grammatically.

 To Solve this problem we need to first get the table column information using this Query.

 "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='person'".

 Here the table name may vary.

 when we execute this Query we will get the each column information .So based on this we can Iterate the each column and get the each column Information as given below 
 
 
  VB.NET 
 Private Sub Initialise_The_Table(ByVal ReqTab As String)  
      On Error GoTo handle_error  
      Dim eachTableAdapter As SqlDataAdapter  
      Dim tableDataset As New DataSet  
      Dim adoHelper As SQLHelper = SQLHelper.GetInstance  
      eachTableAdapter = adoHelper.GetAdapter("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ReqTab + "'")  
      eachTableAdapter.FillSchema(tableDataset, SchemaType.Source, ReqTab)  
      eachTableAdapter.Fill(tableDataset, ReqTab)  
      For ii As Integer = 0 To tableDataset.Tables(ReqTab).Rows.Count - 1  
        If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = (SqlDbType.NVarChar.ToString.ToLower) _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.NText.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.Int.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.Real.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.Float.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.SmallInt.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.TinyInt.ToString.ToLower _  
        Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.Money.ToString.ToLower Then  
          If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.NVarChar.ToString.ToLower _  
          Or Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("DATA_TYPE")).ToLower = SqlDbType.NText.ToString.ToLower Then  
            If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("IS_NULLABLE")).ToLower = "yes" Then ''''Checking the column IS_NULLABLE   
              If Convert.IsDBNull(tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_DEFAULT")) Or _  
              String.IsNullOrEmpty(tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_DEFAULT").ToString) = False Then  
                Errorlog.WriteLog(Information.Err().Number, "Field Defaults set on ", ReqTab, _  
                         tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME"))  
                adoHelper.ExecNonQuery("ALTER TABLE [dbo].[" + ReqTab + "] ADD CONSTRAINT [DF_" + ReqTab + "__" + _  
                         tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME") + "] DEFAULT '' FOR " + _  
                         tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME") + "")  
                GoTo Next_Field  
              End If  
            End If  
          Else  
            If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("IS_NULLABLE")).ToLower = "yes" Then ''''Checking the column IS_NULLABLE   
              If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_DEFAULT")) <> "((0))" Then  
                If Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_DEFAULT")).ToLower() <> "genuniqueid()" _  
                And Strings.Mid(Convert.ToString(tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME")), 1, 2).ToLower() <> "s_" Then  
                  Errorlog.WriteLog(Information.Err().Number, "Field Defaults set on ", ReqTab, _  
    tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME"))  
                  adoHelper.ExecNonQuery("ALTER TABLE [dbo].[" + ReqTab + "] ADD CONSTRAINT [DF_" + ReqTab + "__" + _  
                             tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME") + "] DEFAULT 0 FOR " + _  
                             tableDataset.Tables(ReqTab).Rows(ii)("COLUMN_NAME") + "")  
                End If  
                GoTo Next_Field  
              End If  
            End If  
          End If  
        End If  
  Next_Field:  
      Next ii  
      Exit Sub  
  handle_error:  
      If Information.Err().Number = 3078 Then  
        MessageBox.Show("No such table in database", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error)  
        Exit Sub  
      Else  
        If Do_All = 1 And Not Init_Phase Then  
          MessageBox.Show(Information.Err().Description, "System Error: " & Information.Err().Number & " table = " & Public_db.TableDefs(i).Name, _         MessageBoxButtons.OK, MessageBoxIcon.Error)  
        Else  
          MessageBox.Show(Information.Err().Description, "System Error: " & Information.Err().Number, MessageBoxButtons.OK, MessageBoxIcon.Error)  
        End If  
      End If  
      Resume  
    End Sub





I think solution will be helpful to many guys :-)
Happy meaningful day :-)
Location: Hyderabad, Andhra Pradesh, India

Comments

Post a Comment

Popular posts from this blog

Exporting to excel from a custom class object using C#.NET

We may face a scenario to export the data of custom class object to excel using InterOp .You can get the property name's from the class object using Reflection.Create a work sheet header by reading the each propety of the class object.I have created a separate method to get the all properties into a list collection of string as given below         ///         /// Get the list of Properties Name into a list collection.         ///         ///         ///         public List GetPropeties(Employee objEmployee)         {             Type myType = objEmployee.GetType();             IList props = new List (myType.GetProperties());  ...
Read more

How to hide bootStrap popover when user click(s) outside the popover?

Hiding bootStrap popover when user click(s) outside the popover Recently I have experienced this problem i.e., hiding the popover when we click outsode the popover.I have worked on it and fixed the issue.So,I thought to share this fix. For more detail(s) about the bootStrap please refer this link PopOver popover plugin has a property called " trigger " of type String with default event as " click " and popover can be triggered by click/hover/focus/manual.  To show the popover we need to prepare the HTML mark-up as given below < div class = " btn-group " > < div class = " btn-group " id = " ddlSelected " style = " clear: both; " > < button class = " btn btn-primary btnSelectedStyle selectedBorderStyle " type = " button " > Your selected Items ( < b id = " lblSelectedItemCount " > 0 < / b > ) < / butt...
Read more

How to Clear the Textbox Text using C#?

This is one of the common requirement we get in our projects.Text box has a wonderful method called " Clear ".This method is used to clear the text in the Text box.I have given a simple example to explain this .I have implemenetd a common method to clear the Text of few Controls .One of that is Textbox. void ClearInputs(ControlCollection ctrls) { foreach (Control ctrl in ctrls) { if (ctrl is TextBox) ((TextBox)ctrl).Text = string .Empty; ClearInputs(ctrl.Controls); } }   You can call this method where ever you want to clear the Text from the Textbox. For more information please click this link TextBox       Hope this would help the beginners. Cheers.
Read more