Tag: sql server

SQL SERVER DATABASEDE ISTENILEN DEĞERİ ARAMA PROSEDURE

On By semt20In SqlLeave a comment 
--EXEC SearchAllTables 'a' 

CREATE PROC SearchAllTables( @SearchStr nvarchar(100))
    AS
 BEGIN
     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
        SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
        SET @TableName = ''
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE @TableName IS NOT NULL
      BEGIN
        SET @ColumnName = ''
        SET @TableName = (
                            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                              FROM INFORMATION_SCHEMA.TABLES
                             WHERE TABLE_TYPE = 'BASE TABLE'
                               AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                               AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA)
                                                      + '.' + QUOTENAME(TABLE_NAME) ),
                                                  'IsMSShipped' ) = 0
                         )
      WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
              BEGIN
                        SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME))
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
                        AND TABLE_NAME = PARSENAME(@TableName, 1)
                        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                        AND QUOTENAME(COLUMN_NAME) > @ColumnName )
                         IF @ColumnName IS NOT NULL
                              BEGIN
                                        INSERT
                                          INTO #Results
                                          EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''',
                                                 LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + '
                                                 (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
                                END
                END
       END
SELECT ColumnName, ColumnValue
  FROM #Results
   END

VISUAL STUDIO C# SQL SERVER CONNECTION HELPER

On By semt20In C SHARPLeave a comment 
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;




namespace BMS_PDKS
{
    public class SQLHELPER
    {
        string baglanticumlesi = string.Format("Server={0}; Database={1}; User Id ={2};Password ={3}", CFG_TEMPLATE.CONFIG.LGDBSERVER, CFG_TEMPLATE.CONFIG.LGDBDATABASE, CFG_TEMPLATE.CONFIG.LGDBUSERNAME, CFG_TEMPLATE.CONFIG.LGDBPASSWORD);
        SqlConnection con;
        SqlDataAdapter adtr;
        public SqlConnection baglanti()
        {
            con = new SqlConnection(baglanticumlesi);
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            return con;
        }
        //public DataTable veri_getirme(string sqlcumle, DevExpress.XtraGrid.GridControl dtgrd)
        public DataTable GET_B_CAPIFIRM(GridControl dtgrdc,GridView dtgrdv)
        {
            DataTable dt = new DataTable();
            dtgrdv.Columns.Clear();
            try
            {
                using (adtr = new SqlDataAdapter("select * from B_CAPIFIRM", baglanti()))
                { adtr.Fill(dt); dtgrdc.DataSource = dt; }
            }
            catch (System.Exception ex) { MessageBox.Show(ex.Message); }
            return dt;
        }
        public DataTable GET_B_CAPIUSER(GridControl dtgrdc, GridView dtgrdv)
        {
            DataTable dt = new DataTable();
            dtgrdv.Columns.Clear();
            try
            {
                using (adtr = new SqlDataAdapter("select * from B_CAPIUSER", baglanti()))
                { adtr.Fill(dt); dtgrdc.DataSource = dt; }
            }
            catch (System.Exception ex) { MessageBox.Show(ex.Message); }
            return dt;
        }

    }
}

--


DAHA SONRA ANA FORMDA DEVEXPRESS GRIDVIEW YARAT VE ŞU ŞEKİLDE ÇAĞIR:


        SQLHELPER sqlhelper = new SQLHELPER();


        private void button1_Click(object sender, EventArgs e)
        {
            sqlhelper.GET_B_CAPIFIRM( gridControl1,gridView1);
        }
        private void button2_Click(object sender, EventArgs e)
        {
            sqlhelper.GET_B_CAPIUSER(gridControl1, gridView1);
        }

C# SQL E TARIH AKTARIM UYGUN FORMAT

On By semt20In C SHARP, SqlLeave a comment 
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")




devexpress datetime component automove to next için
masktype 2 tane var ikisinde datetimeadvancingcaret yap

        public Form1()
        {
            InitializeComponent();
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;












ercanın kodlarda
            CultureInfo tr = new CultureInfo("tr-TR");

            Thread.CurrentThread.CurrentCulture = tr;

SQL SERVER TAKVIM TURKCE

On By semt20In SqlLeave a comment 
  ALTER VIEW BM_TAKVIM AS
WITH Calender AS (
    SELECT CAST('2019-01-01' AS DATETIME) AS dt
    UNION ALL
    SELECT dt + 1 FROM Calender
    WHERE dt + 1 <=  CAST('2019-12-31' AS DATETIME)
)
SELECT
dt

,CASE
WHEN DATENAME (Month,dt)='January' THEN 'Ocak'
WHEN DATENAME (Month,dt)='February' THEN 'Şubat'
WHEN DATENAME (Month,dt)='March' THEN 'Mart'
WHEN DATENAME (Month,dt)='April' THEN 'Nisan'
WHEN DATENAME (Month,dt)='May' THEN 'Mayıs'
WHEN DATENAME (Month,dt)='June' THEN 'Haziran'
WHEN DATENAME (Month,dt)='July' THEN 'Temmuz'
WHEN DATENAME (Month,dt)='August' THEN 'Ağustos'
WHEN DATENAME (Month,dt)='September' THEN 'Eylül'
WHEN DATENAME (Month,dt)='October' THEN 'Ekim'
WHEN DATENAME (Month,dt)='November' THEN 'Kasım'
WHEN DATENAME (Month,dt)='December' THEN 'Aralık' ELSE '' END Ay
,CASE WHEN DATENAME (Weekday,dt)='Monday' THEN 'Pazartesi'
WHEN DATENAME (Weekday,dt)='Tuesday' THEN 'Salı'
WHEN DATENAME (Weekday,dt)='Wednesday' THEN 'Çarşamba'
WHEN DATENAME (Weekday,dt)='Thursday' THEN 'Perşembe'
WHEN DATENAME (Weekday,dt)='Friday' THEN 'Cuma'
WHEN DATENAME (Weekday,dt)='Saturday' THEN 'Cumartesi'
WHEN DATENAME (Weekday,dt)='Sunday' THEN 'Pazar' ELSE '' END Gün
,Hafta = DATEPART(WEEK, dt)  FROM Calender

 GO

  SELECT *
 FROM BM_TAKVIM WITH(NOLOCK)
 Option(MaxRecursion 0)

alastyr plesk windows hostinge pcdeki sql serverden baglanma

On By semt20In Sql, webLeave a comment
once pleski acip sol tarafdan databesisden yeni kullanici yarat
ms sql secili olsun allow remote connections from secip altina who.isden ipni yaz

daha sonra database yeni database server sql server

sql serverede
server name sadece ornekserver.com yazilmasi yeterli
login pleskte database kısmında user managementta yaratilan kullanici ve parola

——————-
visual studio asp proje deploy,
pleske visual studio aspxlerin projelerini ataiblmek icin alastyre pleskte web deployu aktif etmelerini soyle.
bu tarz link mail atacaklar sana https://mywebsite.xyz:8172/msdeploy.axd?site=mywebsite.xyz
bunu visual studioda solutiona sag tikla publishden web deployu secerek.

server: https://mywebsite.xyz:8172/msdeploy.axd?site=mywebsite.xyz
site: mywebsite.xyz/sw/test
user name:mywebsite (hostu alirken attiklari kullanici adi)
password:parola (hostu alirken attiklari kullanici adi)
destination url:mywebsite.xyz/sw/test