c# - 检查用户是否存在于数据库中的存储过程
问题描述
我在某个地方犯了一个错误,我的存储过程或源代码无法正常工作。我创建了一个应用程序,它收集所有用户Active Directory
并存储到.txt file
. 现在 INSERT 方法有效,但它会插入每个用户。我只想插入数据库中不存在的那些用户。否则,如果用户存在于数据库中,请检查他/她的状态(帐户是否启用/禁用)并与Active Directory
数据进行比较并进行更改。这是我到目前为止所做的:
USE [DesignSaoOsig1]
GO
/****** Object: StoredProcedure [dbo].[ADProcTemp] Script Date: 28. 4. 2020. 15:07:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ADProcTemp]
@Username varchar(250),
@DisplayName varchar(70),
@isEnabled tinyint,
@PassNevExp tinyint
AS
set nocount on
BEGIN
IF NOT EXISTS (SELECT TOP 1 PrezimeIme FROM [dbo].[tblZaposleni_AD]
WHERE PrezimeIme = @Username
AND NetworkLogin = @DisplayName
AND Status = @isEnabled
AND PassNevExp = @PassNevExp)
BEGIN
INSERT INTO [dbo].[tblZaposleni_AD](NetworkLogin,PrezimeIme,Status,PassNevExp)
VALUES (@Username, @DisplayName, @isEnabled,@PassNevExp)
END
END
IF EXISTS (SELECT TOP 1 PrezimeIme FROM [dbo].[tblZaposleni_AD]
WHERE PrezimeIme = @Username)
BEGIN
UPDATE [dbo].[tblZaposleni_AD]
SET Status = @isEnabled
END
源代码
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices;
using System.IO;
namespace ActiverDirectory
{
public class Korisnik
{
public int Id { get; set; }
public string Username { get; set; } //samaccountname
public string DisplayName { get; set; } //displayname
public bool isEnabled { get; set; } //useraccountcontrol
public bool PassNevExp { get; set; } //pwdlastset
}
class Program
{
static void Main(string[] args)
{
foreach (Korisnik korisnik in VratiKorisnike())
{
Console.WriteLine(korisnik);
}
}
public void ExcStrPrc(string Username, string DisplayName, bool isEnable, bool PassNevExp)
{
SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True");
SqlCommand cmd = new SqlCommand("ADProcTemp", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", Username.ToString().Trim());
cmd.Parameters.AddWithValue("@DisplayName", DisplayName.ToString().Trim());
cmd.Parameters.AddWithValue("@isEnabled", Convert.ToInt32(isEnable));
cmd.Parameters.AddWithValue("@PassNevExp", Convert.ToInt32(PassNevExp));
conn.Open();
int k = cmd.ExecuteNonQuery();
if (k != 0)
{
Console.WriteLine("Record Inserted Succesfully into the Database");
}
conn.Close();
}
public static List<Korisnik> VratiKorisnike()
{
List<Korisnik> lstADUsers = new List<Korisnik>();
string sDomainName = "saos";
string DomainPath = "LDAP://" + sDomainName;
string fileLoc = @"C:\output.txt";
DirectoryEntry searchRoot = new DirectoryEntry(DomainPath);
DirectorySearcher search = new DirectorySearcher(searchRoot);
search.Filter = "(&(objectClass=user)(objectCategory=person))";
search.PropertiesToLoad.Add("samaccountname"); // Username
search.PropertiesToLoad.Add("displayname"); // display name
search.PropertiesToLoad.Add("userAccountControl"); // isEnabled
search.PropertiesToLoad.Add("pwdLastSet"); //passwordExpires
DataTable resultsTable = new DataTable();
resultsTable.Columns.Add("samaccountname");
resultsTable.Columns.Add("displayname");
resultsTable.Columns.Add("Neaktivan");
resultsTable.Columns.Add("dontexpirepassword");
SearchResult result;
SearchResultCollection resultCol = search.FindAll();
if (resultCol != null)
{
for (int counter = 0; counter < resultCol.Count; counter++)
{
string UserNameEmailString = string.Empty;
result = resultCol[counter];
if (result.Properties.Contains("samaccountname")
&& result.Properties.Contains("displayname"))
{
int userAccountControl = Convert.ToInt32(result.Properties["userAccountControl"][0]);
string samAccountName = Convert.ToString(result.Properties["samAccountName"][0]);
int isEnable;
int Dont_Expire_Password;
if ((userAccountControl & 2) > 0)
{
isEnable = 0;
}
else
{
isEnable = 1;
}
if ((userAccountControl & 65536) > 0)
{
Dont_Expire_Password = 1;
}
else
{
Dont_Expire_Password = 0;
}
//if ((userAccountControl & 2) != 0)
//{
// isEnable = 1;
//}
//else
//{
// isEnable = 0;
//}
Korisnik korisnik = new Korisnik();
korisnik.Username = (result.Properties["samaccountname"][0]).ToString();
korisnik.DisplayName = result.Properties["displayname"][0].ToString();
korisnik.isEnabled = Convert.ToBoolean(result.Properties["userAccountControl"][0]);
DataRow dr = resultsTable.NewRow();
dr["samaccountname"] = korisnik.Username.ToString();
dr["displayname"] = korisnik.DisplayName.ToString();
dr["neaktivan"] = Math.Abs(isEnable);
dr["dontexpirepassword"] = Dont_Expire_Password;
resultsTable.Rows.Add(dr);
// Poziva se store procedura
Program p = new Program();
p.ExcStrPrc(korisnik.Username.ToString().Trim(), korisnik.DisplayName.ToString().Trim(), Convert.ToBoolean(isEnable), Convert.ToBoolean(Dont_Expire_Password));
lstADUsers.Add(korisnik);
}
}
var json = JsonConvert.SerializeObject(resultCol, Formatting.Indented);
var res = json;
Console.WriteLine("Ispis uspjesno obavljen");
Console.ReadLine();
File.WriteAllText(fileLoc, json);
}
return lstADUsers;
}
}
}
我在哪里做错了?
解决方案
您的错误位于 SQL 存储过程中的这一行。
IF NOT EXISTS (SELECT TOP 1 PrezimeIme FROM [dbo].[tblZaposleni_AD]
WHERE PrezimeIme = @Username
AND NetworkLogin = @DisplayName
AND Status = @isEnabled
AND PassNevExp = @PassNevExp)
它需要是
IF NOT EXISTS (SELECT * FROM [dbo].[tblZaposleni_AD] WHERE PrezimeIme = @Username)
BEGIN
INSERT INTO [dbo].[tblZaposleni_AD](NetworkLogin,PrezimeIme,Status,PassNevExp)
VALUES (@Username, @DisplayName, @isEnabled,@PassNevExp)
END
ELSE
BEGIN
UPDATE [dbo].[tblZaposleni_AD]
SET
Status = CASE WHEN Status <> @isEnabled THEN @isEnabled ELSE Status END
WHERE
PrezimeIme = @Username
END
推荐阅读
- windows - 当使用 windows 标头模拟输入事件时,它会停止程序,直到手动给出中断
- python - 大尺寸输入的Tensorflow Estimator Graph Size Limitation
- reactjs - 哪个是处理 React JSX 的合适加载器?
- excel - VBA:替换 * 或 & 等字符
- html - 如何在输入字段的 vlaue 中加载 font-awesome 图标
- awk - 匹配模式后仅打印下一行的一部分
- azure - 将 azurerm_application_gateway 与 AKS 与 terraform 集成
- regex - 使用 GREP / Regex 将给定 url 扩展名的 url 中的下划线替换为破折号
- javascript - 如何检查字符串是否有星号后跟四位数字?
- python - Python 函数错误